Group by for standard deviation pandas

user_14576516 0
Viewed 105

I have a dataset which resembles something like this:

ID AMT TAG
1 100  A
2 120  B
1 25   B
1 110  A

I have to calculate the standard deviation of each tag for each ID,something like this:

ID A_STD B_STD
1  5     0
2  0     0

I am trying something like this:

out=df[df['TAG']=='A']
out=out.groupby('ACCT_NB').agg(
    A_STD=('TXN_AMT',lambda x:np.std(x))
)

For each TAG I am doing this. Is there a way to possibly minimise this code?

pandas numpy python
1 Answers

Yes, use DataFrame.pivot_table with DataFrame.reindex and DataFrame.add_suffix:

#default ddof=0 in np.std
out=(out.pivot_table(index='ID', 
                    columns='TAG', 
                    values='AMT',
                    aggfunc=np.std,
                    fill_value=0)
       .reindex(index=df['ID'].unique(), columns=df['TAG'].unique(), fill_value=0)
       .add_suffix('_STD'))
print (out)
TAG     A_STD  B_STD
ID                  
1    7.071068    0.0
2    0.000000    0.0

#default ddof=1 in pandas std
out=(out.pivot_table(index='ID', 
                    columns='TAG', 
                    values='AMT',
                    aggfunc='std',
                    fill_value=0)
       .reindex(index=df['ID'].unique(), columns=df['TAG'].unique(), fill_value=0)
       .add_suffix('_STD'))
user_29010020
Related