{ "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ws_refstartdurationendlabelon_duty
loc
63162017-04-172017-04-1712017-04-170False
63172017-04-182017-04-1812017-04-188True
63182017-04-192017-04-1912017-04-198True
63192017-04-202017-04-2012017-04-208True
63202017-04-212017-04-2112017-04-218True
63212017-04-222017-04-2212017-04-220False
63222017-04-232017-04-2312017-04-230False
63232017-04-242017-04-2412017-04-248True
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ws_refstartdurationendlabelon_dutyteam_Ateam_Bteam_Cteam_D
loc
90842017-04-17 02:00:002017-04-17 02:00:0062017-04-17 07:59:59ATrueTrueFalseFalseFalse
90852017-04-17 08:00:002017-04-17 08:00:00102017-04-17 17:59:59BTrueFalseTrueFalseFalse
90862017-04-17 18:00:002017-04-17 18:00:0082017-04-18 01:59:59CTrueFalseFalseTrueFalse
90872017-04-18 02:00:002017-04-18 02:00:0062017-04-18 07:59:59DTrueFalseFalseFalseTrue
90882017-04-18 08:00:002017-04-18 08:00:00102017-04-18 17:59:59ATrueTrueFalseFalseFalse
90892017-04-18 18:00:002017-04-18 18:00:0082017-04-19 01:59:59BTrueFalseTrueFalseFalse
90902017-04-19 02:00:002017-04-19 02:00:0062017-04-19 07:59:59CTrueFalseFalseTrueFalse
90912017-04-19 08:00:002017-04-19 08:00:00102017-04-19 17:59:59DTrueFalseFalseFalseTrue
90922017-04-19 18:00:002017-04-19 18:00:0082017-04-20 01:59:59ATrueTrueFalseFalseFalse
90932017-04-20 02:00:002017-04-20 02:00:0062017-04-20 07:59:59BTrueFalseTrueFalseFalse
90942017-04-20 08:00:002017-04-20 08:00:00102017-04-20 17:59:59CTrueFalseFalseTrueFalse
90952017-04-20 18:00:002017-04-20 18:00:0082017-04-21 01:59:59DTrueFalseFalseFalseTrue
90962017-04-21 02:00:002017-04-21 02:00:0062017-04-21 07:59:59ATrueTrueFalseFalseFalse
90972017-04-21 08:00:002017-04-21 08:00:00102017-04-21 17:59:59BTrueFalseTrueFalseFalse
90982017-04-21 18:00:002017-04-21 18:00:0082017-04-22 01:59:59CTrueFalseFalseTrueFalse
90992017-04-22 02:00:002017-04-22 02:00:0062017-04-22 07:59:59DTrueFalseFalseFalseTrue
91002017-04-22 08:00:002017-04-22 08:00:00102017-04-22 17:59:59ATrueTrueFalseFalseFalse
91012017-04-22 18:00:002017-04-22 18:00:0082017-04-23 01:59:59BTrueFalseTrueFalseFalse
91022017-04-23 02:00:002017-04-23 02:00:0062017-04-23 07:59:59CTrueFalseFalseTrueFalse
91032017-04-23 08:00:002017-04-23 08:00:00102017-04-23 17:59:59DTrueFalseFalseFalseTrue
91042017-04-23 18:00:002017-04-23 18:00:0082017-04-24 01:59:59ATrueTrueFalseFalseFalse
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
StageDuration
0Development14
1Acceptance2
2Deployment3
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
StageDurationStartDeadline
0Development142018-01-092018-01-26
1Acceptance22018-01-292018-01-30
2Deployment32018-01-312018-02-02
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
StartDurationEnd
02017-04-17 02:00:0062017-04-17 08:00:00
12017-04-18 08:00:00102017-04-18 18:00:00
22017-04-19 18:00:0082017-04-20 02:00:00
32017-04-21 02:00:0062017-04-21 08:00:00
42017-04-22 08:00:00102017-04-22 18:00:00
52017-04-23 18:00:0082017-04-24 02:00:00
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
EnterLeaveRate
Name
Doran01 Feb 201211 Nov 2017700
Robert10 May 201201 Jan 20171000
Joffrey03 Jan 201717 Jul 2017800
Stannis02 Jan 201707 Nov 2017500
Robb03 Apr 201728 Apr 2017200
Daenerys18 Apr 2017None500
Tommen18 Jul 201729 Dec 2017800
Cersei30 Dec 2017None1000
Jon01 Feb 2018None100
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
EnterLeaveRateWorked_in_2017
Name
Doran01 Feb 201211 Nov 20177000.869048
Robert10 May 201201 Jan 201710000.000000
Joffrey03 Jan 201717 Jul 20178000.539683
Stannis02 Jan 201707 Nov 20175000.857143
Robb03 Apr 201728 Apr 20172000.071429
Daenerys18 Apr 2017None5000.710317
Tommen18 Jul 201729 Dec 20178000.460317
Cersei30 Dec 2017None10000.000000
Jon01 Feb 2018None1000.000000
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
EnterLeaveRateWorked_in_2017Salary_April
Name
Doran01 Feb 201211 Nov 20177000.869048700.0
Robert10 May 201201 Jan 201710000.0000000.0
Joffrey03 Jan 201717 Jul 20178000.539683800.0
Stannis02 Jan 201707 Nov 20175000.857143500.0
Robb03 Apr 201728 Apr 20172000.071429200.0
Daenerys18 Apr 2017None5000.710317250.0
Tommen18 Jul 201729 Dec 20178000.4603170.0
Cersei30 Dec 2017None10000.0000000.0
Jon01 Feb 2018None1000.0000000.0
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
EnterLeaveWage_shifts
Name
Doran01 Feb 201211 Nov 2017480
Robert10 May 201201 Jan 20170
Joffrey03 Jan 201717 Jul 2017480
Stannis02 Jan 201707 Nov 2017480
Robb03 Apr 201728 Apr 2017480
Daenerys18 Apr 2017None400
Tommen18 Jul 201729 Dec 20170
Cersei30 Dec 2017None0
Jon01 Feb 2018None0
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
EnterLeaveWage_shiftsWage_hours
Name
Doran01 Feb 201211 Nov 2017480480.0
Robert10 May 201201 Jan 201700.0
Joffrey03 Jan 201717 Jul 2017480480.0
Stannis02 Jan 201707 Nov 2017480480.0
Robb03 Apr 201728 Apr 2017480480.0
Daenerys18 Apr 2017None400420.0
Tommen18 Jul 201729 Dec 201700.0
Cersei30 Dec 2017None00.0
Jon01 Feb 2018None00.0
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
EnterLeaveRateWorked_in_2017Salary_AprilTotal_yrs
Name
Doran01 Feb 201211 Nov 20177000.869048700.05.785714
Robert10 May 201201 Jan 201710000.0000000.04.646825
Joffrey03 Jan 201717 Jul 20178000.539683800.00.539683
Stannis02 Jan 201707 Nov 20175000.857143500.00.857143
Robb03 Apr 201728 Apr 20172000.071429200.00.071429
Daenerys18 Apr 2017None5000.710317250.00.710317
Tommen18 Jul 201729 Dec 20178000.4603170.00.460317
Cersei30 Dec 2017None10000.0000000.00.000000
Jon01 Feb 2018None1000.0000000.00.000000
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
EnterLeaveRateWorked_in_2017Salary_AprilTotal_yrsBonus
Name
Doran01 Feb 201211 Nov 20177000.869048700.05.7857145725.91
Robert10 May 201201 Jan 201710000.0000000.04.6468250.00
Joffrey03 Jan 201717 Jul 20178000.539683800.00.5396832750.36
Stannis02 Jan 201707 Nov 20175000.857143500.00.8571432775.97
Robb03 Apr 201728 Apr 20172000.071429200.00.0714290.00
Daenerys18 Apr 2017None5000.710317250.00.7103172275.94
Tommen18 Jul 201729 Dec 20178000.4603170.00.4603170.00
Cersei30 Dec 2017None10000.0000000.00.0000000.00
Jon01 Feb 2018None1000.0000000.00.0000000.00
\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 }