Permalink
Switch branches/tags
Nothing to show
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
146 lines (88 sloc) 8.25 KB
import json
import os
import csv
import copy
import pandas as pd
csv_paths = []
data_src_dir = 'qgis_counts'
for file in os.listdir(data_src_dir):
if file.endswith(".csv"):
csv_paths.append(str(os.path.join(data_src_dir, file)))
all_dist_data = {}
for path in csv_paths:
if "dod" not in path:
dists = []
with open(path, "r", encoding="utf-8") as ofile:
dists = [{k: v for k, v in row.items()}
for row in csv.DictReader(ofile, skipinitialspace=True)]
ofile.close()
for dist in dists:
if dist["GEOID"] not in all_dist_data:
all_dist_data[dist["GEOID"]] = copy.deepcopy(dist)
else:
count_col_name = list(dist.keys())[-1]
all_dist_data[dist["GEOID"]][count_col_name.replace("NUMPOINTS", "")] = dist[count_col_name]
# BEGIN MERGING WINNERS
winners_dict = {}
fips_state_xwalk = {}
with open("orig_data/winners_18.json", "r") as ofile:
winners_dict = json.loads(ofile.read())
ofile.close()
with open("orig_data/fips_crosswalk.json", "r") as ofile:
fips_state_xwalk = json.loads(ofile.read())
ofile.close()
for key in winners_dict:
race_key = copy.copy(key)
if "-AL" in race_key:
race_key = race_key.replace("-AL", "00")
state_abbrv = race_key[0:2]
race_key = race_key.replace(state_abbrv, fips_state_xwalk[state_abbrv])
race_key = race_key.replace("-", "")
race_key = race_key.replace("*", "")
all_dist_data[race_key]["result"] = winners_dict[key]
# turn dict into list of dicts
all_dist_data_list = []
for key in all_dist_data:
all_dist_data_list.append(all_dist_data[key])
all_dist_data_list_filtered = []
for dist in all_dist_data_list:
if dist["GEOID"][-2:] == "98" or dist["GEOID"][-2:] == "ZZ":
continue
else:
all_dist_data_list_filtered.append(dist)
for dist in all_dist_data_list_filtered:
dist["farms"] = 0
# add in farms data
farm_data = {"3103": 35850, "2707": 33431, "1904": 33422, "4601": 31989, "3801": 30961, "2001": 30489, "4003": 30326, "4002": 29692, "3001": 28008, "2906": 27780, "2904": 23699, "4804": 23397, "2101": 22897, "1901": 22478, "1715": 22203, "4811": 21093, "1902": 20546, "4813": 20372, "5503": 19895, "2002": 19808, "2102": 19734, "2908": 19222, "2701": 19041, "5507": 16175, "4817": 16124, "0804": 15949, "4819": 15781, "0504": 15199, "4810": 14453, "0501": 14422, "0803": 13954, "1718": 13826, "4706": 13698, "0401": 13505, "1602": 13412, "5109": 13389, "3101": 13381, "4825": 13361, "4102": 13284, "4004": 13283, "4707": 13249, "2907": 12894, "3503": 12723, "4805": 12498, "0104": 12450, "4827": 12248, "1903": 12191, "5105": 12069, "4801": 12033, "1803": 11801, "3906": 11766, "5601": 11736, "2106": 11655, "2903": 11580, "2104": 11411, "1601": 11404, "2105": 11144, "2205": 11106, "2801": 10989, "3905": 10792, "4704": 10696, "2803": 10656, "2708": 10586, "1806": 10531, "1716": 10468, "2004": 10463, "2604": 10328, "2802": 10256, "5304": 10221, "4210": 10157, "3502": 10130, "5502": 9962, "4701": 9853, "0503": 9735, "1717": 9730, "1808": 9529, "3907": 9520, "4823": 9377, "3904": 9348, "4808": 9297, "0601": 8970, "0102": 8890, "5506": 8882, "1203": 8862, "4104": 8820, "3623": 8716, "4815": 8704, "5508": 8674, "2607": 8650, "1804": 8570, "5401": 8458, "4834": 8326, "5106": 8195, "4828": 7794, "1713": 7760, "4821": 7738, "5305": 7733, "4209": 7608, "5402": 7563, "1802": 7547, "2204": 7545, "4708": 7529, "3705": 7523, "5001": 7338, "4901": 7334, "4205": 7119, "1309": 7080, "2601": 7028, "1712": 6959, "4836": 6888, "3915": 6879, "1502": 6709, "1809": 6638, "4503": 6630, "1217": 6479, "3711": 6428, "1308": 6419, "0603": 6350, "2610": 6328, "0105": 6283, "2804": 6175, "3621": 6108, "3902": 6107, "4105": 6076, "2606": 6072, "3908": 6055, "3706": 6051, "1312": 6035, "2706": 5739, "0502": 5715, "5303": 5698, "4703": 5665, "1310": 5566, "1302": 5559, "2302": 5523, "4203": 5489, "5403": 5468, "4812": 5466, "2401": 5451, "2203": 5363, "3707": 5330, "3619": 5314, "0621": 5300, "0604": 5257, "0103": 5244, "4702": 5214, "4211": 5191, "4806": 5164, "3708": 5065, "4216": 5054, "0602": 5047, "4831": 4996, "1211": 4978, "4902": 4911, "4505": 4848, "3622": 4770, "0610": 4768, "4101": 4759, "4506": 4690, "3627": 4674, "2702": 4642, "0107": 4556, "0624": 4487, "2603": 4328, "3702": 4319, "3912": 4307, "0650": 4302, "1314": 4184, "1202": 4105, "3710": 4082, "0616": 4025, "5308": 3967, "2602": 3913, "4814": 3818, "0622": 3817, "4005": 3784, "2905": 3727, "5301": 3651, "4903": 3651, "0605": 3592, "4507": 3484, "1303": 3475, "4204": 3470, "5505": 3462, "3701": 3457, "0623": 3373, "4218": 3340, "4826": 3302, "5107": 3265, "0609": 3238, "1201": 3181, "4001": 3160, "4502": 3158, "3703": 3115, "0802": 3055, "3914": 3053, "0101": 3041, "5104": 3003, "3202": 2991, "2605": 2837, "3916": 2820, "3302": 2815, "5110": 2775, "0902": 2762, "0106": 2759, "3624": 2712, "1805": 2682, "5501": 2667, "4215": 2654, "2301": 2650, "5306": 2533, "4207": 2521, "0805": 2520, "4103": 2500, "1001": 2451, "3402": 2409, "1226": 2382, "2406": 2360, "5101": 2338, "0620": 2323, "4212": 2312, "2206": 2289, "3713": 2246, "3407": 2154, "1215": 2141, "1714": 2132, "4904": 2131, "1301": 2069, "2608": 2057, "2501": 2043, "1210": 2017, "4822": 1965, "4705": 1918, "4504": 1912, "3910": 1898, "0626": 1885, "3501": 1868, "0404": 1851, "2405": 1850, "2502": 1832, "4206": 1821, "1206": 1811, "5302": 1660, "3301": 1576, "3405": 1571, "2408": 1558, "5310": 1475, "0403": 1467, "0905": 1462, "0402": 1452, "1205": 1419, "4217": 1388, "2201": 1386, "2509": 1349, "1218": 1341, "0642": 1314, "1212": 1298, "4803": 1239, "1801": 1212, "1702": 1204, "3901": 1200, "3620": 1181, "0636": 1148, "4835": 1133, "1208": 1122, "1204": 1114, "3204": 1074, "3709": 1068, "3704": 1051, "4208": 1048, "3913": 1038, "0608": 1016, "2003": 1013, "1311": 936, "2503": 936, "0649": 935, "3404": 906, "4402": 899, "3618": 871, "3403": 860, "0901": 826, "0607": 814, "0201": 762, "2504": 750, "3102": 738, "0619": 736, "0651": 721, "2703": 699, "1225": 695, "1220": 674, "1216": 670, "2407": 604, "0903": 579, "1209": 578, "5102": 567, "0405": 559, "1221": 554, "4501": 544, "0618": 523, "3909": 506, "0625": 505, "3601": 499, "3712": 483, "1219": 480, "1207": 473, "2506": 464, "0406": 459, "0408": 455, "1227": 442, "1223": 432, "0641": 426, "3412": 416, "2202": 404, "0615": 395, "4830": 387, "2612": 379, "2704": 372, "4816": 370, "5103": 369, "3625": 364, "0806": 357, "3401": 357, "0904": 348, "4401": 344, "1313": 342, "0611": 327, "0807": 321, "1501": 291, "4802": 277, "3411": 264, "1304": 248, "1214": 232, "4709": 228, "0652": 227, "2103": 223, "4820": 221, "4832": 220, "2902": 209, "1307": 209, "2403": 204, "5307": 196, "2505": 196, "0635": 195, "2611": 191, "1701": 191, "1706": 187, "1807": 185, "0631": 181, "0614": 180, "0407": 173, "0606": 171, "2508": 162, "4824": 158, "0627": 154, "4807": 145, "1710": 141, "4809": 138, "0633": 133, "0628": 133, "1222": 133, "1711": 131, "3406": 122, "4818": 119, "0639": 115, "5309": 115, "2402": 115, "2404": 114, "0645": 114, "3603": 112, "4833": 107, "1306": 105, "4829": 100, "1703": 97, "3903": 92, "1213": 87, "0653": 84, "0409": 84, "3911": 81, "0617": 74, "3617": 70, "3203": 66, "2901": 60, "4213": 60, "0629": 59, "3626": 58, "0632": 58, "0644": 56, "4214": 52, "0630": 51, "0643": 46, "2613": 44, "0647": 43, "0646": 41, "1224": 40, "3602": 39, "5504": 37, "0648": 37, "5108": 33, "0613": 32, "2705": 32, "1305": 30, "0638": 30, "5111": 27, "2609": 26, "0801": 24, "0637": 23, "2507": 23, "1705": 21, "1708": 21, "4202": 17, "0640": 16, "2614": 13, "3409": 11, "3604": 10, "3616": 10}
def convert_from_zerozero(fipsid):
if fipsid[2:] == "00":
return fipsid[0:2] + "01"
else:
return fipsid
for key in farm_data:
matching_dist = [x for x in all_dist_data_list_filtered if convert_from_zerozero(x["GEOID"]) == key]
try:
matching_dist[0]["farms"] = farm_data[key]
except:
pass
df = pd.DataFrame(all_dist_data_list_filtered)
data_cols = ["amtrak" ,
"breweries" ,
"fairgrounds" ,
"fortune" ,
"hospitals" ,
"mobilehome" ,
"museums" ,
"sportsvenues",
"starbucks" ,
"farms" ,
"military"]
df['military'].fillna(0).astype(int)
# print(df)
for col in data_cols:
print(col)
df[col] = df[col].astype("int")
df[col + "_alpha_pct"] = (df[col] / max(list(df[col]))) * 255
df.to_csv("all_dist_counts.csv", index=0)
# save file with counts of indicators per result
df.groupby("result").sum().reset_index().to_csv("result_counts.csv", index=0)