Python - Pandas grouping a column for all other columns and statistical measures as new columns

user_3123780 0
Viewed 57

I have a Pandas dataframe as shown below:

Voice_Usage | Data_Usage | Revenue | Age | Segment
--------------------------------------------------
300         | 20         | 400     | 35  | 1
700         | 10         | 300     | 40  | 1
100         | 15         | 200     | 32  | 3
150         | 30         | 100     | 20  | 2
450         | 12         | 450     | 54  | 1
900         | 18         | 800     | 17  | 3
...           ...          ...       ...  ...

I want to derive a dataframe from the above dataframe where each Segment type will have all the variables in data frame and their statistical measures (min, max, mean).

The derived dataframe should be like:

Segment | Variables | Min | Max | Mean |
----------------------------------------
1       Voice_Usage  5     100   50    
1       Data_Usage   0     50    30
1       Revenue      50    1500  300
1       Age          10    80    35
2       Voice_Usage  10    200   70    
2       Data_Usage   10    90    50
2       Revenue      30    500   200
2       Age          15    60    25
3       Voice_Usage  5     100   500    
3       Data_Usage   0     50    30
3       Revenue      50    1500  300
3       Age          10    80    35

...and so on.

How can I derive the second dataframe from the first one? I grouped by segment value with aggregating over other variables but that did not work. I need to make it generic for n no. of variables of the dataframe.

pandas python
1 Answers

Use melt with DataFrameGroupBy.agg:

df = (df.melt('Segment', var_name='a')
        .groupby(['Segment','a'])['value']
        .agg(['min','max','mean'])
        .reset_index())
print (df)
    Segment            a  min  max        mean
0         1          Age   35   54   43.000000
1         1   Data_Usage   10   20   14.000000
2         1      Revenue  300  450  383.333333
3         1  Voice_Usage  300  700  483.333333
4         2          Age   20   20   20.000000
5         2   Data_Usage   30   30   30.000000
6         2      Revenue  100  100  100.000000
7         2  Voice_Usage  150  150  150.000000
8         3          Age   17   32   24.500000
9         3   Data_Usage   15   18   16.500000
10        3      Revenue  200  800  500.000000
11        3  Voice_Usage  100  900  500.000000

If want multiple statistics use DataFrameGroupBy.describe:

df = (df.melt('Segment', var_name='a')
        .groupby(['Segment','a'])['value']
        .describe()
        .reset_index())
print (df)
    Segment            a  count        mean         std    min     25%    50%  \
0         1          Age    3.0   43.000000    9.848858   35.0   37.50   40.0   
1         1   Data_Usage    3.0   14.000000    5.291503   10.0   11.00   12.0   
2         1      Revenue    3.0  383.333333   76.376262  300.0  350.00  400.0   
3         1  Voice_Usage    3.0  483.333333  202.072594  300.0  375.00  450.0   
4         2          Age    1.0   20.000000         NaN   20.0   20.00   20.0   
5         2   Data_Usage    1.0   30.000000         NaN   30.0   30.00   30.0   
6         2      Revenue    1.0  100.000000         NaN  100.0  100.00  100.0   
7         2  Voice_Usage    1.0  150.000000         NaN  150.0  150.00  150.0   
8         3          Age    2.0   24.500000   10.606602   17.0   20.75   24.5   
9         3   Data_Usage    2.0   16.500000    2.121320   15.0   15.75   16.5   
10        3      Revenue    2.0  500.000000  424.264069  200.0  350.00  500.0   
11        3  Voice_Usage    2.0  500.000000  565.685425  100.0  300.00  500.0   

       75%    max  
0    47.00   54.0  
1    16.00   20.0  
2   425.00  450.0  
3   575.00  700.0  
4    20.00   20.0  
5    30.00   30.0  
6   100.00  100.0  
7   150.00  150.0  
8    28.25   32.0  
9    17.25   18.0  
10  650.00  800.0  
11  700.00  900.0
user_29010020
Related