Breakdown of time period (duration) to month/year

My initial dataframe looks as follows:

How to get from this to a dataframe like the following? The problem is, there are quite a low of rows (millions). Therefor performance is quite important.

Update: Please note, there could be more than one month to break. Example: 2023-02-20 (February) and 2023-12-18 (December)

First idea is repeat rows by difference of End/Start columns in days and count number of rows per ID/Year/Month by GroupBy.size:

df[['Start','End']] = df[['Start','End']].apply(pd.to_datetime)


df = df.loc[df.index.repeat(df['End'].sub(df['Start']).dt.days)]

s = pd.to_timedelta(df.groupby(level=0).cumcount(), unit='d')
df['Date'] = df['Start'].add(s)

df = (df.groupby(['ID', df['Date'].dt.year.rename('Year'),
                       df['Date'].dt.month_name().rename('Month')], sort=False)
        .size()
        .reset_index(name='DurationDays'))
print (df)
   ID  Year Month  DurationDays
0   1  2023   May            12
1   1  2023  June             4
2   2  2023   May             3

For better precision is possible use hours instead days:

df[['Start','End']] = df[['Start','End']].apply(pd.to_datetime)


df = df.loc[df.index.repeat(df['End'].sub(df['Start']).dt.total_seconds().div(3600))]

s = pd.to_timedelta(df.groupby(level=0).cumcount(), unit='h')
df['Start'] = df['Start'].add(s)

df = (df.assign(Year=df['Start'].dt.year,
                Month=df['Start'].dt.month_name())
          .groupby(['ID', 'Year','Month'], sort=False)
          .size()
          .div(24)
          .reset_index(name='DurationDays'))
print (df)
   ID  Year Month  DurationDays
0   1  2023   May          11.5
1   1  2023  June           4.5
2   2  2023   May           3.0

@jezrael's approach is on the right track, but probably unrealistic for a large DataFrame as it requires to repeat the rows by days/hours (thus creating an intermediate with tens or hundreds of millions of rows).

Instead, it seems more reasonable to use pandas' Period as unit to repeat rows.

In short, this breaks the rows by period at their boundaries using repeat and increments the periods with groupby.cumcount, gets the boundaries with start_time/end_time, finally groupby.sum to add the duration per period.

I voluntarily added several intermediate variables to make it easier to understand the code, but if needed many steps could be combined into one to avoid creating too many intermediate columns.

df[['Start', 'End']] = df[['Start', 'End']].apply(pd.to_datetime)

period = 'M'

out = (df.assign(start=df['Start'].dt.to_period(period),
                 end=df['End'].dt.to_period(period),
                 diff=lambda d: d['end'].sub(d['start']).apply(lambda x: x.n))
         .loc[lambda d: d.index.repeat(d['diff']+1)]
         .assign(n=lambda d: d.groupby(level=0).cumcount(),
                 Start=lambda d: d['Start'].mask(d.index.duplicated(), d['start'].add(d['n']).dt.start_time),
                 End=lambda d: d['End'].mask(d.index.duplicated(keep='last'), d['end'].add(d['n']).dt.start_time),
                 DurationDays=lambda d: d['End'].sub(d['Start']),
                 Year=lambda d: d['Start'].dt.year,
                 Month=lambda d: d['Start'].dt.month_name(),
                )
        .groupby(['ID', 'Year', 'Month'], as_index=False, sort=False)
       ['DurationDays'].sum()
      )

Output:

   ID  Year Month     DurationDays
0   1  2023   May 11 days 12:00:00
1   1  2023  June  4 days 12:00:00
2   2  2023   May  3 days 00:00:00

Intermediate before the groupby.sum:

   ID               Start                 End     DurationDays    start      end  diff  n  Year Month
0   1 2023-05-20 12:00:00 2023-06-01 00:00:00 11 days 12:00:00  2023-05  2023-06     1  0  2023   May
0   1 2023-06-01 00:00:00 2023-06-03 12:00:00  2 days 12:00:00  2023-05  2023-06     1  1  2023  June
1   2 2023-05-20 12:00:00 2023-05-23 12:00:00  3 days 00:00:00  2023-05  2023-05     0  0  2023   May
2   1 2023-06-01 12:00:00 2023-06-03 12:00:00  2 days 00:00:00  2023-06  2023-06     0  0  2023  June

generalization

For a more generic method that you can use with any period (provided you have enough resources):

df[['Start', 'End']] = df[['Start', 'End']].apply(pd.to_datetime)

period = 'Y'

out = (df.assign(Period=df['Start'].dt.to_period(period),
                 end=df['End'].dt.to_period(period),
                 diff=lambda d: d['end'].sub(d['Period']).apply(lambda x: x.n))
         .loc[lambda d: d.index.repeat(d['diff']+1)]
         .assign(n=lambda d: d.groupby(level=0).cumcount(),
                 Start=lambda d: d['Start'].mask(d.index.duplicated(), d['Period'].add(d['n']).dt.start_time),
                 End=lambda d: d['End'].mask(d.index.duplicated(keep='last'), d['end'].add(d['n']).dt.start_time),
                 DurationDays=lambda d: d['End'].sub(d['Start']),
                )
        .groupby(['ID', 'Period'], as_index=False, sort=False)
       ['DurationDays'].sum()
      )

Output for period = 'Y':

   ID Period DurationDays
0   1   2023      16 days
1   2   2023       3 days

Output for period = 'M':

   ID   Period DurationDays
0   1  2023-05      14 days
1   2  2023-05       3 days
2   1  2023-06       2 days

You can lreshape your DataFrame then use split_months (slightly adjusted) :

tmp = (
    pd.lreshape(df, {'Datetime': ['Start', 'End']})
        .sort_values(by=['ID', 'Datetime'])
)

out = (
    tmp.groupby(['ID']).apply(
        lambda g: split_months(g['Datetime'].min(), g['Datetime'].max())
    ).reset_index('ID')
)

Output :

print(out)

   ID Month  Year  DurationDays
1   1   May  2023         11.00
2   1  June  2023          3.00 # <-- needs to be fixed (it is `2+3`)
1   2   May  2023          3.00