Import agate, a Python-based data analysis library
In [2]:
import agate
Create agate Table of csv file from Nebraska E911 fund data from 2001 to 2015
In [3]:
column_fix = agate.TypeTester(force={
'date': agate.Date(),
'neb_notes': agate.Text()
})
funds = agate.Table.from_csv('e911_fund.csv', column_types=column_fix)
Check out column names & types from funds table
In [4]:
print(funds)
Calculate the number of Nebraska cell phones by dividing the e911 fund by the surcharge amount
In [5]:
def calculateNebCellPhones(row):
return row['neb_balance'] / row['neb_surcharge']
cell_phones = funds.compute([
('cell_phones', agate.Formula(agate.Number(), calculateNebCellPhones))
])
Let's see the new table
In [6]:
cell_phones.print_table()
Now, I'll round those awkward cell phone estimates
In [7]:
from decimal import Decimal
def round_cell_phones(row):
return row['cell_phones'].quantize(Decimal('1'))
rounded_cell_phones = cell_phones.compute([
('cell_phones_round', agate.Formula(agate.Number(), round_cell_phones))
])
Let's see the nebraska e911 fund table
In [8]:
rounded_cell_phones.print_table()
Add the Iowa e911 fund information
In [9]:
column_names = ['date', 'ia_balance', 'ia_surcharge']
column_types = [agate.Date(), agate.Number(), agate.Number()]
iowa_phones = agate.Table.from_csv('e911_fund_iowa.csv', column_names, column_types)
In [10]:
print(iowa_phones)
Calculate IA cell phones from e911 balance and surcharge amounts
In [11]:
def calculateIACellPhones(row):
return row['ia_balance'] / row['ia_surcharge']
In [12]:
ia_cell_phones = iowa_phones.compute([
('ia_cell_phones', agate.Formula(agate.Number(), calculateIACellPhones))
])
In [13]:
ia_cell_phones.print_table()
Round those cell phone amounts
In [14]:
from decimal import Decimal
def round_cell_phones_ia(row):
return row['ia_cell_phones'].quantize(Decimal('1'))
rounded_cell_phones_ia = ia_cell_phones.compute([
('ia_cell_phones_round', agate.Formula(agate.Number(), round_cell_phones_ia))
])
In [15]:
rounded_cell_phones_ia.print_table()
Join together nebraska and iowa tables
In [16]:
total_cell_phones = rounded_cell_phones.join(rounded_cell_phones_ia, 'date', 'date')
In [17]:
total_cell_phones.print_table()
I wanted to calculate some stats by just grabing the amounts from the table
In [18]:
ia_phones_2015 = total_cell_phones.columns['ia_cell_phones_round'][0]
ia_phones_2006 = total_cell_phones.columns['ia_cell_phones_round'][9]
ia_pc_change = ( ia_phones_2015 - ia_phones_2006)/ia_phones_2006
In [19]:
print("Iowa percent change in cell phones for 2015, {}, to 2006 {} is {}".format(ia_phones_2015, ia_phones_2006, ia_pc_change))
In [20]:
neb_phones_2015 = total_cell_phones.columns['cell_phones_round'][0]
neb_phones_2006 = total_cell_phones.columns['cell_phones_round'][9]
neb_pc_change = (neb_phones_2015 - neb_phones_2006)/neb_phones_2006
In [21]:
print("Neb percent change in cell phones for 2015, {}, to 2006 {} is {}".format(neb_phones_2015, neb_phones_2006, neb_pc_change))
In [22]:
neb_phones_2001 = total_cell_phones.columns['cell_phones_round'][14]
neb_pc_change_total = (neb_phones_2015 - neb_phones_2001)/neb_phones_2001
In [23]:
print("Neb percent change in cell phones for 2015, {}, to 2001 {} is {}".format(total_cell_phones.columns['cell_phones_round'][0], total_cell_phones.columns['cell_phones_round'][14], neb_pc_change_total))
Calculate the percent cell phone numbers with previously estimated cell phones and US census pop estimates
In [24]:
neb_pop_15 = 1896190
neb_pop_06 = 1796619
ia_pop_15 = 3123899
ia_pop_06 = 2982644
In [25]:
neb_pc_cell15 = (neb_phones_2015/neb_pop_15)
neb_pc_cell06 = (neb_phones_2006/neb_pop_06)
ia_pc_cell15 = (ia_phones_2015/ia_pop_15)
ia_pc_cell06 = (ia_phones_2006/ia_pop_06)
In [26]:
print('Neb cell 15 {}, neb cell 06 {}, ia cell 15 {}, ia cell 06 {}'.format(neb_pc_cell15, neb_pc_cell06, ia_pc_cell15, ia_pc_cell06))
In [100]:
total_cell_phones.to_csv('clean_cell_phone_fund.csv')
In [27]:
json_table = total_cell_phones.select(['date', 'cell_phones_round', 'ia_cell_phones_round'])
json_table.to_json('cellphones.js')
In [ ]: