Python’s emergence as the programming language of choice for everything from web development to scientific computing, data analytics, and artificial intelligence is largely due to its array of available open-source libraries. A library is a collection of modules (essentially, a Python file with more code) that allows programmers to build on code already written by others, which serves as a feedback loop to invite even more developers to expand the ecosystem. Unsurprisingly, one of the areas with extensive libraries is data analysis, which will be the focus of this article.

Pandas

We will focus on Pandas, a popular library that greatly enhances Python’s ability to work with structured data and spreadsheets. It has obvious similarities to Microsoft Excel and is a good starting point for finance professionals. We provide a walk-through of basic operations in Python and finish with a demo that uses Pandas to manipulate real financial data.

As a third-party library, Pandas is not included in the standard Python package and must be installed separately. Fortunately, this is very easily done via pip install or through Anaconda distribution. Once installed, it should be imported at the beginning of every script.

Figure 1

Creating a DataFrame

With Pandas, programmers work with two-dimensional arrays called DataFrames, which essentially function like tables. Loading Excel files into a Pandas DataFrame is easy (we will cover this later), but for now, we go through the basics of creating and manipulating DataFrames.

There are several ways to create a DataFrame. Below demonstrates a method using a dictionary where keys represent column labels and values are lists of equal length containing data. The dictionary is then used to create a new DataFrame object called new_df.

Tip: Using an.ipynb notebook is generally preferred when working with DataFrames. It renders DataFrames as HTML-like tables, allowing programmers to visualize and interactively modify them line by line.

Figure 2

By default, when creating a new DataFrame, Pandas assign row indices unless otherwise specified, while the columns are labeled with the keys “A”, “B”, “C” that we provided. These labels are not considered part of the dataset, although they appear in the top row. Labels can be any hashable data type (for more on hashable data types, refer to “Hashing in Python”).

Selecting individual values in a DataFrame

We can reference values in the DataFrame using the df.loc[] or df.iloc[] methods. This is like selecting a cell in an Excel spreadsheet (e.g., cell “A1”).

With df.loc[], values are accessed based on the index (for the row) and the label (for the column).

Figure 3

With df.iloc[], both rows and columns are referenced based on indices. Here, the column “A” represents index 0, “B” represents index 1, and so on. 

Figure 4

Using df.loc[] or df.iloc[], we can overwrite values in the DataFrame. Note that the data types within a column or row do not need to be homogeneous; we can store a string just as easily as an integer. However, this flexibility can potentially lead to issues, as we will see later. 

Figure 5

loc and iloc are also great for slicing, enabling us to select portions of a DataFrame. Note that slicing does not modify the original itself but allows programmers to reference subsets of the data. As a general guide, modifications, additions, and overwrites require using the assignment operator “=”.

Figure 6

Adding a new column or row

Inserting a new column is simple.

Figure 7

To add a new row, we can use the df.loc method. However, the DataFrame’s index must be continuous. If there are gaps in the index (like missing numbers), adding a row using df.loc might unintentionally overwrite existing data.

Figure 8

Filtering

Filtering allows us to select specific rows or columns based on certain conditions.

Figure 9

Performing computations

One effective method of performing computations across a DataFrame is the apply() function, which has the below syntax.

Figure 10

For straightforward operations, a lambda function can be used with apply(). Below, we demonstrate adding a new column “E” (specified with axis=1) to the DataFrame. Each value in “E” is computed as the sum of the squared values from columns “A” and “B”, multiplied by the corresponding value in column “D”. (In this case, we can also make use of vectorized operations, which is even faster. This will be demonstrated at the end of this article.)

Figure 11

Not all operations can be performed using lambda functions. Fortunately, df.apply() can support more advanced, user-defined functions. This is where the advantage of being part of the Python environment becomes evident, as we can draw on other libraries.

As an example, suppose we have a spreadsheet/DataFrame of customer support messages with the associated customer contact info, and want to extract the phone numbers or emails into a separate column. This can be achieved relatively easily with the use of the regular expression (regex) library (for more on regex, refer to “Regular Expression in Python”).

Figure 12 

Reading and writing data

We have walked through the basics of creating and manipulating DateFrames, but professionals often work with existing datasets in formats like .csv, .xlsx, .json, and SQL databases.

Pandas has extensive input/output (I/O) support, allowing users to read various types of files into DataFrames. Here are the functions for some of the popular file types, as well as required arguments:

• pd.read_excel(“full_file_path”)
• pd.read_csv(“full_file_path”)
• pd.read_json(“full_file_path”)
• pd.read_sql(“SQL QUERY”, connection) connection supported: ADBC Connection, SQLAlchemy connectable, str, or sqlite3

Below is a simple load example, using a data set provided by Google Colab. We use the method df.head(5) to view only the first five rows.

