{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Common Use Cases"
]
},
{
"cell_type": "raw",
"metadata": {
"raw_mimetype": "text/restructuredtext"
},
"source": [
".. This cell is used for generating HTML. Ignore when in notebook. \n",
"\n",
".. contents:: Table of Contents\n",
" :depth: 2\n",
" :local:\n",
" :backlinks: none"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This document contains code snippets for the common use cases of `timeboard` library. It is also available as a [jupyter notebook](_downloads/use_cases.ipynb).\n",
"\n",
"The import statements for all examples are:"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
" import timeboard as tb\n",
" import pandas as pd"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Note:** We will use `pandas` dataframes to store the data we work with."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Setting up the calendar\n",
"\n",
"\n",
"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](making_a_timeboard.rst) section. Calculations are performed similarly for any type of timeboard.\n",
"\n",
"To obtain a standard business day calendar we use the built-ins:"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
" import timeboard.calendars.RU as RU\n",
" clnd_ru = RU.Weekly8x5()\n",
"\n",
" import timeboard.calendars.UK as UK\n",
" clnd_uk = UK.Weekly8x5(country='england')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"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.\n",
"\n",
"**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.\n"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" ws_ref | \n",
" start | \n",
" duration | \n",
" end | \n",
" label | \n",
" on_duty | \n",
"
\n",
" \n",
" loc | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 6316 | \n",
" 2017-04-17 | \n",
" 2017-04-17 | \n",
" 1 | \n",
" 2017-04-17 | \n",
" 0 | \n",
" False | \n",
"
\n",
" \n",
" 6317 | \n",
" 2017-04-18 | \n",
" 2017-04-18 | \n",
" 1 | \n",
" 2017-04-18 | \n",
" 8 | \n",
" True | \n",
"
\n",
" \n",
" 6318 | \n",
" 2017-04-19 | \n",
" 2017-04-19 | \n",
" 1 | \n",
" 2017-04-19 | \n",
" 8 | \n",
" True | \n",
"
\n",
" \n",
" 6319 | \n",
" 2017-04-20 | \n",
" 2017-04-20 | \n",
" 1 | \n",
" 2017-04-20 | \n",
" 8 | \n",
" True | \n",
"
\n",
" \n",
" 6320 | \n",
" 2017-04-21 | \n",
" 2017-04-21 | \n",
" 1 | \n",
" 2017-04-21 | \n",
" 8 | \n",
" True | \n",
"
\n",
" \n",
" 6321 | \n",
" 2017-04-22 | \n",
" 2017-04-22 | \n",
" 1 | \n",
" 2017-04-22 | \n",
" 0 | \n",
" False | \n",
"
\n",
" \n",
" 6322 | \n",
" 2017-04-23 | \n",
" 2017-04-23 | \n",
" 1 | \n",
" 2017-04-23 | \n",
" 0 | \n",
" False | \n",
"
\n",
" \n",
" 6323 | \n",
" 2017-04-24 | \n",
" 2017-04-24 | \n",
" 1 | \n",
" 2017-04-24 | \n",
" 8 | \n",
" True | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" ws_ref start duration end label on_duty\n",
"loc \n",
"6316 2017-04-17 2017-04-17 1 2017-04-17 0 False\n",
"6317 2017-04-18 2017-04-18 1 2017-04-18 8 True\n",
"6318 2017-04-19 2017-04-19 1 2017-04-19 8 True\n",
"6319 2017-04-20 2017-04-20 1 2017-04-20 8 True\n",
"6320 2017-04-21 2017-04-21 1 2017-04-21 8 True\n",
"6321 2017-04-22 2017-04-22 1 2017-04-22 0 False\n",
"6322 2017-04-23 2017-04-23 1 2017-04-23 0 False\n",
"6323 2017-04-24 2017-04-24 1 2017-04-24 8 True"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
" clnd_uk(('17 Apr 2017', '24 Apr 2017')).to_dataframe()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"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."
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
" teams = ['A', 'B', 'C', 'D']\n",
" day_parts = tb.Marker(each='D', \n",
" at=[{'hours':2}, {'hours':8}, {'hours':18}])\n",
" shifts = tb.Organizer(marker=day_parts, structure=teams)\n",
" clnd_cc = tb.Timeboard(base_unit_freq='H', \n",
" start='01 Jan 2009 02:00', end='01 Jan 2019 01:59',\n",
" layout=shifts)\n",
" for team in teams:\n",
" clnd_cc.add_schedule(name='team_'+ team, \n",
" selector=lambda label, team=team: label==team)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"A sample of `clnd_cc` for the week of 17 April 2017 is shown below."
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" ws_ref | \n",
" start | \n",
" duration | \n",
" end | \n",
" label | \n",
" on_duty | \n",
" team_A | \n",
" team_B | \n",
" team_C | \n",
" team_D | \n",
"
\n",
" \n",
" loc | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 9084 | \n",
" 2017-04-17 02:00:00 | \n",
" 2017-04-17 02:00:00 | \n",
" 6 | \n",
" 2017-04-17 07:59:59 | \n",
" A | \n",
" True | \n",
" True | \n",
" False | \n",
" False | \n",
" False | \n",
"
\n",
" \n",
" 9085 | \n",
" 2017-04-17 08:00:00 | \n",
" 2017-04-17 08:00:00 | \n",
" 10 | \n",
" 2017-04-17 17:59:59 | \n",
" B | \n",
" True | \n",
" False | \n",
" True | \n",
" False | \n",
" False | \n",
"
\n",
" \n",
" 9086 | \n",
" 2017-04-17 18:00:00 | \n",
" 2017-04-17 18:00:00 | \n",
" 8 | \n",
" 2017-04-18 01:59:59 | \n",
" C | \n",
" True | \n",
" False | \n",
" False | \n",
" True | \n",
" False | \n",
"
\n",
" \n",
" 9087 | \n",
" 2017-04-18 02:00:00 | \n",
" 2017-04-18 02:00:00 | \n",
" 6 | \n",
" 2017-04-18 07:59:59 | \n",
" D | \n",
" True | \n",
" False | \n",
" False | \n",
" False | \n",
" True | \n",
"
\n",
" \n",
" 9088 | \n",
" 2017-04-18 08:00:00 | \n",
" 2017-04-18 08:00:00 | \n",
" 10 | \n",
" 2017-04-18 17:59:59 | \n",
" A | \n",
" True | \n",
" True | \n",
" False | \n",
" False | \n",
" False | \n",
"
\n",
" \n",
" 9089 | \n",
" 2017-04-18 18:00:00 | \n",
" 2017-04-18 18:00:00 | \n",
" 8 | \n",
" 2017-04-19 01:59:59 | \n",
" B | \n",
" True | \n",
" False | \n",
" True | \n",
" False | \n",
" False | \n",
"
\n",
" \n",
" 9090 | \n",
" 2017-04-19 02:00:00 | \n",
" 2017-04-19 02:00:00 | \n",
" 6 | \n",
" 2017-04-19 07:59:59 | \n",
" C | \n",
" True | \n",
" False | \n",
" False | \n",
" True | \n",
" False | \n",
"
\n",
" \n",
" 9091 | \n",
" 2017-04-19 08:00:00 | \n",
" 2017-04-19 08:00:00 | \n",
" 10 | \n",
" 2017-04-19 17:59:59 | \n",
" D | \n",
" True | \n",
" False | \n",
" False | \n",
" False | \n",
" True | \n",
"
\n",
" \n",
" 9092 | \n",
" 2017-04-19 18:00:00 | \n",
" 2017-04-19 18:00:00 | \n",
" 8 | \n",
" 2017-04-20 01:59:59 | \n",
" A | \n",
" True | \n",
" True | \n",
" False | \n",
" False | \n",
" False | \n",
"
\n",
" \n",
" 9093 | \n",
" 2017-04-20 02:00:00 | \n",
" 2017-04-20 02:00:00 | \n",
" 6 | \n",
" 2017-04-20 07:59:59 | \n",
" B | \n",
" True | \n",
" False | \n",
" True | \n",
" False | \n",
" False | \n",
"
\n",
" \n",
" 9094 | \n",
" 2017-04-20 08:00:00 | \n",
" 2017-04-20 08:00:00 | \n",
" 10 | \n",
" 2017-04-20 17:59:59 | \n",
" C | \n",
" True | \n",
" False | \n",
" False | \n",
" True | \n",
" False | \n",
"
\n",
" \n",
" 9095 | \n",
" 2017-04-20 18:00:00 | \n",
" 2017-04-20 18:00:00 | \n",
" 8 | \n",
" 2017-04-21 01:59:59 | \n",
" D | \n",
" True | \n",
" False | \n",
" False | \n",
" False | \n",
" True | \n",
"
\n",
" \n",
" 9096 | \n",
" 2017-04-21 02:00:00 | \n",
" 2017-04-21 02:00:00 | \n",
" 6 | \n",
" 2017-04-21 07:59:59 | \n",
" A | \n",
" True | \n",
" True | \n",
" False | \n",
" False | \n",
" False | \n",
"
\n",
" \n",
" 9097 | \n",
" 2017-04-21 08:00:00 | \n",
" 2017-04-21 08:00:00 | \n",
" 10 | \n",
" 2017-04-21 17:59:59 | \n",
" B | \n",
" True | \n",
" False | \n",
" True | \n",
" False | \n",
" False | \n",
"
\n",
" \n",
" 9098 | \n",
" 2017-04-21 18:00:00 | \n",
" 2017-04-21 18:00:00 | \n",
" 8 | \n",
" 2017-04-22 01:59:59 | \n",
" C | \n",
" True | \n",
" False | \n",
" False | \n",
" True | \n",
" False | \n",
"
\n",
" \n",
" 9099 | \n",
" 2017-04-22 02:00:00 | \n",
" 2017-04-22 02:00:00 | \n",
" 6 | \n",
" 2017-04-22 07:59:59 | \n",
" D | \n",
" True | \n",
" False | \n",
" False | \n",
" False | \n",
" True | \n",
"
\n",
" \n",
" 9100 | \n",
" 2017-04-22 08:00:00 | \n",
" 2017-04-22 08:00:00 | \n",
" 10 | \n",
" 2017-04-22 17:59:59 | \n",
" A | \n",
" True | \n",
" True | \n",
" False | \n",
" False | \n",
" False | \n",
"
\n",
" \n",
" 9101 | \n",
" 2017-04-22 18:00:00 | \n",
" 2017-04-22 18:00:00 | \n",
" 8 | \n",
" 2017-04-23 01:59:59 | \n",
" B | \n",
" True | \n",
" False | \n",
" True | \n",
" False | \n",
" False | \n",
"
\n",
" \n",
" 9102 | \n",
" 2017-04-23 02:00:00 | \n",
" 2017-04-23 02:00:00 | \n",
" 6 | \n",
" 2017-04-23 07:59:59 | \n",
" C | \n",
" True | \n",
" False | \n",
" False | \n",
" True | \n",
" False | \n",
"
\n",
" \n",
" 9103 | \n",
" 2017-04-23 08:00:00 | \n",
" 2017-04-23 08:00:00 | \n",
" 10 | \n",
" 2017-04-23 17:59:59 | \n",
" D | \n",
" True | \n",
" False | \n",
" False | \n",
" False | \n",
" True | \n",
"
\n",
" \n",
" 9104 | \n",
" 2017-04-23 18:00:00 | \n",
" 2017-04-23 18:00:00 | \n",
" 8 | \n",
" 2017-04-24 01:59:59 | \n",
" A | \n",
" True | \n",
" True | \n",
" False | \n",
" False | \n",
" False | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" ws_ref start duration end \\\n",
"loc \n",
"9084 2017-04-17 02:00:00 2017-04-17 02:00:00 6 2017-04-17 07:59:59 \n",
"9085 2017-04-17 08:00:00 2017-04-17 08:00:00 10 2017-04-17 17:59:59 \n",
"9086 2017-04-17 18:00:00 2017-04-17 18:00:00 8 2017-04-18 01:59:59 \n",
"9087 2017-04-18 02:00:00 2017-04-18 02:00:00 6 2017-04-18 07:59:59 \n",
"9088 2017-04-18 08:00:00 2017-04-18 08:00:00 10 2017-04-18 17:59:59 \n",
"9089 2017-04-18 18:00:00 2017-04-18 18:00:00 8 2017-04-19 01:59:59 \n",
"9090 2017-04-19 02:00:00 2017-04-19 02:00:00 6 2017-04-19 07:59:59 \n",
"9091 2017-04-19 08:00:00 2017-04-19 08:00:00 10 2017-04-19 17:59:59 \n",
"9092 2017-04-19 18:00:00 2017-04-19 18:00:00 8 2017-04-20 01:59:59 \n",
"9093 2017-04-20 02:00:00 2017-04-20 02:00:00 6 2017-04-20 07:59:59 \n",
"9094 2017-04-20 08:00:00 2017-04-20 08:00:00 10 2017-04-20 17:59:59 \n",
"9095 2017-04-20 18:00:00 2017-04-20 18:00:00 8 2017-04-21 01:59:59 \n",
"9096 2017-04-21 02:00:00 2017-04-21 02:00:00 6 2017-04-21 07:59:59 \n",
"9097 2017-04-21 08:00:00 2017-04-21 08:00:00 10 2017-04-21 17:59:59 \n",
"9098 2017-04-21 18:00:00 2017-04-21 18:00:00 8 2017-04-22 01:59:59 \n",
"9099 2017-04-22 02:00:00 2017-04-22 02:00:00 6 2017-04-22 07:59:59 \n",
"9100 2017-04-22 08:00:00 2017-04-22 08:00:00 10 2017-04-22 17:59:59 \n",
"9101 2017-04-22 18:00:00 2017-04-22 18:00:00 8 2017-04-23 01:59:59 \n",
"9102 2017-04-23 02:00:00 2017-04-23 02:00:00 6 2017-04-23 07:59:59 \n",
"9103 2017-04-23 08:00:00 2017-04-23 08:00:00 10 2017-04-23 17:59:59 \n",
"9104 2017-04-23 18:00:00 2017-04-23 18:00:00 8 2017-04-24 01:59:59 \n",
"\n",
" label on_duty team_A team_B team_C team_D \n",
"loc \n",
"9084 A True True False False False \n",
"9085 B True False True False False \n",
"9086 C True False False True False \n",
"9087 D True False False False True \n",
"9088 A True True False False False \n",
"9089 B True False True False False \n",
"9090 C True False False True False \n",
"9091 D True False False False True \n",
"9092 A True True False False False \n",
"9093 B True False True False False \n",
"9094 C True False False True False \n",
"9095 D True False False False True \n",
"9096 A True True False False False \n",
"9097 B True False True False False \n",
"9098 C True False False True False \n",
"9099 D True False False False True \n",
"9100 A True True False False False \n",
"9101 B True False True False False \n",
"9102 C True False False True False \n",
"9103 D True False False False True \n",
"9104 A True True False False False "
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
" clnd_cc(('17 Apr 2017 2:00', '24 Apr 2017')).to_dataframe()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Determining deadlines\n",
"\n",
"**Source data:**\n",
"\n",
"- Project timetable defined in terms of business days allotted to complete each stage of the project.\n",
"- Start date of the project.\n"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Stage | \n",
" Duration | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" Development | \n",
" 14 | \n",
"
\n",
" \n",
" 1 | \n",
" Acceptance | \n",
" 2 | \n",
"
\n",
" \n",
" 2 | \n",
" Deployment | \n",
" 3 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Stage Duration\n",
"0 Development 14\n",
"1 Acceptance 2\n",
"2 Deployment 3"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
" project_start = '01 Jan 2018'\n",
" project_timetable = pd.DataFrame(data=[ \n",
" ['Development', 14],\n",
" ['Acceptance', 2],\n",
" ['Deployment', 3] \n",
" ],\n",
" columns=['Stage', 'Duration'])\n",
" project_timetable"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"\n",
"The company works standard business hours. The country is Russia.\n",
"\n",
"**Task:** Obtain the project deadlines as the calendar dates. \n"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Stage | \n",
" Duration | \n",
" Start | \n",
" Deadline | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" Development | \n",
" 14 | \n",
" 2018-01-09 | \n",
" 2018-01-26 | \n",
"
\n",
" \n",
" 1 | \n",
" Acceptance | \n",
" 2 | \n",
" 2018-01-29 | \n",
" 2018-01-30 | \n",
"
\n",
" \n",
" 2 | \n",
" Deployment | \n",
" 3 | \n",
" 2018-01-31 | \n",
" 2018-02-02 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Stage Duration Start Deadline\n",
"0 Development 14 2018-01-09 2018-01-26\n",
"1 Acceptance 2 2018-01-29 2018-01-30\n",
"2 Deployment 3 2018-01-31 2018-02-02"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
" clnd = clnd_ru\n",
" start_dates, end_dates = [], []\n",
" for stage_duration in project_timetable['Duration']:\n",
" if not start_dates: \n",
" start_dates = [clnd(project_start).rollforward()]\n",
" else:\n",
" start_dates.append(end_dates[-1] + 1)\n",
" end_dates.append(start_dates[-1] + (stage_duration - 1)) \n",
"\n",
" project_timetable['Start'] = [day.to_timestamp() for day in start_dates]\n",
" project_timetable['Deadline'] = [day.to_timestamp() for day in end_dates]\n",
"\n",
" project_timetable"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Analysis**"
]
},
{
"cell_type": "raw",
"metadata": {
"raw_mimetype": "text/restructuredtext"
},
"source": [
"\n",
"Two :py:mod:`timeboard` methods are used in this example: \n",
"\n",
"- :py:meth:`.Timeboard.get_workshift` is called in line 5 disguised as ``clnd(project_start)``. It puts the start date of the project into the context of the timeline of the calendar and returns the corresponding workshift.\n",
"\n",
"- :py:meth:`.Workshift.rollforward` is called by name in line 5 and by proxy of operator ``+`` in lines 7 and 8.\n",
"\n",
"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.\n",
"\n",
"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."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Generating shift schedule\n",
"\n",
"**Source data:** timeboard of all shifts in a call center.\n",
"\n",
"**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).\n"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Start | \n",
" Duration | \n",
" End | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 2017-04-17 02:00:00 | \n",
" 6 | \n",
" 2017-04-17 08:00:00 | \n",
"
\n",
" \n",
" 1 | \n",
" 2017-04-18 08:00:00 | \n",
" 10 | \n",
" 2017-04-18 18:00:00 | \n",
"
\n",
" \n",
" 2 | \n",
" 2017-04-19 18:00:00 | \n",
" 8 | \n",
" 2017-04-20 02:00:00 | \n",
"
\n",
" \n",
" 3 | \n",
" 2017-04-21 02:00:00 | \n",
" 6 | \n",
" 2017-04-21 08:00:00 | \n",
"
\n",
" \n",
" 4 | \n",
" 2017-04-22 08:00:00 | \n",
" 10 | \n",
" 2017-04-22 18:00:00 | \n",
"
\n",
" \n",
" 5 | \n",
" 2017-04-23 18:00:00 | \n",
" 8 | \n",
" 2017-04-24 02:00:00 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Start Duration End\n",
"0 2017-04-17 02:00:00 6 2017-04-17 08:00:00\n",
"1 2017-04-18 08:00:00 10 2017-04-18 18:00:00\n",
"2 2017-04-19 18:00:00 8 2017-04-20 02:00:00\n",
"3 2017-04-21 02:00:00 6 2017-04-21 08:00:00\n",
"4 2017-04-22 08:00:00 10 2017-04-22 18:00:00\n",
"5 2017-04-23 18:00:00 8 2017-04-24 02:00:00"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
" clnd = clnd_cc\n",
" schedule = clnd.schedules['team_A']\n",
" period = clnd('17 April 2017', period='W')\n",
" shifts = [\n",
" [ws.start_time, ws.duration, ws.end_time.ceil(clnd.base_unit_freq)]\n",
" for ws in period.workshifts(schedule=schedule)\n",
" ]\n",
" \n",
" pd.DataFrame(shifts, columns=['Start', 'Duration', 'End'])"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Analysis**"
]
},
{
"cell_type": "raw",
"metadata": {
"raw_mimetype": "text/restructuredtext"
},
"source": [
"\n",
"- :py:attr:`.Timeboard.schedules` is a dictionary of schedules registered for our timeboard. In line 2 the schedule for team 'A' is retrieved.\n",
"\n",
"- In line 3 ``clnd('17 April 2017', period='A')`` is a call of :py:meth:`.Timeboard.get_interval` returning an interval of shifts which belong to the calendar week 17-23 of April.\n",
"\n",
"- :py:meth:`.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'.\n",
"\n",
"- :py:attr:`start_time`, :py:attr:`duration`, and :py:attr:`end_time` are workshift attributes. We use :py:meth:`pandas.Timestamp.ceil` to round up the end time of a workshift to the beginning of the next base unit of the timeboard.\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Average annual headcount\n",
"\n",
"The following examples are based on a fictitious company *Kings and Queens Ltd.*\n",
"\n",
"**Source data:** staff register for *Kings and Queens Ltd.* containing for each employee:\n",
"\n",
"- dates of entering and leaving the company\n",
"- salary rate\n",
"\n",
"The value of `None` as the leaving date means that this person is still with the company."
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Enter | \n",
" Leave | \n",
" Rate | \n",
"
\n",
" \n",
" Name | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" Doran | \n",
" 01 Feb 2012 | \n",
" 11 Nov 2017 | \n",
" 700 | \n",
"
\n",
" \n",
" Robert | \n",
" 10 May 2012 | \n",
" 01 Jan 2017 | \n",
" 1000 | \n",
"
\n",
" \n",
" Joffrey | \n",
" 03 Jan 2017 | \n",
" 17 Jul 2017 | \n",
" 800 | \n",
"
\n",
" \n",
" Stannis | \n",
" 02 Jan 2017 | \n",
" 07 Nov 2017 | \n",
" 500 | \n",
"
\n",
" \n",
" Robb | \n",
" 03 Apr 2017 | \n",
" 28 Apr 2017 | \n",
" 200 | \n",
"
\n",
" \n",
" Daenerys | \n",
" 18 Apr 2017 | \n",
" None | \n",
" 500 | \n",
"
\n",
" \n",
" Tommen | \n",
" 18 Jul 2017 | \n",
" 29 Dec 2017 | \n",
" 800 | \n",
"
\n",
" \n",
" Cersei | \n",
" 30 Dec 2017 | \n",
" None | \n",
" 1000 | \n",
"
\n",
" \n",
" Jon | \n",
" 01 Feb 2018 | \n",
" None | \n",
" 100 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Enter Leave Rate\n",
"Name \n",
"Doran 01 Feb 2012 11 Nov 2017 700\n",
"Robert 10 May 2012 01 Jan 2017 1000\n",
"Joffrey 03 Jan 2017 17 Jul 2017 800\n",
"Stannis 02 Jan 2017 07 Nov 2017 500\n",
"Robb 03 Apr 2017 28 Apr 2017 200\n",
"Daenerys 18 Apr 2017 None 500\n",
"Tommen 18 Jul 2017 29 Dec 2017 800\n",
"Cersei 30 Dec 2017 None 1000\n",
"Jon 01 Feb 2018 None 100"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
" staff = [\n",
" ['Doran', '01 Feb 2012', '11 Nov 2017', 700],\n",
" ['Robert', '10 May 2012', '01 Jan 2017', 1000],\n",
" ['Joffrey', '03 Jan 2017', '17 Jul 2017', 800 ],\n",
" ['Stannis', '02 Jan 2017', '07 Nov 2017', 500],\n",
" ['Robb', '03 Apr 2017', '28 Apr 2017', 200],\n",
" ['Daenerys', '18 Apr 2017', None, 500], \n",
" ['Tommen', '18 Jul 2017', '29 Dec 2017', 800],\n",
" ['Cersei', '30 Dec 2017', None, 1000],\n",
" ['Jon', '01 Feb 2018', None, 100]\n",
" ]\n",
" register = pd.DataFrame(data=staff, \n",
" columns=['Name', 'Enter', 'Leave', 'Rate']).set_index('Name')\n",
" \n",
" register"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Task:** Calculate the average annual headcount of the company in 2017.\n",
"\n",
"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'."
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Enter | \n",
" Leave | \n",
" Rate | \n",
" Worked_in_2017 | \n",
"
\n",
" \n",
" Name | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" Doran | \n",
" 01 Feb 2012 | \n",
" 11 Nov 2017 | \n",
" 700 | \n",
" 0.869048 | \n",
"
\n",
" \n",
" Robert | \n",
" 10 May 2012 | \n",
" 01 Jan 2017 | \n",
" 1000 | \n",
" 0.000000 | \n",
"
\n",
" \n",
" Joffrey | \n",
" 03 Jan 2017 | \n",
" 17 Jul 2017 | \n",
" 800 | \n",
" 0.539683 | \n",
"
\n",
" \n",
" Stannis | \n",
" 02 Jan 2017 | \n",
" 07 Nov 2017 | \n",
" 500 | \n",
" 0.857143 | \n",
"
\n",
" \n",
" Robb | \n",
" 03 Apr 2017 | \n",
" 28 Apr 2017 | \n",
" 200 | \n",
" 0.071429 | \n",
"
\n",
" \n",
" Daenerys | \n",
" 18 Apr 2017 | \n",
" None | \n",
" 500 | \n",
" 0.710317 | \n",
"
\n",
" \n",
" Tommen | \n",
" 18 Jul 2017 | \n",
" 29 Dec 2017 | \n",
" 800 | \n",
" 0.460317 | \n",
"
\n",
" \n",
" Cersei | \n",
" 30 Dec 2017 | \n",
" None | \n",
" 1000 | \n",
" 0.000000 | \n",
"
\n",
" \n",
" Jon | \n",
" 01 Feb 2018 | \n",
" None | \n",
" 100 | \n",
" 0.000000 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Enter Leave Rate Worked_in_2017\n",
"Name \n",
"Doran 01 Feb 2012 11 Nov 2017 700 0.869048\n",
"Robert 10 May 2012 01 Jan 2017 1000 0.000000\n",
"Joffrey 03 Jan 2017 17 Jul 2017 800 0.539683\n",
"Stannis 02 Jan 2017 07 Nov 2017 500 0.857143\n",
"Robb 03 Apr 2017 28 Apr 2017 200 0.071429\n",
"Daenerys 18 Apr 2017 None 500 0.710317\n",
"Tommen 18 Jul 2017 29 Dec 2017 800 0.460317\n",
"Cersei 30 Dec 2017 None 1000 0.000000\n",
"Jon 01 Feb 2018 None 100 0.000000"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
" clnd = clnd_uk\n",
" \n",
" y2017 = clnd('2017', period='A')\n",
" register['Worked_in_2017'] = [\n",
" clnd(tenure) / y2017 for tenure in zip(register.Enter, register.Leave)\n",
" ]\n",
" \n",
" register"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Analysis**"
]
},
{
"cell_type": "raw",
"metadata": {
"raw_mimetype": "text/restructuredtext"
},
"source": [
"\n",
"Two :py:mod:`timeboard` methods are used in this example: \n",
"\n",
"- :py:meth:`.Timeboard.get_interval` is called in line 3 disguised as ``clnd('2017', period='A')`` and in line 5 as ``clnd(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. \n",
"\n",
"- :py:meth:`.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.\n",
"\n",
"**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.\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The last step in calculating the average annual headcount in 2017 is trivial. We sum up all values in 'Worked_in_2017' column."
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"3.5079365079365079"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
" headcount = register.Worked_in_2017.sum()\n",
" headcount"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Calculating wages and salaries payable\n",
"\n",
"**Source data:** \n",
"\n",
"- Staff register with dates of entering and leaving the company and wage/salary rates.\n",
"- Pay period.\n",
"\n",
"**Task:** For each employee determine the amount of wage/salary payable in the given pay period.\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Periodic salary\n",
"\n",
"Suppose salary is paid monthly. Below is the calculation of the salaries payable to the employees of *Kings and Queens Ltd.* in April 2017."
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Enter | \n",
" Leave | \n",
" Rate | \n",
" Worked_in_2017 | \n",
" Salary_April | \n",
"
\n",
" \n",
" Name | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" Doran | \n",
" 01 Feb 2012 | \n",
" 11 Nov 2017 | \n",
" 700 | \n",
" 0.869048 | \n",
" 700.0 | \n",
"
\n",
" \n",
" Robert | \n",
" 10 May 2012 | \n",
" 01 Jan 2017 | \n",
" 1000 | \n",
" 0.000000 | \n",
" 0.0 | \n",
"
\n",
" \n",
" Joffrey | \n",
" 03 Jan 2017 | \n",
" 17 Jul 2017 | \n",
" 800 | \n",
" 0.539683 | \n",
" 800.0 | \n",
"
\n",
" \n",
" Stannis | \n",
" 02 Jan 2017 | \n",
" 07 Nov 2017 | \n",
" 500 | \n",
" 0.857143 | \n",
" 500.0 | \n",
"
\n",
" \n",
" Robb | \n",
" 03 Apr 2017 | \n",
" 28 Apr 2017 | \n",
" 200 | \n",
" 0.071429 | \n",
" 200.0 | \n",
"
\n",
" \n",
" Daenerys | \n",
" 18 Apr 2017 | \n",
" None | \n",
" 500 | \n",
" 0.710317 | \n",
" 250.0 | \n",
"
\n",
" \n",
" Tommen | \n",
" 18 Jul 2017 | \n",
" 29 Dec 2017 | \n",
" 800 | \n",
" 0.460317 | \n",
" 0.0 | \n",
"
\n",
" \n",
" Cersei | \n",
" 30 Dec 2017 | \n",
" None | \n",
" 1000 | \n",
" 0.000000 | \n",
" 0.0 | \n",
"
\n",
" \n",
" Jon | \n",
" 01 Feb 2018 | \n",
" None | \n",
" 100 | \n",
" 0.000000 | \n",
" 0.0 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Enter Leave Rate Worked_in_2017 Salary_April\n",
"Name \n",
"Doran 01 Feb 2012 11 Nov 2017 700 0.869048 700.0\n",
"Robert 10 May 2012 01 Jan 2017 1000 0.000000 0.0\n",
"Joffrey 03 Jan 2017 17 Jul 2017 800 0.539683 800.0\n",
"Stannis 02 Jan 2017 07 Nov 2017 500 0.857143 500.0\n",
"Robb 03 Apr 2017 28 Apr 2017 200 0.071429 200.0\n",
"Daenerys 18 Apr 2017 None 500 0.710317 250.0\n",
"Tommen 18 Jul 2017 29 Dec 2017 800 0.460317 0.0\n",
"Cersei 30 Dec 2017 None 1000 0.000000 0.0\n",
"Jon 01 Feb 2018 None 100 0.000000 0.0"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
" pay_period = clnd('April 2017', period='M')\n",
" register['Salary_April'] = [\n",
" clnd(tenure) / pay_period for tenure in zip(register.Enter, register.Leave)\n",
" ] * register.Rate\n",
" \n",
" register"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Analysis**\n",
"\n",
"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.\n",
"\n",
"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."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Per-shift wage\n",
"\n",
"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. \n",
"\n",
"(For your reference, the schedule of team's shifts for this week has been generated in an earlier example; it contains 6 shifts.)"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Enter | \n",
" Leave | \n",
" Wage_shifts | \n",
"
\n",
" \n",
" Name | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" Doran | \n",
" 01 Feb 2012 | \n",
" 11 Nov 2017 | \n",
" 480 | \n",
"
\n",
" \n",
" Robert | \n",
" 10 May 2012 | \n",
" 01 Jan 2017 | \n",
" 0 | \n",
"
\n",
" \n",
" Joffrey | \n",
" 03 Jan 2017 | \n",
" 17 Jul 2017 | \n",
" 480 | \n",
"
\n",
" \n",
" Stannis | \n",
" 02 Jan 2017 | \n",
" 07 Nov 2017 | \n",
" 480 | \n",
"
\n",
" \n",
" Robb | \n",
" 03 Apr 2017 | \n",
" 28 Apr 2017 | \n",
" 480 | \n",
"
\n",
" \n",
" Daenerys | \n",
" 18 Apr 2017 | \n",
" None | \n",
" 400 | \n",
"
\n",
" \n",
" Tommen | \n",
" 18 Jul 2017 | \n",
" 29 Dec 2017 | \n",
" 0 | \n",
"
\n",
" \n",
" Cersei | \n",
" 30 Dec 2017 | \n",
" None | \n",
" 0 | \n",
"
\n",
" \n",
" Jon | \n",
" 01 Feb 2018 | \n",
" None | \n",
" 0 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Enter Leave Wage_shifts\n",
"Name \n",
"Doran 01 Feb 2012 11 Nov 2017 480\n",
"Robert 10 May 2012 01 Jan 2017 0\n",
"Joffrey 03 Jan 2017 17 Jul 2017 480\n",
"Stannis 02 Jan 2017 07 Nov 2017 480\n",
"Robb 03 Apr 2017 28 Apr 2017 480\n",
"Daenerys 18 Apr 2017 None 400\n",
"Tommen 18 Jul 2017 29 Dec 2017 0\n",
"Cersei 30 Dec 2017 None 0\n",
"Jon 01 Feb 2018 None 0"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
" clnd = clnd_cc\n",
" \n",
" pay_period = clnd('17 April 2017', period='W')\n",
" sdl_a = clnd.schedules['team_A']\n",
" shift_rate = 80\n",
" \n",
" register['Wage_shifts'] = [\n",
" clnd(tenure).overlap(pay_period).count(schedule=sdl_a) * shift_rate \n",
" for tenure in zip(register.Enter, register.Leave)\n",
" ]\n",
" \n",
" register[['Enter', 'Leave', 'Wage_shifts']]\n",
" "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Analysis**"
]
},
{
"cell_type": "raw",
"metadata": {
"raw_mimetype": "text/restructuredtext"
},
"source": [
"\n",
"Four `timeboard` methods are used in this example: \n",
"\n",
"- :py:meth:`.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. \n",
"\n",
"- :py:attr:`.Timeboard.schedules` is a dictionary of schedules registered for our timeboard. In line 4 the schedule for team 'A' is retrieved.\n",
"\n",
"- :py:meth:`.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.\n",
"\n",
"- :py:meth:`.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'.\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Hourly pay\n",
"\n",
"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."
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Enter | \n",
" Leave | \n",
" Wage_shifts | \n",
" Wage_hours | \n",
"
\n",
" \n",
" Name | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" Doran | \n",
" 01 Feb 2012 | \n",
" 11 Nov 2017 | \n",
" 480 | \n",
" 480.0 | \n",
"
\n",
" \n",
" Robert | \n",
" 10 May 2012 | \n",
" 01 Jan 2017 | \n",
" 0 | \n",
" 0.0 | \n",
"
\n",
" \n",
" Joffrey | \n",
" 03 Jan 2017 | \n",
" 17 Jul 2017 | \n",
" 480 | \n",
" 480.0 | \n",
"
\n",
" \n",
" Stannis | \n",
" 02 Jan 2017 | \n",
" 07 Nov 2017 | \n",
" 480 | \n",
" 480.0 | \n",
"
\n",
" \n",
" Robb | \n",
" 03 Apr 2017 | \n",
" 28 Apr 2017 | \n",
" 480 | \n",
" 480.0 | \n",
"
\n",
" \n",
" Daenerys | \n",
" 18 Apr 2017 | \n",
" None | \n",
" 400 | \n",
" 420.0 | \n",
"
\n",
" \n",
" Tommen | \n",
" 18 Jul 2017 | \n",
" 29 Dec 2017 | \n",
" 0 | \n",
" 0.0 | \n",
"
\n",
" \n",
" Cersei | \n",
" 30 Dec 2017 | \n",
" None | \n",
" 0 | \n",
" 0.0 | \n",
"
\n",
" \n",
" Jon | \n",
" 01 Feb 2018 | \n",
" None | \n",
" 0 | \n",
" 0.0 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Enter Leave Wage_shifts Wage_hours\n",
"Name \n",
"Doran 01 Feb 2012 11 Nov 2017 480 480.0\n",
"Robert 10 May 2012 01 Jan 2017 0 0.0\n",
"Joffrey 03 Jan 2017 17 Jul 2017 480 480.0\n",
"Stannis 02 Jan 2017 07 Nov 2017 480 480.0\n",
"Robb 03 Apr 2017 28 Apr 2017 480 480.0\n",
"Daenerys 18 Apr 2017 None 400 420.0\n",
"Tommen 18 Jul 2017 29 Dec 2017 0 0.0\n",
"Cersei 30 Dec 2017 None 0 0.0\n",
"Jon 01 Feb 2018 None 0 0.0"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
" clnd = clnd_cc\n",
" \n",
" pay_period = clnd('17 April 2017', period='W')\n",
" sdl_a = clnd.schedules['team_A']\n",
" hourly_rate = 10\n",
" \n",
" register['Wage_hours'] = [\n",
" clnd(tenure).overlap(pay_period).worktime(schedule=sdl_a) * hourly_rate \n",
" for tenure in zip(register.Enter, register.Leave)\n",
" ]\n",
" \n",
" register[['Enter', 'Leave', 'Wage_shifts', 'Wage_hours']]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Analysis**"
]
},
{
"cell_type": "raw",
"metadata": {
"raw_mimetype": "text/restructuredtext"
},
"source": [
"\n",
"This snippet is analogous to the previous example. The only change is that in line 8, instead of ``count()``, the last method called is :py:meth:`.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.\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Calculating bonus based on time worked\n",
"\n",
"\n",
"**Source data:** \n",
"\n",
"- Staff register with dates of entering and leaving the company.\n",
"- Bonus coefficient.\n",
"- Bonus increment for each year worked.\n",
"\n",
"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. \n",
"\n",
"**Task:** For each employee calculate the bonus payable for the year 2017.\n"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
" # Housekeeping: remove the now irrelevant columns from the dataframe.\n",
" register.drop(['Wage_shifts', 'Wage_hours'], axis=1, inplace=True)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Let's return *Kings and Queens Ltd.* to the standard office calendar.\n",
"\n",
"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. "
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Enter | \n",
" Leave | \n",
" Rate | \n",
" Worked_in_2017 | \n",
" Salary_April | \n",
" Total_yrs | \n",
"
\n",
" \n",
" Name | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" Doran | \n",
" 01 Feb 2012 | \n",
" 11 Nov 2017 | \n",
" 700 | \n",
" 0.869048 | \n",
" 700.0 | \n",
" 5.785714 | \n",
"
\n",
" \n",
" Robert | \n",
" 10 May 2012 | \n",
" 01 Jan 2017 | \n",
" 1000 | \n",
" 0.000000 | \n",
" 0.0 | \n",
" 4.646825 | \n",
"
\n",
" \n",
" Joffrey | \n",
" 03 Jan 2017 | \n",
" 17 Jul 2017 | \n",
" 800 | \n",
" 0.539683 | \n",
" 800.0 | \n",
" 0.539683 | \n",
"
\n",
" \n",
" Stannis | \n",
" 02 Jan 2017 | \n",
" 07 Nov 2017 | \n",
" 500 | \n",
" 0.857143 | \n",
" 500.0 | \n",
" 0.857143 | \n",
"
\n",
" \n",
" Robb | \n",
" 03 Apr 2017 | \n",
" 28 Apr 2017 | \n",
" 200 | \n",
" 0.071429 | \n",
" 200.0 | \n",
" 0.071429 | \n",
"
\n",
" \n",
" Daenerys | \n",
" 18 Apr 2017 | \n",
" None | \n",
" 500 | \n",
" 0.710317 | \n",
" 250.0 | \n",
" 0.710317 | \n",
"
\n",
" \n",
" Tommen | \n",
" 18 Jul 2017 | \n",
" 29 Dec 2017 | \n",
" 800 | \n",
" 0.460317 | \n",
" 0.0 | \n",
" 0.460317 | \n",
"
\n",
" \n",
" Cersei | \n",
" 30 Dec 2017 | \n",
" None | \n",
" 1000 | \n",
" 0.000000 | \n",
" 0.0 | \n",
" 0.000000 | \n",
"
\n",
" \n",
" Jon | \n",
" 01 Feb 2018 | \n",
" None | \n",
" 100 | \n",
" 0.000000 | \n",
" 0.0 | \n",
" 0.000000 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Enter Leave Rate Worked_in_2017 Salary_April \\\n",
"Name \n",
"Doran 01 Feb 2012 11 Nov 2017 700 0.869048 700.0 \n",
"Robert 10 May 2012 01 Jan 2017 1000 0.000000 0.0 \n",
"Joffrey 03 Jan 2017 17 Jul 2017 800 0.539683 800.0 \n",
"Stannis 02 Jan 2017 07 Nov 2017 500 0.857143 500.0 \n",
"Robb 03 Apr 2017 28 Apr 2017 200 0.071429 200.0 \n",
"Daenerys 18 Apr 2017 None 500 0.710317 250.0 \n",
"Tommen 18 Jul 2017 29 Dec 2017 800 0.460317 0.0 \n",
"Cersei 30 Dec 2017 None 1000 0.000000 0.0 \n",
"Jon 01 Feb 2018 None 100 0.000000 0.0 \n",
"\n",
" Total_yrs \n",
"Name \n",
"Doran 5.785714 \n",
"Robert 4.646825 \n",
"Joffrey 0.539683 \n",
"Stannis 0.857143 \n",
"Robb 0.071429 \n",
"Daenerys 0.710317 \n",
"Tommen 0.460317 \n",
"Cersei 0.000000 \n",
"Jon 0.000000 "
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
" clnd = clnd_uk\n",
" \n",
" by_end_of_2017 = clnd((None, '31 Dec 2017'))\n",
" register['Total_yrs'] = [\n",
" clnd(tenure).overlap(by_end_of_2017).count_periods('A') \n",
" for tenure in zip(register.Enter, register.Leave)\n",
" ]\n",
" \n",
" register\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Analysis**"
]
},
{
"cell_type": "raw",
"metadata": {
"raw_mimetype": "text/restructuredtext"
},
"source": [
"\n",
"This snippet reiterates the composition of the two previous examples. There are two modifications:\n",
"\n",
"- ``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 to :py:meth:`~timeboard.Interval.overlap` called on the tenure in line 5 we effectively drop the part of the employee's tenure which extends into 2018 and beyond. \n",
"\n",
"- The last method in the method chain in line 5 is :py:meth:`.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."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"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."
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" Enter | \n",
" Leave | \n",
" Rate | \n",
" Worked_in_2017 | \n",
" Salary_April | \n",
" Total_yrs | \n",
" Bonus | \n",
"
\n",
" \n",
" Name | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" Doran | \n",
" 01 Feb 2012 | \n",
" 11 Nov 2017 | \n",
" 700 | \n",
" 0.869048 | \n",
" 700.0 | \n",
" 5.785714 | \n",
" 5725.91 | \n",
"
\n",
" \n",
" Robert | \n",
" 10 May 2012 | \n",
" 01 Jan 2017 | \n",
" 1000 | \n",
" 0.000000 | \n",
" 0.0 | \n",
" 4.646825 | \n",
" 0.00 | \n",
"
\n",
" \n",
" Joffrey | \n",
" 03 Jan 2017 | \n",
" 17 Jul 2017 | \n",
" 800 | \n",
" 0.539683 | \n",
" 800.0 | \n",
" 0.539683 | \n",
" 2750.36 | \n",
"
\n",
" \n",
" Stannis | \n",
" 02 Jan 2017 | \n",
" 07 Nov 2017 | \n",
" 500 | \n",
" 0.857143 | \n",
" 500.0 | \n",
" 0.857143 | \n",
" 2775.97 | \n",
"
\n",
" \n",
" Robb | \n",
" 03 Apr 2017 | \n",
" 28 Apr 2017 | \n",
" 200 | \n",
" 0.071429 | \n",
" 200.0 | \n",
" 0.071429 | \n",
" 0.00 | \n",
"
\n",
" \n",
" Daenerys | \n",
" 18 Apr 2017 | \n",
" None | \n",
" 500 | \n",
" 0.710317 | \n",
" 250.0 | \n",
" 0.710317 | \n",
" 2275.94 | \n",
"
\n",
" \n",
" Tommen | \n",
" 18 Jul 2017 | \n",
" 29 Dec 2017 | \n",
" 800 | \n",
" 0.460317 | \n",
" 0.0 | \n",
" 0.460317 | \n",
" 0.00 | \n",
"
\n",
" \n",
" Cersei | \n",
" 30 Dec 2017 | \n",
" None | \n",
" 1000 | \n",
" 0.000000 | \n",
" 0.0 | \n",
" 0.000000 | \n",
" 0.00 | \n",
"
\n",
" \n",
" Jon | \n",
" 01 Feb 2018 | \n",
" None | \n",
" 100 | \n",
" 0.000000 | \n",
" 0.0 | \n",
" 0.000000 | \n",
" 0.00 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" Enter Leave Rate Worked_in_2017 Salary_April \\\n",
"Name \n",
"Doran 01 Feb 2012 11 Nov 2017 700 0.869048 700.0 \n",
"Robert 10 May 2012 01 Jan 2017 1000 0.000000 0.0 \n",
"Joffrey 03 Jan 2017 17 Jul 2017 800 0.539683 800.0 \n",
"Stannis 02 Jan 2017 07 Nov 2017 500 0.857143 500.0 \n",
"Robb 03 Apr 2017 28 Apr 2017 200 0.071429 200.0 \n",
"Daenerys 18 Apr 2017 None 500 0.710317 250.0 \n",
"Tommen 18 Jul 2017 29 Dec 2017 800 0.460317 0.0 \n",
"Cersei 30 Dec 2017 None 1000 0.000000 0.0 \n",
"Jon 01 Feb 2018 None 100 0.000000 0.0 \n",
"\n",
" Total_yrs Bonus \n",
"Name \n",
"Doran 5.785714 5725.91 \n",
"Robert 4.646825 0.00 \n",
"Joffrey 0.539683 2750.36 \n",
"Stannis 0.857143 2775.97 \n",
"Robb 0.071429 0.00 \n",
"Daenerys 0.710317 2275.94 \n",
"Tommen 0.460317 0.00 \n",
"Cersei 0.000000 0.00 \n",
"Jon 0.000000 0.00 "
]
},
"execution_count": 17,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
" bonus_coefficient = 0.5\n",
" bonus_increment_per_year = 0.1\n",
" \n",
" eligibles = register[register.Worked_in_2017 >= 0.5]\n",
" \n",
" y2017 = clnd('2017', period='A')\n",
" annual_salary = [\n",
" clnd(tenure).overlap(y2017).count_periods('M') \n",
" for tenure in zip(eligibles.Enter, eligibles.Leave)\n",
" ] * eligibles.Rate\n",
" \n",
" register['Bonus'] = (bonus_coefficient*(1 + bonus_increment_per_year*eligibles.Total_yrs)) \\\n",
" * annual_salary\n",
" \n",
" register.Bonus = register.Bonus.fillna(0).round(2)\n",
" register"
]
}
],
"metadata": {
"celltoolbar": "Raw Cell Format",
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.3"
}
},
"nbformat": 4,
"nbformat_minor": 2
}