Traffic.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Labels:  Order,Ticker,Type,Discreet?,YearMonthDayUTC,Address,State,Zip,Zip4,Cars,Notes,UTC,Time_Date,Time,Month,Day,Year,Spaces,Population,Male_Population,Female_Population,Population_Density,5_Year_Projected_Population_Growth,Average_ Family_Size,Family_Households,Households,Age_Below_18,Age_18_Plus,Age_65_Plus,Associate_Degree,Bachelors_Degree,High_Schl_ Grad,PPHouseholds_1,PPHouseholds_2,MT2PPHousehold_2Plus,Per_Capita_Income,Median_Disposable_Household_Income,PCTWhitePop,PCTAIANPop,PCTAPop,PCTBPop,PCTHPIPop,PCTHisPop,PCTMultiracePop,PCTOtherRacePop,PCTMinority,PCTNo_Vehicle,PCT_1_Vehicle,PCT_2_Vehicles,PCTMore_2_Vehicles,PCTCivilian_Unemp,Slot 1,Slot 2,Slot 3,Slot 4,Slot 5,Slot 6,Category,PrePost,DOW,Shifted Month,WMT-Q,Week End,Unique Location,Unique Location & Year,Duplicate?,Year Repeat?,Same Store in Two Years?,Region,Sub Region,Create Year\n",
      "\n",
      "Sample:  15.0,CMG,CMG_1467,,11APR01185905,7175 W. Lake Mead  Las Vegas,NV,89128,,53.0,04/01/2011,,2011-04-01 11:59:00,11:59,4.0,1.0,2011.0,90.0,41820.0,0.4897,0.5103,7058.82,0.06669999659061432,3.26,10831.0,16195.0,0.265,0.735,0.102,0.0846,0.1894,0.2755,0.2558,0.3268,0.4174,31258.0,55678.0,0.7339,0.0075,0.0872,0.0893,0.0076,0.1904,0.0408,0.0336,0.2661,0.0464,0.4017,0.4211,0.1308,0.0979,0.0,0.0,0.0,4.0,0.0,0.0,4.0,Post,6.0,4.0,0.0,0.0,CMG_1467_11APR01,CMG7175 W. LAKE MEAD  LAS VEGASNV42011,0.0,0.0,0.0,West,Pacific,2012\n",
      "\n",
      "Columns:  296703\n"
     ]
    }
   ],
   "source": [
    "# Load and view data\n",
    "\n",
    "fname = \"data.csv\"\n",
    "with open(fname, 'r') as fh:\n",
    "    lines = fh.readlines()\n",
    "    \n",
    "print(\"Labels: \", lines[0])\n",
    "print(\"Sample: \", lines[1])\n",
    "print(\"Columns: \", len(lines))\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(fname)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       YearMonthDayUTC                                   Address State  \\\n",
      "0        11APR01185905              7175 W. Lake Mead  Las Vegas    NV   \n",
      "1        11APR01172545      1715 North Town East Blvd.  Mesquite    TX   \n",
      "2        11APR01190010         18951 Brookhurst  Fountain Valley    CA   \n",
      "3        11APR01172543                       4901 W. Park  Plano    TX   \n",
      "4        11APR01172545        7700 N. Central Expressway  Dallas    TX   \n",
      "5        11APR01190024        12359 Seal Beach Blvd.  Seal Beach    CA   \n",
      "6        11APR01174959                16680 N. 83rd Ave.  Peoria    AZ   \n",
      "7        11APR01172543         1009 N. Central Expressway  Plano    TX   \n",
      "8        11APR01190024       16241 Beach Blvd.  Huntington Beach    CA   \n",
      "9        11APR01190023             5310 Lakewood Blvd.  Lakewood    CA   \n",
      "10       11APR01175000         5880 W. Thunderbird Rd.  Glendale    AZ   \n",
      "11       11APR01193126  990 Serramonte Boulevard  Suite D  Colma    CA   \n",
      "12       11APR01190010             2300 Harbor Blvd.  Costa Mesa    CA   \n",
      "13       11APR01175001              3039 W. Peoria Ave.  Phoenix    AZ   \n",
      "14       11APR01190034              1800 Ximeno Ave.  Long Beach    CA   \n",
      "15       11APR01172543       103 Central Expressway North  Allen    TX   \n",
      "16       11APR01172544    11613 North Central Expressway  Dallas    TX   \n",
      "17       11APR01185906            9240 W. Sahara Ave.  Las Vegas    NV   \n",
      "18       11APR01172545                2705 McKinney Ave.  Dallas    TX   \n",
      "19       11APR01190009                13348 Newport Ave.  Tustin    CA   \n",
      "20       11APR01190022              8500 Firestone Blvd.  Downey    CA   \n",
      "21       11APR01190008        501 North State College  Fullerton    CA   \n",
      "22       11APR01185906      7340 Arroyo Crossing Pkwy  Las Vegas    NV   \n",
      "23       11APR01190008                 477 S Associated Rd  Brea    CA   \n",
      "24       11APR01172545                  4330 Lemmon Ave.  Dallas    TX   \n",
      "25       11APR01172545                4502 Mckinney Ave.  Dallas    TX   \n",
      "26       11APR01172544           293 W. Campbell Rd.  Richardson    TX   \n",
      "27       11APR01172542                  3401 Preston Rd.  Frisco    TX   \n",
      "28       11APR01190010             3705 S Bristol St.  Santa Ana    CA   \n",
      "29       11APR02171308            1255 S. Kirkwood Rd.  Kirkwood    MO   \n",
      "...                ...                                       ...   ...   \n",
      "296672   15SEP24162939       2020 BASHFORD MANOR LANE LOUISVILLE    KY   \n",
      "296673   14SEP13162353              3250 VINELAND ROAD KISSIMMEE    FL   \n",
      "296674   15SEP29181956           1710 SOUTH GREENFIELD ROAD MESA    AZ   \n",
      "296675   14SEP18175623            3500 COORS BLVD SW ALBUQUERQUE    NM   \n",
      "296676   15OCT01180433            3500 COORS BLVD SW ALBUQUERQUE    NM   \n",
      "296677   14SEP04155048                  315 FOXON BLVD NEW HAVEN    CT   \n",
      "296678   14SEP17155041                  315 FOXON BLVD NEW HAVEN    CT   \n",
      "296679   15SEP05171443               2600 NW ROCHESTER RD TOPEKA    KS   \n",
      "296680   14SEP25165712           4650 S EMERSON AVE INDIANAPOLIS    IN   \n",
      "296681   14SEP15160840            1040 MALABAR ROAD S E PALM BAY    FL   \n",
      "296682   15SEP11155828               1881 BALTIMORE PIKE HANOVER    PA   \n",
      "296683   14SEP20161847          9820 CALLABRIDGE COURT CHARLOTTE    NC   \n",
      "296684   14AUG31180101          2266 WYOMING BLVD NE ALBUQUERQUE    NM   \n",
      "296685   14SEP13180120          2266 WYOMING BLVD NE ALBUQUERQUE    NM   \n",
      "296686   14SEP14175415          2266 WYOMING BLVD NE ALBUQUERQUE    NM   \n",
      "296687   14SEP18175649          2266 WYOMING BLVD NE ALBUQUERQUE    NM   \n",
      "296688   14SEP09182648         500 SOUTH UTAH AVENUE IDAHO FALLS    ID   \n",
      "296689   14SEP30164002                 201 CHAMBER DRIVE MILFORD    OH   \n",
      "296690   15SEP01165557            8599 HIGHPOINTE DRIVE NEWBURGH    IN   \n",
      "296691   15SEP03164131            8599 HIGHPOINTE DRIVE NEWBURGH    IN   \n",
      "296692   15SEP07165919            8599 HIGHPOINTE DRIVE NEWBURGH    IN   \n",
      "296693   15SEP08165208            8599 HIGHPOINTE DRIVE NEWBURGH    IN   \n",
      "296694   15SEP21165206            8599 HIGHPOINTE DRIVE NEWBURGH    IN   \n",
      "296695   15SEP22164503            8599 HIGHPOINTE DRIVE NEWBURGH    IN   \n",
      "296696   14SEP16160017                     5141 NC HWY 42 GARNER    NC   \n",
      "296697   14OCT01181353               800 EAST SOUTHERN AVE TEMPE    AZ   \n",
      "296698   14SEP04182025               800 EAST SOUTHERN AVE TEMPE    AZ   \n",
      "296699   14SEP05181350               800 EAST SOUTHERN AVE TEMPE    AZ   \n",
      "296700   14SEP30182009               800 EAST SOUTHERN AVE TEMPE    AZ   \n",
      "296701   15SEP29182012               800 EAST SOUTHERN AVE TEMPE    AZ   \n",
      "\n",
      "            Zip   Cars  Male_Population  Population_Density  \\\n",
      "0       89128.0   53.0           0.4897             7058.82   \n",
      "1       75150.0   48.0           0.4931             4711.54   \n",
      "2       92708.0   28.0           0.4946             6061.14   \n",
      "3       75093.0   53.0           0.4936             4117.08   \n",
      "4       75206.0   60.0           0.5578             9170.52   \n",
      "5       90740.0   12.0           0.4439             1953.88   \n",
      "6       85382.0   13.0           0.4778             4272.73   \n",
      "7       75075.0   31.0           0.5002             3807.69   \n",
      "8       92647.0   37.0           0.5103             7429.89   \n",
      "9       90712.0   43.0           0.4802             7052.97   \n",
      "10      85306.0    8.0           0.4965             4794.69   \n",
      "11      94014.0   15.0           0.4940             7980.97   \n",
      "12      92627.0   21.0           0.5233             9401.85   \n",
      "13      85029.0   27.0           0.5036             4974.81   \n",
      "14      90815.0   27.0           0.4804             5239.90   \n",
      "15      75013.0   30.0           0.5022             2119.33   \n",
      "16      75243.0   36.0           0.4928             6286.70   \n",
      "17      89117.0   12.0           0.4921             6746.58   \n",
      "18      75204.0   15.0           0.5576            10580.77   \n",
      "19      92780.0   15.0           0.4943             8188.48   \n",
      "20      90241.0   16.0           0.4789             8436.23   \n",
      "21      92831.0   17.0           0.5043             5492.89   \n",
      "22      89118.0   18.0           0.5118             2132.17   \n",
      "23      92821.0   20.0           0.4917             3302.55   \n",
      "24      75219.0   21.0           0.5901            10411.48   \n",
      "25      75205.0   22.0           0.4783             5414.18   \n",
      "26      75080.0    7.0           0.4981             4242.16   \n",
      "27      75034.0   11.0           0.5049             1190.27   \n",
      "28      92707.0    7.0           0.5241            11438.25   \n",
      "29      63122.0   10.0           0.4585             2606.97   \n",
      "...         ...    ...              ...                 ...   \n",
      "296672  40218.0  223.0           0.4695             3412.00   \n",
      "296673  34746.0  346.0           0.4949              502.52   \n",
      "296674  85206.0  235.0           0.4726             4349.00   \n",
      "296675  87105.0  275.0           0.5034              931.04   \n",
      "296676  87105.0  294.0           0.5034              931.04   \n",
      "296677   6513.0  219.0           0.4706             4721.46   \n",
      "296678   6513.0  152.0           0.4706             4721.46   \n",
      "296679  66617.0  298.0           0.4866              185.40   \n",
      "296680  46203.0  232.0           0.4996             2780.80   \n",
      "296681  32907.0  312.0           0.4916             1680.51   \n",
      "296682  17331.0  188.0           0.4940              655.20   \n",
      "296683  28216.0  277.0           0.4722             1533.79   \n",
      "296684  87112.0  276.0           0.4898             5424.38   \n",
      "296685  87112.0  383.0           0.4898             5424.38   \n",
      "296686  87112.0  296.0           0.4898             5424.38   \n",
      "296687  87112.0  271.0           0.4898             5424.38   \n",
      "296688  83402.0  366.0           0.5185               77.11   \n",
      "296689  45150.0  184.0           0.4885             1006.75   \n",
      "296690  47630.0  215.0           0.4926              683.70   \n",
      "296691  47630.0  230.0           0.4926              683.70   \n",
      "296692  47630.0  285.0           0.4926              683.70   \n",
      "296693  47630.0  193.0           0.4926              683.70   \n",
      "296694  47630.0  252.0           0.4926              683.70   \n",
      "296695  47630.0  197.0           0.4926              683.70   \n",
      "296696  27529.0  240.0           0.4878              710.62   \n",
      "296697  85282.0  129.0           0.5082             4564.61   \n",
      "296698  85282.0  110.0           0.5082             4564.61   \n",
      "296699  85282.0  126.0           0.5082             4564.61   \n",
      "296700  85282.0  132.0           0.5082             4564.61   \n",
      "296701  85282.0  160.0           0.5082             4564.61   \n",
      "\n",
      "        Average_ Family_Size  Per_Capita_Income  \\\n",
      "0                       3.26            31258.0   \n",
      "1                       3.45            22182.0   \n",
      "2                       3.54            31672.0   \n",
      "3                       3.43            65499.0   \n",
      "4                       3.28            29384.0   \n",
      "5                       2.80            43880.0   \n",
      "6                       3.37            26860.0   \n",
      "7                       3.23            35679.0   \n",
      "8                       3.53            27264.0   \n",
      "9                       3.44            28110.0   \n",
      "10                      3.75            22548.0   \n",
      "11                      4.23            22414.0   \n",
      "12                      3.65            26671.0   \n",
      "13                      3.73            19855.0   \n",
      "14                      3.11            37627.0   \n",
      "15                      3.47            43602.0   \n",
      "16                      3.19            25760.0   \n",
      "17                      3.18            36854.0   \n",
      "18                      3.55            32686.0   \n",
      "19                      3.68            23043.0   \n",
      "20                      3.59            23873.0   \n",
      "21                      3.33            29348.0   \n",
      "22                      3.28            26280.0   \n",
      "23                      3.39            31334.0   \n",
      "24                      3.57            40120.0   \n",
      "25                      3.34            72790.0   \n",
      "26                      3.25            29467.0   \n",
      "27                      3.71            32166.0   \n",
      "28                      5.16            14786.0   \n",
      "29                      3.04            42758.0   \n",
      "...                      ...                ...   \n",
      "296672                  3.28            18805.0   \n",
      "296673                  3.05            24558.0   \n",
      "296674                  3.39            24335.0   \n",
      "296675                  3.74            15620.0   \n",
      "296676                  3.74            15620.0   \n",
      "296677                  3.37            19197.0   \n",
      "296678                  3.37            19197.0   \n",
      "296679                  3.08            28939.0   \n",
      "296680                  3.40            17126.0   \n",
      "296681                  3.50            20914.0   \n",
      "296682                  2.98            26640.0   \n",
      "296683                  3.18            23159.0   \n",
      "296684                  3.18            25052.0   \n",
      "296685                  3.18            25052.0   \n",
      "296686                  3.18            25052.0   \n",
      "296687                  3.18            25052.0   \n",
      "296688                  3.09            23473.0   \n",
      "296689                  3.10            26492.0   \n",
      "296690                  2.85            33518.0   \n",
      "296691                  2.85            33518.0   \n",
      "296692                  2.85            33518.0   \n",
      "296693                  2.85            33518.0   \n",
      "296694                  2.85            33518.0   \n",
      "296695                  2.85            33518.0   \n",
      "296696                  3.24            23847.0   \n",
      "296697                  3.46            24293.0   \n",
      "296698                  3.46            24293.0   \n",
      "296699                  3.46            24293.0   \n",
      "296700                  3.46            24293.0   \n",
      "296701                  3.46            24293.0   \n",
      "\n",
      "        Median_Disposable_Household_Income  PCTNo_Vehicle  PCT_1_Vehicle  \\\n",
      "0                                  55678.0         0.0464         0.4017   \n",
      "1                                  48945.0         0.0385         0.3683   \n",
      "2                                  69747.0         0.0325         0.2115   \n",
      "3                                 110395.0         0.0211         0.2466   \n",
      "4                                  43769.0         0.0788         0.5449   \n",
      "5                                  44064.0         0.0808         0.4090   \n",
      "6                                  56810.0         0.0490         0.3545   \n",
      "7                                  71735.0         0.0364         0.2911   \n",
      "8                                  58503.0         0.0396         0.2929   \n",
      "9                                  58961.0         0.0575         0.2725   \n",
      "10                                 52023.0         0.0694         0.3216   \n",
      "11                                 60547.0         0.0698         0.3078   \n",
      "12                                 48386.0         0.0568         0.3618   \n",
      "13                                 42725.0         0.0795         0.3927   \n",
      "14                                 61539.0         0.0442         0.3016   \n",
      "15                                 99629.0         0.0185         0.1350   \n",
      "16                                 40523.0         0.0703         0.5264   \n",
      "17                                 54358.0         0.0568         0.4238   \n",
      "18                                 43355.0         0.1423         0.5606   \n",
      "19                                 49139.0         0.0465         0.3553   \n",
      "20                                 44686.0         0.0764         0.3610   \n",
      "21                                 47775.0         0.0563         0.3711   \n",
      "22                                 44001.0         0.0872         0.4490   \n",
      "23                                 59426.0         0.0453         0.2683   \n",
      "24                                 42493.0         0.1195         0.5701   \n",
      "25                                 74766.0         0.0363         0.4178   \n",
      "26                                 57411.0         0.0378         0.3061   \n",
      "27                                 69304.0         0.0131         0.1868   \n",
      "28                                 50234.0         0.0502         0.2712   \n",
      "29                                 60585.0         0.0426         0.3516   \n",
      "...                                    ...            ...            ...   \n",
      "296672                             37382.0         0.1037         0.4643   \n",
      "296673                             43103.0         0.0543         0.3515   \n",
      "296674                             47341.0         0.0533         0.4258   \n",
      "296675                             34409.0         0.0542         0.3049   \n",
      "296676                             34409.0         0.0542         0.3049   \n",
      "296677                             34500.0         0.2132         0.3988   \n",
      "296678                             34500.0         0.2132         0.3988   \n",
      "296679                             59764.0         0.0204         0.1323   \n",
      "296680                             35504.0         0.1087         0.3815   \n",
      "296681                             44024.0         0.0208         0.3236   \n",
      "296682                             46756.0         0.0491         0.2722   \n",
      "296683                             46993.0         0.0860         0.3236   \n",
      "296684                             43210.0         0.0430         0.3598   \n",
      "296685                             43210.0         0.0430         0.3598   \n",
      "296686                             43210.0         0.0430         0.3598   \n",
      "296687                             43210.0         0.0430         0.3598   \n",
      "296688                             41747.0         0.0417         0.2816   \n",
      "296689                             53249.0         0.0508         0.2656   \n",
      "296690                             61490.0         0.0158         0.1959   \n",
      "296691                             61490.0         0.0158         0.1959   \n",
      "296692                             61490.0         0.0158         0.1959   \n",
      "296693                             61490.0         0.0158         0.1959   \n",
      "296694                             61490.0         0.0158         0.1959   \n",
      "296695                             61490.0         0.0158         0.1959   \n",
      "296696                             53102.0         0.0308         0.2810   \n",
      "296697                             46831.0         0.0729         0.3994   \n",
      "296698                             46831.0         0.0729         0.3994   \n",
      "296699                             46831.0         0.0729         0.3994   \n",
      "296700                             46831.0         0.0729         0.3994   \n",
      "296701                             46831.0         0.0729         0.3994   \n",
      "\n",
      "        PCT_2_Vehicles  PCTMore_2_Vehicles     Region          Sub Region  \n",
      "0               0.4211              0.1308       West             Pacific  \n",
      "1               0.4053              0.1879      South               Other  \n",
      "2               0.4190              0.3370       West             Pacific  \n",
      "3               0.4789              0.2534      South               Other  \n",
      "4               0.3010              0.0753      South               Other  \n",
      "5               0.3447              0.1655       West             Pacific  \n",
      "6               0.4553              0.1412       West            Mountain  \n",
      "7               0.4570              0.2155      South               Other  \n",
      "8               0.4194              0.2481       West             Pacific  \n",
      "9               0.4004              0.2696       West             Pacific  \n",
      "10              0.3794              0.2296       West            Mountain  \n",
      "11              0.3534              0.2690       West             Pacific  \n",
      "12              0.4062              0.1752       West             Pacific  \n",
      "13              0.3629              0.1649       West            Mountain  \n",
      "14              0.4274              0.2268       West             Pacific  \n",
      "15              0.6002              0.2463      South               Other  \n",
      "16              0.3134              0.0899      South               Other  \n",
      "17              0.3762              0.1432       West             Pacific  \n",
      "18              0.2424              0.0547      South               Other  \n",
      "19              0.4069              0.1913       West             Pacific  \n",
      "20              0.3547              0.2079       West             Pacific  \n",
      "21              0.3826              0.1900       West             Pacific  \n",
      "22              0.3366              0.1272       West             Pacific  \n",
      "23              0.4270              0.2594       West             Pacific  \n",
      "24              0.2528              0.0576      South               Other  \n",
      "25              0.3738              0.1721      South               Other  \n",
      "26              0.4887              0.1674      South               Other  \n",
      "27              0.5728              0.2273      South               Other  \n",
      "28              0.3452              0.3334       West             Pacific  \n",
      "29              0.4322              0.1736      South               Other  \n",
      "...                ...                 ...        ...                 ...  \n",
      "296672          0.3163              0.1157      South  East South Central  \n",
      "296673          0.4005              0.1937      South      South Atlantic  \n",
      "296674          0.3796              0.1413       West            Mountain  \n",
      "296675          0.3694              0.2715       West            Mountain  \n",
      "296676          0.3694              0.2715       West            Mountain  \n",
      "296677          0.2670              0.1210  Northeast         New England  \n",
      "296678          0.2670              0.1210  Northeast         New England  \n",
      "296679          0.4059              0.4414    Midwest  West North Central  \n",
      "296680          0.3393              0.1705    Midwest  East North Central  \n",
      "296681          0.4547              0.2009      South      South Atlantic  \n",
      "296682          0.4359              0.2428  Northeast        Mid Atlantic  \n",
      "296683          0.3808              0.2096      South      South Atlantic  \n",
      "296684          0.3962              0.2010       West            Mountain  \n",
      "296685          0.3962              0.2010       West            Mountain  \n",
      "296686          0.3962              0.2010       West            Mountain  \n",
      "296687          0.3962              0.2010       West            Mountain  \n",
      "296688          0.4039              0.2728       West            Mountain  \n",
      "296689          0.4224              0.2612    Midwest  East North Central  \n",
      "296690          0.5050              0.2833    Midwest  East North Central  \n",
      "296691          0.5050              0.2833    Midwest  East North Central  \n",
      "296692          0.5050              0.2833    Midwest  East North Central  \n",
      "296693          0.5050              0.2833    Midwest  East North Central  \n",
      "296694          0.5050              0.2833    Midwest  East North Central  \n",
      "296695          0.5050              0.2833    Midwest  East North Central  \n",
      "296696          0.4234              0.2648      South      South Atlantic  \n",
      "296697          0.3630              0.1647       West            Mountain  \n",
      "296698          0.3630              0.1647       West            Mountain  \n",
      "296699          0.3630              0.1647       West            Mountain  \n",
      "296700          0.3630              0.1647       West            Mountain  \n",
      "296701          0.3630              0.1647       West            Mountain  \n",
      "\n",
      "[296702 rows x 16 columns]\n"
     ]
    }
   ],
   "source": [
    "# Select relevant columns\n",
    "\n",
    "iwant = [\"Region\", \"Sub Region\", \"Address\", \"State\", \"Zip\", \"Cars\", \"YearMonthDayUTC\", \"PCTNo_Vehicle\", \"PCT_1_Vehicle\",\"PCT_2_Vehicles\",\"PCTMore_2_Vehicles\", 'Median_Disposable_Household_Income', 'Per_Capita_Income', 'Average_ Family_Size', 'Population_Density', 'Male_Population']\n",
    "\n",
    "for item in df.keys(): \n",
    "    if not (item in iwant):\n",
    "        del df[item]\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(296702, 16) => 330 x (900, 16)\n"
     ]
    }
   ],
   "source": [
    "# Split data into chunks\n",
    "\n",
    "import numpy as np\n",
    "\n",
    "s = 900 # number of records per chunk\n",
    "t = int(296703 / s) +1\n",
    "\n",
    "chunks = df.groupby(np.arange(len(df)) // s)\n",
    "print(df.shape, \"=>\", len(chunks), \"x\", \"({}, {})\".format(s, 16))\n",
    "\n",
    "indices = list(map(lambda x: x*s, range(0,t)))\n",
    "parcels = list(zip(indices, chunks)) # list of (start_index, df_chunk) tuples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "starting servers: 320-330\n",
      "server #i-078e4f82f516f3af8 not up yet...\n",
      "('i-078e4f82f516f3af8', {'Name': 'running', 'Code': 16}, 'ec2-54-152-249-156.compute-1.amazonaws.com', '54.152.249.156')\n",
      "server #i-0b3e456ace12f0fad not up yet...\n",
      "('i-0b3e456ace12f0fad', {'Name': 'running', 'Code': 16}, 'ec2-54-89-153-7.compute-1.amazonaws.com', '54.89.153.7')\n",
      "server #i-040d8cc149d4fcc04 not up yet...\n",
      "('i-040d8cc149d4fcc04', {'Name': 'running', 'Code': 16}, 'ec2-52-206-248-74.compute-1.amazonaws.com', '52.206.248.74')\n",
      "server #i-0603c555040480e48 not up yet...\n",
      "('i-0603c555040480e48', {'Name': 'running', 'Code': 16}, 'ec2-54-88-13-5.compute-1.amazonaws.com', '54.88.13.5')\n",
      "server #i-074a9a4c00642f5fd not up yet...\n",
      "('i-074a9a4c00642f5fd', {'Name': 'running', 'Code': 16}, 'ec2-52-91-47-53.compute-1.amazonaws.com', '52.91.47.53')\n",
      "server #i-0bf2750fd5b65bda5 not up yet...\n",
      "('i-0bf2750fd5b65bda5', {'Name': 'running', 'Code': 16}, 'ec2-52-90-122-188.compute-1.amazonaws.com', '52.90.122.188')\n",
      "server #i-016a4a2d64e02918d not up yet...\n",
      "('i-016a4a2d64e02918d', {'Name': 'running', 'Code': 16}, 'ec2-52-205-22-234.compute-1.amazonaws.com', '52.205.22.234')\n",
      "server #i-0a70ebb97fae58e61 not up yet...\n",
      "('i-0a70ebb97fae58e61', {'Name': 'running', 'Code': 16}, 'ec2-54-144-106-203.compute-1.amazonaws.com', '54.144.106.203')\n",
      "server #i-04f70b82c137cd73f not up yet...\n",
      "('i-04f70b82c137cd73f', {'Name': 'running', 'Code': 16}, 'ec2-52-91-238-176.compute-1.amazonaws.com', '52.91.238.176')\n",
      "server #i-0bcc020eb858c8928 not up yet...\n",
      "('i-0bcc020eb858c8928', {'Name': 'running', 'Code': 16}, 'ec2-54-91-29-115.compute-1.amazonaws.com', '54.91.29.115')\n",
      "all batch servers up\n",
      "servers: ['54.152.249.156', '54.89.153.7', '52.206.248.74', '54.88.13.5', '52.91.47.53', '52.90.122.188', '52.205.22.234', '54.144.106.203', '52.91.238.176', '54.91.29.115']\n",
      "waiting for initialization...\n",
      "waiting...\n",
      "uploads complete\n",
      "starting task...\n",
      "task started\n",
      "starting task...\n",
      "task started\n",
      "starting task...\n",
      "task started\n",
      "starting task...\n",
      "task started\n",
      "starting task...\n",
      "task started\n",
      "starting task...\n",
      "task started\n",
      "starting task...\n",
      "task started\n",
      "starting task...\n",
      "task started\n",
      "starting task...\n",
      "task started\n",
      "starting task...\n",
      "task started\n",
      "batch initialization complete!\n",
      "collecting from: 54.152.249.156\n",
      "waiting... 0\n",
      "waiting... 1\n",
      "server #321 complete\n",
      "collecting from: 54.89.153.7\n",
      "server #322 complete\n",
      "collecting from: 52.206.248.74\n",
      "server #323 complete\n",
      "collecting from: 54.88.13.5\n",
      "server #324 complete\n",
      "collecting from: 52.91.47.53\n",
      "server #325 complete\n",
      "collecting from: 52.90.122.188\n",
      "server #326 complete\n",
      "collecting from: 52.205.22.234\n",
      "server #327 complete\n",
      "collecting from: 54.144.106.203\n",
      "server #328 complete\n",
      "collecting from: 52.91.238.176\n",
      "server #329 complete\n",
      "collecting from: 54.91.29.115\n",
      "server #330 complete\n",
      "terminating batch in 15...\n",
      "wait for termination to complete...\n",
      "batch complete!\n"
     ]
    }
   ],
   "source": [
    "import boto3\n",
    "import os\n",
    "import requests\n",
    "from time import sleep\n",
    "\n",
    "### Config ###\n",
    "total_servers = t\n",
    "servers_per_batch = 16 # aws permits max 20 servers running at a time\n",
    "\n",
    "\n",
    "# Create tarball for server from tuple\n",
    "#\n",
    "# Result:\n",
    "#     <#>/\n",
    "#       - src.tar.gz\n",
    "#       - src/\n",
    "#           - runscript.sh\n",
    "#           - scraper.py\n",
    "#           - data.csv\n",
    "#\n",
    "\n",
    "def create_tarball(tup):\n",
    "    # 1. Create runscript\n",
    "    ind, dfc = tup\n",
    "    tmpdir = str(ind)+\"/src/\"\n",
    "    cmd = \"mkdir -p \"+tmpdir\n",
    "    os.system(cmd)\n",
    "    \n",
    "    # 1. Copy runscript\n",
    "    cmd = \"cp runscript.sh \"+tmpdir\n",
    "    os.system(cmd)\n",
    "    \n",
    "    # 2. Create datafile \n",
    "    datafile = tmpdir+\"data.csv\"\n",
    "    dfc.to_csv(path_or_buf=datafile)\n",
    "    \n",
    "    # 3. Copy scraper program\n",
    "    cmd = \"cp scraper.py \"+tmpdir\n",
    "    os.system(cmd)\n",
    "    \n",
    "    # 4. Compress runscript.sh, data.csv, scraper.py\n",
    "    cmd = \"cd \"+str(ind)+\" && tar czf src.tar.gz src/\"\n",
    "    os.system(cmd)\n",
    "\n",
    "\n",
    "### Batches ###\n",
    "\n",
    "# initialize aws client\n",
    "ec2 = boto3.resource('ec2')\n",
    "\n",
    "start_inds = range(0, total_servers, servers_per_batch)\n",
    "\n",
    "# create servers in batches\n",
    "for ind in start_inds:\n",
    "   \n",
    "    num_servers = servers_per_batch\n",
    "\n",
    "    # compress tarball\n",
    "    for i in range(ind, ind+servers_per_batch):\n",
    "        if i < t:\n",
    "            create_tarball(parcels[i][1])\n",
    "        else:\n",
    "            num_servers = num_servers - 1\n",
    "\n",
    "    \n",
    "    # start servers (1-12, 13-24, etc.)\n",
    "    print(\"starting servers: {}-{}\".format(ind, ind+num_servers))\n",
    "    batch = ec2.create_instances(\n",
    "        ImageId=\"ami-9be6f38c\",\n",
    "        InstanceInitiatedShutdownBehavior=\"terminate\",\n",
    "        InstanceType=\"t2.nano\",\n",
    "        KeyName=\"cluster\", \n",
    "        MinCount=num_servers,\n",
    "        MaxCount=num_servers)\n",
    "\n",
    "    # wait for all servers to start and collect ip addresses\n",
    "    names = []\n",
    "    for instance in batch:\n",
    "        print(\"server #{} not up yet...\".format(instance.id))\n",
    "        instance.wait_until_running()\n",
    "        instance.reload()\n",
    "        print((instance.id, instance.state, instance.public_dns_name, instance.public_ip_address))\n",
    "        names.append(instance.public_ip_address)\n",
    "    print(\"all batch servers up\")    \n",
    "    print(\"servers: {}\".format(names))\n",
    "\n",
    "    \n",
    "    print(\"waiting for initialization...\")\n",
    "    # send processing tasks via scp   \n",
    "    index = 0\n",
    "    for instance in batch:\n",
    "        try:\n",
    "            res = os.system(\"scp -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -i cluster.pem {}/src.tar.gz ec2-user@{}:src.tar.gz\".format(ind+index, instance.public_ip_address))\n",
    "            while res: # not equal to success case 0\n",
    "                print(\"waiting...\")\n",
    "                sleep(60) # have to wait here for first in batch to initialize\n",
    "                res = os.system(\"scp -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -i cluster.pem {}/src.tar.gz ec2-user@{}:src.tar.gz\".format(ind+index, instance.public_ip_address))          \n",
    "            \n",
    "        except Exception as e:\n",
    "            print(\"could not upload data to {}: {}\".format(instance.id,e))\n",
    "        \n",
    "        index = index + 1\n",
    "    \n",
    "    print(\"uploads complete\")\n",
    "\n",
    "    for instance in batch: \n",
    "        # ssh in and start task\n",
    "        print(\"starting task...\")\n",
    "        host = instance.public_ip_address\n",
    "        cmd = 'echo \"{}\" | ./remoteCmd.sh'.format(host)\n",
    "        os.system(cmd)\n",
    "        print(\"task started\")\n",
    "              \n",
    "    print(\"batch initialization complete!\")\n",
    "    \n",
    "    \n",
    "    # collect completed tasks and shut down\n",
    "    index = 0\n",
    "    for instance in batch:\n",
    "        print(\"collecting from: {}\".format(instance.public_ip_address))\n",
    "        wdir = os.getcwd()\n",
    "        cmd = \"cd {}/{}/ && wget {}\".format(wdir, ind+index, \"http://\"+instance.public_ip_address+\":8000/output.tar.gz\")\n",
    "        not_done = os.system(cmd)\n",
    "        \n",
    "        counter = 0\n",
    "        # wait for done\n",
    "        while not_done:\n",
    "            print(\"waiting... {}\".format(counter))\n",
    "            counter = counter + 1            \n",
    "            sleep(180)\n",
    "            not_done = os.system(cmd)\n",
    "                \n",
    "        index = index + 1\n",
    "        print(\"server #{} complete\".format(ind+index))\n",
    "    \n",
    "    \n",
    "    print(\"terminating batch in 15...\")\n",
    "    sleep(15)\n",
    "    \n",
    "    for instance in batch:\n",
    "        instance.terminate()\n",
    "    \n",
    "    print(\"wait for termination to complete...\")\n",
    "    sleep(100)\n",
    "    print(\"batch complete!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     Unnamed: 0 YearMonthDayUTC                                    Address  \\\n",
      "0           532   12APR20180625               8500 Firestone Blvd.  Downey   \n",
      "1           310   12APR03173938                       635 W Ina Rd  Tucson   \n",
      "2           408   12APR10164900           9540 Mason-Montgomery Rd.  Mason   \n",
      "3           277   12APR01171809                    4170 Lavon Dr.  Garland   \n",
      "4           662   12APR04171147             293 W. Campbell Rd. Richardson   \n",
      "5           877   12APR16162810                 4930 W. Broad St. Richmond   \n",
      "6           215   11APR26160017   435 Walt Whitman Rd.  Huntington Station   \n",
      "7           648   12APR03174450           3040 Excelsior Blvd. Minneapolis   \n",
      "8            11   11APR01193126   990 Serramonte Boulevard  Suite D  Colma   \n",
      "9           725   12APR06173627             4151 Sterling Ave. Kansas City   \n",
      "10          758   12APR09172510              7644 Lyndale Ave. S Richfield   \n",
      "11          102   11APR12192814                      1050 Gilman  Berkeley   \n",
      "12          138   11APR14185309        4530 S. Maryland Parkway  Las Vegas   \n",
      "13          409   12APR10164813           350 Forest Fair Dr.  Forest Park   \n",
      "14          484   12APR17180719               1800 Ximeno Ave.  Long Beach   \n",
      "15          520   12APR19165324               3335 Princeton Rd.  Hamilton   \n",
      "16          109   11APR12192818            1135 Industrial Rd.  San Carlos   \n",
      "17          612   12APR29165019         123 Perimeter Center West  Atlanta   \n",
      "18          775   12APR10183944              9240 W. Sahara Ave. Las Vegas   \n",
      "19           72   11APR09162127       1780 N. Congress Ave.  Boynton Beach   \n",
      "20          847   12APR13181851        2130 Southgate Rd. Colorado Springs   \n",
      "21           90   11APR11182747               235 West Wetmore Rd.  Tucson   \n",
      "22          343   12APR05185727              5310 Lakewood Blvd.  Lakewood   \n",
      "23          221   11APR27183558         5851 Sunrise Blvd.  Citrus Heights   \n",
      "24          479   12APR17191312          18951 Brookhurst  Fountain Valley   \n",
      "25          566   12APR24181150                3170 S. Wadsworth  Lakewood   \n",
      "26          111   11APR12175017    2605 South Stemmons Freeway  Lewisville   \n",
      "27          439   12APR13164148         12827 N. Dale Mabry Highway  Tampa   \n",
      "28          717   12APR05185727               5310 Lakewood Blvd. Lakewood   \n",
      "29          752   12APR09172410            10629 France Ave. S Bloomington   \n",
      "..          ...             ...                                        ...   \n",
      "572      296455   15SEP15164906    2900 SOUTH RUTHERFORD BLVD MURFREESBORO   \n",
      "573      296146   15SEP15155825           2000 CLEMENTS BRIDGE RD DEPTFORD   \n",
      "574      296260   15SEP02160209  3001 NORTH STATE ROAD  7 LAUDERDALE LAKES   \n",
      "575      296353   14SEP18175506                440 WADSWORTH BLVD LAKEWOOD   \n",
      "576      296473   15SEP28171009                1415 LAWRENCE DRIVE DE PERE   \n",
      "577      296303   15SEP11163017               3250 SARDIS CHURCH RD BUFORD   \n",
      "578      296667   15SEP03164121        2020 BASHFORD MANOR LANE LOUISVILLE   \n",
      "579      296487   14SEP11181508            2545 RIMROCK AVE GRAND JUNCTION   \n",
      "580      296309   14OCT01181353                240 WEST BASELINE ROAD MESA   \n",
      "581      296496   14SEP12172124        1603 VANCE JACKSON ROAD SAN ANTONIO   \n",
      "582      296550   14SEP09165235            5255 ELVIS PRESLEY BLVD MEMPHIS   \n",
      "583      296328   14OCT02180357             200 WEST 136TH AVE WESTMINSTER   \n",
      "584      296566   15SEP11155639                8064 BREWERETON ROAD CICERO   \n",
      "585      296603   15SEP20160920               3200 ATLANTIC BLVD NE CANTON   \n",
      "586      296583   15SEP30163343            4221 ATLANTA HIGHWAY LOGANVILLE   \n",
      "587      296591   15OCT01163141           13140 SOUTH TAMIAMI TRAIL OSPREY   \n",
      "588      296162   15SEP24162918             1133 NO EMERSON ROAD GREENWOOD   \n",
      "589      296545   15SEP20165523                   7855 MOFFETT ROAD SEMMES   \n",
      "590      296668   15SEP05162606        2020 BASHFORD MANOR LANE LOUISVILLE   \n",
      "591      296119   15SEP18162234              3240 WILKINSON BLVD CHARLOTTE   \n",
      "592      296332   15SEP29173250            4101 SOUTH MCCOLL ROAD EDINBURG   \n",
      "593      296204   14SEP30164112            2315 MADISON STREET CLARKSVILLE   \n",
      "594      296295   14SEP04163909                 1300 EAST ASH STREET PIQUA   \n",
      "595      296490   14SEP02160700              10050 GLENWOOD AVENUE RALEIGH   \n",
      "596      296540   14SEP26162934          1700 WEST MICHIGAN AVENUE JACKSON   \n",
      "597      296288   15SEP16175434        901 UNSER BLVD SOUTHEAST RIO RANCHO   \n",
      "598      296412   14AUG30163205             4021 LAGNIAPPE WAY TALLAHASSEE   \n",
      "599      296386   14SEP20161925                1326 BUSH RIVER RD COLUMBIA   \n",
      "600      296228   14SEP14192936                 1401 GALAXY DRIVE NE LACEY   \n",
      "601      296375   14SEP13162353              8990 TURKEY LAKE ROAD ORLANDO   \n",
      "\n",
      "    State      Zip   Cars  Male_Population  Population_Density  \\\n",
      "0      CA  90241.0   10.0           0.4789             8436.23   \n",
      "1      AZ  85704.0    2.0           0.4666             1836.90   \n",
      "2      OH  45040.0   15.0           0.4983             1733.30   \n",
      "3      TX  75040.0   29.0           0.5044             3806.69   \n",
      "4      TX  75080.0    6.0           0.4981             4242.16   \n",
      "5      VA  23230.0   23.0           0.4822             1736.07   \n",
      "6      NY  11746.0   15.0           0.5008             2543.06   \n",
      "7      MN  55416.0    8.0           0.4814             3799.95   \n",
      "8      CA  94014.0   15.0           0.4940             7980.97   \n",
      "9      MO  64133.0   14.0           0.4745             1896.65   \n",
      "10     MN  55423.0    9.0           0.4963             4570.64   \n",
      "11     CA  94701.0   17.0           0.5369             8311.34   \n",
      "12     NV  89119.0    6.0           0.5323             3667.37   \n",
      "13     OH  45240.0   21.0           0.4842             3098.98   \n",
      "14     CA  90815.0    7.0           0.4804             5239.90   \n",
      "15     OH  45011.0   28.0           0.5019             1069.30   \n",
      "16     CA  94070.0   21.0           0.4839             3970.50   \n",
      "17     GA  30346.0   20.0           0.4983             3554.44   \n",
      "18     NV  89117.0    1.0           0.4921             6746.58   \n",
      "19     FL  33426.0   19.0           0.4527             3024.14   \n",
      "20     CO  80906.0   35.0           0.4904              736.18   \n",
      "21     AZ  85705.0   12.0           0.5013             4185.44   \n",
      "22     CA  90712.0   31.0           0.4802             7052.97   \n",
      "23     CA  95610.0   26.0           0.4902             5698.29   \n",
      "24     CA  92708.0   19.0           0.4946             6061.14   \n",
      "25     CO  80227.0    6.0           0.4883             2851.59   \n",
      "26     TX  75067.0   12.0           0.4983             4780.95   \n",
      "27     FL  33618.0   27.0           0.4844             2639.33   \n",
      "28     CA  90712.0   36.0           0.4802             7052.97   \n",
      "29     MN  55431.0    2.0           0.4896             2330.63   \n",
      "..    ...      ...    ...              ...                 ...   \n",
      "572    TN  37130.0  212.0           0.5109              999.93   \n",
      "573    NJ   8096.0  284.0           0.4838             2642.93   \n",
      "574    FL  33313.0  302.0           0.4668             8997.74   \n",
      "575    CO  80226.0  192.0           0.4897             4127.96   \n",
      "576    WI  54115.0  284.0           0.5017              322.59   \n",
      "577    GA  30519.0  323.0           0.5157             1174.96   \n",
      "578    KY  40218.0  243.0           0.4695             3412.00   \n",
      "579    CO  81505.0  297.0           0.5112              130.38   \n",
      "580    AZ  85210.0  213.0           0.5260             5842.87   \n",
      "581    TX  78213.0  258.0           0.4738             4896.15   \n",
      "582    TN  38116.0  208.0           0.4570             2853.08   \n",
      "583    CO  80234.0  256.0           0.4951             3413.01   \n",
      "584    NY  13039.0  265.0           0.4955              666.27   \n",
      "585    OH  44705.0  289.0           0.4798             2289.63   \n",
      "586    GA  30052.0  225.0           0.4960              674.62   \n",
      "587    FL  34229.0  259.0           0.4939             1101.25   \n",
      "588    IN  46143.0  283.0           0.4775              905.85   \n",
      "589    AL  36575.0  315.0           0.4955              394.19   \n",
      "590    KY  40218.0  282.0           0.4695             3412.00   \n",
      "591    NC  28208.0  384.0           0.4723             1694.40   \n",
      "592    TX  78539.0  256.0           0.4810             2326.78   \n",
      "593    TN  37043.0  209.0           0.4798              343.92   \n",
      "594    OH  45356.0  210.0           0.4666              316.15   \n",
      "595    NC  27617.0  225.0           0.4920             2022.29   \n",
      "596    MI  49202.0  344.0           0.4727             1494.27   \n",
      "597    NM  87124.0  258.0           0.5009              749.31   \n",
      "598    FL  32317.0  313.0           0.4894              307.21   \n",
      "599    SC  29210.0  251.0           0.4771             2133.03   \n",
      "600    WA  98516.0  321.0           0.5129              664.82   \n",
      "601    FL  32819.0  404.0           0.4984             1083.78   \n",
      "\n",
      "     Average_ Family_Size  Per_Capita_Income  \\\n",
      "0                    3.59            23873.0   \n",
      "1                    3.09            28455.0   \n",
      "2                    3.30            36071.0   \n",
      "3                    3.89            19710.0   \n",
      "4                    3.25            29467.0   \n",
      "5                    3.13            24328.0   \n",
      "6                    3.48            45443.0   \n",
      "7                    2.87            49964.0   \n",
      "8                    4.23            22414.0   \n",
      "9                    2.98            23844.0   \n",
      "10                   3.01            28519.0   \n",
      "11                   3.49            20713.0   \n",
      "12                   3.35            19009.0   \n",
      "13                   3.68            22858.0   \n",
      "14                   3.11            37627.0   \n",
      "15                   3.26            23527.0   \n",
      "16                   3.11            58062.0   \n",
      "17                   2.90            42806.0   \n",
      "18                   3.18            36854.0   \n",
      "19                   2.80            24809.0   \n",
      "20                   3.34            34998.0   \n",
      "21                   3.43            14095.0   \n",
      "22                   3.44            28110.0   \n",
      "23                   3.27            25710.0   \n",
      "24                   3.54            31672.0   \n",
      "25                   3.02            31667.0   \n",
      "26                   3.72            25105.0   \n",
      "27                   3.08            39940.0   \n",
      "28                   3.44            28110.0   \n",
      "29                   2.95            33616.0   \n",
      "..                    ...                ...   \n",
      "572                  3.15            22222.0   \n",
      "573                  3.31            29431.0   \n",
      "574                  3.71            15661.0   \n",
      "575                  2.97            26915.0   \n",
      "576                  3.21            30094.0   \n",
      "577                  3.91            25419.0   \n",
      "578                  3.28            18805.0   \n",
      "579                  3.02            36811.0   \n",
      "580                  3.96            17684.0   \n",
      "581                  3.44            19352.0   \n",
      "582                  3.52            17677.0   \n",
      "583                  3.39            27669.0   \n",
      "584                  3.38            24834.0   \n",
      "585                  3.14            18991.0   \n",
      "586                  3.70            23020.0   \n",
      "587                  2.83            49649.0   \n",
      "588                  2.90            31123.0   \n",
      "589                  3.08            18101.0   \n",
      "590                  3.28            18805.0   \n",
      "591                  3.40            15595.0   \n",
      "592                  3.47            19155.0   \n",
      "593                  2.83            31893.0   \n",
      "594                  2.92            21714.0   \n",
      "595                  2.56            37007.0   \n",
      "596                  2.97            16488.0   \n",
      "597                  3.66            22780.0   \n",
      "598                  3.54            34799.0   \n",
      "599                  2.84            24931.0   \n",
      "600                  2.99            32877.0   \n",
      "601                  3.47            36280.0   \n",
      "\n",
      "     Median_Disposable_Household_Income  PCTNo_Vehicle  PCT_1_Vehicle  \\\n",
      "0                               44686.0         0.0764         0.3610   \n",
      "1                               47077.0         0.0790         0.4114   \n",
      "2                               72060.0         0.0337         0.2543   \n",
      "3                               54244.0         0.0347         0.2644   \n",
      "4                               57411.0         0.0378         0.3061   \n",
      "5                               39133.0         0.0954         0.4276   \n",
      "6                               75767.0         0.0350         0.2095   \n",
      "7                               56280.0         0.0885         0.4402   \n",
      "8                               60547.0         0.0698         0.3078   \n",
      "9                               45315.0         0.0548         0.3819   \n",
      "10                              47023.0         0.0974         0.4082   \n",
      "11                              32430.0         0.1398         0.5364   \n",
      "12                              33833.0         0.1805         0.5016   \n",
      "13                              53022.0         0.0402         0.3096   \n",
      "14                              61539.0         0.0442         0.3016   \n",
      "15                              51672.0         0.0807         0.2668   \n",
      "16                              85133.0         0.0416         0.2670   \n",
      "17                              60855.0         0.0470         0.5279   \n",
      "18                              54358.0         0.0568         0.4238   \n",
      "19                              43415.0         0.0742         0.4926   \n",
      "20                              53526.0         0.0431         0.3452   \n",
      "21                              25446.0         0.1731         0.4824   \n",
      "22                              58961.0         0.0575         0.2725   \n",
      "23                              47112.0         0.0486         0.3496   \n",
      "24                              69747.0         0.0325         0.2115   \n",
      "25                              53629.0         0.0266         0.3754   \n",
      "26                              55241.0         0.0434         0.3718   \n",
      "27                              52218.0         0.0468         0.3527   \n",
      "28                              58961.0         0.0575         0.2725   \n",
      "29                              58453.0         0.0653         0.3093   \n",
      "..                                  ...            ...            ...   \n",
      "572                             35831.0         0.0569         0.3917   \n",
      "573                             52533.0         0.0921         0.3654   \n",
      "574                             32014.0         0.1075         0.4767   \n",
      "575                             47127.0         0.0600         0.3619   \n",
      "576                             58535.0         0.0379         0.2775   \n",
      "577                             68549.0         0.0144         0.1832   \n",
      "578                             37382.0         0.1037         0.4643   \n",
      "579                             41147.0         0.0338         0.2750   \n",
      "580                             41767.0         0.0806         0.4182   \n",
      "581                             37543.0         0.0823         0.4633   \n",
      "582                             36460.0         0.1580         0.4468   \n",
      "583                             58264.0         0.0377         0.3243   \n",
      "584                             57227.0         0.0344         0.2797   \n",
      "585                             34241.0         0.1095         0.3598   \n",
      "586                             60256.0         0.0164         0.1965   \n",
      "587                             58559.0         0.0288         0.3484   \n",
      "588                             59561.0         0.0367         0.2910   \n",
      "589                             44126.0         0.0348         0.2505   \n",
      "590                             37382.0         0.1037         0.4643   \n",
      "591                             30552.0         0.1717         0.4623   \n",
      "592                             40441.0         0.0679         0.4015   \n",
      "593                             47481.0         0.0362         0.2682   \n",
      "594                             40438.0         0.0796         0.4032   \n",
      "595                             61357.0         0.0333         0.2874   \n",
      "596                             35995.0         0.1200         0.4481   \n",
      "597                             47901.0         0.0275         0.2726   \n",
      "598                             69551.0         0.0208         0.1985   \n",
      "599                             39127.0         0.0637         0.4792   \n",
      "600                             58027.0         0.0368         0.2559   \n",
      "601                             66660.0         0.0195         0.2831   \n",
      "\n",
      "     PCT_2_Vehicles  PCTMore_2_Vehicles     Region          Sub Region  \\\n",
      "0            0.3547              0.2079       West             Pacific   \n",
      "1            0.3475              0.1621       West            Mountain   \n",
      "2            0.5134              0.1986    Midwest               Other   \n",
      "3            0.4661              0.2348      South               Other   \n",
      "4            0.4887              0.1674      South               Other   \n",
      "5            0.3494              0.1276      South               Other   \n",
      "6            0.4662              0.2893  Northeast               Other   \n",
      "7            0.3716              0.0997    Midwest               Other   \n",
      "8            0.3534              0.2690       West             Pacific   \n",
      "9            0.3782              0.1851      South               Other   \n",
      "10           0.3582              0.1362    Midwest               Other   \n",
      "11           0.2619              0.0619       West             Pacific   \n",
      "12           0.2540              0.0639       West             Pacific   \n",
      "13           0.4295              0.2207    Midwest               Other   \n",
      "14           0.4274              0.2268       West             Pacific   \n",
      "15           0.4333              0.2192    Midwest               Other   \n",
      "16           0.4278              0.2636       West             Pacific   \n",
      "17           0.3537              0.0714      South               Other   \n",
      "18           0.3762              0.1432       West             Pacific   \n",
      "19           0.3459              0.0873      South               Other   \n",
      "20           0.4326              0.1791       West            Mountain   \n",
      "21           0.2546              0.0899       West            Mountain   \n",
      "22           0.4004              0.2696       West             Pacific   \n",
      "23           0.3993              0.2025       West             Pacific   \n",
      "24           0.4190              0.3370       West             Pacific   \n",
      "25           0.4129              0.1851       West            Mountain   \n",
      "26           0.4497              0.1351      South               Other   \n",
      "27           0.4465              0.1540      South               Other   \n",
      "28           0.4004              0.2696       West             Pacific   \n",
      "29           0.4329              0.1925    Midwest               Other   \n",
      "..              ...                 ...        ...                 ...   \n",
      "572          0.3731              0.1783      South  East South Central   \n",
      "573          0.3678              0.1747  Northeast        Mid Atlantic   \n",
      "574          0.3015              0.1143      South      South Atlantic   \n",
      "575          0.3665              0.2116       West            Mountain   \n",
      "576          0.4697              0.2149    Midwest  East North Central   \n",
      "577          0.5139              0.2885      South      South Atlantic   \n",
      "578          0.3163              0.1157      South  East South Central   \n",
      "579          0.4128              0.2784       West            Mountain   \n",
      "580          0.3430              0.1582       West            Mountain   \n",
      "581          0.3339              0.1205      South  West South Central   \n",
      "582          0.2744              0.1208      South  East South Central   \n",
      "583          0.4344              0.2036       West            Mountain   \n",
      "584          0.4928              0.1931  Northeast        Mid Atlantic   \n",
      "585          0.3466              0.1841    Midwest  East North Central   \n",
      "586          0.4727              0.3144      South      South Atlantic   \n",
      "587          0.4652              0.1576      South      South Atlantic   \n",
      "588          0.4524              0.2199    Midwest  East North Central   \n",
      "589          0.4640              0.2507      South  East South Central   \n",
      "590          0.3163              0.1157      South  East South Central   \n",
      "591          0.2418              0.1242      South      South Atlantic   \n",
      "592          0.3651              0.1655      South  West South Central   \n",
      "593          0.4303              0.2653      South  East South Central   \n",
      "594          0.3586              0.1586    Midwest  East North Central   \n",
      "595          0.4931              0.1862      South      South Atlantic   \n",
      "596          0.3211              0.1108    Midwest  East North Central   \n",
      "597          0.4234              0.2765       West            Mountain   \n",
      "598          0.5176              0.2631      South      South Atlantic   \n",
      "599          0.3339              0.1232      South      South Atlantic   \n",
      "600          0.4194              0.2879       West             Pacific   \n",
      "601          0.4787              0.2187      South      South Atlantic   \n",
      "\n",
      "           Lat         Lng  \n",
      "0          NaN         NaN  \n",
      "1    32.336605 -110.979019  \n",
      "2    39.299679  -84.314228  \n",
      "3    32.955300  -96.616877  \n",
      "4    32.975354  -96.722786  \n",
      "5    37.584604  -77.496576  \n",
      "6    40.814774  -73.410913  \n",
      "7    44.947425  -93.321300  \n",
      "8    37.671072 -122.464325  \n",
      "9    39.046535  -94.446983  \n",
      "10   44.864032  -93.288856  \n",
      "11   37.879212 -122.296297  \n",
      "12   36.107068 -115.136935  \n",
      "13   39.303040  -84.522736  \n",
      "14   33.790222 -118.138497  \n",
      "15   39.389938  -84.504565  \n",
      "16   37.502275 -122.246312  \n",
      "17   33.927853  -84.343974  \n",
      "18   36.144782 -115.297255  \n",
      "19   26.542936  -80.090160  \n",
      "20   38.801428 -104.817619  \n",
      "21   32.286161 -110.975588  \n",
      "22   33.854154 -118.141944  \n",
      "23   38.672035 -121.272047  \n",
      "24   33.687583 -117.954544  \n",
      "25   39.660742 -105.080935  \n",
      "26   33.000508  -96.962614  \n",
      "27   28.063410  -82.503359  \n",
      "28   33.854154 -118.141944  \n",
      "29   44.811197  -93.329017  \n",
      "..         ...         ...  \n",
      "572  35.831408  -86.346284  \n",
      "573  39.844290  -75.091659  \n",
      "574  26.162392  -80.205431  \n",
      "575  39.722293 -105.080362  \n",
      "576  44.432722  -88.102762  \n",
      "577  34.078349  -83.921878  \n",
      "578  38.199065  -85.667597  \n",
      "579  39.076256 -108.581696  \n",
      "580  33.380723 -111.836775  \n",
      "581  29.487974  -98.537729  \n",
      "582  35.004998  -90.027742  \n",
      "583  39.940321 -104.990553  \n",
      "584  43.160972  -76.117663  \n",
      "585  40.835013  -81.329198  \n",
      "586  33.840907  -83.911218  \n",
      "587  27.192974  -82.486157  \n",
      "588  39.634012  -86.080029  \n",
      "589  30.762365  -88.228328  \n",
      "590  38.199065  -85.667597  \n",
      "591  35.226389  -80.894657  \n",
      "592  26.267621  -98.204595  \n",
      "593  36.509307  -87.269140  \n",
      "594  40.153226  -84.208368  \n",
      "595  35.912336  -78.777677  \n",
      "596  42.249534  -84.434326  \n",
      "597  35.242929 -106.699694  \n",
      "598  30.460655  -84.217139  \n",
      "599  34.028493  -81.108425  \n",
      "600  47.060436 -122.773773  \n",
      "601  28.439832  -81.476474  \n",
      "\n",
      "[296702 rows x 19 columns]\n",
      "done!\n"
     ]
    }
   ],
   "source": [
    "# merge output files\n",
    "\n",
    "os.system(\"mkdir output\")\n",
    "\n",
    "def collect(num):\n",
    "    cmd = \"cd {}/ && tar xzf output.tar.gz\".format(num)\n",
    "    os.system(cmd)\n",
    "    \n",
    "    cmd = \"mv {}/output.csv output/{}.csv\".format(num, num)\n",
    "    os.system(cmd)\n",
    "    \n",
    "\n",
    "def merge(num):\n",
    "    fname = \"output/{}.csv\".format(num)\n",
    "    return pd.read_csv(fname)\n",
    "    \n",
    "frames = []    \n",
    "for i in range(0,330):\n",
    "    collect(i)\n",
    "    frames.append(merge(i))\n",
    "\n",
    "result = pd.concat(frames)\n",
    "print(result)\n",
    "\n",
    "datafile = \"combined.csv\"\n",
    "result.to_csv(path_or_buf=datafile)\n",
    "print(\"done!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "resultData.csv written!\n"
     ]
    }
   ],
   "source": [
    "# write csv for map\n",
    "\n",
    "#lines = result.loc[:, result.columns.isin([\"Address\", \"State\", \"Lat\", \"Lng\"])]\n",
    "columns = [\"Address\", \"State\", \"Lat\", \"Lng\"]\n",
    "df1 = pd.DataFrame(result, columns=columns)\n",
    "datafile = \"resultData.csv\"\n",
    "df1.to_csv(path_or_buf=datafile)\n",
    "print(\"{} written!\".format(datafile)) "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}