Multiple rows of dates to turn into 2 columns in a df with a start date range and end date

Asked

Viewed 16 times

0

I have the following df:

time_series                    date   sales  q1  q2  q3
store_0025_item_85011885    2020-07-19  4.0 0.0 2.0 1.0
store_0025_item_85011885    2020-07-26  4.0 0.0 2.0 1.0
store_0025_item_85011885    2020-08-09  6.0 0.0 2.0 1.0
store_0025_item_85011885    2020-08-16  4.0 0.0 2.0 1.0
store_0053_item_85011885    2020-12-06  7.0 0.0 8.0 1.0
store_0053_item_85011885    2020-12-13  7.0 0.0 8.0 1.0
store_0053_item_85011885    2020-12-20  6.0 0.0 8.0 1.0
store_0053_item_85011885    2020-12-27  5.0 0.0 8.0 1.0

I tried using pivot_table with this code:

df_p = pd.pivot_table(df_m, values='q2', index=['time_series'],
                    columns=['date'], fill_value=0)

But, returns columns with dates as column names. What I want is for them to be the same as df below:

time_series                 start_date   end_date   quantity
store_0025_item_85011885    2020-07-19   2020-07-26  2.0
store_0025_item_85011885    2020-08-09   2020-08-16  2.0
store_0053_item_85011885    2020-12-06   2020-12-27  8.0

See, that 'time_series' = store_0025_item_85011885, we have 2 consecutive weeks intervals, so we need 2 lines, but 'time_series' = store_0053_item_85011885, we have only 1 consecutive interval, so we need 1 line. In the 'Quantity' column the amount I need is to copy the 'Q2' column. How can I do this?

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.