Common Use Cases¶
Table of Contents
This document contains code snippets for the common use cases of timeboard
library. It is also available as a jupyter notebook.
The import statements for all examples are:
[1]:
import timeboard as tb
import pandas as pd
Note: We will use pandas
dataframes to store the data we work with.
Setting up the calendar¶
Two types of calendars are used in the examples: a standard business day calendar and a timeboard of shifts in a 24x7 call center. The detailed explanations how to create these or other timeboards are given in Making a Timeboard section. Calculations are performed similarly for any type of timeboard.
To obtain a standard business day calendar we use the built-ins:
[2]:
import timeboard.calendars.RU as RU
clnd_ru = RU.Weekly8x5()
import timeboard.calendars.UK as UK
clnd_uk = UK.Weekly8x5(country='england')
A sample of the UK calendar clnd_uk
is shown below. It starts on Monday, the 17th of April, which was a holiday (Easter Monday), and ends on Monday the 24th, a regular business day.
Note: We take advantage of the nice formatting that jupyter notebooks provide for pandas dataframes. Instead of official print(clnd_uk(('17 Apr 2017', '24 Apr 2017')))
, we will convert the interval to dataframe and let jupyter display its contents.
[3]:
clnd_uk(('17 Apr 2017', '24 Apr 2017')).to_dataframe()
[3]:
ws_ref | start | duration | end | label | on_duty | |
---|---|---|---|---|---|---|
loc | ||||||
6316 | 2017-04-17 | 2017-04-17 | 1 | 2017-04-17 | 0 | False |
6317 | 2017-04-18 | 2017-04-18 | 1 | 2017-04-18 | 8 | True |
6318 | 2017-04-19 | 2017-04-19 | 1 | 2017-04-19 | 8 | True |
6319 | 2017-04-20 | 2017-04-20 | 1 | 2017-04-20 | 8 | True |
6320 | 2017-04-21 | 2017-04-21 | 1 | 2017-04-21 | 8 | True |
6321 | 2017-04-22 | 2017-04-22 | 1 | 2017-04-22 | 0 | False |
6322 | 2017-04-23 | 2017-04-23 | 1 | 2017-04-23 | 0 | False |
6323 | 2017-04-24 | 2017-04-24 | 1 | 2017-04-24 | 8 | True |
Our call center operates round-the-clock in shifts of varying length: 08:00 to 18:00 (10 hours), 18:00 to 02:00 (8 hours), and 02:00 to 08:00 (6 hours). An operator’s schedule consists of one on-duty shift followed by three off-duty shifts. Hence, four teams of operators are needed. They are designated as ‘A’, ‘B’, ‘C’, and ‘D’. Timeboard clnd_cc
for the call center is built by the following code.
[4]:
teams = ['A', 'B', 'C', 'D']
day_parts = tb.Marker(each='D',
at=[{'hours':2}, {'hours':8}, {'hours':18}])
shifts = tb.Organizer(marker=day_parts, structure=teams)
clnd_cc = tb.Timeboard(base_unit_freq='H',
start='01 Jan 2009 02:00', end='01 Jan 2019 01:59',
layout=shifts)
for team in teams:
clnd_cc.add_schedule(name='team_'+ team,
selector=lambda label, team=team: label==team)
A sample of clnd_cc
for the week of 17 April 2017 is shown below.
[5]:
clnd_cc(('17 Apr 2017 2:00', '24 Apr 2017')).to_dataframe()
[5]:
ws_ref | start | duration | end | label | on_duty | team_A | team_B | team_C | team_D | |
---|---|---|---|---|---|---|---|---|---|---|
loc | ||||||||||
9084 | 2017-04-17 02:00:00 | 2017-04-17 02:00:00 | 6 | 2017-04-17 07:59:59 | A | True | True | False | False | False |
9085 | 2017-04-17 08:00:00 | 2017-04-17 08:00:00 | 10 | 2017-04-17 17:59:59 | B | True | False | True | False | False |
9086 | 2017-04-17 18:00:00 | 2017-04-17 18:00:00 | 8 | 2017-04-18 01:59:59 | C | True | False | False | True | False |
9087 | 2017-04-18 02:00:00 | 2017-04-18 02:00:00 | 6 | 2017-04-18 07:59:59 | D | True | False | False | False | True |
9088 | 2017-04-18 08:00:00 | 2017-04-18 08:00:00 | 10 | 2017-04-18 17:59:59 | A | True | True | False | False | False |
9089 | 2017-04-18 18:00:00 | 2017-04-18 18:00:00 | 8 | 2017-04-19 01:59:59 | B | True | False | True | False | False |
9090 | 2017-04-19 02:00:00 | 2017-04-19 02:00:00 | 6 | 2017-04-19 07:59:59 | C | True | False | False | True | False |
9091 | 2017-04-19 08:00:00 | 2017-04-19 08:00:00 | 10 | 2017-04-19 17:59:59 | D | True | False | False | False | True |
9092 | 2017-04-19 18:00:00 | 2017-04-19 18:00:00 | 8 | 2017-04-20 01:59:59 | A | True | True | False | False | False |
9093 | 2017-04-20 02:00:00 | 2017-04-20 02:00:00 | 6 | 2017-04-20 07:59:59 | B | True | False | True | False | False |
9094 | 2017-04-20 08:00:00 | 2017-04-20 08:00:00 | 10 | 2017-04-20 17:59:59 | C | True | False | False | True | False |
9095 | 2017-04-20 18:00:00 | 2017-04-20 18:00:00 | 8 | 2017-04-21 01:59:59 | D | True | False | False | False | True |
9096 | 2017-04-21 02:00:00 | 2017-04-21 02:00:00 | 6 | 2017-04-21 07:59:59 | A | True | True | False | False | False |
9097 | 2017-04-21 08:00:00 | 2017-04-21 08:00:00 | 10 | 2017-04-21 17:59:59 | B | True | False | True | False | False |
9098 | 2017-04-21 18:00:00 | 2017-04-21 18:00:00 | 8 | 2017-04-22 01:59:59 | C | True | False | False | True | False |
9099 | 2017-04-22 02:00:00 | 2017-04-22 02:00:00 | 6 | 2017-04-22 07:59:59 | D | True | False | False | False | True |
9100 | 2017-04-22 08:00:00 | 2017-04-22 08:00:00 | 10 | 2017-04-22 17:59:59 | A | True | True | False | False | False |
9101 | 2017-04-22 18:00:00 | 2017-04-22 18:00:00 | 8 | 2017-04-23 01:59:59 | B | True | False | True | False | False |
9102 | 2017-04-23 02:00:00 | 2017-04-23 02:00:00 | 6 | 2017-04-23 07:59:59 | C | True | False | False | True | False |
9103 | 2017-04-23 08:00:00 | 2017-04-23 08:00:00 | 10 | 2017-04-23 17:59:59 | D | True | False | False | False | True |
9104 | 2017-04-23 18:00:00 | 2017-04-23 18:00:00 | 8 | 2017-04-24 01:59:59 | A | True | True | False | False | False |
Determining deadlines¶
Source data:
- Project timetable defined in terms of business days allotted to complete each stage of the project.
- Start date of the project.
[6]:
project_start = '01 Jan 2018'
project_timetable = pd.DataFrame(data=[
['Development', 14],
['Acceptance', 2],
['Deployment', 3]
],
columns=['Stage', 'Duration'])
project_timetable
[6]:
Stage | Duration | |
---|---|---|
0 | Development | 14 |
1 | Acceptance | 2 |
2 | Deployment | 3 |
The company works standard business hours. The country is Russia.
Task: Obtain the project deadlines as the calendar dates.
[7]:
clnd = clnd_ru
start_dates, end_dates = [], []
for stage_duration in project_timetable['Duration']:
if not start_dates:
start_dates = [clnd(project_start).rollforward()]
else:
start_dates.append(end_dates[-1] + 1)
end_dates.append(start_dates[-1] + (stage_duration - 1))
project_timetable['Start'] = [day.to_timestamp() for day in start_dates]
project_timetable['Deadline'] = [day.to_timestamp() for day in end_dates]
project_timetable
[7]:
Stage | Duration | Start | Deadline | |
---|---|---|---|---|
0 | Development | 14 | 2018-01-09 | 2018-01-26 |
1 | Acceptance | 2 | 2018-01-29 | 2018-01-30 |
2 | Deployment | 3 | 2018-01-31 | 2018-02-02 |
Analysis
Two timeboard
methods are used in this example:
Timeboard.get_workshift()
is called in line 5 disguised asclnd(project_start)
. It puts the start date of the project into the context of the timeline of the calendar and returns the corresponding workshift.Workshift.rollforward()
is called by name in line 5 and by proxy of operator+
in lines 7 and 8.
When called without arguments (line 5) rollforward()
returns the nearest on-duty workshift. In terms of our calendar, this means the nearest business day. It may be either project_start date itself or the next working day if project_start is a weekend or a holiday. In Russia, the first 8 days of January 2017 were holidays, hence, clnd('01 Jan 2017').rollforward()
returns 09 Jan 2017.
When called with an integer argument, rollforward(n)
moves n days toward the future skipping weekends and holidays. This is done in lines 7 and 8 where operator +
is used as a shortcut for rollforward
. For example, note that rollforward(1)
called on Friday, 26 Jan 2017 (the end of Development stage), returns Monday, 29 Jan 2017, which becomes the start of Acceptance stage.
Generating shift schedule¶
Source data: timeboard of all shifts in a call center.
Task: generate the schedule of team’s ‘A’ shifts for the week of 17 April 2017. (This is the same interval which illustrates the call center’s timeboard in Setting up the calendar section above).
[8]:
clnd = clnd_cc
schedule = clnd.schedules['team_A']
period = clnd('17 April 2017', period='W')
shifts = [
[ws.start_time, ws.duration, ws.end_time.ceil(clnd.base_unit_freq)]
for ws in period.workshifts(schedule=schedule)
]
pd.DataFrame(shifts, columns=['Start', 'Duration', 'End'])
[8]:
Start | Duration | End | |
---|---|---|---|
0 | 2017-04-17 02:00:00 | 6 | 2017-04-17 08:00:00 |
1 | 2017-04-18 08:00:00 | 10 | 2017-04-18 18:00:00 |
2 | 2017-04-19 18:00:00 | 8 | 2017-04-20 02:00:00 |
3 | 2017-04-21 02:00:00 | 6 | 2017-04-21 08:00:00 |
4 | 2017-04-22 08:00:00 | 10 | 2017-04-22 18:00:00 |
5 | 2017-04-23 18:00:00 | 8 | 2017-04-24 02:00:00 |
Analysis
Timeboard.schedules
is a dictionary of schedules registered for our timeboard. In line 2 the schedule for team ‘A’ is retrieved.- In line 3
clnd('17 April 2017', period='A')
is a call ofTimeboard.get_interval()
returning an interval of shifts which belong to the calendar week 17-23 of April. Interval.workshifts()
in line 6 returns a generator yielding all on-duty workshifts of the interval. Shifts are classified as “on duty” or “off duty” according to the schedule which is supplied to the method. By default, the method uses the default schedule of the timeboard. It would not be suitable for our purpose as under the default schedule every shift is on duty (the call center is always working). Hence we passed a specific schedule which selects only shifts labeled with ‘A’.start_time
,duration
, andend_time
are workshift attributes. We usepandas.Timestamp.ceil()
to round up the end time of a workshift to the beginning of the next base unit of the timeboard.
Average annual headcount¶
The following examples are based on a fictitious company Kings and Queens Ltd.
Source data: staff register for Kings and Queens Ltd. containing for each employee:
- dates of entering and leaving the company
- salary rate
The value of None
as the leaving date means that this person is still with the company.
[9]:
staff = [
['Doran', '01 Feb 2012', '11 Nov 2017', 700],
['Robert', '10 May 2012', '01 Jan 2017', 1000],
['Joffrey', '03 Jan 2017', '17 Jul 2017', 800 ],
['Stannis', '02 Jan 2017', '07 Nov 2017', 500],
['Robb', '03 Apr 2017', '28 Apr 2017', 200],
['Daenerys', '18 Apr 2017', None, 500],
['Tommen', '18 Jul 2017', '29 Dec 2017', 800],
['Cersei', '30 Dec 2017', None, 1000],
['Jon', '01 Feb 2018', None, 100]
]
register = pd.DataFrame(data=staff,
columns=['Name', 'Enter', 'Leave', 'Rate']).set_index('Name')
register
[9]:
Enter | Leave | Rate | |
---|---|---|---|
Name | |||
Doran | 01 Feb 2012 | 11 Nov 2017 | 700 |
Robert | 10 May 2012 | 01 Jan 2017 | 1000 |
Joffrey | 03 Jan 2017 | 17 Jul 2017 | 800 |
Stannis | 02 Jan 2017 | 07 Nov 2017 | 500 |
Robb | 03 Apr 2017 | 28 Apr 2017 | 200 |
Daenerys | 18 Apr 2017 | None | 500 |
Tommen | 18 Jul 2017 | 29 Dec 2017 | 800 |
Cersei | 30 Dec 2017 | None | 1000 |
Jon | 01 Feb 2018 | None | 100 |
Task: Calculate the average annual headcount of the company in 2017.
As an intermediate step, we will find out what portion of the year 2017 each person has worked for Kings and Queens Ltd. The data will be stored in the new column ‘Worked_in_2017’.
[10]:
clnd = clnd_uk
y2017 = clnd('2017', period='A')
register['Worked_in_2017'] = [
clnd(tenure) / y2017 for tenure in zip(register.Enter, register.Leave)
]
register
[10]:
Enter | Leave | Rate | Worked_in_2017 | |
---|---|---|---|---|
Name | ||||
Doran | 01 Feb 2012 | 11 Nov 2017 | 700 | 0.869048 |
Robert | 10 May 2012 | 01 Jan 2017 | 1000 | 0.000000 |
Joffrey | 03 Jan 2017 | 17 Jul 2017 | 800 | 0.539683 |
Stannis | 02 Jan 2017 | 07 Nov 2017 | 500 | 0.857143 |
Robb | 03 Apr 2017 | 28 Apr 2017 | 200 | 0.071429 |
Daenerys | 18 Apr 2017 | None | 500 | 0.710317 |
Tommen | 18 Jul 2017 | 29 Dec 2017 | 800 | 0.460317 |
Cersei | 30 Dec 2017 | None | 1000 | 0.000000 |
Jon | 01 Feb 2018 | None | 100 | 0.000000 |
Analysis
Two timeboard
methods are used in this example:
Timeboard.get_interval()
is called in line 3 disguised asclnd('2017', period='A')
and in line 5 asclnd(tenure)
. The former call returns the interval which corresponds to the calendar year 2017. The latter call returns an interval which is bounded by the two dates supplied in tenure tuple: the day when the employee entered the company, and the day when he or she left.Interval.what_portion_of()
is called by proxy of the division operator/
in lines 5. This method finds out what portion of the second operand (the year 2017) is contained within the first operand (the working period of a person). Only business days are counted.
Note. For the employees still working at the company, the second element of tenure tuple is None. It means that the interval returned by clnd(tenure)
extends until the last day of the calendar. The end time of the calendar is stored in clnd.end_time
attribute.
The last step in calculating the average annual headcount in 2017 is trivial. We sum up all values in ‘Worked_in_2017’ column.
[11]:
headcount = register.Worked_in_2017.sum()
headcount
[11]:
3.5079365079365079
Calculating wages and salaries payable¶
Source data:
- Staff register with dates of entering and leaving the company and wage/salary rates.
- Pay period.
Task: For each employee determine the amount of wage/salary payable in the given pay period.
Periodic salary¶
Suppose salary is paid monthly. Below is the calculation of the salaries payable to the employees of Kings and Queens Ltd. in April 2017.
[12]:
pay_period = clnd('April 2017', period='M')
register['Salary_April'] = [
clnd(tenure) / pay_period for tenure in zip(register.Enter, register.Leave)
] * register.Rate
register
[12]:
Enter | Leave | Rate | Worked_in_2017 | Salary_April | |
---|---|---|---|---|---|
Name | |||||
Doran | 01 Feb 2012 | 11 Nov 2017 | 700 | 0.869048 | 700.0 |
Robert | 10 May 2012 | 01 Jan 2017 | 1000 | 0.000000 | 0.0 |
Joffrey | 03 Jan 2017 | 17 Jul 2017 | 800 | 0.539683 | 800.0 |
Stannis | 02 Jan 2017 | 07 Nov 2017 | 500 | 0.857143 | 500.0 |
Robb | 03 Apr 2017 | 28 Apr 2017 | 200 | 0.071429 | 200.0 |
Daenerys | 18 Apr 2017 | None | 500 | 0.710317 | 250.0 |
Tommen | 18 Jul 2017 | 29 Dec 2017 | 800 | 0.460317 | 0.0 |
Cersei | 30 Dec 2017 | None | 1000 | 0.000000 | 0.0 |
Jon | 01 Feb 2018 | None | 100 | 0.000000 | 0.0 |
Analysis
The same methods are used as with calculating the values for Worked_in_2017
column. Finally, the portion of the April 2017 taken by the tenure of each employee is multiplied by the salary rate of the employee.
Note that Daenerys has worked only a part of April 2017 - exactly a half (9 of 18 working days), therefore she is paid proportionally. However, Robb checked out all working days in the months because the first, the second, the 29th and the 30th of April - all fall on the weekends. Hence, Robb receives the full monthly salary.
Per-shift wage¶
Suppose that the staff of Kings and Queens Ltd. forms the team ‘A’ of the call center operators. The operators are paid 80 coins per shift. The task is to calculate the wages payable for a week of 17 April 2017.
(For your reference, the schedule of team’s shifts for this week has been generated in an earlier example; it contains 6 shifts.)
[13]:
clnd = clnd_cc
pay_period = clnd('17 April 2017', period='W')
sdl_a = clnd.schedules['team_A']
shift_rate = 80
register['Wage_shifts'] = [
clnd(tenure).overlap(pay_period).count(schedule=sdl_a) * shift_rate
for tenure in zip(register.Enter, register.Leave)
]
register[['Enter', 'Leave', 'Wage_shifts']]
[13]:
Enter | Leave | Wage_shifts | |
---|---|---|---|
Name | |||
Doran | 01 Feb 2012 | 11 Nov 2017 | 480 |
Robert | 10 May 2012 | 01 Jan 2017 | 0 |
Joffrey | 03 Jan 2017 | 17 Jul 2017 | 480 |
Stannis | 02 Jan 2017 | 07 Nov 2017 | 480 |
Robb | 03 Apr 2017 | 28 Apr 2017 | 480 |
Daenerys | 18 Apr 2017 | None | 400 |
Tommen | 18 Jul 2017 | 29 Dec 2017 | 0 |
Cersei | 30 Dec 2017 | None | 0 |
Jon | 01 Feb 2018 | None | 0 |
Analysis
Four timeboard methods are used in this example:
Timeboard.get_interval()
is called in lines 3 and 8.clnd('17 April 2017', period='A')
returns an interval of shifts which belong to the calendar week 17-23 of April.clnd(tenure)
returns the period of time when the person has had a job in the company.Timeboard.schedules
is a dictionary of schedules registered for our timeboard. In line 4 the schedule for team ‘A’ is retrieved.Interval.overlap()
is a part of the chain of methods in line 8. It returns the interval that is the intersection of two intervals: the employee’s tenure and the pay period.Interval.count()
is the last method called in line 8. It returns the number of on-duty workshifts in the given interval. Shifts are classified as “on duty” or “off duty” according to the schedule which is supplied to the method. By default, the method uses the default schedule of the timeboard. It would not be suitable for our purpose as under the default schedule every shift is on duty (the call center is always working). Hence we passed the specific schedule which selects only shifts labeled with ‘A’.
Hourly pay¶
Let us change the pay scheme. Suppose the operators are paid 10 coins per hour. The task is the same: calculate the wages payable for a week of 17 April 2017.
[14]:
clnd = clnd_cc
pay_period = clnd('17 April 2017', period='W')
sdl_a = clnd.schedules['team_A']
hourly_rate = 10
register['Wage_hours'] = [
clnd(tenure).overlap(pay_period).worktime(schedule=sdl_a) * hourly_rate
for tenure in zip(register.Enter, register.Leave)
]
register[['Enter', 'Leave', 'Wage_shifts', 'Wage_hours']]
[14]:
Enter | Leave | Wage_shifts | Wage_hours | |
---|---|---|---|---|
Name | ||||
Doran | 01 Feb 2012 | 11 Nov 2017 | 480 | 480.0 |
Robert | 10 May 2012 | 01 Jan 2017 | 0 | 0.0 |
Joffrey | 03 Jan 2017 | 17 Jul 2017 | 480 | 480.0 |
Stannis | 02 Jan 2017 | 07 Nov 2017 | 480 | 480.0 |
Robb | 03 Apr 2017 | 28 Apr 2017 | 480 | 480.0 |
Daenerys | 18 Apr 2017 | None | 400 | 420.0 |
Tommen | 18 Jul 2017 | 29 Dec 2017 | 0 | 0.0 |
Cersei | 30 Dec 2017 | None | 0 | 0.0 |
Jon | 01 Feb 2018 | None | 0 | 0.0 |
Analysis
This snippet is analogous to the previous example. The only change is that in line 8, instead of count()
, the last method called is Interval.worktime()
. With this timeboard worktime()
returns the total count of hours in all on-duty workshifts of the interval. As with count()
, a schedule is passed to worktime()
in order to tell on-duty shifts from off-duty ones.
Calculating bonus based on time worked¶
Source data:
- Staff register with dates of entering and leaving the company.
- Bonus coefficient.
- Bonus increment for each year worked.
The annual bonus is payable to the employees who have stayed in the company for a half of the year or more. The size of the bonus is the total annual salary multiplied by the bonus coefficient. The coefficient is increased for each full year spent with the company.
Task: For each employee calculate the bonus payable for the year 2017.
[15]:
# Housekeeping: remove the now irrelevant columns from the dataframe.
register.drop(['Wage_shifts', 'Wage_hours'], axis=1, inplace=True)
Let’s return Kings and Queens Ltd. to the standard office calendar.
As an intermediate step, we will find out how many years each employee has spent with the company by the end of the year 2017. The results will be stored in Total_yrs
column.
[16]:
clnd = clnd_uk
by_end_of_2017 = clnd((None, '31 Dec 2017'))
register['Total_yrs'] = [
clnd(tenure).overlap(by_end_of_2017).count_periods('A')
for tenure in zip(register.Enter, register.Leave)
]
register
[16]:
Enter | Leave | Rate | Worked_in_2017 | Salary_April | Total_yrs | |
---|---|---|---|---|---|---|
Name | ||||||
Doran | 01 Feb 2012 | 11 Nov 2017 | 700 | 0.869048 | 700.0 | 5.785714 |
Robert | 10 May 2012 | 01 Jan 2017 | 1000 | 0.000000 | 0.0 | 4.646825 |
Joffrey | 03 Jan 2017 | 17 Jul 2017 | 800 | 0.539683 | 800.0 | 0.539683 |
Stannis | 02 Jan 2017 | 07 Nov 2017 | 500 | 0.857143 | 500.0 | 0.857143 |
Robb | 03 Apr 2017 | 28 Apr 2017 | 200 | 0.071429 | 200.0 | 0.071429 |
Daenerys | 18 Apr 2017 | None | 500 | 0.710317 | 250.0 | 0.710317 |
Tommen | 18 Jul 2017 | 29 Dec 2017 | 800 | 0.460317 | 0.0 | 0.460317 |
Cersei | 30 Dec 2017 | None | 1000 | 0.000000 | 0.0 | 0.000000 |
Jon | 01 Feb 2018 | None | 100 | 0.000000 | 0.0 | 0.000000 |
Analysis
This snippet reiterates the composition of the two previous examples. There are two modifications:
clnd((None, '31 Dec 2017'))
returns the interval from the beginning of the calendar to the day of 31 Dec 2017 inclusive. By passing this interval tooverlap()
called on the tenure in line 5 we effectively drop the part of the employee’s tenure which extends into 2018 and beyond.- The last method in the method chain in line 5 is
Interval.count_periods()
which calculates how many years fit into the interval. The method can see only business days. This is why the result for Cersei is zero in spite of the fact that she joined the company in 2017. Both 30 and 31 of December 2017 were days off, so she checked out no working days in 2017.
The rest of the code invokes the methods already made appearance in the earlier examples. In line 4 we select employees who are eligible for the bonus. In lines 6-10 their annual salaries for the year 2017 are calculated assuming that salary is paid monthly. In line 12 the bonus coefficient is incremented by taking into account the total number of years worked. In line 13 the resulting coefficient is applied to the annual salaries.
[17]:
bonus_coefficient = 0.5
bonus_increment_per_year = 0.1
eligibles = register[register.Worked_in_2017 >= 0.5]
y2017 = clnd('2017', period='A')
annual_salary = [
clnd(tenure).overlap(y2017).count_periods('M')
for tenure in zip(eligibles.Enter, eligibles.Leave)
] * eligibles.Rate
register['Bonus'] = (bonus_coefficient*(1 + bonus_increment_per_year*eligibles.Total_yrs)) \
* annual_salary
register.Bonus = register.Bonus.fillna(0).round(2)
register
[17]:
Enter | Leave | Rate | Worked_in_2017 | Salary_April | Total_yrs | Bonus | |
---|---|---|---|---|---|---|---|
Name | |||||||
Doran | 01 Feb 2012 | 11 Nov 2017 | 700 | 0.869048 | 700.0 | 5.785714 | 5725.91 |
Robert | 10 May 2012 | 01 Jan 2017 | 1000 | 0.000000 | 0.0 | 4.646825 | 0.00 |
Joffrey | 03 Jan 2017 | 17 Jul 2017 | 800 | 0.539683 | 800.0 | 0.539683 | 2750.36 |
Stannis | 02 Jan 2017 | 07 Nov 2017 | 500 | 0.857143 | 500.0 | 0.857143 | 2775.97 |
Robb | 03 Apr 2017 | 28 Apr 2017 | 200 | 0.071429 | 200.0 | 0.071429 | 0.00 |
Daenerys | 18 Apr 2017 | None | 500 | 0.710317 | 250.0 | 0.710317 | 2275.94 |
Tommen | 18 Jul 2017 | 29 Dec 2017 | 800 | 0.460317 | 0.0 | 0.460317 | 0.00 |
Cersei | 30 Dec 2017 | None | 1000 | 0.000000 | 0.0 | 0.000000 | 0.00 |
Jon | 01 Feb 2018 | None | 100 | 0.000000 | 0.0 | 0.000000 | 0.00 |