Search code examples
pandasdataframepivot

Pandas to summarize (cum and count) columns

Dataframe as below and I want to summarize the columns, like:

enter image description here

enter image description here

I tried pivot_table:

import pandas as pd
from io import StringIO

csvfile = StringIO(
"""Staff    Color   Sales   Spend   Transport
Amelia  Red 188 49  35
Elijah  Yellow  326 18  
James   Blue    378 10  
Benjamin    Red 144 34  45
Isabella    Red 269 10  
Lucas   Yellow  159 48  
Mason   Blue    496 48  20""")

df = pd.read_csv(csvfile, sep = '\t', engine='python')

df = df.pivot_table(index= None, \
                            values=['Sales', "Spend", "Transport"], \
                            aggfunc={'Sales':'sum','Spend':'sum','Transport':'count'})

print (df)

But the error says:

ValueError: No group keys passed!

And I also tried:

columns = ['sum of Sales','sum of Spend','count of Transport']
df_1 = pd.DataFrame(columns = columns)
df_1 = df_1.append({"sum of Sales":df.Sales.sum(),
                    "sum of Spend":df.Spend.sum(),
                    "count of Transport":df.Transport.count()}, ignore_index=True)

print (df_1)

enter image description here

It looks OK, but is there a better way to achieve it?

Solution

  • In your case just do

    df.agg({'Sales':'sum','Spend':'sum','Transport':'count'}).to_frame().T