tutorial: Python can also help our parents ;)
By Julien Hernandez Lallement, 2020-08-03, in category Tutorial
By Julien Hernandez Lallement, 2020-08-03, in category Tutorial
My mom works as a translator in law enforcing organisations. She gets called at any hour of the day or night to reach a location where translation is needed, and performs a so-called mission over there.
Now, for every mission, she would get a transcript that states the date and hour of start & end of mission. She would then use that transcript to calculate the amount in euros she would be payed for her work.
But my mom is not very good with computers and paperwork, so it took her forever to look up the dates (because week end, holiday and normal business day are payed differently) and hours (cause day and night hours are payed differently)...These transcripts accumulated, and she was getting stressed about it.
I realized that her problem could be solved quite easily by a simple application that would calculate for her the fees she would receive from her employer and for each mission.
This notebook describes the easy solution I came up with (always helped by many posts online of course!). In the follow up post, I create a GUI using Tkinter (my mom is scared of code...)
import pandas as pd
from pandas.tseries.holiday import *
from pandas.tseries.offsets import CustomBusinessDay
My mom works in France. So first, I created a calendar of french holidays, since the fare that she is getting differs whether the day was holiday or a regular business day.
class FrenchBusinessCalendar(AbstractHolidayCalendar):
rules = [
Holiday('New Years Day', month=1, day=1),
EasterMonday,
Holiday('Labour Day', month=5, day=1),
Holiday('Victory in Europe Day', month=5, day=8),
Holiday('Ascension Day', month=1, day=1, offset=[Easter(), Day(39)]),
Holiday('Bastille Day', month=7, day=14),
Holiday('Assumption of Mary to Heaven', month=8, day=15),
Holiday('All Saints Day', month=11, day=1),
Holiday('Armistice Day', month=11, day=11),
Holiday('Christmas Day', month=12, day=25)
]
Second, I make a list of all regular business days
French_BD = CustomBusinessDay(calendar=FrenchBusinessCalendar())
The missions my mom was performing were ranging between 2016 and 2020 so I took up to 2021, anticipation is key ;)
s = pd.date_range('2016-12-29', end='2021-01-03', freq=French_BD)
df = pd.DataFrame(s, columns=['Date'])
print(df)
Third, I define the fares (based on the aforementioned rules) in different dictionnaries. These fares are in euros, but you can add whatever you want in there, currency is not relevant.
There were some rules I had to follow:
# Define fares depending on day time
normal_dict = {'day_first_hour_fare':'42',
'night_first_hour_fare':'49.50',
'day_subsequent_hour_fare': '30',
'night_subsequent_hour_fare': '37.50'
}
holiday_dict = {'day_first_hour_fare':'49.50',
'night_first_hour_fare':'57',
'day_subsequent_hour_fare':'37.50',
'night_subsequent_hour_fare':'45'
}
Last, I create a function that calculates to total fare based on start and end date, and fare dictionnaries
def calculate_honorary(start_date, end_date, normal_dict, holiday_dict):
"""
Calculate the honorary for worked hours based on following rules
params: start_date (str), start date in format '%Y-%m-%d-H:M:S'
params: end_date (str), end date in format '%Y-%m-%d-H:M:S'
params: normal_dict (dict), business day fare dictionnary
params: holiday_dict (dict), holiday day fare dictionnary
#### Payment Rules
day is between 0700 and 2200
night is between 2200 and 0700
normal day fare:
* first hour = 42 euros
* subsequent hour = 30 euros
normal night fare
* first hour = 49.50 euros
* subsequent hour = 37.50 euros
holiday fare:
* first hour = 49.50 euros
* subsequent hour = 37.50 euros
holiday fare
* first hour = 57 euros
* subsequent hour = 45 euros
"""
# Transform dates to Timestamps
start_date = pd.Timestamp(start_date)
end_date = pd.Timestamp(end_date)
print('Start date: ' + str(start_date))
print('End date: ' + str(end_date))
print(' ')
# Get number of hours worked
number_hours_worked = int(pd.Timedelta(end_date-start_date,unit='h') / timedelta(hours=1))
if number_hours_worked < 0:
raise ValueError("End date happened before start date")
else:
print('You have worked ' +str(number_hours_worked) + ' hours.')
# Get actual hours relative to the day
worked_hours = pd.Series(pd.date_range(start_date,end_date,freq='H').hour)
worked_dates = pd.Series(pd.date_range(start_date,end_date,freq='H').date)
# Get whether these hours were day or night shift
## Day is defined between 0700 and 2200
bins = [0, 7,22, 24]
# I add a third night label that I rename later on. Suboptimal
labels = ['Night', 'Day', 'Night1']
# Compute shifts
shift = pd.cut(worked_hours[1:], bins=bins, labels=labels, include_lowest=True, right=True).replace('Night1', 'Night')
# Concatenate data
hours_per_shift = (pd
.DataFrame(pd.concat([worked_dates, worked_hours,shift],axis=1))
.rename(columns={0: 'date', 1: 'hour', 2:'shifts'}))
# Shift the shifts column to get correct number of hours per shift per day
hours_per_shift.shifts = hours_per_shift.shifts.shift(-1)
# Groupby and count the number of hours
# Fill NaN with 0 hours worked
hours_per_shift = hours_per_shift.groupby(['date', 'shifts']).count().fillna(0)
print(hours_per_shift)
print(' ')
# Verify whether start date is holiday
if (df.Date.astype(str).str.contains(start_date.strftime('%Y-%m-%d')).sum()) > 0:
# Day in calendar, so not holiday
start_holiday=False
fare_dict_start = normal_dict
print('Start date is business day.')
else:
# Day not in calendar, so holiday
start_holiday=True
fare_dict_start = holiday_dict
print('Start date is weekend or holiday')
if df.Date.astype(str).str.contains(end_date.strftime('%Y-%m-%d')).sum() > 0:
# Day in calendar, so not holiday
end_holiday=False
fare_dict_end = normal_dict
print('End date is business day')
else:
# Day not in calendar, so holiday
end_holiday=True
fare_dict_end = holiday_dict
print('End date is weekend or holiday')
# Calculate fee
print('')
if start_date.date() == end_date.date(): # if mission was on one day only
print('Mission was on one single day')
# Set end date as no gain
honorary_end_date = 0
# Get first day data
day_one = hours_per_shift.reset_index().loc[hours_per_shift.reset_index()['date']==start_date]
if shift.iloc[0] == 'Day':
honorary_start_date = (float(fare_dict_start.get("day_first_hour_fare"))
+ float((day_one.loc[day_one.shifts == 'Day'].hour - 1)
* float(fare_dict_start.get("day_subsequent_hour_fare")))
+ float((day_one.loc[day_one.shifts == 'Night'].hour)
* float(fare_dict_start.get("night_subsequent_hour_fare"))))
else:
honorary_start_date = (float(fare_dict.get("night_first_hour_fare"))
+ float((day_one.loc[day_one.shifts == 'Night'].hour - 1)
* float(fare_dict_start.get("night_subsequent_hour_fare")))
+ float((day_one.loc[day_one.shifts == 'Day'].hour)
* float(fare_dict_start.get("day_subsequent_hour_fare"))))
else: # if mission was on two consecutive days
print('Mission was on two consecutive days')
print('')
day_one = hours_per_shift.reset_index().loc[hours_per_shift.reset_index()['date']==start_date]
day_two = hours_per_shift.reset_index().loc[hours_per_shift.reset_index()['date']==end_date]
if shift.iloc[0] == 'Day':
print('First hour is day shift')
# Honorary Start Date
honorary_start_date = (float(fare_dict_start.get("day_first_hour_fare"))
+ float((day_one.loc[day_one.shifts == 'Day'].hour - 1)
* float(fare_dict_start.get("day_subsequent_hour_fare")))
+ float((day_one.loc[day_one.shifts == 'Night'].hour)
* float(fare_dict_start.get("night_subsequent_hour_fare"))))
# Honorary End Date
honorary_end_date = (
+ float((day_two.loc[day_two.shifts == 'Night'].hour)
* float(fare_dict_end.get("night_subsequent_hour_fare")))
+ float((day_two.loc[day_two.shifts == 'Day'].hour)
* float(fare_dict_end.get("day_subsequent_hour_fare"))))
else:
print('First hour isnight shift')
# Honorary Start Date
honorary_start_date = (float(fare_dict_start.get("night_first_hour_fare"))
+ float((day_one.loc[day_one.shifts == 'Night'].hour - 1)
* float(fare_dict_start.get("night_subsequent_hour_fare")))
+ float((day_one.loc[day_one.shifts == 'Day'].hour)
* float(fare_dict_start.get("day_subsequent_hour_fare"))))
# Honorary End Date
honorary_end_date = (
+ float((day_two.loc[day_two.shifts == 'Night'].hour)
* float(fare_dict_end.get("night_subsequent_hour_fare")))
+ float((day_two.loc[day_two.shifts == 'Day'].hour)
* float(fare_dict_end.get("day_subsequent_hour_fare"))))
honorary_total = int(honorary_start_date+honorary_end_date)
print(' ')
print('You are owed ' + str(honorary_total) + ' euros.')
return honorary_total
calculate_honorary(start_date='2019-05-01 15:00:00',
end_date='2019-05-02 00:00:00',
normal_dict=normal_dict,
holiday_dict=holiday_dict)
Indeed, the First of May 2019 was holiday in France. I do not distinguish between week end and holiday, since the fare is the same. But you could do that eventually by modifying the function.
I added a quick feedback on how many hours per day and night were performed, as well as the total amount of euros owed to my mom.
calculate_honorary(start_date='2019-04-16 15:00:00',
end_date='2019-04-17 09:00:00',
normal_dict=normal_dict,
holiday_dict=holiday_dict)
And here again, these two days were indeed regular business days
In the following post, I used Tkinter to create a GUI that allowed my mom to use this code without ever needing to see it, which was quite important to not scare her off :D
I hope you enjoyed this, and maybe found it useful!