Extract_duty_time_from_outlook.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "id": "c8bf2bff-9684-4d40-8710-fd7f6ba21cdf",
   "metadata": {},
   "source": [
    "Creator: Peter Betlem\n",
    "Year: 2021\n",
    "Institution: University Centre in Svalbard"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f5b1011-1e78-4f2d-be1c-b47b9799cf99",
   "metadata": {},
   "source": [
    "# How it works:\n",
    "\n",
    "The following calls use pywin32 to connect to Outlook while it is running.\n",
    "This allows Python to tap into the Outlook calendar of the first email account.\n",
    "Once tapped in, the functions below scan each calendar appointment for specific keywords in the title.\n",
    "These keywords are based on the categories described in the UNIS Duty sheet for PhD students.\n",
    "\n",
    "General naming convention to allow for this process:\n",
    "\n",
    "{Category} - {Subcategory activity} - {WHO} / {description of task}\n",
    "\n",
    "Please do not include dashes (\"-\") or forward slashes (\"/\") as part of the title other than those placed in the general naming convention.\n",
    "\n",
    "The categories and activities are listed below:\n",
    "\n",
    "## Teaching\n",
    "\n",
    "### TA\n",
    "- Practical support\n",
    "- Technical support\n",
    "\n",
    "### Teacher\n",
    "- Lectures\n",
    "- Original lectures\n",
    "- Colloqiuium\n",
    "- Original colloquium\n",
    "- Field leader\n",
    "- Field assistant\n",
    "\n",
    "## Supervision\n",
    "\n",
    "### BSc\n",
    "- Course project\n",
    "- thesis project\n",
    "\n",
    "### MSc\n",
    "- Course project\n",
    "- Thesis project\n",
    "\n",
    "## Grading\n",
    "- report <15p\n",
    "- report <25p\n",
    "- report >25p\n",
    "- exam BSc <4h\n",
    "- exam BSc <10h\n",
    "- exam MSc\n",
    "- oral\n",
    "- field\n",
    "\n",
    "## Skills development\n",
    "- course\n",
    "\n",
    "## Other\n",
    "- IT\n",
    "- hardware\n",
    "- software\n",
    "- documentation\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ad81481c-fa21-4f3b-b51c-f4a89007ec14",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "import win32com.client\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "def get_calendar(begin,end):\n",
    "    Outlook = win32com.client.Dispatch(\"Outlook.Application\")\n",
    "    ns = Outlook.GetNamespace(\"MAPI\")\n",
    "    appts = ns.GetDefaultFolder(9).Items\n",
    "    calendar = appts\n",
    "    calendar.IncludeRecurrences = \"True\"\n",
    "    calendar.Sort('[Start]')\n",
    "\n",
    "    restriction = \"[Start] >= '\" + begin.strftime('%m/%d/%Y') + \"' AND [END] <= '\" + end.strftime('%m/%d/%Y') + \"'\"\n",
    "    calendar = calendar.Restrict(restriction)\n",
    "    return calendar\n",
    "\n",
    "\n",
    "def get_appointments(calendar,subject_kw = None,exclude_subject_kw = None, body_kw = None):\n",
    "\n",
    "    if subject_kw == None:\n",
    "        appointments = [app for app in calendar]    \n",
    "    else:\n",
    "        appointments = [app for app in calendar if subject_kw in app.Subject]\n",
    "\n",
    "    if exclude_subject_kw != None:\n",
    "        appointments = [app for app in appointments if exclude_subject_kw not in app.Subject]\n",
    "    cal_subject = [app.Subject for app in appointments]\n",
    "    cal_start = [app.Start for app in appointments]\n",
    "    cal_end = [app.End for app in appointments]\n",
    "    cal_body = [app.Body for app in appointments]\n",
    "\n",
    "    df = pd.DataFrame({'subject': cal_subject,\n",
    "                       'start': [datetime.replace(tzinfo=None) for datetime in cal_start],\n",
    "                       'end': [datetime.replace(tzinfo=None) for datetime in cal_end],\n",
    "                       'body': cal_body})\n",
    "    return df\n",
    "\n",
    "def classify_appointments(appointments):\n",
    "    appointments[\"Category\"], appointments[\"Activity\"], appointments[\"Who\"] = appointments.subject.apply(lambda x: x.split(\" / \")[0].split(' - ')).values[0]\n",
    "    return appointments\n",
    "\n",
    "def analyse_activity(s):\n",
    "    try:\n",
    "        description = s.subject.split(\" / \")[1]\n",
    "        s = s.subject.split(\" / \")[0]\n",
    "\n",
    "        cat = s.split(\" - \")[0]\n",
    "        activity = s.split(\" - \")[1]\n",
    "        who = s.split(\" - \")[2]\n",
    "        return cat.lower(), activity.lower(), who, description\n",
    "    except:\n",
    "        return None, None, None, \"Failed to analyse calendar meeting.\"\n",
    "\n",
    "def calculate_time(s):\n",
    "    try:\n",
    "        delta = s.end - s.start\n",
    "        return delta\n",
    "    except:\n",
    "        return None\n",
    "\n",
    "def analyse_calendar(start = dt.datetime(2021,5,26), end = dt.datetime(2021,7,26)):\n",
    "    cal = get_calendar(start, end)\n",
    "    analysis = None\n",
    "    for category in [\"Teaching\",\"Supervision\",\"Grading\",\"Skills\",\"Other\"]:\n",
    "        sub_analysis = get_appointments(cal,subject_kw = category)\n",
    "        \n",
    "        sub_analysis[[\"Category\",\"Activity\",\"Who\", \"Description\"]] = sub_analysis.apply(analyse_activity, axis=1, result_type=\"expand\")\n",
    "        if not sub_analysis.empty:\n",
    "            sub_analysis = sub_analysis[[\"Category\",\"Activity\",\"Who\", \"Description\",\"start\",\"end\"]]\n",
    "            sub_analysis[[\"time\"]] = sub_analysis.apply(calculate_time,axis=1, result_type = \"expand\")\n",
    "        \n",
    "            if analysis is not None:\n",
    "                analysis = analysis.append(sub_analysis)\n",
    "            else:\n",
    "                analysis = sub_analysis.copy()\n",
    "    \n",
    "    return analysis\n",
    "\n",
    "def statistics_calendar(analysis, drop_failed = True):\n",
    "    if analysis is not None:\n",
    "        if drop_failed:\n",
    "            analysis = analysis[analysis[\"Description\"] != \"Failed to analyse calendar meeting.\"]\n",
    "        return analysis.groupby(by = [\"Category\",\"Activity\"]).time.sum(), analysis.time.sum()\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "da937e87-0503-4bb2-b343-aa7d572e56f4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Category</th>\n",
       "      <th>Activity</th>\n",
       "      <th>Who</th>\n",
       "      <th>Description</th>\n",
       "      <th>start</th>\n",
       "      <th>end</th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>teaching</td>\n",
       "      <td>seminar</td>\n",
       "      <td>AG209</td>\n",
       "      <td>Term projects presentations</td>\n",
       "      <td>2021-05-26 10:00:00</td>\n",
       "      <td>2021-05-26 15:30:00</td>\n",
       "      <td>0 days 05:30:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Category Activity    Who                  Description               start  \\\n",
       "0  teaching  seminar  AG209  Term projects presentations 2021-05-26 10:00:00   \n",
       "\n",
       "                  end            time  \n",
       "0 2021-05-26 15:30:00 0 days 05:30:00  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "analysis = analyse_calendar(start = dt.datetime(2021,5,26),end = dt.datetime(2021,5,27))\n",
    "analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "007597b8-1e49-4a65-9a0d-f9cfecd5cbbf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Category  Activity\n",
       " teaching  seminar    0 days 05:30:00\n",
       " Name: time, dtype: timedelta64[ns],\n",
       " Timedelta('0 days 05:30:00'))"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statistics_calendar(analysis)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2c327875-a505-4caf-b44c-cbfa5f9a0cae",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}