CODEWITHSUNDEEP
pythonpandasgrouping
armara
armara

Reputation: 557

resample and groupby, mean and sum

this is my input dataframe

d1 = pd.Timestamp(2020,8,1)
d2 = pd.Timestamp(2020,9,1)
somedict = {'a':[2,3,2,3,4], 'b':['one','two','two','two','one'], 'c':['yes','no','yes','yes','no'] ,'d':[d1,d1,d2,d1,d2], 'e':[8,7,7,6,9]}
df = pd.DataFrame(somedict)
df.set_index('d',inplace=True)

i would like to group it by column b using df.groupby('b'), and resample by month using df.resample('M'). after grouping and resampling, i would like to see two things

  1. how many instances of yes we have in column c
  2. take the sum of column a, divided by the sum of column e

so the resulting output should be grouped by the categories one, two from column b, and the sums/number of instances should be shown per month

i've tried to run (df['c'] == 'yes').resample('M').mean() which almost answers question 1, but since b is no longer a part of the result, i can't write groupby('b').

Upvotes: 0

Views: 792

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150745

Try with pd.Grouper:

groups = df.groupby(['b',pd.Grouper(freq='M')])

out = pd.DataFrame({'count_c': groups['c'].apply(lambda x: x.eq('yes').sum()),
                    'a/e': groups['a'].sum()/groups['e'].sum()
                   })

Output:

                count_c       a/e
b   d                            
one 2020-08-31        1  0.250000
    2020-09-30        0  0.444444
two 2020-08-31        1  0.461538
    2020-09-30        1  0.285714

Alternatively, you could bypass the apply function and use a pipe instead (with a slight speed increase):

(df.assign(c=lambda df: df['c'].map({"yes":1,"no":0}))
   .groupby(['b', pd.Grouper(freq='M')])
   .pipe(lambda df: pd.DataFrame({"count_c": df['c'].sum(), 
                                  "a/e": df['a'].sum()/df['e'].sum()}))
 )
 
                count_c       a/e
b   d                            
one 2020-08-31        1  0.250000
    2020-09-30        0  0.444444
two 2020-08-31        1  0.461538
    2020-09-30        1  0.285714

Upvotes: 2

Related Questions