CODEWITHSUNDEEP
pythonpandas
SCool
SCool

Reputation: 3375

How to get average number of transactions within groupby groups?

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}}

Upvotes: 0

Views: 753

Answers (1)

jezrael
jezrael

Reputation: 862661

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()

Upvotes: 2

Related Questions