I want to add missing dates for a specific date range, but keep all columns. I found many posts using afreq()
, resample()
, reindex()
, but they seemed to be for Series and I couldn’t get them to work for my DataFrame.
Given a sample dataframe:
data = [{'id' : '123', 'product' : 'apple', 'color' : 'red', 'qty' : 10, 'week' : '2019-3-7'}, {'id' : '123', 'product' : 'apple', 'color' : 'blue', 'qty' : 20, 'week' : '2019-3-21'}, {'id' : '123', 'product' : 'orange', 'color' : 'orange', 'qty' : 8, 'week' : '2019-3-21'}] df = pd.DataFrame(data) color id product qty week 0 red 123 apple 10 2019-3-7 1 blue 123 apple 20 2019-3-21 2 orange 123 orange 8 2019-3-21
My goal is to return below; filling in qty as 0, but fill other columns. Of course, I have many other ids. I would like to be able to specify the start/end dates to fill; this example uses 3/7 to 3/21.
color id product qty week 0 red 123 apple 10 2019-3-7 1 blue 123 apple 20 2019-3-21 2 orange 123 orange 8 2019-3-21 3 red 123 apple 0 2019-3-14 4 red 123 apple 0 2019-3-21 5 blue 123 apple 0 2019-3-7 6 blue 123 apple 0 2019-3-14 7 orange 123 orange 0 2019-3-7 8 orange 123 orange 0 2019-3-14
How can I keep the remainder of my DataFrame intact?
Advertisement
Answer
In you case , you just need do with unstack
and stack
+ reindex
df.week=pd.to_datetime(df.week) s=pd.date_range(df.week.min(),df.week.max(),freq='7 D') df=df.set_index(['color','id','product','week']). qty.unstack().reindex(columns=s,fill_value=0).stack().reset_index() df color id product level_3 0 0 blue 123 apple 2019-03-14 0.0 1 blue 123 apple 2019-03-21 20.0 2 orange 123 orange 2019-03-14 0.0 3 orange 123 orange 2019-03-21 8.0 4 red 123 apple 2019-03-07 10.0 5 red 123 apple 2019-03-14 0.0