How can I summarize the following pandas data frame (df1) to obtain (df2)

Question

I am looking to summarize the following table, df1, pandas data frame:

  name subname  value1  value2  value3
0    a       b       1       1       1
1    a       b       2       2       2
2    a       b       3       3       3
3    a       b       4       4       4
4    a       b       5       5       5

to obtain in df2 format:

  name subname    type  mean  sd
0    a       b  value1     3   3
1    a       b  value2     3   3
2    a       b  value3     3   3

Reproducible input:

df1 = pd.DataFrame({'name': ['a','a','a','a','a'],
                    'subname': ['b','b','b','b','b'],
                    'value1': [1,2,3,4,5],
                    'value2': [1,2,3,4,5],
                    'value3': [1,2,3,4,5]})

df2 = pd.DataFrame({'name': ['a','a','a'],
                    'subname': ['b','b','b'],
                    'type': ['value1','value2','value3'],
                    'mean': [3,3,3],
                    'sd': [3,3,3]})

Can you clarify what is `sd`? Did you mean `std`? – mozway Aug 30 '23 at 08:55 — mozway, Aug 30 '23 at 08:55

score 2 · Answer 1 · answered Aug 30 '23 at 08:58

Use GroupBy.agg with DataFrame.stack by first level:

out = (df1.groupby(['name','subname'])
          .agg(['mean','std'])
          .rename_axis(['type', None], axis=1)
          .stack(0)
          .reset_index()
         )
print (out)
  name subname    type  mean       std
0    a       b  value1   3.0  1.581139
1    a       b  value2   3.0  1.581139
2    a       b  value3   3.0  1.581139

Another solution with DataFrame.pivot_table and stack:

out = (df1.pivot_table(index=['name','subname'],  aggfunc=['mean','std'])
          .rename_axis([None,'type'], axis=1)
          .stack()
          .reset_index())
print (out)
  name subname    type  mean       std
0    a       b  value1     3  1.581139
1    a       b  value2     3  1.581139
2    a       b  value3     3  1.581139

mozway · Answer 2 · 2023-08-30T08:59:52.097

You can melt and groupby.agg:

out = (df1.melt(['name', 'subname'], var_name='type')
          .groupby(['name', 'subname', 'type'], as_index=False)['value']
          .agg(['mean', 'std'])
       )

Alternatively, with a pivot_table and stack:

out = (df1.pivot_table(index=['name', 'subname'], aggfunc=['mean', 'std'])
          .rename_axis(columns=[None, 'type']).stack().reset_index()
      )

Output:

  name subname    type  mean       std
0    a       b  value1   3.0  1.581139
1    a       b  value2   3.0  1.581139
2    a       b  value3   3.0  1.581139

How can I summarize the following pandas data frame (df1) to obtain (df2)

2 Answers2