Determine month given time range and expand to multiple rows

Question

Is there a way to extract month from a given time range, and apply it to prices based on that range? Maybe best to illustrate by example. I have a range of prices with an offer start and end dates:

d = {'Price': [12, 11, 14], 'Offer_From': ['2019-10-12', '2019-10-13', '2020-02-01'],'Offer_To': ['2019-12-31', '2019-10-31', '2020-05-31'],  }
df = pd.DataFrame(data=d)
df
   Price  Offer_From    Offer_To
0     12  2019-10-12  2019-12-31
1     11  2019-10-13  2019-10-31
2     14  2020-02-01  2020-05-31

What I want is a table similar to the below, where month names are extracted from the time range:

d2 = {'Price': [12,12,12,11,14,14,14,14], 'Month': ['2019-10', '2019-11', '2019-12', '2019-10', '2020-02', '2020-03', '2020-04', '2020-05']}
df2 = pd.DataFrame(data=d2)
df2
   Price    Month
0     12  2019-10
1     12  2019-11
2     12  2019-12
3     11  2019-10
4     14  2020-02
5     14  2020-03
6     14  2020-04
7     14  2020-05

@MartijnPieters Is this an appropriate duplicate?, the solution for this answers involves other steps ... — Dani Mesejo, Nov 21 '19 at 15:16
@MartijnPieters I reopen the question as the duplicate was not appropiate — Dani Mesejo, Nov 21 '19 at 15:27
This is perhaps a closer duplicate: https://stackoverflow.com/questions/42151886/expanding-pandas-data-frame-with-date-range-in-columns — Dani Mesejo, Nov 21 '19 at 15:32

score 1 · Accepted Answer · answered Nov 21 '19 at 15:42

Assuming both Offer_From and Offer_To are DateTime columns, you could use date_range + explode and then extract the month manually:

df['Month'] = df[['Offer_From', 'Offer_To']].apply(lambda x: pd.date_range(x[0], x[1], freq='M'), axis=1)
result = df.explode('Month').drop(['Offer_From', 'Offer_To'], axis=1)

result['Month'] = [f'{date.year}-{date.month:02d}' for date in result['Month']]

print(result)

Output

   Price    Month
0     12  2019-10
0     12  2019-11
0     12  2019-12
1     11  2019-10
2     14  2020-02
2     14  2020-03
2     14  2020-04
2     14  2020-05

Thanks @Daniel Mesejo, it worked fine with a caveat that the same month range (i.e. 2019/12/01 to 2019/12/15) will result in nan, I'll probably correct these by filtering and applying dt.month() — Polto, Nov 21 '19 at 16:51

Determine month given time range and expand to multiple rows

1 Answers1