<jupyter_start><jupyter_text>1. Postive Covid-19 Test Rate<jupyter_code>import pandas as pd
import numpy as np


def get_fips(county_id):
    State, county = county_id.split('-')
    state_id = State_to_FIPS[State]
    return state_id+ county


# BroadBoardSpeedMobile#[cols]
def modifyGEOID(v):
    if type(v) == int:
        v = str(v)
    if len(v) == 4:
        v = '0' + v
    return v


string  = '''Alabama	AL	01
Alaska	AK	02
Arizona	AZ	04
Arkansas	AR	05
California	CA	06
Colorado	CO	08
Connecticut	CT	09
Delaware	DE	10
Florida	FL	12
Georgia	GA	13
Hawaii	HI	15
Idaho	ID	16
Illinois	IL	17
Indiana	IN	18
Iowa	IA	19
Kansas	KS	20
Kentucky	KY	21
Louisiana	LA	22
Maine	ME	23
Maryland	MD	24
Massachusetts	MA	25
Michigan	MI	26
Minnesota	MN	27
Mississippi	MS	28
Missouri	MO	29
Montana	MT	30
Nebraska	NE	31
Nevada	NV	32
New Hampshire	NH	33
New Jersey	NJ	34
New Mexico	NM	35
New York	NY	36
North Carolina	NC	37
North Dakota	ND	38
Ohio	OH	39
Oklahoma	OK	40
Oregon	OR	41
Pennsylvania	PA	42
Rhode Island	RI	44
South Carolina	SC	45
South Dakota	SD	46
Tennessee	TN	47
Texas	TX	48
Utah	UT	49
Vermont	VT	50
Virginia	VA	51
Washington	WA	53
West Virginia	WV	54
Wisconsin	WI	55
Wyoming	WY	56
American Samoa	AS	60
Guam	GU	66
Northern Mariana Islands	MP	69
Puerto Rico	PR	72
Virgin Islands	VI	78
WashtingtonDC	DC	11
Palau	PW	00'''

# State_to_FIPS = {k:v for k, v in [i.split('\t')[1:] for i in string.split('\n')]}
# State_to_FIPS
State_to_ABBR = {k:v for k, v in [i.split('\t')[:2] for i in string.split('\n')]}
State_to_ABBR
import pandas as pd

# url = 'https://covid.cdc.gov/covid-data-tracker/COVIDData/getAjaxData?id=integrated_county_latest_external_data'
Available_States = {
 'California': 'CA',
 'Illinois': 'IL',
 'Indiana': 'IN',
 'Maine': 'ME',
 'New Jersey': 'NJ',
 'New York': 'NY',
 'North Carolina': 'NC',
 'Ohio': 'OH',
 'Oregon': 'OR',
 'Pennsylvania': 'PA',
 'South Carolina': 'SC',
 'Tennessee': 'TN',
 'Texas': 'TX',
 'Virginia': 'VA',
 'West Virginia': 'WV',
 'Wisconsin': 'WI',
 }

# len(Available_States)

Available_States_Links = {}

for state, state_abbr in Available_States.items():
    url = 'https://covid.cdc.gov/covid-data-tracker/COVIDData/getAjaxData?id=integrated_county_timeseries_state_' 
    Available_States_Links[state] = url + state_abbr + '_external'

    
Available_States_Links

from datetime import datetime


def get_data_from_url(url):
    
    Data = pd.read_json(url)
    L = []
    for idx, row in Data.iterrows():
        d = row.to_dict()
        data = d['integrated_county_timeseries_external_data']
        L.append(data)

    Data = pd.DataFrame(L)
    return Data


url = Available_States_Links['New Jersey']
Data = get_data_from_url(url)
Data
# max(Data['date'])
Data[Data['date'] == '2021-03-27']

date = '2021-03-27'
# url = Available_States_Links['New Jersey']
L = []
for state, url in Available_States_Links.items():
    print(datetime.now(), '\t', state,)
    NewReportRate = get_data_from_url(url)
    NewReportRate = NewReportRate[NewReportRate['date'] == date]
    NewReportRate = NewReportRate[['fips_code', 'date', 'state', 'county', 'percent_new_test_results_reported_positive_7_day_rolling_average']]
    NewReportRate = NewReportRate.reset_index(drop = True)
    print(datetime.now(), '\t', NewReportRate.shape)
    
    L.append(NewReportRate)
    

NewCaseRate = pd.concat(L)
NewCaseRate
NewCaseRate
# NewCaseRate.describe()
NewCaseRate = NewCaseRate.rename(columns = {'fips_code': 'GEOID'}) # ['GEOID'] = NewCaseRate['fips_code'].apply(get_fips).apply(modifyGEOID)
NewCaseRate.to_csv('DataMerge/1-NewCaseRate-2021-03-27.csv', index = False)
NewCaseRate<jupyter_output><empty_output><jupyter_text>A. Racial Bias<jupyter_code>path = 'DataMerge/Data/AB-Pysch/county_means_2016.csv'

RaceBias = pd.read_csv(path)
RaceBias['GEOID'] = RaceBias['county_id'].apply(get_fips).apply(modifyGEOID)
RaceBias = RaceBias.sort_values(by = 'GEOID').reset_index(drop = True)

cols = ['GEOID',  'bias', 'explicit_diff', 'weighted_bias', 'weighted_explicit_diff', ]
RaceBias = RaceBias[cols]
RaceBias.columns = ['GEOID', 'racial_bias', 'racial_explicit_bias', 'racial_weighted_bias', 'racial_weighted_explicit_bias', ]
RaceBias.to_csv('DataMerge/A-RaceBias.csv', index = False)
RaceBias.head()<jupyter_output><empty_output><jupyter_text>B. Sex Bias<jupyter_code>import pandas as pd
import numpy as np

path = 'DataMerge/Data/AB-Pysch/county_means_sexuality_2016.csv'
SexBias = pd.read_csv(path)

SexBias['GEOID'] = SexBias['county_id'].apply(get_fips).apply(modifyGEOID)
SexBias = SexBias.sort_values(by = 'GEOID').reset_index(drop = True)


cols = ['GEOID',  'bias', 'explicit', 'weighted_bias', 'weighted_explicit', ]
SexBias = SexBias[cols]

SexBias.columns = ['GEOID',  'sex_bias', 'sex_explicit_bias', 'sex_weighted_bias', 'sex_weighted_explicit_bias', ]
SexBias.to_csv('DataMerge/B-SexBias.csv', index = False)

SexBias.head()<jupyter_output><empty_output><jupyter_text>C. Age (*Disparity)<jupyter_code>import pandas as pd
import numpy as np

def get_county_id(v):
    v = str(v)
    if len(v) == 1:
        return '00' + str(v)
    elif len(v) == 2:
        return '0' + str(v)
    elif len(v) == 3:
        return v
    else:
        raise ValueError('Error')
    
def get_state_id(v):
    v = str(v)
    if len(v) == 1:
        return '0' + str(v)
    elif len(v) == 2:
        return v
    else:
        raise ValueError('Error')
    
def remove_string(s):
    return s.replace(' County', '')

def get_county(s):
    return s.split(', ')[0]

def get_state(s):
    return s.split(', ')[1]

def getGEOID(idx):
    return idx.split('US')[-1]


