def run_sd():
    sd_bus = clean_sd_bus()
    sd_add = pd.read_csv(data_dict['intermediate']['sd']['parcel'] + 'addresses.csv')

    sd_bus = merge_addresses(
            sd_bus, sd_add[sd_add['parsed_city'].isin(['san diego', 'la jolla'])], 
            fuzzy=True, nearest_n1=True,
            fuzzy_threshold=90, n1_threshold=5,
            add_merge_cols=['parsed_addr_n1', 'parsed_addr_sn', 'parsed_addr_ss'],
            bus_merge_cols=['primary_cleaned_addr_n1', 'primary_cleaned_addr_sn', 'primary_cleaned_addr_ss'],
            cols_to_merge=['lat', 'long', 'parcelID', 'parsed_city', 'parsed_addr_zip']
                                )
    sd_bus.to_csv(data_dict['intermediate']['sd']['business_location'] + '/business_location_addresses_merged.csv', 
    index=False)
    sd_bus = pd.read_csv('/home/jfish/project_data/boring_cities/data/intermediate/sd/business_location/business_location_addresses_merged.csv')
    # read in census data
    ca_shp = gpd.read_file(census_data_dict['ca bg shp'])
    con = sqlite3.connect(dataPrefix + "/data/census.db")
    census_tracts = pd.read_sql_query("SELECT GEOID10 from acs", con)
    census_tracts['GEOID10'] = census_tracts['GEOID10'].str.pad(width = 11, side="left", fillchar = '0')
    # print(census_tracts['GEOID10'].str.len().value_counts())
    sd_bus = merge_GEOID(sd_bus, ca_shp, reset=True)
    business_loc_to_sql(sd_bus, table="business_locations_flat", mode="append")
    # con = sqlite3.connect(dataPrefix + "/data/business_locations.db")
    # sd_bus = pd.read_sql_query("SELECT * from business_locations_flat", con)
    sd_bus = make_business_panel_wrapper(sd_bus, n_cores=4)
    sd_bus = make_location_vars(sd_bus)

    sd_bus.to_csv(data_dict['final']['sd']['business_location'] + '/business_location_panel.csv', index=False)
    # con = sqlite3.connect(dataPrefix + "/data/business_locations.db")
    # panel = pd.read_sql_query("SELECT * from business_locations_panel WHERE ", con)
    business_loc_to_sql(sd_bus, table="business_locations_panel", mode="append")
def run_long_beach():
    print("running long_beach")
    long_beach_bus = clean_long_beach_bus()
    # long_beach_bus = pd.read_csv(data_dict['intermediate']['long_beach']['business_location'] + '/business_location.csv')
    #df = pd.read_csv('/home/jfish/project_data/boring_cities/data/intermediate/long_beach/business_location/business_location.csv')
    print(long_beach_bus.shape[0])
    long_beach_add = pd.read_csv(data_dict['intermediate']['la']['parcel'] + 'addresses.csv')
    # long_beach_bus,long_beach_add  = misc_chi_cleaning(bus_df=long_beach_bus, 
    # add_df=long_beach_add)
    print("misc add")

    long_beach_bus = merge_addresses(
            long_beach_bus, long_beach_add[long_beach_add['parsed_city']=="long beach"], fuzzy=True, nearest_n1=True,
            fuzzy_threshold=90, n1_threshold=5,
            add_merge_cols=['parsed_addr_n1', 'parsed_addr_sn', 'parsed_addr_ss'],
            bus_merge_cols=['primary_cleaned_addr_n1', 'primary_cleaned_addr_sn', 'primary_cleaned_addr_ss'],
            cols_to_merge=['lat', 'long', 'parcelID', 'parsed_city', 'parsed_addr_zip']
                                )
    print(long_beach_bus.shape[0])
    long_beach_bus.to_csv(data_dict['intermediate']['long_beach']['business_location'] + '/business_location_addresses_merged.csv', 
    index=False)
    print("merged addresses")
    #long_beach_bus = pd.read_csv('/home/jfish/project_data/boring_cities/data/intermediate/long_beach/business_location/business_location_addresses_merged.csv')
    print(len(long_beach_bus))
    print(long_beach_bus.shape)
    if long_beach_bus.shape[0] > 150000:
        raise ValueError
    # print(long_beach_bus.shape)
    # read in census data
    ca_shp = gpd.read_file(census_data_dict['ca bg shp'])
    con = sqlite3.connect(dataPrefix + "/data/census.db")
    census_tracts = pd.read_sql_query("SELECT GEOID10 from acs", con)
    census_tracts['GEOID10'] = census_tracts['GEOID10'].str.pad(width = 11, side="left", fillchar = '0')

    # print(census_tracts['GEOID10'].str.len().value_counts())
    long_beach_bus = merge_GEOID(long_beach_bus, ca_shp, reset=True)
    business_loc_to_sql(long_beach_bus, table="business_locations_flat", mode="append")
    print("exported to int")
    #con = sqlite3.connect(dataPrefix + "/data/business_locations.db")
    #long_beach_bus = pd.read_sql_query("SELECT * from business_locations_flat WHERE source = 'long_beach'", con)
    long_beach_bus = make_business_panel_wrapper(long_beach_bus, n_cores=4)
    long_beach_bus = long_beach_bus.drop_duplicates(subset=['location_id','year'])
    long_beach_bus = make_location_vars(long_beach_bus)

    long_beach_bus.to_csv(data_dict['final']['long_beach']['business_location'] + '/business_location_panel.csv', index=False)
    # con = sqlite3.connect(dataPrefix + "/data/business_locations.db")
    # panel = pd.read_sql_query("SELECT * from business_locations_panel WHERE ", con)
    business_loc_to_sql(long_beach_bus, table="business_locations_panel", mode="append")
