Pandas: Count days in each month between given start and end date

Question

I have a pandas dataframe with some beginning and ending dates.

ActualStartDate ActualEndDate
0   2019-06-30  2019-08-15
1   2019-09-01  2020-01-01
2   2019-08-28  2019-11-13

Given these start & end dates I need to count how many days in each month between beginning and ending dates. I can't figure out a good way to approach this, but resulting dataframe should be something like:

ActualStartDate ActualEndDate 2019-06 2019-07 2019-08 2019-09 2019-10 2019-11 2019-12 2020-01 etc
0   2019-06-30  2019-08-15    1       31      15      0       0       0       0       0
1   2019-09-01  2020-01-01    0       0       0       30      31      30      31      1
2   2019-08-28  2019-11-13    0       0       4       30      31      13      0       0

Note that actual dataframe has ~1,500 rows with varying beginning & end dates. Open to different df output, but showing the above to give you the idea of what I need to accomplish. Thank you in advance for any help!

Refer this link : https://stackoverflow.com/q/45816497/10077354, similarly try to find number of days in each month between the 2 given dates. — Suraj, Mar 27 '20 at 05:15

jezrael · Accepted Answer · 2020-03-27T05:44:04.877

Idea is create month periods by DatetimeIndex.to_period from date_range and count by Index.value_counts, then create DataFrame by concat with replace missing values by DataFrame.fillna, last join to original by DataFrame.join:

L = {r.Index: pd.date_range(r.ActualStartDate, r.ActualEndDate).to_period('M').value_counts()
     for r in df.itertuples()}
df = df.join(pd.concat(L, axis=1).fillna(0).astype(int).T)
print (df)
  ActualStartDate ActualEndDate  2019-06  2019-07  2019-08  2019-09  2019-10  \
0      2019-06-30    2019-08-15        1       31       15        0        0   
1      2019-09-01    2020-01-01        0        0        0       30       31   
2      2019-08-28    2019-11-13        0        0        4       30       31   

   2019-11  2019-12  2020-01  
0        0        0        0  
1       30       31        1  
2       13        0        0

Performance:

df = pd.concat([df] * 1000, ignore_index=True)

In [44]: %%timeit
    ...: L = {r.Index: pd.date_range(r.ActualStartDate, r.ActualEndDate).to_period('M').value_counts()
    ...:      for r in df.itertuples()}
    ...: df.join(pd.concat(L, axis=1).fillna(0).astype(int).T)
    ...: 
689 ms ± 5.63 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [45]: %%timeit
    ...: df.join(
    ...:     df.apply(lambda v: pd.Series(pd.date_range(v['ActualStartDate'], v['ActualEndDate'], freq='D').to_period('M')), axis=1)
    ...:     .apply(pd.value_counts, axis=1)
    ...:     .fillna(0)
    ...:     .astype(int))
    ...:     
994 ms ± 5.17 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

score 1 · Answer 2 · answered Mar 27 '20 at 05:35

Probably not the most efficient but shouldn't be too bad for ~1500 rows... expand out a date range and then convert it to a monthly period, take the counts of those and rejoin back to your original DF, eg:

res = df.join(
    df.apply(lambda v: pd.Series(pd.date_range(v['ActualStartDate'], v['ActualEndDate'], freq='D').to_period('M')), axis=1)
    .apply(pd.value_counts, axis=1)
    .fillna(0)
    .astype(int)
)

Gives you:

  ActualStartDate ActualEndDate  2019-06  2019-07  2019-08  2019-09  2019-10  2019-11  2019-12  2020-01  2020-02  2020-03  2020-04  2020-05  2020-06  2020-07  2020-08  2020-09  2020-10  2020-11
0      2019-06-30    2020-08-15        1       31       31       30       31       30       31       31       29       31       30       31       30       31       15        0        0        0
1      2019-09-01    2020-01-01        0        0        0       30       31       30       31        1        0        0        0        0        0        0        0        0        0        0
2      2019-08-28    2020-11-13        0        0        4       30       31       30       31       31       29       31       30       31       30       31       31       30       31       13

score 0 · Answer 3 · answered Mar 27 '20 at 06:04

import pandas as pd
import calendar

date_info = pd.DataFrame({
    'ActualStartDate': [
        pd.Timestamp('2019-06-30'),
        pd.Timestamp('2019-09-01'),
        pd.Timestamp('2019-08-28'),
    ],
    'ActualEndDate': [
        pd.Timestamp('2019-08-15'),
        pd.Timestamp('2020-01-01'),
        pd.Timestamp('2019-11-13'),
    ]
})

# ============================================================

result = {}  # result should in dict, in case of too many cols.
for index, timepair in date_info.iterrows():
    start = timepair['ActualStartDate']
    end = timepair['ActualEndDate']

    current  = start
    result[index] = {}  # delta days in this pair
    while True:
        # find the delta days
        # current day is also count, so should + 1
        _, days = calendar.monthrange(current.year, current.month)
        days = min(days, (end - current).days + 1)
        delta = days - current.day + 1

        result[index]['%s-%s'%(current.year, current.month)] = delta
        current += pd.Timedelta(delta, unit='d')

        if current >= end:
            break

# you can save the result in dataframe, if you insisit
columns = set()
for value in result.values():
    columns.update(value.keys())

for col in columns:
    date_info[col] = 0

for index, delta in result.items():
    for date, days in delta.items():
        date_info.loc[index, date] = days

print(date_info)

Pandas: Count days in each month between given start and end date

3 Answers3