def change_col_name(name):
    d = {}
    
    d['TOT_POP'] = 'WholeNum'
    d['WA'] = 'WhiteNum'
    d['BA'] = 'BlackNum'
    d['NHWA'] = 'NHWhiteNum'
    d['NHBA'] = 'NHBlackNum'
    
    for k, v in d.items():
        if k in name:
            name = name.replace(k, v)
    
    return name

def get_Age_Race_County(path_cc, ageidx = 0):
    

    CCALLDATA = pd.read_csv(path_cc)

    CCALLDATA = CCALLDATA[CCALLDATA['YEAR'] == 12]  # 12: 2019-7-1
    CCALLDATA = CCALLDATA[CCALLDATA['AGEGRP'] == ageidx] #  0: Age Group Total
    CCALLDATA = CCALLDATA[CCALLDATA['SUMLEV'] == 50] #  50: County and/or Statistical Equivalent
    CCALLDATA = CCALLDATA.reset_index(drop = True)



    CCALLDATA['GEOID'] = CCALLDATA['STATE'].apply(get_state_id) + CCALLDATA['COUNTY'].apply(get_county_id)
    # POPULATION

    PrefixCols = ['GEOID', 'TOT_POP'] 
    RaceCols = ['WA', 'BA', 'NHWA', 'NHBA']

    for i in RaceCols:

        CCALLDATA[i] = CCALLDATA[i + '_MALE'] + CCALLDATA[i +'_FEMALE']



    CCALLDATA = CCALLDATA[PrefixCols + RaceCols]
    
    # return US_COUNTY_POPULATION
    return CCALLDATA



path_cc = 'DataMerge/Data/C-AnnualCountyPopulation/cc-est2019-alldata.csv'

df0 = get_Age_Race_County(path_cc, ageidx = 0)
df0

print(0, df0.shape)
for ageidx in list(range(1,19)):
    
    df = get_Age_Race_County(path_cc, ageidx = ageidx)
    new_names = dict(zip(['TOT_POP', 'WA', 'BA', 'NHWA', 'NHBA'], [i+'_' + str(ageidx) for i in ['TOT_POP', 'WA', 'BA',  'NHWA', 'NHBA']]))
    df = df.rename(columns = new_names)
    print(ageidx, df.shape)
    df0 = pd.merge(df0, df, on ='GEOID')
    
    
df0.columns
list(range(1, 19))

age_groups = {
    'Above75': [16, 17, 18],
    'Above65': [14, 15, 16, 17, 18],
    # 'Below14': [1, 2, 3],
    # 'Below19': [1, 2, 3, 4],
    # 'Below24': [1, 2, 3, 4, 5],
    # 'Below29': [1, 2, 3, 4, 5, 6],
    # 'Below35': [1, 2, 3, 4, 5, 6, 7],
    'Above15': [4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18], 
    'Above20': [5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18],
    'A20T74': [5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, ],
    'A15T74': [4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, ], 
}


# def get_age_list_rate(age_idx_list):

for name, age_idx_list in age_groups.items():
    range_dict = {}
    range_dict['WA'] = ['WA_' + str(ageidx) for ageidx in age_idx_list] # Total White People in the corresponding age period
    range_dict['BA'] = ['BA_' + str(ageidx) for ageidx in age_idx_list] # Total Black People in the corresponding age period
    range_dict['NHWA'] = ['NHWA_' + str(ageidx) for ageidx in age_idx_list] # Total White People in the corresponding age period
    range_dict['NHBA'] = ['NHBA_' + str(ageidx) for ageidx in age_idx_list] # Total Black People in the corresponding age period
    
    range_dict['TOT_POP'] = ['TOT_POP_' + str(ageidx) for ageidx in age_idx_list] # # Total Popluation in the corresponding age period
    
    for item, item_list in range_dict.items():
        df0[name + '_' + item] = df0[item_list].sum(axis = 1)
        # print(name + '_' + item)
    
    
    # df0[name + '_WhiteRate'] = df0[name + '_WA'] / df0['WA']  
    # df0[name + '_BlackRate'] = df0[name + '_BA'] / df0['BA']
    # df0[name + '_WholeRate'] = df0[name + '_TOT_POP'] / df0['TOT_POP']

    # df0[name + '_Diff']      = df0[name + '_WhiteRate'] - df0[name + '_BlackRate']
    # df0[name + '_Avg']       = (df0[name + '_WA']   + df0[name + '_BA']) / (df0['WA'] + df0['BA'])
    # df0[name + '_Disparity'] = df0[name + '_Diff'] / df0[name + '_Avg']
    
    
    # Which WhiteNumToUse
    df0[name + '_WholeNum'] = df0[name + '_TOT_POP']
    
    df0[name + '_WhiteNum'] = df0[name + '_WA']
    df0[name + '_NHWhiteNum'] = df0[name + '_NHWA']
    
    df0[name + '_BlackNum'] = df0[name + '_BA']
    df0[name + '_NHBlackNum'] = df0[name + '_NHBA']
    
    
    df0[name + '_WholeRate'] = df0[name + '_TOT_POP'] / df0['TOT_POP'] * 100
    df0[name + '_WhiteRate'] = df0[name + '_WA'] / df0['WA'] * 100
    df0[name + '_NHWhiteRate'] = df0[name + '_NHWA'] / df0['NHWA']  * 100
    df0[name + '_BlackRate'] = df0[name + '_BA'] / df0['BA'] * 100
    df0[name + '_NHBlackRate'] = df0[name + '_NHBA'] / df0['NHBA'] * 100
    

cols = ['GEOID'] +  [i for i in df0.columns if 'Rate' in i or 'Num' in i]
AgeDisparity = df0[cols]
AgeDisparity

AgeDisparity.columns
AgeDisparity.to_csv('DataMerge/C-AgeDisparity.csv', index = False)<jupyter_output><empty_output><jupyter_text>D. Mean Income (*Disparity)<jupyter_code># Per Capita Average Income

import pandas as pd
import numpy as np

path = 'DataMerge/Data/DE-INCOME/MeanIncome/ACSST5Y2019.S1902_2021-03-28T123828/ACSST5Y2019.S1902_data_with_overlays_2021-03-28T123801.csv'
DATA = pd.read_csv(path, skiprows=[0], low_memory=False)

cols = list(DATA.columns)
cols = [i for i in cols if 'Margin of Error!' not in i]
DATA = DATA[cols]

print(DATA.shape)
new_names = []

for t in [i.split('!!') for i in list(DATA.columns)]:
    if len(t) <= 2:
        name = t[0]
    else:
        name = t[1] +'_' + t[-1]
    new_names.append(name)
    

new_names_dict = dict(zip(list(DATA.columns), new_names))
# [v for k, v in new_names_dict.items()]



def remove_string(s):
    return s.replace(' County', '')

def get_county(s):
    return s.split(', ')[0]

def get_state(s):
    return s.split(', ')[1]


DATA = DATA.rename(columns = new_names_dict)

DATA['Geographic Area Name'] = DATA['Geographic Area Name'].apply(remove_string)
DATA['County'] = DATA['Geographic Area Name'].apply(get_county)
DATA['State']  = DATA['Geographic Area Name'].apply(get_state)