def run_chicago():
    # print("running chicago")
    chicago_bus = clean_chicago_bus()
    print("cleaned businesses")
    # chicago_bus = pd.read_csv(data_dict['intermediate']['chicago']['business_location'] + '/business_location.csv')
    chicago_add = pd.read_csv(data_dict['intermediate']['chicago']['parcel'] + 'addresses_concat.csv')
    chicago_add, chicago_bus  = misc_chi_cleaning(bus_df=chicago_bus, 
    add_df=chicago_add)
    print("misc add")

    chicago_bus = merge_addresses(
            chicago_bus, chicago_add, fuzzy=True, nearest_n1=True,
            fuzzy_threshold=90, n1_threshold=5,
            add_merge_cols=['parsed_addr_n1', 'parsed_addr_sn', 'parsed_addr_ss'],
            bus_merge_cols=['primary_cleaned_addr_n1', 'primary_cleaned_addr_sn', 'primary_cleaned_addr_ss'],
            cols_to_merge=['lat', 'long', 'parcelID', 'parsed_city', 'parsed_addr_zip']
                                )
    chicago_bus.to_csv(data_dict['intermediate']['chicago']['business_location'] + '/business_location_addresses_merged.csv', 
    index=False)
    print("merged addresses")
    # chicago_bus = pd.read_csv('/home/jfish/project_data/boring_cities/data/intermediate/chicago/business_location/business_location_addresses_merged.csv')
    # if chicago_bus.shape[0] > 300000:
    #     raise ValueError
    # print(chicago_bus.shape)
    # read in census data
    il_shp = gpd.read_file(census_data_dict['il bg shp'])
    con = sqlite3.connect(dataPrefix + "/data/census.db")
    census_tracts = pd.read_sql_query("SELECT GEOID10 from acs", con)
    census_tracts['GEOID10'] = census_tracts['GEOID10'].str.pad(width = 11, side="left", fillchar = '0')

    # print(census_tracts['GEOID10'].str.len().value_counts())
    chicago_bus['long_from_address'] = chicago_bus['long_from_address'].abs()
    chicago_bus['lat_from_address'] = chicago_bus['lat_from_address'].abs()
    chicago_bus = merge_GEOID(chicago_bus, il_shp, reset=True)
    business_loc_to_sql(chicago_bus, table="business_locations_flat", mode="append")
    print("exported to int")
    #con = sqlite3.connect(dataPrefix + "/data/business_locations.db")
    #chicago_bus = pd.read_sql_query("SELECT * from business_locations_flat WHERE source = 'chicago'", con)
    chicago_bus = make_business_panel_wrapper(chicago_bus, n_cores=4)
    chicago_bus = chicago_bus.drop_duplicates(subset=['location_id','year'])
    chicago_bus = make_location_vars(chicago_bus)

    chicago_bus.to_csv(data_dict['final']['chicago']['business_location'] + '/business_location_panel.csv', index=False)
    # con = sqlite3.connect(dataPrefix + "/data/business_locations.db")
    # panel = pd.read_sql_query("SELECT * from business_locations_panel WHERE ", con)
    business_loc_to_sql(chicago_bus, table="business_locations_panel", mode="append")
