{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Import the required libraries and open the connection to Mongo\n",
    "\n",
    "import collections\n",
    "from datetime import datetime\n",
    "import matplotlib as mpl\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import scipy.stats\n",
    "\n",
    "import pymongo\n",
    "from bson.objectid import ObjectId\n",
    "# client = pymongo.MongoClient('mongodb://localhost:27117/')\n",
    "# client = pymongo.MongoClient('mongodb://ogedei:27017')\n",
    "client = pymongo.MongoClient('mongodb://localhost:27017')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to the database\n",
    "summerofcode_db = client.summerofcode\n",
    "imp = summerofcode_db.imp\n",
    "icmas = summerofcode_db.icmas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "all-starts-hours.png\r\n",
      "attempts-per-day.png\r\n",
      "completion-hours.png\r\n",
      "import-data.ipynb\r\n",
      "participated-in-days.png\r\n",
      "scores-per-day.png\r\n",
      "SUMMEROFCODE-Day 0 Warm up holiday prices-grades.csv\r\n",
      "SUMMEROFCODE-Day 10 Word search-grades.csv\r\n",
      "SUMMEROFCODE-Day 1 Choosing a holiday-grades.csv\r\n",
      "SUMMEROFCODE-Day 2 Lifts-grades.csv\r\n",
      "SUMMEROFCODE-Day 3 Door codes-grades.csv\r\n",
      "SUMMEROFCODE-Day 4 Beach labyrinth-grades.csv\r\n",
      "SUMMEROFCODE-Day 5 Laser display boards-grades.csv\r\n",
      "SUMMEROFCODE-Day 6 Tour guides-grades.csv\r\n",
      "SUMMEROFCODE-Day 7 Fixing the minibar-grades.csv\r\n",
      "SUMMEROFCODE-Day 8 Visa woes-grades.csv\r\n",
      "SUMMEROFCODE-Day 9 Resolving the bill-grades.csv\r\n",
      "task-analysis.ipynb\r\n",
      "winners.ipynb\r\n"
     ]
    }
   ],
   "source": [
    "!ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def update_grades():\n",
    "    for i in imp.find(modifiers={\"$snapshot\": True}):\n",
    "        imp.update_one({'_id': i['_id']},\n",
    "            {'$set': {'grade': i['Grade/10']['00'],\n",
    "                      'q1': i['Q'][' 1 /5']['00'],\n",
    "                      'q2': i['Q'][' 2 /5']['00']},\n",
    "             '$unset': {'Grade/10': '', 'Q': ''}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def update_dates():\n",
    "    for i in imp.find(modifiers={\"$snapshot\": True}):\n",
    "        starttime = datetime.strptime(i['Started on'].strip(), '%d %b %Y %H:%M')\n",
    "        if i['Completed'] == '-':\n",
    "            imp.update_one({'_id': i['_id']},\n",
    "                           {'$set': {'started': starttime},\n",
    "                            '$unset': {'Started on': '', 'Completed': '', 'Time taken': ''}})\n",
    "        else:\n",
    "            endtime = datetime.strptime(i['Completed'].strip(), '%d %b %Y %H:%M')\n",
    "            imp.update_one({'_id': i['_id']},\n",
    "                           {'$set': {'started': starttime,\n",
    "                                     'completed': endtime},\n",
    "                            '$unset': {'Started on': '', 'Completed': '', 'Time taken': ''}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def merge_imported(icma_number):\n",
    "    icmas.delete_many({'icma_number': icma_number})\n",
    "\n",
    "    for i in imp.find(modifiers={\"$snapshot\": True}):\n",
    "        del i['_id']\n",
    "        i['icma_number'] = icma_number\n",
    "        icmas.insert_one(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['SUMMEROFCODE-Day 0 Warm up holiday prices-grades.csv',\n",
       " 'SUMMEROFCODE-Day 10 Word search-grades.csv',\n",
       " 'SUMMEROFCODE-Day 1 Choosing a holiday-grades.csv',\n",
       " 'SUMMEROFCODE-Day 2 Lifts-grades.csv',\n",
       " 'SUMMEROFCODE-Day 3 Door codes-grades.csv',\n",
       " 'SUMMEROFCODE-Day 4 Beach labyrinth-grades.csv',\n",
       " 'SUMMEROFCODE-Day 5 Laser display boards-grades.csv',\n",
       " 'SUMMEROFCODE-Day 6 Tour guides-grades.csv',\n",
       " 'SUMMEROFCODE-Day 7 Fixing the minibar-grades.csv',\n",
       " 'SUMMEROFCODE-Day 8 Visa woes-grades.csv',\n",
       " 'SUMMEROFCODE-Day 9 Resolving the bill-grades.csv']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "csvs = !ls SUMMEROFCODE*csv\n",
    "csvs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.DeleteResult at 0x7f1909986c18>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "icmas.delete_many({})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SUMMEROFCODE-Day 0 Warm up holiday prices-grades.csv\n",
      "2017-08-07T10:55:24.121+0100\tconnected to: localhost\n",
      "2017-08-07T10:55:24.121+0100\tdropping: summerofcode.imp\n",
      "2017-08-07T10:55:24.190+0100\timported 294 documents\n",
      "SUMMEROFCODE-Day 10 Word search-grades.csv\n",
      "2017-08-07T10:55:24.567+0100\tconnected to: localhost\n",
      "2017-08-07T10:55:24.567+0100\tdropping: summerofcode.imp\n",
      "2017-08-07T10:55:24.577+0100\timported 64 documents\n",
      "SUMMEROFCODE-Day 1 Choosing a holiday-grades.csv\n",
      "2017-08-07T10:55:24.746+0100\tconnected to: localhost\n",
      "2017-08-07T10:55:24.746+0100\tdropping: summerofcode.imp\n",
      "2017-08-07T10:55:24.751+0100\timported 158 documents\n",
      "SUMMEROFCODE-Day 2 Lifts-grades.csv\n",
      "2017-08-07T10:55:24.990+0100\tconnected to: localhost\n",
      "2017-08-07T10:55:24.990+0100\tdropping: summerofcode.imp\n",
      "2017-08-07T10:55:24.993+0100\timported 105 documents\n",
      "SUMMEROFCODE-Day 3 Door codes-grades.csv\n",
      "2017-08-07T10:55:25.187+0100\tconnected to: localhost\n",
      "2017-08-07T10:55:25.187+0100\tdropping: summerofcode.imp\n",
      "2017-08-07T10:55:25.198+0100\timported 89 documents\n",
      "SUMMEROFCODE-Day 4 Beach labyrinth-grades.csv\n",
      "2017-08-07T10:55:25.381+0100\tconnected to: localhost\n",
      "2017-08-07T10:55:25.381+0100\tdropping: summerofcode.imp\n",
      "2017-08-07T10:55:25.383+0100\timported 97 documents\n",
      "SUMMEROFCODE-Day 5 Laser display boards-grades.csv\n",
      "2017-08-07T10:55:25.571+0100\tconnected to: localhost\n",
      "2017-08-07T10:55:25.571+0100\tdropping: summerofcode.imp\n",
      "2017-08-07T10:55:25.573+0100\timported 77 documents\n",
      "SUMMEROFCODE-Day 6 Tour guides-grades.csv\n",
      "2017-08-07T10:55:25.743+0100\tconnected to: localhost\n",
      "2017-08-07T10:55:25.744+0100\tdropping: summerofcode.imp\n",
      "2017-08-07T10:55:25.746+0100\timported 101 documents\n",
      "SUMMEROFCODE-Day 7 Fixing the minibar-grades.csv\n",
      "2017-08-07T10:55:25.955+0100\tconnected to: localhost\n",
      "2017-08-07T10:55:25.955+0100\tdropping: summerofcode.imp\n",
      "2017-08-07T10:55:25.956+0100\timported 54 documents\n",
      "SUMMEROFCODE-Day 8 Visa woes-grades.csv\n",
      "2017-08-07T10:55:26.126+0100\tconnected to: localhost\n",
      "2017-08-07T10:55:26.126+0100\tdropping: summerofcode.imp\n",
      "2017-08-07T10:55:26.128+0100\timported 61 documents\n",
      "SUMMEROFCODE-Day 9 Resolving the bill-grades.csv\n",
      "2017-08-07T10:55:26.302+0100\tconnected to: localhost\n",
      "2017-08-07T10:55:26.302+0100\tdropping: summerofcode.imp\n",
      "2017-08-07T10:55:26.303+0100\timported 42 documents\n"
     ]
    }
   ],
   "source": [
    "for csv in csvs:\n",
    "    print(csv)\n",
    "    qcsv = \"'{}'\".format(csv)\n",
    "    !mongoimport --drop --db summerofcode --collection imp --type csv --headerline --ignoreBlanks --file {qcsv}\n",
    "    imp.delete_many({'Surname': 'Overall average'})\n",
    "    update_grades()\n",
    "    update_dates()\n",
    "    merge_imported(int(csv.split()[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1131"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "icmas.find({}, ['icma']).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'_id': 0, 'count': 293},\n",
       " {'_id': 1, 'count': 157},\n",
       " {'_id': 2, 'count': 104},\n",
       " {'_id': 3, 'count': 88},\n",
       " {'_id': 4, 'count': 96},\n",
       " {'_id': 5, 'count': 76},\n",
       " {'_id': 6, 'count': 100},\n",
       " {'_id': 7, 'count': 53},\n",
       " {'_id': 8, 'count': 60},\n",
       " {'_id': 9, 'count': 41},\n",
       " {'_id': 10, 'count': 63}]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pipeline = [{\"$group\": {\"_id\": \"$icma_number\", \"count\": {\"$sum\": 1}}},\n",
    "            {'$sort': {'_id': 1}}]\n",
    "list(icmas.aggregate(pipeline))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.5.2+"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}