<jupyter_start><jupyter_text>ACS Download ACS TOOL STEP 1 -> SETUP :  Uses: csa2tractcrosswalk.csv, VitalSignsCensus_ACS_Tables.xlsx Creates: ./AcsDataRaw/   ./AcsDataClean/  Import Modules & Construct Path Handlers<jupyter_code>import os
import sys
import pandas as pd

pd.set_option('display.expand_frame_repr', False)
pd.set_option('display.precision', 2)<jupyter_output><empty_output><jupyter_text>Get Vital Signs Reference Table<jupyter_code>acs_tables = pd.read_csv('https://raw.githubusercontent.com/bniajfi/bniajfi/main/vs_acs_table_ids.csv')
acs_tables.head()<jupyter_output><empty_output><jupyter_text>Get Tract/ CSA CrossWalk<jupyter_code>file = 'https://raw.githubusercontent.com/bniajfi/bniajfi/main/CSA-to-Tract-2010.csv'
crosswalk = pd.read_csv( file )
crosswalk = dict(zip(crosswalk['TRACTCE10'], crosswalk['CSA2010']  ) )<jupyter_output><empty_output><jupyter_text>Get retrieve_acs_data function<jupyter_code>!pip install dataplay geopandas VitalSigns
import VitalSigns as vs
from VitalSigns import acsDownload
help(acsDownload)
help(vs.acsDownload.retrieve_acs_data)<jupyter_output>Help on function retrieve_acs_data in module VitalSigns.acsDownload:

retrieve_acs_data(state, county, tract, tableId, year, save)<jupyter_text>Column Operations<jupyter_code>import csv # 'quote all'
def fixColNamesForCSV(x): return str(x)[:] if str(x) in ["NAME","state","county","tract", "CSA"] else str(x)[12:]<jupyter_output><empty_output><jupyter_text>ACS TOOL STEP 2 -> Execute :<jupyter_code>acs_tables.head()<jupyter_output><empty_output><jupyter_text>Save the ACS Data<jupyter_code># Set Index      df.set_index("NAME", inplace = True) 
# Save raw to    '../../data/3_outputs/acs/raw/'+year+'/'+tableId+'_'+description+'_5y'+year+'_est.csv'
# Tract to CSA   df['CSA'] = df.apply(lambda row: crosswalk.get(int(row['tract']), "empty"), axis=1)
# Save 4 use     '../../data/2_cleaned/acs/'+tableId+'_'+description+'_5y'+year+'_est.csv'

year = '19'
count = 0
startFrom = 0

state = '24'
county = '510'
tract = '*'
tableId = 'B19001'
saveAcs = True

# For each ACS Table
for x, row in acs_tables.iterrows():
    count += 1

    # Grab its Meta Data
    description = str(acs_tables.loc[x, 'shortname'])
    tableId = str(acs_tables.loc[x, 'id'])
    yearExists = int(acs_tables.loc[x, year+'_exists'])

    # If the Indicator is valid for the year 
    # use startFrom to being at a specific count
    if yearExists and count >= startFrom:
        print(str(count)+') '+tableId + ' ' + description)

        # retrieve the Python ACS indicator
        print('sending retrieve_acs_data', year, tableId)
        df = vs.acsDownload.retrieve_acs_data(state, county, tract, tableId, year, saveAcs)


        df.set_index("NAME", inplace = True) 

        # Save the Data as Raw
        # We do not want the id in the column names
        saveThis = df.rename( columns = lambda x : ( fixColNamesForCSV(x) ) )
        saveThis.to_csv('./AcsDataRaw/'+tableId+'_'+description+'_5y'+year+'_est.csv', quoting=csv.QUOTE_ALL)

        # Match Tract to CSA
        df['CSA'] = df.apply(lambda row: crosswalk.get(int(row['tract']), "empty"), axis=1)

        # Save the data (again) as Cleaned for me to use in the next scripts
        df.to_csv('./AcsDataClean/'+tableId+'_5y'+year+'_est.csv', quoting=csv.QUOTE_ALL)<jupyter_output><empty_output><jupyter_text>ACS Create IndicatorsACS TOOL STEP 1 -> SETUP : Uses: ./AcsDataClean/      VitalSignsCensus_ACS_Tables.xlsx  VitalSignsCensus_ACS_compare_data.xlsmCreates: ./VSData/  Get Vital Signs Reference Table<jupyter_code>ls
file = 'VitalSignsCensus_ACS_Tables.xlsx'
xls = pd.ExcelFile(findFile('./', file))
indicators = pd.read_excel(xls, sheet_name='indicators', index_col=0 )
indicators.head(30)<jupyter_output><empty_output><jupyter_text>ACS TOOL STEP 2 -> Execute :  Create ACS Indicators  Settings/ Get Data<jupyter_code>flag = True;
year = '19'
vsTbl = pd.read_excel(xls, sheet_name=str('vs'+year), index_col=0 )