def getGEOID(idx):
    return idx.split('US')[-1]

cols = ['GEOID', 'Mean income (dollars)_Total population',
 'Mean income (dollars)_White',
 'Mean income (dollars)_White alone, not Hispanic or Latino',
 'Mean income (dollars)_Black or African American',]

DATA['GEOID'] = DATA['id'].apply(getGEOID)

DATA = DATA[cols]
DATA = DATA.replace(['-', 'N'], np.nan)
for col in cols[1:]:
    print(col)
    DATA[col] = DATA[col].astype(float)

MeanIncome = DATA
MeanIncome.columns = ['GEOID', 'MeanInc_Whole', 'MeanInc_White', 'MeanInc_NHWhite',  'MeanInc_Black']

# MeanIncome['MeanInc_Disparity'] = MeanIncome['MeanInc_White'] - MeanIncome['MeanInc_Black']
MeanIncome.to_csv('DataMerge/D-MeanIncome.csv', index = False)
MeanIncome.head()<jupyter_output>Mean income (dollars)_Total population
Mean income (dollars)_White
Mean income (dollars)_White alone, not Hispanic or Latino
Mean income (dollars)_Black or African American<jupyter_text>E. Median Income (*Disparity)<jupyter_code># Househod Median Income
import pandas as pd
import numpy as np

path = 'DataMerge/Data/DE-INCOME/MedianIncome/ACSST5Y2019.S1903_2021-03-28T121313/ACSST5Y2019.S1903_data_with_overlays_2021-03-28T121240.csv'
DATA = pd.read_csv(path, skiprows=[0], low_memory=False)
# How to understand total population in household?

cols = list(DATA.columns)
cols = [i for i in cols if 'Margin of Error!' not in i]
DATA = DATA[cols]
# [i.split('!!')[-1] for i in list(INTERNET_SUBSCRIPTIONS.columns)]

print(DATA.shape)

new_names = []

for t in [i.split('!!') for i in list(DATA.columns)]:
    # print(t)
    
    if len(t) <= 2:
        name = t[0]
    else:
        name = t[1] +'_' + t[-1]
    
    # print(name)
    
    new_names.append(name)
    
new_names_dict = dict(zip(list(DATA.columns), new_names))

# [v for k,v in new_names_dict.items()]


# new_names_dict

def remove_string(s):
    return s.replace(' County', '')

def get_county(s):
    return s.split(', ')[0]

def get_state(s):
    return s.split(', ')[1]



DATA = DATA.rename(columns = new_names_dict)

DATA['Geographic Area Name'] = DATA['Geographic Area Name'].apply(remove_string)
DATA['County'] = DATA['Geographic Area Name'].apply(get_county)
DATA['State']  = DATA['Geographic Area Name'].apply(get_state)

def getGEOID(idx):
    return idx.split('US')[-1]


cols = ['GEOID', 'Median income (dollars)_Households',
 'Median income (dollars)_White',
 'Median income (dollars)_White alone, not Hispanic or Latino',
 'Median income (dollars)_Black or African American',]


DATA['GEOID'] = DATA['id'].apply(getGEOID)


DATA = DATA[cols]
DATA = DATA.replace(['-', 'N'], np.nan)
DATA = DATA.replace(['2,500-'], 2500)
DATA = DATA.replace(['250,000+'], 250000)

for col in cols[1:]:
    print(col)
    DATA[col] = DATA[col].astype(float)
    
MedianIncome = DATA
MedianIncome.columns = ['GEOID', 'MedianInc_Whole','MedianInc_White', 'MedianInc_NHWhite', 'MedianInc_Black']
# MedianIncome['MedianInc_Disparity'] = MedianIncome['MedianInc_White'] - MedianIncome['MedianInc_Black']

MedianIncome.to_csv('DataMerge/E-MedianIncome.csv', index = False)

MedianIncome.head()<jupyter_output>Median income (dollars)_Households
Median income (dollars)_White
Median income (dollars)_White alone, not Hispanic or Latino
Median income (dollars)_Black or African American<jupyter_text>F. Political Ideology<jupyter_code>import pandas as pd

path = 'DataMerge/Data/F-PoliticalIdeology/political_ideology.xlsx'

def convertFIPS(v):
    v = str(int(v))
    if len(v) == 4:
        v = '0' + v
    else:
        v = v
    return v


DATA = pd.read_excel(path)


DATA['GEOID'] = DATA['FIPS'].apply(convertFIPS)

cols = 'GEOID	winner	votes_R_2020	votes_D_2020	votes_third_party_2020	votes_total_2020'.split('\t')


DATA = DATA[cols].sort_values(by ='GEOID').reset_index(drop = True)
PoliticalIdeology = DATA
PoliticalIdeology['republican'] = (PoliticalIdeology['winner'] == 'R').astype(int)
PoliticalIdeology['republican_Rate'] = PoliticalIdeology['votes_R_2020'] / PoliticalIdeology['votes_total_2020']

PoliticalIdeology.to_csv('DataMerge/F-PoliticalIdeology.csv', index = False)
PoliticalIdeology.head()
PoliticalIdeology.describe()<jupyter_output><empty_output><jupyter_text>G. VaxHesitancy<jupyter_code>path = 'DataMerge/Data/G-VACCINE HESITANCY/county_week26_data_fixed.xlsx'

VaxHesitancy = pd.read_excel(path)

def convertFIPS(v):
    v = str(int(v))
    if len(v) == 4:
        v = '0' + v
    else:
        v = v
    return v


VaxHesitancy['GEOID'] = VaxHesitancy['FIPS Code'].apply(convertFIPS)
VaxHesitancy = VaxHesitancy[['GEOID', 'Estimated hesitant', 'Estimated strongly hesitant']]
VaxHesitancy.columns = ['GEOID', 'hesitancy', 'strong_hesitancy']
VaxHesitancy = VaxHesitancy.sort_values(by = 'GEOID').reset_index(drop = True)

print(VaxHesitancy.shape)
VaxHesitancy.to_csv('DataMerge/G-VaxHesitancy.csv', index = False)
VaxHesitancy.head()<jupyter_output>(3142, 3)<jupyter_text>H. Covid-19<jupyter_code>#########
# path = 'DataMerge/Data/H-JHU-covid/jhu-2021-04-07.csv'
# path = 'DataMerge/Data/H-JHU-covid/jhu-05-20-2021.csv'
path = 'DataMerge/Data/H-JHU-covid/jhu-04-07-2021.csv'
#########


COVID = pd.read_csv(path)

COVID = COVID[- COVID['FIPS'].isna()]

COVID['GEOID'] = COVID['FIPS'].apply(convertFIPS)
COVID = COVID[['GEOID', 'Confirmed', 'Deaths', 'Incident_Rate', 'Case_Fatality_Ratio']].sort_values(by ='GEOID').reset_index(drop = True)
# COVID.rename(columns = {'mortality': 'mortality_rate'})
COVID.to_csv('DataMerge/H-COVID-04-07.csv', index = False)

COVID
#########
# path = 'DataMerge/Data/H-JHU-covid/jhu-2021-04-07.csv'
# path = 'DataMerge/Data/H-JHU-covid/jhu-05-20-2021.csv'
# path = 'DataMerge/Data/H-JHU-covid/jhu-04-07-2021.csv'
path = 'DataMerge/Data/H-JHU-covid/jhu-03-27-2021.csv'
#########


