Python pandas how to group by while having the keys stored all the way down

user_13385376 0
Viewed 29

I have a dataframe called 'data' that I want to group by and sum based off of multiple keys:

NAME     ORDER   COST  
 Joe    Burger     10
 Joe    Burger     12
 Jill    Fries      5
 Joe     Nachos     8

I run

data = data.groupby(['NAME','ORDER'])['COST'].sum()

and get this:

NAME     ORDER   COST  
 Joe    Burger     22
        Nachos     8
 Jill    Fries      5

but I am losing they key 'Joe' for the second row. I want the dataframe to keep all keys so that if Joe has multiple burger orders, it will be prefaced with Joe all the way down like so:

NAME     ORDER   COST  
 Joe    Burger     22
 Joe    Nachos     8
 Jill    Fries      5

Similar dataframe initialization: df = pd.DataFrame({'NAME': ['Joe', 'Jill', 'Joe', 'Joe'], 'ORDER': ['burger', 'fries', 'burger', ' Ube'], 'COST': [1, 2, 3, 6]})

pandas dataframe python
1 Answers

The output of a groupby aggregation is a dataframe with a MultiIndex including as many levels as groupers used (2 in this case: 'NAME' and 'ORDER')

Your new dataframe still has that 'Joe' in the second row, it is just not shown when you print. See here

data = data.groupby(['NAME', 'ORDER']).COST.sum()

>>> print(data)
NAME  ORDER
Jill  Fries      5
Joe   Burger    22
      Nachos     8
Name: COST, dtype: int64

>>> print(data.index)
MultiIndex([('Jill',  'Fries'),
            ( 'Joe', 'Burger'),
            ( 'Joe', 'Nachos')],
           names=['NAME', 'ORDER'])


>>> print(data.loc[('Joe', 'Nachos')])
8

And actually data is now a Series (not a DataFrame), since you selected only one column from the groupby object.

user_66928980
Related