+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: Transforming Employee Roster into a Headcount Calendar (/thread-37812.html)
Transforming Employee Roster into a Headcount Calendar - Michaperki - Jul-25-2022
Hello, I've started a new job recently and one of my first tasks as a BI analyst is to create a regularly updating dashboard showing employee headcount over time.
The input dataset is a roster export from our HR system. The data includes the following columns:
- Employee Name
- Function
- Start Date
- Termination Date (if the employee has left / will leave on a certain date).
The output should be a calendar view showing the total headcount for each function by month.
Really just three columns:
- Month
- Function
- Headcount
I've attached an Excel file with sample input and the desired output (apologies, but I wrote this manually and overlooked one software developer so there is a small error in the output).
I have found a workable solution but came here to refine my approach and collect ideas.
Current approach:
- Looking at just 'Start_Date', add 12 Boolean 'Is_date_prior_to' columns for each month in 2022 (Is_date_prior_to_Jan, Is_date_prior_to_Feb, etc...)
- Perform 12 group by operations (one on each of the new columns) to create 12 new dataframes showing monthly onboarded headcount. Append these dataframes to create Start_Headcount_DF
- Repeat steps one and two for 'End_Date' to create End_Headcount_DF.
- Left Join the new End_Headcount_DF onto the Start_Headcount_DF.
- Subtract the End_Headcounts from the Start_Headcounts
If helpful, I can prepare/share a script showing my current process. But I am hoping that this description of the task is enough detail for you to propose an approach.
Thank you for your help!
Michael