def run_sac():
    sac_bus = pd.read_csv(data_dict['intermediate']['sac']['business_location'] + 'business_location.csv')
    sac_bus = clean_sac_bus()
    sac_add = pd.read_csv(data_dict['raw']['sac']['parcel'] + 'Address.csv')
    sac_xwalk = pd.read_csv(data_dict['raw']['sac']['parcel'] + 'Address_parcel_xwalk.csv')
    sac_add = merge_sac_parcel_id(sac_add=sac_add, xwalk=sac_xwalk)
    sac_add= clean_sac_add(sac_add)
    sac_add.to_csv(data_dict['intermediate']['sac']['parcel'] + 'addresses_concat.csv',
     index=False)

    sac_bus = merge_addresses(
            sac_bus, sac_add, fuzzy=True, nearest_n1=True,
            fuzzy_threshold=90, n1_threshold=5,
            add_merge_cols=['parsed_addr_n1', 'parsed_addr_sn', 'parsed_addr_ss'],
            bus_merge_cols=['primary_cleaned_addr_n1', 'primary_cleaned_addr_sn', 'primary_cleaned_addr_ss'],
            cols_to_merge=['lat', 'long', 'parcelID', 'parsed_city', 'parsed_addr_zip']
                                  )
    sac_bus.to_csv(data_dict['intermediate']['sac']['business_location'] + '/business_location_addresses_merged.csv', 
    index=False)
    sac_bus = pd.read_csv(data_dict['intermediate']['sac']['business_location'] + '/business_location_addresses_merged.csv')
    sac_bus = make_business_vars_wrapper(sac_bus, n_cores=4)
    sac_bus.to_csv(data_dict['final']['sac']['business_location'] + '/business_location_flat.csv', index=False)
    sac_bus = pd.read_csv(data_dict['final']['sac']['business_location'] + '/business_location_flat.csv')
    # read in census data
    ca_shp = gpd.read_file(census_data_dict['ca bg shp'])
    con = sqlite3.connect(dataPrefix + "/data/census.db")
    census_tracts = pd.read_sql_query("SELECT GEOID10 from acs", con)
    census_tracts['GEOID10'] = census_tracts['GEOID10'].str.pad(width = 11, side="left", fillchar = '0')
    print(census_tracts['GEOID10'].str.len().value_counts())
    sac_bus = merge_GEOID(sac_bus, ca_shp, reset=True)
    business_loc_to_sql(sac_bus, table="business_locations_flat", mode="replace")
    # sac_bus = pd.read_csv(data_dict['final']['sac']['business_location'] + '/business_location_flat.csv')
    #sac_bus = pd.read_csv(data_dict['final']['sac']['business_location'] + '/business_location_panel.csv')
    sac_bus = make_business_panel_wrapper(sac_bus, n_cores=4)
    sac_bus = make_location_vars(sac_bus)


    sac_bus.to_csv(data_dict['final']['sac']['business_location'] + '/business_location_panel.csv', index=False)
    business_loc_to_sql(sac_bus, table="business_locations_panel", mode="replace")
    print(sac_bus['CT_ID_10'].str.len().value_counts())
    ###sac_bus = merge_census_tract_data(sac_bus, census_tracts, panel=True)
    print(sum(sac_bus['pop'].isna()))
    sac_bus.to_csv(data_dict['final']['sac']['business_location'] + '/business_location_panel_census_merge.csv', index = False)
    sac_bus = None
    sac_add = None
    gc.collect()
def run_baton_rouge():
    baton_rouge_bus = clean_baton_rouge_bus()
    baton_rouge_add = pd.read_csv(data_dict['intermediate']['baton_rouge']['parcel'] + 'addresses.csv')
    print(baton_rouge_add.columns)
    print(baton_rouge_bus.shape[0], '!!!!')
    baton_rouge_bus  = misc_baton_rouge_cleaning(bus_df=baton_rouge_bus, 
    add_df=baton_rouge_add)

    baton_rouge_bus = merge_addresses(
            baton_rouge_bus, baton_rouge_add, fuzzy=True, nearest_n1=True,
            fuzzy_threshold=90, n1_threshold=5,
            add_merge_cols=['parsed_addr_n1', 'parsed_addr_sn', 'parsed_addr_ss'],
            bus_merge_cols=['primary_cleaned_addr_n1', 'primary_cleaned_addr_sn', 'primary_cleaned_addr_ss'],
            cols_to_merge=['lat', 'long', 'parcelID', 'parsed_city', 'parsed_addr_zip']
                                )
    baton_rouge_bus.to_csv(data_dict['intermediate']['baton_rouge']['business_location'] + '/business_location_addresses_merged.csv', 
    index=False)
    # baton_rouge_bus = pd.read_csv('/home/jfish/project_data/boring_cities/data/intermediate/baton_rouge/business_location/business_location_addresses_merged.csv')
    # if baton_rouge_bus.shape[0] > 300000:
    #     raise ValueError
    # print(baton_rouge_bus.shape)
    # read in census data
    la_shp = gpd.read_file(census_data_dict['la bg shp'])
    con = sqlite3.connect(dataPrefix + "/data/census.db")
    census_tracts = pd.read_sql_query("SELECT GEOID10 from acs", con)
    census_tracts['GEOID10'] = census_tracts['GEOID10'].str.pad(width = 11, side="left", fillchar = '0')

    # print(census_tracts['GEOID10'].str.len().value_counts())
    baton_rouge_bus = merge_GEOID(baton_rouge_bus, la_shp, reset=True)
    business_loc_to_sql(baton_rouge_bus, table="business_locations_flat", mode="append")
    #con = sqlite3.connect(dataPrefix + "/data/business_locations.db")
    #baton_rouge_bus = pd.read_sql_query("SELECT * from business_locations_flat WHERE source = 'baton_rouge'", con)
    baton_rouge_bus = make_business_panel_wrapper(baton_rouge_bus, n_cores=4)
    baton_rouge_bus = make_location_vars(baton_rouge_bus)

    baton_rouge_bus.to_csv(data_dict['final']['baton_rouge']['business_location'] + '/business_location_panel.csv', index=False)
    # con = sqlite3.connect(dataPrefix + "/data/business_locations.db")
    # panel = pd.read_sql_query("SELECT * from business_locations_panel WHERE ", con)
    business_loc_to_sql(baton_rouge_bus, table="business_locations_panel", mode="append")