Excel2Calendar.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Written by Chongbin Zhang. \n",
    "# Designed to facilitate UCSD HDH's calendar import from events organized in excel table. \n",
    "\n",
    "# necessary imports\n",
    "import pandas as pd\n",
    "from pandas import ExcelWriter\n",
    "from pandas import ExcelFile\n",
    "from datetime import datetime\n",
    "from icalendar import Calendar, Event\n",
    "\n",
    "import TimeProcessor as tp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Event: Encuentros Engineering Academy has no given arrival people count. Setting it to zero. \n",
      "ERROR: SKYUS-Senri High School , because arrival time count is entered as:\n",
      "\t“ Check in time TBD ”\n",
      "Please enter this event manually. \n",
      "ERROR: SKYUS-Senri High School , because people count is entered as:\n",
      "\t“ 44 Participants including chaperones ”\n",
      "Please enter this event manually. \n"
     ]
    }
   ],
   "source": [
    "# open the excel and parse all the columns into lists\n",
    "# df stands for data frame\n",
    "df = pd.read_excel('SampleEvents.xls', sheet_name = 'Sheet1')\n",
    "listConfName = df['Conference Name']\n",
    "listHousArea = df['College Housing Area']\n",
    "listConfCord = df['Conference Coordinator']\n",
    "listArrvDate = df['Arrival 1 : Date']\n",
    "listArrTm_Ct = df['Arrival 1 : Time, Count']\n",
    "listChkInstr = df['Arrival 1 : Check-In Instructions']\n",
    "listMobi_Plc = df['Mobile Check-In Location  & Date']\n",
    "    \n",
    "listArrvTime = []\n",
    "listArrCount = []\n",
    "\n",
    "for i in range(0, 4): \n",
    "    \n",
    "    # split arrival time & people count, 0~150 is ERC Desk\n",
    "    temp = listArrTm_Ct[i].split(', ')\n",
    "    \n",
    "    if temp[0].find(':') == -1 and temp[0].find('a') == -1 and temp[0].find('p') == -1: # bad format\n",
    "        print(\"ERROR:\", listConfName[i], \", because arrival time count is entered as:\\n\\t“\", temp[0], \"”\\nPlease enter this event manually. \")\n",
    "    else:\n",
    "        listArrvTime.append(tp.process(temp[0]))\n",
    "        \n",
    "    if len(temp) <= 1:\n",
    "        print(\"Event:\", listConfName[i], \"has no given arrival people count. Setting it to zero. \")\n",
    "        listArrCount.append(0)\n",
    "    else:\n",
    "        incount = tp.stringIsInt(temp[1])\n",
    "        if not incount:\n",
    "            print(\"ERROR:\", listConfName[i], \", because people count is entered as:\\n\\t“\", temp[1], \"”\\nPlease enter this event manually. \")\n",
    "        else:\n",
    "            listArrCount.append(incount)\n",
    "\n",
    "listArrTm_Ct = None # free memory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Yes-4 6ft tables outside in front of the ERC Conference Desk\n",
      "Yes-4 6ft tables outside in front of the ERC Conference Desk\n",
      "nan\n"
     ]
    }
   ],
   "source": [
    "# Calendar() to write to .ics file\n",
    "cal = Calendar()\n",
    "\n",
    "# iterate through the table to create .ics file, 150 is the last for ERC desk in this file\n",
    "for i in range(0, (len(listArrvTime))):\n",
    "    \n",
    "    event = Event()\n",
    "\n",
    "    peopleCount = listArrCount[i]\n",
    "    beginhr = listArrvTime[i][0]\n",
    "    beginmin = listArrvTime[i][1]\n",
    "    endhr = listArrvTime[i][2]\n",
    "    endmin = listArrvTime[i][3]\n",
    "    \n",
    "    begin = datetime(int(listArrvDate[i].year), int(listArrvDate[i].month), int(listArrvDate[i].day), \n",
    "                     beginhr, beginmin)\n",
    "    \n",
    "    # ending at an earlier hour indicates a two-day event\n",
    "    if endhr < beginhr:\n",
    "        end = datetime(int(listArrvDate[i].year), int(listArrvDate[i].month), int(listArrvDate[i].day + 1), \n",
    "                       endhr, endmin)\n",
    "    else:\n",
    "        end = datetime(int(listArrvDate[i].year), int(listArrvDate[i].month), int(listArrvDate[i].day), \n",
    "                       endhr, endmin)\n",
    "    \n",
    "    # Add people count in title\n",
    "    summary = listConfName[i] + ' (' + str(peopleCount) + ')'\n",
    "    \n",
    "    # categorize, \"nan\" means the cell in Excel table is empty\n",
    "    print(listMobi_Plc[i])\n",
    "    if listMobi_Plc[i] == \"nan\": \n",
    "        summary = summary + ' (Check-in)'\n",
    "    else: \n",
    "        summary += ' (Mobile Check-in)'\n",
    "    \n",
    "    event.add('summary', summary)\n",
    "    event.add('description', listChkInstr[i])\n",
    "    event.add('dtstart', begin)\n",
    "    event.add('dtend', end)\n",
    "    cal.add_component(event)\n",
    "\n",
    "    filename = './' + summary + '.ics'\n",
    "    file = open(filename, 'wb')\n",
    "    file.write(cal.to_ical())\n",
    "    file.close()\n",
    "    \n",
    "    # clear event to prevent writing previous information to next event\n",
    "    event.clear()\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Extra Credit Challenges: \n",
    "I self taught try-catch, iCalendar module and file I/O for this python project."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "· Try-catch is essential when trying to convert an int to string: if it failed it will raise an error, and this cannot be handled with if-else or other conditional block. To me the most challenging part of try-catch is to learn the syntax (since I used to program in Java and C but little in Python) and learning to catch the correct type of error."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "· iCalendar and file I/O module is essential for writing the output of all the functions to calendar (.ics) file. To me the most challenging part of iCalendar is reading their documentation and applying the right API at the right place. For example, I didn't realize that I need to clear the event in my program after finished processing one event and caused the previous event's information to overflow to next event.\n",
    "· For file I/O, it took me a while to understand how to use the file API with the right mode (read-only, R/W combined, etc). But in the end I was able to figure out that 'wb' mode is the right one to use in my case. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}