CODEWITHSUNDEEP
pythonpandas
agarg
agarg

Reputation: 418

python custom summary table

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
    ----------

Upvotes: 2

Views: 550

Answers (2)

Alex
Alex

Reputation: 1126

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

Upvotes: 2

jezrael
jezrael

Reputation: 862661

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

Upvotes: 5

Related Questions