Pandas combine two group by's, filter and merge the groups(counts)

user_8102175 0
Viewed 14966

I have a dataframe that I need to combine two different groupbys with one of them filtered.

 ID     EVENT      SUCCESS
 1       PUT          Y
 2       POST         Y
 2       PUT          N
 1       DELETE       Y

This table below is how I would like the data to look like. Firstly grouping the 'EVENT' counts, the second is to count the amount of Successes ('Y') per ID

ID  PUT   POST  DELETE SUCCESS
 1   1     0       1      2
 2   1     1       0      1

I've tried a few techniques and the closet I've found is two separate methods which yield the following

group_df = df.groupby(['ID', 'EVENT']) count_group_df = group_df.size().unstack()

Which yields the following for the 'EVENT' counts

ID  PUT   POST  DELETE
 1   1     0       1      
 2   1     1       0

For the Successes with filters, i dont know whether I can join this to the first set on 'ID'

 df_success = df.loc[df['SUCCESS'] == 'Y', ['ID', 'SUCCESS']]
 count_group_df_2 = df_success.groupby(['ID', 'SUCCESS'])


ID  SUCCESS
1      2
2      1

I need to combine these somehow?

Additionally I'd also like to merge the counts two of the 'EVENT''s for example PUT's and POST's into one column.

pandas-groupby pandas python
3 Answers

use a pivot_table and a dataframe filter

 df=pd.DataFrame([{"ID":1,
             "EVENT":"PUT",
             "SUCCESS":"Y"
            },
             {
                 "ID":2,
             "EVENT":"POST",
             "SUCCESS":"Y"
             }
             ,
             {
                 "ID":2,
             "EVENT":"PUT",
             "SUCCESS":"N"
             },
             {
                 "ID":1,
             "EVENT":"DELETE",
             "SUCCESS":"Y"
             }])
 filter=df['SUCCESS']=='Y'
 event= df[filter].groupby('ID')['EVENT'].size().reset_index()

 print(event)
 #df_t=df.T
 #print(df_t)

 event= df[filter].pivot_table(index='ID', columns='EVENT', values='SUCCESS', aggfunc='count',fill_value=0)
 print (event)
user_40011770
Related