Data_prep_for_sankey.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ORIGIN</th>\n",
       "      <th>DEST</th>\n",
       "      <th>DEP_DELAY</th>\n",
       "      <th>ARR_DELAY</th>\n",
       "      <th>CARRIER_DELAY</th>\n",
       "      <th>WEATHER_DELAY</th>\n",
       "      <th>NAS_DELAY</th>\n",
       "      <th>SECURITY_DELAY</th>\n",
       "      <th>LATE_AIRCRAFT_DELAY</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABE</td>\n",
       "      <td>DTW</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>-14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABE</td>\n",
       "      <td>DTW</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ABE</td>\n",
       "      <td>DTW</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ABE</td>\n",
       "      <td>DTW</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>-14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ABE</td>\n",
       "      <td>DTW</td>\n",
       "      <td>-6.0</td>\n",
       "      <td>-19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ORIGIN DEST  DEP_DELAY  ARR_DELAY  CARRIER_DELAY  WEATHER_DELAY  NAS_DELAY  \\\n",
       "0    ABE  DTW       -5.0      -14.0            NaN            NaN        NaN   \n",
       "1    ABE  DTW       -2.0        0.0            NaN            NaN        NaN   \n",
       "2    ABE  DTW       -9.0       -9.0            NaN            NaN        NaN   \n",
       "3    ABE  DTW       -8.0      -14.0            NaN            NaN        NaN   \n",
       "4    ABE  DTW       -6.0      -19.0            NaN            NaN        NaN   \n",
       "\n",
       "   SECURITY_DELAY  LATE_AIRCRAFT_DELAY  Unnamed: 9  \n",
       "0             NaN                  NaN         NaN  \n",
       "1             NaN                  NaN         NaN  \n",
       "2             NaN                  NaN         NaN  \n",
       "3             NaN                  NaN         NaN  \n",
       "4             NaN                  NaN         NaN  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#load the data\n",
    "data = pd.read_csv(\"data/July17.csv\")\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "var cities =\n",
    "{\"nodes\": [{\"name\":\"Chicago\"}],\n",
    "\"links\":[{\"source\":1,\"target\":0,\"value\":20}]};"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def getCityData(city, dataset):\n",
    "    city_destin_data = dataset[dataset.DEST == city]\n",
    "    city_destin_data = city_destin_data.groupby('ORIGIN', as_index = False).mean()\n",
    "    city_origin_data = dataset[dataset.ORIGIN == city]\n",
    "    city_origin_data = city_origin_data.groupby('DEST', as_index = False).mean()\n",
    "    return city_destin_data, city_origin_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def dictPad(text):\n",
    "    return dict({\"name\":text})\n",
    "\n",
    "def getNodes(city, incoming, outgoing):\n",
    "    cities = []\n",
    "    for i in incoming.ORIGIN:\n",
    "        cities.append(i)\n",
    "    for i in outgoing.DEST:\n",
    "        cities.append(i)\n",
    "    cities = list(set(cities))\n",
    "    return len(cities), [city]+cities+cities\n",
    "\n",
    "def getNewNodes(city, incoming, outgoing):\n",
    "    in_cities = []\n",
    "    out_cities = []\n",
    "    for i in incoming.ORIGIN:\n",
    "        in_cities.append(i)\n",
    "    for i in outgoing.DEST:\n",
    "        out_cities.append(i)\n",
    "    return len(in_cities), [city]+in_cities+out_cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def getIncomingLinks(data, nodes, city):\n",
    "    links = []\n",
    "    for index, row in data.iterrows():\n",
    "        links.append(dict({\"source\": nodes.index(row['ORIGIN']) ,\"target\":nodes.index(city) ,\"value\":row['ARR_DELAY'] }))\n",
    "    return links\n",
    "\n",
    "def getOutgoingLinks(data, nodes, city, num):\n",
    "    links = []\n",
    "    for index, row in data.iterrows():\n",
    "        links.append(dict({\"source\": nodes.index(city),\"target\": max(loc for loc, val in enumerate(nodes) if val == row['DEST']) ,\"value\":row['DEP_DELAY'] }))\n",
    "    return links\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "def visualize(cities, nodes):\n",
    "    for idx,l in enumerate(cities['links']):\n",
    "        print (1+idx, nodes[l['source']] + \"-->\" + nodes[l['target']] + \"=\" + str(l['value']))\n",
    "\n",
    "def getSankeyInfo(file):\n",
    "    data = pd.read_csv(file)\n",
    "    list_of_cities = list(set(list(data.ORIGIN)+list(data.DEST)))\n",
    "    outlist = {}\n",
    "    for city in list_of_cities:\n",
    "        incoming, outgoing = getCityData(city, data)\n",
    "\n",
    "        incoming = incoming.sort_values('ARR_DELAY', axis=0, ascending=False)\n",
    "        outgoing = outgoing.sort_values('DEP_DELAY', axis=0, ascending=False)\n",
    "\n",
    "        num, nodes = getNewNodes(city, incoming, outgoing)\n",
    "\n",
    "        links_in = getIncomingLinks(incoming, nodes, city)\n",
    "        links_out = getOutgoingLinks(outgoing, nodes, city, num)\n",
    "        links = links_in + links_out\n",
    "\n",
    "        cities = {\"nodes\": list(map(lambda x:dictPad(x),nodes)), \"links\": links}\n",
    "        # visualize(cities,nodes)\n",
    "        outlist[city] = cities\n",
    "    file = 'sankey_data/data_{0}.json'.format(file[21:-4])\n",
    "    print (file)\n",
    "    with open(file, 'w') as outfile:\n",
    "        json.dump(outlist, outfile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sankey_data/data_Sep16.json\n",
      "data/sankey_raw_data/Sep16.csv\n",
      "sankey_data/data_May17.json\n",
      "data/sankey_raw_data/May17.csv\n",
      "sankey_data/data_Dec16.json\n",
      "data/sankey_raw_data/Dec16.csv\n",
      "sankey_data/data_Aug17.json\n",
      "data/sankey_raw_data/Aug17.csv\n",
      "sankey_data/data_March16.json\n",
      "data/sankey_raw_data/March16.csv\n",
      "sankey_data/data_Jan17.json\n",
      "data/sankey_raw_data/Jan17.csv\n",
      "sankey_data/data_April16.json\n",
      "data/sankey_raw_data/April16.csv\n",
      "sankey_data/data_Feb17.json\n",
      "data/sankey_raw_data/Feb17.csv\n",
      "sankey_data/data_Feb16.json\n",
      "data/sankey_raw_data/Feb16.csv\n",
      "sankey_data/data_July16.json\n",
      "data/sankey_raw_data/July16.csv\n",
      "sankey_data/data_June16.json\n",
      "data/sankey_raw_data/June16.csv\n",
      "sankey_data/data_Jan16.json\n",
      "data/sankey_raw_data/Jan16.csv\n",
      "sankey_data/data_Nov16.json\n",
      "data/sankey_raw_data/Nov16.csv\n",
      "sankey_data/data_Aug16.json\n",
      "data/sankey_raw_data/Aug16.csv\n",
      "sankey_data/data_July17.json\n",
      "data/sankey_raw_data/July17.csv\n",
      "sankey_data/data_Oct16.json\n",
      "data/sankey_raw_data/Oct16.csv\n",
      "sankey_data/data_June17.json\n",
      "data/sankey_raw_data/June17.csv\n",
      "sankey_data/data_May16.json\n",
      "data/sankey_raw_data/May16.csv\n",
      "sankey_data/data_April17.json\n",
      "data/sankey_raw_data/April17.csv\n",
      "sankey_data/data_Sep17.json\n",
      "data/sankey_raw_data/Sep17.csv\n",
      "sankey_data/data_March17.json\n",
      "data/sankey_raw_data/March17.csv\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "for subdir, dirs, files in os.walk('data/sankey_raw_data'):\n",
    "    for file in files:\n",
    "        filepath = subdir + os.sep + file\n",
    "        if filepath.endswith(\".csv\"):\n",
    "            getSankeyInfo(filepath)\n",
    "            print (filepath)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# newlist = sorted(sorted(links, key=lambda m: m['target']) , key=lambda k: k['value']) \n",
    "# newlist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# cities = {\"nodes\": list(map(lambda x:dictPad(x),nodes)), \"links\": newlist}\n",
    "# import json\n",
    "# file = 'data_{0}.txt'.format(city+\"sort\")\n",
    "# with open(file, 'w') as outfile:\n",
    "#     json.dump(cities, outfile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 SJC-->JFK=73.48387096774194\n",
      "2 SMF-->JFK=58.241379310344826\n",
      "3 DCA-->JFK=50.516129032258064\n",
      "4 PHL-->JFK=50.193548387096776\n",
      "5 RSW-->JFK=44.18032786885246\n",
      "6 FLL-->JFK=39.91694352159468\n",
      "7 ORD-->JFK=38.89041095890411\n",
      "8 RDU-->JFK=38.63793103448276\n",
      "9 HOU-->JFK=37.357142857142854\n",
      "10 MCO-->JFK=32.174107142857146\n",
      "11 PSE-->JFK=30.0\n",
      "12 MIA-->JFK=29.924342105263158\n",
      "13 CLT-->JFK=29.91891891891892\n",
      "14 PBI-->JFK=28.5\n",
      "15 ACK-->JFK=27.77777777777778\n",
      "16 BUF-->JFK=27.663366336633665\n",
      "17 JAX-->JFK=26.65909090909091\n",
      "18 MVY-->JFK=25.63157894736842\n",
      "19 SAV-->JFK=25.17543859649123\n",
      "20 BGR-->JFK=22.727272727272727\n",
      "21 LGB-->JFK=22.666666666666668\n",
      "22 ATL-->JFK=21.826291079812208\n",
      "23 ROC-->JFK=21.261904761904763\n",
      "24 BUR-->JFK=20.774193548387096\n",
      "25 ABQ-->JFK=19.612903225806452\n",
      "26 PWM-->JFK=19.36521739130435\n",
      "27 BOS-->JFK=19.26843657817109\n",
      "28 SJU-->JFK=19.13372093023256\n",
      "29 TPA-->JFK=19.085106382978722\n",
      "30 LAS-->JFK=17.885416666666668\n",
      "31 IAD-->JFK=17.8625\n",
      "32 AUS-->JFK=17.30635838150289\n",
      "33 SFO-->JFK=16.989583333333332\n",
      "34 MSY-->JFK=16.891304347826086\n",
      "35 CHS-->JFK=16.0\n",
      "36 DTW-->JFK=15.892857142857142\n",
      "37 PHX-->JFK=15.875\n",
      "38 SEA-->JFK=15.566433566433567\n",
      "39 DEN-->JFK=14.912751677852349\n",
      "40 LAX-->JFK=14.174311926605505\n",
      "41 HYA-->JFK=13.193548387096774\n",
      "42 BQN-->JFK=13.189655172413794\n",
      "43 SLC-->JFK=12.174157303370787\n",
      "44 SAN-->JFK=12.115942028985508\n",
      "45 SYR-->JFK=9.460674157303371\n",
      "46 BTV-->JFK=8.047058823529412\n",
      "47 MSP-->JFK=8.008771929824562\n",
      "48 RNO-->JFK=6.857142857142857\n",
      "49 OAK-->JFK=4.774193548387097\n",
      "50 STT-->JFK=2.918032786885246\n",
      "51 SRQ-->JFK=1.9\n",
      "52 DAB-->JFK=-1.7419354838709677\n",
      "53 DFW-->JFK=-1.8870967741935485\n",
      "54 PDX-->JFK=-3.9130434782608696\n",
      "55 SAT-->JFK=-6.67741935483871\n",
      "56 HNL-->JFK=-14.096774193548388\n",
      "57 CVG-->JFK=-15.0\n",
      "58 JFK-->SAT=77.35483870967742\n",
      "59 JFK-->SMF=73.96666666666667\n",
      "60 JFK-->OAK=50.903225806451616\n",
      "61 JFK-->LGB=42.87719298245614\n",
      "62 JFK-->PSE=42.51851851851852\n",
      "63 JFK-->MCO=38.40481400437637\n",
      "64 JFK-->SJC=36.483870967741936\n",
      "65 JFK-->IAD=36.025\n",
      "66 JFK-->JAX=35.20224719101124\n",
      "67 JFK-->MSY=34.43478260869565\n",
      "68 JFK-->SJU=34.08670520231214\n",
      "69 JFK-->PHL=32.29032258064516\n",
      "70 JFK-->DTW=30.688524590163933\n",
      "71 JFK-->PWM=30.191304347826087\n",
      "72 JFK-->FLL=29.42904290429043\n",
      "73 JFK-->BOS=29.183431952662723\n",
      "74 JFK-->RNO=27.3\n",
      "75 JFK-->BUF=27.202970297029704\n",
      "76 JFK-->ROC=27.188235294117646\n",
      "77 JFK-->ABQ=27.0\n",
      "78 JFK-->AUS=25.511363636363637\n",
      "79 JFK-->RDU=25.203389830508474\n",
      "80 JFK-->HOU=24.612903225806452\n",
      "81 JFK-->ATL=24.350467289719628\n",
      "82 JFK-->LAS=23.54639175257732\n",
      "83 JFK-->PBI=23.06382978723404\n",
      "84 JFK-->BTV=23.023255813953487\n",
      "85 JFK-->SYR=22.9438202247191\n",
      "86 JFK-->ORD=22.789115646258505\n",
      "87 JFK-->DCA=21.93548387096774\n",
      "88 JFK-->SEA=21.75862068965517\n",
      "89 JFK-->DFW=21.306451612903224\n",
      "90 JFK-->TPA=18.84375\n",
      "91 JFK-->ACK=18.581818181818182\n",
      "92 JFK-->SAN=18.307692307692307\n",
      "93 JFK-->DEN=18.16\n",
      "94 JFK-->BQN=17.152542372881356\n",
      "95 JFK-->MIA=16.34426229508197\n",
      "96 JFK-->LAX=15.920909090909092\n",
      "97 JFK-->MSP=15.547826086956523\n",
      "98 JFK-->SLC=15.47191011235955\n",
      "99 JFK-->CLT=14.558035714285714\n",
      "100 JFK-->CHS=14.258620689655173\n",
      "101 JFK-->BUR=13.0\n",
      "102 JFK-->RSW=12.065573770491802\n",
      "103 JFK-->SFO=11.838757396449704\n",
      "104 JFK-->PHX=11.832618025751072\n",
      "105 JFK-->STT=11.53225806451613\n",
      "106 JFK-->SAV=8.948275862068966\n",
      "107 JFK-->PDX=8.904347826086957\n",
      "108 JFK-->MVY=5.615384615384615\n",
      "109 JFK-->SRQ=5.387096774193548\n",
      "110 JFK-->BGR=4.545454545454546\n",
      "111 JFK-->HYA=4.096774193548387\n",
      "112 JFK-->DAB=1.7096774193548387\n",
      "113 JFK-->HNL=-4.290322580645161\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "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.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}