python custom summary table

Question

I have a table which looks like the one below:

    A   B   C   D
    1   1   2   3
    1   1   3   3
    2   3   0   1
    2   4   2   3
    3   1   4   1
    3   0   2   4

And I need to generate a table something like the one below:

    A   Metric  Min Mean    Max
    1   B       1   1.0     1
        C       2   2.5     3
        D       3   3.0     3
    2   B       3   3.5     4
        C       0   1.0     2
        D       1   2.0     3
    3   B       0   0.5     1
        C       2   3.0     4
        D       1   2.5     4

So far I have come up with this.but doesn't look good. I'm sure there is a better way:

grouped = df.groupby(['A'])
for name, group in grouped:
    dt = dict()
    for str in ['max','mean','min']:
        dt.update({str:group.agg(str)})
    print(pd.DataFrame(data = dt))
    print('----------')

   max  mean  min
B    1   1.0    1
C    3   2.5    2
D    3   3.0    3
    ----------
    max  mean  min
B    4   3.5    3
C    2   1.0    0
D    3   2.0    1
    ----------
    max  mean  min
B    1   0.5    0
C    4   3.0    2
D    4   2.5    1
    ----------

jezrael · Accepted Answer · 2019-11-25 13:43:41Z

5

Use GroupBy.agg with reshape by DataFrame.stack with 0 for first level:

df = df.groupby('A').agg({'min', 'max', 'mean'}).stack(0)
print (df)
     max  mean  min
A                  
1 B    1   1.0    1
  C    3   2.5    2
  D    3   3.0    3
2 B    4   3.5    3
  C    2   1.0    0
  D    3   2.0    1
3 B    1   0.5    0
  C    4   3.0    2
  D    4   2.5    1

Another idea with DataFrameGroupBy.describe:

df = df.groupby('A').describe().stack(0)
print (df)
      25%  50%   75%  count  max  mean  min       std
A                                                    
1 B  1.00  1.0  1.00    2.0  1.0   1.0  1.0  0.000000
  C  2.25  2.5  2.75    2.0  3.0   2.5  2.0  0.707107
  D  3.00  3.0  3.00    2.0  3.0   3.0  3.0  0.000000
2 B  3.25  3.5  3.75    2.0  4.0   3.5  3.0  0.707107
  C  0.50  1.0  1.50    2.0  2.0   1.0  0.0  1.414214
  D  1.50  2.0  2.50    2.0  3.0   2.0  1.0  1.414214
3 B  0.25  0.5  0.75    2.0  1.0   0.5  0.0  0.707107
  C  2.50  3.0  3.50    2.0  4.0   3.0  2.0  1.414214
  D  1.75  2.5  3.25    2.0  4.0   2.5  1.0  2.121320

EDIT:

For custom function pass it to list of function - e.g. for count number of 0 per groups:

def func(x):
    return (x == 0).sum()

df = df.groupby('A').agg({'min', 'max', 'mean', func}).stack(0).rename_axis(('A', 'Metric'))
print (df)
A Metric                      
1 B          0    1   1.0    1
  C          0    3   2.5    2
  D          0    3   3.0    3
2 B          0    4   3.5    3
  C          1    2   1.0    0
  D          0    3   2.0    1
3 B          1    1   0.5    0
  C          0    4   3.0    2
  D          0    4   2.5    1

edited Nov 25, 2019 at 13:43

answered Nov 25, 2019 at 13:24

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

4 Comments

agarg Over a year ago

Is it possible to sneak in a lambda function as well?

agarg Over a year ago

both answers are fine,but why one call to agg sorts the columns by name, but other does not. Comparing the answer by Alexey below with the one above

jezrael Over a year ago

@agarg - I think reason for ordering is function stack - my answer first aggregate and then reshape. alexey first reshape and then aggregate, so ordering is not changed.

jezrael Over a year ago

@agarg - So for expected output with ordering is necessary df.groupby('A').agg(['min', 'max', 'mean']).stack(0).reindex(['min', 'max', 'mean'], axis=1)

Alexey · Accepted Answer · 2019-11-25 13:46:43Z

Preparing data

dd = {'A': {0: 1, 1: 1, 2: 2, 3: 2, 4: 3, 5: 3}, 'B': {0: 1, 1: 1, 2: 3, 3: 4, 4: 1, 5: 0}, 'C': {0: 2, 1: 3, 2: 0, 3: 2, 4: 4, 5: 2}, 'D': {0: 3, 1: 3, 2: 1, 3: 3, 4: 1, 5: 4}}
df = pd.DataFrame.from_dict(dd)    
df

Out[1]:
    A   B   C   D
0   1   1   2   3
1   1   1   3   3
2   2   3   0   1
3   2   4   2   3
4   3   1   4   1
5   3   0   2   4

Solution

We are using df.melt() for unpivot

df1 = df.melt(id_vars=['A'], var_name = 'Metric').groupby(['A','Metric']).agg(['min', 'mean', 'max'])
df1.columns = ['min','mean','max']
df1

Out[2]:
           min  mean    max
A   Metric          
1   B       1   1.0     1
    C       2   2.5     3
    D       3   3.0     3
2   B       3   3.5     4
    C       0   1.0     2
    D       1   2.0     3
3   B       0   0.5     1
    C       2   3.0     4
    D       1   2.5     4

Collectives™ on Stack Overflow

python custom summary table

2 Answers 2

4 Comments

Preparing data

Solution

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Preparing data

Solution

Comments

Your Answer

Sign up or log in

Post as a guest

Related