Let's say we have a pandas dataframe like the one below.
> category level score > Bus travel 0.75 > Bus travel 0.60 > Bus vehicles 0.50 What I want is to group by the 'level' and calculate the 'count' and the maximum score for each 'level'. Also the 'hard' part is to create an output like this:
> category travel score vehicles score > Bus 2 0.75 1 0.5 I have been trying doing this:
> grouped = df.groupby('level').agg( { 'category': 'count', 'score': 'max' }) Any ideas?
from StringIO import StringIO import pandas as pd text = """category level score Bus travel 0.75 Bus travel 0.60 Bus vehicles 0.50""" df = pd.read_csv(StringIO(text), delim_whitespace=1) print df category level score 0 Bus travel 0.75 1 Bus travel 0.60 2 Bus vehicles 0.50 gdf = df.groupby('category').apply( lambda df: df.groupby('level')['score'].agg({'count', 'max'})).unstack() gdf.columns = gdf.columns.swaplevel(0, 1) gdf = gdf.sort_index(axis=1) print gdf level travel vehicles count max count max category Bus 2 0.75 1 0.5 
