Splitting a record into 12 months based on the date in pandas dataframe

Question

I have the data in the below format stored in a pandas dataframe

PolicyNumber    InceptionDate
    1         2017-12-28 00:00:00.0

https://i.stack.imgur.com/pEfLT.png

I want to split this single record into 12 records based on the inception date. For eg,

    1        2017-12-28 00:00:00.0
    1        2018-1-28 00:00:00.0
    1        2018-2-28 00:00:00.0
    1        2018-3-28 00:00:00.0
          .
          .
    1        2018-11-28 00:00:00.0

Is this possible?

Answer 1

You can use pd.date_range to generate a list of date range then explode the column

df['InceptionDate'] = pd.to_datetime(df['InceptionDate'])
df = (df.assign(InceptionDate=df['InceptionDate'].apply(lambda date: pd.date_range(start=date, periods=12, freq='MS')+pd.DateOffset(days=date.day-1)))
      .explode('InceptionDate'))

print(df)

   PolicyNumber InceptionDate
0             1    2018-01-28
0             1    2018-02-28
0             1    2018-03-28
0             1    2018-04-28
0             1    2018-05-28
0             1    2018-06-28
0             1    2018-07-28
0             1    2018-08-28
0             1    2018-09-28
0             1    2018-10-28
0             1    2018-11-28
0             1    2018-12-28

To convert it to your original format from datetime type

df['InceptionDate'] = df['InceptionDate'].dt.strftime('%Y-%m-%d %H:%M:%S.%f')

   PolicyNumber               InceptionDate
0             1  2018-01-28 00:00:00.000000
0             1  2018-02-28 00:00:00.000000
0             1  2018-03-28 00:00:00.000000
0             1  2018-04-28 00:00:00.000000
0             1  2018-05-28 00:00:00.000000
0             1  2018-06-28 00:00:00.000000
0             1  2018-07-28 00:00:00.000000
0             1  2018-08-28 00:00:00.000000
0             1  2018-09-28 00:00:00.000000
0             1  2018-10-28 00:00:00.000000
0             1  2018-11-28 00:00:00.000000
0             1  2018-12-28 00:00:00.000000