# Prepare the Compare Historic Data
file = 'VitalSignsCensus_ACS_compare_data.xlsm'
compare_table = pd.read_excel(findFile('./', file), None);
comparable = False
if( str('VS'+year) in compare_table.keys() ):
    compare_table = compare_table[str('VS'+year)]
    comparable = True  
    columnsNames = compare_table.iloc[0]
    compare_table = compare_table.drop(compare_table.index[0])
    compare_table.set_index(['CSA2010'], drop = True, inplace = True)<jupyter_output><empty_output><jupyter_text>Create Indicators<jupyter_code># For Each Indicator
for x, row in indicators.iterrows():
    # Grab its Meta Data
    shortSource = str(indicators.loc[x, 'Short Source'])
    shortName = str(indicators.loc[x, 'ShortName'])[:-2]
    yearExists = int(float(indicators.loc[x, year+'_exists']))
    indicator = str(indicators.loc[x, 'Indicator'])
    indicator_number = str(indicators.index.tolist().index(x)+1 )
    fileLocation = str(findFile( './', shortName+'.py') )  

    # If the Indicator is valid for the year, and uses ACS Data, and method exists
    flag = True if fileLocation != str('None') else False
    flag = True if flag and yearExists else False
    flag = True if flag and shortSource in ['ACS', 'Census'] else False
    if flag:

        print(shortSource, shortName, yearExists, indicator, fileLocation, indicator_number )
        
        # retrieve the Python ACS indicator
        module = __import__( shortName )
        result = getattr( module, shortName )( year )

        # Put Baltimore City at the bottom of the list
        idx = result.index.tolist()
        idx.pop(idx.index('Baltimore City')) 
        result = result.reindex(idx+['Baltimore City']) 

        # Write the results back into the XL dataframe
        vsTbl[ str(indicator_number + '_' +shortName ) ] = result

        # Save the Data
        result.to_csv('./VSData/vs'+ str(year)+'_'+shortName+'.csv')
        
# drop columns with any empty values
vsTbl = vsTbl.dropna(axis=1, how='any')
            
# Save the Data
file = 'VS'+str(year)+'_indicators.xlsx'
file = findFile( 'VSData', file)
# writer = pd.ExcelWriter(file)

#vsTbl.to_excel(writer, str(year+'New_VS_Values') )

# Save the Data
vsTbl.to_csv('./VSData/vs'+str(year+'_New_VS_Values')+'.csv')

# Include Historic Data if exist
if( comparable ): 
    # add historic indicator to excel doc
    # compare_table.to_excel(writer,sheet_name = str(year+'Original_VS_Values') )  
    
    # compare sets
    info = pd.DataFrame()
    diff = pd.DataFrame()
    simi = pd.DataFrame()

    for column in vsTbl:
        number = ''
        plchld = ''
        if str(column[0:3]).isdigit(): plchld = 3
        elif str(column[0:2]).isdigit(): plchld = 2
        else: number = plchld = 1
        number = int(column[0:plchld])
        if number == 98: twoNotThree = False;
        new = pd.to_numeric(vsTbl[column], downcast='float')
        old = pd.to_numeric(compare_table[number], downcast='float', errors='coerce')

        info[str(number)+'_Error#_'] = old - new
        diff[str(number)+'_Error#_'] = old - new
        info[str(number)+'_Error%_'] = old / new
        simi[str(number)+'_Error%_'] = old / new
        info[str(number)+'_new_'+column[plchld:]] = vsTbl[column]
        info[str(number)+'_old_'+columnsNames[number]] = compare_table[number]


    #info.to_csv('./VSData/vs_comparisons_'+ str(year)+'.csv')
    #diff.to_csv('./VSData/vs_differences_'+ str(year)+'.csv')

    # Save the info dataframe
    #info.to_excel(writer, str(year+'_ExpandedView') )
    # Save the diff dataframe
    #diff.to_excel(writer,sheet_name = str(year+'_Error') )  
    # Save the diff dataframe
    #simi.to_excel(writer,sheet_name = str(year+'_Similarity_Ratio') )  
    
    info.to_csv('./VSData/vs'+str(year+'_ExpandedView')+'.csv')
    diff.to_csv('./VSData/vs'+str(year+'_Error')+'.csv')
    simi.to_csv('./VSData/vs'+str(year+'_Similarity_Ratio')+'.csv')

# writer.save()<jupyter_output><empty_output><jupyter_text>Compare Historic Indicators<jupyter_code>ls
# Quick test
shortName = str('hh25inc')
year = 19
# retrieve the Python ACS indicator
module = __import__( shortName )
result = getattr( module, shortName )( year )
result
# Delete Unassigned--Jail
df = df[df.index != 'Unassigned--Jail']

# Move Baltimore to Bottom
bc = df.loc[ 'Baltimore City' ]
df = df.drop( df.index[1] )
df.loc[ 'Baltimore City' ] = bc
vsTbl['18_fam']<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1414936769267478, 'fraction_numerical': 0.00882844189930804, 'mean_word_length': 4.25250626566416, 'pattern_counts': {'":': 0, '<': 44, '<?xml version=': 0, '>': 49, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, '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': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '10409', 'license': 'Apache-2.0', 'n_tokens_mistral': 3033, 'n_tokens_neox': 2932, 'n_words': 615, 'path': 'notebooks/91_Acs_Download_VS.ipynb', 'repo_name': 'BNIA/VitalSigns', 'size': '8382'}