Categorize a datetime interval based on other datetime interval and put values on columns

Your problem is a lot harder than I thought. One thing that has to be noticed is that the Start_Time and End_Time can have different dates. Furthermore, category C spans over two days. Both of these facts make the code a little bit complicated, but it seems to work.

First, the setup for your problem. I created your data frame and created the variables. Important is that these structures have the correct data types.

import pandas as pd
from io import StringIO
from datetime import datetime, time, date, timedelta

# Create your data frame
data = StringIO("""Product_Name     Start_Time     End_Time
 Product X      2021-10-20 20:32:00     2021-10-21 03:50:00 
 Product Y      2021-10-21 11:50:00     2021-10-21 16:00:00 
 Product Z      2022-01-11 20:10:00     2022-01-12 15:30:00""")
df = pd.read_csv(data, sep='     ', engine='python')

# Convert the columns to date time format
df[["Start_Time", "End_Time"]] = df[["Start_Time", "End_Time"]].apply(pd.to_datetime)

# Create the range start and end time as datetime format
start_a = datetime.strptime('05:01:00', '%H:%M:%S')
end_a = datetime.strptime('14:00:00', '%H:%M:%S')
start_b = datetime.strptime('14:01:00', '%H:%M:%S')
end_b = datetime.strptime('22:00:00', '%H:%M:%S')
start_c = datetime.strptime('22:01:00', '%H:%M:%S')
end_c = datetime.strptime('05:00:00', '%H:%M:%S')

Then, I created a function that can calculate the hours for your problem. start and end are the times that are defined for one range. The function now iterates over the days and looks at how much of your range fits in it. Usually, it needs only one iteration, but your Product Z goes over two days and needs therefore two iterations.

def calc_hours(start_time, end_time, start, end):
    
    # Set range to have date also => allows us to compare to start_time and end_time
    range_start = datetime.combine(start_time.date(), start.time())
    range_end = datetime.combine(start_time.date(), end.time())

    # Special case for range C as end of range is on the next day
    if (range_end<range_start):
        range_end = range_end + timedelta(days=1)

    # start_time and end_time can go over one or more days => Iterate over the days and sum the ours in the range
    total_hours=0.0
    while (range_start < end_time):
        # Calculation to get the hours or zero if range is not within time frame
        hours_in_frame = max((min(range_end, end_time) - max(range_start, start_time)).total_seconds(), 0)/3600
        total_hours += hours_in_frame
        
        # Increment the day to check if range is in time frame
        range_start = range_start + timedelta(days=1)
        range_end = range_end + timedelta(days=1)
    
    return total_hours

In order to use the function and add the results to the dataframe, I used the function apply() from pandas. The apply() takes each row of your dataframe and calculates the hours within a range with the previously shown function. This is done for all three ranges.

# Use apply to calculate the hours for each row and each range
df['A'] = df.apply(lambda x: calc_hours(x['Start_Time'], x['End_Time'], start_a, end_a), axis=1)
df['B'] = df.apply(lambda x: calc_hours(x['Start_Time'], x['End_Time'], start_b, end_b), axis=1)
df['C'] = df.apply(lambda x: calc_hours(x['Start_Time'], x['End_Time'], start_c, end_c), axis=1)

Output is almost what you wanted, but not rounded to two decimal places:

    Product_Name    Start_Time  End_Time    A   B   C
0   Product X   2021-10-20 20:32:00 2021-10-21 03:50:00 0.000000    1.466667    5.816667
1   Product Y   2021-10-21 11:50:00 2021-10-21 16:00:00 2.166667    1.983333    0.000000
2   Product Z   2022-01-11 20:10:00 2022-01-12 15:30:00 8.983333    3.316667    6.983333

Another approach would be to create a Series with all the serial minute numbers of the ranges in question and then intersect them to get the overlapping duration.

Don't have time to provide a full answer but thought I would drop off the idea and you could take it from there.

Create the reference series:

start = pd.Timestamp('22:01')
end = pd.Timestamp('05:00')

if end < start: 
    end +=  pd.Timedelta(days=1)

drC = pd.Series(pd.date_range(start=start, end=end, freq='min')).dt.hour * 60 + \
        pd.Series(pd.date_range(start=start, end=end, freq='min')).dt.minute

Create a function to do the intersection and duration calculation:

def intersecting_duration(x):
    min_of_day = pd.Series(pd.Series(pd.date_range(start=x['Start_Time'], end=x['End_Time'], freq='min')).dt.hour * 60 + \
        pd.Series(pd.date_range(start=x['Start_Time'], end=x['End_Time'], freq='min')).dt.minute)
    
    dur_mins = len(np.intersect1d(min_of_day, drC))       
    return 0 if (dur_mins == 0) else (dur_mins-1)/60

Then apply it:

df.apply(intersecting_duration, axis=1)

0    5.816667
1    0.000000
2    6.983333

You would need to take it from there.