Reshape A Dataset With Start And End Dates To Create A Time Series Counting Aggregate Sum By Day/month/quarter

I have a dataset exactly like this: ProjectID Start End Type Project 1 01/01/2019 27/04/2019 HR Project 2 15/01/2019 11/11/2019 Marketing Project 3 25/02/2019 30/07/

Solution 1:

One way to do this is to take a date range (for example for 1 year) and then join all projects to all days. I'm using hvplot to create a nice interactive plot of the end result.

Here's a working example with your sample data:

from io import StringIO
import pandas as pd
import hvplot.pandas

text = """
ProjectID   Start   End Type
Project1   01/01/2019  27/04/2019  HR
Project2   15/01/2019  11/11/2019  Marketing
Project3   25/02/2019  30/07/2019  Finance
Project4   22/02/2019  15/04/2019  HR
Project5   05/03/2019  29/09/2019  HR
Project6   11/04/2019  01/12/2019  Marketing
Project7   29/07/2019  23/08/2019  Finance
Project8   25/08/2019  23/12/2019  Operations
Project9   31/10/2019  29/11/2019  Operations
Project10  10/12/2019  25/12/2019  Operations
"""

df = pd.read_csv(StringIO(text), header=0, sep='\s+')
df['Start'] = pd.to_datetime(df['Start'], dayfirst=True)
df['End'] = pd.to_datetime(df['End'], dayfirst=True)

# create a dummy key with which we can join all projects with all dates
df['key'] = 'key'# create a daterange so that we can count all open projects for all days
df2 = pd.DataFrame(pd.date_range(start='01-01-2019', periods=365, freq='d'), columns=['date'])
# create a dummy key with which we can join all projects with all dates
df2['key'] = 'key'# join all dates with all projects on dummy key = cartesian product
df3 = pd.merge(df, df2, on=['key'])

# check if date is within project dates
df3['count_projects'] = df3['date'].ge(df3['Start']) & df3['date'].le(df3['End'])

# group per day: count all open projects
group_overall = df3.groupby(
    'date', as_index=False)['count_projects'].sum()

# group per day per department: count all projects 
group_per_department = df3.groupby(
    ['date', 'Type'], as_index=False)['count_projects'].sum()

# plot overall result
plot_overall = group_overall.hvplot.line(
    x='date', y='count_projects',
    title='Open projects Overall',
    width=1000,
)

# plot per department
plot_per_department = group_per_department.hvplot.line(
    x='date', y='count_projects', 
    by='Type',
    title='Open projects per Department',
    width=1000,
)

# show both plots using hvplot
(plot_overall + plot_per_department).cols(1)

Resulting plot: