Find all months between two date columns and generate row for each month

user_10214220 0
Viewed 887

I have a dataframe which has two date columns:

   Date1       Date2
2018-10-02    2018-12-21
2019-01-20    2019-04-30

and so on

I want to create a third column which basically is a column containing all the months between the two dates, something like this:

   Date1       Date2           months
2018-10-02    2018-12-21       201810
2018-10-02    2018-12-21       201811
2018-10-02    2018-12-21       201812
2019-01-20    2019-04-30       201901
2019-01-20    2019-04-30       201902
2019-01-20    2019-04-30       201903
2019-01-20    2019-04-30       201904

How can i do this? I tried using this formula:

df['months']=df.apply(lambda x: pd.date_range(x.Date1,x.Date2, freq='MS').strftime("%Y%m"))

but i am not getting the desired result. Kindly help. Thanks

pandas numpy date python
3 Answers

Using merge

final = df.merge(df.apply(lambda s: pd.date_range(s.Date1, s.Date2, freq='30D'), 1)\
                   .explode()\
                   .rename('Months')\
                   .dt.strftime('%Y%m'),
                 left_index=True, 
                 right_index=True)

   Months       Date1       Date2
0  201810  2018-10-02  2018-12-21
0  201811  2018-10-02  2018-12-21
0  201812  2018-10-02  2018-12-21
1  201901  2019-01-20  2019-04-30
1  201902  2019-01-20  2019-04-30
1  201903  2019-01-20  2019-04-30
1  201904  2019-01-20  2019-04-30
user_25356110

Using groupby with pd.date_range and join.

Notice: I used replace(day=1), to be sure we catch every month.

months = df.groupby(level=0).apply(lambda x: pd.date_range(x['Date1'].iat[0].replace(day=1), 
                                                           x['Date2'].iat[0], 
                                                           freq='MS')).explode().to_frame(name='Months')

df2 = months.join(df).reset_index(drop=True)

Output

      Months      Date1      Date2
0 2018-10-01 2018-10-02 2018-12-21
1 2018-11-01 2018-10-02 2018-12-21
2 2018-12-01 2018-10-02 2018-12-21
3 2019-01-01 2019-01-20 2019-04-30
4 2019-02-01 2019-01-20 2019-04-30
5 2019-03-01 2019-01-20 2019-04-30
6 2019-04-01 2019-01-20 2019-04-30
user_90812670

You can melt the data, resample, and merge back:

df.merge(df.reset_index()                               # reset index as column
           .melt(id_vars='index', value_name='months')  # usual melt
           .resample('M', on='months')                  # resample to get the month list
           .first().ffill()                             # interpolate the index
           .drop(['variable', 'months'], axis=1)        # remove unnecessary columns
           .reset_index(),                              # make months a column
         left_index=True,
         right_on='index'
)

Output:

       Date1      Date2     months  index
0 2018-10-02 2018-12-21 2018-10-31    0.0
1 2018-10-02 2018-12-21 2018-11-30    0.0
2 2018-10-02 2018-12-21 2018-12-31    0.0
3 2019-01-20 2019-04-30 2019-01-31    1.0
4 2019-01-20 2019-04-30 2019-02-28    1.0
5 2019-01-20 2019-04-30 2019-03-31    1.0
6 2019-01-20 2019-04-30 2019-04-30    1.0
user_42384080
Related