Padd missing dates in Pandas series

debugcn Published at Dev

proximacentauri

I have a Panas series object created after groupby and resample operations on a dataframe.

I would like to fill missing days between min and max dates with zero quantity for each item_type.

    data = {'date_time': ['2018-01-22 12:40:03', '2018-01-22 13:40:03', '2018-01-23 15:00:05', '2018-01-26 14:30:04'], 
     'quantity': [11, 21, 23, 12], 'item_type': ['543', '543', '842', '543']}
    df = pd.DataFrame(data, columns = ['date_time', 'quantity' , 'item_type']) 
    df.index = df['date_time']
    df.index = pd.to_datetime(df.index)
    min_date = df.index.min()
    max_date = df.index.max()
    grouped = df.groupby('item_type').resample('D')['quantity'].sum()
    print(grouped)

>> Message: item_type   date_time 
543  2018-01-22    32.0
     2018-01-23     NaN
     2018-01-24     NaN
     2018-01-25     NaN
     2018-01-26    12.0
842  2018-01-23    23.0
Name: quantity, dtype: float64

If I don't resampling by day I could get a groupby object that I could iterate over then create a dataframe from each group and get totals, but totals are not for the day:

grouped = df.groupby('item_type')
for item_type, group in grouped:
    df = group.groupby(['date_time']).sum()

How can I get total quantities for each item type per day, with missing days given a quantity value of zero as per desired output below?

item_type   date_time  quantity
543  2018-01-22    32.0
     2018-01-23    0
     2018-01-24    0
     2018-01-25    0
     2018-01-26    12.0
842  2018-01-22    0
     2018-01-23    23.0
     2018-01-24    0
     2018-01-25    0
     2018-01-26    0

cs95

I'm not sure how easy this would be to do during the groupby, but you sure can do this after grouping. Create a date range using pd.date_range and then reindex.

g = df.groupby('item_type').resample('D')['quantity'].sum()
dates = pd.date_range(
      g.index.levels[0].min(), g.index.levels[0].max()
) 
idx = pd.MultiIndex.from_product([g.index.levels[0], dates])

g.reindex(idx, fill_value=0)

543  2018-01-22    32
     2018-01-23     0
     2018-01-24     0
     2018-01-25     0
     2018-01-26    12
842  2018-01-22     0
     2018-01-23    23
     2018-01-24     0
     2018-01-25     0
     2018-01-26     0
Name: quantity, dtype: int64

Collected from the Internet

Please contact [email protected] to delete if infringement.