<jupyter_start><jupyter_text>PROJECT 4 TEAM 2 Demographic Data Acquistion, Cleaning, Merging Kade, <NAME><jupyter_code>import pandas as pd

pd.set_option('display.max_columns', None)
pd.set_option('display.max_colwidth', None)<jupyter_output><empty_output><jupyter_text>American Community Survey 2019 data: 5-year estimates. Start with age and sex.- We used the metadata descriptions from this file: `ACSST5Y2019.S0101_metadata_2021-10-06T131343.csv` to choose the desired columns.- To keep things less busy, we created a columns_of_interest list for each .csv that we read in and used that during the `pd.read_csv()` process.<jupyter_code># Specify columns of interest.
acs2019_age_sex_columns_of_interest = ['GEO_ID', 
                                      'NAME', 
                                      'S0101_C01_001E', 
                                      'S0101_C01_032E',
                                      'S0101_C01_033E',
                                      'S0101_C02_022E',
                                      'S0101_C02_026E',
                                      'S0101_C02_030E',
                                      ]
                                      

# Read in the data. skiprows = 1 removes the verbose first row that was acting like a second header; it was forcing numeric columns to be objects.

acs2019_age_sex = pd.read_csv(
    '../data/02_demo_data/ACS/ACS_2019_age_and_sex_by_county/ACSST5Y2019.S0101_data_with_overlays_2021-10-06T131343.csv',
    usecols = acs2019_age_sex_columns_of_interest,
    skiprows=[1])
    

# Assigning new column names.
# Prior to renaming columns we ran the import without skipping the row just to ensure the column naming conventions matched up.
acs2019_age_sex.columns = ['geo_id', 
                          'name',
                          'total_pop_age_sex',
                          'median_age',
                          'sex_ratio_males',
                          'under_18_percent',
                          'over_18_percent',
                          'over_65_percent'
                         ]
acs2019_age_sex.head(3)<jupyter_output><empty_output><jupyter_text>Create a little function to clean up the imported dataframes.<jupyter_code>def clean_acs(df):
    df[['county', 'state']] = df['name'].str.split(',', expand = True)
    df['fips'] = df['geo_id'].str[-5:]
    df.drop(columns = ['geo_id', 'name'], inplace = True)
    df.set_index('fips', inplace = True)
    df = df[df['state'].str.contains('Puerto') == False]
    return df<jupyter_output><empty_output><jupyter_text>Note that we'll end up with duplicate `county` and `state` columns in each of these dataframes if we don't take care of it. We'll keep them from the first dataframe and drop the other ones after they serve their purpose for dropping Puerto Rico.<jupyter_code>acs2019_age_sex_clean = clean_acs(acs2019_age_sex)
acs2019_age_sex_clean
acs2019_age_sex.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Index: 3220 entries, 01001 to 72153
Data columns (total 8 columns):
 #   Column             Non-Null Count  Dtype  
---  ------             --------------  -----  
 0   total_pop_age_sex  3220 non-null   int64  
 1   median_age         3220 non-null   float64
 2   sex_ratio_males    3220 non-null   float64
 3   under_18_percent   3220 non-null   float64
 4   over_18_percent    3220 non-null   float64
 5   over_65_percent    3220 non-null   float64
 6   county             3220 non-null   object 
 7   state              3220 non-null   object 
dtypes: float64(5), int64(1), object(2)
memory usage: 226.4+ KB<jupyter_text>Next, read in the ACS 2019 income data columns of interest. We used `ACSST5Y2019.S1901_metadata_2021-10-05T144610.csv` to choose the desired columns.<jupyter_code># Specify columns of interest.
acs2019_income_columns_of_interest = ['GEO_ID', 
                                      'NAME', 
                                      'S1901_C01_001E', 
                                      'S1901_C01_012E', 
                                      'S1901_C01_013E', 
                                      'S1901_C02_001E', 
                                      'S1901_C02_012E', 
                                      'S1901_C02_013E'
                                     ]

