Reshaping the result of a pd.DataFrame.aggregate

user_754176 0
Viewed 133

I have a df with 2 numeric columns

DATA_ROWS = 5
df = pd.DataFrame({"id":[1]*DATA_ROWS,"x":[1,2,3,4,5],
                        "z":[1,1,1,5,6]})
df.set_index("id", drop=True, append=True, inplace=True)

          x  z
     id      
   0   1  1  1
   1   1  2  1
   2   1  3  1
   3   1  4  5
   4   1  5  6

(id is an index)

Also, I have a list of functions

funcs = [np.max, np.min, np.std, func1, func2]

So, when I aggregate, I got

df.aggregate(funcs)

                                                           x         z
amax                                                5.000000  6.000000
amin                                                1.000000  1.000000
std                                                 1.581139  2.489980
func1                                               7.000000  1.000000
func2                                              23.500000  6.200000

I will like to get instead of this the following

   x_amax, x_amin  x_std  x_func1 x_func2 z_amax z_amin z_std z_func1 z_func2
1  5.000   1.000 1.5811    7.000  23.500  6.000  1.000 2.4899 1.000  6.2000

I read the documentaion about pivot, melt etc and I can't get how to do this, Any ideas?

pandas python
2 Answers

Use unstack for reshape, to_frame for one column df and then transpose by T. Last create columns by flattening MultiIndex by map and join:

#select first value of level id
id1 = df_fdw.index.get_level_values('id')[0]

df = df.unstack().to_frame(id1).T
df.columns = df.columns.map('_'.join)
print (df)
   x_amax  x_amin     x_std  x_func1  x_func2  z_amax  z_amin    z_std  \
1     5.0     1.0  1.581139      3.0     15.0     6.0     1.0  2.48998   

   z_func1  z_func2  
1      2.8     14.0

Solution for multiple id (working nice for unique id too):

df = pd.DataFrame({"id":[1]*2 + [2]*3,"x":[1,2,3,4,5],
                        "z":[1,1,1,5,6]})
df.set_index("id", drop=True, append=True, inplace=True)

#sample functions
def func1(x):
    return x.mean()

def func2(x):
    return x.sum()


funcs = [np.max, np.min, np.std, func1, func2]

df = df.groupby(level='id').aggregate(funcs)
df.columns = df.columns.map('_'.join)
print (df)
    x_amax  x_amin     x_std  x_func1  x_func2  z_amax  z_amin     z_std  \
id                                                                         
1        2       1  0.707107      1.5        3       1       1  0.000000   
2        5       3  1.000000      4.0       12       6       1  2.645751   

    z_func1  z_func2  
id                    
1         1        2  
2         4       12
user_29010020

I think you should be using groupby here, if you want a row per id. 

df1 = df.groupby('id').agg([np.max, np.min, np.std, 'first', 'last'])
df1.columns =['_'.join(c) for c in df1.columns.values]

df1

    x_amax  x_amin     x_std  x_first  x_last  z_amax  z_amin    z_std  \
id                                                                       
1        5       1  1.581139        1       5       6       1  2.48998   

    z_first  z_last  
id                   
1         1       6

Note that you can pass string names of all the basic aggregation functions (mean/max/min/std/etc) to agg, so this works too:

aggfuncs = ['max', 'min', 'std', func1, func2]
df1 = df.groupby('id').agg(aggfuncs)
user_49090870
Related