Pandas DataFrame: Grouping rows by day of the week

Overview

When working with time series data in Python, Pandas is an incredibly powerful tool that can simplify data manipulation and analysis. One common task is grouping data by time intervals, such as days of the week. This can unlock insights into weekly patterns and trends that might not be immediately apparent. In this tutorial, we’ll explore how to use Pandas to group DataFrame rows by the day of the week with several examples, progressing from basic to advanced.

Prerequisites: Before we dive into the examples, ensure you have Pandas installed in your Python environment. If not, you can install it using pip:

pip install pandas

Basic Grouping by Day of the Week

First, let’s start with a simple example. Assume we have a DataFrame containing sales data, including a timestamp for each sale. Our goal is to group these sales by the day of the week.

import pandas as pd

# Sample sales data
data = {'timestamp': ['2023-04-01 09:00:00', '2023-04-02 10:00:00', '2023-04-03 11:00:00', '2023-04-04 12:00:00', '2023-04-05 13:00:00', '2023-04-06 14:00:00', '2023-04-07 15:00:00'],
        'sales': [100, 150, 200, 250, 300, 350, 400]}
sales_df = pd.DataFrame(data)

# Convert timestamp to datetime
sales_df['timestamp'] = pd.to_datetime(sales_df['timestamp'])

# Extract day of week and group by it
day_of_week = sales_df['timestamp'].dt.dayofweek
sales_by_day = sales_df.groupby(day_of_week).sum()

# Print grouped data
print(sales_by_day)

In this basic example, days are represented by numbers (Monday=0, Sunday=6). So, our output would group sales based on those numbers, totaling sales for each day of the week.

Making It More Readable

While the first example gives us the result, interpreting the day of the week by numbers isn’t very intuitive. Let’s improve this by mapping the numeric representation to actual day names.

day_names = {0: 'Monday', 1: 'Tuesday', 2: 'Wednesday', 3: 'Thursday', 4: 'Friday', 5: 'Saturday', 6: 'Sunday'}
sales_by_day.index = sales_by_day.index.map(day_names)

# Enhanced printout
print(sales_by_day)

Now, the output is more readable, with sales grouped under each day of the week by name.

Advanced Analysis: Adding More Complexity

Let’s take our analysis a step further. What if we wanted to not only group data by the day of the week but also analyze other factors, such as promotions on different days? To do this, we can adjust our grouping to consider multiple columns.

data.update({'promotion': ['Yes', 'No', 'Yes', 'No', 'No', 'Yes', 'No']})
sales_df = pd.DataFrame(data)

# Group by day of the week and promotion status
complex_group = sales_df.groupby([sales_df['timestamp'].dt.day_name(), 'promotion']).sum()

# Display the enhanced grouping
print(complex_group)

In this advanced example, we are grouping by both the day of the week and the presence of a promotion, offering a more nuanced view of the sales data.

Visualizing Weekly Patterns

To fully leverage the insights from our grouped data, visualizing it can be extremely helpful. Pandas integrates well with plotting libraries like matplotlib to facilitate this. Here’s how you can visualize the total sales per day of the week.

import matplotlib.pyplot as plt

# Convert grouped data to a suitable format for plotting
sales_by_day_plot = sales_by_day.reset_index()
sales_by_day_plot.columns = ['Day of the Week', 'Total Sales']

# Plotting
plt.figure(figsize=(10, 6))
plt.bar(sales_by_day_plot['Day of the Week'], sales_by_day_plot['Total Sales'])
plt.title('Sales by Day of the Week')
plt.xlabel('Day of the Week')
plt.ylabel('Total Sales')
plt.xticks(rotation=45)
plt.show()

This plot provides a visual representation of sales trends throughout the week, making it easier to spot patterns and analyze the impact of weekdays on sales.

Conclusion

Grouping DataFrame rows by the day of the week using Pandas can unveil valuable insights into time-based patterns and trends in your data. Starting from simple grouping to more advanced analyses that incorporate multiple factors, Pandas equips you with the tools to perform robust time series analysis. With the ability to visualize these groupings, you can easily communicate insights and make informed decisions.