Split a row into multiple rows (to another dataframe) by date range

userQKba1 0
Viewed 37

I have a dataset in pandas that contains date range info:

Project Name Project Lead Start Date End Date Hours
Project 1 Me Jan 1, 2022 Jan 5, 2022 10
Project 2 Myself Mar 1, 2022 Mar 5, 2022 20
Project 3 I Jun 1, 2022 Jun 5, 2022 30

The end goal is to break each line into a daily entry, counting only workdays, and divide hours by the number of days. Something like this:

Project Name Project Lead Start Date End Date Hours
Project 1 Me Jan 1, 2022 Jan 1, 2022 2
Project 1 Me Jan 2, 2022 Jan 2, 2022 2
Project 1 Me Jan 3, 2022 Jan 3, 2022 2
Project 1 Me Jan 4, 2022 Jan 4, 2022 2
Project 1 Me Jan 5, 2022 Jan 5, 2022 2
Project 2 Myself Mar 1, 2022 Mar 1, 2022 4
Project 2 Myself Mar 2, 2022 Mar 2, 2022 4
Project 2 Myself Mar 3, 2022 Mar 3, 2022 4
Project 2 Myself Mar 4, 2022 Mar 4, 2022 4
Project 2 Myself Mar 5, 2022 Mar 5, 2022 4

I'm still new to Python but this is my attempt:

  1. create a function
def split_days(row) :
    v_Temp_Start_Date = row["Start Date"]
    v_Temp_End_Date   = row["End Date"]
    v_Workday_Count   = pandas.bdate_range(row["Start Date"], row["End Date"])
    v_Avg_Hours       = round((row["Hours"] / v_Workday_Count), 2)
    
    while v_Temp_Start_Date <= v_Temp_End_Date:
        if v_Temp_Start_Date.weekday() <= 4 :
            T_Temp1 = pandas.DataFrame(row["ProjectName"], row["Project Lead"], v_Temp_Start_Date, \
            v_Temp_Start_Date, v_Avg_Hours)
            T_output = T_output.append(T_Temp1)
        v_Temp_Start_Date += datetime.timedelta(days=1)

T_Final = pandas.concat(T_Source_Data.apply(lambda row: split_days(row), axis = 1))

Completely not working of course but I don't even know where to start to find out what the problem is.

I tried to run it before I post here to get more detail error but my application is behaving and cannot load...

row split dataframe pandas python
1 Answers

If you convert your dates to datetime objects, you can use date_range to generate row-wise dates which can be exploded. The rest is calculating the Hours and casting the dates back to the desired string format.

If you are using a linux system, the strftime format will use %-d instead of %#d

import pandas as pd
df = pd.DataFrame({'Project Name': ['Project 1', 'Project 2', 'Project 3'],
 'Project Lead': ['Me', 'Myself', 'I'],
 'Start Date': ['Jan 1, 2022', 'Mar 1, 2022', 'Jun 1, 2022'],
 'End Date': ['Jan 5, 2022', 'Mar 5, 2022', 'Jun 5, 2022'],
 'Hours': [10, 20, 30]})

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

df['Start Date'] = df.apply(lambda x: pd.date_range(x['Start Date'], x['End Date'], freq='D'), axis=1)

df = df.explode('Start Date')

df['Hours'] = df.groupby('Project Name')['Hours'].transform(lambda x: x/ len(x)).astype(int)

df['Start Date'] = df['Start Date'].dt.strftime('%b %#d, %Y')
df['End Date'] = df['Start Date']

print(df)

Output

  Project Name Project Lead   Start Date     End Date  Hours
0    Project 1           Me  Jan 1, 2022  Jan 1, 2022      2
0    Project 1           Me  Jan 2, 2022  Jan 2, 2022      2
0    Project 1           Me  Jan 3, 2022  Jan 3, 2022      2
0    Project 1           Me  Jan 4, 2022  Jan 4, 2022      2
0    Project 1           Me  Jan 5, 2022  Jan 5, 2022      2
1    Project 2       Myself  Mar 1, 2022  Mar 1, 2022      4
1    Project 2       Myself  Mar 2, 2022  Mar 2, 2022      4
1    Project 2       Myself  Mar 3, 2022  Mar 3, 2022      4
1    Project 2       Myself  Mar 4, 2022  Mar 4, 2022      4
1    Project 2       Myself  Mar 5, 2022  Mar 5, 2022      4
2    Project 3            I  Jun 1, 2022  Jun 1, 2022      6
2    Project 3            I  Jun 2, 2022  Jun 2, 2022      6
2    Project 3            I  Jun 3, 2022  Jun 3, 2022      6
2    Project 3            I  Jun 4, 2022  Jun 4, 2022      6
2    Project 3            I  Jun 5, 2022  Jun 5, 2022      6
user2uNj0
Related