I have a list of dates and times in a Pandas DataFrame. For each date / time, an activity was done (this is classed as "main" or "additional" within the dataframe.
df = pd.DataFrame([[2021-10-30, 10:37:00, Main , 10 , Day], [2021-10-30, 11:11:00, Main, 11, Day]], columns=[Date, Time, Additional, Hour, Shift])
The data consists of two sets of workers, who work a day shift (6am to 6pm) or night shift (6pm to 6am).
I am looking to group the data and sum the total activities done by each group of workers.
I was able to almost complete this with Pandas groupby() function
df = pd.DataFrame(data)
df['Shift'] = df['Hour'].between(6,18)
df['Shift'] = np.where(df['Shift'], 'Night', 'Day')
df = df.groupby(['Date', 'Shift', 'Additional']).size().reset_index(name='Target Count')
df['Cumulative Total'] = df['Target Count'].cumsum()
The issue I have, is that the nightshift can work for example on the 02-Nov from midnight to 6am, and also 6pm to midnight. This is technically two (2) separate shifts, but the groupby function I have used groups this per day, which joins the sum() together.
Ideally, I would group the data for night shift from for 01-Nov (6pm) to 02-Nov (6am)