COVID = pd.read_csv(path)

COVID = COVID[- COVID['FIPS'].isna()]

COVID['GEOID'] = COVID['FIPS'].apply(convertFIPS)
COVID = COVID[['GEOID', 'Confirmed', 'Deaths', 'Incident_Rate', 'Case_Fatality_Ratio']].sort_values(by ='GEOID').reset_index(drop = True)
# COVID.rename(columns = {'mortality': 'mortality_rate'})
COVID.to_csv('DataMerge/H-COVID-03-27.csv', index = False)

COVID
path = 'DataMerge/Data/H-JHU-covid/jhu-04-19-2021.csv'

# path = 'DataMerge/Data/H-JHU-covid/jhu-05-20-2021.csv'


COVID = pd.read_csv(path)

COVID = COVID[- COVID['FIPS'].isna()]

COVID['GEOID'] = COVID['FIPS'].apply(convertFIPS)
COVID = COVID[['GEOID', 'Confirmed', 'Deaths', 'Incident_Rate', 'Case_Fatality_Ratio']].sort_values(by ='GEOID').reset_index(drop = True)
# COVID.rename(columns = {'mortality': 'mortality_rate'})
COVID.to_csv('DataMerge/H-COVID-04-19.csv', index = False)

COVID<jupyter_output><empty_output><jupyter_text>I. Internet (*Disparity)<jupyter_code>import pandas as pd
import numpy as np
path = 'DataMerge/Data/I-INTERNET/ACSST5Y2019.S2802_2021-03-20T111825/ACSST5Y2019.S2802_data_with_overlays_2021-03-20T111812.csv'

INTERNET_SUBSCRIPTIONS = pd.read_csv(path, skiprows=[0], low_memory=False)

cols = list(INTERNET_SUBSCRIPTIONS.columns)
cols = [i for i in cols if 'Margin of Error!' not in i]
INTERNET_SUBSCRIPTIONS = INTERNET_SUBSCRIPTIONS[cols]


new_names = []

for t in [i.split('!!') for i in list(INTERNET_SUBSCRIPTIONS.columns)]:
    # print(t)
    
    if len(t) <= 2:
        name = t[0]
    else:
        name = t[1] +'_' + t[-1]
    
    new_names.append(name)
    
    
new_names_dict = dict(zip(list(INTERNET_SUBSCRIPTIONS.columns), new_names))

# [v for k, v in new_names_dict.items()]


def remove_string(s):
    return s.replace(' County', '')

def get_county(s):
    return s.split(', ')[0]

def get_state(s):
    return s.split(', ')[1]



def getGEOID(idx):
    return idx.split('US')[-1]

INTERNET_SUBSCRIPTIONS = INTERNET_SUBSCRIPTIONS.rename(columns = new_names_dict)

INTERNET_SUBSCRIPTIONS['Geographic Area Name'] = INTERNET_SUBSCRIPTIONS['Geographic Area Name'].apply(remove_string)
INTERNET_SUBSCRIPTIONS['County'] = INTERNET_SUBSCRIPTIONS['Geographic Area Name'].apply(get_county)
INTERNET_SUBSCRIPTIONS['State']  = INTERNET_SUBSCRIPTIONS['Geographic Area Name'].apply(get_state)

INTERNET_SUBSCRIPTIONS['GEOID'] = INTERNET_SUBSCRIPTIONS['id'].apply(getGEOID)
# [i for i in INTERNET_SUBSCRIPTIONS.columns if 'Broadband' in i ]
cols_dict = {
 'GEOID': 'GEOID',

 'Geographic Area Name': 'Geographic Area Name',

 #  Total Race
 # 'Total_Total population in households',
 # 'Total_White alone',
 # 'Total_Black or African American alone',


 # Education
 # 'Total_Household population 25 years and over',
 # 'Total_Less than high school graduate or equivalency':'HighSchool_WholeNum',
 # "Total_High school graduate (includes equivalency), some college or associate's degree": 'HighSchool_WholeNum',
 # "Total_Bachelor's degree or higher": 'Bachelor_WholeNum',
    
#  # Employment
 # 'Total_Civilian population 16 years and over',
 # 'Total_In labor force',
 # 'Total_Employed',
 # 'Total_Unemployed',
 # 'Total_Not in labor force',

    
 # Race Rate with Internet
 'Broadband Internet Subscription_Total population in households':'IT_WholeNum',
 'Broadband Internet Subscription_White alone':'IT_WhiteNum',
 'Broadband Internet Subscription_White alone, not Hispanic or Latino':'IT_NHWhiteNum',
 'Broadband Internet Subscription_Black or African American alone':'IT_BlackNum',
 # 'Percent  Broadband Internet Subscription_American Indian and Alaska Native alone',
 #'Percent  Broadband Internet Subscription_Asian alone',
 
 'Percent  Broadband Internet Subscription_Total population in households':'IT_WholeRate',
 'Percent  Broadband Internet Subscription_White alone':'IT_WhiteRate',
 'Percent  Broadband Internet Subscription_White alone, not Hispanic or Latino':'IT_NHWhiteRate',
 'Percent  Broadband Internet Subscription_Black or African American alone':'IT_BlackRate',

    
 # Race Rate without Internet
#  'without an Internet Subscription_Total population in households':'CnI_WholeNum',
#  'without an Internet Subscription_White alone':'CnI_WhiteNum',
#  'without an Internet Subscription_White alone, not Hispanic or Latino':'CnI_NHWhiteNum',
#  'without an Internet Subscription_Black or African American alone':'CnI_BlackNum',
 # 'without an Internet Subscription_American Indian and Alaska Native alone',
 # 'without an Internet Subscription_Asian alone',


 # Race Rate without Computer
#  'no computer in household_Total population in households':'noC_WholeNum',
#  'no computer in household_White alone':'noC_WhiteNum',
#  'no computer in household_White alone, not Hispanic or Latino':'noC_NHWhiteNum',
#  'no computer in household_Black or African American alone':'noC_BlackNum',
 # 'Percent no computer in household_American Indian and Alaska Native alone',
 # 'Percent no computer in household_Asian alone',
}

# should be empty
[i for i in cols_dict if i not in INTERNET_SUBSCRIPTIONS.columns]


# cols_dict = {name: change_Demo_col_name(name) for name in cols}
INTERNET_SUBSCRIPTIONS = INTERNET_SUBSCRIPTIONS[[i for i in cols_dict]].rename(columns = cols_dict)
print(INTERNET_SUBSCRIPTIONS.shape)


INTERNET_SUBSCRIPTIONS = INTERNET_SUBSCRIPTIONS.replace('-', np.nan)
for i in INTERNET_SUBSCRIPTIONS.columns:
    if 'Rate' in i:
        INTERNET_SUBSCRIPTIONS[i] = INTERNET_SUBSCRIPTIONS[i].astype(float)

print(INTERNET_SUBSCRIPTIONS.columns)

INTERNET_SUBSCRIPTIONS



INTERNET_SUBSCRIPTIONS.to_csv('DataMerge/I-INTERNET_SUBSCRIPTIONS.csv', index = False)

