I have a transaction dataframe with sales figures for McDonalds and KFC
month shop transaction_value 0 January McDonalds 5 1 January KFC 1 2 January KFC 34 3 January KFC 12 4 February McDonalds 23 5 February McDonalds 45 6 February KFC 23 7 February KFC 56 8 March McDonalds 45 9 March McDonalds 3 10 March KFC 2 11 March KFC 1 12 March KFC 1 I want to get the average count of transactions per month for each shop.
I have gotten this far, grouping by shop and month:
df.groupby([df.shop,df.month])['transaction_value'].count() shop month KFC February 2 January 3 March 3 McDonalds February 2 January 1 March 2 What I need is, what is the average count of transactions per month for McDonalds and KFC? I can look at the above and say McDonalds has 1.66 transactions per month on average, and KFC has 2.66 transactions per month.
But how can I calculate that info in pandas?
I have tried to get the mean of the groupby:
df.groupby([df.shop,df.month])['transaction_value'].count().mean() But that gets the mean of everything. It returns a single number.
I am trying to get to something like this:
shop average number of transactions per month KFC 2.66 McDonalds 1.66 It's probably something simple to add to the groupby but I can't figure it out.
My dataframe so you can use datafarme.from_dict():
{'month': {0: 'January', 1: 'January', 2: 'January', 3: 'January', 4: 'February', 5: 'February', 6: 'February', 7: 'February', 8: 'March', 9: 'March', 10: 'March', 11: 'March', 12: 'March'}, 'shop': {0: 'McDonalds', 1: 'KFC', 2: 'KFC', 3: 'KFC', 4: 'McDonalds', 5: 'McDonalds', 6: 'KFC', 7: 'KFC', 8: 'McDonalds', 9: 'McDonalds', 10: 'KFC', 11: 'KFC', 12: 'KFC'}, 'transaction_value': {0: 5, 1: 1, 2: 34, 3: 12, 4: 23, 5: 45, 6: 23, 7: 56, 8: 45, 9: 3, 10: 2, 11: 1, 12: 1}} 1 Answer
You are close, need mean per level=0:
df.groupby([df.shop,df.month])['transaction_value'].count().mean(level=0) What working same like:
df.groupby([df.shop,df.month])['transaction_value'].count().groupby(level=0).mean() 