Figure 13

Naturally, output is also widely supported by Pandas. Below is a list of output functions for the aforementioned types.

• pd.to_excel(“full_file_path”)
• pd.to_csv(“full_file_path”)
• pd.to_json(“full_file_path”)
• pd.to_sql(“Table_name”, connection)

Here is an example of writing the DataFrame to Excel.

Figure 14

Comparisons to Excel: Speed and scalability of Pandas

Like other Python data types, the DataFrame is an object that resides in computer memory (RAM), enabling faster in-memory computations than Excel, which relies on more I/O when handling large datasets or complex formulas that reference other sheets. (For more on computer memory, refer to “Copy and Reference in Python.”)

Excel’s graphical user interface (GUI), which makes it easy for beginners to pick up, also contributes to slower performance due to the overhead of real-time updates and handling user interactions. Meanwhile, Pandas computations are highly optimized, benefiting from its efficient implementation in C, as well as leveraging NumPy (another Python library) for vectorized operations, which are significantly faster for numerical computations compared to traditional looping (i.e., in VBA macros). Scalability is another factor, as Excel has a cap of just over a million rows of data, while Pandas DataFrames are theoretically bounded by the amount of computer RAM, which for a standard 16 GB RAM computer can go up to several hundred million rows.

In addition, being part of the Python ecosystem means that operations are easier to automate and can be easily version-controlled using systems like Git, which can track line-by-line changes. This can be more challenging for VBA macros embedded in Excel files.

Although it comes at the cost of a steeper initial learning curve, Pandas’s speed, scalability, and integration with the rest of the Python ecosystem make it a must-have for large-scale, complex data analysis. However, Excel remains an excellent choice for small datasets and quick visualizations thanks to its GUI.

Putting it all together using real-world financial data

There are many other DataFrame operations, such as merging and summarizing, that we do not have enough scope to cover. Instead, we will finish with a demo to showcase how beginners can leverage Python’s extensive programming environment to automate basic extract, transform, load (ETL). We highly recommend readers check out the Pandas documentation for a deeper dive into Pandas.

An important advantage of Python is the availability of many application programming interfaces (APIs) to retrieve data. An API is a set of protocols that enable a software application to interact with another—in this case to retrieve data from a source or server.

Below, we showcase the use of the Yahoo Finance Python API, which allows us to easily pull historical data into a Pandas DataFrame. From there, we can perform various automated data computations, then export and/or share. Best of all, this is all free.

We will break the process down cell by cell before presenting it all in one final script.

Pip install Yahoo Finance

Figure 15

Define stock tickers to pull

In this case we will pull the daily closing prices of the popular Magnificent Seven stocks and the S&P 500 Index and put them all in one DataFrame. The more detailed documentation is available on PyPI.

We will only use the yfinance.download() call, which returns a Pandas DataFrame of historical data for a given ticker. This is what the DataFrame looks like for S&P 500 Index.

Figure 16

We do for this for all eight tickers, extract only the “Adj Close” column, and stack them into a DataFrame.

Figure 17

Pulling all the tickers and combining them into one DataFrame, we note that all the Magnificent Seven stocks have a NaN, which makes sense as none of them had IPOed back in the 1920s. Pandas allows us to easily deal with values by dropping rows with NaN via df.dropna(). As such, we are left with data as recent as 2012, the day of Meta’s IPO.

Figure 18

Suppose we are interested in comparing cumulative returns over time. Leveraging several built-in Pandas vectorized operations like df.pct_change() and df.cumprod(), we can easily convert the daily prices to cumulative returns, as below.

(Note that this computation method will fail to capture the true total return, as the data is not dividend-adjusted. Fortunately for us, the period captured is relatively short, while several of these stocks have not paid dividends over that time.)

Figure 19

With the cumulative returns ready, we can use Python’s Matplotlib library to display them all on the same chart. We will define a function called plot_returns() which takes the DataFrame as an argument, in case we want to make more plots later.

Figure 20

Due to the standout performance of Nvidia Corp stock, which beat the S&P 500 Index by a factor of over 100 during the period, the scale of the chart is thrown off and does not adequately capture the relative movement of the stocks in periods of market turmoil (like in 2022). With Python, we can easily apply a logarithmic transformation by leveraging the NumPy library to better visualize the relative performance.

Figure 21

Below presents the entire program in a concise Python script, with minimal comments. We have added an export_results() function to illustrate how one might export the data to Excel, though this can be easily substituted for other types of storage, such as an SQL database. Then, the process can be scheduled to run at regular intervals via Task Scheduler (Windows) or Cron Job (Mac/Linux).

Thanks to Python’s simple syntax and rich libraries, we just performed an entire ETL, plus basic visualization, in about 50 lines. Not bad! 

Figure 22