Applying Pandas Data Aggregation in Sales Data Analysis.🛍️

Image created via ChatGPT

Data aggregation in pandas represents a fundamental analytical technique for summarizing large and complex datasets by organizing observations according to one or more grouping variables and applying statistical operations. Within the pandas ecosystem, this process is commonly achieved through the groupby() function combined with aggregation methods such as agg(), which enables analysts to derive structured summaries from granular data.

Mastering data aggregation is therefore a core competency in real-world data analysis, with wide-ranging applications across business analytics, finance, healthcare, e-commerce and scientific research.

This content focuses on applying data aggregation techniques to online sales data, demonstrating how pandas facilitates scalable and efficient analysis of datasets. Through this real-world scenario, readers will gain a conceptual understanding of how aggregation transforms raw sales data into actionable business insights that drive strategic and operational decisions.

The Role of Data Aggregation in Exploratory Analysis

Before starting sales data analysis, let’s quickly examine why data aggregation is important in exploratory analysis. Data aggregation refers to the systematic process of combining and summarizing individual data records into meaningful groups based on shared attributes. In exploratory and descriptive analytics, aggregation plays a critical role by reducing data complexity while maintaining essential patterns and relationships. Aggregated data helps identify trends, compare performance across dimensions, and generate interpretable summaries. It also facilitates effective data visualization by simplifying large datasets.

In the context of online sales data, common aggregation metrics are designed to capture both business performance and customer behaviour. These metrics typically include total sales revenue, number of transactions, average order value, and quantity sold, which collectively provide insight into overall sales effectiveness. Temporal aggregates, such as daily, weekly, and monthly totals, enable the examination of trends and seasonality, while category-level or customer-level aggregates support comparative and segmentation analyses. Together, these aggregated measures form the foundation for data-driven evaluation and strategic decision-making in e-commerce analytics.

Aggregation with Online Sales Data

1. Aggregation by Business Dimensions

Assume you have an online store’s sales data, which has columns like ‘Date’, ‘Product’, ‘Category’, ‘Sales’, and ‘Quantity’. The goal is to aggregate by category to find total sales, average sales, total quantity sold, and the number of entries per category.

# Perform aggregation: Group by 'Category' and apply multiple functions
aggregated = df.groupby('Category').agg({
    'Sales': ['sum', 'mean'],
    'Quantity': ['sum', 'count']
})

# Display the result
print(aggregated)

The .groupby(‘Category’) function splits the data into unique categories, while the .agg() function applies ‘sum’ and ‘mean’ to ‘Sales’, and ‘sum’ and ‘count’ to ‘Quantity’. This aggregation method quickly highlights category performance without manual calculation.

2. Time Series Aggregation for Sales Analysis

Applying the .resample() function to time series data allows for summarizing data at different frequencies, such as Quarterly, Monthly, or Weekly, for reporting or trend analysis. This function enables grouping data by time periods and applying aggregation functions.

Assume you have sales data to apply the pandas aggregation functions

A. Monthly Total Sales, Average Sales, Total Quantity

# Monthly total sales in revenue, average sales, and total quantity in sales. 
monthly = df.resample('ME').agg({  #<--- Use 'ME' for month-end
    'Sales': ['sum', 'mean'],
    'Quantity': 'sum'
})
print(monthly)

B. Quarterly Total Sales And Total Quantity

# Quarterly total sales in revenue and total quantity in sales
quarterly = df.resample('QE').sum()  #<--- Quarter-end sum
print(quarterly)

C. Weekly Average Sales

# To see weekly average sales, use the 'Sales' column and apply .resample('W') combined with a statistical function .mean()
weekly_mean_sales = df['Sales'].resample('W').mean()  # Week-ending Sunday
print(weekly_mean_sales.head(10))

3. Aggregated Data Visualization: Quarterly, Monthly, Weekly

Now, let’s visualize resampled time series data to identify trends, seasonality, or anomalies quickly. To do it, import matplotlib for visualization.

A. Plot Monthly Aggregated Data By a Line Chart

This chart visualizes the total monthly sales and average daily sales over the year, making it ideal for observing seasonal peaks. The line chart displays two lines: the upper line represents total monthly sales, while the lower line represents average daily sales. It shows a peak in October as the standout month in sales.

import matplotlib.pyplot as plt

# Plot: Total and Average Monthly Sales
monthly['Sales'].plot(kind='line', marker='o', figsize=(8, 6))
plt.title('Monthly Sales Trends (2023)')
plt.xlabel('Month')
plt.ylabel('Sales ($)')
plt.legend(['Total Sales', 'Average Daily Sales'])
plt.grid(True)
plt.show()

B. Plot Quarterly Total Sales By a Bar Chart

Bar charts are perfect for comparing discrete periods like quarters. The bar graphs below show the totals for Q1 — Q4, with Q4 towering the highest. This highlights the end of year performance, making it ideal for business reports.

# Plot as bar chart
quarterly['Sales'].plot(kind='bar', color='skyblue', figsize=(8, 5))
plt.title('Quarterly Total Sales (2023)')
plt.xlabel('Quarter')
plt.ylabel('Total Sales ($)')
plt.xticks(rotation=45)
plt.grid(axis='y')
plt.show()

C. Plot Weekly Average Sales By a Line Chart

This is ideal for focusing on finer details and visualizing the weekly average to uncover short-term fluctuations.

# Plot with rolling average for trend line
weekly_mean_sales.plot(kind='line', label='Weekly Average', figsize=(12, 6))
weekly_mean_sales.rolling(window=4).mean().plot(label='4-Week Rolling Avg', linestyle='--', color='red')
plt.title('Weekly Average Daily Sales (2023)')
plt.xlabel('Week')
plt.ylabel('Average Sales ($)')
plt.legend()
plt.grid(True)
plt.show()

Takeaways

Data aggregation is a foundational analytical method for transforming detailed transactional data into structured summaries that support exploratory analysis and effective visualization. It helps identify trends, recurring patterns, and potential anomalies that may not be apparent at the individual record level. Furthermore, data aggregation facilitates scalable sales analysis and supports evidence-based decision-making by providing interpretable metrics that inform strategic planning, performance evaluation, and operational optimization within the analysis workflow.

👉 For more learning resources: visit here

🔗 Connect with me:

👩‍💻 LinkedIn | Medium | Instagram | Substack | Blogger