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)