INTERNET_SUBSCRIPTIONS.head()<jupyter_output><empty_output><jupyter_text>J. Employment (*Disparity)<jupyter_code>import pandas as pd
import numpy as np


path = 'DataMerge/Data/J-EMPLOYMENT/ACSST5Y2019.S2301_2021-03-20T122111/ACSST5Y2019.S2301_data_with_overlays_2021-03-20T122103.csv'
EMPLOYMENT = pd.read_csv(path, skiprows=[0], low_memory=False)
# How to understand total population in household?

cols = list(EMPLOYMENT.columns)
cols = [i for i in cols if 'Margin of Error!' not in i]
EMPLOYMENT = EMPLOYMENT[cols]

new_names = []


for i in EMPLOYMENT.columns:

    t = i.split('!!')
    
    if len(t) <= 2:
        name = t[0]
    else:
        name = t[1] +'_' + t[-1]
    
    new_names.append(name)
    
    
new_names_dict = dict(zip(list(EMPLOYMENT.columns), new_names))
# new_names_dict


def remove_string(s):
    return s.replace(' County', '')

def get_county(s):
    return s.split(', ')[0]

def get_state(s):
    return s.split(', ')[1]



EMPLOYMENT = EMPLOYMENT.rename(columns = new_names_dict)

EMPLOYMENT['Geographic Area Name'] = EMPLOYMENT['Geographic Area Name'].apply(remove_string)
EMPLOYMENT['County'] = EMPLOYMENT['Geographic Area Name'].apply(get_county)
EMPLOYMENT['State']  = EMPLOYMENT['Geographic Area Name'].apply(get_state)
# EMPLOYMENT['GEOID'] = EMPLOYMENT['id'].apply(getGEOID)
# EMPLOYMENT.to_csv('DataMerge/J-EMPLOYMENT.csv', index = False)

# EMPLOYMENT.head()

# list(EMPLOYMENT.columns)
# [i for i in EMPLOYMENT.columns if 'Unemployment' in i]


cols_dict = {
#     'GEOID': 'GEOID',
 'Geographic Area Name': 'Geographic Area Name',
 'Unemployment rate_Population 16 years and over':'Unempl_WholeRate',
 'Unemployment rate_White alone': 'Unempl_WhiteRate',
 'Unemployment rate_White alone, not Hispanic or Latino': 'Unempl_NHWhiteRate',
 'Unemployment rate_Black or African American alone': 'Unempl_BlackRate',}

# EMPLOYMENT = EMPLOYMENT[cols]
# cols_dict = {name: change_Employment_col_name(name) for name in cols}
EMPLOYMENT = EMPLOYMENT[[i for i in cols_dict]].rename(columns = cols_dict)
print(EMPLOYMENT.shape)


EMPLOYMENT = EMPLOYMENT.replace('-', np.nan)
for i in EMPLOYMENT.columns[2:]:
    EMPLOYMENT[i] = EMPLOYMENT[i].astype(float)

    
# Disparity
# EMPLOYMENT['Unemployment Parity'] = EMPLOYMENT['Rate_Unemployment_White'] - EMPLOYMENT['Rate_Unemployment_Black']

EMPLOYMENT.to_csv('DataMerge/J-EMPLOYMENT.csv', index = False)
EMPLOYMENT<jupyter_output>(3220, 5)<jupyter_text>K. Education (*Disparity)25 Above<jupyter_code>import pandas as pd
path = 'DataMerge/Data/K-EDUCATION/ACSST5Y2019.S1501_2021-03-20T122521/ACSST5Y2019.S1501_data_with_overlays_2021-03-18T142754.csv'
EDUCATION = pd.read_csv(path, skiprows=[0], low_memory=False)
# How to understand total population in household?

cols = list(EDUCATION.columns)
cols = [i for i in cols if 'Margin of Error!' not in i]
EDUCATION = EDUCATION[cols]
# [i for i in list(EDUCATION.columns) if 'Percent!!' in i]


def remove_string(s):
    return s.replace(' County', '')

def get_county(s):
    return s.split(', ')[0]

def get_state(s):
    return s.split(', ')[1]



# EDUCATION = EDUCATION.rename(columns = new_names_dict)

EDUCATION['Geographic Area Name'] = EDUCATION['Geographic Area Name'].apply(remove_string)
EDUCATION['County'] = EDUCATION['Geographic Area Name'].apply(get_county)
EDUCATION['State']  = EDUCATION['Geographic Area Name'].apply(get_state)
# EDUCATION.to_csv('DataMerge/K-EDUCATION.csv', index = False)

# EDUCATION.head()

# [i for i in EDUCATION.columns]



cols_dict = {
 'Geographic Area Name':'Geographic Area Name',

    
 'Estimate!!Percent!!AGE BY EDUCATIONAL ATTAINMENT!!Population 25 years and over!!High school graduate or higher':'HighSchool_WholeRate',
 "Estimate!!Percent!!AGE BY EDUCATIONAL ATTAINMENT!!Population 25 years and over!!Bachelor's degree or higher": "Bachelor_WholeRate",
    
    
 'Estimate!!Percent!!RACE AND HISPANIC OR LATINO ORIGIN BY EDUCATIONAL ATTAINMENT!!White alone!!High school graduate or higher':'HighSchool_WhiteRate',
 "Estimate!!Percent!!RACE AND HISPANIC OR LATINO ORIGIN BY EDUCATIONAL ATTAINMENT!!White alone!!Bachelor's degree or higher": 'Bachelor_WhiteRate',
    
    
 'Estimate!!Percent!!RACE AND HISPANIC OR LATINO ORIGIN BY EDUCATIONAL ATTAINMENT!!White alone, not Hispanic or Latino!!High school graduate or higher': 'HighSchool_NHWhiteRate',
 "Estimate!!Percent!!RACE AND HISPANIC OR LATINO ORIGIN BY EDUCATIONAL ATTAINMENT!!White alone, not Hispanic or Latino!!Bachelor's degree or higher": 'Bachelor_NHWhiteRate',

    
 'Estimate!!Percent!!RACE AND HISPANIC OR LATINO ORIGIN BY EDUCATIONAL ATTAINMENT!!Black alone!!High school graduate or higher':'HighSchool_BlackRate',
 "Estimate!!Percent!!RACE AND HISPANIC OR LATINO ORIGIN BY EDUCATIONAL ATTAINMENT!!Black alone!!Bachelor's degree or higher":'Bachelor_BlackRate',
    
}


EDUCATION = EDUCATION[cols]
# cols_dict = {name: change_Education_col_name(name) for name in cols}

EDUCATION = EDUCATION[[i for i in cols_dict ]].rename(columns = cols_dict)
print(EDUCATION.shape)


EDUCATION = EDUCATION.replace('-', np.nan)
for i in EDUCATION.columns[1:]:
    EDUCATION[i] = EDUCATION[i].astype(float)#/100

EDUCATION



EDUCATION.to_csv('DataMerge/K-EDUCATION.csv', index = False)

EDUCATION.columns<jupyter_output><empty_output><jupyter_text>L. BroadBoard Speed<jupyter_code>path1 = 'DataMerge/Data/L-Broadband/fixed_internet_speed.xlsx'
path2 = 'DataMerge/Data/L-Broadband/mobile_internet_speed.xlsx'

