I have the following table




Function
Department
Start Date
End Date




Const
Const 1
2023-03-01
2023-03-05


Const
Const 2
2023-03-02
2023-03-03


Mining
Mining 1
2023-03-02
2023-03-05


Mining
Mining 2
2023-03-01
2023-03-06


Const
Const 1
2023-03-03
2023-03-07


Const
Const 2
2023-03-02
2023-03-05


Mining
Mining 1
2023-03-06
2023-03-09


Mining
Mining 2
2023-03-05
2023-03-08




I want to get per date the total count in each department. Both start date and end date and included in counting.
It would be nice to have an intermediate output as follows




Function
Department
Date
Count




Const
Const1
2023-03-01
1


Const
Const1
2023-03-02
1


Const
Const1
2023-03-03
2


Const
Const1
2023-03-04
2


Const
Const1
2023-03-05
2


Const
Const1
2023-03-06
1


Const
Const1
2023-03-07
1


Const
Const1
2023-03-08
0


Const
Const1
2023-03-09
0


Const
Const1
2023-03-10
0


Const
Const2
2023-03-01
0


Const
Const2
2023-03-02
2


Const
Const2
2023-03-03
2


Const
Const2
2023-03-04
1


Const
Const2
2023-03-05
1


Const
Const2
2023-03-06
0


Const
Const2
2023-03-07
0


Const
Const2
2023-03-08
0


Const
Const2
2023-03-09
0


Const
Const2
2023-03-10
0


Mining
Mining 1
2023-03-01
0


Mining
Mining 1
2023-03-02
1


Mining
Mining 1
2023-03-03
1


Mining
Mining 1
2023-03-04
1


Mining
Mining 1
2023-03-05
1


Mining
Mining 1
2023-03-06
1


Mining
Mining 1
2023-03-07
1


Mining
Mining 1
2023-03-08
1


Mining
Mining 1
2023-03-09
1


Mining
Mining 1
2023-03-10
0


Mining
Mining 2
2023-03-01
1


Mining
Mining 2
2023-03-02
1


Mining
Mining 2
2023-03-03
1


Mining
Mining 2
2023-03-04
1


Mining
Mining 2
2023-03-05
2


Mining
Mining 2
2023-03-06
2


Mining
Mining 2
2023-03-07
1


Mining
Mining 2
2023-03-08
1


Mining
Mining 2
2023-03-09
0


Mining
Mining 2
2023-03-10
0




The desired final output is a pandas df as follows




Date
Const 1
Const 2
Mining 1
Mining 2




2023-03-01
1
0
0
1


2023-03-02
1
2
1
1


2023-03-03
2
2
1
1


2023-03-04
2
1
1
1


2023-03-05
2
1
1
2




Calculating Collective Count of departments on individual dates from a given date range
df['Start Date'] = pd.to_datetime(df['Start Date'])
df['End Date'] = pd.to_datetime(df['End Date'])

dates = pd.date_range(df['Start Date'].min(), df['End Date'].max()) #get the complete dates in data set
final_df = pd.DataFrame({'Date': dates})
final_df = final_df.set_index('Date')

departments = df['Department'].unique()

for department in departments:
    mask = (df['Department'] == department)
    department_counts = (df.loc[mask, 'Start Date'].value_counts() + df.loc[mask, 'End Date'].value_counts()).sort_index()
    final_df[department] = department_counts

final_df = final_df.fillna(0)

