Q#85: Retail revenue trends
Given the table below, called ‘orders’, write code to show the average revenue by month by channel. The format of the result should look like the following:
- Month | Channel | Avg. Revenue
TRY IT YOURSELF
ANSWER
At this point, we should be pretty familiar with the approach, lets utilize pandas and groupby to solve!
Step 1: Creating the DataFrame
We have already created the ‘orders’ DataFrame using the provided code. The DataFrame looks like this:
import pandas as pd
raw_data = {
'order_id': [1, 2, 3, 4, 5, 6],
'channel': ['Online', 'Online', 'In_Store', 'In_Store', 'Online', 'Online'],
'date': ['2018-09-01', '2018-09-03', '2018-10-11', '2018-08-21', '2018-08-13', '2018-10-29'],
'month': ['09', '09', '10', '08', '08', '10'],
'revenue': [100, 125, 200, 80, 200, 100]
}
df = pd.DataFrame(raw_data, columns=['order_id', 'channel', 'date', 'month', 'revenue'])Step 2: Calculating the Average Revenue by Month and Channel
To calculate the average revenue by month and channel, we will group the data by the ‘month’ and ‘channel’ columns and then apply the mean function to calculate the average revenue for each group.
average_revenue_by_month_channel = df.groupby(['month', 'channel'])['revenue'].mean().reset_index()
print(average_revenue_by_month_channel)Plug: Checkout all my digital products on Gumroad here. Please purchase ONLY if you have the means to do so. Use code: MEDSUB to get a 10% discount!
