Menu
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])
555
Using Python datetime module: In order to find the difference between two dates we simply input the two dates with date type and subtract them, which in turn provides us the number of days between the two dates.
Use df. dates1-df. dates2 to find the difference between the two dates and then convert the result in the form of months.
timedelta() to create a range of dates. Use a for-loop to iterate through range(stop) where stop is the wanted number of days. Use the + operator to add datetime. timedelta(days) to the starting datetime.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
