Pandas: use one column for groupby and get stats for multiple other columns

user_8698142 0
Viewed 2398

I hv a data frame with 3 columns,

ID col1 col2 
A1 1 12
A1 3 10
A1 4 16
........
A9 9 18
A9 7 11
A9 8 15

I want to create a new data frame with columns: 

ID col1_min, col1_max, col2_min, col2_max.

A1 1         4         10        16
...........
A9 7         9         11        18

I can do this by using groupby 

col1_min = df.groupby(['ID'])['col1'].min()
col1_max = df.groupby(['ID'])['col1'].max()
col2_min = df.groupby(['ID'])['col2'].min()
col2_max = df.groupby(['ID'])['col2'].max()

df2 = pd.DataFrame({'col1_min':col1_min, 'col1_max':col1_max, 'col2_min':col2_min, 'col2_max':col2_max})

There must be a better and more elegant way (one liner) ?

Many thanks in advance.

pandas group-by
3 Answers
df.groupby('ID').agg(['min', 'max'])

   col1     col2    
    min max  min max
ID                  
A1    1   4   10  16
A9    7   9   11  18

Flatten the columns with

d = df.groupby('ID').agg(['min', 'max'])
d.columns = d.columns.map('_'.join)
d

    col1_min  col1_max  col2_min  col2_max
ID                                        
A1         1         4        10        16
A9         7         9        11        18

If your columns headers are numeric, you can use 

d = df.groupby('ID').agg(['min', 'max'])
d.columns = d.columns.map('{0[0]}_{0[1]}'.format)
d

    col1_min  col1_max  col2_min  col2_max
ID                                        
A1         1         4        10        16
A9         7         9        11        18

Finally, reset_index to get index back in dataframe proper.

d = df.groupby('ID').agg(['min', 'max'])
d.columns = d.columns.map('{0[0]}_{0[1]}'.format)
d.reset_index()

   ID  col1_min  col1_max  col2_min  col2_max
0  A1         1         4        10        16
1  A9         7         9        11        18
user_23366540

Use groupby and agg

df = df.groupby('ID').agg({'col1': ['min', 'max'], 'col2': ['min', 'max']}).reset_index()
df.columns = df.columns.map('_'.join)min', 'max']})

    ID_ col1_min    col1_max    col2_min    col2_max
0   A1  1           4           10          16
1   A9  7           9           11          18
user_62873080

I think PiR and Vai already provide nice answer , But if you would like something different , you can look at this ...

from functools import reduce
df1=df.groupby('ID')['col1','col2'].apply(lambda x: [x.min().values,x.max().values])
pd.DataFrame(data=[reduce(lambda x,y:  np.append(x,y),l) for l in df1],index=df1.index,columns=np.core.defchararray.add(np.repeat(['col1','col2'], 2),np.tile(['min','max'],2))) 


Out[1001]: 
    col1min  col1max  col2min  col2max
ID                                    
A1        1       10        4       16
A9        7       11        9       18
user_79645270
Related