Python in Excel: How to understand how objects work

The concept of objects significantly distinguishes Python from Excel, enhancing its importance in programming. Integrating Python objects into Excel introduces unique behaviors and functionalities, which will be the focal point of this blog post. Download the following to explore this intriguing intersection of Python and Excel.

 

We’ll begin the exploration in the formulas-objects worksheet.

How do Python objects work differently than Excel cells?

One of the most notable distinctions between Python and Excel lies in Python’s utilization of objects. In Python, each data element is stored as a distinct object in memory, isolated from its predecessors. This architecture ensures that data is not immediately recalculated whenever another object is modified. This approach significantly simplifies auditing and reproducing work, making it more efficient than in Excel.

Consider a basic scenario in an Excel worksheet, where formulas are sequentially stacked to derive a result:

A single misplaced keystroke or alteration can lead to errors cascading throughout an Excel spreadsheet. Imagine the consequences if this erroneous cell resides in a separate worksheet or, even more challenging, in a different workbook.

Troubleshooting and debugging in such scenarios become exceedingly difficult, resembling a precarious and haphazard game of Jenga rather than a methodical debugging process.

Contrarily, Python operates by storing each data element within a distinct object. While this is a simplified explanation, envision assigning each data element as placing it into its unique container, termed an object. Each of these objects is stored separately in memory, and altering one does not inherently affect the others. Therefore, if an anomaly occurs in one object, it does not trigger widespread chaos among the rest.

It’s important to note that in standard Python, when data is encapsulated into an object, you must print or metaphorically “open the box” to view its contents. This characteristic of Python differs in its application within Excel. Additionally, the default method of object storage in Python is modified when integrated with Excel.

Python in Excel is optimized for Pandas DataFrames

For this next part of the demo, head to the points worksheet, which displays the top five NBA players ranked by points scored at the time this blog post was composed.

Python, much like a collection of boxes with varying shapes and sizes, accommodates a diverse array of object types.

Excel’s grid-based layout is particularly suited for handling tabular datasets. It’s a powerful tool for tasks such as generating new columns from existing ones, altering column formats, and other similar operations.

When dealing with such data in Excel, the ideal Python object type is the Pandas DataFrame. This is the default object type utilized when integrating Python with Excel, perfectly tailored for managing Excel data.

To begin, activate a Python cell in your Excel worksheet using the PY() function, and link it to the points data. This action will generate a Pandas DataFrame right within Excel:

By default, the name of the created object appears in the Excel cell. However, if you wish to view the actual data it contains, simply use the keyboard shortcut Ctrl + Alt + Shift + M to reveal the underlying dataset.

Don’t forget to assign the object

So far, we haven’t assigned the results of our data manipulation to a Python object. Utilizing Python without assigning operations to objects might seem feasible, but doing so significantly limits its capabilities. It’s akin to using a hand-held calculator for ad-hoc calculations without retaining any of the results for future use. To leverage Python’s full potential in Excel, return to your Python code cell and begin by assigning the results to a DataFrame named points:

Having successfully assigned and named the DataFrame, we now have the flexibility to perform extensive data analysis and manipulation, thanks to the capabilities of Pandas.

Suppose we want to determine the average number of points scored in this dataset. To accomplish this, we can utilize the powerful features of Pandas. The resulting computation will be assigned back to a new object, which I’ll name avg_points_pd:

Switching to other Python object types

While Python in Excel defaults to storing data in a DataFrame, it’s important to note that this isn’t the only data structure available. As previously mentioned, Python offers a variety of object types for different purposes. Generally, Pandas is an excellent choice for handling data, thanks to its robust and efficient functionalities.

However, there are instances when opting for a different data type is advantageous. Take, for instance, my current task: I plan to convert the points column into a NumPy array to calculate its mean. This method yields the same result as using Pandas, albeit requiring a bit more effort. So, one might wonder, why choose this approach?

The benefits of switching to other object types

One of the most significant aspects to consider is the efficiency in terms of time and performance. This can be effectively evaluated using the %%time magic cell command in Python. By placing this command at the beginning of your cell, you can swiftly obtain performance metrics.

These speed measurements are conveniently displayed in the Diagnostics menu, located to the right of your data:

Let’s delve into the diagnostics together. A millisecond (ms) is equivalent to 1000 microseconds (µs), making the first calculation notably quicker than the second. Specifically, the NumPy method, utilized in cell A10, demonstrates a speed roughly five times greater than the Pandas method, when considering both CPU and wall times. To understand the nuances between CPU and wall time, and their applications in Python, you might find this post informative.

This difference in performance time is quite significant on a relative scale. However, the distinction between microseconds and milliseconds might seem negligible to the average user. Thus, it’s important to weigh whether the efficiency gained with NumPy justifies the slightly more complex coding requirements. Imagine you’re working with a large dataset where calculating the average is faster with NumPy by a few seconds compared to Pandas. In such cases, opting for NumPy could be advantageous.

Beyond speed and efficiency, there are scenarios where transitioning between different object types in Python can simplify your tasks in Excel. A fascinating instance is provided by Owen from FlexYourData, demonstrating how 3D NumPy arrays can be employed to circumvent the need for writing loops or iterations in solving Excel problems.

In both my example and Owen’s, we emphasize the NumPy array as an optimal choice for converting your DataFrame. This serves as an indication of effective practices, but numerous other possibilities exist for transforming your DataFrame into different objects, offering substantial benefits. Below is a summary table that illustrates these methods:

Conversion Target	Code to Convert from DataFrame	Code to Convert Back to DataFrame
Numpy Array	df.to_numpy() or df.values	pd.DataFrame(array)
List	df.values.tolist()	pd.DataFrame(list)
Tuple	tuple(map(tuple, df.values))	pd.DataFrame(list_of_tuples)
Dictionary	df.to_dict()	pd.DataFrame(dict)

Additionally, it’s worth noting that Owen’s demonstrations are remarkably impressive, showcasing an aspirational level of proficiency in utilizing diverse Python objects within Excel. However, it’s equally important to acknowledge and celebrate your current achievements. Remember, by integrating NumPy, you’ve achieved notable efficiency gains. Feel free to compare your work with mine to see the progress you’ve made:

What questions do you have about how Python objects work in Excel specifically, or Python in Excel more generally? Let me know in the comments.

The post Python in Excel: How to understand how objects work first appeared on Stringfest Analytics.