As shown in the image above, we can enter our Python formula in cell B2.

Here's the code for loading a pandas DataFrame using the CustomerBehaviorPQ connection:

Clicking the disk (i.e., Save) icon will run the Python code:

Conceptually, here's what's happening behind the scenes:

1. The PQ connection reads in the CustomerBehavior.csv file.

2. This data is passed to the new xl() function.

3. The data and code are uploaded to the Azure cloud.

4. A secure container is created in Azure to run the code with the data.

5. The results are then returned to Excel.

After the code is run in the Azure cloud, you can click on the DataFrame output in the Python Editor to get a preview of the DataFrame object:

The new xl() function is the data gateway for your PiE code. For security reasons, your PiE code is very limited in what it can do. For DIY data science, your PiE code essentially does 3 things:

• Source data via the xl() function.

• Perform analytics on the data.

• Return the results of these analytics (e.g., DataFrames, visualizations, ML models, etc.).

For more information on the xl() function, check out this Microsoft article.

As you do more and more DIY data science with PiE, you will likely find that you increasingly rely on data sources outside your workbooks. This is where PQ connections come into play.

It will also be common to source data from within your workbooks. A best practice is to use Excel tables as the primary data source for intra-workbook DIY data science:

You also use the xl() function to load data from Excel tables by name (you do name your Excel tables, don't you? 🤣).

Clicking the following button in the Python Editor will add another Python formula in cell B3 of the Python Code worksheet:

And the code:

The above code specifies three things:

• The Excel table name (i.e., CustomerBehavior).

• The entire table should be loaded (i.e., [#All]).

• The first row of the table is the column names (i.e., headers = True).

Clicking DataFrame in the Python Editor output will display a preview of the DataFrame (BTW - it's the same as what you saw from the PQ code).

Lastly, you can use the xl() function to load data from a specified cell range. While this is possible, please don't!

Using Excel tables is better because they force you to organize your data properly for use in DIY data science (e.g., machine learning).

Here's the code in cell B4 of the Python Code worksheet:

You've probably noticed that I've been writing the PiE code in the same column and moving down a row for each new Python formula.

I highly recommend you do this. Here's a screenshot of how I typically organize my PiE code:

If you're familiar with Jupyter Notebooks (e.g., from my free Python Crash Course), you'll notice that this style is very similar to how you build a Jupyter Notebook.

This is an engineering practice for building maintainable PiE workbooks, but it is not required.

As I mentioned previously, you can make any cell a Python formula. To handle the nature of Excel cells and worksheets, PiE has a particular calculation order you must understand.

From the Microsoft documentation:

"Traditional Python statements calculate from top to bottom. Within a Python in Excel cell, Python statements do the same thing—they calculate from top to bottom. But in a Python in Excel worksheet, Python cells calculate in row-major order. The cell calculations run across a row (from column A to column XFD), and then across each following row down the worksheet.

Python statements are ordered, so each Python statement has an implicit dependency on the Python statement that immediately precedes it in the calculation order.

The calculation order is important when defining and referencing variables in a worksheet, because you must define variables before you can reference them."

BTW - What isn't mentioned above is that the calculation order also goes worksheet by worksheet from left to right.

Trust me, building your workbooks relying on PiE calculation order is a recipe for disaster.

Keep your PiE code in dedicated worksheets and vertically organize your Python formulas as I show above.

Your future self will thank you.