How I can apply groupby two times on pandas data frame?

user_8228723 0
Viewed 9258

I have pandas data frame with column 'year', 'month' and 'transaction id'. I want to get the transaction count of every month for every year. For ex my data is like:

year: {2015,2015,2015,2016,2016,2017}
month: {1,  1,   2,   2,   2,    1}
tid: {123,  343, 453, 675, 786, 332}

I want to get the output such that for every year I will get the number of transactions per month. For ex for year 2015 I will get the output:

month: [1,2]
count: [2,1]

I used groupby('year'). but after that how I can get the per month transaction count.

pandas group-by python
2 Answers

Another option for more complex tasks - suppose you want to group by "year" and a function applied to "tid" - e.g. a bucket categorization

def tidBucket(x):
   if x<300:             return "low"
   if (300<=x & x<700):  return "medium"
   if 700<=x:            return "high"

Then the above solution would not work. You could solve the problem by first grouping by year, then iterate over the contents of the groupby object with another groupby:

gb = df.groupby(by='year') #['tid'].size().reset_index(name='count')
for _,df1 in gb:
    df1.index = df1["tid"]
    df1 = df1.groupby(by=tidBucket)

Then aggregate as desired. Alternatively, you could create an additional "bucket" column

df["bucket"] = df["tid"].map(tidBucket)

and follow the @jezrael 's solution.

user_56236210
Related