BroadBoardSpeed = pd.merge(pd.read_excel(path1), pd.read_excel(path2), on = 'GEOID')

BroadBoardSpeed = BroadBoardSpeed[['GEOID', 'fixed_avg_d_mbps_wt',
       'fixed_avg_u_mbps_wt', 'fixed_avg_lat_wt',
       'mobile_avg_d_mbps_wt', 'mobile_avg_u_mbps_wt', 'mobile_avg_lat_wt']]


BroadBoardSpeed['GEOID'] = BroadBoardSpeed['GEOID'].apply(modifyGEOID)
BroadBoardSpeed.to_csv('DataMerge/L-BroadBoardSpeed.csv', index = False)
BroadBoardSpeed<jupyter_output><empty_output><jupyter_text>M. Social Vulnerability Index<jupyter_code># Leave it for now


path = 'DataMerge/Data/M-SocialVulnerabilityIndex/SVI2018_US_COUNTY.csv'

SocialVulnerabilityIndex = pd.read_csv(path)

SocialVulnerabilityIndex['GEOID'] = SocialVulnerabilityIndex['FIPS'].apply(modifyGEOID)


SocialVulnerabilityIndex = SocialVulnerabilityIndex.replace([-999], np.nan)


cols = [
    'GEOID',
    'AREA_SQMI', 
    'DENSITY',
    'EP_POV', # poverty
     'EP_AGE65',
     'EP_DISABL', # disablity
     'EP_MINRTY', # minority
     'EP_LIMENG', # Limited English 
     'EP_NOVEH',  # No Vechile
    

     'RPL_THEME1',
     'RPL_THEME2',
     'RPL_THEME3',
     'RPL_THEME4',
     'RPL_THEMES',
 ]

# 'LOCATION': 'Autauga County, Alabama',
# 'AREA_SQMI': 594.4434589,


SocialVulnerabilityIndex['DENSITY'] = SocialVulnerabilityIndex['E_TOTPOP'] / SocialVulnerabilityIndex['AREA_SQMI']



SocialVulnerabilityIndex = SocialVulnerabilityIndex.sort_values(by = 'GEOID')[cols].reset_index(drop = True)
for i in ['EP_POV', # poverty
         'EP_AGE65',
         'EP_DISABL', # disablity
         'EP_MINRTY', # minority
         'EP_LIMENG', # Limited English 
         'EP_NOVEH',  # No Vechile
    ]:
    SocialVulnerabilityIndex[i] = SocialVulnerabilityIndex[i].astype(float)/100

    
SocialVulnerabilityIndex.to_csv('DataMerge/M-SocialVulnerabilityIndex.csv', index = False)

SocialVulnerabilityIndex.describe()
SocialVulnerabilityIndex.head()<jupyter_output><empty_output><jupyter_text>N. TrafficAllTransit<jupyter_code>path = 'DataMerge/Data/N-TrafficAllTransit/AllTransit.xlsx'
DATA = pd.read_excel(path)
DATA.head()



def remove_string(s):
    return s.replace(' County', '')


def change_state(s):
    State_Ab = {'NM': 'New Mexico', 'AL': 'Alabama', 'AK': 'Alaska', 'AZ': 'Arizona', 'AR': 'Arkansas', 'CA': 'California', 'CO': 'Colorado', 'CT': 'Connecticut', 'DE': 'Delaware', 'FL': 'Florida', 'GA': 'Georgia', 'ID': 'Idaho', 'IL': 'Illinois', 'IN': 'Indiana', 'IA': 'Iowa', 'KS': 'Kansas', 'KY': 'Kentucky', 'LA': 'Louisiana', 'ME': 'Maine', 'MD': 'Maryland', 'MA': 'Massachusetts', 'MI': 'Michigan', 'MN': 'Minnesota', 'MS': 'Mississippi', 'MO': 'Missouri', 'MT': 'Montana', 'NE': 'Nebraska', 'NV': 'Nevada', 'NH': 'New Hampshire', 'NJ': 'New Jersey', 'NY': 'New York', 'NC': 'North Carolina', 'ND': 'North Dakota', 'OH': 'Ohio', 'OK': 'Oklahoma', 'OR': 'Oregon', 'PA': 'Pennsylvania', 'RI': 'Rhode Island', 'SC': 'South Carolina', 'SD': 'South Dakota', 'TN': 'Tennessee', 'TX': 'Texas', 'UT': 'Utah', 'VT': 'Vermont', 'VA': 'Virginia', 'WA': 'Washington', 'WV': 'West Virginia', 'WI': 'Wisconsin', 'WY': 'Wyoming', 'HI': 'Hawaii', 'DC': 'District Of Columbia'}
    
    return State_Ab[s.strip()]

# DATA = DATA.rename(columns = new_names_dict)

# DATA['Geographic Area Name'] = DATA['Geographic Area Name'].apply(remove_string)
DATA['County'] = DATA['County'].apply(remove_string)
DATA['State'] = DATA['State'].apply(change_state)
# DATA['State']  = DATA['Geographic Area Name'].apply(get_state)

DATA['Geographic Area Name'] =  DATA['County'] + ', ' +   DATA['State']
Traffic = DATA
Traffic = Traffic[['Geographic Area Name', 'TCI+']]


set1 = set(DATA ['Geographic Area Name'].unique())

set2 = set(EDUCATION['Geographic Area Name'].unique())

print(set1 - set2)

Traffic = Traffic.rename(columns = {'TCI+': 'TCI'})

Traffic.to_csv('DataMerge/N-Traffic.csv', index = False)

Traffic<jupyter_output>{'District of Columbia, District Of Columbia'}<jupyter_text>O. Vaccine Coverage Index<jupyter_code>path = 'DataMerge/Data/O-Vaccine Coverage Index/CVAC_data_Feb2021.xlsx'

VaccineCoverageIndex = pd.read_excel(path, sheet_name = 'County_CVAC')

VaccineCoverageIndex['GEOID'] = VaccineCoverageIndex['FIPS'].apply(modifyGEOID)
VaccineCoverageIndex = VaccineCoverageIndex[['GEOID',
                      'Th1_Historic_Undervaccination',
                      'Th2_Sociodemographic_Barriers',
                      'Th3_Resource_constrained_Healthcare_System',
                      'Th4_Healthcare_Accessibility_Barriers',
                      'Th5_Irregular_Care_Seeking_Behavior', 
                      'CVAC']]


VaccineCoverageIndex.to_csv('DataMerge/O-VaccineCoverageIndex.csv', index = False)
VaccineCoverageIndex.head()
VaccineCoverageIndex.describe()<jupyter_output><empty_output><jupyter_text>P. Vaccine Faclility<jupyter_code>path = 'DataMerge/Data/P-VaccineFaclility/Racial Disparities Cont US.xlsx'

VaccineFaclility = pd.read_excel(path)


VaccineFaclility['GEOID'] = VaccineFaclility['fips Calc'].apply(modifyGEOID)
VaccineFaclility = VaccineFaclility.sort_values(by = 'GEOID')

cols = ['GEOID',
        'FQHC Count',
        'FacDensity10K', 
        'HOPD count', 
        'PHMCY Count', 
        'RUHC Count', ]

