Skip to main content
Practice

Analyze Monthly Sales by Product Using Pandas

How can you quickly analyze monthly sales volume, unit price, and revenue data for various products?

When analyzing data, there are times when you need to calculate various statistics for each month and product.

Using Pandas, you can automate such complex tasks.

This time, let's look at how to analyze unit price, sales volume, and revenue data by month and product.


When Monthly and Product Data Analysis is Needed​

For example, let's assume you have unit price and sales volume data for products A and B from January to March.

Example Data by Month and Product
import pandas as pd

# Create example data
data = {
    'Month': ['2023-01', '2023-01', '2023-02', '2023-02', '2023-03', '2023-03'],
    'Product': ['A', 'B', 'A', 'B', 'A', 'B'],
    'Unit Price': [20, 30, 22, 28, 21, 29],
    'Sales Volume': [100, 150, 120, 130, 110, 140],
}

df = pd.DataFrame(data)

# Calculate revenue
df['Revenue'] = df['Unit Price'] * df['Sales Volume']

print(df)

Running the above code will generate the following DataFrame:

Data by Month and Product
      Month  Product  Unit Price  Sales Volume  Revenue
0   2023-01        A          20           100     2000
1   2023-01        B          30           150     4500
2   2023-02        A          22           120     2640
3   2023-02        B          28           130     3640
4   2023-03        A          21           110     2310
5   2023-03        B          29           140     4060

Now, let's summarize the data by product and month.


Summarizing Data by Product/Month​

First, let's summarize the total sales volume and revenue by product.

To summarize grouped data, you can use the groupby function to group the data by month, and then use the agg function to calculate summary statistics for each group.

The 'agg' function is used to aggregate DataFrame data. agg('mean') calculates the mean of each group, and agg('sum') calculates the sum of each group.


Calculating Total Revenue by Product​

The total revenue by product can be calculated by grouping by Product using df.groupby('Product').

Summary of Total Sales Volume and Revenue by Product
import pandas as pd

data = {
    'Month': ['2023-01', '2023-01', '2023-02', '2023-02', '2023-03', '2023-03'],
    'Product': ['A', 'B', 'A', 'B', 'A', 'B'],
    'Unit Price': [20, 30, 22, 28, 21, 29],
    'Sales Volume': [100, 150, 120, 130, 110, 140],
}

df = pd.DataFrame(data)

# Calculate revenue
df['Revenue'] = df['Unit Price'] * df['Sales Volume']

# Summary of total sales volume and revenue by product
result = df.groupby('Product').agg({'Sales Volume': 'sum', 'Revenue': 'sum'})

print(result)

Running this code will output the following summary data:

Summary Data by Month and Product
        Sales Volume    Revenue
Product                        
A                330       6950
B                420      12200

Calculating Total Revenue by Month​

The total revenue by month can be calculated by grouping by Month using df.groupby('Month').

Summary of Total Sales Volume and Revenue by Month
...(snip)...
# Summary of total sales volume and revenue by month
result = df.groupby('Month').agg({'Sales Volume': 'sum', 'Revenue': 'sum'})

Running this code will output the following results:

Summary Data by Month
        Sales Volume    Revenue
Month                           
2023-01            250       6500
2023-02            250       6280
2023-03            250       6370

Restructuring Data with a Pivot Table​

Furthermore, you can restructure the data with a pivot table to compare data based on specific items. Here, let's compare the revenue by product for each month.

Pivot Table of Revenue by Month and Product
# Pivot table of revenue by month and product
pivot_revenue_table = df.pivot_table(values='Revenue', index='Month', columns='Product', aggfunc='sum', fill_value=0)
print(pivot_revenue_table)

The result will be displayed as follows:

Pivot Table of Revenue by Month and Product
Product       A      B
Month                    
2023-01    2000   4500
2023-02    2640   3640
2023-03    2310   4060

This allows you to easily compare the revenue of products A and B for each month at a glance.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.