In [ ]:

    

import sys
import os
import pandas as pd
import numpy as np
sys.path.insert(0, os.path.realpath('..'))
import django
from django.db import connections
django.setup()
from firecares.firestation.models import FireDepartment, FireStation
from IPython.display import display

pd.set_option('display.max_columns', None)

# Note: you'll need to move the predictions csv into /firecares, see s3://firecares-share/predictions.2015.csv

df = pd.read_csv('/firecares/predictions.2015.csv')
df


    

In [ ]:

    

cols = ['lr_fire', 'lr_size_2', 'lr_size_3', 'mr_fire', 'mr_size_2', 'mr_size_3', 'h.fire', 'h.size2', 'h.size3']

fd_id = 75500

# for xx.size2 (beyond room + beyond floor) you would want
# Sum( xx.fire * xx.size2 ) / sum( xx.fire )
# And for xx.size3 (beyond structure) you would want
# Sum( xx.fire * xx.size2 * xx.size3 ) / sum( xx. fire * xx.size2 )
df['lr_beyond_room'] = df[df['fd_id'] == fd_id].apply(lambda row: row['lr_fire'] * row['lr_size_2'], axis=1)
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)
df['mr_beyond_room'] = df[df['fd_id'] == fd_id].apply(lambda row: row['mr_fire'] * row['mr_size_2'], axis=1)
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)
df['hr_beyond_room'] = df[df['fd_id'] == fd_id].apply(lambda row: row['h.fire'] * row['h.size2'], axis=1)
df['hr_beyond_structure'] = df[df['fd_id'] == fd_id].apply(lambda row: row['h.fire'] * row['h.size2'] * row['h.size3'], axis=1)

cols2 = ['lr_beyond_room', 'lr_beyond_structure', 'mr_beyond_room', 'mr_beyond_structure', 'hr_beyond_room', 'hr_beyond_structure']

sums = df[df['fd_id'] == fd_id].groupby(['fd_id', 'state']).sum()
display(sums[cols])
display(sums[cols2])

print '% of low risk beyond room/floor fires: {}'.format(sums['lr_beyond_room'][0] / sums['lr_fire'][0])
print '% of low risk beyond structure fires: {}'.format(sums['lr_beyond_structure'][0] / sums['lr_beyond_room'][0])
print '% of medium risk beyond room/floor fires: {}'.format(sums['mr_beyond_room'][0] / sums['mr_fire'][0])
print '% of medium risk beyond structure fires: {}'.format(sums['mr_beyond_structure'][0] / sums['mr_beyond_room'][0])
print '% of high risk beyond room/floor fires: {}'.format(sums['hr_beyond_room'][0] / sums['h.fire'][0])
print '% of high risk beyond structure fires: {}'.format(sums['hr_beyond_structure'][0] / sums['hr_beyond_room'][0])

#df[df['fd_id'] == 93345]
#lr_size2 = df[['lr_fire', 'lr_size_2']].apply(lambda row: row['lr_fire'] * row['lr_size_2'])


    

In [ ]:

    

cols2 = ['lr_size_2', 'mr_size_2', 'h.size2', 'lr_size_3', 'mr_size_3', 'h.size3']

df[df['fd_id'] == 93345].groupby(['fd_id', 'state']).sum()


    

In [ ]:

    

db = len(FireDepartment.objects.count())
csv = len(df.groupby(['fd_id', 'state'])[cols].sum())
(db, csv, csv/float(db))  # hit rate


    

In [ ]:

    

# Has no community fires assessment, but has deaths/injury assessment
q = """
select department_id,
    case when level = 0 then 'ALL LEVELS'
        when level = 1 then 'LOW'
        when level = 2 then 'MEDIUM'
        when level = 4 then 'HIGH'
        when level = 5 then 'UNKNOWN'
    end
from firestation_firedepartmentriskmodels
where risk_model_fires is null and (risk_model_injuries is not null or risk_model_deaths is not null)
"""
pd.read_sql_query(q, connections['default']).to_csv('/tmp/only_deaths_injuries.csv')
#pd.read_sql_query(q, connections['default'])


    

In [ ]:

    

# Has performance score, but is missing one or more community assessments
q = """
select department_id,
    case when level = 0 then 'ALL LEVELS'
        when level = 1 then 'LOW'
        when level = 2 then 'MEDIUM'
        when level = 4 then 'HIGH'
        when level = 5 then 'UNKNOWN'
    end,
    risk_model_injuries is null as injuries_empty,
    risk_model_deaths is null as deaths_empty,
    risk_model_fires is null as fires_empty,
    risk_model_fires_size1 is null as fires_size1_empty,
    risk_model_fires_size2 is null as fires_size2_empty,
    dist_model_score
from firestation_firedepartmentriskmodels
where
    dist_model_score is not null
        and (risk_model_injuries is null
            or risk_model_deaths is null
            or risk_model_fires is null
            or risk_model_fires_size1 is null
            or risk_model_fires_size2 is null)
        and level != 5
order by department_id
"""
pd.read_sql_query(q, connections['default']).to_csv('/tmp/score_but_no_assessment.csv')