VaccineFaclility = VaccineFaclility[cols].reset_index(drop = True)
VaccineFaclility.to_csv('DataMerge/P-VaccineFaclility.csv', index = False)
VaccineFaclility.head()
VaccineFaclility.describe()<jupyter_output><empty_output><jupyter_text>Q. FCC<jupyter_code># Health
FCC = pd.read_csv('DataMerge/Data/Q-FCC/c2hgis_county.csv')


FCC = FCC.sort_values(by = 'geography_id')
FCC['GEOID'] = FCC['geography_id'].apply(modifyGEOID)
FCC['Rate_poor_fair_health'] = FCC['poor_fair_health_pct'].astype(float)/100
FCC = FCC[['GEOID', 'Rate_poor_fair_health', 'provcount_b', 'provcount_c', 'injury_deaths_per_100000']]

FCC.to_csv('DataMerge/Q-FCC.csv', index = False)

FCC.head()<jupyter_output><empty_output><jupyter_text>R. Health Ranking<jupyter_code>import pandas as pd
from DataMerge import us_popu

# path_cc  = 'DataMerge/Data/C-AnnualCountyPopulation/cc-est2019-alldata.csv'
# path_acs = 'DataMerge/Data/I-INTERNET/ACSST5Y2019.S2802_2021-03-20T111825/ACSST5Y2019.S2802_data_with_overlays_2021-03-20T111812.csv'
# US_COUNTY_POPULATION = us_popu.get_US_COUNTY_POPULATION(path_cc, path_acs)

path = 'DataMerge/Data/C-AnnualCountyPopulation/US_COUNTY_POPULATION.csv'

US_COUNTY_POPULATION = pd.read_csv(path)
States = US_COUNTY_POPULATION['State'].unique()



import requests

def get_url(state):
    
    url = 'https://www.countyhealthrankings.org/sites/default/files/media/document/2020%20County%20Health%20Rankings%20'
    url = url + state.replace(' ', '%20') + '%20Data%20-%20v1_0.xlsx'
    return url

header = {
  "User-Agent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.75 Safari/537.36",
  "X-Requested-With": "XMLHttpRequest"
}



D = {}
for state in States:

    tmp_path = 'DataMerge/Data/R-CountyHealthRanking/' + state + '.xls'
    D[state] = tmp_path
    
#     url = get_url(state)
#     resp = requests.get(url, headers=header)
#     output = open(tmp_path, 'wb')
#     output.write(resp.content)
#     output.close()

def get_heatlth_ranking(state, D):
    tmp_path = D[state]

    data = pd.read_excel(tmp_path, sheet_name ='Additional Measure Data', skiprows=1)[['FIPS', 'Segregation index', 'Average Traffic Volume per Meter of Major Roadways']]

    data1 = data.rename(columns = {'FIPS': 'GEOID'})
    data1
    
    cols = ['FIPS',
     '% Vaccinated',
     '% Vaccinated (Asian)',
     '% Vaccinated (Black)',
     '% Vaccinated (White)',
     'Violent Crime Rate'
    ]
    data = pd.read_excel(tmp_path, sheet_name ='Ranked Measure Data', skiprows=1)[cols]


    data2 = data.rename(columns = {'FIPS': 'GEOID'})
    data2

    data = pd.merge(data1, data2,  on = 'GEOID')
    return data


D2 = {}
for state in States:
    try:
        D2[state] = get_heatlth_ranking(state, D)
    except:
        print(state, D[state])

    
        
tmp_path = 'DataMerge/Data/R-CountyHealthRanking/2020 County Health Rankings West Virginia Data - v1.xlsx'

data = pd.read_excel(tmp_path, sheet_name ='Additional Measure Data', skiprows=1)[['FIPS', 'Segregation index', 'Average Traffic Volume per Meter of Major Roadways']]

data1 = data.rename(columns = {'FIPS': 'GEOID'})
data1

cols = ['FIPS',
 '% Vaccinated',
 '% Vaccinated (Asian)',
 '% Vaccinated (Black)',
 '% Vaccinated (White)',
 'Violent Crime Rate'
]
data = pd.read_excel(tmp_path, sheet_name ='Ranked Measure Data', skiprows=1)[cols]


data2 = data.rename(columns = {'FIPS': 'GEOID'})
# data2

data = pd.merge(data1, data2,  on = 'GEOID')
D2['West Virginia'] = data
HealthRanking = pd.concat([v for k,v in D2.items()])
HealthRanking['GEOID'] = HealthRanking['GEOID'].apply(modifyGEOID)
HealthRanking.to_csv('DataMerge/R-HealthRanking.csv', index = False)
HealthRanking<jupyter_output><empty_output><jupyter_text>S. ADI<jupyter_code>import pandas as pd
import numpy as np
adi = pd.read_csv("DataMerge/Data/S-Area Deprivation Index/us_bg.txt", sep=',', thousands = ',')

def county_fips(x):
    if len(str(x)) == 11:
        fips = str(0) + str(x)[0:4]
    else:
        fips = str(x)[0:5]
    return fips

adi['GEOID'] = adi['FIPS'].apply(lambda x: county_fips(x))


def convert_int(x):
    try:
        return float(x)
    except:
        return np.nan
    
adi['ADI_NATRANK'] = adi['ADI_NATRANK'].apply(lambda x: convert_int(x))



L = []
error = []
for county, df in adi.groupby('GEOID'):
    
    d =  {}
    d['GEOID'] = county
    d['ADIMean'] = df['ADI_NATRANK'].mean()
    d['ADIStd'] = df['ADI_NATRANK'].std()
    if np.isnan(d['ADIStd']):
        error.append(df)
    L.append(d)

ADI = pd.DataFrame(L)

ADI.to_csv('DataMerge/S-ADI.csv', index = False)
ADI.describe()
ADI.head()<jupyter_output><empty_output><jupyter_text>T. SDI<jupyter_code>path ='DataMerge/Data/T-SDI/ACS2015_countyallvars.xlsx'

SDI = pd.read_excel(path)

SDI['GEOID'] = SDI['county'].apply(modifyGEOID)
print(SDI.columns)
SDI = SDI[['GEOID', 'sdi_score']]


SDI.to_csv('DataMerge/T-SDI.csv', index = False)
SDI.head()
SDI.describe()<jupyter_output><empty_output><jupyter_text>U. Flu Vaccination (*Disparity)<jupyter_code>import pandas as pd

path = 'DataMerge/Data/U-FluVax/FluVax_Black.csv'
FluBlack = pd.read_csv(path)[['fips', 'analysis_value']].rename(columns = {'analysis_value':'FluVax_BlackRate'})

path = 'DataMerge/Data/U-FluVax/FluVax_White.csv'
FluWhite = pd.read_csv(path)[['fips', 'analysis_value']].rename(columns = {'analysis_value':'FluVax_NHWhiteRate'})

path = 'DataMerge/Data/U-FluVax/FluVax_Whole.csv'
FluWhole = pd.read_csv(path)[['fips', 'urban', 'analysis_value']].rename(columns = {'analysis_value':'FluVax_WholeRate'})


# path = 'DataMerge/Data/U-FluVax/FluVaxAA_Black.csv'
# FluAABlack = pd.read_csv(path)[['fips', 'analysis_value']].rename(columns = {'analysis_value':'FluAABlack'})

