The problem

Working as rotational doctor means changing to different hospitals every 3 - 6 months, which also means a new rota every 3 - 6 months!

These are sent as a spreadsheet with each row containing a date and corresponding cell as a shift type. This shift type can be in a variety of different formats e.g. long day vs LD vs ld vs Long Day.

Example spreadsheet

Entering each shift into my calendar is time consuming so a way of automating this was needed.

The aim

Develop a simple python script which takes a csv file as input and outputs a ics calendar.

This will need to:

• take a csv input: column 1 - date, column 2 - shift type
• from the value in column 2 determine start and finish times
• allow for ‘all day’ events if an ‘Off’ day
• handle night shifts which span two days
• generate a ics calendar file with: date, shift type, shift start time, shift end time and location
• extra: use docker to deploy script and handle required dependencies

The solution

Overview

The git repository can be found here.

Outline

The general outline of the script was devised:

1. import csv file/ variables required to parse csv file
2. create icalendar object
3. loop through csv generating and adding each day’s shift type and details to the icalendar
4. output ics file

Note: ics or ‘Internet Calendaring and Scheduling Core Object Specification’ is a media type that stores calendar and scheduling information. This format can be easily imported into a caldav server (in my case nextcloud)

Setting the variables

The first step was to declare the variables required by the main script. These were entered into a separate python file settings.py which will be imported by the main python script. This allows for an easy way to update the variables used to process the csv file with each new rota’s details such as shift type and shift start/ end times.

settings.py
# dictionay containing shift type as key (case insensitive) and shift name/ start time/ end time as values
SHIFTS = {'off': ['Off', '', ''],
          'day': ['Day shift', '08:00', '17:00'],
          'ld': ['Long shift', '08:00', '20:00'],
          'n': ['Night shift', '20:00', '08:00']
          }

# address of hospital
ADDRESS = "A Hospital, Hospital Road, London, AB1 C11"

# used to generate calendar name
ROTA_TYPE = "Anaesthetics"
SHORT_LOCATION = "Hospital"

Importing the spreadsheet

The spreadsheet rota was exported to a csv file in this format:

input.csv
dd/mm/yy,shift_type

Note: The csv has no header row

Using the example table from the introduction.

input.csv
14/01/20,Day
15/01/20,LD
16/01/20,Off
17/01/20,N
18/01/20,N

Writing the main script

Version 1.0 of the script is below:

main.py
import datetime
import icalendar
import pandas as pd
import settings
import uuid

# import df and variables
base_df = pd.read_csv('input.csv', header=None)

shifts = settings.SHIFTS
address = settings.ADDRESS
rota_type = settings.ROTA_TYPE
short_location = settings.SHORT_LOCATION

# convert all keys in shifts[] to lower case
for key in shifts:
  shifts[key.lower()] = shifts.pop(key)

# initialise calendar
cal = icalendar.Calendar()

# ics headers
cal.add('prodid', 'Rota by python')
cal.add('version', '2.0')
cal.add('X-WR-TIMEZONE', 'Europe/London')
cal.add('X-WR-CALNAME', f"{rota_type} {short_location}")
cal.add('CALSCALE', 'GREGORIAN')
cal.add('METHOD', 'PUBLISH')

for row in range(len(base_df)):

# initialise variables and icalendar.event
    shift_date = base_df.iloc[row][0]
    csv_shift_name = base_df.iloc[row][1].lower()
    shift_name = shifts[csv_shift_name][0]
    start_date_time = f"{shift_date} {shifts[csv_shift_name][1]}"
    end_date_time = f"{shift_date} {shifts[csv_shift_name][2]}"
    event = icalendar.Event()

    if shift_name[:3].lower() == 'off':
        start_dt = datetime.datetime.strptime(shift_date, '%d/%m/%y')
        end_dt = datetime.datetime.strptime(shift_date, '%d/%m/%y')
    else:
        start_dt = datetime.datetime.strptime(start_date_time, '%d/%m/%y %H:%M')
        end_dt = datetime.datetime.strptime(end_date_time, '%d/%m/%y %H:%M')
        event.add('location', address)

# if night shift or off run to next day
    if shift_name.lower() in ['night', 'off']:
        end_dt += datetime.timedelta(days=1)

# create icalendar events
    event.add('summary', shift_name)
    event.add('dtstart', start_dt)
    event.add('dtstamp', datetime.datetime.now())
    event.add('uid', f"{uuid.uuid4().hex}@{rota_type}.{short_location}")
    event.add('dtend', end_dt)

# write calendar
    cal.add_component(event)

with open('output.ics', 'wb') as outfile:
    outfile.write(cal.to_ical())

# print count of shift types
print(base_df[1].value_counts())
print("Success: converted csv to ics")

Deploying with docker

Docker is a convenient way of handling python modules when deploying apps. Keeping the host system safe from potential python dependency version incompatibility.

The build script of the docker file is below. This adds to an alpine Linux container; python, pip and the required modules. To speed the build time pandas can be directly installed from the testing repository.

Dockerfile
FROM alpine:latest

RUN echo "@testing http://dl-cdn.alpinelinux.org/alpine/edge/testing" >> /etc/apk/repositories && \
    apk upgrade --update && \
    apk add --no-cache python3 py3-pandas@testing && \
    pip3 install icalendar datetime uuid

RUN touch /main.py && \
    touch /settings.py && \
    touch /input.csv && \
    touch /output.ics

CMD [ "python3", "/main.py" ]

The container was built by running the command below in the working directory containing the Dockerfile:

$ docker build -t local/rota .

To run the script the csv input, py files and ics output were mounted to the container.

$ docker run -it --rm --name rota_csvtoics \
    -v ${PWD}/main.py:/main.py \
    -v ${PWD}/settings.py:/settings.py \
    -v ${PWD}/input.csv:/input.csv \
    -v ${PWD}/output.ics:/output.ics \
    local/rota

Summary

A quick and easy solution to generating an ics calendar from csv using python and docker.

April 2020