User_antenna mapping.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# User_antenna mapping\n",
    "La idea es crear la lista que cuenta antennas utilizadas para cada user en el dataset outputeado por simple_format diariamente y agrupar esta lista (con repeticiones) en un agregado mensual (para luego determinar home_antenna etc.)\n",
    "\n",
    "Tambien usando info de las antenas (epidemicas o no). Podriamos ver si un user \"vive\" o solo \"paso\" por la zona epidemica.\n",
    "\n",
    "## definiciones utilizadas\n",
    "* timestamp arranca en 0 segundos para 01/01/2012 00:00am\n",
    "* el analisis es solo para CDRs con usuarios de la TelCo. Los users de la TelCo van todos en la primer columna (independientemente del nombre de la columna..) entonces si la llamada es entrante quiere decir que el user de la telco esta recibiendo un llamado en esa antennaID. si la llamada es saliente es al reves. (Podria pasar que sea comunicaciones inter-Telco y ahi habria dos records uno saliente y otro entrante pero con los mismos 2 userIDs).\n",
    "* La Direction, viene dada relativa al user de la primer columna (incoming si entra y viceversa si es outgoing).\n",
    "\n",
    "### el dataset de simple_format vendria con este header\n",
    " {'USER':np.int32 \\\\\\  'OTHER_USER':np.uint32 \\\\\\  'DIRECTION':np.bool \\\\\\ 'TIMESTAMP':np.uint32 \\\\\\  'DURATION':np.uint16 \\\\\\  'ANTENNAID':np.uint16}\n",
    "\n",
    "por lo cual seria conveniente eliminar los headers de cada .txt.gz para parsear rapidamente, asignar dtypes y despues volver a asignar column names aca en pandas (u otro program)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# (Al 02/02) \n",
    "vamos a ver como usar pandas.Dataframe con la opcion de chunksize para ir usando el algoritmo de a cachos en la tabla que es muy pesada para levantar toda en memoria. Y luego ir 'guardando' tipo con cat."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "IPython.notebook.set_autosave_interval(180000)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Autosaving every 180 seconds\n"
     ]
    }
   ],
   "source": [
    "%autosave 180\n",
    "\n",
    "import pandas as pd; import numpy as np; import os;import random;\n",
    "pd.set_option('display.max_rows', 300)\n",
    "#from matplotlib import pyplot as plt\n",
    "#%matplotlib inline\n",
    "import time\n",
    "import os\n",
    "np.random.seed(2016)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#ejemplo  \"/grandata/simple_format/01-02012014.txt.gz\"\n",
    "#seteamos el lugar de trabajo\n",
    "rootdir=\"/grandata/simple_format/\"\n",
    "os.chdir(rootdir)\n",
    "year = \"2015\"; \n",
    "month_start= \"01\";\n",
    "#todos estos sum_links terminan 2 meses mas tardes\n",
    "month_end= str(int(month_start)+2)\n",
    "if int(month_end )<10:\n",
    "    month_end=\"0\"+month_end\n",
    "    \n",
    "day_start = \"01\"\n",
    "day_end = str(int(day_start)+1)\n",
    "if int(day_end )<10:\n",
    "    day_end=\"0\"+ day_end\n",
    "\n",
    "input_file= rootdir +\"simple_format_{y}{ms}.txt.gz\"\\\n",
    "                .format(y=year,ms=month_start,me=month_end,\n",
    "                       ds=day_start,de=day_end)\n",
    "    \n",
    "def get_output_file(hash_map=False,night_filter=False,week_end=False):\n",
    "    output = \"/home/juan/mobility-study/output\"\n",
    "    if week_end == True:\n",
    "        output = output + \"_wkend\"\n",
    "    if night_filter == True:\n",
    "        output = output + \"_ngtfilter\"\n",
    "    if hash_map == True:\n",
    "        output = output + \"_user_hash_map\"\n",
    "    return output + \".txt.gz\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "USER,ANTENNA_ID_0,COUNT_0,ANTENNA_ID_1,COUNT_1,ANTENNA_ID_2,COUNT_2,ANTENNA_ID_3,COUNT_3,ANTENNA_ID_4,COUNT_4,ANTENNA_ID_5,COUNT_5,ANTENNA_ID_6,COUNT_6,ANTENNA_ID_7,COUNT_7,ANTENNA_ID_8,COUNT_8,ANTENNA_ID_9,COUNT_9,MOBILITY_DIAMETER,EXPOSED,EPIDEMIC\r\n",
      "FFFF78CB080636632B2D1DE7A7BFAB03,3415,21,2969,5,1320,3,2471,3,1115,2,-1,65535,-1,65535,-1,65535,-1,65535,-1,65535,1063,0,0\r\n",
      "FFFF51FDDD55A2E24D074DE30C4798E9,1207,15,1171,14,1820,9,583,8,2026,5,1066,4,1831,4,1625,2,401,1,825,1,520,1,0\r\n",
      "FFFE1EDDA25369CBC467536FA6A787FD,1847,22,1753,14,1422,11,1028,10,765,9,3072,9,862,8,2463,7,1416,6,871,5,990,1,0\r\n",
      "FFFD0A912A959CDFCFFAB2F93BFBA435,1407,18,1702,8,1613,6,1704,5,1394,3,1398,3,818,2,958,2,726,1,848,1,491,1,0\r\n",
      "FFFCB235D6D917DDF350F5B11CCB0EE8,17,74,174,33,1123,4,1178,4,173,3,890,2,503,1,1039,1,1228,1,1327,1,1613,1,0\r\n",
      "FFFC9EC4F662C44D3006BA69C9F0DE85,876,19,1737,6,1744,5,326,1,378,1,503,1,1118,1,2216,1,2477,1,2962,1,3004,1,1\r\n",
      "FFFB2F18F4CCF253C06B581EA518A7A5,2543,13,704,7,2540,7,1453,6,1642,5,45,4,2393,4,1841,3,3031,3,1009,2,2895,1,0\r\n",
      "FFFA147CB2B23AD12F207F0EFBEB14D0,1857,103,2959,19,1344,2,2309,2,1511,1,2858,1,-1,65535,-1,65535,-1,65535,-1,65535,1633,1,1\r\n",
      "FFFA0736F66FB09200CE1D13E081A946,266,63,1799,46,1790,29,2716,29,537,21,3377,21,1439,18,2715,16,1720,15,2000,14,2558,1,1\r\n",
      "\r\n",
      "gzip: stdout: Broken pipe\r\n"
     ]
    }
   ],
   "source": [
    "!zcat $input_file | head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#para enriquecer el dataset de CDRs con info acerca de la zona epidemica etc.\n",
    "antennas= pd.read_csv('/home/juan/mobility-study/antennas_mexico.csv',sep = \"|\",header=0,index_col=0)\n",
    "antennas.index.rename(\"ANTENNA_ID\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sample_file = input_file.replace(\".txt.gz\",\"_sample.txt.gz\")\n",
    "#para el manejo de las distintas salidas del programa\n",
    "\n",
    "\n",
    "week_end = False\n",
    "night_filter = False\n",
    "hash_map = False\n",
    "output_file = get_output_file(hash_map,night_filter,week_end)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/home/juan/mobility-study/output.txt.gz'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output_file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sample.to_csv(sample_file,\n",
    "              sep=\" \",\n",
    "              header=False,\n",
    "              index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "USER OTHER_USER DIRECTION TIMESTAMP DURATION ANTENNA_ID\r\n",
      "216E0E920C0B51093276E270CC36CB72 5492B0033211A161BA4DCECD83A6690E O 94695418 88 1\r\n",
      "73C2C1F8233884659CC65DF58BA2D031 0ECA1BB365D1BD1F3B54F5F5225A4337 I 94820900 153 2\r\n",
      "73C2C1F8233884659CC65DF58BA2D031 0ECA1BB365D1BD1F3B54F5F5225A4337 O 94842758 189 3\r\n",
      "73C2C1F8233884659CC65DF58BA2D031 0ECA1BB365D1BD1F3B54F5F5225A4337 O 94820507 361 2\r\n",
      "73C2C1F8233884659CC65DF58BA2D031 0ECA1BB365D1BD1F3B54F5F5225A4337 O 94831395 52 4\r\n",
      "73C2C1F8233884659CC65DF58BA2D031 29D24A2CD146FFEDC05757A22BAED052 I 94835642 40 5\r\n",
      "73C2C1F8233884659CC65DF58BA2D031 3386587CF389FB2B195C60D92DEB9F81 I 94711785 25 2\r\n",
      "73C2C1F8233884659CC65DF58BA2D031 3386587CF389FB2B195C60D92DEB9F81 I 94838044 27 6\r\n",
      "73C2C1F8233884659CC65DF58BA2D031 3386587CF389FB2B195C60D92DEB9F81 O 94819123 34 6\r\n",
      "\r\n",
      "gzip: stdout: Broken pipe\r\n"
     ]
    }
   ],
   "source": [
    "!zcat $input_file | head -n10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## testeo el algo en un file pequenyo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#leo las primeras 10M lineas\n",
    "sample = pd.read_csv(\n",
    "                input_file,\n",
    "                engine = 'c',\n",
    "#                chunksize = 10**7,\n",
    "    #            iterator =True,\n",
    "                nrows = 10**7,\n",
    "                sep = ' ',\n",
    "                header = 0,\n",
    "                index_col=None,\n",
    "                usecols = ['USER','OTHER_USER','ANTENNA_ID','TIMESTAMP'],\n",
    "                dtype = {'TIMESTAMP':np.uint32,'ANTENNA_ID':np.uint16,'USER':np.object_,'OTHER_USER':np.object_}\n",
    "                )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "working group number 0 of 45, time elapsed is 0.00221490859985 \n",
      "\n",
      "Working on month 05-2015, time elapsed is 0.0901749134064 \n"
     ]
    },
    {
     "ename": "CParserError",
     "evalue": "Error tokenizing data. C error: Calling read(nbytes) on source failed. Try engine='python'.",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mCParserError\u001b[0m                              Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-28-163639e43c0e>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m     40\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     41\u001b[0m         \u001b[0mnumb\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m;\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 42\u001b[1;33m         \u001b[1;32mfor\u001b[0m \u001b[0mchunk\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mtable\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     43\u001b[0m                 \u001b[0mnumb\u001b[0m\u001b[1;33m+=\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     44\u001b[0m                 \u001b[1;31m#a cada chunk filtro por todos los USERs por el hash del string (el hash es un int) y despue filtro modulo passes\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/home/juan/anaconda2/lib/python2.7/site-packages/pandas/io/parsers.pyc\u001b[0m in \u001b[0;36m__iter__\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    721\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    722\u001b[0m                 \u001b[1;32mwhile\u001b[0m \u001b[0mTrue\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 723\u001b[1;33m                     \u001b[1;32myield\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    724\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    725\u001b[0m                 \u001b[1;32myield\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/home/juan/anaconda2/lib/python2.7/site-packages/pandas/io/parsers.pyc\u001b[0m in \u001b[0;36mread\u001b[1;34m(self, nrows)\u001b[0m\n\u001b[0;32m    745\u001b[0m                 \u001b[1;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'skip_footer not supported for iteration'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    746\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 747\u001b[1;33m         \u001b[0mret\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnrows\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    748\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    749\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'as_recarray'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m/home/juan/anaconda2/lib/python2.7/site-packages/pandas/io/parsers.pyc\u001b[0m in \u001b[0;36mread\u001b[1;34m(self, nrows)\u001b[0m\n\u001b[0;32m   1195\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mnrows\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1196\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1197\u001b[1;33m             \u001b[0mdata\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_reader\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnrows\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1198\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mStopIteration\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1199\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_first_chunk\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas/parser.pyx\u001b[0m in \u001b[0;36mpandas.parser.TextReader.read (pandas/parser.c:7988)\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas/parser.pyx\u001b[0m in \u001b[0;36mpandas.parser.TextReader._read_low_memory (pandas/parser.c:8444)\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas/parser.pyx\u001b[0m in \u001b[0;36mpandas.parser.TextReader._read_rows (pandas/parser.c:8970)\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas/parser.pyx\u001b[0m in \u001b[0;36mpandas.parser.TextReader._tokenize_rows (pandas/parser.c:8838)\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas/parser.pyx\u001b[0m in \u001b[0;36mpandas.parser.raise_parser_error (pandas/parser.c:22649)\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mCParserError\u001b[0m: Error tokenizing data. C error: Calling read(nbytes) on source failed. Try engine='python'."
     ]
    }
   ],
   "source": [
    "## script para extraer todos los atributos del simple_format\n",
    "\n",
    "#ver el tiempo que tarda\n",
    "start_time = time.time()\n",
    "\n",
    "#el chunk basicamente va leyendo el file de a 'chunksize' cantidad de filas\n",
    "#subgroup = pd.DataFrame()\n",
    "\n",
    "#hay cinco meses del cual extraer atributos:\n",
    "months= [\"0\"+str(month) for month in range(5,10)]\n",
    "\n",
    "year = \"2015\"\n",
    "\n",
    "#aca seteamos como vamos a partir la tabla segun el nro correspondiente a c/hash y tomando modulo\n",
    "passes = 45\n",
    "#creo los grupos que despues van a filtrar c/chunk de la tabla para hacer varias pasadas\n",
    "for group in range(passes):\n",
    "    print(\"working group number {it} of {pas}, time elapsed is {t} \\n\".format(it=group,pas=passes, t=(time.time()-start_time)))\n",
    "\n",
    "    #itero sobre los meses\n",
    "    subgroup = pd.DataFrame()\n",
    "    for month in months:\n",
    "        print(\"Working on month {ms}-{y}, time elapsed is {t} \".format(ms=month,y=year,t=(time.time()-start_time)))\n",
    "        input_file= rootdir +\"simple_format_{y}{ms}.txt.gz\"\\\n",
    "            .format(y=year,ms=month)\n",
    "\n",
    "        table = pd.read_csv(\n",
    "                input_file,\n",
    "                engine = 'c',\n",
    "                chunksize = 6*10**7,\n",
    "    #            iterator =True,\n",
    "                sep = ' ',\n",
    "                header = 0,\n",
    "                index_col=None,\n",
    "                usecols = ['USER','OTHER_USER','ANTENNA_ID','TIMESTAMP'],\n",
    "                dtype = {'TIMESTAMP':np.uint32,'ANTENNA_ID':np.uint16,'USER':np.object_,'OTHER_USER':np.object_}\n",
    "                )\n",
    "\n",
    "        #cuando entramos a este loop, table tiene tantos 'chunks' como el valor entero de la cantidad de lineas en el file\n",
    "        #dividido el tamanyo del chunksize\n",
    "\n",
    "        numb=0;\n",
    "        for chunk in table:\n",
    "                numb+=1\n",
    "                #a cada chunk filtro por todos los USERs por el hash del string (el hash es un int) y despue filtro modulo passes\n",
    "                #y trabajo sobre la tabla subgroup nada mas que ahora tiene menos usuarios\n",
    "                subgroup = subgroup.append(chunk[chunk['USER'].apply(lambda x: hash(x) % passes == group )])\n",
    "\n",
    "    #entonces la idea es que yo ahora solo voy a trabajar, dentro de esta tabla filtrada y para todos los meses juntos\n",
    "    \n",
    "    #paso de segundos a horas\n",
    "    #notar que TIMESTAMP arranca en 0 segundos para domingo 01/01/2012 00:00am \n",
    "    #con lo cual domingo es el dia 0, lunes el 1, asi..\n",
    "    print('finished month reading')\n",
    "    subgroup['Hour'] =  (subgroup['TIMESTAMP'].values*1.0/3600)%24\n",
    "    \n",
    "    subgroup['Day']  =  (subgroup['TIMESTAMP'].values*1.0/(3600*24))%7\n",
    "\n",
    "    #filtro usuarios con pocos o demasiados llamados en general menos de 25 y mas de 2000 en 5 meses  \n",
    "    insignificant_users = subgroup['USER'].value_counts()[(subgroup['USER'].value_counts() < 25) \\\n",
    "                        | (subgroup['USER'].value_counts() > 2000)].index.values.tolist()\n",
    "    subgroup= subgroup.loc[~subgroup['USER'].isin(insignificant_users)]\n",
    "\n",
    "\n",
    "    #filtro segun nightfilter y week_end. el day light seria de [7,19) segun la convencion de mexico/GranData\n",
    "    if night_filter == True:\n",
    "        subgroup = subgroup.loc[(subroup['Hour']<7) | (subgroup['Hour']>19)]\n",
    "    if week_end == True:\n",
    "        subgroup = subgroup.loc[(subgroup['Day']==0) | (subgroup['Day']==6)]\n",
    "\n",
    "\n",
    "    #   \n",
    "\n",
    "    grouped = subgroup.groupby(['USER', 'ANTENNA_ID'])['ANTENNA_ID'].agg({'count': np.size})\n",
    "    grouped.reset_index(inplace=True,drop=False)\n",
    "\n",
    "    del subgroup\n",
    "    #reordeno dentro de c/ USER por el count del antenna, esto me sirve para despues ordenar las antenas por uso\n",
    "    grouped.sort_values(by=['USER','count'],ascending=False,inplace=True)\n",
    "\n",
    "    ##enriquezco la muestra con datos epidemicos\n",
    "    #primero agrego a cada antenna del df el dato de si es epidemica\n",
    "    #despues agrupo por el USER y me fijo solo la columna epidemica en c/grupo\n",
    "    #finalmente sumo en c/ grupo y tomo la parte superior \n",
    "    #entera de esa division con el largo del grupo. Si uso al menos una antena epidemica entonces esta expuesto(==1) Si no,\n",
    "    # da 0 pues no estuvo expuesto.        \n",
    "    ##enriquezco la muestra con datos epidemicos\n",
    "\n",
    "    exposed_info =grouped.join(antennas['EPIDEMIC'], on='ANTENNA_ID').\\\n",
    "    groupby('USER')['EPIDEMIC'].\\\n",
    "        agg({'EXPOSED' : lambda x: int( np.ceil(np.sum(x)*1.0/np.size(x)) )})\n",
    "\n",
    "    #actualizo la tabla\n",
    "    grouped = grouped.join(exposed_info['EXPOSED'],on=\"USER\")\n",
    "    del exposed_info\n",
    "\n",
    "\n",
    "    #creo la tabla filtrada solo por users, que es la que voy a terminar guardando (hay tantos rows como users)\n",
    "    output_table = grouped.drop_duplicates(subset = 'USER', keep='first')\n",
    "    #re indexo\n",
    "    output_table.index = output_table['USER'].values\n",
    "\n",
    "    #agrupo ahora la tabla por USER para hacer todos los calculos en los grupos\n",
    "\n",
    "    grouped = grouped.groupby('USER')\n",
    "\n",
    "    #aca voy a ir agregando las top 10 antennas utilizadas por el user, Si no llego a 10 antennas, relleno con NaNs\n",
    "    for i in range(10):\n",
    "        #me quedo con la i-esima fila de c/grupo (si no hay fila, no toma en cuenta ese USER)\n",
    "\n",
    "        buffer_table = grouped.nth(i)[['ANTENNA_ID','count']]\n",
    "        #renombre a iesima ANTENNA_ID e iesimo count\n",
    "        buffer_table.columns=['ANTENNA_ID_%i'%i,'count_%i'%i]\n",
    "        #agrego esta info como nuevas columnas, dejando lo demas como NaNs\n",
    "        output_table = pd.concat([output_table, buffer_table], axis=1, join_axes=[output_table.index])\n",
    "\n",
    "    del grouped\n",
    "    #dropeo la columna USER que ya es redundante por el indice\n",
    "    output_table.index.name = \"USER\"\n",
    "    output_table.drop('USER', axis=1, inplace=True)\n",
    "    \n",
    "    #los primeros ANTENNA_IDs ya no me sirven, idem con el primer\n",
    "    output_table.drop('ANTENNA_ID', axis=1, inplace=True)\n",
    "    output_table.drop('count', axis=1, inplace=True)\n",
    "    \n",
    "    \n",
    "\n",
    "    #para los datos faltantes dentro del Top10, relleno con -1s, que vendrian a ser los NaNs\n",
    "    output_table.fillna(-1,inplace=True)\n",
    "\n",
    "    #como elimine la columan de hashes puedo convertir todo a int\n",
    "    output_table = output_table.astype(int,copy=False)\n",
    "\n",
    "    #agrego info de EPIDEMIC, asumiendo a alguien como epidemico si al antenna donde vive (la ANTENNA_ID_0) \n",
    "    #esta catalogada como EPIDEMIC\n",
    "\n",
    "    output_table = output_table.join(antennas['EPIDEMIC'], on='ANTENNA_ID_0')\n",
    "\n",
    "    #aca termino guardando (en forma de append) el output final pero solo para esos usuarios % pass ==group\n",
    "\n",
    "    #el primer write (first group==0) va con header\n",
    "    if group ==0 :\n",
    "        output_table.to_csv(output_file, index = True,\n",
    "                   header = True,\n",
    "                    mode='a',\n",
    "                compression='gzip')\n",
    "    else: \n",
    "        output_table.to_csv(output_file, index = True,\n",
    "                   header = False,\n",
    "                    mode='a',\n",
    "                    compression='gzip')\n",
    "        \n",
    "    print(\"Finished group {gr} of {ps}, time elapsed is {t}\\n \".format(gr=group,ps=passes,t=(time.time()-start_time)))\n",
    "then = start_time\n",
    "seconds = time.time() - then\n",
    "print(\"total running time of script is %d \" % seconds)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## cambiamos el working dir y el input_file (output.txt.gz --> output1.txt.gz)\n",
    "Esto pues vamos salvando los resultados del procesamiento de datos en files sucesivos para no tener que repetir el algoritmo ni para arriesgar cambiar una version (en terminos de datos) y no tener checkpoints intermedios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#ejemplo  \"/grandata/simple_format/01-02012014.txt.gz\"\n",
    "#seteamos el lugar de trabajo\n",
    "rootdir=\"/home/juan/mobility-study/\"\n",
    "os.chdir(rootdir)\n",
    "year = \"2015\"; \n",
    "month_start= \"01\";\n",
    "#todos estos sum_links terminan 2 meses mas tardes\n",
    "month_end= str(int(month_start)+2)\n",
    "if int(month_end )<10:\n",
    "    month_end=\"0\"+month_end\n",
    "    \n",
    "day_start = \"01\"\n",
    "day_end = str(int(day_start)+1)\n",
    "if int(day_end )<10:\n",
    "    day_end=\"0\"+ day_end\n",
    "\n",
    "input_file= rootdir +\"output1.txt.gz\"\\\n",
    "                .format(y=year,ms=month_start,me=month_end,\n",
    "                       ds=day_start,de=day_end)\n",
    "output_file = \"/home/juan/mobility-study/output2\"\n",
    "if week_end == True:\n",
    "    output_file = output_file + \"_wkend\"\n",
    "if night_filter == True:\n",
    "    output_file += \"_ngtfilter\"\n",
    "if hash_map == True:\n",
    "    output_file += \"_user_hash_map\"\n",
    "output_file += \".txt.gz\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/home/juan/mobility-study/output1.txt.gz\r\n"
     ]
    }
   ],
   "source": [
    "!echo $input_file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "165M\t/home/juan/mobility-study/output1.txt.gz\r\n"
     ]
    }
   ],
   "source": [
    "!du -ha $input_file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "USER,EXPOSED,ANTENNA_ID_0,count_0,ANTENNA_ID_1,count_1,ANTENNA_ID_2,count_2,ANTENNA_ID_3,count_3,ANTENNA_ID_4,count_4,ANTENNA_ID_5,count_5,ANTENNA_ID_6,count_6,ANTENNA_ID_7,count_7,ANTENNA_ID_8,count_8,ANTENNA_ID_9,count_9,EPIDEMIC\r\n",
      "FFFF78CB080636632B2D1DE7A7BFAB03,0,3415,21,2969,5,1320,3,2471,3,1115,2,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,0\r\n",
      "FFFF51FDDD55A2E24D074DE30C4798E9,1,1207,15,1171,14,1820,9,583,8,2026,5,1066,4,1831,4,1625,2,401,1,825,1,0\r\n",
      "FFFE1EDDA25369CBC467536FA6A787FD,1,1847,22,1753,14,1422,11,1028,10,765,9,3072,9,862,8,2463,7,1416,6,871,5,1\r\n",
      "FFFD0A912A959CDFCFFAB2F93BFBA435,1,1407,18,1702,8,1613,6,1704,5,1394,3,1398,3,818,2,958,2,726,1,848,1,0\r\n",
      "FFFCB235D6D917DDF350F5B11CCB0EE8,1,17,74,174,33,1123,4,1178,4,173,3,890,2,503,1,1039,1,1228,1,1327,1,1\r\n",
      "FFFC9EC4F662C44D3006BA69C9F0DE85,1,876,19,1737,6,1744,5,326,1,378,1,503,1,1118,1,2216,1,2477,1,2962,1,1\r\n",
      "FFFB2F18F4CCF253C06B581EA518A7A5,1,2543,13,704,7,2540,7,1453,6,1642,5,45,4,2393,4,1841,3,3031,3,1009,2,0\r\n",
      "FFFA147CB2B23AD12F207F0EFBEB14D0,1,1857,103,2959,19,1344,2,2309,2,1511,1,2858,1,-1,-1,-1,-1,-1,-1,-1,-1,1\r\n",
      "FFFA0736F66FB09200CE1D13E081A946,1,266,63,1799,46,1790,29,2716,29,537,21,3377,21,1439,18,2715,16,1720,15,2000,14,1\r\n",
      "FFF976C823C8ACDE846E68DF75F4D698,1,1421,6,668,5,1150,5,572,4,1247,3,1633,3,496,2,507,2,714,2,1023,2,1\r\n",
      "FFF8711184E944160F2003E834F47464,1,508,21,1470,20,867,15,582,11,2121,6,2980,4,800,2,2638,1,-1,-1,-1,-1,1\r\n",
      "FFF8373FC0468150A4B6460E6F1A3768,1,3250,16,2879,12,3384,6,2799,5,2483,3,1077,2,2480,2,3512,2,1865,1,1883,1,0\r\n",
      "FFF806371B7F8FC0973C63A4DB39FF8D,1,2985,9,1160,4,2729,1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,1\r\n",
      "FFF761BE0942967457C00CFE4D70582A,1,137,70,2263,48,3536,41,1333,36,193,23,51,9,417,6,55,5,95,2,116,2,0\r\n",
      "FFF2F8D91418841E5479664E93184C41,1,895,18,715,8,537,5,2619,5,266,3,778,2,896,2,1439,2,1720,2,3182,2,1\r\n",
      "FFF2E60B036874A8E13515CCB6D33881,1,583,18,401,11,948,7,2474,7,825,6,1658,6,1096,5,1433,5,2197,5,84,4,1\r\n",
      "FFF15C718A1F7B5D06A86E150F4446A5,1,688,19,235,15,374,11,1262,9,143,8,1791,7,609,4,346,2,347,2,596,2,0\r\n",
      "FFF0E97CBF5D7455DF2002A0C3785B0F,1,2773,6,2754,4,2840,1,2926,1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,1\r\n",
      "FFF0D9CCCCFAD903B866BA751954EB8A,1,1705,43,1442,31,1099,22,1315,11,2039,11,529,8,1886,7,2134,6,2241,6,603,5,0\r\n",
      "\r\n",
      "gzip: stdout: Broken pipe\r\n"
     ]
    }
   ],
   "source": [
    "!zcat $input_file | head -n20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "table = pd.read_csv(\n",
    "                input_file,\n",
    "                engine = 'c',\n",
    "                sep = ',',\n",
    "                header = 0,\n",
    "                index_col=0,\n",
    "                dtype = {'EXPOSED':np.bool,'EPIDEMIC':np.bool,\n",
    "                         'ANTENNA_ID_0':np.int16,'count_0':np.uint16,'ANTENNA_ID_1':np.int16,'count_1':np.uint16,\n",
    "                         'ANTENNA_ID_2':np.int16,'count_2':np.uint16,'ANTENNA_ID_3':np.int16,'count_3':np.uint16,\n",
    "                        'ANTENNA_ID_4':np.int16,'count_4':np.uint16,'ANTENNA_ID_5':np.int16,'count_5':np.uint16,\n",
    "                        'ANTENNA_ID_6':np.int16,'count_6':np.uint16,'ANTENNA_ID_7':np.int16,'count_7':np.uint16,\n",
    "                        'ANTENNA_ID_8':np.int16,'count_8':np.uint16,'ANTENNA_ID_9':np.int16,'count_9':np.uint16}\n",
    "                )\n",
    "## ojo con setear los dtypes para las antennas en np.usigned int pues ahi me transforma todos los -1s en el max del dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "col_labels = [ col.replace(\"count\",\"COUNT\") if (\"count\" in col) else col for col in table.columns]\n",
    "table.columns = col_labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#df['new_col'] = zip(df.lat, df.long)\n",
    "antenna_lat_long_dict = dict(zip(antennas.index.values, zip(antennas.LATITUDE,antennas.LONGITUDE)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import math\n",
    "#earht_radi in kms\n",
    "earth_radi = 6371\n",
    "\n",
    "def distance_on_unit_sphere(lat1, long1, lat2, long2, earth_radi=6371):\n",
    " \n",
    "    # Convert latitude and longitude to \n",
    "    # spherical coordinates in radians.\n",
    "    degrees_to_radians = math.pi/180.0\n",
    "         \n",
    "    # phi = 90 - latitude\n",
    "    phi1 = (90.0 - lat1)*degrees_to_radians\n",
    "    phi2 = (90.0 - lat2)*degrees_to_radians\n",
    "         \n",
    "    # theta = longitude\n",
    "    theta1 = long1*degrees_to_radians\n",
    "    theta2 = long2*degrees_to_radians\n",
    "         \n",
    "    # Compute spherical distance from spherical coordinates.\n",
    "         \n",
    "    # For two locations in spherical coordinates \n",
    "    # (1, theta, phi) and (1, theta', phi')\n",
    "    # cosine( arc length ) = \n",
    "    #    sin phi sin phi' cos(theta-theta') + cos phi cos phi'\n",
    "    # distance = rho * arc length\n",
    "     \n",
    "    cos = (math.sin(phi1)*math.sin(phi2)*math.cos(theta1 - theta2) + \n",
    "           math.cos(phi1)*math.cos(phi2))\n",
    "    arc = math.acos( cos )\n",
    " \n",
    "    # Remember to multiply arc by the radius of the earth \n",
    "    # in your favorite set of units to get length.\n",
    "    return arc*earth_radi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from scipy import spatial"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### checking antenna_id consistency\n",
    "para ver si todos los antenna_ids de nuestra tabla estan en la base de datos de las antennas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def test_antennaid_consistency(row,antenna_id_list):\n",
    "    res = False\n",
    "    col_labels = table.columns\n",
    "    #miramos solo las columnas de antenna_ids \n",
    "    row = row[[col for col in table.columns if (\"ANTENNA\" in col)]]\n",
    "    #filtramos aquellas que tienen ant_id no nulo (==-1)\n",
    "    row = row[row >-1]\n",
    "    #print(row)\n",
    "    #le pregunto a la serie si c/valor esta en el id_list, paso a un ndarray y consulto si todos estan\n",
    "    res = row.isin(antenna_id_list).values.all()\n",
    "    \n",
    "    return res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "antenna_id_list = list(antennas.index.values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "False\n",
      "CPU times: user 2min 18s, sys: 76 ms, total: 2min 18s\n",
      "Wall time: 2min 18s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "consistency = table.sample(10**5).apply(func = (lambda row: test_antennaid_consistency(row,antenna_id_list)),axis='columns')\n",
    "#testeo que todos los resultados sean True\n",
    "print(consistency.values.all())\n",
    "del consistency\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "user = 'FFFA147CB2B23AD12F207F0EFBEB14D0'\n",
    "row = table.loc[user]\n",
    "row = row[row>-1]\n",
    "row = row[[ind for ind in row.index if \"ANTENNA\" in ind]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### END antenna consistency test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "##algoritmo sin convex hull\n",
    "\n",
    "filter_col = [col for col in list(table) if col.startswith('ANTENNA')]\n",
    "\n",
    "def convex_hull_radi(row,antenna_lat_long_dict,filter_col):\n",
    "    #rows are pandas series\n",
    "    #print(row.name)\n",
    "    row =  row[filter_col]\n",
    "    latitudes=[]\n",
    "    longitudes =[]\n",
    "    \n",
    "    #cuidado con los NaNs resultantes del map\n",
    "    for ant_id in (row.map(antenna_lat_long_dict).dropna()):\n",
    "        latitudes += [ant_id[0]] \n",
    "        longitudes += [ant_id[1]]\n",
    "    \n",
    "    \n",
    "    #try:\n",
    "    radii=0\n",
    "    for i in range(len(latitudes)):\n",
    "        for j in range(i):\n",
    "            new_dist= distance_on_unit_sphere(latitudes[i], longitudes[i], latitudes[j], longitudes[j])\n",
    "            if radii< new_dist:\n",
    "                radii = new_dist \n",
    "\n",
    "    return radii\n",
    "    #except:\n",
    "    #    print(row.name)\n",
    "    #    return 0\n",
    "        \n",
    "       \n",
    "#row =test_table.loc[2][filter_col]\n",
    "#latitudes=[]\n",
    "#longitudes =[]\n",
    "#for ant_id in row.map(antenna_lat_long_dict).dropna():\n",
    "#    latitudes += [ant_id[0]] \n",
    "#    longitudes += [ant_id[1]]\n",
    "#latitudes\n",
    "\n",
    "#convex_hull_radi(test_table.loc[12],antenna_lat_long_dict,filter_col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## algoritmo con convexhulll\n",
    "\n",
    "filter_col = [col for col in list(table) if col.startswith('ANTENNA')]\n",
    "\n",
    "def convex_hull_radi(row,antenna_lat_long_dict,filter_col):\n",
    "    #rows are pandas series\n",
    "    #print(row.name)\n",
    "    row =  row[filter_col]\n",
    "    latitudes=[]\n",
    "    longitudes =[]\n",
    "    \n",
    "    #cuidado con los NaNs resultantes del map\n",
    "    for ant_id in (row.map(antenna_lat_long_dict).dropna()):\n",
    "        latitudes += [ant_id[0]] \n",
    "        longitudes += [ant_id[1]]\n",
    "    #case: only 1 antennas\n",
    "    if len(latitudes)<=1:\n",
    "        return 0\n",
    "    \n",
    "  \n",
    "    \n",
    "    #case only 2 antennas, arc distance\n",
    "    elif len(latitudes)==2:\n",
    "        return distance_on_unit_sphere(latitudes[0],longitudes[0],latitudes[1],longitudes[1])\n",
    "    \n",
    "    \n",
    "    elif  len(latitudes)>2:\n",
    "        points = np.array([np.asarray(latitudes).transpose() ,np.asarray(longitudes).transpose()])\n",
    "        points = points.transpose()\n",
    "        \n",
    "        #chequeamos los casos super patologicos que todas las antenas esten sobre la misma linea\n",
    "        if latitudes.count(latitudes[0]) == len(latitudes):\n",
    "            radii=0\n",
    "\n",
    "            for i in range(len(longitudes)-1):\n",
    "                new_dist= distance_on_unit_sphere(latitudes[0], longitudes[i], latitudes[0], longitudes[i+1])\n",
    "                if radii< new_dist:\n",
    "                    radii = new_dist \n",
    "        \n",
    "        #chequear sobre la otra linea\n",
    "        elif longitudes.count(longitudes[0]) == len(longitudes):    \n",
    "            radii=0\n",
    "            for i in range(len(latitudes)-1):\n",
    "                new_dist= distance_on_unit_sphere(latitudes[i], longitudes[0], latitudes[i+1], longitudes[0])\n",
    "                if radii< new_dist:\n",
    "                    radii = new_dist \n",
    "        else:\n",
    "            try:\n",
    "                chull = spatial.ConvexHull(points)\n",
    "                #points = spatial.ConvexHull(points).points\n",
    "                points = chull.points\n",
    "            except:\n",
    "                print(row.name)\n",
    "            vert = [list(point) for point in chull.points] #chull vertices\n",
    "\n",
    "            radii=0\n",
    "            for i in range(len(vert)):\n",
    "                for j in range(i):\n",
    "                    new_dist= distance_on_unit_sphere(vert[i][0], vert[i][1], vert[j][0], vert[j][1])\n",
    "                    if radii< new_dist:\n",
    "                        radii = new_dist \n",
    "\n",
    "        return radii\n",
    "        #return (\"%s,len(lats) is %d\" %(radii,len(latitudes)))\n",
    "\n",
    "#convex_hull_radi(test_table.loc[12],antenna_lat_long_dict,filter_col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "#mini_sample =  table.sample(10**3)\n",
    "table['MOBILITY_DIAMETER']= table.apply(lambda row: convex_hull_radi(row,antenna_lat_long_dict,filter_col),axis=1)\n",
    "\n",
    "print(\"elapsed time is %s seconds for %d amount of rows\" % (time.time()-start_time,len(mini_sample)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(-99.191000000000003, -99.191000000000003, -99.191000000000003)"
      ]
     },
     "execution_count": 309,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antennas.loc[1695].LONGITUDE, antennas.loc[1311].LONGITUDE,antennas.loc[1179].LONGITUDE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 317,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'EXPOSED', u'ANTENNA_ID_0', u'COUNT_0', u'ANTENNA_ID_1', u'COUNT_1',\n",
       "       u'ANTENNA_ID_2', u'COUNT_2', u'ANTENNA_ID_3', u'COUNT_3',\n",
       "       u'ANTENNA_ID_4', u'COUNT_4', u'ANTENNA_ID_5', u'COUNT_5',\n",
       "       u'ANTENNA_ID_6', u'COUNT_6', u'ANTENNA_ID_7', u'COUNT_7',\n",
       "       u'ANTENNA_ID_8', u'COUNT_8', u'ANTENNA_ID_9', u'COUNT_9', u'EPIDEMIC',\n",
       "       u'MOBILITY_DIAMETER'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 317,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## un par de corridas usando el diametro del convex/hull dan \n",
    "elapsed time is 253.765459061 for 40000 amount of rows\n",
    "\n",
    "elapsed time is 249.712596893 seconds for 40000 amount of rows\n",
    "\n",
    "elapsed time is 227.963900089 seconds for 40000 amount of rows\n",
    "\n",
    "elapsed time is 57.2980730534 seconds for 10000 amount of rows\n",
    "\n",
    "elapsed time is 0.896952867508 seconds for 10 amount of rows\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Testeamos la validez de resultados\n",
    "Nos fijamos en todos los users que tengan al menos dos antenas, en los users que solo tienen 1 antenna, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(312, 23)"
      ]
     },
     "execution_count": 249,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mini_sample[(mini_sample['MOBILITY_DIAMETER']<10**3) & (mini_sample['ANTENNA_ID_0']>-1)].shape\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EXPOSED</th>\n",
       "      <th>ANTENNA_ID_0</th>\n",
       "      <th>COUNT_0</th>\n",
       "      <th>ANTENNA_ID_1</th>\n",
       "      <th>COUNT_1</th>\n",
       "      <th>ANTENNA_ID_2</th>\n",
       "      <th>COUNT_2</th>\n",
       "      <th>ANTENNA_ID_3</th>\n",
       "      <th>COUNT_3</th>\n",
       "      <th>ANTENNA_ID_4</th>\n",
       "      <th>...</th>\n",
       "      <th>COUNT_5</th>\n",
       "      <th>ANTENNA_ID_6</th>\n",
       "      <th>COUNT_6</th>\n",
       "      <th>ANTENNA_ID_7</th>\n",
       "      <th>COUNT_7</th>\n",
       "      <th>ANTENNA_ID_8</th>\n",
       "      <th>COUNT_8</th>\n",
       "      <th>ANTENNA_ID_9</th>\n",
       "      <th>COUNT_9</th>\n",
       "      <th>EPIDEMIC</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USER</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>FFFF78CB080636632B2D1DE7A7BFAB03</th>\n",
       "      <td>False</td>\n",
       "      <td>3415</td>\n",
       "      <td>21</td>\n",
       "      <td>2969</td>\n",
       "      <td>5</td>\n",
       "      <td>1320</td>\n",
       "      <td>3</td>\n",
       "      <td>2471</td>\n",
       "      <td>3</td>\n",
       "      <td>1115</td>\n",
       "      <td>...</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FFFF51FDDD55A2E24D074DE30C4798E9</th>\n",
       "      <td>True</td>\n",
       "      <td>1207</td>\n",
       "      <td>15</td>\n",
       "      <td>1171</td>\n",
       "      <td>14</td>\n",
       "      <td>1820</td>\n",
       "      <td>9</td>\n",
       "      <td>583</td>\n",
       "      <td>8</td>\n",
       "      <td>2026</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>1831</td>\n",
       "      <td>4</td>\n",
       "      <td>1625</td>\n",
       "      <td>2</td>\n",
       "      <td>401</td>\n",
       "      <td>1</td>\n",
       "      <td>825</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FFFE1EDDA25369CBC467536FA6A787FD</th>\n",
       "      <td>True</td>\n",
       "      <td>1847</td>\n",
       "      <td>22</td>\n",
       "      <td>1753</td>\n",
       "      <td>14</td>\n",
       "      <td>1422</td>\n",
       "      <td>11</td>\n",
       "      <td>1028</td>\n",
       "      <td>10</td>\n",
       "      <td>765</td>\n",
       "      <td>...</td>\n",
       "      <td>9</td>\n",
       "      <td>862</td>\n",
       "      <td>8</td>\n",
       "      <td>2463</td>\n",
       "      <td>7</td>\n",
       "      <td>1416</td>\n",
       "      <td>6</td>\n",
       "      <td>871</td>\n",
       "      <td>5</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FFFD0A912A959CDFCFFAB2F93BFBA435</th>\n",
       "      <td>True</td>\n",
       "      <td>1407</td>\n",
       "      <td>18</td>\n",
       "      <td>1702</td>\n",
       "      <td>8</td>\n",
       "      <td>1613</td>\n",
       "      <td>6</td>\n",
       "      <td>1704</td>\n",
       "      <td>5</td>\n",
       "      <td>1394</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>818</td>\n",
       "      <td>2</td>\n",
       "      <td>958</td>\n",
       "      <td>2</td>\n",
       "      <td>726</td>\n",
       "      <td>1</td>\n",
       "      <td>848</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FFFCB235D6D917DDF350F5B11CCB0EE8</th>\n",
       "      <td>True</td>\n",
       "      <td>17</td>\n",
       "      <td>74</td>\n",
       "      <td>174</td>\n",
       "      <td>33</td>\n",
       "      <td>1123</td>\n",
       "      <td>4</td>\n",
       "      <td>1178</td>\n",
       "      <td>4</td>\n",
       "      <td>173</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>503</td>\n",
       "      <td>1</td>\n",
       "      <td>1039</td>\n",
       "      <td>1</td>\n",
       "      <td>1228</td>\n",
       "      <td>1</td>\n",
       "      <td>1327</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 EXPOSED  ANTENNA_ID_0  COUNT_0  ANTENNA_ID_1  \\\n",
       "USER                                                                            \n",
       "FFFF78CB080636632B2D1DE7A7BFAB03   False          3415       21          2969   \n",
       "FFFF51FDDD55A2E24D074DE30C4798E9    True          1207       15          1171   \n",
       "FFFE1EDDA25369CBC467536FA6A787FD    True          1847       22          1753   \n",
       "FFFD0A912A959CDFCFFAB2F93BFBA435    True          1407       18          1702   \n",
       "FFFCB235D6D917DDF350F5B11CCB0EE8    True            17       74           174   \n",
       "\n",
       "                                  COUNT_1  ANTENNA_ID_2  COUNT_2  \\\n",
       "USER                                                               \n",
       "FFFF78CB080636632B2D1DE7A7BFAB03        5          1320        3   \n",
       "FFFF51FDDD55A2E24D074DE30C4798E9       14          1820        9   \n",
       "FFFE1EDDA25369CBC467536FA6A787FD       14          1422       11   \n",
       "FFFD0A912A959CDFCFFAB2F93BFBA435        8          1613        6   \n",
       "FFFCB235D6D917DDF350F5B11CCB0EE8       33          1123        4   \n",
       "\n",
       "                                  ANTENNA_ID_3  COUNT_3  ANTENNA_ID_4  \\\n",
       "USER                                                                    \n",
       "FFFF78CB080636632B2D1DE7A7BFAB03          2471        3          1115   \n",
       "FFFF51FDDD55A2E24D074DE30C4798E9           583        8          2026   \n",
       "FFFE1EDDA25369CBC467536FA6A787FD          1028       10           765   \n",
       "FFFD0A912A959CDFCFFAB2F93BFBA435          1704        5          1394   \n",
       "FFFCB235D6D917DDF350F5B11CCB0EE8          1178        4           173   \n",
       "\n",
       "                                    ...     COUNT_5  ANTENNA_ID_6  COUNT_6  \\\n",
       "USER                                ...                                      \n",
       "FFFF78CB080636632B2D1DE7A7BFAB03    ...       65535            -1    65535   \n",
       "FFFF51FDDD55A2E24D074DE30C4798E9    ...           4          1831        4   \n",
       "FFFE1EDDA25369CBC467536FA6A787FD    ...           9           862        8   \n",
       "FFFD0A912A959CDFCFFAB2F93BFBA435    ...           3           818        2   \n",
       "FFFCB235D6D917DDF350F5B11CCB0EE8    ...           2           503        1   \n",
       "\n",
       "                                  ANTENNA_ID_7  COUNT_7  ANTENNA_ID_8  \\\n",
       "USER                                                                    \n",
       "FFFF78CB080636632B2D1DE7A7BFAB03            -1    65535            -1   \n",
       "FFFF51FDDD55A2E24D074DE30C4798E9          1625        2           401   \n",
       "FFFE1EDDA25369CBC467536FA6A787FD          2463        7          1416   \n",
       "FFFD0A912A959CDFCFFAB2F93BFBA435           958        2           726   \n",
       "FFFCB235D6D917DDF350F5B11CCB0EE8          1039        1          1228   \n",
       "\n",
       "                                  COUNT_8  ANTENNA_ID_9  COUNT_9  EPIDEMIC  \n",
       "USER                                                                        \n",
       "FFFF78CB080636632B2D1DE7A7BFAB03    65535            -1    65535     False  \n",
       "FFFF51FDDD55A2E24D074DE30C4798E9        1           825        1     False  \n",
       "FFFE1EDDA25369CBC467536FA6A787FD        6           871        5      True  \n",
       "FFFD0A912A959CDFCFFAB2F93BFBA435        1           848        1     False  \n",
       "FFFCB235D6D917DDF350F5B11CCB0EE8        1          1327        1      True  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3667451, (3667451, 22))"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(np.unique(table.index.values)), table.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(5, 23)\n",
      "ANTENNA_ID_0    1149\n",
      "ANTENNA_ID_1    1573\n",
      "ANTENNA_ID_2    2812\n",
      "ANTENNA_ID_3    2948\n",
      "ANTENNA_ID_4      82\n",
      "ANTENNA_ID_5     189\n",
      "ANTENNA_ID_6     233\n",
      "ANTENNA_ID_7     713\n",
      "ANTENNA_ID_8    1477\n",
      "ANTENNA_ID_9    1493\n",
      "Name: 5D51048D591291F351EF09EADBD12F53, dtype: object\n",
      "ANTENNA_ID_0      29\n",
      "ANTENNA_ID_1    1700\n",
      "ANTENNA_ID_2      73\n",
      "ANTENNA_ID_3    2347\n",
      "ANTENNA_ID_4      72\n",
      "ANTENNA_ID_5    1235\n",
      "ANTENNA_ID_6    1450\n",
      "ANTENNA_ID_7    1561\n",
      "ANTENNA_ID_8     674\n",
      "ANTENNA_ID_9    1290\n",
      "Name: 7BC884A1A97BEA2573B0BE18F7DB085C, dtype: object\n",
      "ANTENNA_ID_0    1637\n",
      "ANTENNA_ID_1    1352\n",
      "ANTENNA_ID_2    1176\n",
      "ANTENNA_ID_3    2026\n",
      "ANTENNA_ID_4     268\n",
      "ANTENNA_ID_5     942\n",
      "ANTENNA_ID_6    1167\n",
      "ANTENNA_ID_7    1255\n",
      "ANTENNA_ID_8    1324\n",
      "ANTENNA_ID_9    2968\n",
      "Name: DD1ED3EB5C1AD91A7D590C5E1841F684, dtype: object\n",
      "ANTENNA_ID_0    2043\n",
      "ANTENNA_ID_1    1080\n",
      "ANTENNA_ID_2    2323\n",
      "ANTENNA_ID_3    2561\n",
      "ANTENNA_ID_4    2269\n",
      "ANTENNA_ID_5    2268\n",
      "ANTENNA_ID_6    1097\n",
      "ANTENNA_ID_7    1979\n",
      "ANTENNA_ID_8    3152\n",
      "ANTENNA_ID_9     571\n",
      "Name: DE28C1739C7885D653455F2EED4D58EE, dtype: object\n",
      "ANTENNA_ID_0     254\n",
      "ANTENNA_ID_1     267\n",
      "ANTENNA_ID_2    1613\n",
      "ANTENNA_ID_3     744\n",
      "ANTENNA_ID_4    1545\n",
      "ANTENNA_ID_5     205\n",
      "ANTENNA_ID_6     791\n",
      "ANTENNA_ID_7     956\n",
      "ANTENNA_ID_8    1103\n",
      "ANTENNA_ID_9    1327\n",
      "Name: 876104BEC6734C7BB6AEBC227F13BEFF, dtype: object\n"
     ]
    }
   ],
   "source": [
    "#mob_test = mini_sample['mobility_diameter']\n",
    "results = mini_sample[(mini_sample['MOBILITY_DIAMETER']<10**3) & (mini_sample['ANTENNA_ID_8']>-1)].head()\n",
    "print(results.shape)\n",
    "for user in results.index:\n",
    "    row = table.loc[user]\n",
    "    row = row[row>-1]\n",
    "    row = row[[ind for ind in row.index if \"ANTENNA\" in ind]]\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 265,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#testeamos que todos los users con 1 antenna tengan result ==0 \n",
    "mob_test = mini_sample['MOBILITY_DIAMETER']\n",
    "results = mini_sample[(mini_sample['MOBILITY_DIAMETER']<10) & (mini_sample['ANTENNA_ID_1']==-1)]\n",
    "#mob_test[(mob_test.values<10) & (mini_sample['ANTENNA_ID_1']==-1)]\n",
    "(results['MOBILITY_DIAMETER'].values==0).all()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### End mobility diameter result tests, Save table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 269,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ANTENNA_ID_0</th>\n",
       "      <th>ANTENNA_ID_1</th>\n",
       "      <th>ANTENNA_ID_2</th>\n",
       "      <th>ANTENNA_ID_3</th>\n",
       "      <th>ANTENNA_ID_4</th>\n",
       "      <th>ANTENNA_ID_5</th>\n",
       "      <th>ANTENNA_ID_6</th>\n",
       "      <th>ANTENNA_ID_7</th>\n",
       "      <th>ANTENNA_ID_8</th>\n",
       "      <th>ANTENNA_ID_9</th>\n",
       "      <th>MOBILITY_DIAMETER</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USER</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9AF7A02045CB182FE90A88C9D03130AB</th>\n",
       "      <td>2178</td>\n",
       "      <td>392</td>\n",
       "      <td>2010</td>\n",
       "      <td>709</td>\n",
       "      <td>1407</td>\n",
       "      <td>1412</td>\n",
       "      <td>2084</td>\n",
       "      <td>520</td>\n",
       "      <td>2041</td>\n",
       "      <td>1083</td>\n",
       "      <td>1368.713640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45AEB0A267D2BFFD3983EF2C95CA206B</th>\n",
       "      <td>2964</td>\n",
       "      <td>2943</td>\n",
       "      <td>2301</td>\n",
       "      <td>3673</td>\n",
       "      <td>1012</td>\n",
       "      <td>3790</td>\n",
       "      <td>634</td>\n",
       "      <td>3681</td>\n",
       "      <td>1463</td>\n",
       "      <td>2404</td>\n",
       "      <td>2325.646079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3243E73992D14766D85D1F282F4C7690</th>\n",
       "      <td>291</td>\n",
       "      <td>2596</td>\n",
       "      <td>561</td>\n",
       "      <td>655</td>\n",
       "      <td>887</td>\n",
       "      <td>3143</td>\n",
       "      <td>3577</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>1594.044422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32F7FFA33F10A88CEDEFE8D9BD4060F0</th>\n",
       "      <td>20</td>\n",
       "      <td>22</td>\n",
       "      <td>31</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>707.770266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2A50CA984894ADD3989C8920D4B479AE</th>\n",
       "      <td>400</td>\n",
       "      <td>161</td>\n",
       "      <td>2322</td>\n",
       "      <td>1216</td>\n",
       "      <td>1326</td>\n",
       "      <td>1292</td>\n",
       "      <td>1565</td>\n",
       "      <td>637</td>\n",
       "      <td>1088</td>\n",
       "      <td>1205</td>\n",
       "      <td>1716.188192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A81BC0F14993A91B60E780236F7EBB0E</th>\n",
       "      <td>2175</td>\n",
       "      <td>780</td>\n",
       "      <td>911</td>\n",
       "      <td>1820</td>\n",
       "      <td>1766</td>\n",
       "      <td>1991</td>\n",
       "      <td>3199</td>\n",
       "      <td>1043</td>\n",
       "      <td>1649</td>\n",
       "      <td>1938</td>\n",
       "      <td>1534.150943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B8C676F2420B18EB3794FD4E8CAFC2BA</th>\n",
       "      <td>1247</td>\n",
       "      <td>1244</td>\n",
       "      <td>564</td>\n",
       "      <td>1697</td>\n",
       "      <td>307</td>\n",
       "      <td>2788</td>\n",
       "      <td>197</td>\n",
       "      <td>502</td>\n",
       "      <td>609</td>\n",
       "      <td>1171</td>\n",
       "      <td>1549.084125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168F7B598F3AD64679C123DFAC59D9B2</th>\n",
       "      <td>2746</td>\n",
       "      <td>218</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>524.782360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96397BB4600DE9D8440E0F1A77E30740</th>\n",
       "      <td>1637</td>\n",
       "      <td>1758</td>\n",
       "      <td>1438</td>\n",
       "      <td>1990</td>\n",
       "      <td>1324</td>\n",
       "      <td>1385</td>\n",
       "      <td>1750</td>\n",
       "      <td>1429</td>\n",
       "      <td>1601</td>\n",
       "      <td>1621</td>\n",
       "      <td>1447.386187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>575E4BBEA9126F43CFE7207CCA1A5749</th>\n",
       "      <td>85</td>\n",
       "      <td>3151</td>\n",
       "      <td>748</td>\n",
       "      <td>852</td>\n",
       "      <td>72</td>\n",
       "      <td>501</td>\n",
       "      <td>1422</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>1438.496606</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  ANTENNA_ID_0  ANTENNA_ID_1  ANTENNA_ID_2  \\\n",
       "USER                                                                         \n",
       "9AF7A02045CB182FE90A88C9D03130AB          2178           392          2010   \n",
       "45AEB0A267D2BFFD3983EF2C95CA206B          2964          2943          2301   \n",
       "3243E73992D14766D85D1F282F4C7690           291          2596           561   \n",
       "32F7FFA33F10A88CEDEFE8D9BD4060F0            20            22            31   \n",
       "2A50CA984894ADD3989C8920D4B479AE           400           161          2322   \n",
       "A81BC0F14993A91B60E780236F7EBB0E          2175           780           911   \n",
       "B8C676F2420B18EB3794FD4E8CAFC2BA          1247          1244           564   \n",
       "168F7B598F3AD64679C123DFAC59D9B2          2746           218            -1   \n",
       "96397BB4600DE9D8440E0F1A77E30740          1637          1758          1438   \n",
       "575E4BBEA9126F43CFE7207CCA1A5749            85          3151           748   \n",
       "\n",
       "                                  ANTENNA_ID_3  ANTENNA_ID_4  ANTENNA_ID_5  \\\n",
       "USER                                                                         \n",
       "9AF7A02045CB182FE90A88C9D03130AB           709          1407          1412   \n",
       "45AEB0A267D2BFFD3983EF2C95CA206B          3673          1012          3790   \n",
       "3243E73992D14766D85D1F282F4C7690           655           887          3143   \n",
       "32F7FFA33F10A88CEDEFE8D9BD4060F0            -1            -1            -1   \n",
       "2A50CA984894ADD3989C8920D4B479AE          1216          1326          1292   \n",
       "A81BC0F14993A91B60E780236F7EBB0E          1820          1766          1991   \n",
       "B8C676F2420B18EB3794FD4E8CAFC2BA          1697           307          2788   \n",
       "168F7B598F3AD64679C123DFAC59D9B2            -1            -1            -1   \n",
       "96397BB4600DE9D8440E0F1A77E30740          1990          1324          1385   \n",
       "575E4BBEA9126F43CFE7207CCA1A5749           852            72           501   \n",
       "\n",
       "                                  ANTENNA_ID_6  ANTENNA_ID_7  ANTENNA_ID_8  \\\n",
       "USER                                                                         \n",
       "9AF7A02045CB182FE90A88C9D03130AB          2084           520          2041   \n",
       "45AEB0A267D2BFFD3983EF2C95CA206B           634          3681          1463   \n",
       "3243E73992D14766D85D1F282F4C7690          3577            -1            -1   \n",
       "32F7FFA33F10A88CEDEFE8D9BD4060F0            -1            -1            -1   \n",
       "2A50CA984894ADD3989C8920D4B479AE          1565           637          1088   \n",
       "A81BC0F14993A91B60E780236F7EBB0E          3199          1043          1649   \n",
       "B8C676F2420B18EB3794FD4E8CAFC2BA           197           502           609   \n",
       "168F7B598F3AD64679C123DFAC59D9B2            -1            -1            -1   \n",
       "96397BB4600DE9D8440E0F1A77E30740          1750          1429          1601   \n",
       "575E4BBEA9126F43CFE7207CCA1A5749          1422            -1            -1   \n",
       "\n",
       "                                  ANTENNA_ID_9  MOBILITY_DIAMETER  \n",
       "USER                                                               \n",
       "9AF7A02045CB182FE90A88C9D03130AB          1083        1368.713640  \n",
       "45AEB0A267D2BFFD3983EF2C95CA206B          2404        2325.646079  \n",
       "3243E73992D14766D85D1F282F4C7690            -1        1594.044422  \n",
       "32F7FFA33F10A88CEDEFE8D9BD4060F0            -1         707.770266  \n",
       "2A50CA984894ADD3989C8920D4B479AE          1205        1716.188192  \n",
       "A81BC0F14993A91B60E780236F7EBB0E          1938        1534.150943  \n",
       "B8C676F2420B18EB3794FD4E8CAFC2BA          1171        1549.084125  \n",
       "168F7B598F3AD64679C123DFAC59D9B2            -1         524.782360  \n",
       "96397BB4600DE9D8440E0F1A77E30740          1621        1447.386187  \n",
       "575E4BBEA9126F43CFE7207CCA1A5749            -1        1438.496606  "
      ]
     },
     "execution_count": 269,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mini_sample[[col for col in mini_sample.columns if (\"ANTENNA\" in col or \"MOBILITY\" in col)]].sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 318,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "table.to_csv(output_file, \n",
    "             index = True,\n",
    "            header = True,\n",
    "            compression='gzip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "173M\t/home/juan/mobility-study/output2.txt.gz\r\n"
     ]
    }
   ],
   "source": [
    "!du -ha $output_file*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 323,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EXPOSED</th>\n",
       "      <th>ANTENNA_ID_0</th>\n",
       "      <th>COUNT_0</th>\n",
       "      <th>ANTENNA_ID_1</th>\n",
       "      <th>COUNT_1</th>\n",
       "      <th>ANTENNA_ID_2</th>\n",
       "      <th>COUNT_2</th>\n",
       "      <th>ANTENNA_ID_3</th>\n",
       "      <th>COUNT_3</th>\n",
       "      <th>ANTENNA_ID_4</th>\n",
       "      <th>...</th>\n",
       "      <th>ANTENNA_ID_6</th>\n",
       "      <th>COUNT_6</th>\n",
       "      <th>ANTENNA_ID_7</th>\n",
       "      <th>COUNT_7</th>\n",
       "      <th>ANTENNA_ID_8</th>\n",
       "      <th>COUNT_8</th>\n",
       "      <th>ANTENNA_ID_9</th>\n",
       "      <th>COUNT_9</th>\n",
       "      <th>EPIDEMIC</th>\n",
       "      <th>MOBILITY_DIAMETER</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USER</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18113EA238478375460839F4F5F3B017</th>\n",
       "      <td>True</td>\n",
       "      <td>1083</td>\n",
       "      <td>10</td>\n",
       "      <td>798</td>\n",
       "      <td>5</td>\n",
       "      <td>1608</td>\n",
       "      <td>5</td>\n",
       "      <td>248</td>\n",
       "      <td>4</td>\n",
       "      <td>849</td>\n",
       "      <td>...</td>\n",
       "      <td>2679</td>\n",
       "      <td>3</td>\n",
       "      <td>369</td>\n",
       "      <td>2</td>\n",
       "      <td>1016</td>\n",
       "      <td>1</td>\n",
       "      <td>1245</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>1596.220319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4C21FDD80CD47E153298C70D3527F079</th>\n",
       "      <td>True</td>\n",
       "      <td>3183</td>\n",
       "      <td>37</td>\n",
       "      <td>2875</td>\n",
       "      <td>31</td>\n",
       "      <td>2298</td>\n",
       "      <td>26</td>\n",
       "      <td>1449</td>\n",
       "      <td>16</td>\n",
       "      <td>2299</td>\n",
       "      <td>...</td>\n",
       "      <td>2296</td>\n",
       "      <td>4</td>\n",
       "      <td>2745</td>\n",
       "      <td>4</td>\n",
       "      <td>1154</td>\n",
       "      <td>3</td>\n",
       "      <td>1258</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>2847.255740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5B69FC601B10AD78AF0E250899DA4428</th>\n",
       "      <td>True</td>\n",
       "      <td>989</td>\n",
       "      <td>8</td>\n",
       "      <td>1379</td>\n",
       "      <td>8</td>\n",
       "      <td>327</td>\n",
       "      <td>7</td>\n",
       "      <td>990</td>\n",
       "      <td>6</td>\n",
       "      <td>1431</td>\n",
       "      <td>...</td>\n",
       "      <td>1610</td>\n",
       "      <td>3</td>\n",
       "      <td>988</td>\n",
       "      <td>1</td>\n",
       "      <td>1074</td>\n",
       "      <td>1</td>\n",
       "      <td>1098</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>737.250275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3104F21B0C633119F9EB28433F5DF0AC</th>\n",
       "      <td>True</td>\n",
       "      <td>2277</td>\n",
       "      <td>13</td>\n",
       "      <td>1990</td>\n",
       "      <td>5</td>\n",
       "      <td>1758</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>True</td>\n",
       "      <td>739.327422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794E3583897B762E37DB26CB29BF2C95</th>\n",
       "      <td>True</td>\n",
       "      <td>1461</td>\n",
       "      <td>27</td>\n",
       "      <td>3825</td>\n",
       "      <td>19</td>\n",
       "      <td>2895</td>\n",
       "      <td>18</td>\n",
       "      <td>1012</td>\n",
       "      <td>17</td>\n",
       "      <td>2584</td>\n",
       "      <td>...</td>\n",
       "      <td>2055</td>\n",
       "      <td>6</td>\n",
       "      <td>2275</td>\n",
       "      <td>6</td>\n",
       "      <td>3787</td>\n",
       "      <td>4</td>\n",
       "      <td>2043</td>\n",
       "      <td>3</td>\n",
       "      <td>True</td>\n",
       "      <td>2655.544887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D3B4C0718A0A4B8B3A0495B9256C1262</th>\n",
       "      <td>True</td>\n",
       "      <td>540</td>\n",
       "      <td>4</td>\n",
       "      <td>1315</td>\n",
       "      <td>4</td>\n",
       "      <td>2013</td>\n",
       "      <td>3</td>\n",
       "      <td>2014</td>\n",
       "      <td>3</td>\n",
       "      <td>763</td>\n",
       "      <td>...</td>\n",
       "      <td>2520</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>False</td>\n",
       "      <td>701.064183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D0085B84A7EB85956E996C0EF2201453</th>\n",
       "      <td>True</td>\n",
       "      <td>594</td>\n",
       "      <td>43</td>\n",
       "      <td>2276</td>\n",
       "      <td>27</td>\n",
       "      <td>515</td>\n",
       "      <td>20</td>\n",
       "      <td>1386</td>\n",
       "      <td>17</td>\n",
       "      <td>2316</td>\n",
       "      <td>...</td>\n",
       "      <td>2174</td>\n",
       "      <td>13</td>\n",
       "      <td>771</td>\n",
       "      <td>12</td>\n",
       "      <td>1319</td>\n",
       "      <td>10</td>\n",
       "      <td>1721</td>\n",
       "      <td>10</td>\n",
       "      <td>True</td>\n",
       "      <td>742.937402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B10EEF8023AA67CD510AF3F9BD0D1405</th>\n",
       "      <td>True</td>\n",
       "      <td>399</td>\n",
       "      <td>33</td>\n",
       "      <td>1508</td>\n",
       "      <td>20</td>\n",
       "      <td>1415</td>\n",
       "      <td>11</td>\n",
       "      <td>526</td>\n",
       "      <td>10</td>\n",
       "      <td>1483</td>\n",
       "      <td>...</td>\n",
       "      <td>873</td>\n",
       "      <td>6</td>\n",
       "      <td>1573</td>\n",
       "      <td>5</td>\n",
       "      <td>111</td>\n",
       "      <td>4</td>\n",
       "      <td>1356</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "      <td>559.340031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3ECB89A445B6C708219724E905EF17D6</th>\n",
       "      <td>True</td>\n",
       "      <td>197</td>\n",
       "      <td>9</td>\n",
       "      <td>2362</td>\n",
       "      <td>8</td>\n",
       "      <td>2084</td>\n",
       "      <td>4</td>\n",
       "      <td>2278</td>\n",
       "      <td>4</td>\n",
       "      <td>2197</td>\n",
       "      <td>...</td>\n",
       "      <td>391</td>\n",
       "      <td>1</td>\n",
       "      <td>484</td>\n",
       "      <td>1</td>\n",
       "      <td>572</td>\n",
       "      <td>1</td>\n",
       "      <td>709</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>1368.713640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1EEBCA4BD819E8A29E5ECBBE28AB8FB4</th>\n",
       "      <td>True</td>\n",
       "      <td>2014</td>\n",
       "      <td>3</td>\n",
       "      <td>894</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>True</td>\n",
       "      <td>703.007715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90D07F4337386F033351CB9448115C7D</th>\n",
       "      <td>True</td>\n",
       "      <td>2034</td>\n",
       "      <td>61</td>\n",
       "      <td>2927</td>\n",
       "      <td>60</td>\n",
       "      <td>2978</td>\n",
       "      <td>42</td>\n",
       "      <td>3504</td>\n",
       "      <td>38</td>\n",
       "      <td>2717</td>\n",
       "      <td>...</td>\n",
       "      <td>1282</td>\n",
       "      <td>2</td>\n",
       "      <td>1642</td>\n",
       "      <td>1</td>\n",
       "      <td>1729</td>\n",
       "      <td>1</td>\n",
       "      <td>2550</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>2281.388691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A39FF837B196D60571B6CDE4F90144E9</th>\n",
       "      <td>True</td>\n",
       "      <td>1200</td>\n",
       "      <td>48</td>\n",
       "      <td>2932</td>\n",
       "      <td>43</td>\n",
       "      <td>390</td>\n",
       "      <td>39</td>\n",
       "      <td>1571</td>\n",
       "      <td>11</td>\n",
       "      <td>255</td>\n",
       "      <td>...</td>\n",
       "      <td>1063</td>\n",
       "      <td>2</td>\n",
       "      <td>1998</td>\n",
       "      <td>1</td>\n",
       "      <td>2933</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>True</td>\n",
       "      <td>679.380053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643BF4CF7AB745F8C57286A3DAE05BA4</th>\n",
       "      <td>True</td>\n",
       "      <td>2385</td>\n",
       "      <td>21</td>\n",
       "      <td>3849</td>\n",
       "      <td>4</td>\n",
       "      <td>350</td>\n",
       "      <td>2</td>\n",
       "      <td>3245</td>\n",
       "      <td>2</td>\n",
       "      <td>1997</td>\n",
       "      <td>...</td>\n",
       "      <td>3911</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>False</td>\n",
       "      <td>2306.405188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6373B991BA573D0A55CE1CC5E92F2D85</th>\n",
       "      <td>False</td>\n",
       "      <td>1219</td>\n",
       "      <td>6</td>\n",
       "      <td>1169</td>\n",
       "      <td>4</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>False</td>\n",
       "      <td>309.191035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CDB529176C404C97A791D3C760CA9CD8</th>\n",
       "      <td>True</td>\n",
       "      <td>2470</td>\n",
       "      <td>7</td>\n",
       "      <td>650</td>\n",
       "      <td>4</td>\n",
       "      <td>1915</td>\n",
       "      <td>4</td>\n",
       "      <td>2515</td>\n",
       "      <td>4</td>\n",
       "      <td>647</td>\n",
       "      <td>...</td>\n",
       "      <td>1392</td>\n",
       "      <td>2</td>\n",
       "      <td>1908</td>\n",
       "      <td>2</td>\n",
       "      <td>1955</td>\n",
       "      <td>2</td>\n",
       "      <td>3051</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>1105.111336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B456A8D6A2BBB7F08ABA945E17DDF905</th>\n",
       "      <td>True</td>\n",
       "      <td>2471</td>\n",
       "      <td>21</td>\n",
       "      <td>2470</td>\n",
       "      <td>10</td>\n",
       "      <td>582</td>\n",
       "      <td>9</td>\n",
       "      <td>581</td>\n",
       "      <td>6</td>\n",
       "      <td>2268</td>\n",
       "      <td>...</td>\n",
       "      <td>583</td>\n",
       "      <td>2</td>\n",
       "      <td>851</td>\n",
       "      <td>2</td>\n",
       "      <td>3630</td>\n",
       "      <td>1</td>\n",
       "      <td>4042</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>3123.598053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71FE2223D5BD0FE4C31551A4F8153F39</th>\n",
       "      <td>True</td>\n",
       "      <td>484</td>\n",
       "      <td>77</td>\n",
       "      <td>1150</td>\n",
       "      <td>12</td>\n",
       "      <td>1154</td>\n",
       "      <td>12</td>\n",
       "      <td>1449</td>\n",
       "      <td>10</td>\n",
       "      <td>3335</td>\n",
       "      <td>...</td>\n",
       "      <td>2800</td>\n",
       "      <td>9</td>\n",
       "      <td>639</td>\n",
       "      <td>5</td>\n",
       "      <td>2057</td>\n",
       "      <td>3</td>\n",
       "      <td>944</td>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "      <td>1739.022773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C07270AB11F4906B7A5F90AA4A8A0AAE</th>\n",
       "      <td>True</td>\n",
       "      <td>3503</td>\n",
       "      <td>39</td>\n",
       "      <td>2550</td>\n",
       "      <td>33</td>\n",
       "      <td>252</td>\n",
       "      <td>31</td>\n",
       "      <td>502</td>\n",
       "      <td>31</td>\n",
       "      <td>3743</td>\n",
       "      <td>...</td>\n",
       "      <td>2556</td>\n",
       "      <td>18</td>\n",
       "      <td>1595</td>\n",
       "      <td>16</td>\n",
       "      <td>2348</td>\n",
       "      <td>16</td>\n",
       "      <td>2714</td>\n",
       "      <td>14</td>\n",
       "      <td>False</td>\n",
       "      <td>3001.055968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6622F857282ABA17B92280AC5BE887B4</th>\n",
       "      <td>True</td>\n",
       "      <td>586</td>\n",
       "      <td>36</td>\n",
       "      <td>332</td>\n",
       "      <td>13</td>\n",
       "      <td>451</td>\n",
       "      <td>8</td>\n",
       "      <td>441</td>\n",
       "      <td>4</td>\n",
       "      <td>265</td>\n",
       "      <td>...</td>\n",
       "      <td>585</td>\n",
       "      <td>2</td>\n",
       "      <td>258</td>\n",
       "      <td>1</td>\n",
       "      <td>350</td>\n",
       "      <td>1</td>\n",
       "      <td>448</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>544.082280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B64565ACE0FB546233C4A2EDB19EE30A</th>\n",
       "      <td>True</td>\n",
       "      <td>620</td>\n",
       "      <td>9</td>\n",
       "      <td>619</td>\n",
       "      <td>7</td>\n",
       "      <td>3051</td>\n",
       "      <td>4</td>\n",
       "      <td>2029</td>\n",
       "      <td>3</td>\n",
       "      <td>1908</td>\n",
       "      <td>...</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>False</td>\n",
       "      <td>914.336724</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 EXPOSED  ANTENNA_ID_0  COUNT_0  ANTENNA_ID_1  \\\n",
       "USER                                                                            \n",
       "18113EA238478375460839F4F5F3B017    True          1083       10           798   \n",
       "4C21FDD80CD47E153298C70D3527F079    True          3183       37          2875   \n",
       "5B69FC601B10AD78AF0E250899DA4428    True           989        8          1379   \n",
       "3104F21B0C633119F9EB28433F5DF0AC    True          2277       13          1990   \n",
       "794E3583897B762E37DB26CB29BF2C95    True          1461       27          3825   \n",
       "D3B4C0718A0A4B8B3A0495B9256C1262    True           540        4          1315   \n",
       "D0085B84A7EB85956E996C0EF2201453    True           594       43          2276   \n",
       "B10EEF8023AA67CD510AF3F9BD0D1405    True           399       33          1508   \n",
       "3ECB89A445B6C708219724E905EF17D6    True           197        9          2362   \n",
       "1EEBCA4BD819E8A29E5ECBBE28AB8FB4    True          2014        3           894   \n",
       "90D07F4337386F033351CB9448115C7D    True          2034       61          2927   \n",
       "A39FF837B196D60571B6CDE4F90144E9    True          1200       48          2932   \n",
       "643BF4CF7AB745F8C57286A3DAE05BA4    True          2385       21          3849   \n",
       "6373B991BA573D0A55CE1CC5E92F2D85   False          1219        6          1169   \n",
       "CDB529176C404C97A791D3C760CA9CD8    True          2470        7           650   \n",
       "B456A8D6A2BBB7F08ABA945E17DDF905    True          2471       21          2470   \n",
       "71FE2223D5BD0FE4C31551A4F8153F39    True           484       77          1150   \n",
       "C07270AB11F4906B7A5F90AA4A8A0AAE    True          3503       39          2550   \n",
       "6622F857282ABA17B92280AC5BE887B4    True           586       36           332   \n",
       "B64565ACE0FB546233C4A2EDB19EE30A    True           620        9           619   \n",
       "\n",
       "                                  COUNT_1  ANTENNA_ID_2  COUNT_2  \\\n",
       "USER                                                               \n",
       "18113EA238478375460839F4F5F3B017        5          1608        5   \n",
       "4C21FDD80CD47E153298C70D3527F079       31          2298       26   \n",
       "5B69FC601B10AD78AF0E250899DA4428        8           327        7   \n",
       "3104F21B0C633119F9EB28433F5DF0AC        5          1758        2   \n",
       "794E3583897B762E37DB26CB29BF2C95       19          2895       18   \n",
       "D3B4C0718A0A4B8B3A0495B9256C1262        4          2013        3   \n",
       "D0085B84A7EB85956E996C0EF2201453       27           515       20   \n",
       "B10EEF8023AA67CD510AF3F9BD0D1405       20          1415       11   \n",
       "3ECB89A445B6C708219724E905EF17D6        8          2084        4   \n",
       "1EEBCA4BD819E8A29E5ECBBE28AB8FB4        1          2018        1   \n",
       "90D07F4337386F033351CB9448115C7D       60          2978       42   \n",
       "A39FF837B196D60571B6CDE4F90144E9       43           390       39   \n",
       "643BF4CF7AB745F8C57286A3DAE05BA4        4           350        2   \n",
       "6373B991BA573D0A55CE1CC5E92F2D85        4            -1    65535   \n",
       "CDB529176C404C97A791D3C760CA9CD8        4          1915        4   \n",
       "B456A8D6A2BBB7F08ABA945E17DDF905       10           582        9   \n",
       "71FE2223D5BD0FE4C31551A4F8153F39       12          1154       12   \n",
       "C07270AB11F4906B7A5F90AA4A8A0AAE       33           252       31   \n",
       "6622F857282ABA17B92280AC5BE887B4       13           451        8   \n",
       "B64565ACE0FB546233C4A2EDB19EE30A        7          3051        4   \n",
       "\n",
       "                                  ANTENNA_ID_3  COUNT_3  ANTENNA_ID_4  \\\n",
       "USER                                                                    \n",
       "18113EA238478375460839F4F5F3B017           248        4           849   \n",
       "4C21FDD80CD47E153298C70D3527F079          1449       16          2299   \n",
       "5B69FC601B10AD78AF0E250899DA4428           990        6          1431   \n",
       "3104F21B0C633119F9EB28433F5DF0AC            -1    65535            -1   \n",
       "794E3583897B762E37DB26CB29BF2C95          1012       17          2584   \n",
       "D3B4C0718A0A4B8B3A0495B9256C1262          2014        3           763   \n",
       "D0085B84A7EB85956E996C0EF2201453          1386       17          2316   \n",
       "B10EEF8023AA67CD510AF3F9BD0D1405           526       10          1483   \n",
       "3ECB89A445B6C708219724E905EF17D6          2278        4          2197   \n",
       "1EEBCA4BD819E8A29E5ECBBE28AB8FB4            -1    65535            -1   \n",
       "90D07F4337386F033351CB9448115C7D          3504       38          2717   \n",
       "A39FF837B196D60571B6CDE4F90144E9          1571       11           255   \n",
       "643BF4CF7AB745F8C57286A3DAE05BA4          3245        2          1997   \n",
       "6373B991BA573D0A55CE1CC5E92F2D85            -1    65535            -1   \n",
       "CDB529176C404C97A791D3C760CA9CD8          2515        4           647   \n",
       "B456A8D6A2BBB7F08ABA945E17DDF905           581        6          2268   \n",
       "71FE2223D5BD0FE4C31551A4F8153F39          1449       10          3335   \n",
       "C07270AB11F4906B7A5F90AA4A8A0AAE           502       31          3743   \n",
       "6622F857282ABA17B92280AC5BE887B4           441        4           265   \n",
       "B64565ACE0FB546233C4A2EDB19EE30A          2029        3          1908   \n",
       "\n",
       "                                        ...          ANTENNA_ID_6  COUNT_6  \\\n",
       "USER                                    ...                                  \n",
       "18113EA238478375460839F4F5F3B017        ...                  2679        3   \n",
       "4C21FDD80CD47E153298C70D3527F079        ...                  2296        4   \n",
       "5B69FC601B10AD78AF0E250899DA4428        ...                  1610        3   \n",
       "3104F21B0C633119F9EB28433F5DF0AC        ...                    -1    65535   \n",
       "794E3583897B762E37DB26CB29BF2C95        ...                  2055        6   \n",
       "D3B4C0718A0A4B8B3A0495B9256C1262        ...                  2520        2   \n",
       "D0085B84A7EB85956E996C0EF2201453        ...                  2174       13   \n",
       "B10EEF8023AA67CD510AF3F9BD0D1405        ...                   873        6   \n",
       "3ECB89A445B6C708219724E905EF17D6        ...                   391        1   \n",
       "1EEBCA4BD819E8A29E5ECBBE28AB8FB4        ...                    -1    65535   \n",
       "90D07F4337386F033351CB9448115C7D        ...                  1282        2   \n",
       "A39FF837B196D60571B6CDE4F90144E9        ...                  1063        2   \n",
       "643BF4CF7AB745F8C57286A3DAE05BA4        ...                  3911        1   \n",
       "6373B991BA573D0A55CE1CC5E92F2D85        ...                    -1    65535   \n",
       "CDB529176C404C97A791D3C760CA9CD8        ...                  1392        2   \n",
       "B456A8D6A2BBB7F08ABA945E17DDF905        ...                   583        2   \n",
       "71FE2223D5BD0FE4C31551A4F8153F39        ...                  2800        9   \n",
       "C07270AB11F4906B7A5F90AA4A8A0AAE        ...                  2556       18   \n",
       "6622F857282ABA17B92280AC5BE887B4        ...                   585        2   \n",
       "B64565ACE0FB546233C4A2EDB19EE30A        ...                    -1    65535   \n",
       "\n",
       "                                  ANTENNA_ID_7  COUNT_7  ANTENNA_ID_8  \\\n",
       "USER                                                                    \n",
       "18113EA238478375460839F4F5F3B017           369        2          1016   \n",
       "4C21FDD80CD47E153298C70D3527F079          2745        4          1154   \n",
       "5B69FC601B10AD78AF0E250899DA4428           988        1          1074   \n",
       "3104F21B0C633119F9EB28433F5DF0AC            -1    65535            -1   \n",
       "794E3583897B762E37DB26CB29BF2C95          2275        6          3787   \n",
       "D3B4C0718A0A4B8B3A0495B9256C1262            -1    65535            -1   \n",
       "D0085B84A7EB85956E996C0EF2201453           771       12          1319   \n",
       "B10EEF8023AA67CD510AF3F9BD0D1405          1573        5           111   \n",
       "3ECB89A445B6C708219724E905EF17D6           484        1           572   \n",
       "1EEBCA4BD819E8A29E5ECBBE28AB8FB4            -1    65535            -1   \n",
       "90D07F4337386F033351CB9448115C7D          1642        1          1729   \n",
       "A39FF837B196D60571B6CDE4F90144E9          1998        1          2933   \n",
       "643BF4CF7AB745F8C57286A3DAE05BA4            -1    65535            -1   \n",
       "6373B991BA573D0A55CE1CC5E92F2D85            -1    65535            -1   \n",
       "CDB529176C404C97A791D3C760CA9CD8          1908        2          1955   \n",
       "B456A8D6A2BBB7F08ABA945E17DDF905           851        2          3630   \n",
       "71FE2223D5BD0FE4C31551A4F8153F39           639        5          2057   \n",
       "C07270AB11F4906B7A5F90AA4A8A0AAE          1595       16          2348   \n",
       "6622F857282ABA17B92280AC5BE887B4           258        1           350   \n",
       "B64565ACE0FB546233C4A2EDB19EE30A            -1    65535            -1   \n",
       "\n",
       "                                  COUNT_8  ANTENNA_ID_9  COUNT_9  EPIDEMIC  \\\n",
       "USER                                                                         \n",
       "18113EA238478375460839F4F5F3B017        1          1245        1      True   \n",
       "4C21FDD80CD47E153298C70D3527F079        3          1258        2     False   \n",
       "5B69FC601B10AD78AF0E250899DA4428        1          1098        1      True   \n",
       "3104F21B0C633119F9EB28433F5DF0AC    65535            -1    65535      True   \n",
       "794E3583897B762E37DB26CB29BF2C95        4          2043        3      True   \n",
       "D3B4C0718A0A4B8B3A0495B9256C1262    65535            -1    65535     False   \n",
       "D0085B84A7EB85956E996C0EF2201453       10          1721       10      True   \n",
       "B10EEF8023AA67CD510AF3F9BD0D1405        4          1356        4     False   \n",
       "3ECB89A445B6C708219724E905EF17D6        1           709        1     False   \n",
       "1EEBCA4BD819E8A29E5ECBBE28AB8FB4    65535            -1    65535      True   \n",
       "90D07F4337386F033351CB9448115C7D        1          2550        1     False   \n",
       "A39FF837B196D60571B6CDE4F90144E9        1            -1    65535      True   \n",
       "643BF4CF7AB745F8C57286A3DAE05BA4    65535            -1    65535     False   \n",
       "6373B991BA573D0A55CE1CC5E92F2D85    65535            -1    65535     False   \n",
       "CDB529176C404C97A791D3C760CA9CD8        2          3051        2     False   \n",
       "B456A8D6A2BBB7F08ABA945E17DDF905        1          4042        1     False   \n",
       "71FE2223D5BD0FE4C31551A4F8153F39        3           944        2      True   \n",
       "C07270AB11F4906B7A5F90AA4A8A0AAE       16          2714       14     False   \n",
       "6622F857282ABA17B92280AC5BE887B4        1           448        1     False   \n",
       "B64565ACE0FB546233C4A2EDB19EE30A    65535            -1    65535     False   \n",
       "\n",
       "                                  MOBILITY_DIAMETER  \n",
       "USER                                                 \n",
       "18113EA238478375460839F4F5F3B017        1596.220319  \n",
       "4C21FDD80CD47E153298C70D3527F079        2847.255740  \n",
       "5B69FC601B10AD78AF0E250899DA4428         737.250275  \n",
       "3104F21B0C633119F9EB28433F5DF0AC         739.327422  \n",
       "794E3583897B762E37DB26CB29BF2C95        2655.544887  \n",
       "D3B4C0718A0A4B8B3A0495B9256C1262         701.064183  \n",
       "D0085B84A7EB85956E996C0EF2201453         742.937402  \n",
       "B10EEF8023AA67CD510AF3F9BD0D1405         559.340031  \n",
       "3ECB89A445B6C708219724E905EF17D6        1368.713640  \n",
       "1EEBCA4BD819E8A29E5ECBBE28AB8FB4         703.007715  \n",
       "90D07F4337386F033351CB9448115C7D        2281.388691  \n",
       "A39FF837B196D60571B6CDE4F90144E9         679.380053  \n",
       "643BF4CF7AB745F8C57286A3DAE05BA4        2306.405188  \n",
       "6373B991BA573D0A55CE1CC5E92F2D85         309.191035  \n",
       "CDB529176C404C97A791D3C760CA9CD8        1105.111336  \n",
       "B456A8D6A2BBB7F08ABA945E17DDF905        3123.598053  \n",
       "71FE2223D5BD0FE4C31551A4F8153F39        1739.022773  \n",
       "C07270AB11F4906B7A5F90AA4A8A0AAE        3001.055968  \n",
       "6622F857282ABA17B92280AC5BE887B4         544.082280  \n",
       "B64565ACE0FB546233C4A2EDB19EE30A         914.336724  \n",
       "\n",
       "[20 rows x 23 columns]"
      ]
     },
     "execution_count": 323,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.sample(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  In case of antenna epidemic tag change\n",
    "En el case en que cambien la definicion de la zona epidemica, esto cambiaria la condicion de epidemicidad para c/ antenna"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#seteamos el lugar de trabajo\n",
    "rootdir=\"/home/juan/mobility-study/\"\n",
    "os.chdir(rootdir)\n",
    "year = \"2015\"; \n",
    "\n",
    "input_file= rootdir +\"output2.txt.gz\"\\\n",
    "                .format(y=year,ms=month_start,me=month_end,\n",
    "                       ds=day_start,de=day_end)\n",
    "    \n",
    "output_file = \"/home/juan/mobility-study/output2\"\n",
    "if week_end == True:\n",
    "    output_file = output_file + \"_wkend\"\n",
    "if night_filter == True:\n",
    "    output_file += \"_ngtfilter\"\n",
    "if hash_map == True:\n",
    "    output_file += \"_user_hash_map\"\n",
    "output_file += \".txt.gz\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "USER,ANTENNA_ID_0,COUNT_0,ANTENNA_ID_1,COUNT_1,ANTENNA_ID_2,COUNT_2,ANTENNA_ID_3,COUNT_3,ANTENNA_ID_4,COUNT_4,ANTENNA_ID_5,COUNT_5,ANTENNA_ID_6,COUNT_6,ANTENNA_ID_7,COUNT_7,ANTENNA_ID_8,COUNT_8,ANTENNA_ID_9,COUNT_9,MOBILITY_DIAMETER,EXPOSED,EPIDEMIC\r\n",
      "FFFF78CB080636632B2D1DE7A7BFAB03,3415,21,2969,5,1320,3,2471,3,1115,2,-1,65535,-1,65535,-1,65535,-1,65535,-1,65535,1063,0,0\r\n",
      "FFFF51FDDD55A2E24D074DE30C4798E9,1207,15,1171,14,1820,9,583,8,2026,5,1066,4,1831,4,1625,2,401,1,825,1,520,1,0\r\n",
      "FFFE1EDDA25369CBC467536FA6A787FD,1847,22,1753,14,1422,11,1028,10,765,9,3072,9,862,8,2463,7,1416,6,871,5,990,1,0\r\n",
      "FFFD0A912A959CDFCFFAB2F93BFBA435,1407,18,1702,8,1613,6,1704,5,1394,3,1398,3,818,2,958,2,726,1,848,1,491,1,0\r\n",
      "FFFCB235D6D917DDF350F5B11CCB0EE8,17,74,174,33,1123,4,1178,4,173,3,890,2,503,1,1039,1,1228,1,1327,1,1613,1,0\r\n",
      "FFFC9EC4F662C44D3006BA69C9F0DE85,876,19,1737,6,1744,5,326,1,378,1,503,1,1118,1,2216,1,2477,1,2962,1,3004,1,1\r\n",
      "FFFB2F18F4CCF253C06B581EA518A7A5,2543,13,704,7,2540,7,1453,6,1642,5,45,4,2393,4,1841,3,3031,3,1009,2,2895,1,0\r\n",
      "FFFA147CB2B23AD12F207F0EFBEB14D0,1857,103,2959,19,1344,2,2309,2,1511,1,2858,1,-1,65535,-1,65535,-1,65535,-1,65535,1633,1,1\r\n",
      "FFFA0736F66FB09200CE1D13E081A946,266,63,1799,46,1790,29,2716,29,537,21,3377,21,1439,18,2715,16,1720,15,2000,14,2558,1,1\r\n",
      "\r\n",
      "gzip: stdout: Broken pipe\r\n"
     ]
    }
   ],
   "source": [
    "!zcat $input_file | head -n10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#primero leo el file de antennas\n",
    "antennas= pd.read_csv('/home/juan/mobility-study/antennas_mexico.csv',sep = \"|\",header=0,index_col=0)\n",
    "antennas.index.rename(\"ANTENNA_ID\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "table = pd.read_csv(\n",
    "                input_file,\n",
    "                engine = 'c',\n",
    "                sep = '|',\n",
    "                header = 0,\n",
    "                index_col=0,\n",
    "                dtype = {'EXPOSED':np.bool,'EPIDEMIC':np.bool, 'MOBILITY_DIAMETER':np.float32,\n",
    "                         'ANTENNA_ID_0':np.int16,'COUNT_0':np.uint16,'ANTENNA_ID_1':np.int16,'COUNT_1':np.uint16,\n",
    "                         'ANTENNA_ID_2':np.int16,'COUNT_2':np.uint16,'ANTENNA_ID_3':np.int16,'COUNT_3':np.uint16,\n",
    "                        'ANTENNA_ID_4':np.int16,'COUNT_4':np.uint16,'ANTENNA_ID_5':np.int16,'COUNT_5':np.uint16,\n",
    "                        'ANTENNA_ID_6':np.int16,'COUNT_6':np.uint16,'ANTENNA_ID_7':np.int16,'COUNT_7':np.uint16,\n",
    "                        'ANTENNA_ID_8':np.int16,'COUNT_8':np.uint16,'ANTENNA_ID_9':np.int16,'COUNT_9':np.uint16}\n",
    "                )\n",
    "table['MOBILITY_DIAMETER'] = table['MOBILITY_DIAMETER'].astype(int)\n",
    "## ojo con setear los dtypes para las antennas en np.usigned int pues ahi me transforma todos los -1s en el max del dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# elimino todas las columnas que quiero re-calcular\n",
    "table.drop('EPIDEMIC',axis=1, inplace=True)\n",
    "table.drop('EXPOSED',axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ANTENNA_ID_0</th>\n",
       "      <th>COUNT_0</th>\n",
       "      <th>ANTENNA_ID_1</th>\n",
       "      <th>COUNT_1</th>\n",
       "      <th>ANTENNA_ID_2</th>\n",
       "      <th>COUNT_2</th>\n",
       "      <th>ANTENNA_ID_3</th>\n",
       "      <th>COUNT_3</th>\n",
       "      <th>ANTENNA_ID_4</th>\n",
       "      <th>COUNT_4</th>\n",
       "      <th>...</th>\n",
       "      <th>COUNT_6</th>\n",
       "      <th>ANTENNA_ID_7</th>\n",
       "      <th>COUNT_7</th>\n",
       "      <th>ANTENNA_ID_8</th>\n",
       "      <th>COUNT_8</th>\n",
       "      <th>ANTENNA_ID_9</th>\n",
       "      <th>COUNT_9</th>\n",
       "      <th>MOBILITY_DIAMETER</th>\n",
       "      <th>EPIDEMIC</th>\n",
       "      <th>EXPOSED</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USER</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>FFFF78CB080636632B2D1DE7A7BFAB03</th>\n",
       "      <td>3415</td>\n",
       "      <td>21</td>\n",
       "      <td>2969</td>\n",
       "      <td>5</td>\n",
       "      <td>1320</td>\n",
       "      <td>3</td>\n",
       "      <td>2471</td>\n",
       "      <td>3</td>\n",
       "      <td>1115</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>1063</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FFFF51FDDD55A2E24D074DE30C4798E9</th>\n",
       "      <td>1207</td>\n",
       "      <td>15</td>\n",
       "      <td>1171</td>\n",
       "      <td>14</td>\n",
       "      <td>1820</td>\n",
       "      <td>9</td>\n",
       "      <td>583</td>\n",
       "      <td>8</td>\n",
       "      <td>2026</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>1625</td>\n",
       "      <td>2</td>\n",
       "      <td>401</td>\n",
       "      <td>1</td>\n",
       "      <td>825</td>\n",
       "      <td>1</td>\n",
       "      <td>520</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FFFE1EDDA25369CBC467536FA6A787FD</th>\n",
       "      <td>1847</td>\n",
       "      <td>22</td>\n",
       "      <td>1753</td>\n",
       "      <td>14</td>\n",
       "      <td>1422</td>\n",
       "      <td>11</td>\n",
       "      <td>1028</td>\n",
       "      <td>10</td>\n",
       "      <td>765</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>2463</td>\n",
       "      <td>7</td>\n",
       "      <td>1416</td>\n",
       "      <td>6</td>\n",
       "      <td>871</td>\n",
       "      <td>5</td>\n",
       "      <td>990</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FFFD0A912A959CDFCFFAB2F93BFBA435</th>\n",
       "      <td>1407</td>\n",
       "      <td>18</td>\n",
       "      <td>1702</td>\n",
       "      <td>8</td>\n",
       "      <td>1613</td>\n",
       "      <td>6</td>\n",
       "      <td>1704</td>\n",
       "      <td>5</td>\n",
       "      <td>1394</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>958</td>\n",
       "      <td>2</td>\n",
       "      <td>726</td>\n",
       "      <td>1</td>\n",
       "      <td>848</td>\n",
       "      <td>1</td>\n",
       "      <td>491</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FFFCB235D6D917DDF350F5B11CCB0EE8</th>\n",
       "      <td>17</td>\n",
       "      <td>74</td>\n",
       "      <td>174</td>\n",
       "      <td>33</td>\n",
       "      <td>1123</td>\n",
       "      <td>4</td>\n",
       "      <td>1178</td>\n",
       "      <td>4</td>\n",
       "      <td>173</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1039</td>\n",
       "      <td>1</td>\n",
       "      <td>1228</td>\n",
       "      <td>1</td>\n",
       "      <td>1327</td>\n",
       "      <td>1</td>\n",
       "      <td>1613</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  ANTENNA_ID_0  COUNT_0  ANTENNA_ID_1  \\\n",
       "USER                                                                    \n",
       "FFFF78CB080636632B2D1DE7A7BFAB03          3415       21          2969   \n",
       "FFFF51FDDD55A2E24D074DE30C4798E9          1207       15          1171   \n",
       "FFFE1EDDA25369CBC467536FA6A787FD          1847       22          1753   \n",
       "FFFD0A912A959CDFCFFAB2F93BFBA435          1407       18          1702   \n",
       "FFFCB235D6D917DDF350F5B11CCB0EE8            17       74           174   \n",
       "\n",
       "                                  COUNT_1  ANTENNA_ID_2  COUNT_2  \\\n",
       "USER                                                               \n",
       "FFFF78CB080636632B2D1DE7A7BFAB03        5          1320        3   \n",
       "FFFF51FDDD55A2E24D074DE30C4798E9       14          1820        9   \n",
       "FFFE1EDDA25369CBC467536FA6A787FD       14          1422       11   \n",
       "FFFD0A912A959CDFCFFAB2F93BFBA435        8          1613        6   \n",
       "FFFCB235D6D917DDF350F5B11CCB0EE8       33          1123        4   \n",
       "\n",
       "                                  ANTENNA_ID_3  COUNT_3  ANTENNA_ID_4  \\\n",
       "USER                                                                    \n",
       "FFFF78CB080636632B2D1DE7A7BFAB03          2471        3          1115   \n",
       "FFFF51FDDD55A2E24D074DE30C4798E9           583        8          2026   \n",
       "FFFE1EDDA25369CBC467536FA6A787FD          1028       10           765   \n",
       "FFFD0A912A959CDFCFFAB2F93BFBA435          1704        5          1394   \n",
       "FFFCB235D6D917DDF350F5B11CCB0EE8          1178        4           173   \n",
       "\n",
       "                                  COUNT_4   ...     COUNT_6  ANTENNA_ID_7  \\\n",
       "USER                                        ...                             \n",
       "FFFF78CB080636632B2D1DE7A7BFAB03        2   ...       65535            -1   \n",
       "FFFF51FDDD55A2E24D074DE30C4798E9        5   ...           4          1625   \n",
       "FFFE1EDDA25369CBC467536FA6A787FD        9   ...           8          2463   \n",
       "FFFD0A912A959CDFCFFAB2F93BFBA435        3   ...           2           958   \n",
       "FFFCB235D6D917DDF350F5B11CCB0EE8        3   ...           1          1039   \n",
       "\n",
       "                                  COUNT_7  ANTENNA_ID_8  COUNT_8  \\\n",
       "USER                                                               \n",
       "FFFF78CB080636632B2D1DE7A7BFAB03    65535            -1    65535   \n",
       "FFFF51FDDD55A2E24D074DE30C4798E9        2           401        1   \n",
       "FFFE1EDDA25369CBC467536FA6A787FD        7          1416        6   \n",
       "FFFD0A912A959CDFCFFAB2F93BFBA435        2           726        1   \n",
       "FFFCB235D6D917DDF350F5B11CCB0EE8        1          1228        1   \n",
       "\n",
       "                                  ANTENNA_ID_9  COUNT_9  MOBILITY_DIAMETER  \\\n",
       "USER                                                                         \n",
       "FFFF78CB080636632B2D1DE7A7BFAB03            -1    65535               1063   \n",
       "FFFF51FDDD55A2E24D074DE30C4798E9           825        1                520   \n",
       "FFFE1EDDA25369CBC467536FA6A787FD           871        5                990   \n",
       "FFFD0A912A959CDFCFFAB2F93BFBA435           848        1                491   \n",
       "FFFCB235D6D917DDF350F5B11CCB0EE8          1327        1               1613   \n",
       "\n",
       "                                  EPIDEMIC  EXPOSED  \n",
       "USER                                                 \n",
       "FFFF78CB080636632B2D1DE7A7BFAB03     False     True  \n",
       "FFFF51FDDD55A2E24D074DE30C4798E9     False     True  \n",
       "FFFE1EDDA25369CBC467536FA6A787FD     False     True  \n",
       "FFFD0A912A959CDFCFFAB2F93BFBA435      True    False  \n",
       "FFFCB235D6D917DDF350F5B11CCB0EE8     False     True  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>STATE</th>\n",
       "      <th>EPIDEMIC</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ANTENNA_ID_9</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2797</th>\n",
       "      <td>14.671</td>\n",
       "      <td>-92.372</td>\n",
       "      <td>Chiapas</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3060</th>\n",
       "      <td>14.682</td>\n",
       "      <td>-92.155</td>\n",
       "      <td>Chiapas</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3344</th>\n",
       "      <td>14.721</td>\n",
       "      <td>-92.424</td>\n",
       "      <td>Chiapas</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3487</th>\n",
       "      <td>14.778</td>\n",
       "      <td>-92.179</td>\n",
       "      <td>Chiapas</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1924</th>\n",
       "      <td>14.786</td>\n",
       "      <td>-92.366</td>\n",
       "      <td>Chiapas</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              LATITUDE  LONGITUDE    STATE  EPIDEMIC\n",
       "ANTENNA_ID_9                                        \n",
       "2797            14.671    -92.372  Chiapas         0\n",
       "3060            14.682    -92.155  Chiapas         0\n",
       "3344            14.721    -92.424  Chiapas         0\n",
       "3487            14.778    -92.179  Chiapas         0\n",
       "1924            14.786    -92.366  Chiapas         0"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antennas.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(True, False)\n",
      "(4221, 4429)\n"
     ]
    }
   ],
   "source": [
    "antena_ids = np.unique(antennas.index.values)\n",
    "user_antenna_ids = set(np.unique(table['ANTENNA_ID_0'].values)) | set(np.unique(table['ANTENNA_ID_1'].values)) |\\\n",
    "                    set(np.unique(table['ANTENNA_ID_2'].values)) | set(np.unique(table['ANTENNA_ID_3'].values)) |\\\n",
    "                set(np.unique(table['ANTENNA_ID_4'].values)) | set(np.unique(table['ANTENNA_ID_5'].values)) |\\\n",
    "        set(np.unique(table['ANTENNA_ID_6'].values)) | set(np.unique(table['ANTENNA_ID_7'].values))\n",
    "if -1 in user_antenna_ids:\n",
    "    user_antenna_ids.remove(-1)\n",
    "user_antenna_ids  = np.asarray(list(user_antenna_ids))\n",
    "\n",
    "print(np.in1d(user_antenna_ids,antena_ids).all(), np.in1d(antena_ids,user_antenna_ids).all() )\n",
    "\n",
    "print(np.in1d(antena_ids,user_antenna_ids).sum(), len(antena_ids))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "antenna_cols = [col for col in table.columns if \"ANTENNA_ID\" in col]\n",
    "\n",
    "for col in antenna_cols:\n",
    "    antennas.index.name = col\n",
    "    table = table.join(antennas['EPIDEMIC'].astype(int), on=col,rsuffix='_{0}'.format(col.split(\"_\")[-1]))\n",
    "#solo falta renombrar la columna que no tiene rsuffix\n",
    "table = table.rename(columns={'EPIDEMIC':'EPIDEMIC_0'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "epi_cols = [col for col in table.columns if 'EPIDEMIC_' in col]\n",
    "table['EXPOSED'] = (table[epi_cols].sum(axis=1)>=1).astype(int)\n",
    "table['EPIDEMIC'] =  table['EPIDEMIC_0'].values\n",
    "for col in epi_cols:\n",
    "    table.drop(col,axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ANTENNA_ID_0</th>\n",
       "      <th>COUNT_0</th>\n",
       "      <th>ANTENNA_ID_1</th>\n",
       "      <th>COUNT_1</th>\n",
       "      <th>ANTENNA_ID_2</th>\n",
       "      <th>COUNT_2</th>\n",
       "      <th>ANTENNA_ID_3</th>\n",
       "      <th>COUNT_3</th>\n",
       "      <th>ANTENNA_ID_4</th>\n",
       "      <th>COUNT_4</th>\n",
       "      <th>...</th>\n",
       "      <th>COUNT_6</th>\n",
       "      <th>ANTENNA_ID_7</th>\n",
       "      <th>COUNT_7</th>\n",
       "      <th>ANTENNA_ID_8</th>\n",
       "      <th>COUNT_8</th>\n",
       "      <th>ANTENNA_ID_9</th>\n",
       "      <th>COUNT_9</th>\n",
       "      <th>MOBILITY_DIAMETER</th>\n",
       "      <th>EXPOSED</th>\n",
       "      <th>EPIDEMIC</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USER</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ED96A156B5712B53C57CFF8E8ABC182D</th>\n",
       "      <td>2002</td>\n",
       "      <td>98</td>\n",
       "      <td>2204</td>\n",
       "      <td>40</td>\n",
       "      <td>1962</td>\n",
       "      <td>30</td>\n",
       "      <td>2353</td>\n",
       "      <td>23</td>\n",
       "      <td>1446</td>\n",
       "      <td>21</td>\n",
       "      <td>...</td>\n",
       "      <td>17</td>\n",
       "      <td>1443</td>\n",
       "      <td>15</td>\n",
       "      <td>1444</td>\n",
       "      <td>10</td>\n",
       "      <td>514</td>\n",
       "      <td>9</td>\n",
       "      <td>846</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A53F04CCB9D4DE72D1D80A9695CA0F38</th>\n",
       "      <td>2040</td>\n",
       "      <td>57</td>\n",
       "      <td>2282</td>\n",
       "      <td>21</td>\n",
       "      <td>754</td>\n",
       "      <td>20</td>\n",
       "      <td>1279</td>\n",
       "      <td>14</td>\n",
       "      <td>1877</td>\n",
       "      <td>13</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>343</td>\n",
       "      <td>4</td>\n",
       "      <td>131</td>\n",
       "      <td>3</td>\n",
       "      <td>1328</td>\n",
       "      <td>3</td>\n",
       "      <td>1535</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4921680006841C65C5960D6E9C31667F</th>\n",
       "      <td>2555</td>\n",
       "      <td>2</td>\n",
       "      <td>676</td>\n",
       "      <td>1</td>\n",
       "      <td>1002</td>\n",
       "      <td>1</td>\n",
       "      <td>3312</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>...</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>2128</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45690AA7F26E3FEF051AD1252E716CB8</th>\n",
       "      <td>125</td>\n",
       "      <td>13</td>\n",
       "      <td>1310</td>\n",
       "      <td>8</td>\n",
       "      <td>257</td>\n",
       "      <td>4</td>\n",
       "      <td>250</td>\n",
       "      <td>3</td>\n",
       "      <td>123</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1018</td>\n",
       "      <td>1</td>\n",
       "      <td>1401</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>695</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F1CBA36A18650AB67A10181F1232CB3D</th>\n",
       "      <td>2047</td>\n",
       "      <td>75</td>\n",
       "      <td>657</td>\n",
       "      <td>22</td>\n",
       "      <td>409</td>\n",
       "      <td>19</td>\n",
       "      <td>2359</td>\n",
       "      <td>19</td>\n",
       "      <td>256</td>\n",
       "      <td>18</td>\n",
       "      <td>...</td>\n",
       "      <td>16</td>\n",
       "      <td>2208</td>\n",
       "      <td>15</td>\n",
       "      <td>954</td>\n",
       "      <td>14</td>\n",
       "      <td>321</td>\n",
       "      <td>11</td>\n",
       "      <td>1007</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FAF9615D48D09049AF12453F767E5B9E</th>\n",
       "      <td>1174</td>\n",
       "      <td>16</td>\n",
       "      <td>116</td>\n",
       "      <td>11</td>\n",
       "      <td>3053</td>\n",
       "      <td>7</td>\n",
       "      <td>2921</td>\n",
       "      <td>6</td>\n",
       "      <td>3054</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>2387</td>\n",
       "      <td>2</td>\n",
       "      <td>2716</td>\n",
       "      <td>2</td>\n",
       "      <td>3591</td>\n",
       "      <td>2</td>\n",
       "      <td>2850</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A17D41FD01A77C21E2C73C85D3D6C865</th>\n",
       "      <td>2688</td>\n",
       "      <td>188</td>\n",
       "      <td>834</td>\n",
       "      <td>20</td>\n",
       "      <td>729</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>...</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>912</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84B9E30956F6A66675D17A15189F9CCB</th>\n",
       "      <td>2075</td>\n",
       "      <td>64</td>\n",
       "      <td>568</td>\n",
       "      <td>46</td>\n",
       "      <td>1821</td>\n",
       "      <td>36</td>\n",
       "      <td>1289</td>\n",
       "      <td>29</td>\n",
       "      <td>840</td>\n",
       "      <td>19</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>713</td>\n",
       "      <td>4</td>\n",
       "      <td>1960</td>\n",
       "      <td>4</td>\n",
       "      <td>2079</td>\n",
       "      <td>4</td>\n",
       "      <td>1056</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6903CCA86378FDB0D1589C0D7FC3ACE3</th>\n",
       "      <td>566</td>\n",
       "      <td>5</td>\n",
       "      <td>276</td>\n",
       "      <td>4</td>\n",
       "      <td>945</td>\n",
       "      <td>4</td>\n",
       "      <td>1244</td>\n",
       "      <td>3</td>\n",
       "      <td>297</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1125</td>\n",
       "      <td>1</td>\n",
       "      <td>1230</td>\n",
       "      <td>1</td>\n",
       "      <td>2082</td>\n",
       "      <td>1</td>\n",
       "      <td>959</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EB5933360A48B8E9C58FB0B6E82EDA79</th>\n",
       "      <td>2622</td>\n",
       "      <td>13</td>\n",
       "      <td>520</td>\n",
       "      <td>5</td>\n",
       "      <td>1515</td>\n",
       "      <td>2</td>\n",
       "      <td>892</td>\n",
       "      <td>1</td>\n",
       "      <td>924</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>690</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3BF4DD122167DEA926F1F6583C317714</th>\n",
       "      <td>1868</td>\n",
       "      <td>33</td>\n",
       "      <td>3014</td>\n",
       "      <td>27</td>\n",
       "      <td>3037</td>\n",
       "      <td>26</td>\n",
       "      <td>2473</td>\n",
       "      <td>16</td>\n",
       "      <td>1493</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>1637</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172E4EF82AC628145968F95DD5A34769</th>\n",
       "      <td>3132</td>\n",
       "      <td>35</td>\n",
       "      <td>1943</td>\n",
       "      <td>9</td>\n",
       "      <td>2548</td>\n",
       "      <td>3</td>\n",
       "      <td>2850</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>...</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>3222</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>504FDCAB69ECA97E1B9CB0DE577367FB</th>\n",
       "      <td>3733</td>\n",
       "      <td>22</td>\n",
       "      <td>2319</td>\n",
       "      <td>13</td>\n",
       "      <td>2320</td>\n",
       "      <td>12</td>\n",
       "      <td>797</td>\n",
       "      <td>11</td>\n",
       "      <td>225</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>2510</td>\n",
       "      <td>1</td>\n",
       "      <td>2631</td>\n",
       "      <td>1</td>\n",
       "      <td>2843</td>\n",
       "      <td>1</td>\n",
       "      <td>786</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F8F8077701985AAACF655F618EECF0A0</th>\n",
       "      <td>210</td>\n",
       "      <td>256</td>\n",
       "      <td>1281</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>...</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>669</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134FA4E3841C5571EF4515B5CCFFBAA4</th>\n",
       "      <td>2612</td>\n",
       "      <td>59</td>\n",
       "      <td>3149</td>\n",
       "      <td>23</td>\n",
       "      <td>927</td>\n",
       "      <td>18</td>\n",
       "      <td>3651</td>\n",
       "      <td>11</td>\n",
       "      <td>3496</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>7</td>\n",
       "      <td>2259</td>\n",
       "      <td>5</td>\n",
       "      <td>1695</td>\n",
       "      <td>4</td>\n",
       "      <td>1266</td>\n",
       "      <td>3</td>\n",
       "      <td>2107</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BA1D6FBDD42B45516248BF3D33F4C777</th>\n",
       "      <td>230</td>\n",
       "      <td>38</td>\n",
       "      <td>1270</td>\n",
       "      <td>35</td>\n",
       "      <td>290</td>\n",
       "      <td>32</td>\n",
       "      <td>1609</td>\n",
       "      <td>14</td>\n",
       "      <td>1853</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>9</td>\n",
       "      <td>2987</td>\n",
       "      <td>8</td>\n",
       "      <td>207</td>\n",
       "      <td>7</td>\n",
       "      <td>850</td>\n",
       "      <td>7</td>\n",
       "      <td>1204</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E8AC90785DF7DDD578595B0952245117</th>\n",
       "      <td>2113</td>\n",
       "      <td>52</td>\n",
       "      <td>28</td>\n",
       "      <td>11</td>\n",
       "      <td>1562</td>\n",
       "      <td>3</td>\n",
       "      <td>1853</td>\n",
       "      <td>3</td>\n",
       "      <td>3276</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>712</td>\n",
       "      <td>1</td>\n",
       "      <td>1380</td>\n",
       "      <td>1</td>\n",
       "      <td>2413</td>\n",
       "      <td>1</td>\n",
       "      <td>1137</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7118182ECB7EB87624DCC38C6A310B25</th>\n",
       "      <td>1373</td>\n",
       "      <td>10</td>\n",
       "      <td>1382</td>\n",
       "      <td>7</td>\n",
       "      <td>1370</td>\n",
       "      <td>4</td>\n",
       "      <td>1374</td>\n",
       "      <td>3</td>\n",
       "      <td>1371</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>1016</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D55F730F65271FE6B45B988A3CDC769D</th>\n",
       "      <td>1692</td>\n",
       "      <td>26</td>\n",
       "      <td>684</td>\n",
       "      <td>17</td>\n",
       "      <td>1628</td>\n",
       "      <td>15</td>\n",
       "      <td>2245</td>\n",
       "      <td>6</td>\n",
       "      <td>2596</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>3659</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>2314</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8397EDD7856EE48A11F2590572988481</th>\n",
       "      <td>1934</td>\n",
       "      <td>15</td>\n",
       "      <td>2090</td>\n",
       "      <td>3</td>\n",
       "      <td>2112</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>...</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>-1</td>\n",
       "      <td>65535</td>\n",
       "      <td>1457</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  ANTENNA_ID_0  COUNT_0  ANTENNA_ID_1  \\\n",
       "USER                                                                    \n",
       "ED96A156B5712B53C57CFF8E8ABC182D          2002       98          2204   \n",
       "A53F04CCB9D4DE72D1D80A9695CA0F38          2040       57          2282   \n",
       "4921680006841C65C5960D6E9C31667F          2555        2           676   \n",
       "45690AA7F26E3FEF051AD1252E716CB8           125       13          1310   \n",
       "F1CBA36A18650AB67A10181F1232CB3D          2047       75           657   \n",
       "FAF9615D48D09049AF12453F767E5B9E          1174       16           116   \n",
       "A17D41FD01A77C21E2C73C85D3D6C865          2688      188           834   \n",
       "84B9E30956F6A66675D17A15189F9CCB          2075       64           568   \n",
       "6903CCA86378FDB0D1589C0D7FC3ACE3           566        5           276   \n",
       "EB5933360A48B8E9C58FB0B6E82EDA79          2622       13           520   \n",
       "3BF4DD122167DEA926F1F6583C317714          1868       33          3014   \n",
       "172E4EF82AC628145968F95DD5A34769          3132       35          1943   \n",
       "504FDCAB69ECA97E1B9CB0DE577367FB          3733       22          2319   \n",
       "F8F8077701985AAACF655F618EECF0A0           210      256          1281   \n",
       "134FA4E3841C5571EF4515B5CCFFBAA4          2612       59          3149   \n",
       "BA1D6FBDD42B45516248BF3D33F4C777           230       38          1270   \n",
       "E8AC90785DF7DDD578595B0952245117          2113       52            28   \n",
       "7118182ECB7EB87624DCC38C6A310B25          1373       10          1382   \n",
       "D55F730F65271FE6B45B988A3CDC769D          1692       26           684   \n",
       "8397EDD7856EE48A11F2590572988481          1934       15          2090   \n",
       "\n",
       "                                  COUNT_1  ANTENNA_ID_2  COUNT_2  \\\n",
       "USER                                                               \n",
       "ED96A156B5712B53C57CFF8E8ABC182D       40          1962       30   \n",
       "A53F04CCB9D4DE72D1D80A9695CA0F38       21           754       20   \n",
       "4921680006841C65C5960D6E9C31667F        1          1002        1   \n",
       "45690AA7F26E3FEF051AD1252E716CB8        8           257        4   \n",
       "F1CBA36A18650AB67A10181F1232CB3D       22           409       19   \n",
       "FAF9615D48D09049AF12453F767E5B9E       11          3053        7   \n",
       "A17D41FD01A77C21E2C73C85D3D6C865       20           729        1   \n",
       "84B9E30956F6A66675D17A15189F9CCB       46          1821       36   \n",
       "6903CCA86378FDB0D1589C0D7FC3ACE3        4           945        4   \n",
       "EB5933360A48B8E9C58FB0B6E82EDA79        5          1515        2   \n",
       "3BF4DD122167DEA926F1F6583C317714       27          3037       26   \n",
       "172E4EF82AC628145968F95DD5A34769        9          2548        3   \n",
       "504FDCAB69ECA97E1B9CB0DE577367FB       13          2320       12   \n",
       "F8F8077701985AAACF655F618EECF0A0        1            -1    65535   \n",
       "134FA4E3841C5571EF4515B5CCFFBAA4       23           927       18   \n",
       "BA1D6FBDD42B45516248BF3D33F4C777       35           290       32   \n",
       "E8AC90785DF7DDD578595B0952245117       11          1562        3   \n",
       "7118182ECB7EB87624DCC38C6A310B25        7          1370        4   \n",
       "D55F730F65271FE6B45B988A3CDC769D       17          1628       15   \n",
       "8397EDD7856EE48A11F2590572988481        3          2112        1   \n",
       "\n",
       "                                  ANTENNA_ID_3  COUNT_3  ANTENNA_ID_4  \\\n",
       "USER                                                                    \n",
       "ED96A156B5712B53C57CFF8E8ABC182D          2353       23          1446   \n",
       "A53F04CCB9D4DE72D1D80A9695CA0F38          1279       14          1877   \n",
       "4921680006841C65C5960D6E9C31667F          3312        1            -1   \n",
       "45690AA7F26E3FEF051AD1252E716CB8           250        3           123   \n",
       "F1CBA36A18650AB67A10181F1232CB3D          2359       19           256   \n",
       "FAF9615D48D09049AF12453F767E5B9E          2921        6          3054   \n",
       "A17D41FD01A77C21E2C73C85D3D6C865            -1    65535            -1   \n",
       "84B9E30956F6A66675D17A15189F9CCB          1289       29           840   \n",
       "6903CCA86378FDB0D1589C0D7FC3ACE3          1244        3           297   \n",
       "EB5933360A48B8E9C58FB0B6E82EDA79           892        1           924   \n",
       "3BF4DD122167DEA926F1F6583C317714          2473       16          1493   \n",
       "172E4EF82AC628145968F95DD5A34769          2850        2            -1   \n",
       "504FDCAB69ECA97E1B9CB0DE577367FB           797       11           225   \n",
       "F8F8077701985AAACF655F618EECF0A0            -1    65535            -1   \n",
       "134FA4E3841C5571EF4515B5CCFFBAA4          3651       11          3496   \n",
       "BA1D6FBDD42B45516248BF3D33F4C777          1609       14          1853   \n",
       "E8AC90785DF7DDD578595B0952245117          1853        3          3276   \n",
       "7118182ECB7EB87624DCC38C6A310B25          1374        3          1371   \n",
       "D55F730F65271FE6B45B988A3CDC769D          2245        6          2596   \n",
       "8397EDD7856EE48A11F2590572988481            -1    65535            -1   \n",
       "\n",
       "                                  COUNT_4    ...     COUNT_6  ANTENNA_ID_7  \\\n",
       "USER                                         ...                             \n",
       "ED96A156B5712B53C57CFF8E8ABC182D       21    ...          17          1443   \n",
       "A53F04CCB9D4DE72D1D80A9695CA0F38       13    ...           8           343   \n",
       "4921680006841C65C5960D6E9C31667F    65535    ...       65535            -1   \n",
       "45690AA7F26E3FEF051AD1252E716CB8        2    ...           1          1018   \n",
       "F1CBA36A18650AB67A10181F1232CB3D       18    ...          16          2208   \n",
       "FAF9615D48D09049AF12453F767E5B9E        5    ...           3          2387   \n",
       "A17D41FD01A77C21E2C73C85D3D6C865    65535    ...       65535            -1   \n",
       "84B9E30956F6A66675D17A15189F9CCB       19    ...           8           713   \n",
       "6903CCA86378FDB0D1589C0D7FC3ACE3        2    ...           1          1125   \n",
       "EB5933360A48B8E9C58FB0B6E82EDA79        1    ...       65535            -1   \n",
       "3BF4DD122167DEA926F1F6583C317714        3    ...           1            -1   \n",
       "172E4EF82AC628145968F95DD5A34769    65535    ...       65535            -1   \n",
       "504FDCAB69ECA97E1B9CB0DE577367FB        2    ...           1          2510   \n",
       "F8F8077701985AAACF655F618EECF0A0    65535    ...       65535            -1   \n",
       "134FA4E3841C5571EF4515B5CCFFBAA4        8    ...           7          2259   \n",
       "BA1D6FBDD42B45516248BF3D33F4C777       11    ...           9          2987   \n",
       "E8AC90785DF7DDD578595B0952245117        2    ...           1           712   \n",
       "7118182ECB7EB87624DCC38C6A310B25        2    ...       65535            -1   \n",
       "D55F730F65271FE6B45B988A3CDC769D        6    ...           1          3659   \n",
       "8397EDD7856EE48A11F2590572988481    65535    ...       65535            -1   \n",
       "\n",
       "                                  COUNT_7  ANTENNA_ID_8  COUNT_8  \\\n",
       "USER                                                               \n",
       "ED96A156B5712B53C57CFF8E8ABC182D       15          1444       10   \n",
       "A53F04CCB9D4DE72D1D80A9695CA0F38        4           131        3   \n",
       "4921680006841C65C5960D6E9C31667F    65535            -1    65535   \n",
       "45690AA7F26E3FEF051AD1252E716CB8        1          1401        1   \n",
       "F1CBA36A18650AB67A10181F1232CB3D       15           954       14   \n",
       "FAF9615D48D09049AF12453F767E5B9E        2          2716        2   \n",
       "A17D41FD01A77C21E2C73C85D3D6C865    65535            -1    65535   \n",
       "84B9E30956F6A66675D17A15189F9CCB        4          1960        4   \n",
       "6903CCA86378FDB0D1589C0D7FC3ACE3        1          1230        1   \n",
       "EB5933360A48B8E9C58FB0B6E82EDA79    65535            -1    65535   \n",
       "3BF4DD122167DEA926F1F6583C317714    65535            -1    65535   \n",
       "172E4EF82AC628145968F95DD5A34769    65535            -1    65535   \n",
       "504FDCAB69ECA97E1B9CB0DE577367FB        1          2631        1   \n",
       "F8F8077701985AAACF655F618EECF0A0    65535            -1    65535   \n",
       "134FA4E3841C5571EF4515B5CCFFBAA4        5          1695        4   \n",
       "BA1D6FBDD42B45516248BF3D33F4C777        8           207        7   \n",
       "E8AC90785DF7DDD578595B0952245117        1          1380        1   \n",
       "7118182ECB7EB87624DCC38C6A310B25    65535            -1    65535   \n",
       "D55F730F65271FE6B45B988A3CDC769D        1            -1    65535   \n",
       "8397EDD7856EE48A11F2590572988481    65535            -1    65535   \n",
       "\n",
       "                                  ANTENNA_ID_9  COUNT_9  MOBILITY_DIAMETER  \\\n",
       "USER                                                                         \n",
       "ED96A156B5712B53C57CFF8E8ABC182D           514        9                846   \n",
       "A53F04CCB9D4DE72D1D80A9695CA0F38          1328        3               1535   \n",
       "4921680006841C65C5960D6E9C31667F            -1    65535               2128   \n",
       "45690AA7F26E3FEF051AD1252E716CB8            -1    65535                695   \n",
       "F1CBA36A18650AB67A10181F1232CB3D           321       11               1007   \n",
       "FAF9615D48D09049AF12453F767E5B9E          3591        2               2850   \n",
       "A17D41FD01A77C21E2C73C85D3D6C865            -1    65535                912   \n",
       "84B9E30956F6A66675D17A15189F9CCB          2079        4               1056   \n",
       "6903CCA86378FDB0D1589C0D7FC3ACE3          2082        1                959   \n",
       "EB5933360A48B8E9C58FB0B6E82EDA79            -1    65535                690   \n",
       "3BF4DD122167DEA926F1F6583C317714            -1    65535               1637   \n",
       "172E4EF82AC628145968F95DD5A34769            -1    65535               3222   \n",
       "504FDCAB69ECA97E1B9CB0DE577367FB          2843        1                786   \n",
       "F8F8077701985AAACF655F618EECF0A0            -1    65535                669   \n",
       "134FA4E3841C5571EF4515B5CCFFBAA4          1266        3               2107   \n",
       "BA1D6FBDD42B45516248BF3D33F4C777           850        7               1204   \n",
       "E8AC90785DF7DDD578595B0952245117          2413        1               1137   \n",
       "7118182ECB7EB87624DCC38C6A310B25            -1    65535               1016   \n",
       "D55F730F65271FE6B45B988A3CDC769D            -1    65535               2314   \n",
       "8397EDD7856EE48A11F2590572988481            -1    65535               1457   \n",
       "\n",
       "                                  EXPOSED  EPIDEMIC  \n",
       "USER                                                 \n",
       "ED96A156B5712B53C57CFF8E8ABC182D        1         0  \n",
       "A53F04CCB9D4DE72D1D80A9695CA0F38        1         0  \n",
       "4921680006841C65C5960D6E9C31667F        1         0  \n",
       "45690AA7F26E3FEF051AD1252E716CB8        1         0  \n",
       "F1CBA36A18650AB67A10181F1232CB3D        1         0  \n",
       "FAF9615D48D09049AF12453F767E5B9E        1         1  \n",
       "A17D41FD01A77C21E2C73C85D3D6C865        0         0  \n",
       "84B9E30956F6A66675D17A15189F9CCB        1         0  \n",
       "6903CCA86378FDB0D1589C0D7FC3ACE3        1         0  \n",
       "EB5933360A48B8E9C58FB0B6E82EDA79        1         0  \n",
       "3BF4DD122167DEA926F1F6583C317714        1         0  \n",
       "172E4EF82AC628145968F95DD5A34769        0         0  \n",
       "504FDCAB69ECA97E1B9CB0DE577367FB        1         0  \n",
       "F8F8077701985AAACF655F618EECF0A0        1         1  \n",
       "134FA4E3841C5571EF4515B5CCFFBAA4        1         0  \n",
       "BA1D6FBDD42B45516248BF3D33F4C777        1         1  \n",
       "E8AC90785DF7DDD578595B0952245117        1         0  \n",
       "7118182ECB7EB87624DCC38C6A310B25        1         1  \n",
       "D55F730F65271FE6B45B988A3CDC769D        0         0  \n",
       "8397EDD7856EE48A11F2590572988481        0         0  \n",
       "\n",
       "[20 rows x 23 columns]"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.sample(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#fast consistency check\n",
    "(table['EXPOSED'] >= table['EPIDEMIC']).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test.drop_duplicates?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ANTENNA_ID_0</th>\n",
       "      <th>EPIDEMIC</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USER</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>69E679784251659B335C109AB9BDEE5C</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1C74A9C234FAEC2953DD1E727DFBEB30</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1689BA5694E0E9CA56325AE24721552A</th>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>08CC1463A441C353F8699BBDA7819BE9</th>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5298821615BB5D5CF795BC6A5268924E</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  ANTENNA_ID_0  EPIDEMIC\n",
       "USER                                                    \n",
       "69E679784251659B335C109AB9BDEE5C             1         0\n",
       "1C74A9C234FAEC2953DD1E727DFBEB30             2         0\n",
       "1689BA5694E0E9CA56325AE24721552A             6         0\n",
       "08CC1463A441C353F8699BBDA7819BE9             8         1\n",
       "5298821615BB5D5CF795BC6A5268924E             9         1"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## individual samples check\n",
    "n=1500\n",
    "test = table[['ANTENNA_ID_0','EPIDEMIC']].sample(n).sort_values(by='ANTENNA_ID_0').drop_duplicates()\n",
    "#el drop_duplicates es porque puede haber 2 usuarios con la misma antenna\n",
    "test.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#comparo que la condicion epidemica sea igual para c/uno de los users una vez que sortie por las antenna_ids\n",
    "#el sort_index es necesario pues son unicas las antennas\n",
    "(antennas[np.in1d(antennas.index.values,test['ANTENNA_ID_0'].values)]['EPIDEMIC'].sort_index().values == \n",
    " test.EPIDEMIC.values).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1min 42s, sys: 352 ms, total: 1min 42s\n",
      "Wall time: 1min 43s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "#save file back\n",
    "table.to_csv(output_file,\n",
    "             sep = \"|\",\n",
    "             index = True,\n",
    "            header = True,\n",
    "            compression='gzip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "USER,ANTENNA_ID_0,COUNT_0,ANTENNA_ID_1,COUNT_1,ANTENNA_ID_2,COUNT_2,ANTENNA_ID_3,COUNT_3,ANTENNA_ID_4,COUNT_4,ANTENNA_ID_5,COUNT_5,ANTENNA_ID_6,COUNT_6,ANTENNA_ID_7,COUNT_7,ANTENNA_ID_8,COUNT_8,ANTENNA_ID_9,COUNT_9,MOBILITY_DIAMETER,EXPOSED,EPIDEMIC\r\n",
      "FFFF78CB080636632B2D1DE7A7BFAB03,3415,21,2969,5,1320,3,2471,3,1115,2,-1,65535,-1,65535,-1,65535,-1,65535,-1,65535,1063,0,0\r\n",
      "FFFF51FDDD55A2E24D074DE30C4798E9,1207,15,1171,14,1820,9,583,8,2026,5,1066,4,1831,4,1625,2,401,1,825,1,520,1,0\r\n",
      "FFFE1EDDA25369CBC467536FA6A787FD,1847,22,1753,14,1422,11,1028,10,765,9,3072,9,862,8,2463,7,1416,6,871,5,990,1,0\r\n",
      "FFFD0A912A959CDFCFFAB2F93BFBA435,1407,18,1702,8,1613,6,1704,5,1394,3,1398,3,818,2,958,2,726,1,848,1,491,1,0\r\n",
      "FFFCB235D6D917DDF350F5B11CCB0EE8,17,74,174,33,1123,4,1178,4,173,3,890,2,503,1,1039,1,1228,1,1327,1,1613,1,0\r\n",
      "FFFC9EC4F662C44D3006BA69C9F0DE85,876,19,1737,6,1744,5,326,1,378,1,503,1,1118,1,2216,1,2477,1,2962,1,3004,1,1\r\n",
      "FFFB2F18F4CCF253C06B581EA518A7A5,2543,13,704,7,2540,7,1453,6,1642,5,45,4,2393,4,1841,3,3031,3,1009,2,2895,1,0\r\n",
      "FFFA147CB2B23AD12F207F0EFBEB14D0,1857,103,2959,19,1344,2,2309,2,1511,1,2858,1,-1,65535,-1,65535,-1,65535,-1,65535,1633,1,1\r\n",
      "FFFA0736F66FB09200CE1D13E081A946,266,63,1799,46,1790,29,2716,29,537,21,3377,21,1439,18,2715,16,1720,15,2000,14,2558,1,1\r\n",
      "\r\n",
      "gzip: stdout: Broken pipe\r\n"
     ]
    }
   ],
   "source": [
    "#chequeo a ver como esta escrito\n",
    "!zcat $output_file | head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}