I am working on dataframe like this in Python Pandas:
import pandas as pd
df = pd.DataFrame({'date_time':['2019-03-29 16:04','2019-03-20 20:31' , '2019-03-26 21:02' , '2019-03-26 21:05' , '2019-03-29 16:04','2019-04-01 19:40' , '2019-04-01 19:41'],
'item':['bed', 'lamp', 'candle', 'chair', 'bed', 'candle', 'lamp'],
'location' :['home', 'home', 'home', 'home', 'home', 'home', 'home' ],
'status' :['new', 'used', 'used', 'new', 'new', 'used', 'new' ]})
df
and I am trying to create a summary like this dataframe:
# Expected output
# The number of records for each date_time and item, sorted by date_time
# date_time item count
# 2019-03-20 20:31 lamp 1
# 2019-03-26 21:02 candle 1
# 2019-03-26 21:05 chair 1
# 2019-03-29 16:04 bed 2
# 2019-04-01 19:40 candle 1
# 2019-04-01 19:41 lamp 1
I tried different aggregation methods but I could not create this dataframe.