# path = 'DataMerge/Data/U-FluVax/FluVaxAA_White.csv'
# FluAAWhite = pd.read_csv(path)[['fips', 'analysis_value']].rename(columns = {'analysis_value':'FluAAWhite'})

# path = 'DataMerge/Data/U-FluVax/FluVaxAA_Whole.csv'
# FluAAWhole = pd.read_csv(path)[['fips', 'analysis_value']].rename(columns = {'analysis_value':'FluAAWhole'})


df = pd.merge(FluWhole, FluWhite, on = 'fips', how = 'left')
df = pd.merge(df, FluBlack, on = 'fips', how = 'left')
# df = pd.merge(df, FluAAWhole, on = 'fips', how = 'left')
# df = pd.merge(df, FluAAWhite, on = 'fips', how = 'left')
# df = pd.merge(df, FluAABlack, on = 'fips', how = 'left')

for i in ['FluVax_BlackRate', 'FluVax_NHWhiteRate','FluVax_WholeRate']:
    df[i] = df[i]
    
df['GEOID'] = df['fips'].apply(convertFIPS)

FluVax = df[['GEOID', 'urban',  'FluVax_BlackRate', 'FluVax_NHWhiteRate','FluVax_WholeRate']]
FluVax = FluVax.reset_index(drop = True)
FluVax.loc[FluVax.index, 'urban'] = (FluVax['urban'] == 'Urban').astype(int)
FluVax.to_csv('DataMerge/U-FluVax.csv', index = False)

FluVax<jupyter_output><empty_output><jupyter_text>V. Race_Population<jupyter_code>import pandas as pd
import numpy as np


def get_county_id(v):
    v = str(v)
    if len(v) == 1:
        return '00' + str(v)
    elif len(v) == 2:
        return '0' + str(v)
    elif len(v) == 3:
        return v
    else:
        raise ValueError('Error')
    
def get_state_id(v):
    v = str(v)
    if len(v) == 1:
        return '0' + str(v)
    elif len(v) == 2:
        return v
    else:
        raise ValueError('Error')
    
    


def remove_string(s):
    return s.replace(' County', '')

def get_county(s):
    return s.split(', ')[0]

def get_state(s):
    return s.split(', ')[1]

def getGEOID(idx):
    return idx.split('US')[-1]


def change_col_name(name):
    d = {}
    
    d['TOT_POP'] = 'WholeNum'
    d['WA'] = 'WhiteNum'
    d['BA'] = 'BlackNum'
    d['NHWA'] = 'NHWhiteNum'
    d['NHBA'] = 'NHBlackNum'
    # d['IA'] = 'Total_AIndA'
    # d['AA'] = 'Total_Asian'
    
    for k, v in d.items():
        if k in name:
            name = name.replace(k, v)
    
    return name



def get_US_COUNTY_POPULATION(path_cc, path_acs):
    # path_cc = 'Data/cc-est2019-alldata.csv'

    CCALLDATA = pd.read_csv(path_cc)

    CCALLDATA = CCALLDATA[CCALLDATA['YEAR'] == 12]  # 12: 2019-7-1
    CCALLDATA = CCALLDATA[CCALLDATA['AGEGRP'] == 0] #  0: Age Group Total
    CCALLDATA = CCALLDATA[CCALLDATA['SUMLEV'] == 50] #  50: County and/or Statistical Equivalent
    CCALLDATA = CCALLDATA.reset_index(drop = True)


    CCALLDATA['GEOID'] = CCALLDATA['STATE'].apply(get_state_id) + CCALLDATA['COUNTY'].apply(get_county_id)
    # POPULATION

    PrefixCols = ['GEOID', 'TOT_POP'] 
    RaceCols = ['WA', 'BA', 'NHWA', 'NHBA']

    for i in RaceCols:

        CCALLDATA[i] = CCALLDATA[i + '_MALE'] + CCALLDATA[i +'_FEMALE']


    CCALLDATA = CCALLDATA[PrefixCols + RaceCols]

    # print(CCALLDATA.shape)
    # CCALLDATA.head()
    
    INTERNET_SUBSCRIPTIONS = pd.read_csv(path_acs, skiprows=[0], low_memory=False)[['id', 'Geographic Area Name'] ]
    # How to understand total population in household?


    INTERNET_SUBSCRIPTIONS['Geographic Area Name'] = INTERNET_SUBSCRIPTIONS['Geographic Area Name'].apply(remove_string)
    INTERNET_SUBSCRIPTIONS['County'] = INTERNET_SUBSCRIPTIONS['Geographic Area Name'].apply(get_county)
    INTERNET_SUBSCRIPTIONS['State']  = INTERNET_SUBSCRIPTIONS['Geographic Area Name'].apply(get_state)



    INTERNET_SUBSCRIPTIONS['GEOID'] = INTERNET_SUBSCRIPTIONS['id'].apply(getGEOID)
    INTERNET_SUBSCRIPTIONS = INTERNET_SUBSCRIPTIONS[['GEOID', 'State', 'County', 'Geographic Area Name', ]]
    # print(INTERNET_SUBSCRIPTIONS.shape)
    # INTERNET_SUBSCRIPTIONS.head()



    US_COUNTY_POPULATION = pd.merge(INTERNET_SUBSCRIPTIONS, CCALLDATA, on = 'GEOID')

    cols_dict = {name: change_col_name(name) for name in US_COUNTY_POPULATION.columns}

    US_COUNTY_POPULATION = US_COUNTY_POPULATION.rename(columns = cols_dict)


    # print(US_COUNTY_POPULATION.shape)
    # US_COUNTY_POPULATION.head()
    
    
    return US_COUNTY_POPULATION



path_cc = 'DataMerge/Data/C-AnnualCountyPopulation/cc-est2019-alldata.csv'
path_acs = 'DataMerge/Data/I-INTERNET/ACSST5Y2019.S2802_2021-03-20T111825/ACSST5Y2019.S2802_data_with_overlays_2021-03-20T111812.csv'


US_COUNTY_POPULATION = get_US_COUNTY_POPULATION(path_cc, path_acs)

US_COUNTY_POPULATION.head()

AdultPopu = AgeDisparity[['GEOID'] + [i for i in AgeDisparity.columns if ('Above15' in i or 'Above20' in i) and 'Num' in i]]
US_COUNTY_POPULATION = US_COUNTY_POPULATION.merge(AdultPopu, on = 'GEOID')

US_COUNTY_POPULATION.columns
path = 'DataMerge/V-US_COUNTY_RACE_POPULATION.csv'

US_COUNTY_POPULATION.to_csv(path, index = False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15063221550302364, 'fraction_numerical': 0.02574674729704966, 'mean_word_length': 4.77779248279513, 'pattern_counts': {'":': 8, '<': 92, '<?xml version=': 0, '>': 88, 'https://': 3, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '23', 'dir': 'jupyter-structured-clean-dedup', 'id': '588965', 'license': 'CECILL-B', 'n_tokens_mistral': 18143, 'n_tokens_neox': 16495, 'n_words': 3318, 'path': '2.DataClean.ipynb', 'repo_name': 'floydluo/Covid19-Vaccination-Race-Disparity', 'size': '43656'}