python - group by two columns based on created column

Question

I have a data set 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]})

I want to split the time into 3 meal times breakfast, lunch, dinner. and add it to data

I did it like this

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))

now I want to groupby the data based on these 3 meals and have an output like this:

question from:https://stackoverflow.com/questions/65908850/group-by-two-columns-based-on-created-column

Answer 1

df['time'] = df['time'].replace(r'[:]','.',regex=True).astype(float)
df['meal'] = pd.cut(df['time'],bins = [6,11,15,24],labels = ['breakfast','lunch','dinner'])
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]