Short Python tutorial: Advanced topics in Pandas (2023)

My experience with delving into the advanced features of Pandas for Python in 2023
James Birkenau



November 27, 2023


I’ve been deep in the trenches of data processing and analysis for years, and I can tell you this: Pandas is more than just another Python library. Whether you’re wrangling complex datasets or fine-tuning performance for large-scale data operations, Pandas serves as the backbone for efficient, robust data manipulation. It’s a tool I’ve leveraged time and again, not just for basic tasks but also for the crucial stages of machine learning workflows. Through trial, error, and many hours of coding, I’ve gathered insights and best practices that I’m eager to share – tips that are especially pivotal for those stepping into the world of Python data science.

Advanced Data Manipulation with MultiIndex

A screenshot of a multi-level dataframe with multiindex in action

When I first started working with Pandas, dealing with flat data was a walk in the park. But as soon as I had to tackle more complex data structures, such as multiple levels of grouping within a single dataset, things got a bit trickier. Enter MultiIndex: a game-changer for working with high-dimensional data in a two-dimensional data structure.

So, what’s the deal with MultiIndex? Well, imagine you’re dealing with sales data that span different regions, various products, and multiple time periods. Without MultiIndex, you’d be juggling several pivot tables or merging datasets left and right. With MultiIndex, you can structure this data in a single DataFrame, with indices representing multiple dimensions.

Here’s a quick example to show you how it’s done. Let’s say we have sales data for two products (“Widgets” and “Gadgets”) across two regions (“North” and “South”) for two quarters:

import pandas as pd

arrays = [['North', 'North', 'South', 'South'], ['Q1', 'Q2', 'Q1', 'Q2'], ['Widgets', 'Widgets', 'Gadgets', 'Gadgets']]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['Region', 'Quarter', 'Product'])
data = {'Sales': [25000, 30000, 15000, 20000]}
df = pd.DataFrame(data, index=index)

This gives us a DataFrame with hierarchical indexing, which you can verify by simply printing df.

Next, let’s say I want to look at total sales by region, regardless of the quarter or product. This is where the sum function, along with the level parameter, becomes handy:

regional_sales = df.sum(level='Region')

You’ll notice that this operation respects the hierarchical structure, adding up only within the specified level.

Now, what if I need to reshape this data? With MultiIndex, pivoting becomes a breeze. Suppose I want to compare the product sales side by side for each quarter. The unstack method can rearrange our MultiIndex DataFrame into something more conventional:

df_unstacked = df.unstack(level="Product")

The opposite of unstack is, predictably, stack. It collapses one of the DataFrame’s levels, which can be useful when you need to serialize or feed the data into some machine learning pipeline—just not the one you might be learning in another part of this article series.

Slicing is also pretty straightforward with MultiIndex, especially with the help of the pd.IndexSlice utility. If I need to fetch data for “Widgets” in the “North” region:

idx = pd.IndexSlice
widgets_north_data = df.loc[idx['North', :, 'Widgets'], :]

Looks simple once you get the hang of it, right?

