{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Enrich Variable Name Lookup\n", "\n", "Enrich variable names can be represented using a variety of string representations depending on where you are looking. Frequently there is a need to cross reference between these. The `Country.enrich_variables` property provides the ability to easily retrieve a Pandas data frame. This data frame can be used to perform cross reference lookups, but there also is a hidden method you can use as well, `Country._ba_cntry.get_enrich_variables_from_name_list`. This method accepts any iterabe of strings, and will look up the values by checking for a sequence of case insensitive matches in the `name`, `alias`, `field_name` and `enrich_field_name` columns.\n", "\n", "NOTE: This requires at least version 1.9.1 of the Python API. This was included with the Pro 2.9 release, but if you have a previous release of Pro, you can get the most recent release of the Python API by [cloning your default Python environment, setting your new environment to be the default](https://pro.arcgis.com/en/pro-app/latest/arcpy/get-started/work-with-python-environments.htm) and [updating the ArcGIS Python API (arcgis) package](https://pro.arcgis.com/en/pro-app/latest/arcpy/get-started/work-with-python-packages.htm)." ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "from pathlib import Path\n", "\n", "from arcgis.geoenrichment import Country\n", "from arcgis.gis import GIS\n", "import pandas as pd" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Load Excel Table with Various Representations of Enrich Names\n", "\n", "This is a table of name variations for enrich variables I was passed to use as an example of different variations needing to be looked up." ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "dir_prj = Path.cwd().parent\n", "dir_ref = dir_prj/'references'\n", "xlsx_pth = dir_ref/'arcmap_pro_variables.xlsx'\n", "\n", "assert xlsx_pth.exists()" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
guided_workflow_namespro_enrich_namesfieldnames_arcmapfieldnames_pro
0TOTPOP_CY.2021 Key Demographic Indicators (Esr...populationtotals.totpop_cyTOTPOP_CYpopulationtotals_totpop_cy
1HHPOP_CY.2021 Key Demographic Indicators (Esri...householdtotals.tothh_cyHHPOP_CYpopulationtotals_hhpop_cy
2FAMPOP_CY.2021 Key Demographic Indicators (Esr...populationtotals.fampop_cyFAMPOP_CYpopulationtotals_fampop_cy
3MP14129a_B.2021 Health (Market Potential) (Esr...healthpersonalcare.mp14129a_bMP14129a_Bhealthpersonalcare_mp14129a_b
4X11001_X.2021 Education (Consumer Spending) (E...education.x11001_xX11001_Xeducation_x11001_x
5X11001_A.2021 Education (Consumer Spending) (E...education.x11001_x_aX11001_Aeducation_x11001_x_a
6X11001_I.2021 Education (Consumer Spending) (E...education.x11001_x_iX11001_Ieducation_x11001_x_i
7MP09147a_B.2021 Electronics (Market Potential)...electronicsinternet.mp09147a_bMP09147a_Belectronicsinternet_mp09147a_b
8MP09147a_I.2021 Electronics (Market Potential)...electronicsinternet.mp09147a_b_iMP09147a_Ielectronicsinternet_mp09147a_b_i
9MP09148a_B.2021 Electronics (Market Potential)...electronicsinternet.mp09148a_bMP09148a_Belectronicsinternet_mp09148a_b
10MP09148a_I.2021 Electronics (Market Potential)...electronicsinternet.mp09148a_b_iMP09148a_Ielectronicsinternet_mp09148a_b_i
11MP09134a_B.2021 Electronics (Market Potential)...electronicsinternet.mp09134a_bMP09134a_Belectronicsinternet_mp09134a_b
12MP09134a_I.2021 Electronics (Market Potential)...electronicsinternet.mp09134a_b_iMP09134a_Ielectronicsinternet_mp09134a_b_i
13ACSTOTPOP.2015-2019 Key Demographic Indicators...veterans.acstotpopACSTOTPOPveterans_acstotpop
14MOETOTPOP.2015-2019 Key Demographic Indicators...veterans.acstotpop_eMOETOTPOPveterans_acstotpop_e
15RELTOTPOP.2015-2019 Key Demographic Indicators...veterans.acstotpop_rRELTOTPOPveterans_acstotpop_r
16INDAGRI_CY.2021 Labor Force (Esri) (US 2021 Re...industry.indagri_cyINDAGRI_CYindustry_indagri_cy
17INDMIN_CY.2021 Labor Force (Esri) (US 2021 Ret...industry.indmin_cyINDMIN_CYindustry_indmin_cy
18INDCONS_CY.2021 Labor Force (Esri) (US 2021 Re...industry.indcons_cyINDCONS_CYindustry_indcons_cy
19HHWHTHHR10.2010 Households by Race_Hispanic Or...householdsbyraceofhouseholder.hhwhthhr10HHWHTHHR10householdsbyraceofhouseholder_hhwhthhr10
20HHBLKHHR10.2010 Households by Race_Hispanic Or...householdsbyraceofhouseholder.hhblkhhr10HHBLKHHR10householdsbyraceofhouseholder_hhblkhhr10
21HHAIHHR10.2010 Households by Race_Hispanic Ori...householdsbyraceofhouseholder.hhaihhr10HHAIHHR10householdsbyraceofhouseholder_hhaihhr10
22HHASNHHR10.2010 Households by Race_Hispanic Or...householdsbyraceofhouseholder.hhasnhhr10HHASNHHR10householdsbyraceofhouseholder_hhasnhhr10
23HHPIHHR10.2010 Households by Race_Hispanic Ori...householdsbyraceofhouseholder.hhpihhr10HHPIHHR10householdsbyraceofhouseholder_hhpihhr10
24HHOTHHHR10.2010 Households by Race_Hispanic Or...householdsbyraceofhouseholder.hhothhhr10HHOTHHHR10householdsbyraceofhouseholder_hhothhhr10
25TADULTBASE.2021 Tapestry Market Segmentation (...tapestryadultsnew.tadultbaseTADULTBASEtapestryadultsnew_tadultbase
26TADULT01.2021 Tapestry Market Segmentation (Ad...tapestryadultsnew.tadult01TADULT01tapestryadultsnew_tadult01
27TADULT02.2021 Tapestry Market Segmentation (Ad...tapestryadultsnew.tadult02TADULT02tapestryadultsnew_tadult02
28POPGRW10CY.2021 Key Demographic Indicators (Es...populationtotals.popgrw10cyPOPGRW10CYpopulationtotals_popgrw10cy
29DIVINDX_CY.2021 Race and Hispanic Origin (Esri...raceandhispanicorigin.divindx_cyDIVINDX_CYraceandhispanicorigin_divindx_cy
30MEDHINC_CY.2021 Income (Esri) (Esri USA 2021)householdincome.medhinc_cyMEDHINC_CYhouseholdincome_medhinc_cy
31ACSMEDHINC.2015-2019 Income (ACS) (Esri USA 20...householdincome.acsmedhincACSMEDHINChouseholdincome_acsmedhinc
32MOEMEDHINC.2015-2019 Income (ACS) (Esri USA 20...householdincome.acsmedhinc_eMOEMEDHINChouseholdincome_acsmedhinc_e
33RELMEDHINC.2015-2019 Income (ACS) (Esri USA 20...householdincome.acsmedhinc_rRELMEDHINChouseholdincome_acsmedhinc_r
\n", "
" ], "text/plain": [ " guided_workflow_names \\\n", "0 TOTPOP_CY.2021 Key Demographic Indicators (Esr... \n", "1 HHPOP_CY.2021 Key Demographic Indicators (Esri... \n", "2 FAMPOP_CY.2021 Key Demographic Indicators (Esr... \n", "3 MP14129a_B.2021 Health (Market Potential) (Esr... \n", "4 X11001_X.2021 Education (Consumer Spending) (E... \n", "5 X11001_A.2021 Education (Consumer Spending) (E... \n", "6 X11001_I.2021 Education (Consumer Spending) (E... \n", "7 MP09147a_B.2021 Electronics (Market Potential)... \n", "8 MP09147a_I.2021 Electronics (Market Potential)... \n", "9 MP09148a_B.2021 Electronics (Market Potential)... \n", "10 MP09148a_I.2021 Electronics (Market Potential)... \n", "11 MP09134a_B.2021 Electronics (Market Potential)... \n", "12 MP09134a_I.2021 Electronics (Market Potential)... \n", "13 ACSTOTPOP.2015-2019 Key Demographic Indicators... \n", "14 MOETOTPOP.2015-2019 Key Demographic Indicators... \n", "15 RELTOTPOP.2015-2019 Key Demographic Indicators... \n", "16 INDAGRI_CY.2021 Labor Force (Esri) (US 2021 Re... \n", "17 INDMIN_CY.2021 Labor Force (Esri) (US 2021 Ret... \n", "18 INDCONS_CY.2021 Labor Force (Esri) (US 2021 Re... \n", "19 HHWHTHHR10.2010 Households by Race_Hispanic Or... \n", "20 HHBLKHHR10.2010 Households by Race_Hispanic Or... \n", "21 HHAIHHR10.2010 Households by Race_Hispanic Ori... \n", "22 HHASNHHR10.2010 Households by Race_Hispanic Or... \n", "23 HHPIHHR10.2010 Households by Race_Hispanic Ori... \n", "24 HHOTHHHR10.2010 Households by Race_Hispanic Or... \n", "25 TADULTBASE.2021 Tapestry Market Segmentation (... \n", "26 TADULT01.2021 Tapestry Market Segmentation (Ad... \n", "27 TADULT02.2021 Tapestry Market Segmentation (Ad... \n", "28 POPGRW10CY.2021 Key Demographic Indicators (Es... \n", "29 DIVINDX_CY.2021 Race and Hispanic Origin (Esri... \n", "30 MEDHINC_CY.2021 Income (Esri) (Esri USA 2021) \n", "31 ACSMEDHINC.2015-2019 Income (ACS) (Esri USA 20... \n", "32 MOEMEDHINC.2015-2019 Income (ACS) (Esri USA 20... \n", "33 RELMEDHINC.2015-2019 Income (ACS) (Esri USA 20... \n", "\n", " pro_enrich_names fieldnames_arcmap \\\n", "0 populationtotals.totpop_cy TOTPOP_CY \n", "1 householdtotals.tothh_cy HHPOP_CY \n", "2 populationtotals.fampop_cy FAMPOP_CY \n", "3 healthpersonalcare.mp14129a_b MP14129a_B \n", "4 education.x11001_x X11001_X \n", "5 education.x11001_x_a X11001_A \n", "6 education.x11001_x_i X11001_I \n", "7 electronicsinternet.mp09147a_b MP09147a_B \n", "8 electronicsinternet.mp09147a_b_i MP09147a_I \n", "9 electronicsinternet.mp09148a_b MP09148a_B \n", "10 electronicsinternet.mp09148a_b_i MP09148a_I \n", "11 electronicsinternet.mp09134a_b MP09134a_B \n", "12 electronicsinternet.mp09134a_b_i MP09134a_I \n", "13 veterans.acstotpop ACSTOTPOP \n", "14 veterans.acstotpop_e MOETOTPOP \n", "15 veterans.acstotpop_r RELTOTPOP \n", "16 industry.indagri_cy INDAGRI_CY \n", "17 industry.indmin_cy INDMIN_CY \n", "18 industry.indcons_cy INDCONS_CY \n", "19 householdsbyraceofhouseholder.hhwhthhr10 HHWHTHHR10 \n", "20 householdsbyraceofhouseholder.hhblkhhr10 HHBLKHHR10 \n", "21 householdsbyraceofhouseholder.hhaihhr10 HHAIHHR10 \n", "22 householdsbyraceofhouseholder.hhasnhhr10 HHASNHHR10 \n", "23 householdsbyraceofhouseholder.hhpihhr10 HHPIHHR10 \n", "24 householdsbyraceofhouseholder.hhothhhr10 HHOTHHHR10 \n", "25 tapestryadultsnew.tadultbase TADULTBASE \n", "26 tapestryadultsnew.tadult01 TADULT01 \n", "27 tapestryadultsnew.tadult02 TADULT02 \n", "28 populationtotals.popgrw10cy POPGRW10CY \n", "29 raceandhispanicorigin.divindx_cy DIVINDX_CY \n", "30 householdincome.medhinc_cy MEDHINC_CY \n", "31 householdincome.acsmedhinc ACSMEDHINC \n", "32 householdincome.acsmedhinc_e MOEMEDHINC \n", "33 householdincome.acsmedhinc_r RELMEDHINC \n", "\n", " fieldnames_pro \n", "0 populationtotals_totpop_cy \n", "1 populationtotals_hhpop_cy \n", "2 populationtotals_fampop_cy \n", "3 healthpersonalcare_mp14129a_b \n", "4 education_x11001_x \n", "5 education_x11001_x_a \n", "6 education_x11001_x_i \n", "7 electronicsinternet_mp09147a_b \n", "8 electronicsinternet_mp09147a_b_i \n", "9 electronicsinternet_mp09148a_b \n", "10 electronicsinternet_mp09148a_b_i \n", "11 electronicsinternet_mp09134a_b \n", "12 electronicsinternet_mp09134a_b_i \n", "13 veterans_acstotpop \n", "14 veterans_acstotpop_e \n", "15 veterans_acstotpop_r \n", "16 industry_indagri_cy \n", "17 industry_indmin_cy \n", "18 industry_indcons_cy \n", "19 householdsbyraceofhouseholder_hhwhthhr10 \n", "20 householdsbyraceofhouseholder_hhblkhhr10 \n", "21 householdsbyraceofhouseholder_hhaihhr10 \n", "22 householdsbyraceofhouseholder_hhasnhhr10 \n", "23 householdsbyraceofhouseholder_hhpihhr10 \n", "24 householdsbyraceofhouseholder_hhothhhr10 \n", "25 tapestryadultsnew_tadultbase \n", "26 tapestryadultsnew_tadult01 \n", "27 tapestryadultsnew_tadult02 \n", "28 populationtotals_popgrw10cy \n", "29 raceandhispanicorigin_divindx_cy \n", "30 householdincome_medhinc_cy \n", "31 householdincome_acsmedhinc \n", "32 householdincome_acsmedhinc_e \n", "33 householdincome_acsmedhinc_r " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "xlsx_df = pd.read_excel(xlsx_pth)\n", "drop_cols = [c for c in xlsx_df.columns if c.startswith('Unnamed:')]\n", "xlsx_df.drop(columns=drop_cols, inplace=True)\n", "xlsx_df.columns = ['guided_workflow_names', 'pro_enrich_names', 'fieldnames_arcmap', 'fieldnames_pro']\n", "\n", "xlsx_df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Create a Country Object Instance\n", "\n", "Create a Country object instance referencing the country you are looking for variables in." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "usa = Country('usa')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Perform Lookups\n", "\n", "Now, we just need to provide an interable of strings to be matched into the `get_enrich_variables_from_list` function." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### ArcGIS Pro Enrich Names\n", "\n", "If using a list of names used to enrich with ArcGIS Pro, you can easily look up these values." ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "['populationtotals.totpop_cy', 'householdtotals.tothh_cy', 'populationtotals.fampop_cy', 'healthpersonalcare.mp14129a_b', 'education.x11001_x', 'education.x11001_x_a', 'education.x11001_x_i', 'electronicsinternet.mp09147a_b', 'electronicsinternet.mp09147a_b_i', 'electronicsinternet.mp09148a_b', 'electronicsinternet.mp09148a_b_i', 'electronicsinternet.mp09134a_b', 'electronicsinternet.mp09134a_b_i', 'veterans.acstotpop', 'veterans.acstotpop_e', 'veterans.acstotpop_r', 'industry.indagri_cy', 'industry.indmin_cy', 'industry.indcons_cy', 'householdsbyraceofhouseholder.hhwhthhr10', 'householdsbyraceofhouseholder.hhblkhhr10', 'householdsbyraceofhouseholder.hhaihhr10', 'householdsbyraceofhouseholder.hhasnhhr10', 'householdsbyraceofhouseholder.hhpihhr10', 'householdsbyraceofhouseholder.hhothhhr10', 'tapestryadultsnew.tadultbase', 'tapestryadultsnew.tadult01', 'tapestryadultsnew.tadult02', 'populationtotals.popgrw10cy', 'raceandhispanicorigin.divindx_cy', 'householdincome.medhinc_cy', 'householdincome.acsmedhinc', 'householdincome.acsmedhinc_e', 'householdincome.acsmedhinc_r']\n" ] } ], "source": [ "print(list(xlsx_df.pro_enrich_names))" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namealiasdata_collectionenrich_nameenrich_field_name
0ACSMEDHINC2019 Median HH Income (ACS 5-Yr)householdincomehouseholdincome.ACSMEDHINChouseholdincome_ACSMEDHINC
1MEDHINC_CY2021 Median Household Incomehouseholdincomehouseholdincome.MEDHINC_CYhouseholdincome_MEDHINC_CY
2MP09147a_B2021 Own Any TabletElectronicsInternetElectronicsInternet.MP09147a_BElectronicsInternet_MP09147a_B
3MP09148a_B2021 Own Any E-ReaderElectronicsInternetElectronicsInternet.MP09148a_BElectronicsInternet_MP09148a_B
4MP09134a_B2021 Own E-Reader/Tablet: Amazon KindleElectronicsInternetElectronicsInternet.MP09134a_BElectronicsInternet_MP09134a_B
5X11001_X2021 Educationeducationeducation.X11001_Xeducation_X11001_X
6MP14129a_B2021 Typically Spend 1-3 Hrs Exercising/WkHealthPersonalCareHealthPersonalCare.MP14129a_BHealthPersonalCare_MP14129a_B
7INDAGRI_CY2021 Industry: Agricultureindustryindustry.INDAGRI_CYindustry_INDAGRI_CY
8INDMIN_CY2021 Industry: Miningindustryindustry.INDMIN_CYindustry_INDMIN_CY
9INDCONS_CY2021 Industry: Constructionindustryindustry.INDCONS_CYindustry_INDCONS_CY
10HHWHTHHR102010 HHs w/White HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHWHTHHR10householdsbyraceofhouseholder_HHWHTHHR10
11HHBLKHHR102010 HHs w/Black HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHBLKHHR10householdsbyraceofhouseholder_HHBLKHHR10
12HHAIHHR102010 HHs w/Amer Indian HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHAIHHR10householdsbyraceofhouseholder_HHAIHHR10
13HHASNHHR102010 HHs w/Asian HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHASNHHR10householdsbyraceofhouseholder_HHASNHHR10
14HHPIHHR102010 HHs w/Pacific Isl HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHPIHHR10householdsbyraceofhouseholder_HHPIHHR10
15HHOTHHHR102010 HHs w/Other Race HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHOTHHHR10householdsbyraceofhouseholder_HHOTHHHR10
16TOTHH_CY2021 Total Householdshouseholdtotalshouseholdtotals.TOTHH_CYhouseholdtotals_TOTHH_CY
17TADULTBASE2021 Tapestry Adult Pop BasetapestryadultsNEWtapestryadultsNEW.TADULTBASEtapestryadultsNEW_TADULTBASE
18TADULT012021 Pop 18+ in Tapestry Seg 1AtapestryadultsNEWtapestryadultsNEW.TADULT01tapestryadultsNEW_TADULT01
19TADULT022021 Pop 18+ in Tapestry Seg 1BtapestryadultsNEWtapestryadultsNEW.TADULT02tapestryadultsNEW_TADULT02
20TOTPOP_CY2021 Total Populationpopulationtotalspopulationtotals.TOTPOP_CYpopulationtotals_TOTPOP_CY
21FAMPOP_CY2021 Family Populationpopulationtotalspopulationtotals.FAMPOP_CYpopulationtotals_FAMPOP_CY
22POPGRW10CY2010-2021 Growth Rate: Populationpopulationtotalspopulationtotals.POPGRW10CYpopulationtotals_POPGRW10CY
23DIVINDX_CY2021 Diversity Indexraceandhispanicoriginraceandhispanicorigin.DIVINDX_CYraceandhispanicorigin_DIVINDX_CY
24ACSTOTPOP2019 Total Population (ACS 5-Yr)veteransveterans.ACSTOTPOPveterans_ACSTOTPOP
\n", "
" ], "text/plain": [ " name alias \\\n", "0 ACSMEDHINC 2019 Median HH Income (ACS 5-Yr) \n", "1 MEDHINC_CY 2021 Median Household Income \n", "2 MP09147a_B 2021 Own Any Tablet \n", "3 MP09148a_B 2021 Own Any E-Reader \n", "4 MP09134a_B 2021 Own E-Reader/Tablet: Amazon Kindle \n", "5 X11001_X 2021 Education \n", "6 MP14129a_B 2021 Typically Spend 1-3 Hrs Exercising/Wk \n", "7 INDAGRI_CY 2021 Industry: Agriculture \n", "8 INDMIN_CY 2021 Industry: Mining \n", "9 INDCONS_CY 2021 Industry: Construction \n", "10 HHWHTHHR10 2010 HHs w/White HHr \n", "11 HHBLKHHR10 2010 HHs w/Black HHr \n", "12 HHAIHHR10 2010 HHs w/Amer Indian HHr \n", "13 HHASNHHR10 2010 HHs w/Asian HHr \n", "14 HHPIHHR10 2010 HHs w/Pacific Isl HHr \n", "15 HHOTHHHR10 2010 HHs w/Other Race HHr \n", "16 TOTHH_CY 2021 Total Households \n", "17 TADULTBASE 2021 Tapestry Adult Pop Base \n", "18 TADULT01 2021 Pop 18+ in Tapestry Seg 1A \n", "19 TADULT02 2021 Pop 18+ in Tapestry Seg 1B \n", "20 TOTPOP_CY 2021 Total Population \n", "21 FAMPOP_CY 2021 Family Population \n", "22 POPGRW10CY 2010-2021 Growth Rate: Population \n", "23 DIVINDX_CY 2021 Diversity Index \n", "24 ACSTOTPOP 2019 Total Population (ACS 5-Yr) \n", "\n", " data_collection enrich_name \\\n", "0 householdincome householdincome.ACSMEDHINC \n", "1 householdincome householdincome.MEDHINC_CY \n", "2 ElectronicsInternet ElectronicsInternet.MP09147a_B \n", "3 ElectronicsInternet ElectronicsInternet.MP09148a_B \n", "4 ElectronicsInternet ElectronicsInternet.MP09134a_B \n", "5 education education.X11001_X \n", "6 HealthPersonalCare HealthPersonalCare.MP14129a_B \n", "7 industry industry.INDAGRI_CY \n", "8 industry industry.INDMIN_CY \n", "9 industry industry.INDCONS_CY \n", "10 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHWHTHHR10 \n", "11 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHBLKHHR10 \n", "12 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHAIHHR10 \n", "13 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHASNHHR10 \n", "14 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHPIHHR10 \n", "15 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHOTHHHR10 \n", "16 householdtotals householdtotals.TOTHH_CY \n", "17 tapestryadultsNEW tapestryadultsNEW.TADULTBASE \n", "18 tapestryadultsNEW tapestryadultsNEW.TADULT01 \n", "19 tapestryadultsNEW tapestryadultsNEW.TADULT02 \n", "20 populationtotals populationtotals.TOTPOP_CY \n", "21 populationtotals populationtotals.FAMPOP_CY \n", "22 populationtotals populationtotals.POPGRW10CY \n", "23 raceandhispanicorigin raceandhispanicorigin.DIVINDX_CY \n", "24 veterans veterans.ACSTOTPOP \n", "\n", " enrich_field_name \n", "0 householdincome_ACSMEDHINC \n", "1 householdincome_MEDHINC_CY \n", "2 ElectronicsInternet_MP09147a_B \n", "3 ElectronicsInternet_MP09148a_B \n", "4 ElectronicsInternet_MP09134a_B \n", "5 education_X11001_X \n", "6 HealthPersonalCare_MP14129a_B \n", "7 industry_INDAGRI_CY \n", "8 industry_INDMIN_CY \n", "9 industry_INDCONS_CY \n", "10 householdsbyraceofhouseholder_HHWHTHHR10 \n", "11 householdsbyraceofhouseholder_HHBLKHHR10 \n", "12 householdsbyraceofhouseholder_HHAIHHR10 \n", "13 householdsbyraceofhouseholder_HHASNHHR10 \n", "14 householdsbyraceofhouseholder_HHPIHHR10 \n", "15 householdsbyraceofhouseholder_HHOTHHHR10 \n", "16 householdtotals_TOTHH_CY \n", "17 tapestryadultsNEW_TADULTBASE \n", "18 tapestryadultsNEW_TADULT01 \n", "19 tapestryadultsNEW_TADULT02 \n", "20 populationtotals_TOTPOP_CY \n", "21 populationtotals_FAMPOP_CY \n", "22 populationtotals_POPGRW10CY \n", "23 raceandhispanicorigin_DIVINDX_CY \n", "24 veterans_ACSTOTPOP " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "usa._ba_cntry.get_enrich_variables_from_iterable(xlsx_df.pro_enrich_names)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### ArcMap Enrich Names\n", "\n", "ArcMap required a slightly different string format. Hence, if migrating from ArcMap, the same workflow can be used to retrieve these values as well." ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "['TOTPOP_CY', 'HHPOP_CY', 'FAMPOP_CY', 'MP14129a_B', 'X11001_X', 'X11001_A', 'X11001_I', 'MP09147a_B', 'MP09147a_I', 'MP09148a_B', 'MP09148a_I', 'MP09134a_B', 'MP09134a_I', 'ACSTOTPOP', 'MOETOTPOP', 'RELTOTPOP', 'INDAGRI_CY', 'INDMIN_CY', 'INDCONS_CY', 'HHWHTHHR10', 'HHBLKHHR10', 'HHAIHHR10', 'HHASNHHR10', 'HHPIHHR10', 'HHOTHHHR10', 'TADULTBASE', 'TADULT01', 'TADULT02', 'POPGRW10CY', 'DIVINDX_CY', 'MEDHINC_CY', 'ACSMEDHINC', 'MOEMEDHINC', 'RELMEDHINC']\n" ] } ], "source": [ "print(list(xlsx_df.fieldnames_arcmap))" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namealiasdata_collectionenrich_nameenrich_field_name
0MEDHINC_CY2021 Median Household IncomeHealthHealth.MEDHINC_CYHealth_MEDHINC_CY
1MOETOTPOP2019 Total Population MOE (ACS 5-Yr)ACS_Housing_Summary_repACS_Housing_Summary_rep.MOETOTPOPACS_Housing_Summary_rep_MOETOTPOP
2TADULTBASE2021 Tapestry Adult Pop BasetravelMPItravelMPI.TADULTBASEtravelMPI_TADULTBASE
3TOTPOP_CY2021 Total PopulationAge_by_Sex_by_Race_Profile_repAge_by_Sex_by_Race_Profile_rep.TOTPOP_CYAge_by_Sex_by_Race_Profile_rep_TOTPOP_CY
4DIVINDX_CY2021 Diversity IndexPolicyPolicy.DIVINDX_CYPolicy_DIVINDX_CY
5ACSMEDHINC2019 Median HH Income (ACS 5-Yr)householdincomehouseholdincome.ACSMEDHINChouseholdincome_ACSMEDHINC
6MOEMEDHINC2019 Median HH Income MOE (ACS 5-Yr)householdincomehouseholdincome.MOEMEDHINChouseholdincome_MOEMEDHINC
7RELMEDHINC2019 Median HH Income REL (ACS 5-Yr)householdincomehouseholdincome.RELMEDHINChouseholdincome_RELMEDHINC
8X11001_I2021 Index: EducationCommunity_Profile_repCommunity_Profile_rep.X11001_ICommunity_Profile_rep_X11001_I
9MP09147a_B2021 Own Any TabletElectronicsInternetElectronicsInternet.MP09147a_BElectronicsInternet_MP09147a_B
10MP09147a_I2021 Index: Own Any TabletElectronicsInternetElectronicsInternet.MP09147a_IElectronicsInternet_MP09147a_I
11MP09148a_B2021 Own Any E-ReaderElectronicsInternetElectronicsInternet.MP09148a_BElectronicsInternet_MP09148a_B
12MP09148a_I2021 Index: Own Any E-ReaderElectronicsInternetElectronicsInternet.MP09148a_IElectronicsInternet_MP09148a_I
13MP09134a_B2021 Own E-Reader/Tablet: Amazon KindleElectronicsInternetElectronicsInternet.MP09134a_BElectronicsInternet_MP09134a_B
14MP09134a_I2021 Index: Own E-Reader/Tablet: Amazon KindleElectronicsInternetElectronicsInternet.MP09134a_IElectronicsInternet_MP09134a_I
15X11001_X2021 Educationeducationeducation.X11001_Xeducation_X11001_X
16X11001_A2021 Avg: Educationeducationeducation.X11001_Aeducation_X11001_A
17MP14129a_B2021 Typically Spend 1-3 Hrs Exercising/WkHealthPersonalCareHealthPersonalCare.MP14129a_BHealthPersonalCare_MP14129a_B
18INDAGRI_CY2021 Industry: Agricultureindustryindustry.INDAGRI_CYindustry_INDAGRI_CY
19INDMIN_CY2021 Industry: Miningindustryindustry.INDMIN_CYindustry_INDMIN_CY
20INDCONS_CY2021 Industry: Constructionindustryindustry.INDCONS_CYindustry_INDCONS_CY
21HHWHTHHR102010 HHs w/White HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHWHTHHR10householdsbyraceofhouseholder_HHWHTHHR10
22HHBLKHHR102010 HHs w/Black HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHBLKHHR10householdsbyraceofhouseholder_HHBLKHHR10
23HHAIHHR102010 HHs w/Amer Indian HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHAIHHR10householdsbyraceofhouseholder_HHAIHHR10
24HHASNHHR102010 HHs w/Asian HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHASNHHR10householdsbyraceofhouseholder_HHASNHHR10
25HHPIHHR102010 HHs w/Pacific Isl HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHPIHHR10householdsbyraceofhouseholder_HHPIHHR10
26HHOTHHHR102010 HHs w/Other Race HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHOTHHHR10householdsbyraceofhouseholder_HHOTHHHR10
27POPGRW10CY2010-2021 Growth Rate: PopulationKeyUSFactsKeyUSFacts.POPGRW10CYKeyUSFacts_POPGRW10CY
28TADULT012021 Pop 18+ in Tapestry Seg 1AtapestryadultsNEWtapestryadultsNEW.TADULT01tapestryadultsNEW_TADULT01
29TADULT022021 Pop 18+ in Tapestry Seg 1BtapestryadultsNEWtapestryadultsNEW.TADULT02tapestryadultsNEW_TADULT02
30ACSTOTPOP2019 Total Population (ACS 5-Yr)populationpopulation.ACSTOTPOPpopulation_ACSTOTPOP
31RELTOTPOP2019 Total Population REL (ACS 5-Yr)populationpopulation.RELTOTPOPpopulation_RELTOTPOP
32HHPOP_CY2021 Household Populationpopulationtotalspopulationtotals.HHPOP_CYpopulationtotals_HHPOP_CY
33FAMPOP_CY2021 Family Populationpopulationtotalspopulationtotals.FAMPOP_CYpopulationtotals_FAMPOP_CY
\n", "
" ], "text/plain": [ " name alias \\\n", "0 MEDHINC_CY 2021 Median Household Income \n", "1 MOETOTPOP 2019 Total Population MOE (ACS 5-Yr) \n", "2 TADULTBASE 2021 Tapestry Adult Pop Base \n", "3 TOTPOP_CY 2021 Total Population \n", "4 DIVINDX_CY 2021 Diversity Index \n", "5 ACSMEDHINC 2019 Median HH Income (ACS 5-Yr) \n", "6 MOEMEDHINC 2019 Median HH Income MOE (ACS 5-Yr) \n", "7 RELMEDHINC 2019 Median HH Income REL (ACS 5-Yr) \n", "8 X11001_I 2021 Index: Education \n", "9 MP09147a_B 2021 Own Any Tablet \n", "10 MP09147a_I 2021 Index: Own Any Tablet \n", "11 MP09148a_B 2021 Own Any E-Reader \n", "12 MP09148a_I 2021 Index: Own Any E-Reader \n", "13 MP09134a_B 2021 Own E-Reader/Tablet: Amazon Kindle \n", "14 MP09134a_I 2021 Index: Own E-Reader/Tablet: Amazon Kindle \n", "15 X11001_X 2021 Education \n", "16 X11001_A 2021 Avg: Education \n", "17 MP14129a_B 2021 Typically Spend 1-3 Hrs Exercising/Wk \n", "18 INDAGRI_CY 2021 Industry: Agriculture \n", "19 INDMIN_CY 2021 Industry: Mining \n", "20 INDCONS_CY 2021 Industry: Construction \n", "21 HHWHTHHR10 2010 HHs w/White HHr \n", "22 HHBLKHHR10 2010 HHs w/Black HHr \n", "23 HHAIHHR10 2010 HHs w/Amer Indian HHr \n", "24 HHASNHHR10 2010 HHs w/Asian HHr \n", "25 HHPIHHR10 2010 HHs w/Pacific Isl HHr \n", "26 HHOTHHHR10 2010 HHs w/Other Race HHr \n", "27 POPGRW10CY 2010-2021 Growth Rate: Population \n", "28 TADULT01 2021 Pop 18+ in Tapestry Seg 1A \n", "29 TADULT02 2021 Pop 18+ in Tapestry Seg 1B \n", "30 ACSTOTPOP 2019 Total Population (ACS 5-Yr) \n", "31 RELTOTPOP 2019 Total Population REL (ACS 5-Yr) \n", "32 HHPOP_CY 2021 Household Population \n", "33 FAMPOP_CY 2021 Family Population \n", "\n", " data_collection enrich_name \\\n", "0 Health Health.MEDHINC_CY \n", "1 ACS_Housing_Summary_rep ACS_Housing_Summary_rep.MOETOTPOP \n", "2 travelMPI travelMPI.TADULTBASE \n", "3 Age_by_Sex_by_Race_Profile_rep Age_by_Sex_by_Race_Profile_rep.TOTPOP_CY \n", "4 Policy Policy.DIVINDX_CY \n", "5 householdincome householdincome.ACSMEDHINC \n", "6 householdincome householdincome.MOEMEDHINC \n", "7 householdincome householdincome.RELMEDHINC \n", "8 Community_Profile_rep Community_Profile_rep.X11001_I \n", "9 ElectronicsInternet ElectronicsInternet.MP09147a_B \n", "10 ElectronicsInternet ElectronicsInternet.MP09147a_I \n", "11 ElectronicsInternet ElectronicsInternet.MP09148a_B \n", "12 ElectronicsInternet ElectronicsInternet.MP09148a_I \n", "13 ElectronicsInternet ElectronicsInternet.MP09134a_B \n", "14 ElectronicsInternet ElectronicsInternet.MP09134a_I \n", "15 education education.X11001_X \n", "16 education education.X11001_A \n", "17 HealthPersonalCare HealthPersonalCare.MP14129a_B \n", "18 industry industry.INDAGRI_CY \n", "19 industry industry.INDMIN_CY \n", "20 industry industry.INDCONS_CY \n", "21 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHWHTHHR10 \n", "22 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHBLKHHR10 \n", "23 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHAIHHR10 \n", "24 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHASNHHR10 \n", "25 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHPIHHR10 \n", "26 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHOTHHHR10 \n", "27 KeyUSFacts KeyUSFacts.POPGRW10CY \n", "28 tapestryadultsNEW tapestryadultsNEW.TADULT01 \n", "29 tapestryadultsNEW tapestryadultsNEW.TADULT02 \n", "30 population population.ACSTOTPOP \n", "31 population population.RELTOTPOP \n", "32 populationtotals populationtotals.HHPOP_CY \n", "33 populationtotals populationtotals.FAMPOP_CY \n", "\n", " enrich_field_name \n", "0 Health_MEDHINC_CY \n", "1 ACS_Housing_Summary_rep_MOETOTPOP \n", "2 travelMPI_TADULTBASE \n", "3 Age_by_Sex_by_Race_Profile_rep_TOTPOP_CY \n", "4 Policy_DIVINDX_CY \n", "5 householdincome_ACSMEDHINC \n", "6 householdincome_MOEMEDHINC \n", "7 householdincome_RELMEDHINC \n", "8 Community_Profile_rep_X11001_I \n", "9 ElectronicsInternet_MP09147a_B \n", "10 ElectronicsInternet_MP09147a_I \n", "11 ElectronicsInternet_MP09148a_B \n", "12 ElectronicsInternet_MP09148a_I \n", "13 ElectronicsInternet_MP09134a_B \n", "14 ElectronicsInternet_MP09134a_I \n", "15 education_X11001_X \n", "16 education_X11001_A \n", "17 HealthPersonalCare_MP14129a_B \n", "18 industry_INDAGRI_CY \n", "19 industry_INDMIN_CY \n", "20 industry_INDCONS_CY \n", "21 householdsbyraceofhouseholder_HHWHTHHR10 \n", "22 householdsbyraceofhouseholder_HHBLKHHR10 \n", "23 householdsbyraceofhouseholder_HHAIHHR10 \n", "24 householdsbyraceofhouseholder_HHASNHHR10 \n", "25 householdsbyraceofhouseholder_HHPIHHR10 \n", "26 householdsbyraceofhouseholder_HHOTHHHR10 \n", "27 KeyUSFacts_POPGRW10CY \n", "28 tapestryadultsNEW_TADULT01 \n", "29 tapestryadultsNEW_TADULT02 \n", "30 population_ACSTOTPOP \n", "31 population_RELTOTPOP \n", "32 populationtotals_HHPOP_CY \n", "33 populationtotals_FAMPOP_CY " ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "usa._ba_cntry.get_enrich_variables_from_iterable(xlsx_df.fieldnames_arcmap)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Field Names\n", "\n", "Once enriched, the `enrich_name` string has the period replaced for the output attibute field name (`enrich_field_name`). We can use also use these output field name values to lookup relevant rows in the `enrich_variables` table." ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "['populationtotals_totpop_cy', 'populationtotals_hhpop_cy', 'populationtotals_fampop_cy', 'healthpersonalcare_mp14129a_b', 'education_x11001_x', 'education_x11001_x_a', 'education_x11001_x_i', 'electronicsinternet_mp09147a_b', 'electronicsinternet_mp09147a_b_i', 'electronicsinternet_mp09148a_b', 'electronicsinternet_mp09148a_b_i', 'electronicsinternet_mp09134a_b', 'electronicsinternet_mp09134a_b_i', 'veterans_acstotpop', 'veterans_acstotpop_e', 'veterans_acstotpop_r', 'industry_indagri_cy', 'industry_indmin_cy', 'industry_indcons_cy', 'householdsbyraceofhouseholder_hhwhthhr10', 'householdsbyraceofhouseholder_hhblkhhr10', 'householdsbyraceofhouseholder_hhaihhr10', 'householdsbyraceofhouseholder_hhasnhhr10', 'householdsbyraceofhouseholder_hhpihhr10', 'householdsbyraceofhouseholder_hhothhhr10', 'tapestryadultsnew_tadultbase', 'tapestryadultsnew_tadult01', 'tapestryadultsnew_tadult02', 'populationtotals_popgrw10cy', 'raceandhispanicorigin_divindx_cy', 'householdincome_medhinc_cy', 'householdincome_acsmedhinc', 'householdincome_acsmedhinc_e', 'householdincome_acsmedhinc_r']\n" ] } ], "source": [ "print(list(xlsx_df.fieldnames_pro))" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namealiasdata_collectionenrich_nameenrich_field_name
0ACSMEDHINC2019 Median HH Income (ACS 5-Yr)householdincomehouseholdincome.ACSMEDHINChouseholdincome_ACSMEDHINC
1MEDHINC_CY2021 Median Household Incomehouseholdincomehouseholdincome.MEDHINC_CYhouseholdincome_MEDHINC_CY
2MP09147a_B2021 Own Any TabletElectronicsInternetElectronicsInternet.MP09147a_BElectronicsInternet_MP09147a_B
3MP09148a_B2021 Own Any E-ReaderElectronicsInternetElectronicsInternet.MP09148a_BElectronicsInternet_MP09148a_B
4MP09134a_B2021 Own E-Reader/Tablet: Amazon KindleElectronicsInternetElectronicsInternet.MP09134a_BElectronicsInternet_MP09134a_B
5X11001_X2021 Educationeducationeducation.X11001_Xeducation_X11001_X
6MP14129a_B2021 Typically Spend 1-3 Hrs Exercising/WkHealthPersonalCareHealthPersonalCare.MP14129a_BHealthPersonalCare_MP14129a_B
7INDAGRI_CY2021 Industry: Agricultureindustryindustry.INDAGRI_CYindustry_INDAGRI_CY
8INDMIN_CY2021 Industry: Miningindustryindustry.INDMIN_CYindustry_INDMIN_CY
9INDCONS_CY2021 Industry: Constructionindustryindustry.INDCONS_CYindustry_INDCONS_CY
10HHWHTHHR102010 HHs w/White HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHWHTHHR10householdsbyraceofhouseholder_HHWHTHHR10
11HHBLKHHR102010 HHs w/Black HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHBLKHHR10householdsbyraceofhouseholder_HHBLKHHR10
12HHAIHHR102010 HHs w/Amer Indian HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHAIHHR10householdsbyraceofhouseholder_HHAIHHR10
13HHASNHHR102010 HHs w/Asian HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHASNHHR10householdsbyraceofhouseholder_HHASNHHR10
14HHPIHHR102010 HHs w/Pacific Isl HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHPIHHR10householdsbyraceofhouseholder_HHPIHHR10
15HHOTHHHR102010 HHs w/Other Race HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHOTHHHR10householdsbyraceofhouseholder_HHOTHHHR10
16TADULTBASE2021 Tapestry Adult Pop BasetapestryadultsNEWtapestryadultsNEW.TADULTBASEtapestryadultsNEW_TADULTBASE
17TADULT012021 Pop 18+ in Tapestry Seg 1AtapestryadultsNEWtapestryadultsNEW.TADULT01tapestryadultsNEW_TADULT01
18TADULT022021 Pop 18+ in Tapestry Seg 1BtapestryadultsNEWtapestryadultsNEW.TADULT02tapestryadultsNEW_TADULT02
19TOTPOP_CY2021 Total Populationpopulationtotalspopulationtotals.TOTPOP_CYpopulationtotals_TOTPOP_CY
20HHPOP_CY2021 Household Populationpopulationtotalspopulationtotals.HHPOP_CYpopulationtotals_HHPOP_CY
21FAMPOP_CY2021 Family Populationpopulationtotalspopulationtotals.FAMPOP_CYpopulationtotals_FAMPOP_CY
22POPGRW10CY2010-2021 Growth Rate: Populationpopulationtotalspopulationtotals.POPGRW10CYpopulationtotals_POPGRW10CY
23DIVINDX_CY2021 Diversity Indexraceandhispanicoriginraceandhispanicorigin.DIVINDX_CYraceandhispanicorigin_DIVINDX_CY
24ACSTOTPOP2019 Total Population (ACS 5-Yr)veteransveterans.ACSTOTPOPveterans_ACSTOTPOP
\n", "
" ], "text/plain": [ " name alias \\\n", "0 ACSMEDHINC 2019 Median HH Income (ACS 5-Yr) \n", "1 MEDHINC_CY 2021 Median Household Income \n", "2 MP09147a_B 2021 Own Any Tablet \n", "3 MP09148a_B 2021 Own Any E-Reader \n", "4 MP09134a_B 2021 Own E-Reader/Tablet: Amazon Kindle \n", "5 X11001_X 2021 Education \n", "6 MP14129a_B 2021 Typically Spend 1-3 Hrs Exercising/Wk \n", "7 INDAGRI_CY 2021 Industry: Agriculture \n", "8 INDMIN_CY 2021 Industry: Mining \n", "9 INDCONS_CY 2021 Industry: Construction \n", "10 HHWHTHHR10 2010 HHs w/White HHr \n", "11 HHBLKHHR10 2010 HHs w/Black HHr \n", "12 HHAIHHR10 2010 HHs w/Amer Indian HHr \n", "13 HHASNHHR10 2010 HHs w/Asian HHr \n", "14 HHPIHHR10 2010 HHs w/Pacific Isl HHr \n", "15 HHOTHHHR10 2010 HHs w/Other Race HHr \n", "16 TADULTBASE 2021 Tapestry Adult Pop Base \n", "17 TADULT01 2021 Pop 18+ in Tapestry Seg 1A \n", "18 TADULT02 2021 Pop 18+ in Tapestry Seg 1B \n", "19 TOTPOP_CY 2021 Total Population \n", "20 HHPOP_CY 2021 Household Population \n", "21 FAMPOP_CY 2021 Family Population \n", "22 POPGRW10CY 2010-2021 Growth Rate: Population \n", "23 DIVINDX_CY 2021 Diversity Index \n", "24 ACSTOTPOP 2019 Total Population (ACS 5-Yr) \n", "\n", " data_collection enrich_name \\\n", "0 householdincome householdincome.ACSMEDHINC \n", "1 householdincome householdincome.MEDHINC_CY \n", "2 ElectronicsInternet ElectronicsInternet.MP09147a_B \n", "3 ElectronicsInternet ElectronicsInternet.MP09148a_B \n", "4 ElectronicsInternet ElectronicsInternet.MP09134a_B \n", "5 education education.X11001_X \n", "6 HealthPersonalCare HealthPersonalCare.MP14129a_B \n", "7 industry industry.INDAGRI_CY \n", "8 industry industry.INDMIN_CY \n", "9 industry industry.INDCONS_CY \n", "10 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHWHTHHR10 \n", "11 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHBLKHHR10 \n", "12 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHAIHHR10 \n", "13 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHASNHHR10 \n", "14 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHPIHHR10 \n", "15 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHOTHHHR10 \n", "16 tapestryadultsNEW tapestryadultsNEW.TADULTBASE \n", "17 tapestryadultsNEW tapestryadultsNEW.TADULT01 \n", "18 tapestryadultsNEW tapestryadultsNEW.TADULT02 \n", "19 populationtotals populationtotals.TOTPOP_CY \n", "20 populationtotals populationtotals.HHPOP_CY \n", "21 populationtotals populationtotals.FAMPOP_CY \n", "22 populationtotals populationtotals.POPGRW10CY \n", "23 raceandhispanicorigin raceandhispanicorigin.DIVINDX_CY \n", "24 veterans veterans.ACSTOTPOP \n", "\n", " enrich_field_name \n", "0 householdincome_ACSMEDHINC \n", "1 householdincome_MEDHINC_CY \n", "2 ElectronicsInternet_MP09147a_B \n", "3 ElectronicsInternet_MP09148a_B \n", "4 ElectronicsInternet_MP09134a_B \n", "5 education_X11001_X \n", "6 HealthPersonalCare_MP14129a_B \n", "7 industry_INDAGRI_CY \n", "8 industry_INDMIN_CY \n", "9 industry_INDCONS_CY \n", "10 householdsbyraceofhouseholder_HHWHTHHR10 \n", "11 householdsbyraceofhouseholder_HHBLKHHR10 \n", "12 householdsbyraceofhouseholder_HHAIHHR10 \n", "13 householdsbyraceofhouseholder_HHASNHHR10 \n", "14 householdsbyraceofhouseholder_HHPIHHR10 \n", "15 householdsbyraceofhouseholder_HHOTHHHR10 \n", "16 tapestryadultsNEW_TADULTBASE \n", "17 tapestryadultsNEW_TADULT01 \n", "18 tapestryadultsNEW_TADULT02 \n", "19 populationtotals_TOTPOP_CY \n", "20 populationtotals_HHPOP_CY \n", "21 populationtotals_FAMPOP_CY \n", "22 populationtotals_POPGRW10CY \n", "23 raceandhispanicorigin_DIVINDX_CY \n", "24 veterans_ACSTOTPOP " ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "usa._ba_cntry.get_enrich_variables_from_iterable(xlsx_df.fieldnames_pro)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "NOTE: Another way to get this list is simply using a list comprehension listing the field names using `arcpy.ListFields`.\n", "\n", "```\n", "import arcpy\n", "from arcgis.gis import Country\n", "pth_to_fc = r'C:/to/somwhere/somedb.fdgb/fc_of_stuff'\n", "field_name_list = [f.name for f in arcpy.ListFields(pth_to_fc)]\n", "usa = Country('usa')\n", "usa._ba_cntry.get_enrich_variables_from_name_list(field_name_list)\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Preprocessing with Lookup\n", "\n", "The first column we are saving for last. These values do not have explicit matches. However, using pandas, we can split the string at the period and only keep the first part. This first part can then be looked up since it matches the `name` column." ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 TOTPOP_CY.2021 Key Demographic Indicators (Esr...\n", "1 HHPOP_CY.2021 Key Demographic Indicators (Esri...\n", "2 FAMPOP_CY.2021 Key Demographic Indicators (Esr...\n", "3 MP14129a_B.2021 Health (Market Potential) (Esr...\n", "4 X11001_X.2021 Education (Consumer Spending) (E...\n", "Name: guided_workflow_names, dtype: object" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "xlsx_df.guided_workflow_names.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We do it slightly differently here. Instead of simply creating an iterable to use for lookup, in this case, we add the `search_names` to the existing dataframe generated from the Excel table we read in." ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
guided_workflow_namespro_enrich_namesfieldnames_arcmapfieldnames_prosearch_names
0TOTPOP_CY.2021 Key Demographic Indicators (Esr...populationtotals.totpop_cyTOTPOP_CYpopulationtotals_totpop_cyTOTPOP_CY
1HHPOP_CY.2021 Key Demographic Indicators (Esri...householdtotals.tothh_cyHHPOP_CYpopulationtotals_hhpop_cyHHPOP_CY
2FAMPOP_CY.2021 Key Demographic Indicators (Esr...populationtotals.fampop_cyFAMPOP_CYpopulationtotals_fampop_cyFAMPOP_CY
3MP14129a_B.2021 Health (Market Potential) (Esr...healthpersonalcare.mp14129a_bMP14129a_Bhealthpersonalcare_mp14129a_bMP14129a_B
4X11001_X.2021 Education (Consumer Spending) (E...education.x11001_xX11001_Xeducation_x11001_xX11001_X
\n", "
" ], "text/plain": [ " guided_workflow_names \\\n", "0 TOTPOP_CY.2021 Key Demographic Indicators (Esr... \n", "1 HHPOP_CY.2021 Key Demographic Indicators (Esri... \n", "2 FAMPOP_CY.2021 Key Demographic Indicators (Esr... \n", "3 MP14129a_B.2021 Health (Market Potential) (Esr... \n", "4 X11001_X.2021 Education (Consumer Spending) (E... \n", "\n", " pro_enrich_names fieldnames_arcmap \\\n", "0 populationtotals.totpop_cy TOTPOP_CY \n", "1 householdtotals.tothh_cy HHPOP_CY \n", "2 populationtotals.fampop_cy FAMPOP_CY \n", "3 healthpersonalcare.mp14129a_b MP14129a_B \n", "4 education.x11001_x X11001_X \n", "\n", " fieldnames_pro search_names \n", "0 populationtotals_totpop_cy TOTPOP_CY \n", "1 populationtotals_hhpop_cy HHPOP_CY \n", "2 populationtotals_fampop_cy FAMPOP_CY \n", "3 healthpersonalcare_mp14129a_b MP14129a_B \n", "4 education_x11001_x X11001_X " ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "xlsx_df['search_names'] = xlsx_df.guided_workflow_names.str.split('.').apply(lambda val: val[0])\n", "\n", "xlsx_df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Since the function just needs an `Iterable` object, we can use the column (Pandas Series) we just created to look up valid values." ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namealiasdata_collectionenrich_nameenrich_field_name
0MEDHINC_CY2021 Median Household IncomeHealthHealth.MEDHINC_CYHealth_MEDHINC_CY
1MOETOTPOP2019 Total Population MOE (ACS 5-Yr)ACS_Housing_Summary_repACS_Housing_Summary_rep.MOETOTPOPACS_Housing_Summary_rep_MOETOTPOP
2TADULTBASE2021 Tapestry Adult Pop BasetravelMPItravelMPI.TADULTBASEtravelMPI_TADULTBASE
3TOTPOP_CY2021 Total PopulationAge_by_Sex_by_Race_Profile_repAge_by_Sex_by_Race_Profile_rep.TOTPOP_CYAge_by_Sex_by_Race_Profile_rep_TOTPOP_CY
4DIVINDX_CY2021 Diversity IndexPolicyPolicy.DIVINDX_CYPolicy_DIVINDX_CY
5ACSMEDHINC2019 Median HH Income (ACS 5-Yr)householdincomehouseholdincome.ACSMEDHINChouseholdincome_ACSMEDHINC
6MOEMEDHINC2019 Median HH Income MOE (ACS 5-Yr)householdincomehouseholdincome.MOEMEDHINChouseholdincome_MOEMEDHINC
7RELMEDHINC2019 Median HH Income REL (ACS 5-Yr)householdincomehouseholdincome.RELMEDHINChouseholdincome_RELMEDHINC
8X11001_I2021 Index: EducationCommunity_Profile_repCommunity_Profile_rep.X11001_ICommunity_Profile_rep_X11001_I
9MP09147a_B2021 Own Any TabletElectronicsInternetElectronicsInternet.MP09147a_BElectronicsInternet_MP09147a_B
10MP09147a_I2021 Index: Own Any TabletElectronicsInternetElectronicsInternet.MP09147a_IElectronicsInternet_MP09147a_I
11MP09148a_B2021 Own Any E-ReaderElectronicsInternetElectronicsInternet.MP09148a_BElectronicsInternet_MP09148a_B
12MP09148a_I2021 Index: Own Any E-ReaderElectronicsInternetElectronicsInternet.MP09148a_IElectronicsInternet_MP09148a_I
13MP09134a_B2021 Own E-Reader/Tablet: Amazon KindleElectronicsInternetElectronicsInternet.MP09134a_BElectronicsInternet_MP09134a_B
14MP09134a_I2021 Index: Own E-Reader/Tablet: Amazon KindleElectronicsInternetElectronicsInternet.MP09134a_IElectronicsInternet_MP09134a_I
15X11001_X2021 Educationeducationeducation.X11001_Xeducation_X11001_X
16X11001_A2021 Avg: Educationeducationeducation.X11001_Aeducation_X11001_A
17MP14129a_B2021 Typically Spend 1-3 Hrs Exercising/WkHealthPersonalCareHealthPersonalCare.MP14129a_BHealthPersonalCare_MP14129a_B
18INDAGRI_CY2021 Industry: Agricultureindustryindustry.INDAGRI_CYindustry_INDAGRI_CY
19INDMIN_CY2021 Industry: Miningindustryindustry.INDMIN_CYindustry_INDMIN_CY
20INDCONS_CY2021 Industry: Constructionindustryindustry.INDCONS_CYindustry_INDCONS_CY
21HHWHTHHR102010 HHs w/White HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHWHTHHR10householdsbyraceofhouseholder_HHWHTHHR10
22HHBLKHHR102010 HHs w/Black HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHBLKHHR10householdsbyraceofhouseholder_HHBLKHHR10
23HHAIHHR102010 HHs w/Amer Indian HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHAIHHR10householdsbyraceofhouseholder_HHAIHHR10
24HHASNHHR102010 HHs w/Asian HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHASNHHR10householdsbyraceofhouseholder_HHASNHHR10
25HHPIHHR102010 HHs w/Pacific Isl HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHPIHHR10householdsbyraceofhouseholder_HHPIHHR10
26HHOTHHHR102010 HHs w/Other Race HHrhouseholdsbyraceofhouseholderhouseholdsbyraceofhouseholder.HHOTHHHR10householdsbyraceofhouseholder_HHOTHHHR10
27POPGRW10CY2010-2021 Growth Rate: PopulationKeyUSFactsKeyUSFacts.POPGRW10CYKeyUSFacts_POPGRW10CY
28TADULT012021 Pop 18+ in Tapestry Seg 1AtapestryadultsNEWtapestryadultsNEW.TADULT01tapestryadultsNEW_TADULT01
29TADULT022021 Pop 18+ in Tapestry Seg 1BtapestryadultsNEWtapestryadultsNEW.TADULT02tapestryadultsNEW_TADULT02
30ACSTOTPOP2019 Total Population (ACS 5-Yr)populationpopulation.ACSTOTPOPpopulation_ACSTOTPOP
31RELTOTPOP2019 Total Population REL (ACS 5-Yr)populationpopulation.RELTOTPOPpopulation_RELTOTPOP
32HHPOP_CY2021 Household Populationpopulationtotalspopulationtotals.HHPOP_CYpopulationtotals_HHPOP_CY
33FAMPOP_CY2021 Family Populationpopulationtotalspopulationtotals.FAMPOP_CYpopulationtotals_FAMPOP_CY
\n", "
" ], "text/plain": [ " name alias \\\n", "0 MEDHINC_CY 2021 Median Household Income \n", "1 MOETOTPOP 2019 Total Population MOE (ACS 5-Yr) \n", "2 TADULTBASE 2021 Tapestry Adult Pop Base \n", "3 TOTPOP_CY 2021 Total Population \n", "4 DIVINDX_CY 2021 Diversity Index \n", "5 ACSMEDHINC 2019 Median HH Income (ACS 5-Yr) \n", "6 MOEMEDHINC 2019 Median HH Income MOE (ACS 5-Yr) \n", "7 RELMEDHINC 2019 Median HH Income REL (ACS 5-Yr) \n", "8 X11001_I 2021 Index: Education \n", "9 MP09147a_B 2021 Own Any Tablet \n", "10 MP09147a_I 2021 Index: Own Any Tablet \n", "11 MP09148a_B 2021 Own Any E-Reader \n", "12 MP09148a_I 2021 Index: Own Any E-Reader \n", "13 MP09134a_B 2021 Own E-Reader/Tablet: Amazon Kindle \n", "14 MP09134a_I 2021 Index: Own E-Reader/Tablet: Amazon Kindle \n", "15 X11001_X 2021 Education \n", "16 X11001_A 2021 Avg: Education \n", "17 MP14129a_B 2021 Typically Spend 1-3 Hrs Exercising/Wk \n", "18 INDAGRI_CY 2021 Industry: Agriculture \n", "19 INDMIN_CY 2021 Industry: Mining \n", "20 INDCONS_CY 2021 Industry: Construction \n", "21 HHWHTHHR10 2010 HHs w/White HHr \n", "22 HHBLKHHR10 2010 HHs w/Black HHr \n", "23 HHAIHHR10 2010 HHs w/Amer Indian HHr \n", "24 HHASNHHR10 2010 HHs w/Asian HHr \n", "25 HHPIHHR10 2010 HHs w/Pacific Isl HHr \n", "26 HHOTHHHR10 2010 HHs w/Other Race HHr \n", "27 POPGRW10CY 2010-2021 Growth Rate: Population \n", "28 TADULT01 2021 Pop 18+ in Tapestry Seg 1A \n", "29 TADULT02 2021 Pop 18+ in Tapestry Seg 1B \n", "30 ACSTOTPOP 2019 Total Population (ACS 5-Yr) \n", "31 RELTOTPOP 2019 Total Population REL (ACS 5-Yr) \n", "32 HHPOP_CY 2021 Household Population \n", "33 FAMPOP_CY 2021 Family Population \n", "\n", " data_collection enrich_name \\\n", "0 Health Health.MEDHINC_CY \n", "1 ACS_Housing_Summary_rep ACS_Housing_Summary_rep.MOETOTPOP \n", "2 travelMPI travelMPI.TADULTBASE \n", "3 Age_by_Sex_by_Race_Profile_rep Age_by_Sex_by_Race_Profile_rep.TOTPOP_CY \n", "4 Policy Policy.DIVINDX_CY \n", "5 householdincome householdincome.ACSMEDHINC \n", "6 householdincome householdincome.MOEMEDHINC \n", "7 householdincome householdincome.RELMEDHINC \n", "8 Community_Profile_rep Community_Profile_rep.X11001_I \n", "9 ElectronicsInternet ElectronicsInternet.MP09147a_B \n", "10 ElectronicsInternet ElectronicsInternet.MP09147a_I \n", "11 ElectronicsInternet ElectronicsInternet.MP09148a_B \n", "12 ElectronicsInternet ElectronicsInternet.MP09148a_I \n", "13 ElectronicsInternet ElectronicsInternet.MP09134a_B \n", "14 ElectronicsInternet ElectronicsInternet.MP09134a_I \n", "15 education education.X11001_X \n", "16 education education.X11001_A \n", "17 HealthPersonalCare HealthPersonalCare.MP14129a_B \n", "18 industry industry.INDAGRI_CY \n", "19 industry industry.INDMIN_CY \n", "20 industry industry.INDCONS_CY \n", "21 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHWHTHHR10 \n", "22 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHBLKHHR10 \n", "23 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHAIHHR10 \n", "24 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHASNHHR10 \n", "25 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHPIHHR10 \n", "26 householdsbyraceofhouseholder householdsbyraceofhouseholder.HHOTHHHR10 \n", "27 KeyUSFacts KeyUSFacts.POPGRW10CY \n", "28 tapestryadultsNEW tapestryadultsNEW.TADULT01 \n", "29 tapestryadultsNEW tapestryadultsNEW.TADULT02 \n", "30 population population.ACSTOTPOP \n", "31 population population.RELTOTPOP \n", "32 populationtotals populationtotals.HHPOP_CY \n", "33 populationtotals populationtotals.FAMPOP_CY \n", "\n", " enrich_field_name \n", "0 Health_MEDHINC_CY \n", "1 ACS_Housing_Summary_rep_MOETOTPOP \n", "2 travelMPI_TADULTBASE \n", "3 Age_by_Sex_by_Race_Profile_rep_TOTPOP_CY \n", "4 Policy_DIVINDX_CY \n", "5 householdincome_ACSMEDHINC \n", "6 householdincome_MOEMEDHINC \n", "7 householdincome_RELMEDHINC \n", "8 Community_Profile_rep_X11001_I \n", "9 ElectronicsInternet_MP09147a_B \n", "10 ElectronicsInternet_MP09147a_I \n", "11 ElectronicsInternet_MP09148a_B \n", "12 ElectronicsInternet_MP09148a_I \n", "13 ElectronicsInternet_MP09134a_B \n", "14 ElectronicsInternet_MP09134a_I \n", "15 education_X11001_X \n", "16 education_X11001_A \n", "17 HealthPersonalCare_MP14129a_B \n", "18 industry_INDAGRI_CY \n", "19 industry_INDMIN_CY \n", "20 industry_INDCONS_CY \n", "21 householdsbyraceofhouseholder_HHWHTHHR10 \n", "22 householdsbyraceofhouseholder_HHBLKHHR10 \n", "23 householdsbyraceofhouseholder_HHAIHHR10 \n", "24 householdsbyraceofhouseholder_HHASNHHR10 \n", "25 householdsbyraceofhouseholder_HHPIHHR10 \n", "26 householdsbyraceofhouseholder_HHOTHHHR10 \n", "27 KeyUSFacts_POPGRW10CY \n", "28 tapestryadultsNEW_TADULT01 \n", "29 tapestryadultsNEW_TADULT02 \n", "30 population_ACSTOTPOP \n", "31 population_RELTOTPOP \n", "32 populationtotals_HHPOP_CY \n", "33 populationtotals_FAMPOP_CY " ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "enrich_vars = usa._ba_cntry.get_enrich_variables_from_iterable(xlsx_df.search_names)\n", "\n", "enrich_vars" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Concantenating for Local Enrich\n", "\n", "The `arcpy.ba.EnrichLayer` tool in ArcGIS Pro needs a semicolon separated string of `enrich_name` values for input. From any of the above results, this can quickly be created from the `enich_name` column." ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Health.MEDHINC_CY;ACS_Housing_Summary_rep.MOETOTPOP;travelMPI.TADULTBASE;Age_by_Sex_by_Race_Profile_rep.TOTPOP_CY;Policy.DIVINDX_CY;householdincome.ACSMEDHINC;householdincome.MOEMEDHINC;householdincome.RELMEDHINC;Community_Profile_rep.X11001_I;ElectronicsInternet.MP09147a_B;ElectronicsInternet.MP09147a_I;ElectronicsInternet.MP09148a_B;ElectronicsInternet.MP09148a_I;ElectronicsInternet.MP09134a_B;ElectronicsInternet.MP09134a_I;education.X11001_X;education.X11001_A;HealthPersonalCare.MP14129a_B;industry.INDAGRI_CY;industry.INDMIN_CY;industry.INDCONS_CY;householdsbyraceofhouseholder.HHWHTHHR10;householdsbyraceofhouseholder.HHBLKHHR10;householdsbyraceofhouseholder.HHAIHHR10;householdsbyraceofhouseholder.HHASNHHR10;householdsbyraceofhouseholder.HHPIHHR10;householdsbyraceofhouseholder.HHOTHHHR10;KeyUSFacts.POPGRW10CY;tapestryadultsNEW.TADULT01;tapestryadultsNEW.TADULT02;population.ACSTOTPOP;population.RELTOTPOP;populationtotals.HHPOP_CY;populationtotals.FAMPOP_CY\n" ] } ], "source": [ "vars_str = ';'.join(enrich_vars.enrich_name.values)\n", "\n", "print(vars_str)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Adding Onto Existing Table\n", "\n", "Also, if we want, we can add the values needed for the Enrich Layer tool onto our existing table." ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
enrich_name
name
MEDHINC_CYHealth.MEDHINC_CY
MOETOTPOPACS_Housing_Summary_rep.MOETOTPOP
TADULTBASEtravelMPI.TADULTBASE
TOTPOP_CYAge_by_Sex_by_Race_Profile_rep.TOTPOP_CY
DIVINDX_CYPolicy.DIVINDX_CY
\n", "
" ], "text/plain": [ " enrich_name\n", "name \n", "MEDHINC_CY Health.MEDHINC_CY\n", "MOETOTPOP ACS_Housing_Summary_rep.MOETOTPOP\n", "TADULTBASE travelMPI.TADULTBASE\n", "TOTPOP_CY Age_by_Sex_by_Race_Profile_rep.TOTPOP_CY\n", "DIVINDX_CY Policy.DIVINDX_CY" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "add_df = enrich_vars[['name', 'enrich_name']].set_index('name')\n", "\n", "add_df.head()" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "RangeIndex: 34 entries, 0 to 33\n", "Data columns (total 6 columns):\n", " # Column Non-Null Count Dtype \n", "--- ------ -------------- ----- \n", " 0 guided_workflow_names 34 non-null object\n", " 1 pro_enrich_names 34 non-null object\n", " 2 fieldnames_arcmap 34 non-null object\n", " 3 fieldnames_pro 34 non-null object\n", " 4 search_names 34 non-null object\n", " 5 enrich_name 34 non-null object\n", "dtypes: object(6)\n", "memory usage: 1.7+ KB\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
guided_workflow_namespro_enrich_namesfieldnames_arcmapfieldnames_prosearch_namesenrich_name
0TOTPOP_CY.2021 Key Demographic Indicators (Esr...populationtotals.totpop_cyTOTPOP_CYpopulationtotals_totpop_cyTOTPOP_CYAge_by_Sex_by_Race_Profile_rep.TOTPOP_CY
1HHPOP_CY.2021 Key Demographic Indicators (Esri...householdtotals.tothh_cyHHPOP_CYpopulationtotals_hhpop_cyHHPOP_CYpopulationtotals.HHPOP_CY
2FAMPOP_CY.2021 Key Demographic Indicators (Esr...populationtotals.fampop_cyFAMPOP_CYpopulationtotals_fampop_cyFAMPOP_CYpopulationtotals.FAMPOP_CY
3MP14129a_B.2021 Health (Market Potential) (Esr...healthpersonalcare.mp14129a_bMP14129a_Bhealthpersonalcare_mp14129a_bMP14129a_BHealthPersonalCare.MP14129a_B
4X11001_X.2021 Education (Consumer Spending) (E...education.x11001_xX11001_Xeducation_x11001_xX11001_Xeducation.X11001_X
\n", "
" ], "text/plain": [ " guided_workflow_names \\\n", "0 TOTPOP_CY.2021 Key Demographic Indicators (Esr... \n", "1 HHPOP_CY.2021 Key Demographic Indicators (Esri... \n", "2 FAMPOP_CY.2021 Key Demographic Indicators (Esr... \n", "3 MP14129a_B.2021 Health (Market Potential) (Esr... \n", "4 X11001_X.2021 Education (Consumer Spending) (E... \n", "\n", " pro_enrich_names fieldnames_arcmap \\\n", "0 populationtotals.totpop_cy TOTPOP_CY \n", "1 householdtotals.tothh_cy HHPOP_CY \n", "2 populationtotals.fampop_cy FAMPOP_CY \n", "3 healthpersonalcare.mp14129a_b MP14129a_B \n", "4 education.x11001_x X11001_X \n", "\n", " fieldnames_pro search_names \\\n", "0 populationtotals_totpop_cy TOTPOP_CY \n", "1 populationtotals_hhpop_cy HHPOP_CY \n", "2 populationtotals_fampop_cy FAMPOP_CY \n", "3 healthpersonalcare_mp14129a_b MP14129a_B \n", "4 education_x11001_x X11001_X \n", "\n", " enrich_name \n", "0 Age_by_Sex_by_Race_Profile_rep.TOTPOP_CY \n", "1 populationtotals.HHPOP_CY \n", "2 populationtotals.FAMPOP_CY \n", "3 HealthPersonalCare.MP14129a_B \n", "4 education.X11001_X " ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "joined_df = xlsx_df.join(add_df, on='search_names')\n", "\n", "joined_df.info()\n", "joined_df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Finally, if all we want is the input values, and the enrich names, we can quickly filter to just these columns." ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
guided_workflow_namesenrich_name
0TOTPOP_CY.2021 Key Demographic Indicators (Esr...Age_by_Sex_by_Race_Profile_rep.TOTPOP_CY
1HHPOP_CY.2021 Key Demographic Indicators (Esri...populationtotals.HHPOP_CY
2FAMPOP_CY.2021 Key Demographic Indicators (Esr...populationtotals.FAMPOP_CY
3MP14129a_B.2021 Health (Market Potential) (Esr...HealthPersonalCare.MP14129a_B
4X11001_X.2021 Education (Consumer Spending) (E...education.X11001_X
5X11001_A.2021 Education (Consumer Spending) (E...education.X11001_A
6X11001_I.2021 Education (Consumer Spending) (E...Community_Profile_rep.X11001_I
7MP09147a_B.2021 Electronics (Market Potential)...ElectronicsInternet.MP09147a_B
8MP09147a_I.2021 Electronics (Market Potential)...ElectronicsInternet.MP09147a_I
9MP09148a_B.2021 Electronics (Market Potential)...ElectronicsInternet.MP09148a_B
10MP09148a_I.2021 Electronics (Market Potential)...ElectronicsInternet.MP09148a_I
11MP09134a_B.2021 Electronics (Market Potential)...ElectronicsInternet.MP09134a_B
12MP09134a_I.2021 Electronics (Market Potential)...ElectronicsInternet.MP09134a_I
13ACSTOTPOP.2015-2019 Key Demographic Indicators...population.ACSTOTPOP
14MOETOTPOP.2015-2019 Key Demographic Indicators...ACS_Housing_Summary_rep.MOETOTPOP
15RELTOTPOP.2015-2019 Key Demographic Indicators...population.RELTOTPOP
16INDAGRI_CY.2021 Labor Force (Esri) (US 2021 Re...industry.INDAGRI_CY
17INDMIN_CY.2021 Labor Force (Esri) (US 2021 Ret...industry.INDMIN_CY
18INDCONS_CY.2021 Labor Force (Esri) (US 2021 Re...industry.INDCONS_CY
19HHWHTHHR10.2010 Households by Race_Hispanic Or...householdsbyraceofhouseholder.HHWHTHHR10
20HHBLKHHR10.2010 Households by Race_Hispanic Or...householdsbyraceofhouseholder.HHBLKHHR10
21HHAIHHR10.2010 Households by Race_Hispanic Ori...householdsbyraceofhouseholder.HHAIHHR10
22HHASNHHR10.2010 Households by Race_Hispanic Or...householdsbyraceofhouseholder.HHASNHHR10
23HHPIHHR10.2010 Households by Race_Hispanic Ori...householdsbyraceofhouseholder.HHPIHHR10
24HHOTHHHR10.2010 Households by Race_Hispanic Or...householdsbyraceofhouseholder.HHOTHHHR10
25TADULTBASE.2021 Tapestry Market Segmentation (...travelMPI.TADULTBASE
26TADULT01.2021 Tapestry Market Segmentation (Ad...tapestryadultsNEW.TADULT01
27TADULT02.2021 Tapestry Market Segmentation (Ad...tapestryadultsNEW.TADULT02
28POPGRW10CY.2021 Key Demographic Indicators (Es...KeyUSFacts.POPGRW10CY
29DIVINDX_CY.2021 Race and Hispanic Origin (Esri...Policy.DIVINDX_CY
30MEDHINC_CY.2021 Income (Esri) (Esri USA 2021)Health.MEDHINC_CY
31ACSMEDHINC.2015-2019 Income (ACS) (Esri USA 20...householdincome.ACSMEDHINC
32MOEMEDHINC.2015-2019 Income (ACS) (Esri USA 20...householdincome.MOEMEDHINC
33RELMEDHINC.2015-2019 Income (ACS) (Esri USA 20...householdincome.RELMEDHINC
\n", "
" ], "text/plain": [ " guided_workflow_names \\\n", "0 TOTPOP_CY.2021 Key Demographic Indicators (Esr... \n", "1 HHPOP_CY.2021 Key Demographic Indicators (Esri... \n", "2 FAMPOP_CY.2021 Key Demographic Indicators (Esr... \n", "3 MP14129a_B.2021 Health (Market Potential) (Esr... \n", "4 X11001_X.2021 Education (Consumer Spending) (E... \n", "5 X11001_A.2021 Education (Consumer Spending) (E... \n", "6 X11001_I.2021 Education (Consumer Spending) (E... \n", "7 MP09147a_B.2021 Electronics (Market Potential)... \n", "8 MP09147a_I.2021 Electronics (Market Potential)... \n", "9 MP09148a_B.2021 Electronics (Market Potential)... \n", "10 MP09148a_I.2021 Electronics (Market Potential)... \n", "11 MP09134a_B.2021 Electronics (Market Potential)... \n", "12 MP09134a_I.2021 Electronics (Market Potential)... \n", "13 ACSTOTPOP.2015-2019 Key Demographic Indicators... \n", "14 MOETOTPOP.2015-2019 Key Demographic Indicators... \n", "15 RELTOTPOP.2015-2019 Key Demographic Indicators... \n", "16 INDAGRI_CY.2021 Labor Force (Esri) (US 2021 Re... \n", "17 INDMIN_CY.2021 Labor Force (Esri) (US 2021 Ret... \n", "18 INDCONS_CY.2021 Labor Force (Esri) (US 2021 Re... \n", "19 HHWHTHHR10.2010 Households by Race_Hispanic Or... \n", "20 HHBLKHHR10.2010 Households by Race_Hispanic Or... \n", "21 HHAIHHR10.2010 Households by Race_Hispanic Ori... \n", "22 HHASNHHR10.2010 Households by Race_Hispanic Or... \n", "23 HHPIHHR10.2010 Households by Race_Hispanic Ori... \n", "24 HHOTHHHR10.2010 Households by Race_Hispanic Or... \n", "25 TADULTBASE.2021 Tapestry Market Segmentation (... \n", "26 TADULT01.2021 Tapestry Market Segmentation (Ad... \n", "27 TADULT02.2021 Tapestry Market Segmentation (Ad... \n", "28 POPGRW10CY.2021 Key Demographic Indicators (Es... \n", "29 DIVINDX_CY.2021 Race and Hispanic Origin (Esri... \n", "30 MEDHINC_CY.2021 Income (Esri) (Esri USA 2021) \n", "31 ACSMEDHINC.2015-2019 Income (ACS) (Esri USA 20... \n", "32 MOEMEDHINC.2015-2019 Income (ACS) (Esri USA 20... \n", "33 RELMEDHINC.2015-2019 Income (ACS) (Esri USA 20... \n", "\n", " enrich_name \n", "0 Age_by_Sex_by_Race_Profile_rep.TOTPOP_CY \n", "1 populationtotals.HHPOP_CY \n", "2 populationtotals.FAMPOP_CY \n", "3 HealthPersonalCare.MP14129a_B \n", "4 education.X11001_X \n", "5 education.X11001_A \n", "6 Community_Profile_rep.X11001_I \n", "7 ElectronicsInternet.MP09147a_B \n", "8 ElectronicsInternet.MP09147a_I \n", "9 ElectronicsInternet.MP09148a_B \n", "10 ElectronicsInternet.MP09148a_I \n", "11 ElectronicsInternet.MP09134a_B \n", "12 ElectronicsInternet.MP09134a_I \n", "13 population.ACSTOTPOP \n", "14 ACS_Housing_Summary_rep.MOETOTPOP \n", "15 population.RELTOTPOP \n", "16 industry.INDAGRI_CY \n", "17 industry.INDMIN_CY \n", "18 industry.INDCONS_CY \n", "19 householdsbyraceofhouseholder.HHWHTHHR10 \n", "20 householdsbyraceofhouseholder.HHBLKHHR10 \n", "21 householdsbyraceofhouseholder.HHAIHHR10 \n", "22 householdsbyraceofhouseholder.HHASNHHR10 \n", "23 householdsbyraceofhouseholder.HHPIHHR10 \n", "24 householdsbyraceofhouseholder.HHOTHHHR10 \n", "25 travelMPI.TADULTBASE \n", "26 tapestryadultsNEW.TADULT01 \n", "27 tapestryadultsNEW.TADULT02 \n", "28 KeyUSFacts.POPGRW10CY \n", "29 Policy.DIVINDX_CY \n", "30 Health.MEDHINC_CY \n", "31 householdincome.ACSMEDHINC \n", "32 householdincome.MOEMEDHINC \n", "33 householdincome.RELMEDHINC " ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "vals_df = joined_df.loc[:,['guided_workflow_names', 'enrich_name']]\n", "\n", "vals_df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Saving Results\n", "\n", "Obviously, copying and pasting out of this notebook is a possibility, but Pandas also includes a large number of output options including back to Excel or one of my personal favorites, [directly to the clipboard](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_clipboard.html#pandas.DataFrame.to_clipboard).\n", "\n", "[Input/Output - Pandas Documentation](https://pandas.pydata.org/pandas-docs/stable/reference/io.html)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Garbage Disposal Function\n", "\n", "Taking advantage of a little iteration we can create a more universal function to try and find matches based on any of these input types. While we are at it, we can also add a bit of convenience, the option to copy the results directly to the clipboard." ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [], "source": [ "from pathlib import Path\n", "from typing import Union, Iterable\n", "\n", "from arcgis.geoenrichment import Country\n", "from pandas.io.clipboards import to_clipboard\n", "\n", "\n", "def get_variable_string(input_names: Union[Iterable[str], Path], country: Country, copy_to_clipboard: bool = False) -> str:\n", " \"\"\"\n", " Based on an iterable input identifying variables, return the string needed to perform enrichment using the Enrich Layer\n", " geoprocessing tool.\n", " \n", " Args:\n", " input_names: Iterable (typically a list) of strings identifying the variable names for enrichment, or a path to a\n", " Feature Class if trying to find variable names from the feature inputs.\n", " country: Country to search in for enrichment variables.\n", " copy_to_clipboard: Whether or not to copy the result to the clipboard. Default is false.\n", " \n", " Return:\n", " String formatted for input into the Enrich Layer geoprocessing tool.\n", " \"\"\"\n", " # if the input names is a path to a feature class, get the column names to use for lookup\n", " if isinstance(input_names, Path):\n", " input_names = [f.name for f in arcpy.ListFields(input_names)]\n", " \n", " # try to find valid enrich variable strings based on the input names\n", " enrich_vars = country._ba_cntry.get_enrich_variables_from_iterable(input_names)\n", " \n", " # if nothing found, try to split on a period, and see if this provides any matches\n", " if len(enrich_vars.index) == 0:\n", " input_names = [var_str.split('.')[0] for var_str in input_names]\n", " \n", " # try again - NOTE: not doing the split before trying, becuase another potential input format can include periods\n", " enrich_vars = country._ba_cntry.get_enrich_variables_from_iterable(input_names)\n", " \n", " # sort for consistency\n", " enrich_vars.sort_values('name', inplace=True)\n", " \n", " # combine the enrich variable strings into a single string\n", " enrich_str = ';'.join(enrich_vars.enrich_name.values)\n", " \n", " # now, if desiring making life easy by dropping right into the clipboard\n", " if copy_to_clipboard:\n", " to_clipboard(enrich_str, excel=False)\n", " \n", " return enrich_str" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now, with this new function we can input the variations from above, and consistently get the results we need. Because of the repeat trying to find variables, you will encounter the warning below if passing in the variables needing to be modified (split on the period)." ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "d:\\projects\\geosaurus\\src\\arcgis\\geoenrichment\\_business_analyst\\_main.py:1157: UserWarning: It appears none of the input enrich enrich_variables were found.\n", " warn(f\"It appears none of the input enrich enrich_variables were found.\")\n" ] }, { "data": { "text/plain": [ "'householdincome.ACSMEDHINC;population.ACSTOTPOP;Policy.DIVINDX_CY;populationtotals.FAMPOP_CY;householdsbyraceofhouseholder.HHAIHHR10;householdsbyraceofhouseholder.HHASNHHR10;householdsbyraceofhouseholder.HHBLKHHR10;householdsbyraceofhouseholder.HHOTHHHR10;householdsbyraceofhouseholder.HHPIHHR10;populationtotals.HHPOP_CY;householdsbyraceofhouseholder.HHWHTHHR10;industry.INDAGRI_CY;industry.INDCONS_CY;industry.INDMIN_CY;Health.MEDHINC_CY;householdincome.MOEMEDHINC;ACS_Housing_Summary_rep.MOETOTPOP;ElectronicsInternet.MP09134a_B;ElectronicsInternet.MP09134a_I;ElectronicsInternet.MP09147a_B;ElectronicsInternet.MP09147a_I;ElectronicsInternet.MP09148a_B;ElectronicsInternet.MP09148a_I;HealthPersonalCare.MP14129a_B;KeyUSFacts.POPGRW10CY;householdincome.RELMEDHINC;population.RELTOTPOP;tapestryadultsNEW.TADULT01;tapestryadultsNEW.TADULT02;travelMPI.TADULTBASE;Age_by_Sex_by_Race_Profile_rep.TOTPOP_CY;education.X11001_A;Community_Profile_rep.X11001_I;education.X11001_X'" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "get_variable_string(xlsx_df.guided_workflow_names, country=usa)" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'householdincome.ACSMEDHINC;population.ACSTOTPOP;Policy.DIVINDX_CY;populationtotals.FAMPOP_CY;householdsbyraceofhouseholder.HHAIHHR10;householdsbyraceofhouseholder.HHASNHHR10;householdsbyraceofhouseholder.HHBLKHHR10;householdsbyraceofhouseholder.HHOTHHHR10;householdsbyraceofhouseholder.HHPIHHR10;populationtotals.HHPOP_CY;householdsbyraceofhouseholder.HHWHTHHR10;industry.INDAGRI_CY;industry.INDCONS_CY;industry.INDMIN_CY;Health.MEDHINC_CY;householdincome.MOEMEDHINC;ACS_Housing_Summary_rep.MOETOTPOP;ElectronicsInternet.MP09134a_B;ElectronicsInternet.MP09134a_I;ElectronicsInternet.MP09147a_B;ElectronicsInternet.MP09147a_I;ElectronicsInternet.MP09148a_B;ElectronicsInternet.MP09148a_I;HealthPersonalCare.MP14129a_B;KeyUSFacts.POPGRW10CY;householdincome.RELMEDHINC;population.RELTOTPOP;tapestryadultsNEW.TADULT01;tapestryadultsNEW.TADULT02;travelMPI.TADULTBASE;Age_by_Sex_by_Race_Profile_rep.TOTPOP_CY;education.X11001_A;Community_Profile_rep.X11001_I;education.X11001_X'" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "get_variable_string(xlsx_df.fieldnames_arcmap, country=usa)" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'householdincome.ACSMEDHINC;veterans.ACSTOTPOP;raceandhispanicorigin.DIVINDX_CY;populationtotals.FAMPOP_CY;householdsbyraceofhouseholder.HHAIHHR10;householdsbyraceofhouseholder.HHASNHHR10;householdsbyraceofhouseholder.HHBLKHHR10;householdsbyraceofhouseholder.HHOTHHHR10;householdsbyraceofhouseholder.HHPIHHR10;householdsbyraceofhouseholder.HHWHTHHR10;industry.INDAGRI_CY;industry.INDCONS_CY;industry.INDMIN_CY;householdincome.MEDHINC_CY;ElectronicsInternet.MP09134a_B;ElectronicsInternet.MP09147a_B;ElectronicsInternet.MP09148a_B;HealthPersonalCare.MP14129a_B;populationtotals.POPGRW10CY;tapestryadultsNEW.TADULT01;tapestryadultsNEW.TADULT02;tapestryadultsNEW.TADULTBASE;householdtotals.TOTHH_CY;populationtotals.TOTPOP_CY;education.X11001_X'" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "get_variable_string(xlsx_df.pro_enrich_names, country=usa)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.11" } }, "nbformat": 4, "nbformat_minor": 4 }