<commit_before>{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>﻿</th>\n",
       "      <th>geoid</th>\n",
       "      <th>fd_id</th>\n",
       "      <th>fd_size</th>\n",
       "      <th>region</th>\n",
       "      <th>state</th>\n",
       "      <th>lr_fire</th>\n",
       "      <th>mr_fire</th>\n",
       "      <th>lr_inj</th>\n",
       "      <th>mr_inj</th>\n",
       "      <th>lr_death</th>\n",
       "      <th>mr_death</th>\n",
       "      <th>lr_size_2</th>\n",
       "      <th>lr_size_3</th>\n",
       "      <th>mr_size_2</th>\n",
       "      <th>mr_size_3</th>\n",
       "      <th>h.fire</th>\n",
       "      <th>h.inj</th>\n",
       "      <th>h.death</th>\n",
       "      <th>h.size2</th>\n",
       "      <th>h.size3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>14000US01001020100</td>\n",
       "      <td>92679</td>\n",
       "      <td>size_4</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.30</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>14000US01001020200</td>\n",
       "      <td>92679</td>\n",
       "      <td>size_4</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.60</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>14000US01001020300</td>\n",
       "      <td>92679</td>\n",
       "      <td>size_4</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.60</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.50</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>14000US01001020400</td>\n",
       "      <td>92679</td>\n",
       "      <td>size_4</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>14000US01001020500</td>\n",
       "      <td>92679</td>\n",
       "      <td>size_4</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>14000US01001020600</td>\n",
       "      <td>92679</td>\n",
       "      <td>size_4</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.40</td>\n",
       "      <td>0.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>14000US01001020700</td>\n",
       "      <td>92679</td>\n",
       "      <td>size_4</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>14000US01001020801</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>14000US01001020802</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>14000US01001020900</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>14000US01001021000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>14000US01001021100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>14000US01003010100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>14000US01003010200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>14000US01003010300</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>14000US01003010400</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>14000US01003010500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>14000US01003010600</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>14000US01003010701</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>14000US01003010703</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>14000US01003010704</td>\n",
       "      <td>79344</td>\n",
       "      <td>size_3</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22</td>\n",
       "      <td>14000US01003010705</td>\n",
       "      <td>79344</td>\n",
       "      <td>size_3</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23</td>\n",
       "      <td>14000US01003010800</td>\n",
       "      <td>79344</td>\n",
       "      <td>size_3</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>14000US01003010903</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>14000US01003010904</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>14000US01003010905</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>27</td>\n",
       "      <td>14000US01003010906</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>28</td>\n",
       "      <td>14000US01003011000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>29</td>\n",
       "      <td>14000US01003011101</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ﻿               geoid  fd_id fd_size region state  lr_fire  mr_fire  \\\n",
       "0    1  14000US01001020100  92679  size_4  South    AL        1        7   \n",
       "1    2  14000US01001020200  92679  size_4  South    AL        2        6   \n",
       "2    3  14000US01001020300  92679  size_4  South    AL        3        5   \n",
       "3    4  14000US01001020400  92679  size_4  South    AL        4        0   \n",
       "4    5  14000US01001020500  92679  size_4  South    AL        5        0   \n",
       "5    6  14000US01001020600  92679  size_4  South    AL        6      NaN   \n",
       "6    7  14000US01001020700  92679  size_4  South    AL        7        0   \n",
       "7    8  14000US01001020801    NaN     NaN  South    AL      NaN      NaN   \n",
       "8    9  14000US01001020802    NaN     NaN  South    AL      NaN      NaN   \n",
       "9   10  14000US01001020900    NaN     NaN  South    AL      NaN      NaN   \n",
       "10  11  14000US01001021000    NaN     NaN  South    AL      NaN      NaN   \n",
       "11  12  14000US01001021100    NaN     NaN  South    AL      NaN      NaN   \n",
       "12  13  14000US01003010100    NaN     NaN  South    AL      NaN      NaN   \n",
       "13  14  14000US01003010200    NaN     NaN  South    AL      NaN      NaN   \n",
       "14  15  14000US01003010300    NaN     NaN  South    AL      NaN      NaN   \n",
       "15  16  14000US01003010400    NaN     NaN  South    AL      NaN      NaN   \n",
       "16  17  14000US01003010500    NaN     NaN  South    AL      NaN      NaN   \n",
       "17  18  14000US01003010600    NaN     NaN  South    AL      NaN      NaN   \n",
       "18  19  14000US01003010701    NaN     NaN  South    AL      NaN      NaN   \n",
       "19  20  14000US01003010703    NaN     NaN  South    AL      NaN      NaN   \n",
       "20  21  14000US01003010704  79344  size_3  South    AL      NaN      NaN   \n",
       "21  22  14000US01003010705  79344  size_3  South    AL      NaN      NaN   \n",
       "22  23  14000US01003010800  79344  size_3  South    AL      NaN      NaN   \n",
       "23  24  14000US01003010903    NaN     NaN  South    AL      NaN      NaN   \n",
       "24  25  14000US01003010904    NaN     NaN  South    AL      NaN      NaN   \n",
       "25  26  14000US01003010905    NaN     NaN  South    AL      NaN      NaN   \n",
       "26  27  14000US01003010906    NaN     NaN  South    AL      NaN      NaN   \n",
       "27  28  14000US01003011000    NaN     NaN  South    AL      NaN      NaN   \n",
       "28  29  14000US01003011101    NaN     NaN  South    AL      NaN      NaN   \n",
       "\n",
       "    lr_inj  mr_inj  lr_death  mr_death  lr_size_2  lr_size_3  mr_size_2  \\\n",
       "0        1       0         1       NaN       0.30       0.25       0.25   \n",
       "1        2       0         1       NaN       0.60       0.25       0.00   \n",
       "2        3       0         1       NaN       0.25       0.60       0.50   \n",
       "3      NaN       0         1       NaN       0.10       0.00       0.25   \n",
       "4      NaN       0         1       NaN       0.20       0.00       0.10   \n",
       "5      NaN       0         1       NaN       0.40       0.75        NaN   \n",
       "6      NaN       0         1       NaN       0.50       0.80        NaN   \n",
       "7      NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "8      NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "9      NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "10     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "11     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "12     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "13     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "14     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "15     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "16     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "17     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "18     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "19     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "20     NaN       6       NaN         0        NaN        NaN        NaN   \n",
       "21       0       0       NaN         1        NaN        NaN        NaN   \n",
       "22       0       5       NaN         0        NaN        NaN        NaN   \n",
       "23     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "24     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "25     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "26     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "27     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "28     NaN     NaN       NaN       NaN        NaN        NaN        NaN   \n",
       "\n",
       "    mr_size_3  h.fire  h.inj  h.death  h.size2  h.size3  \n",
       "0        0.25     NaN    NaN      NaN      NaN      NaN  \n",
       "1        0.00       1      1        0     0.25     0.25  \n",
       "2        0.50       2      1        0     0.50     0.25  \n",
       "3        0.25       3    NaN      NaN      NaN      NaN  \n",
       "4         NaN       4      1        0     0.25     0.25  \n",
       "5         NaN     NaN      1        1      NaN      NaN  \n",
       "6         NaN     NaN      1      NaN      NaN      NaN  \n",
       "7         NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "8         NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "9         NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "10        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "11        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "12        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "13        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "14        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "15        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "16        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "17        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "18        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "19        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "20        NaN     NaN      1        0      NaN      NaN  \n",
       "21        NaN     NaN      2        0      NaN      NaN  \n",
       "22        NaN     NaN      3        0      NaN      NaN  \n",
       "23        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "24        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "25        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "26        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "27        NaN     NaN    NaN      NaN      NaN      NaN  \n",
       "28        NaN     NaN    NaN      NaN      NaN      NaN  "
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sys\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "sys.path.insert(0, os.path.realpath('..'))\n",
    "import django\n",
    "django.setup()\n",
    "from firecares.firestation.models import FireDepartment, FireStation\n",
    "from IPython.display import display\n",
    "\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "# Note: you'll need to move the predictions csv into /firecares, see s3://firecares-share/predictions.2015.csv\n",
    "\n",
    "df = pd.read_csv('../firecares/firestation/tests/mock/predictions.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Count of departments in csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>lr_fire</th>\n",
       "      <th>lr_size_2</th>\n",
       "      <th>lr_size_3</th>\n",
       "      <th>mr_fire</th>\n",
       "      <th>mr_size_2</th>\n",
       "      <th>mr_size_3</th>\n",
       "      <th>h.fire</th>\n",
       "      <th>h.size2</th>\n",
       "      <th>h.size3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fd_id</th>\n",
       "      <th>state</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>92679</th>\n",
       "      <th>AL</th>\n",
       "      <td>28</td>\n",
       "      <td>2.35</td>\n",
       "      <td>2.65</td>\n",
       "      <td>18</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             lr_fire  lr_size_2  lr_size_3  mr_fire  mr_size_2  mr_size_3  \\\n",
       "fd_id state                                                                 \n",
       "92679 AL          28       2.35       2.65       18        1.1          1   \n",
       "\n",
       "             h.fire  h.size2  h.size3  \n",
       "fd_id state                            \n",
       "92679 AL         10        1     0.75  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>lr_beyond_room</th>\n",
       "      <th>lr_beyond_structure</th>\n",
       "      <th>mr_beyond_room</th>\n",
       "      <th>mr_beyond_structure</th>\n",
       "      <th>hr_beyond_room</th>\n",
       "      <th>hr_beyond_structure</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fd_id</th>\n",
       "      <th>state</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>92679</th>\n",
       "      <th>AL</th>\n",
       "      <td>9.55</td>\n",
       "      <td>5.425</td>\n",
       "      <td>4.25</td>\n",
       "      <td>1.6875</td>\n",
       "      <td>2.25</td>\n",
       "      <td>0.5625</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             lr_beyond_room  lr_beyond_structure  mr_beyond_room  \\\n",
       "fd_id state                                                        \n",
       "92679 AL               9.55                5.425            4.25   \n",
       "\n",
       "             mr_beyond_structure  hr_beyond_room  hr_beyond_structure  \n",
       "fd_id state                                                            \n",
       "92679 AL                  1.6875            2.25               0.5625  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "% of low risk beyond room/floor fires: 0.341071428571\n",
      "% of low risk beyond structure fires: 0.568062827225\n",
      "% of medium risk beyond room/floor fires: 0.236111111111\n",
      "% of medium risk beyond structure fires: 0.397058823529\n",
      "% of high risk beyond room/floor fires: 0.225\n",
      "% of high risk beyond structure fires: 0.25\n"
     ]
    }
   ],
   "source": [
    "cols = ['lr_fire', 'lr_size_2', 'lr_size_3', 'mr_fire', 'mr_size_2', 'mr_size_3', 'h.fire', 'h.size2', 'h.size3']\n",
    "\n",
    "fd_id = 92679\n",
    "\n",
    "# for xx.size2 (beyond room + beyond floor) you would want\n",
    "# Sum( xx.fire * xx.size2 ) / sum( xx.fire )\n",
    "# And for xx.size3 (beyond structure) you would want\n",
    "# Sum( xx.fire * xx.size2 * xx.size3 ) / sum( xx. fire * xx.size2 )\n",
    "df['lr_beyond_room'] = df[df['fd_id'] == fd_id].apply(lambda row: row['lr_fire'] * row['lr_size_2'], axis=1)\n",
    "df['lr_beyond_structure'] = df[df['fd_id'] == fd_id].apply(lambda row: row['lr_fire'] * row['lr_size_2'] * row['lr_size_3'], axis=1)\n",
    "df['mr_beyond_room'] = df[df['fd_id'] == fd_id].apply(lambda row: row['mr_fire'] * row['mr_size_2'], axis=1)\n",
    "df['mr_beyond_structure'] = df[df['fd_id'] == fd_id].apply(lambda row: row['mr_fire'] * row['mr_size_2'] * row['mr_size_3'], axis=1)\n",
    "df['hr_beyond_room'] = df[df['fd_id'] == fd_id].apply(lambda row: row['h.fire'] * row['h.size2'], axis=1)\n",
    "df['hr_beyond_structure'] = df[df['fd_id'] == fd_id].apply(lambda row: row['h.fire'] * row['h.size2'] * row['h.size3'], axis=1)\n",
    "\n",
    "cols2 = ['lr_beyond_room', 'lr_beyond_structure', 'mr_beyond_room', 'mr_beyond_structure', 'hr_beyond_room', 'hr_beyond_structure']\n",
    "\n",
    "sums = df[df['fd_id'] == fd_id].groupby(['fd_id', 'state']).sum()\n",
    "display(sums[cols])\n",
    "display(sums[cols2])\n",
    "\n",
    "print '% of low risk beyond room/floor fires: {}'.format(sums['lr_beyond_room'][0] / sums['lr_fire'][0])\n",
    "print '% of low risk beyond structure fires: {}'.format(sums['lr_beyond_structure'][0] / sums['lr_beyond_room'][0])\n",
    "print '% of medium risk beyond room/floor fires: {}'.format(sums['mr_beyond_room'][0] / sums['mr_fire'][0])\n",
    "print '% of medium risk beyond structure fires: {}'.format(sums['mr_beyond_structure'][0] / sums['mr_beyond_room'][0])\n",
    "print '% of high risk beyond room/floor fires: {}'.format(sums['hr_beyond_room'][0] / sums['h.fire'][0])\n",
    "print '% of high risk beyond structure fires: {}'.format(sums['hr_beyond_structure'][0] / sums['hr_beyond_room'][0])\n",
    "\n",
    "#df[df['fd_id'] == 93345]\n",
    "#lr_size2 = df[['lr_fire', 'lr_size_2']].apply(lambda row: row['lr_fire'] * row['lr_size_2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>lr_fire</th>\n",
       "      <th>mr_fire</th>\n",
       "      <th>lr_inj</th>\n",
       "      <th>mr_inj</th>\n",
       "      <th>lr_death</th>\n",
       "      <th>mr_death</th>\n",
       "      <th>lr_size_2</th>\n",
       "      <th>lr_size_3</th>\n",
       "      <th>mr_size_2</th>\n",
       "      <th>mr_size_3</th>\n",
       "      <th>h.fire</th>\n",
       "      <th>h.inj</th>\n",
       "      <th>h.death</th>\n",
       "      <th>h.size2</th>\n",
       "      <th>h.size3</th>\n",
       "      <th>lr_beyond_room</th>\n",
       "      <th>lr_beyond_structure</th>\n",
       "      <th>mr_beyond_room</th>\n",
       "      <th>mr_beyond_structure</th>\n",
       "      <th>hr_beyond_room</th>\n",
       "      <th>hr_beyond_structure</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fd_id</th>\n",
       "      <th>state</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>93345</th>\n",
       "      <th>VA</th>\n",
       "      <td>166.284421</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45.619757</td>\n",
       "      <td>4.770429</td>\n",
       "      <td>1.747018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29.837817</td>\n",
       "      <td>7.132377</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>177.324624</td>\n",
       "      <td>0.905018</td>\n",
       "      <td>1.279571</td>\n",
       "      <td>10.111845</td>\n",
       "      <td>7.179326</td>\n",
       "      <td>76.0249</td>\n",
       "      <td>7.836138</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.909935</td>\n",
       "      <td>2.018505</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                lr_fire  mr_fire     lr_inj    mr_inj  lr_death  mr_death  \\\n",
       "fd_id state                                                                 \n",
       "93345 VA     166.284421      NaN  45.619757  4.770429  1.747018       NaN   \n",
       "\n",
       "             lr_size_2  lr_size_3  mr_size_2  mr_size_3      h.fire     h.inj  \\\n",
       "fd_id state                                                                     \n",
       "93345 VA     29.837817   7.132377        NaN        NaN  177.324624  0.905018   \n",
       "\n",
       "              h.death    h.size2   h.size3  lr_beyond_room  \\\n",
       "fd_id state                                                  \n",
       "93345 VA     1.279571  10.111845  7.179326         76.0249   \n",
       "\n",
       "             lr_beyond_structure  mr_beyond_room  mr_beyond_structure  \\\n",
       "fd_id state                                                             \n",
       "93345 VA                7.836138             NaN                  NaN   \n",
       "\n",
       "             hr_beyond_room  hr_beyond_structure  \n",
       "fd_id state                                       \n",
       "93345 VA          17.909935             2.018505  "
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols2 = ['lr_size_2', 'mr_size_2', 'h.size2', 'lr_size_3', 'mr_size_3', 'h.size3']\n",
    "\n",
    "df[df['fd_id'] == 93345].groupby(['fd_id', 'state']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CSV counts vs FireCARES count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(799, 72, 0.09011264080100125)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db = len(FireDepartment.objects.count())\n",
    "csv = len(df.groupby(['fd_id', 'state'])[cols].sum())\n",
    "(db, csv, csv/float(db))  # hit rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "nan"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.array([1, np.nan, 2]).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Django Shell-Plus",
   "language": "python",
   "name": "django_extensions"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}<commit_msg>Added queries re: community risk for unusual situations<commit_after>{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "sys.path.insert(0, os.path.realpath('..'))\n",
    "import django\n",
    "from django.db import connections\n",
    "django.setup()\n",
    "from firecares.firestation.models import FireDepartment, FireStation\n",
    "from IPython.display import display\n",
    "\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "# Note: you'll need to move the predictions csv into /firecares, see s3://firecares-share/predictions.2015.csv\n",
    "\n",
    "df = pd.read_csv('/firecares/predictions.2015.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Count of departments in csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cols = ['lr_fire', 'lr_size_2', 'lr_size_3', 'mr_fire', 'mr_size_2', 'mr_size_3', 'h.fire', 'h.size2', 'h.size3']\n",
    "\n",
    "fd_id = 75500\n",
    "\n",
    "# for xx.size2 (beyond room + beyond floor) you would want\n",
    "# Sum( xx.fire * xx.size2 ) / sum( xx.fire )\n",
    "# And for xx.size3 (beyond structure) you would want\n",
    "# Sum( xx.fire * xx.size2 * xx.size3 ) / sum( xx. fire * xx.size2 )\n",
    "df['lr_beyond_room'] = df[df['fd_id'] == fd_id].apply(lambda row: row['lr_fire'] * row['lr_size_2'], axis=1)\n",
    "df['lr_beyond_structure'] = df[df['fd_id'] == fd_id].apply(lambda row: row['lr_fire'] * row['lr_size_2'] * row['lr_size_3'], axis=1)\n",
    "df['mr_beyond_room'] = df[df['fd_id'] == fd_id].apply(lambda row: row['mr_fire'] * row['mr_size_2'], axis=1)\n",
    "df['mr_beyond_structure'] = df[df['fd_id'] == fd_id].apply(lambda row: row['mr_fire'] * row['mr_size_2'] * row['mr_size_3'], axis=1)\n",
    "df['hr_beyond_room'] = df[df['fd_id'] == fd_id].apply(lambda row: row['h.fire'] * row['h.size2'], axis=1)\n",
    "df['hr_beyond_structure'] = df[df['fd_id'] == fd_id].apply(lambda row: row['h.fire'] * row['h.size2'] * row['h.size3'], axis=1)\n",
    "\n",
    "cols2 = ['lr_beyond_room', 'lr_beyond_structure', 'mr_beyond_room', 'mr_beyond_structure', 'hr_beyond_room', 'hr_beyond_structure']\n",
    "\n",
    "sums = df[df['fd_id'] == fd_id].groupby(['fd_id', 'state']).sum()\n",
    "display(sums[cols])\n",
    "display(sums[cols2])\n",
    "\n",
    "print '% of low risk beyond room/floor fires: {}'.format(sums['lr_beyond_room'][0] / sums['lr_fire'][0])\n",
    "print '% of low risk beyond structure fires: {}'.format(sums['lr_beyond_structure'][0] / sums['lr_beyond_room'][0])\n",
    "print '% of medium risk beyond room/floor fires: {}'.format(sums['mr_beyond_room'][0] / sums['mr_fire'][0])\n",
    "print '% of medium risk beyond structure fires: {}'.format(sums['mr_beyond_structure'][0] / sums['mr_beyond_room'][0])\n",
    "print '% of high risk beyond room/floor fires: {}'.format(sums['hr_beyond_room'][0] / sums['h.fire'][0])\n",
    "print '% of high risk beyond structure fires: {}'.format(sums['hr_beyond_structure'][0] / sums['hr_beyond_room'][0])\n",
    "\n",
    "#df[df['fd_id'] == 93345]\n",
    "#lr_size2 = df[['lr_fire', 'lr_size_2']].apply(lambda row: row['lr_fire'] * row['lr_size_2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cols2 = ['lr_size_2', 'mr_size_2', 'h.size2', 'lr_size_3', 'mr_size_3', 'h.size3']\n",
    "\n",
    "df[df['fd_id'] == 93345].groupby(['fd_id', 'state']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CSV counts vs FireCARES count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "db = len(FireDepartment.objects.count())\n",
    "csv = len(df.groupby(['fd_id', 'state'])[cols].sum())\n",
    "(db, csv, csv/float(db))  # hit rate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Community Assessment vs Performance Score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Has no community fires assessment, but has deaths/injury assessment\n",
    "q = \"\"\"\n",
    "select department_id,\n",
    "    case when level = 0 then 'ALL LEVELS'\n",
    "        when level = 1 then 'LOW'\n",
    "        when level = 2 then 'MEDIUM'\n",
    "        when level = 4 then 'HIGH'\n",
    "        when level = 5 then 'UNKNOWN'\n",
    "    end\n",
    "from firestation_firedepartmentriskmodels\n",
    "where risk_model_fires is null and (risk_model_injuries is not null or risk_model_deaths is not null)\n",
    "\"\"\"\n",
    "pd.read_sql_query(q, connections['default']).to_csv('/tmp/only_deaths_injuries.csv')\n",
    "#pd.read_sql_query(q, connections['default'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Has performance score, but is missing one or more community assessments\n",
    "q = \"\"\"\n",
    "select department_id,\n",
    "    case when level = 0 then 'ALL LEVELS'\n",
    "        when level = 1 then 'LOW'\n",
    "        when level = 2 then 'MEDIUM'\n",
    "        when level = 4 then 'HIGH'\n",
    "        when level = 5 then 'UNKNOWN'\n",
    "    end,\n",
    "    risk_model_injuries is null as injuries_empty,\n",
    "    risk_model_deaths is null as deaths_empty,\n",
    "    risk_model_fires is null as fires_empty,\n",
    "    risk_model_fires_size1 is null as fires_size1_empty,\n",
    "    risk_model_fires_size2 is null as fires_size2_empty,\n",
    "    dist_model_score\n",
    "from firestation_firedepartmentriskmodels\n",
    "where\n",
    "    dist_model_score is not null\n",
    "        and (risk_model_injuries is null\n",
    "            or risk_model_deaths is null\n",
    "            or risk_model_fires is null\n",
    "            or risk_model_fires_size1 is null\n",
    "            or risk_model_fires_size2 is null)\n",
    "        and level != 5\n",
    "order by department_id\n",
    "\"\"\"\n",
    "pd.read_sql_query(q, connections['default']).to_csv('/tmp/score_but_no_assessment.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Django Shell-Plus",
   "language": "python",
   "name": "django_extensions"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
{'fraction_non_alphanumeric': 0.2248610210776514, 'fraction_numerical': 0.04620189757793624, 'mean_word_length': 1.1757415163689204, 'pattern_counts': {'":': 158, '<': 1685, '<?xml version=': 0, '>': 1685, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 66, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3285534', 'n_tokens_mistral': 21564, 'n_tokens_neox': 19879, 'n_words': 2828}