pandas aggregate multiple columns during pivot_table

Question

Have a dataframe like this:

df = pd.DataFrame((['1990-01-01','A','S1','2','string2','string3'],
 ['1990-01-01','A','S2','1','string1','string4'],
 ['1990-01-01','A','S3','1','string5','string6']),columns= 
 ["date","type","status","count","s1","s2")


           date type status count       s1       s2
 0  1990-01-01    A     S1     2  string2  string3
 1  1990-01-01    A     S2     1  string1  string4
 2  1990-01-01    A     S3     1  string5  string6
 ...

I want to get bellow result (each date and each type should have single row, and get the min of s1 column, get max of s2 column)

date             type       S1    S2   S3    min_s1        max_s2
1990-01-01       A           2     1   1     string1      string6

I tried to use pivot_table

df.pivot_table(index=['date','type'],columns=['status'],values=['count','s1','s2'], aggfunc={
'count':np.sum, 
's1': np.min,
's2': np.max
})

But this would only get bellow result, which leads to multiple columns and not the final result.

                count             s1                         s2
status             S1 S2 S3       S1       S2       S3       S1       S2       S3
date       type
1990-01-01 A        2  1  1  string2  string1  string5  string3  string4  string6

Anyone idea? Thanks.

please check your input data again, the example dataframe is not what you show as output. — Rabinzel
– Rabinzel, Commented Aug 31, 2022 at 12:17
How do you define min_s1 and max_s2? What is logic to compute these values, since they seem to be str data? — itprorh66
– itprorh66, Commented Aug 31, 2022 at 12:19
string is comparable, so use the default logic. like 'a' < 'b' , '1'<''2' — Niuya
– Niuya, Commented Aug 31, 2022 at 12:21

mozway · Accepted Answer · 2022-08-31 12:21:34Z

5

Looks like you want to combine a pivot and groupby.agg:

(df.pivot(index=['date','type'],columns='status', values='count')
   .join(df.groupby(['date', 'type']).agg({'s1': 'min', 's2': 'max'}))
   .reset_index()
)

output:

         date type S1 S2 S3       s1       s2
0  1990-01-01    A  2  1  1  string1  string6

answered Aug 31, 2022 at 12:21

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pandas aggregate multiple columns during pivot_table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related