python - sort a column based on the result of groupby

Question

I have a dataset like this

df = pd.DataFrame({'time':['13:30', '9:20', '18:12', '19:00', '11:20', '13:30', '15:20', '17:12', '16:00', '8:20'],
               'item': ["coffee", "bread", "pizza", "rice", "soup", "coffee", "bread", "pizza", "rice", "soup"]})

this is my desired output

and this is my code

#split the hour part from the time string
df['hour'] = df.Time.apply(lambda x: int(x.split(':')[0]))

def time_period(hour):
    if hour >= 6 and hour < 11:
        return 'breakfast'
    elif hour >= 11 and hour < 15:
        return 'lunch'
    else:
        return 'dinner'
df['meal'] = df['hour'].apply(lambda x: time_period(x))

a = df.groupby(['meal','Item']).size()
l = []
for i in np.sort(a.index.get_level_values(level=0).unique().tolist()):
    l.append(a.loc[i].reset_index().rename(columns = {0:'count'}))
b = pd.concat(l,axis=1)
c = [i for i in a.index.get_level_values(level=0).unique().tolist()*2]
c = np.sort(c)
b.columns = [c,b.columns]
display(b.head(10))

I just want to sort the table based on breakfast count and I don't know how to do it.

question from:https://stackoverflow.com/questions/65951725/sort-a-column-based-on-the-result-of-groupby

Answer 1

A better code to achieve what you need is:

df = pd.DataFrame({'time':['13:30', '9:20', '18:12', '19:00', '11:20', '13:30', 
                  '15:20', '17:12', '16:00', '8:20'],
                   'item': ["coffee", "bread", "pizza", "rice", "soup", "coffee", "bread", "pizza", "rice", "soup"]})

df['hour'] = df.time.apply(lambda x: int(x.split(':')[0]))
df['meal'] = np.where((df.hour >= 6) & (df.hour < 11), 'breakfast',
                 np.where((df.hour>=11) & (df.hour < 15), 'lunch', 'dinner')) 

df = df.groupby(['meal', 
     'item']).size().rename('count').to_frame().reset_index().pivot(columns=['meal'])
df.columns = df.columns.swaplevel(0,1)
df.sort_index(axis=1, level=0, inplace=True)
df.sort_values(by=('breakfast', 'count'), inplace=True)
df

           breakfast        dinner        lunch   
           count   item  count   item count    item
0          1.0  bread    NaN    NaN   NaN     NaN
1          1.0   soup    NaN    NaN   NaN     NaN
2          NaN    NaN    1.0  bread   NaN     NaN
3          NaN    NaN    2.0  pizza   NaN     NaN
4          NaN    NaN    2.0   rice   NaN     NaN
5          NaN    NaN    NaN    NaN   2.0  coffee
6          NaN    NaN    NaN    NaN   1.0    soup