For those who want to dig even deeper, you can find extensive documentation on MultiIndex in the official Pandas documentation ( And, if you’re like me and learn better by looking at examples, the Pandas GitHub repository ( is full of unit tests and usage instances that reveal real-world applications of MultiIndex.

I have to say, mastering MultiIndex has paid off brilliantly in my data manipulation tasks. It brings a powerful dimensionality to DataFrame operations, turning seemingly complex tasks into a few lines of code. And for beginners, don’t feel overwhelmed. The more I integrated MultiIndex into my workflows, the more intuitive it became. Happy indexing!

Performance Tuning in Pandas

A graph showing time taken by different operations before and after optimization

Performance tuning is critical when working with large datasets in Pandas. Efficiency can often be the difference between a task that takes minutes versus one that drags on for hours. I’ve stumbled upon a bunch of tricks that significantly cut down my code’s running time, and I’m eager to share some of these optimization strategies.

One of the first things I learned was the importance of choosing the right data types. Pandas defaults to using 64-bit types, which can be overkill and consume unnecessary amounts of memory. By converting to smaller data types where possible, you can dramatically reduce memory usage. Let’s look at a basic example:

import pandas as pd

# Assume df is our large DataFrame

# Check the data types and memory usage

# Convert int64 to int32 and float64 to float32
df_int = df.select_dtypes(include=['int64']).astype('int32')
df[df_int.columns] = df_int

df_float = df.select_dtypes(include=['float64']).astype('float32')
df[df_float.columns] = df_float

# Verify the reduced memory usage

If you’re dealing with categorical data, the category datatype in Pandas can be a lifesaver. Especially if you have a significant amount of repetition in your text data:

# Convert object to category
df_object = df.select_dtypes(include=['object']).nunique().sort_values()
# Assume we have a 'category' column that has a limited number of unique values
df['category'] = df['category'].astype('category')

# Check the before and after memory usage

Another performance tip I follow is to avoid loops wherever possible and utilize Pandas’ vectorized operations. This was a game-changer when I first refactored my loop-laden scripts. Here’s what this might look like in practice:

# Bad Practice with Loop
for index, row in df.iterrows():[index, 'new_col'] = row['col1'] * row['col2']

# Better Practice with Vectorization
df['new_col'] = df['col1'] * df['col2']

Lastly, I frequently make use of the query() and eval() methods to speed up my boolean indexing and computations. Instead of the standard pandas operations, which can be slow, these methods accelerate operations by using Numexpr. Here’s a comparison:

# Using standard Pandas operations
df_filtered = df[df['col'] > 0]

# Using query for faster operation
df_filtered = df.query('col > 0')

And for calculations:

# Using standard Pandas operations
df['new_col'] = df['col1'] * df['col2'] + df['col3']

# Using eval for faster operation
df.eval('new_col = col1 * col2 + col3', inplace=True)

A combination of these tactics usually improves performance spectacularly, but remember that the results can vary based on the size and nature of your dataset. The key takeaway I’ve had from my experiences is to always test different approaches to find the most suitable solution.

For further reading and deeper dives into Pandas performance tuning, I highly recommend checking out the official Pandas documentation on enhancing performance, as well as continuously benchmarking your code with tools like %timeit in IPython or Python’s time module. Keep experimenting to discover the optimal setup for your specific use case.

Timeseries Analysis Made Easy

A plot of a time series dataset with trend components highlighted

Timeseries analysis is a critical tool for anyone working with data affected by time. With pandas, one of Python’s flagship libraries, you can efficiently manipulate and analyze timeseries data, even with limited experience in data science. I’ll share some essentials to help you get up and running quickly.

Firstly, make sure pandas is installed:

pip install pandas

Now, let’s say we have a CSV file named ‘stock_data.csv’ containing daily stock prices with a date column. Creating a pandas DataFrame from this file is straightforward:

import pandas as pd

df = pd.read_csv('stock_data.csv', parse_dates=['Date'], index_col='Date')

The parse_dates parameter ensures that pandas recognizes our ‘Date’ column as datetime objects, which is vital for timeseries analysis. Setting this column as the index with index_col allows for easy slicing and dicing of data based on time.

With this setup, I can quickly access stock prices on a particular day:

specific_day = df.loc['2023-01-01']

Or if I need to get all the stock prices for January 2023, I use:

january_data = df['2023-01']

One common operation is resampling, which changes the frequency of time points in the data. For instance, to convert our daily data into monthly data and calculate the mean price per month:

monthly_data = df.resample('M').mean()

Moving averages are often used to smooth out short-term fluctuations and highlight longer-term trends. Calculating a simple 7-day moving average goes like this:

df['7_day_rolling_avg'] = df['Price'].rolling(window=7).mean()

For timeseries forecasting, pandas can prepare the data structure that will be fed into a forecasting model. Let’s use a hypothetical machine learning model that predicts future stock prices based on historical data. Our first step would be to create a lagged feature, which is the previous day’s price:

df['Prev_day_price'] = df['Price'].shift(1)

But there’s more to timeseries than simple manipulation. Often, we’re dealing with missing values, especially if the stock market is closed on weekends and holidays. We can forward-fill these missing values with pandas:

df = df.asfreq('D', method='ffill')

Plotting is also a significant part of the analysis. With pandas, visualizing your timeseries data is a snap. Here’s how I can quickly plot our monthly_data DataFrame:

import matplotlib.pyplot as plt

plt.title('Monthly Average Stock Price')

Now, if you’re keen on furthering your knowledge beyond the basics I’ve shown, there’s a goldmine of resources out there. For in-depth techniques and complex scenarios, the pandas documentation ( is exceptionally detailed. Additionally, Jake VanderPlas’s “Python Data Science Handbook” is a fantastic resource with an associated GitHub repository ( that has example notebooks for you to follow along.

To get beyond the beginner stage, consider tackling a project with real-life data. The famous Time Series Forecasting Practice on Kaggle ( offers datasets and challenges that will test your skills and develop your understanding.

Remember, while this overview touches upon key aspects of timeseries analysis with pandas, practice is truly where the learning happens. So grab a dataset and start exploring!

Integrating Pandas with Modern Python Async IO

A diagram illustrating how pandas interfaces with async io functions

Integrating async IO with Pandas is a bit like trying to fit a square peg into a round hole; they’re not originally designed to go together seamlessly. However, Python’s asynchronous features can offer significant performance benefits when dealing with I/O bound operations. As someone who has wrestled with large, unwieldy datasets that depend on live data feeds, I’ve developed some strategies to weave together the synchronous world of Pandas with the asynchronous Python functions. Let’s walk through them.

The asyncio library in Python enables concurrency via coroutines. Traditional synchronous operations block the thread until completion, while an asynchronous operation is suspended when awaiting on a result that is not immediately available, thus freeing the thread to do other work in the meantime.

However, most Pandas functions are synchronous and, as such, will block the event loop when they’re running. To prevent this, we must run Pandas operations in a thread pool, using asyncio’s run_in_executor method. This enables a non-blocking way to use Pandas within an async application. Let’s demonstrate this with an example.

Assume we have an async function to fetch data over the network (a common async operation). We’ll execute the fetching asynchronously but then process the retrieved data using Pandas in a thread pool.

import aiohttp
import pandas as pd
import asyncio
from concurrent.futures import ThreadPoolExecutor

# Fetch data asynchronously
async def fetch_data(session, url):
async with session.get(url) as response:
return await response.text()

# Process data with Pandas in a thread-safe way
def process_data(data):
df = pd.read_csv(data)
# Do some Pandas operations on the df
return df

async def main():
# URL list to fetch data from
urls = ["", ""]
# Create a ThreadPoolExecutor
executor = ThreadPoolExecutor(max_workers=2)

async with aiohttp.ClientSession() as session:
# Schedule all fetch operations to run asynchronously
fetch_futures = [fetch_data(session, url) for url in urls]
raw_data_list = await asyncio.gather(*fetch_futures)

# Once we have the data, process it in parallel using Pandas in a thread-safe way
loop = asyncio.get_event_loop()
df_list = await asyncio.gather(*(loop.run_in_executor(executor, process_data, data) for data in raw_data_list))

# Now df_list contains all our processed DataFrames

if __name__ == "__main__":

The code uses aiohttp for asynchronous HTTP requests, fetching CSV data from example URLs. It then processes the data into Pandas DataFrames in a thread-safe manner using a ThreadPoolExecutor. This is a simplified example, but it demonstrates the essential setup.

It’s important to note that while asyncio and Pandas don’t communicate directly, this method of handling Pandas’ heavy lifting in a separate thread allows your application to benefit from async IO. You’ll want to adjust the number of workers in the ThreadPoolExecutor to match your system’s capabilities, as creating too many threads can have a negative impact on performance.

By applying this hybrid approach in your Python development, you’re combining the strengths of both asynchronous programming and Pandas’ powerful data manipulation. While we’re not turning Pandas itself into an async library, we’re allowing it to fit seamlessly into an asynchronous framework.

For aspiring developers and data analysts, mastering the intricacies of both Pandas and Python’s async IO can be the difference between an application that just works and one that excels in efficiency. Just remember that the async world is full of gotchas, so test each step thoroughly and keep an eye on your event loop!

Leveraging Pandas for Machine Learning Workflows

A flowchart showing the steps of using pandas in a machine learning data pipeline

After exploring the multifaceted capabilities of Pandas in dealing with advanced data manipulation, performance optimization, and timeseries, I’d like to turn your attention to how this powerful library harmonizes with machine learning workflows.

In machine learning, data preprocessing is a critical step. Pandas excels at this. Cleaning data, handling missing values, and feature engineering are just a few examples where Pandas acts as a preliminary workhorse before you even start training models.

Let’s start by importing the essential libraries:

import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import StandardScaler

Next, assume you’ve loaded a dataset into a Pandas DataFrame. You’ll first want to take care of any missing values:

# Sample code for handling missing values
df.fillna(df.mean(), inplace=True)

With your data’s null values addressed, you move onto encoding categorical variables if present:

# Convert categorical column 'Category' to numerical values
df['Category'] = df['Category'].astype('category')

Before sending your data through a machine learning model, it’s best practice to standardize features:

# Standardizing the feature columns
scaler = StandardScaler()
df[['Feature1', 'Feature2']] = scaler.fit_transform(df[['Feature1', 'Feature2']])

Splitting your dataset into training and test sets is easily handled by sklearn’s train_test_split, but it’s worth noting that Pandas plays nicely with this splitting:

# Note: Assumes 'Label' is the column you're predicting
X = df.drop('Label', axis=1)
y = df['Label']
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

As you can see, I’m able to pass Pandas structures directly into the training and testing functions, which streamlines the process. Most of sklearn’s API can handle Pandas DataFrames and Series without issue.

Feature selection is another area where Pandas shows its strength. Say you’ve computed the correlation matrix, and wish to drop highly correlated features:

# Compute the correlation matrix
corr_matrix = df.corr().abs()

# Select upper triangle of correlation matrix
upper = corr_matrix.where(np.triu(np.ones(corr_matrix.shape), k=1).astype(np.bool))

# Find features with correlation greater than 0.95
to_drop = [column for column in upper.columns if any(upper[column] > 0.95)]

# Drop features
df.drop(to_drop, axis=1, inplace=True)

Once you’ve trained your model, Pandas can also assist in evaluating the results. Assuming you’ve made some predictions, you can juxtapose them with the actual values and compute metrics right from the DataFrame:

from sklearn.metrics import accuracy_score

predictions = model.predict(X_test)
results = pd.DataFrame({'Actual': y_test, 'Predicted': predictions})
print(accuracy_score(results['Actual'], results['Predicted']))

Throughout your machine learning pipeline, you will find that Pandas provides intuitive, flexible, and efficient ways to manipulate your data. From cleaning to feature extraction, and even to the post-modeling analysis – embracing Pandas during the journey can make the process significantly smoother.

While this might seem a lot to take in for a beginner, I want to assure you that once you get the hang of these operations, they become second nature in your data science practice. Remember, the key is persistence, and experimenting with these tools will pay off as you delve deeper into machine learning projects.

In conclusion, by leveraging the power of Pandas alongside your machine learning algorithms, you streamline the preprocessing pipeline, enabling you to focus on constructing and optimizing sophisticated models. The interplay between data preprocessing with Pandas and model building is symbiotic, each reinforcing the success of the other. Embrace these techniques; they are indispensable tools in your data science arsenal.