acs2019_income = pd.read_csv(
    '../data/02_demo_data/ACS/ACS_2019_income_by_county/ACSST5Y2019.S1901_data_with_overlays_2021-10-05T144610.csv',
    usecols = acs2019_income_columns_of_interest,
    skiprows=[1])


# Clean up the naming conventions.
acs2019_income.columns = ['geo_id', 
                          'name',
                          'total_hh',
                          'median_hh_income',
                          'mean_hh_income',
                          'total_families',
                          'median_family_income',
                          'mean_family_income',
                          ]

acs2019_income.head(3)
acs2019_income.info()
# Call the dataframe cleaning function.
acs2019_income_clean = clean_acs(acs2019_income)
acs2019_income_clean.drop(columns = ['state', 'county'], inplace = True)
acs2019_income_clean<jupyter_output>/opt/anaconda3/lib/python3.8/site-packages/pandas/core/frame.py:4906: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  return super().drop(<jupyter_text>Next we need to parse off the FIPS ID, and reset the index to the FIPS ID.<jupyter_code>acs2019_income.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Index: 3220 entries, 01001 to 72153
Data columns (total 8 columns):
 #   Column                Non-Null Count  Dtype 
---  ------                --------------  ----- 
 0   total_hh              3220 non-null   int64 
 1   median_hh_income      3220 non-null   int64 
 2   mean_hh_income        3220 non-null   int64 
 3   total_families        3220 non-null   int64 
 4   median_family_income  3220 non-null   int64 
 5   mean_family_income    3220 non-null   int64 
 6   county                3220 non-null   object
 7   state                 3220 non-null   object
dtypes: int64(6), object(2)
memory usage: 226.4+ KB<jupyter_text>Then, read in the race and ethnicity by county data. We used `ACSDT5Y2019.B02001_metadata_2021-10-08T032031.csv` to identify columns of interest.<jupyter_code># Specify columns of interest.
acs2019_income_columns_of_interest = ['GEO_ID', 
                                      'NAME', 
                                      'B02001_001E', 
                                      'B02001_002E', 
                                      'B02001_003E', 
                                      'B02001_004E', 
                                      'B02001_005E', 
                                      'B02001_006E'
                                     ]

acs2019_race_ethnicity = pd.read_csv(
    '../data/02_demo_data/ACS/ACS_2019_race_and_ethnicity_by_county/ACSDT5Y2019.B02001_data_with_overlays_2021-10-08T032031.csv',
    usecols = acs2019_income_columns_of_interest,
    skiprows=[1])

# Clean up naming conventions.
acs2019_race_ethnicity.columns = ['geo_id', 
                          'name',
                          'tot_pop_race_ethnicity',
                          'tot_white',
                          'tot_black_af_am',
                          'tot_aminalnat', # American Indian and Alaska Native
                          'tot_asian',
                          'tot_hawopi' # Hawaiiwan and other Pacific Islander
                          ]
acs2019_race_ethnicity
acs2019_race_ethnicity_clean = clean_acs(acs2019_race_ethnicity)
acs2019_race_ethnicity_clean.drop(columns = ['state', 'county'], inplace = True)
acs2019_race_ethnicity_clean<jupyter_output>/opt/anaconda3/lib/python3.8/site-packages/pandas/core/frame.py:4906: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  return super().drop(<jupyter_text>Do the same type of import and data cleanup on the ACS 2019 poverty data.<jupyter_code># Specify columns of interest.
acs2019_poverty_columns_of_interest = ['GEO_ID', 
                                      'NAME', 
                                      'S1701_C01_001E', 
                                       'S1701_C02_001E', 
                                       'S1701_C03_001E']

# Read in the data.

acs2019_poverty = pd.read_csv(
    '../data/02_demo_data/ACS/ACS_2019_poverty_by_county/ACSST5Y2019.S1701_data_with_overlays_2021-11-02T160432.csv',
    usecols = acs2019_poverty_columns_of_interest,
    skiprows = [1])

# Change the column naming conventions.
acs2019_poverty.columns = ['geo_id', 
                          'name',
                          'total_pop_poverty',
                          'pop_below_poverty',
                          'percent_below_poverty',
                          ]
acs2019_poverty.head(3)
acs2019_poverty.info()
acs2019_poverty_clean = clean_acs(acs2019_poverty)
acs2019_poverty_clean.drop(columns = ['state', 'county'], inplace = True)
acs2019_poverty_clean<jupyter_output>/opt/anaconda3/lib/python3.8/site-packages/pandas/core/frame.py:4906: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  return super().drop(<jupyter_text>Read in the data from OpenIntro.org. This is an organization that created a nearly full file of data variables that many people are interested in. It is likely that many of the columns will overlap with others that we've read in from ACS data but we're grabbing them anyway because occasionally they're in a better format than the ACS data. https://www.openintro.org/data/?data=county_complete<jupyter_code>county_complete_columns_of_interest = ['fips', 
                           'state', 
                           'name', 
                           'smoking_ban_2010',                                                        
                           'asian_2019', 
                           'avg_family_size_2019', 
                           'black_2019',
                           'hispanic_2019',
                           'household_has_broadband_2019', 
                           'household_has_computer_2019',
                           'household_has_smartphone_2019',
                           'households_2019',
                           'households_speak_limited_english_2019',
                           'housing_mobile_homes_2019',
                           'hs_grad_2019',                           
                           'median_household_income_2019',
                           'median_individual_income_2019',
                           'native_2019',
                           'other_single_race_2019',
                           'pac_isl_2019',
                           'persons_per_household_2019',
                           'pop_2019',
                           'unemployment_rate_2019',
                           'uninsured_2019',
                           'veterans_2019',
                           'white_2019',
                           'white_not_hispanic_2019']

county_complete = pd.read_csv(
    '../data/02_demo_data/openintro_dot_org/county_complete.csv',
    usecols = county_complete_columns_of_interest,
    converters = {'fips': lambda x: str(x)})


county_complete.info()

# Got the idea for the converters from:
# https://stackoverflow.com/questions/13250046/how-to-keep-leading-zeros-in-a-column-when-reading-csv-with-pandas
county_complete.rename(columns = {'name': 'county'}, inplace = True)

county_complete['fips'] = county_complete['fips'].str.zfill(5)

# Learned about zfill() here: https://stackoverflow.com/questions/42375068/python-add-a-leading-zero-to-column-with-str-and-int
# Also here: https://www.datasciencemadesimple.com/add-leading-preceding-zeros-python/

county_complete.set_index('fips', inplace = True)
county_complete.drop(columns = ['state', 'county'], inplace = True)
county_complete<jupyter_output><empty_output><jupyter_text>Read in data about county classifications from: https://www.ers.usda.gov/data-products/atlas-of-rural-and-small-town-america/download-the-data/<jupyter_code>county_class_columns_of_interest = ['FIPStxt', 
                                    'State', 
                                    'County',
                                    'Metro2013',
                                    'RuralUrbanContinuumCode2013', 
                                    'Retirement_Destination_2015_Update', 
                                    'Metro_Adjacent2013']

county_class = pd.read_csv(
    '../data/02_demo_data/rural_atlas_all_counties/County Classifications.csv', 
    delimiter='\t', 
    encoding_errors = 'Ignore',
    converters = {'FIPStxt': lambda x: str(x)},
    usecols = county_class_columns_of_interest)


# Note: I added the encoding_errors = 'Ignore' as a test after getting this error:
# 'utf-8' codec can't decode byte 0xf1 in position 185518: invalid continuation byte
# This was on the second pass of importing the file; the first pass yielded no errors.

county_class
county_class.info()
# Fix the county column names
county_class.columns = ['fips', 
                        'state', 
                        'county', 
                        'rural_urban_continuum_code_2013',
                        'Metro2013',
                        'retirement_destination_2015_update', 
                        'metro_adjacent_2013'
                       ]

# Set the index to match the other columns
county_class.set_index('fips', inplace = True)

# Drop Puerto Rico.
county_class = county_class[county_class['state'].str.contains('PR') == False]

# Drop the state and county columns as they are no longer needed and are duplicates.

county_class.drop(columns = ['state', 'county'], inplace = True)

# Note that the state and county naming conventions don't match the other data set; this doesn't matter as we will merge on the FIPS index.
county_class.info()
%who DataFrame
dfs = [
    acs2019_age_sex_clean,
    acs2019_income_clean,
    acs2019_race_ethnicity_clean,
    acs2019_poverty_clean,
    county_complete,
    county_class, 
]

final_demo_df = pd.concat(dfs, axis = 1)
final_demo_df<jupyter_output><empty_output><jupyter_text>We discovered that five FIPS codes in the `county_class` dataframe were not in the other three. See below:<jupyter_code>county_class[~county_class.index.isin(acs2019_poverty.index)]
county_class[~county_class.index.isin(acs2019_income.index)]
county_class[~county_class.index.isin(county_complete.index)]<jupyter_output><empty_output><jupyter_text>Here's what happened to them: - 02010 in Aleutian Islands, AK, became 02016. https://www.thearda.com/archive/files/descriptions/AppendixC_RCMS.pdf - 02201 in Prince of Wales-Outer Ketchikan: Part of this area (Outer Ketchikan) was annexed by Ketchikan Gateway Borough (FIPS code = 02130), part was included in the new Wrangell City and borough (FIPS code = 02275), and the remainder was renamed Prince of Wales-Hyder Census Area (FIPS code = 02198). https://www.cdc.gov/nchs/data/nvss/bridged_race/county_geography_changes.pdf - 02232 in Skagway-Hoonah-Angoon, AK was split to create Skagway Municipality (FIPS = 02230) and Hoonah-Angoon Census Area (FIPS code = 02105). https://www.cdc.gov/nchs/data/data_acces_files/County-Geography.pdf - 02280 in Wrangell-Petersburg, AK, was split to create part of Wrangell City and Borough (FIPS code = 02275) and all of Petersburg Census Area (FIPS code = 02195)  https://www.cdc.gov/nchs/data/data_acces_files/County-Geography.pdf - 51515 in Bedford, VA, was merged (Bedford city, VA (FIPS code=51515). In 2013, Bedford City, an independent city, mergedwith Bedford county (FIPS code=51019) https://www.cdc.gov/nchs/data/data_acces_files/County-Geography.pdf We'll just drop those five rows from the `final_demo_df`.<jupyter_code>final_demo_df.drop(index=['02010', '02201', '02232', '02280', '51515'], inplace = True)

# Reset the index to pop FIPS back into the dataframe so that we can write it out to CSV and read it back in properly. 
final_demo_df.reset_index(inplace = True)
final_demo_df.info()
final_demo_df
final_demo_df.to_csv(
    '../data/cleaned/final_demo.csv',
    index=False
)
test_df = pd.read_csv(
    '../data/cleaned/final_demo.csv', 
    dtype = {'fips' : object}
)
    
test_df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08836374513909662, 'fraction_numerical': 0.061202512713131915, 'mean_word_length': 2.1701118907642707, 'pattern_counts': {'":': 0, '<': 47, '<?xml version=': 0, '>': 47, 'https://': 13, 'lorem ipsum': 0, 'www.': 8, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 38, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '541156', 'license': 'CC0-1.0', 'n_tokens_mistral': 5916, 'n_tokens_neox': 5023, 'n_words': 1129, 'path': 'code/02-1-Demo_Data-e.ipynb', 'repo_name': 'marcjreid/climate-change-analysis-group-project', 'size': '16721'}