def handle_noargs(self, **options):
for year in range(2001, 2016):
source_file = '%s/energy/attf_v2_%s.csv' % (settings.LOCAL_DATA_ROOT, year)
if os.path.isfile(source_file):
data_reader = csv.reader(open(source_file))
insert_count = 0
update_count = 0
for i, row in enumerate(data_reader):
if i == 0:
year_row = row
else:
for j,col in enumerate(row):
if j == 0:
state = col.strip()
elif j > 0 and state <> '' and col.strip() <> '':
year = year_row[j]
try:
record = AlternativeFuelVehicles.objects.get(state=state,year=year)
if record.value <> clean_num(col):
record.value = clean_num(col)
record.save()
db.reset_queries()
update_count = update_count + 1
except MultipleObjectsReturned:
print '% error: multiple records exist for %s %s' % (source_file, year, state)
continue
except:
record = AlternativeFuelVehicles(year = year, state=state, value = clean_num(col))
record.save()
db.reset_queries()
insert_count = insert_count + 1
print '%s import complete. %s records updated, %s inserted' % (source_file, update_count, insert_count)
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
insert_count = 0
update_count = 0
unchanged_count = 0
for i, row in enumerate(data_reader):
if i == 0:
year_row = row;
else:
for j,col in enumerate(row):
if j == 0:
state = col.strip()
elif j > 0:
year = year_row[j]
try:
record = NewAidsCases.objects.get(state=state,year=year)
if record.value <> clean_num(col):
record.value = clean_num(col)
record.save()
db.reset_queries()
update_count = update_count + 1
else:
unchanged_count = unchanged_count + 1
except MultipleObjectsReturned:
print 'error: multiple records exist for %s %s' % (year, state)
continue
except:
record = NewAidsCases(year = year, state=state, value = clean_num(col))
record.save()
db.reset_queries()
insert_count = insert_count + 1
print '%s import complete. %s records updated, %s inserted, %s unchanged' % (SOURCE_FILE, update_count, insert_count, unchanged_count)
def load_year(year):
source_file = '%slaucnty%s.csv' % (SOURCE_FOLDER, str(year)[2:4])
try:
data_reader = csv.reader(open(source_file))
except:
print '%s file not found' % year
return
insert_count = 0
update_count = 0
for i, row in enumerate(data_reader):
if i > 0 and len(row[0]):
laus_code = row[0]
state_fips = row[1]
county_fips = row[2]
county_name = row[3]
year = row[4]
labor_force = clean_num(row[5])
employed = clean_num(row[6])
unemployed = clean_num(row[7])
unemployment_rate = clean_num(row[8])
try:
record = LaborForceCountyRaw.objects.get(
year=year,
state_fips=pad_state_ansi(state_fips),
county_fips=pad_county_ansi(county_fips))
record.labor_force = labor_force
record.employed = employed
record.unemployed = unemployed
record.unemployment_rate = unemployment_rate
update_count = update_count + 1
except MultipleObjectsReturned:
print 'error: multiple records exist for %s %s %s (%s)' % (
year, state_fips, county_fips, county_name)
continue
except:
record = LaborForceCountyRaw(
laus_code=laus_code,
state_fips=pad_state_ansi(state_fips),
county_fips=pad_county_ansi(county_fips),
county_name=county_name,
year=year,
labor_force=labor_force,
employed=employed,
unemployed=unemployed,
unemployment_rate=unemployment_rate)
insert_count = insert_count + 1
record.save()
db.reset_queries()
print '%s county labor force import complete. %s inserts and %s updates.' % (
year, insert_count, update_count)
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
update_count = 0
insert_count = 0
for i, row in enumerate(data_reader):
if i > 0:
year = row[0]
if len(year):
if row[1].lower().find('us') >= 0:
state = 'US'
else:
state = row[1]
producer_type = row[2]
energy_source = row[3]
if row[4].lower().find('-') >= 0:
co2 = 0
else:
co2 = clean_num(row[4])
if row[5].lower().find('-') >= 0:
so2 = 0
else:
so2 = clean_num(row[5])
if row[6].lower().find('- ') >= 0:
nox = 0
else:
nox = clean_num(row[6])
try:
record = ElectricEmissionsStateRaw.objects.get(
year=year,
state=state,
producer_type=producer_type,
energy_source=energy_source)
record.c02 = co2
record.s02 = s02
record.nox = nox
update_count = update_count + 1
except:
record = ElectricEmissionsStateRaw(
year=year,
state=state,
producer_type=producer_type,
energy_source=energy_source,
co2=co2,
so2=so2,
nox=nox)
insert_count = insert_count + 1
record.save()
db.reset_queries()
print 'Emissions import complete. %s records inserted, %s updated.' % (
insert_count, update_count)
def handle_noargs(self, **options):
for year in range(2001, 2015):
source_file = '%s/census.gov/income/median_household_%s.csv' % (
settings.LOCAL_DATA_ROOT, year)
try:
data_reader = csv.reader(open(source_file))
insert_count = 0
update_count = 0
error_count = 0
for i, row in enumerate(data_reader):
if i == 0:
header_row = row
elif i > 0:
if row[0].strip() <> '':
try:
state = row[2]
record, created = MedianHouseholdIncomeStateRaw.objects.get_or_create(
state=state, year=year)
record.year = year
record.state = state
if len(row[1]) == 1:
record.state_fips = '0%s' % row[1]
elif len(row[1]) == 2:
record.state_fips = row[1]
else:
record.state_fips = '00'
record.median_household_income = clean_num(
row[3])
record.median_household_income_moe = clean_num(
row[4])
record.save()
db.reset_queries()
except:
print 'Error inserting/updating record for year %s, state %s' % (
year, state)
print sys.exc_info()[1]
#traceback.print_exc(sys.exc_info()[2])
error_count = error_count + 1
continue
if created:
insert_count = insert_count + 1
else:
update_count = update_count + 1
print '%s median household income raw import complete. %s records updated, %s inserted' % (
year, update_count, insert_count)
except:
continue
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
insert_count = 0
update_count = 0
unchanged_count = 0
for i, row in enumerate(data_reader):
if i == 0:
year_row = row
else:
place = row[0].rstrip()
#hacky stuff to make 2011 dataset comply with older imports
place = place.replace('Dept.', 'Department')
place = place.replace('Dept', 'Department')
if place[0] != ' ':
state = row[0]
type = 'Total'
else:
place = place.lstrip()
type = place
for j, col in enumerate(row):
if j > 0:
year = year_row[j]
try:
record = WicBenefitsStateRaw.objects.get(
state=state, place=place, year=year)
current_value = clean_num(col)
if record.value != current_value:
record.value = current_value
record.save()
update_count = update_count + 1
else:
unchanged_count = unchanged_count + 1
except:
record = WicBenefitsStateRaw()
record.state = state
record.place = place
record.type = type
record.year = year
record.value = clean_num(col)
record.save()
insert_count = insert_count + 1
db.reset_queries()
print 'wic benefits import complete. %s inserted, %s updated, %s unchanged' % (
insert_count, update_count, unchanged_count)
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
insert_count = 0
update_count = 0
unchanged_count = 0
for i, row in enumerate(data_reader):
if i == 0:
year_row = row;
else:
place = row[0].rstrip()
#hacky stuff to make 2011 dataset comply with older imports
place = place.replace('Dept.', 'Department')
place = place.replace('Dept', 'Department')
if place[0] != ' ':
state = row[0]
type = 'Total'
else:
place = place.lstrip()
type = place
for j,col in enumerate(row):
if j > 0:
year = year_row[j]
try:
record = WicBenefitsStateRaw.objects.get(
state=state,place=place,year=year)
current_value = clean_num(col)
if record.value != current_value:
record.value = current_value
record.save()
update_count = update_count + 1
else:
unchanged_count = unchanged_count + 1
except:
record = WicBenefitsStateRaw()
record.state = state
record.place = place
record.type = type
record.year = year
record.value = clean_num(col)
record.save()
insert_count = insert_count + 1
db.reset_queries()
print 'wic benefits import complete. %s inserted, %s updated, %s unchanged' % (
insert_count, update_count, unchanged_count)
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
update_count = 0
insert_count = 0
for i, row in enumerate(data_reader):
if i > 0:
year = row[0]
if len(year):
if row[1].lower().find('us') >= 0:
state = 'US'
else:
state = row[1]
producer_type = row[2]
energy_source = row[3]
if row[4].lower().find('-') >= 0:
co2 = 0
else:
co2 = clean_num(row[4])
if row[5].lower().find('-') >= 0:
so2 = 0
else:
so2 = clean_num(row[5])
if row[6].lower().find('- ') >= 0:
nox = 0
else:
nox = clean_num(row[6])
try:
record = ElectricEmissionsStateRaw.objects.get(
year=year, state=state,
producer_type = producer_type,
energy_source = energy_source)
record.c02 = co2
record.s02 = s02
record.nox = nox
update_count = update_count + 1
except:
record = ElectricEmissionsStateRaw(year=year, state=state,
producer_type=producer_type,
energy_source=energy_source,
co2=co2, so2=so2, nox=nox)
insert_count = insert_count + 1
record.save()
db.reset_queries()
print 'Emissions import complete. %s records inserted, %s updated.' % (insert_count, update_count)
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
insert_count = 0
update_count = 0
for i, row in enumerate(data_reader):
if i == 0:
year_row = row;
else:
for j,col in enumerate(row):
if j == 0:
state = col
elif j > 0:
year = year_row[j]
try:
record = SchipEnrollmentStateRaw.objects.get(
state=state, year=year)
update_count = update_count + 1
except:
record = SchipEnrollmentStateRaw()
record.state = state
record.year = year
insert_count = insert_count + 1
record.value = clean_num(col)
record.save()
db.reset_queries()
print 'SCHIP enrollments import complete. %s inserted, %s updated' % (
insert_count, update_count)
def import_consumption():
data_reader = csv.reader(open(SOURCE_FILE))
insert_count = 0
cutoff_year = 1990
for i, row in enumerate(data_reader):
if i==0:
years = row[2:]
#if we already have data for the years in the file, delete it
for h, header in enumerate(years):
if header >= cutoff_year:
print 'deleting %s records' % header
data = EnergyConsumptionStateRaw.objects.filter(year=header)
data.delete()
else:
for j, col in enumerate(row[2:]):
year = years[j]
if year >= cutoff_year:
record = EnergyConsumptionStateRaw(
year=year,
state=row[0],
msn=row[1],
value=clean_num(col))
record.save()
db.reset_queries()
insert_count = insert_count + 1
print '%s import complete. %s records inserted' % (
SOURCE_FILE, insert_count)
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
insert_count = 0
update_count = 0
for i, row in enumerate(data_reader):
if i == 0:
year_row = row
else:
for j, col in enumerate(row):
if j == 0:
state = col
elif j > 0:
year = year_row[j]
try:
record = SchipEnrollmentStateRaw.objects.get(
state=state, year=year)
update_count = update_count + 1
except:
record = SchipEnrollmentStateRaw()
record.state = state
record.year = year
insert_count = insert_count + 1
record.value = clean_num(col)
record.save()
db.reset_queries()
print 'SCHIP enrollments import complete. %s inserted, %s updated' % (
insert_count, update_count)
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
insert_count = 0
update_count = 0
unchanged_count = 0
for i, row in enumerate(data_reader):
if i == 0:
year_row = row
else:
state = row[0]
if len(state):
for j, col in enumerate(row):
if j > 0:
try:
# if year & state already exist, update the value (previous years' data is often revised)
record = SchoolBreakfastParticipationStateRaw.objects.get(
state=state, year=int(year_row[j])
)
current_value = clean_num(col)
if record.value != current_value:
record.value = current_value
record.save()
update_count = update_count + 1
else:
unchanged_count = unchanged_count + 1
except MultipleObjectsReturned:
print "error: multiple records exist for %s %s" % (year_row[j], state)
continue
except:
# this year & state isn't in the db yet; insert
record = SchoolBreakfastParticipationStateRaw()
record.year = int(year_row[j])
record.state = state
record.value = clean_num(col)
record.save()
insert_count = insert_count + 1
db.reset_queries()
print "school breakfast participation import complete. %s inserted, %s updated, %s unchanged" % (
insert_count,
update_count,
unchanged_count,
)
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
insert_count = 0
update_count = 0
unchanged_count = 0
for i, row in enumerate(data_reader):
if i == 0:
year_row = row
else:
state = row[0]
if len(state):
for j, col in enumerate(row):
if j > 0:
try:
#if year & state already exist, update the value (previous years' data is often revised)
record = SnapMonthlyBenefitsPersonStateRaw.objects.get(
state=state, year=int(year_row[j]))
current_value = clean_num(col)
if record.value != current_value:
record.value = current_value
record.save()
update_count = update_count + 1
else:
unchanged_count = unchanged_count + 1
except MultipleObjectsReturned:
print 'error: multiple records exist for %s %s' % (
year_row[j], state)
continue
except:
#this year & state isn't in the db yet; insert
record = SnapMonthlyBenefitsPersonStateRaw()
record.year = int(year_row[j])
record.state = state
record.value = clean_num(col)
record.save()
insert_count = insert_count + 1
db.reset_queries()
print 'snap monthly benefits import complete. %s inserted, %s updated, %s unchanged' % (
insert_count, update_count, unchanged_count)
def handle_noargs(self, **options):
for year in range(2001, 2015):
source_file = '%s/census.gov/income/median_household_%s.csv' % (settings.LOCAL_DATA_ROOT, year)
try:
data_reader = csv.reader(open(source_file))
insert_count = 0
update_count = 0
error_count = 0
for i, row in enumerate(data_reader):
if i == 0:
header_row = row
elif i > 0:
if row[0].strip() <> '':
try:
state = row[2]
record, created = MedianHouseholdIncomeStateRaw.objects.get_or_create(state=state,year=year)
record.year = year
record.state = state
if len(row[1]) == 1:
record.state_fips = '0%s' % row[1]
elif len(row[1]) == 2:
record.state_fips = row[1]
else:
record.state_fips = '00'
record.median_household_income = clean_num(row[3])
record.median_household_income_moe = clean_num(row[4])
record.save()
db.reset_queries()
except:
print 'Error inserting/updating record for year %s, state %s' % (year, state)
print sys.exc_info()[1]
#traceback.print_exc(sys.exc_info()[2])
error_count = error_count + 1
continue
if created:
insert_count = insert_count + 1
else:
update_count = update_count + 1
print '%s median household income raw import complete. %s records updated, %s inserted' % (year, update_count, insert_count)
except:
continue
def load_year(year):
source_file = '%slaucnty%s.csv' % (SOURCE_FOLDER,str(year)[2:4])
try:
data_reader = csv.reader(open(source_file))
except:
print '%s file not found' % year
return
insert_count = 0
update_count = 0
for i, row in enumerate(data_reader):
if i > 0 and len(row[0]):
laus_code = row[0]
state_fips = row[1]
county_fips = row[2]
county_name = row[3]
year = row[4]
labor_force = clean_num(row[5])
employed = clean_num(row[6])
unemployed = clean_num(row[7])
unemployment_rate = clean_num(row[8])
try:
record = LaborForceCountyRaw.objects.get(year=year,state_fips=pad_state_ansi(state_fips),county_fips=pad_county_ansi(county_fips))
record.labor_force = labor_force
record.employed = employed
record.unemployed = unemployed
record.unemployment_rate = unemployment_rate
update_count = update_count + 1
except MultipleObjectsReturned:
print 'error: multiple records exist for %s %s %s (%s)' % (year, state_fips, county_fips, county_name)
continue
except:
record = LaborForceCountyRaw(laus_code=laus_code, state_fips=pad_state_ansi(state_fips),
county_fips=pad_county_ansi(county_fips),county_name=county_name, year=year,
labor_force=labor_force, employed=employed, unemployed=unemployed,
unemployment_rate=unemployment_rate)
insert_count = insert_count + 1
record.save()
db.reset_queries()
print '%s county labor force import complete. %s inserts and %s updates.' % (year,insert_count,update_count)
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
insert_count = 0
update_count = 0
unchanged_count = 0
for i, row in enumerate(data_reader):
if i == 0:
year_row = row
else:
for j, col in enumerate(row):
if j == 0:
state = col.strip()
elif j > 0:
if len(state):
year = year_row[j]
try:
record = TanfParticipationStateRaw.objects.get(
year=year, state=state)
current_value = clean_num(col)
if record.value != current_value:
record.value = current_value
record.save()
update_count = update_count + 1
else:
unchanged_count = unchanged_count + 1
except MultipleObjectsReturned:
print 'error: multiple records exist for %s %s' % (
year_row[j], state)
continue
except:
record = TanfParticipationStateRaw()
record.year = year
record.state = state
record.value = clean_num(col)
record.save()
insert_count = insert_count + 1
db.reset_queries()
print 'tanf individual participation import complete. %s inserted, %s updated, %s unchanged' % (
insert_count, update_count, unchanged_count)
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
insert_count = 0
update_count = 0
unchanged_count = 0
for i, row in enumerate(data_reader):
if i == 0:
year_row = row;
else:
for j,col in enumerate(row):
if j == 0:
state = col.strip()
elif j > 0:
if len(state):
year = year_row[j]
try:
record = TanfFamilyStateRaw.objects.get(year=year,state=state)
current_value = clean_num(col)
if record.value != current_value:
record.value = current_value
record.save()
update_count = update_count + 1
else:
unchanged_count = unchanged_count + 1
except MultipleObjectsReturned:
print 'error: multiple records exist for %s %s' % (year_row[j], state)
continue
except:
record = TanfFamilyStateRaw()
record.year = year
record.state = state
record.value = clean_num(col)
record.save()
insert_count = insert_count + 1
db.reset_queries()
print 'tanf family participation import complete. %s inserted, %s updated, %s unchanged' % (
insert_count, update_count, unchanged_count)
def handle_noargs(self, **options):
for year in range(2001, 2016):
source_file = '%s/energy/attf_v2_%s.csv' % (
settings.LOCAL_DATA_ROOT, year)
if os.path.isfile(source_file):
data_reader = csv.reader(open(source_file))
insert_count = 0
update_count = 0
for i, row in enumerate(data_reader):
if i == 0:
year_row = row
else:
for j, col in enumerate(row):
if j == 0:
state = col.strip()
elif j > 0 and state <> '' and col.strip() <> '':
year = year_row[j]
try:
record = AlternativeFuelVehicles.objects.get(
state=state, year=year)
if record.value <> clean_num(col):
record.value = clean_num(col)
record.save()
db.reset_queries()
update_count = update_count + 1
except MultipleObjectsReturned:
print '% error: multiple records exist for %s %s' % (
source_file, year, state)
continue
except:
record = AlternativeFuelVehicles(
year=year,
state=state,
value=clean_num(col))
record.save()
db.reset_queries()
insert_count = insert_count + 1
print '%s import complete. %s records updated, %s inserted' % (
source_file, update_count, insert_count)
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
insert_count = 0
update_count = 0
unchanged_count = 0
for i, row in enumerate(data_reader):
if i == 0:
year_row = row
else:
for j, col in enumerate(row):
if j == 0:
state = col.strip()
elif j > 0:
year = year_row[j]
try:
record = NewAidsCases.objects.get(state=state,
year=year)
if record.value <> clean_num(col):
record.value = clean_num(col)
record.save()
db.reset_queries()
update_count = update_count + 1
else:
unchanged_count = unchanged_count + 1
except MultipleObjectsReturned:
print 'error: multiple records exist for %s %s' % (
year, state)
continue
except:
record = NewAidsCases(year=year,
state=state,
value=clean_num(col))
record.save()
db.reset_queries()
insert_count = insert_count + 1
print '%s import complete. %s records updated, %s inserted, %s unchanged' % (
SOURCE_FILE, update_count, insert_count, unchanged_count)
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
update_count = 0
insert_count = 0
unchanged_count = 0
for i, row in enumerate(data_reader):
if i == 0:
fields = row
else:
for j, col in enumerate(row):
if fields[j].lower() == 'statecode':
state = col
elif fields[j].lower() == 'msn':
msn = col
else:
year = int(fields[j])
value = clean_num(col)
try:
record = EnergyProductionStateRaw.objects.get(
year=year, state=state, msn=msn)
if record.value <> value:
record.value = value
record.save()
db.reset_queries()
update_count = update_count + 1
else:
unchanged_count = unchanged_count + 1
except MultipleObjectsReturned:
print 'error: multiple records exist for %s %s %s' % (
year, state, msn)
continue
except:
record = EnergyProductionStateRaw(state=state,
msn=msn,
year=year,
value=value)
record.save()
db.reset_queries()
insert_count = insert_count + 1
print '%s import complete. %s records updated, %s inserted, %s unchanged' % (
SOURCE_FILE, update_count, insert_count, unchanged_count)
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
update_count = 0
insert_count = 0
unchanged_count = 0
for i, row in enumerate(data_reader):
if i==0:
fields = row
else:
for j, col in enumerate(row):
if fields[j].lower() == 'statecode':
state = col
elif fields[j].lower() == 'msn':
msn = col
else:
year = int(fields[j])
value = clean_num(col)
try:
record = EnergyProductionStateRaw.objects.get(
year=year,
state=state,
msn=msn)
if record.value <> value:
record.value = value
record.save()
db.reset_queries()
update_count = update_count + 1
else:
unchanged_count = unchanged_count + 1
except MultipleObjectsReturned:
print 'error: multiple records exist for %s %s %s' % (
year, state, msn)
continue
except:
record = EnergyProductionStateRaw(state=state,
msn=msn, year=year, value=value)
record.save()
db.reset_queries()
insert_count = insert_count + 1
print '%s import complete. %s records updated, %s inserted, %s unchanged' % (
SOURCE_FILE, update_count, insert_count, unchanged_count)
def handle_noargs(self, **options):
for year in range(2001, 2015):
source_file = '%s/education/dropouts/hs_dropouts_%s.csv' % (
settings.LOCAL_DATA_ROOT, year)
try:
data_reader = csv.reader(open(source_file))
insert_count = 0
update_count = 0
for i, row in enumerate(data_reader):
if i == 0:
year_row = row
else:
for j, col in enumerate(row):
if j == 0:
state = col
elif j > 0:
if row[0].strip() <> '':
try:
year = year_row[j]
record, created = HighSchoolDropouts.objects.get_or_create(
state=state, year=year)
record.value = clean_num(col)
record.save()
db.reset_queries()
if created:
insert_count = insert_count + 1
else:
update_count = update_count + 1
except:
print 'Error inserting/updating record for year %s, state %s' % (
year, state)
print sys.exc_info()[1]
#traceback.print_exc(sys.exc_info()[2])
continue
print '%s hs dropouts complete. %s records updated, %s inserted' % (
year, update_count, insert_count)
except:
continue
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
insert_count = 0
update_count = 0
for i, row in enumerate(data_reader):
if i == 0:
header_row = row
else:
if len(row[0]):
area_fips = row[0]
if len(area_fips) == 1:
area_fips = '0' + area_fips
year = row[2]
try:
record = LaborForceStateRaw.objects.get(
year=year, area_fips=area_fips)
update_count = update_count + 1
except MultipleObjectsReturned:
print 'error: multiple records exists for year = %s and fips = %s' % (
year, area_fips)
continue
except:
insert_count = insert_count + 1
record = LaborForceStateRaw()
record.year = year
record.area_fips = area_fips
record.area = row[1]
record.civilian_noninstitutional_pop = clean_num(row[3])
record.labor_force_total = clean_num(row[4])
record.labor_force_participation_rate = clean_num(row[5])
record.employment_total = clean_num(row[6])
record.employment_pop_rate = clean_num(row[7])
record.unemployment_total = clean_num(row[8])
record.unemployment_rate = clean_num(row[9])
record.save()
db.reset_queries()
print 'state labor force import complete. %s inserts and %s updates.' % (
insert_count, update_count)
def handle_noargs(self, **options):
for year in range(2001, 2015):
source_file = '%s/education/dropouts/hs_dropouts_%s.csv' % (settings.LOCAL_DATA_ROOT, year)
try:
data_reader = csv.reader(open(source_file))
insert_count = 0
update_count = 0
for i, row in enumerate(data_reader):
if i == 0:
year_row = row
else:
for j,col in enumerate(row):
if j == 0:
state = col
elif j > 0:
if row[0].strip() <> '':
try:
year = year_row[j]
record, created = HighSchoolDropouts.objects.get_or_create(state=state,year=year)
record.value = clean_num(col)
record.save()
db.reset_queries()
if created:
insert_count = insert_count + 1
else:
update_count = update_count + 1
except:
print 'Error inserting/updating record for year %s, state %s' % (year, state)
print sys.exc_info()[1]
#traceback.print_exc(sys.exc_info()[2])
continue
print '%s hs dropouts complete. %s records updated, %s inserted' % (year, update_count, insert_count)
except:
continue
def handle_noargs(self, **options):
data_reader = csv.reader(open(SOURCE_FILE))
insert_count = 0
update_count = 0
for i, row in enumerate(data_reader):
if i == 0:
header_row = row;
else:
if len(row[0]):
area_fips = row[0]
if len(area_fips) == 1:
area_fips = '0' + area_fips
year = row[2]
try:
record = LaborForceStateRaw.objects.get(
year = year, area_fips = area_fips)
update_count = update_count + 1
except MultipleObjectsReturned:
print 'error: multiple records exists for year = %s and fips = %s' % (year, area_fips)
continue
except:
insert_count = insert_count + 1
record = LaborForceStateRaw()
record.year = year
record.area_fips = area_fips
record.area = row[1]
record.civilian_noninstitutional_pop = clean_num(row[3])
record.labor_force_total = clean_num(row[4])
record.labor_force_participation_rate = clean_num(row[5])
record.employment_total = clean_num(row[6])
record.employment_pop_rate = clean_num(row[7])
record.unemployment_total = clean_num(row[8])
record.unemployment_rate = clean_num(row[9])
record.save()
db.reset_queries()
print 'state labor force import complete. %s inserts and %s updates.' % (insert_count,update_count)
def import_state(filename):
try:
with open(filename) as f:
#data_reader = csv.reader(f)
data_reader = csv.reader(open(file))
except IOError:
print 'cannot open file: %s' % filename
return
except:
print 'unknown file issue: %s' % filename
return
insert_count = 0
update_count = 0
for i, row in enumerate(data_reader):
if i == 0:
header_row = [x.lower() for x in row]
else:
state = pad_state(row[1])
county = pad_county(row[2])
gender = row[5]
ethnic_origin = row[6]
race = row[7]
record, created = PopulationEst10Raw.objects.get_or_create(
state=state,
county=county,
gender=gender,
ethnic_origin=ethnic_origin,
race=race)
record.sumlev = row[0]
record.stname = row[3]
record.ctyname = clean_county_name(row[4])
record.census2010pop = clean_num(row[8])
record.estimatesbase2010 = clean_num(row[9])
record.popestimate2010 = clean_num(row[10])
record.popestimate2011 = clean_num(row[11])
try:
record.popestimate2012 = clean_num(row[12])
record.popestimate2013 = clean_num(row[13])
record.popestimate2014 = clean_num(row[14])
record.popestimate2015 = clean_num(row[15])
record.popestimate2016 = clean_num(row[16])
record.popestimate2017 = clean_num(row[17])
record.popestimate2018 = clean_num(row[18])
record.popestimate2019 = clean_num(row[19])
record.census2020pop = clean_num(row[20])
record.popestimate2020 = clean_num(row[21])
except:
pass
if created:
insert_count = insert_count + 1
else:
update_count = update_count + 1
record.save()
db.reset_queries()
f.close()
print '%s: updates = %s; inserts = %s' % (filename, update_count,
insert_count)
def import_state(filename):
try:
with open(filename) as f:
#data_reader = csv.reader(f)
data_reader = csv.reader(open(file))
except IOError:
print 'cannot open file: %s' % filename
return
except:
print 'unknown file issue: %s' % filename
return
insert_count = 0
update_count = 0
for i, row in enumerate(data_reader):
if i == 0:
header_row = [x.lower() for x in row]
else:
state = pad_state(row[1])
county = pad_county(row[2])
gender = row[5]
ethnic_origin = row[6]
race = row[7]
record, created = PopulationEst10Raw.objects.get_or_create(state=state,county=county,gender=gender,ethnic_origin=ethnic_origin,race=race)
record.sumlev = row[0]
record.stname = row[3]
record.ctyname = clean_county_name(row[4])
record.census2010pop = clean_num(row[8])
record.estimatesbase2010 = clean_num(row[9])
record.popestimate2010 = clean_num(row[10])
record.popestimate2011 = clean_num(row[11])
try:
record.popestimate2012 = clean_num(row[12])
record.popestimate2013 = clean_num(row[13])
record.popestimate2014 = clean_num(row[14])
record.popestimate2015 = clean_num(row[15])
record.popestimate2016 = clean_num(row[16])
record.popestimate2017 = clean_num(row[17])
record.popestimate2018 = clean_num(row[18])
record.popestimate2019 = clean_num(row[19])
record.census2020pop = clean_num(row[20])
record.popestimate2020 = clean_num(row[21])
except:
pass
if created:
insert_count = insert_count + 1
else:
update_count = update_count + 1
record.save()
db.reset_queries()
f.close()
print '%s: updates = %s; inserts = %s' % (filename, update_count, insert_count)
def handle_noargs(self, **options):
for year in range(2005, 2015):
source_file = '%s/census.gov/housing/housing_occupancy_%s.csv' % (settings.LOCAL_DATA_ROOT, year)
insert_count = 0
update_count = 0
try:
data_reader = csv.reader(open(source_file))
except:
continue
for i, row in enumerate(data_reader):
if i == 0:
header_row = row
elif i > 0:
if row[0].strip() <> '':
state = row[2]
try:
record = HousingOccupancyStateRaw.objects.get(state=state,year=year)
update_count = update_count + 1
except:
record = HousingOccupancyStateRaw()
record.year = year
record.state = state
insert_count = insert_count + 1
if len(row[1]) == 1:
record.state_fips = '0%s' % row[1]
elif len(row[1]) == 2:
record.state_fips = row[1]
else:
record.state_fips = '00'
record.total_units = clean_num(row[3])
record.total_units_moe = clean_moe(row[4])
record.occupied_units = clean_num(row[5])
record.occupied_units_moe = clean_moe(row[6])
record.occupied_units_percent = clean_num(row[7])
record.occupied_units_percent_moe = clean_moe(row[8])
record.vacant_units = clean_num(row[9])
record.vacant_units_moe = clean_moe(row[10])
record.vacant_units_percent = clean_num(row[11])
record.vacant_units_percent_moe = clean_moe(row[12])
record.owner_vacancy_rate = clean_num(row[13])
record.owner_vacancy_rate_moe = clean_moe(row[14])
record.renter_vacancy_rate = clean_num(row[15])
record.renter_vacancy_rate_moe = clean_moe(row[16])
record.owner_occupied = clean_num(row[17])
record.owner_occupied_moe = clean_moe(row[18])
record.owner_occupied_percent = clean_num(row[19])
record.owner_occupied_percent_moe = clean_moe(row[20])
record.renter_occupied = clean_num(row[21])
record.renter_occupied_moe = clean_moe(row[22])
record.renter_occupied_percent = clean_num(row[23])
record.renter_occupied_percent_moe = clean_moe(row[24])
record.save()
print '%s household occupancy raw import complete. %s records updated, %s inserted' % (year, update_count, insert_count)
db.reset_queries()
def load_year(file, year):
data_reader = csv.reader(open(file))
total_inserts = 0
total_updates = 0
for i, row in enumerate(data_reader):
if i == 0:
header_row = row
elif row[0][0].isdigit() == False and i == 1:
#if we're working with an annotated version of the
#downloaded ACS file, ignore the 2nd row
pass
else:
state = row[2]
try:
record = HealthInsuranceStateRaw.objects.get(year=year,state=state)
total_updates = total_updates + 1
except:
record = HealthInsuranceStateRaw()
record.year = year
record.state = state
total_inserts = total_inserts + 1
record.geoid = row[0]
record.pop = clean_num(row[3])
record.pop_moe = clean_num(row[4])
record.pop_under_18 = clean_num(row[5])
record.pop_under_18_moe = clean_num(row[6])
record.pop_under_18_private = clean_num(row[7])
record.pop_under_18_private_moe = clean_num(row[8])
record.pop_under_18_public = clean_num(row[9])
record.pop_under_18_public_moe = clean_num(row[10])
record.pop_under_18_private_public = clean_num(row[11])
record.pop_under_18_private_public_moe = clean_num(row[12])
record.pop_under_18_no_ins = clean_num(row[13])
record.pop_under_18_no_ins_moe = clean_num(row[14])
record.pop_18_34 = clean_num(row[15])
record.pop_18_34_moe = clean_num(row[16])
record.pop_18_34_private = clean_num(row[17])
record.pop_18_34_private_moe = clean_num(row[18])
record.pop_18_34_public = clean_num(row[19])
record.pop_18_34_public_moe = clean_num(row[20])
record.pop_18_34_private_public = clean_num(row[21])
record.pop_18_34_private_public_moe = clean_num(row[22])
record.pop_18_34_no_ins = clean_num(row[23])
record.pop_18_34_no_ins_moe = clean_num(row[24])
record.pop_35_64 = clean_num(row[25])
record.pop_35_64_moe = clean_num(row[26])
record.pop_35_64_private = clean_num(row[27])
record.pop_35_64_private_moe = clean_num(row[28])
record.pop_35_64_public = clean_num(row[29])
record.pop_35_64_public_moe = clean_num(row[30])
record.pop_35_64_private_public = clean_num(row[31])
record.pop_35_64_private_public_moe = clean_num(row[32])
record.pop_35_64_no_ins = clean_num(row[33])
record.pop_35_64_no_ins_moe = clean_num(row[34])
record.pop_over_64 = clean_num(row[35])
record.pop_over_64_moe = clean_num(row[36])
record.pop_over_64_private = clean_num(row[37])
record.pop_over_64_private_moe = clean_num(row[38])
record.pop_over_64_public = clean_num(row[39])
record.pop_over_64_public_moe = clean_num(row[40])
record.pop_over_64_private_public = clean_num(row[41])
record.pop_over_64_private_public_moe = clean_num(row[42])
record.pop_over_64_no_ins = clean_num(row[43])
record.pop_over_64_no_ins_moe = clean_num(row[44])
record.save()
db.reset_queries()
print '%s imported. %s updated, %s inserted' % (year, total_updates, total_inserts)
def load_year(file, year):
data_reader = csv.reader(open(file))
total_inserts = 0
total_updates = 0
for i, row in enumerate(data_reader):
if i == 0:
header_row = row
elif row[0][0].isdigit() == False and i == 1:
#if we're working with an annotated version of the
#downloaded ACS file, ignore the 2nd row
pass
else:
state = row[2]
try:
record = HealthInsuranceStateRaw.objects.get(
year=year, state=state)
total_updates = total_updates + 1
except:
record = HealthInsuranceStateRaw()
record.year = year
record.state = state
total_inserts = total_inserts + 1
record.geoid = row[0]
record.pop = clean_num(row[3])
record.pop_moe = clean_num(row[4])
record.pop_under_18 = clean_num(row[5])
record.pop_under_18_moe = clean_num(row[6])
record.pop_under_18_private = clean_num(row[7])
record.pop_under_18_private_moe = clean_num(row[8])
record.pop_under_18_public = clean_num(row[9])
record.pop_under_18_public_moe = clean_num(row[10])
record.pop_under_18_private_public = clean_num(row[11])
record.pop_under_18_private_public_moe = clean_num(row[12])
record.pop_under_18_no_ins = clean_num(row[13])
record.pop_under_18_no_ins_moe = clean_num(row[14])
record.pop_18_34 = clean_num(row[15])
record.pop_18_34_moe = clean_num(row[16])
record.pop_18_34_private = clean_num(row[17])
record.pop_18_34_private_moe = clean_num(row[18])
record.pop_18_34_public = clean_num(row[19])
record.pop_18_34_public_moe = clean_num(row[20])
record.pop_18_34_private_public = clean_num(row[21])
record.pop_18_34_private_public_moe = clean_num(row[22])
record.pop_18_34_no_ins = clean_num(row[23])
record.pop_18_34_no_ins_moe = clean_num(row[24])
record.pop_35_64 = clean_num(row[25])
record.pop_35_64_moe = clean_num(row[26])
record.pop_35_64_private = clean_num(row[27])
record.pop_35_64_private_moe = clean_num(row[28])
record.pop_35_64_public = clean_num(row[29])
record.pop_35_64_public_moe = clean_num(row[30])
record.pop_35_64_private_public = clean_num(row[31])
record.pop_35_64_private_public_moe = clean_num(row[32])
record.pop_35_64_no_ins = clean_num(row[33])
record.pop_35_64_no_ins_moe = clean_num(row[34])
record.pop_over_64 = clean_num(row[35])
record.pop_over_64_moe = clean_num(row[36])
record.pop_over_64_private = clean_num(row[37])
record.pop_over_64_private_moe = clean_num(row[38])
record.pop_over_64_public = clean_num(row[39])
record.pop_over_64_public_moe = clean_num(row[40])
record.pop_over_64_private_public = clean_num(row[41])
record.pop_over_64_private_public_moe = clean_num(row[42])
record.pop_over_64_no_ins = clean_num(row[43])
record.pop_over_64_no_ins_moe = clean_num(row[44])
record.save()
db.reset_queries()
print '%s imported. %s updated, %s inserted' % (
year, total_updates, total_inserts)
def handle_noargs(self, **options):
state_fips = ''
total_inserts = 0
total_updates = 0
raw = HousingOccupancyStateRaw.objects.all().order_by('state_fips')
total_raw = raw.count()
for r in raw:
if r.state_fips != state_fips:
try:
state_ref_current = State.objects.get(state_ansi=r.state_fips)
except:
print 'Skipping record. Unable to find state: ' + r.state
continue
state_fips = r.state_fips
try:
record = HousingOccupancyState.objects.get(year=r.year,state=state_ref_current)
total_updates = total_updates + 1
except:
record = HousingOccupancyState(year = r.year, state = state_ref_current)
total_inserts = total_inserts + 1
record.total_units = r.total_units
if str(r.total_units_moe).find('*****') > -1:
record.total_units_moe = 0
else:
record.total_units_moe = clean_num(r.total_units_moe)
record.occupied_units = r.occupied_units
record.occupied_units_moe = clean_num(r.occupied_units_moe)
if r.occupied_units_percent is not None:
record.occupied_units_percent = r.occupied_units_percent
record.occupied_units_percent_moe = clean_num(r.occupied_units_percent_moe)
else:
record.occupied_units_percent = get_percent(
record.occupied_units, record.total_units)
record.occupied_units_percent_moe = get_proportion_moe(
record.occupied_units, record.total_units,
record.occupied_units_moe, record.total_units_moe) * 100
record.vacant_units = r.vacant_units
record.vacant_units_moe = clean_num(r.vacant_units_moe)
if r.vacant_units_percent is not None:
record.vacant_units_percent = r.vacant_units_percent
record.vacant_units_percent_moe = clean_num(r.vacant_units_percent_moe)
else:
record.vacant_units_percent = get_percent(
record.vacant_units, record.total_units)
record.vacant_units_percent_moe = get_proportion_moe(
record.vacant_units, record.total_units,
record.vacant_units_moe, record.total_units_moe) * 100
record.owner_vacancy_rate = r.owner_vacancy_rate
record.owner_vacancy_rate_moe = clean_num(r.owner_vacancy_rate_moe)
record.renter_vacancy_rate = r.renter_vacancy_rate
record.renter_vacancy_rate_moe = clean_num(r.renter_vacancy_rate_moe)
record.owner_occupied = r.owner_occupied
record.owner_occupied_moe = clean_num(r.owner_occupied_moe)
if r.owner_occupied_percent is not None:
record.owner_occupied_percent = r.owner_occupied_percent
record.owner_occupied_percent_moe = clean_num(r.owner_occupied_percent_moe)
else:
record.owner_occupied_percent = get_percent(
record.owner_occupied, record.occupied_units)
record.owner_occupied_percent_moe = get_proportion_moe(
record.owner_occupied, record.occupied_units,
record.owner_occupied_moe, record.occupied_units_moe) * 100
record.renter_occupied = r.renter_occupied
record.renter_occupied_moe = clean_num(r.renter_occupied_moe)
if r.renter_occupied_percent is not None:
record.renter_occupied_percent = r.renter_occupied_percent
record.renter_occupied_percent_moe = clean_num(r.renter_occupied_percent_moe)
else:
record.renter_occupied_percent = get_percent(
record.renter_occupied, record.occupied_units)
record.renter_occupied_percent_moe = get_proportion_moe(
record.renter_occupied, record.occupied_units,
record.renter_occupied_moe, record.occupied_units_moe) * 100
record.save()
db.reset_queries()
print 'Housing Occupancy (state): total records from raw data = ' + str(total_raw)
print 'Housing Occupancy (state): total inserts = ' + str(total_inserts)
print 'Housing Occupancy (state): total updates = ' + str(total_updates)
def handle_noargs(self, **options):
state_fips = ''
total_inserts = 0
total_updates = 0
raw = HousingOccupancyStateRaw.objects.all().order_by('state_fips')
total_raw = raw.count()
for r in raw:
if r.state_fips != state_fips:
try:
state_ref_current = State.objects.get(
state_ansi=r.state_fips)
except:
print 'Skipping record. Unable to find state: ' + r.state
continue
state_fips = r.state_fips
try:
record = HousingOccupancyState.objects.get(
year=r.year, state=state_ref_current)
total_updates = total_updates + 1
except:
record = HousingOccupancyState(year=r.year,
state=state_ref_current)
total_inserts = total_inserts + 1
record.total_units = r.total_units
if str(r.total_units_moe).find('*****') > -1:
record.total_units_moe = 0
else:
record.total_units_moe = clean_num(r.total_units_moe)
record.occupied_units = r.occupied_units
record.occupied_units_moe = clean_num(r.occupied_units_moe)
if r.occupied_units_percent is not None:
record.occupied_units_percent = r.occupied_units_percent
record.occupied_units_percent_moe = clean_num(
r.occupied_units_percent_moe)
else:
record.occupied_units_percent = get_percent(
record.occupied_units, record.total_units)
record.occupied_units_percent_moe = get_proportion_moe(
record.occupied_units, record.total_units,
record.occupied_units_moe, record.total_units_moe) * 100
record.vacant_units = r.vacant_units
record.vacant_units_moe = clean_num(r.vacant_units_moe)
if r.vacant_units_percent is not None:
record.vacant_units_percent = r.vacant_units_percent
record.vacant_units_percent_moe = clean_num(
r.vacant_units_percent_moe)
else:
record.vacant_units_percent = get_percent(
record.vacant_units, record.total_units)
record.vacant_units_percent_moe = get_proportion_moe(
record.vacant_units, record.total_units,
record.vacant_units_moe, record.total_units_moe) * 100
record.owner_vacancy_rate = r.owner_vacancy_rate
record.owner_vacancy_rate_moe = clean_num(r.owner_vacancy_rate_moe)
record.renter_vacancy_rate = r.renter_vacancy_rate
record.renter_vacancy_rate_moe = clean_num(
r.renter_vacancy_rate_moe)
record.owner_occupied = r.owner_occupied
record.owner_occupied_moe = clean_num(r.owner_occupied_moe)
if r.owner_occupied_percent is not None:
record.owner_occupied_percent = r.owner_occupied_percent
record.owner_occupied_percent_moe = clean_num(
r.owner_occupied_percent_moe)
else:
record.owner_occupied_percent = get_percent(
record.owner_occupied, record.occupied_units)
record.owner_occupied_percent_moe = get_proportion_moe(
record.owner_occupied, record.occupied_units,
record.owner_occupied_moe, record.occupied_units_moe) * 100
record.renter_occupied = r.renter_occupied
record.renter_occupied_moe = clean_num(r.renter_occupied_moe)
if r.renter_occupied_percent is not None:
record.renter_occupied_percent = r.renter_occupied_percent
record.renter_occupied_percent_moe = clean_num(
r.renter_occupied_percent_moe)
else:
record.renter_occupied_percent = get_percent(
record.renter_occupied, record.occupied_units)
record.renter_occupied_percent_moe = get_proportion_moe(
record.renter_occupied, record.occupied_units,
record.renter_occupied_moe,
record.occupied_units_moe) * 100
record.save()
db.reset_queries()
print 'Housing Occupancy (state): total records from raw data = ' + str(
total_raw)
print 'Housing Occupancy (state): total inserts = ' + str(
total_inserts)
print 'Housing Occupancy (state): total updates = ' + str(
total_updates)
