python how to find the number of days in each month from Dec 2019 and forward between two date columns

Question

I have two date columns 'StartDate' and 'EndDate'. I want to find the number of days in each month between those two dates from Dec 2019 and forward ignoring any prior months of 2019 for calculation. StartDate and EndDate of each row can span across 2 years with overlapped months and Date columns can also be empty.

Sample Data:

df = {'Id': ['1','2','3','4','5','6','7', '8'],
      'Item': ['A','B','C','D','E','F','G', 'H'],
        'StartDate': ['2019-12-10', '2019-12-01', '2019-10-01', '2020-01-01', '2019-03-01','2019-03-01','2019-10-01', ''],
        'EndDate': ['2020-02-21' ,'2020-01-01','2020-08-31','2020-01-30','2019-12-31','2019-12-31','2020-08-31', '']
        }
df = pd.DataFrame(df,columns= ['Id', 'Item','StartDate','EndDate'])

Expected O/P:

The below solution works partially works.

df['StartDate'] = pd.to_datetime(df['StartDate'])
df['EndDate'] = pd.to_datetime(df['EndDate'])

def days_of_month(x):
    s = pd.date_range(*x, freq='D').to_series()
    return s.resample('M').count().rename(lambda x: x.month)

df1 = df[['StartDate', 'EndDate']].apply(days_of_month, axis=1).fillna(0)

df_final = df[['StartDate', 'EndDate']].join([df['StartDate'].dt.year.rename('Year'), df1])

Answer 1

Try this:

df.join(df.dropna(axis=0,how='any')
         .apply(lambda x: pd.date_range(x['StartDate'],x['EndDate'], freq='D')
         .to_frame().resample('M').count().loc['2019-12-01':].unstack(), axis=1)[0].fillna(0))

Output:

 Id Item  StartDate    EndDate  2019-12-31 00:00:00  2020-01-31 00:00:00  2020-02-29 00:00:00  2020-03-31 00:00:00  2020-04-30 00:00:00  2020-05-31 00:00:00  2020-06-30 00:00:00  2020-07-31 00:00:00  2020-08-31 00:00:00
0  1    A 2019-12-10 2020-02-21                 22.0                 31.0                 21.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0
1  2    B 2019-12-01 2020-01-01                 31.0                  1.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0
2  3    C 2019-10-01 2020-08-31                 31.0                 31.0                 29.0                 31.0                 30.0                 31.0                 30.0                 31.0                 31.0
3  4    D 2020-01-01 2020-01-30                  0.0                 30.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0
4  5    E 2019-03-01 2019-12-31                 31.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0
5  6    F 2019-03-01 2019-12-31                 31.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0
6  7    G 2019-10-01 2020-08-31                 31.0                 31.0                 29.0                 31.0                 30.0                 31.0                 30.0                 31.0                 31.0
7  8    H        NaT        NaT                  NaN                  NaN                  NaN                  NaN                  NaN                  NaN                  NaN                  NaN                  NaN