Q#58: 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

This question tests our ability to wrangle data specifically using Pandas.

Like other questions up to this point, we first need to read in the data, or in this case design the data from the information we are given. To do this, we can format the input into a dictionary and then call the pd.DataFrame() passing in the dictionary and optionally columns names.

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'])

Next, to get the information we want, we can use the .groupby() function with multiple arguments such that we groupby month and channel. Afterwards we can continue the chain of functions in pandas and select the column of interest, revenue, and then use an aggregate function, in this case .mean().

print(df.groupby(['month', 'channel']).revenue.mean())