Turnover rate = # of terminations/ ((start_headcount + end_headcount)/2)
In order to get the 3 data items in this formula, I am using two reports from Workday, one is active report which contains current employees with status as active, and term report which includes already terminated employees. I need to grab data from both reports. I've already coded the approach to calculate turnover rate, see:
from datetime import date
import pandas as pd
## read in active employees report
headcount = pd.read_csv(r"C:\Users\employees.csv", encoding='cp1252')
headcount = headcount[['Employee ID', 'Name', 'Hire Date']]
## read in termination report for termed employees
termination = pd.read_csv(r"C:\Users\Terminations.csv", encoding='cp1252')
termination = termination[['Employee ID', 'Worker', 'Hire Date', 'Termination Date']]
## Input date range to calculate turnover rate, e.g.turnover rate for 2022
start_date = date(2022, 1, 1)
end_date = date(2022, 12, 31)
## Calculate number of terminations in selected date range
termination['Term_Date'] = pd.to_datetime(termination['Termination Date']).dt.date
termination['Hire_Date'] = pd.to_datetime(termination['Hire Date']).dt.date
termination.pop('Termination Date')
termination.pop('Hire Date')
terms = len(termination[(termination['Term_Date'] >= start_date) & (termination['Term_Date'] <= end_date)])
## Calculate number of start headcounts
headcount['Hire_Date'] = pd.to_datetime(headcount['Hire Date']).dt.date
headcount.pop('Hire Date')
start_headcount1 = len(headcount[(headcount['Hire_Date'] <= start_date)])
start_headcount2 = len(termination[(termination['Hire_Date'] <= start_date) & (termination['Term_Date'] >= start_date)])
start_headcount = start_headcount1 + start_headcount2
## Calculate number of end headcounts
end_headcount1 = len(headcount[(headcount['Hire_Date'] <= end_date)])
end_headcount2 = len(termination[(termination['Hire_Date'] <= end_date) & (termination['Term_Date'] > end_date)])
end_headcount = end_headcount1 + end_headcount2
## Calculate turnover rate
turnover = terms / ((start_headcount + end_headcount) / 2)
print("{:.2%}".format(turnover))
How to convert the above lengthy codes into a function? How could I enter the dates range in a pop-up window like Tkinter rather than input them manually between the codes? Can anyone help?
