build geodata reference from us census bureau

%23%20%2F%2F%2F%20script%0A%23%20requires-python%20%3D%20%22%3E%3D3.14%22%0A%23%20dependencies%20%3D%20%5B%0A%23%20%20%20%20%20%22marimo%3E%3D0.20.4%22%2C%0A%23%20%20%20%20%20%22pandas%3D%3D3.0.1%22%2C%0A%23%20%20%20%20%20%22requests%3D%3D2.32.5%22%2C%0A%23%20%5D%0A%23%20%2F%2F%2F%0A%0Aimport%20marimo%0A%0A__generated_with%20%3D%20%220.20.4%22%0Aapp%20%3D%20marimo.App()%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%20U.S.%20Geography%20Reference%20Builder%20(2020%20Census%20%2B%202023%20CT%20Planning%20Regions)%0A%0A%20%20%20%20*Dataset%3A%20U.S.%20Census%20ANSI%2FFIPS%2FNS%20Codes%20(2020%20release)%20%2B%20Connecticut%20Planning%20Regions%20(2023%2B)*%0A%0A%20%20%20%20Builds%20a%20canonical%20U.S.%20geographic%20reference%20table%20combining%20**state%20and%20county%20reference%20files**%20from%20the%20Census%20Bureau%20and%20extends%20it%20with%20**Connecticut%20planning%20regions%20introduced%20in%202023**.%0A%0A%20%20%20%20**Source**%0A%20%20%20%20U.S.%20Census%20Bureau%20%E2%80%93%20ANSI%2FFIPS%2FNS%20Codes%0A%20%20%20%20https%3A%2F%2Fwww.census.gov%2Flibrary%2Freference%2Fcode-lists%2Fansi.html%0A%0A%20%20%20%20Direct%20downloads%20used%20in%20this%20notebook%3A%0A%0A%20%20%20%20-%20States%3A%20https%3A%2F%2Fwww2.census.gov%2Fgeo%2Fdocs%2Freference%2Fcodes2020%2Fnational_state2020.txt%0A%20%20%20%20-%20Counties%3A%20https%3A%2F%2Fwww2.census.gov%2Fgeo%2Fdocs%2Freference%2Fcodes2020%2Fnational_county2020.txt%0A%0A%20%20%20%20**Dataset**%0A%0A%20%20%20%20-%20Pipe-delimited%20national%20files%20(%60%7C%60)%20for%20states%20and%20counties%0A%20%20%20%20-%20Includes%20all%20**50%20states%2C%20DC%2C%20Puerto%20Rico%2C%20and%20Island%20Areas**%0A%20%20%20%20-%20Adds%20**historical%20Connecticut%20counties**%20and%20**2023%2B%20planning%20regions**%0A%20%20%20%20-%20Relationships%3A%20one%20row%20per%20**county%20%2F%20county-equivalent%20entity**%0A%0A%20%20%20%20**Columns%20(standardized)**%0A%0A%20%20%20%20-%20%60state_name%60%2C%20%60state_abbrev%60%2C%20%60state_fips_2d%60%0A%20%20%20%20-%20%60county_name%60%2C%20%60county_fips_3d%60%2C%20%60county_ns_8d%60%0A%20%20%20%20-%20%60class_fp%60%2C%20%60func_stat%60%0A%20%20%20%20-%20%60full_fips_5d%60%20(combined%20state%2Bcounty%20FIPS)%0A%20%20%20%20-%20%60full_fips_numeric%60%20(numeric%20version%20for%20calculations%2C%20BI%20use)%0A%0A%20%20%20%20**Common%20Uses**%0A%0A%20%20%20%20-%20Geographic%20joins%20across%20**county%20and%20state%20datasets**%0A%20%20%20%20-%20BI-friendly%20tables%20for%20**Tableau%2C%20Excel%2C%20Power%20BI**%0A%20%20%20%20-%20Historical%20vs.%20current%20**Connecticut%20regional%20analysis**%0A%20%20%20%20-%20Reference%20for%20**spatial%2C%20demographic%2C%20and%20policy%20datasets**%0A%0A%20%20%20%20**Outputs**%0A%0A%20%20%20%20-%20%60data%2Fprocessed%2Fcensus_county_equivalent_reference.txt%60%20(raw%20Census-style%2C%20pipe-delimited)%0A%20%20%20%20-%20%60data%2Fprocessed%2Fcensus_county_equivalent_reference.csv%60%20(raw%20Census-style%2C%20comma-delimited)%0A%20%20%20%20-%20%60data%2Fprocessed%2Fus_geography_reference.txt%60%20(curated%20BI-friendly%2C%20pipe-delimited)%0A%20%20%20%20-%20%60data%2Fprocessed%2Fus_geography_reference.csv%60%20(curated%20BI-friendly%2C%20comma-delimited)%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_()%3A%0A%20%20%20%20import%20marimo%20as%20mo%0A%20%20%20%20import%20pandas%20as%20pd%0A%20%20%20%20import%20requests%0A%20%20%20%20from%20pathlib%20import%20Path%0A%0A%20%20%20%20return%20Path%2C%20mo%2C%20pd%2C%20requests%0A%0A%0A%40app.cell%0Adef%20_(Path)%3A%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20%23%201.%20DIRECTORY%20CONFIGURATION%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20BASE_DIR%20%3D%20Path(%22data%22)%0A%20%20%20%20RAW_DIR%20%3D%20BASE_DIR%20%2F%20%22raw%22%20%20%20%20%20%20%20%20%20%20%20%20%23%20Original%20Census%2Fraw%20files%0A%20%20%20%20PROCESSED_DIR%20%3D%20BASE_DIR%20%2F%20%22processed%22%20%23%20Processed%20output%20files%0A%0A%20%20%20%20RAW_DIR.mkdir(parents%3DTrue%2C%20exist_ok%3DTrue)%0A%20%20%20%20PROCESSED_DIR.mkdir(parents%3DTrue%2C%20exist_ok%3DTrue)%0A%20%20%20%20return%20PROCESSED_DIR%2C%20RAW_DIR%0A%0A%0A%40app.cell%0Adef%20_(PROCESSED_DIR%2C%20RAW_DIR%2C%20pd%2C%20requests)%3A%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20%23%202.%20SOURCE%20FILES%20(Census%202020)%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20STATE_URL%20%3D%20%22https%3A%2F%2Fwww2.census.gov%2Fgeo%2Fdocs%2Freference%2Fcodes2020%2Fnational_state2020.txt%22%0A%20%20%20%20COUNTY_URL%20%3D%20%22https%3A%2F%2Fwww2.census.gov%2Fgeo%2Fdocs%2Freference%2Fcodes2020%2Fnational_county2020.txt%22%0A%0A%20%20%20%20STATE_FILE%20%3D%20RAW_DIR%20%2F%20%22national_state2020.txt%22%0A%20%20%20%20COUNTY_FILE%20%3D%20RAW_DIR%20%2F%20%22national_county2020.txt%22%0A%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20%23%203.%20DOWNLOAD%20FUNCTION%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20def%20download_file(url%2C%20path)%3A%0A%20%20%20%20%20%20%20%20%22%22%22Download%20file%20if%20not%20already%20present.%22%22%22%0A%20%20%20%20%20%20%20%20if%20not%20path.exists()%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20print(f%22Downloading%20%7Bpath.name%7D%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20resp%20%3D%20requests.get(url)%0A%20%20%20%20%20%20%20%20%20%20%20%20resp.raise_for_status()%0A%20%20%20%20%20%20%20%20%20%20%20%20path.write_bytes(resp.content)%0A%20%20%20%20%20%20%20%20else%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20print(f%22%7Bpath.name%7D%20already%20exists%20(skipping%20download)%22)%0A%0A%20%20%20%20download_file(STATE_URL%2C%20STATE_FILE)%0A%20%20%20%20download_file(COUNTY_URL%2C%20COUNTY_FILE)%0A%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20%23%204.%20LOAD%20STATE%20REFERENCE%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20states%20%3D%20pd.read_csv(STATE_FILE%2C%20sep%3D%22%7C%22%2C%20dtype%3Dstr)%0A%20%20%20%20states%20%3D%20states.rename(columns%3D%7B%0A%20%20%20%20%20%20%20%20%22STATE%22%3A%20%22state_abbrev%22%2C%0A%20%20%20%20%20%20%20%20%22STATEFP%22%3A%20%22state_fips_2d%22%2C%20%20%20%20%20%23%202-digit%20FIPS%0A%20%20%20%20%20%20%20%20%22STATE_NAME%22%3A%20%22state_name%22%2C%0A%20%20%20%20%20%20%20%20%22STATENS%22%3A%20%22state_ns_8d%22%20%20%20%20%20%20%20%20%23%20State%20NS%20code%20(8%20digits)%0A%20%20%20%20%7D)%0A%20%20%20%20states%5B%22state_fips_2d%22%5D%20%3D%20states%5B%22state_fips_2d%22%5D.str.zfill(2)%0A%0A%20%20%20%20%23%20Keep%20only%20relevant%20columns%0A%20%20%20%20states%20%3D%20states%5B%5B%22state_fips_2d%22%2C%20%22state_abbrev%22%2C%20%22state_name%22%2C%20%22state_ns_8d%22%5D%5D%0A%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20%23%205.%20LOAD%20COUNTY%20REFERENCE%20(Census%202020)%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20counties%20%3D%20pd.read_csv(COUNTY_FILE%2C%20sep%3D%22%7C%22%2C%20dtype%3Dstr)%0A%20%20%20%20counties%20%3D%20counties.rename(columns%3D%7B%0A%20%20%20%20%20%20%20%20%22STATEFP%22%3A%20%22state_fips_2d%22%2C%0A%20%20%20%20%20%20%20%20%22COUNTYFP%22%3A%20%22county_fips_3d%22%2C%20%20%23%203-digit%20FIPS%0A%20%20%20%20%20%20%20%20%22COUNTYNS%22%3A%20%22county_ns_8d%22%2C%20%20%20%20%23%208-digit%20National%20Standard%0A%20%20%20%20%20%20%20%20%22COUNTYNAME%22%3A%20%22county_name%22%2C%0A%20%20%20%20%20%20%20%20%22CLASSFP%22%3A%20%22class_fp%22%2C%0A%20%20%20%20%20%20%20%20%22FUNCSTAT%22%3A%20%22func_stat%22%0A%20%20%20%20%7D)%0A%0A%20%20%20%20%23%20Ensure%20proper%20zero-padding%0A%20%20%20%20counties%5B%22state_fips_2d%22%5D%20%3D%20counties%5B%22state_fips_2d%22%5D.str.zfill(2)%0A%20%20%20%20counties%5B%22county_fips_3d%22%5D%20%3D%20counties%5B%22county_fips_3d%22%5D.str.zfill(3)%0A%20%20%20%20counties%5B%22county_ns_8d%22%5D%20%3D%20counties%5B%22county_ns_8d%22%5D.str.zfill(8)%0A%0A%20%20%20%20%23%20Add%20state%20name%20and%20abbreviation%0A%20%20%20%20counties%20%3D%20counties.merge(states%2C%20on%3D%22state_fips_2d%22%2C%20how%3D%22left%22)%0A%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20%23%206.%20CONNECTICUT%20DATA%20(HISTORICAL%20%2B%202023%2B%20PLANNING%20REGIONS)%0A%20%20%20%20%23%20----------------------------------------------------------%0A%0A%20%20%20%20%23%20Historical%20CT%20counties%20(pre-2023)%0A%20%20%20%20ct_historical_counties%20%3D%20pd.DataFrame(%7B%0A%20%20%20%20%20%20%20%20%22state_fips_2d%22%3A%20%5B%2209%22%5D*8%2C%0A%20%20%20%20%20%20%20%20%22county_fips_3d%22%3A%20%5B%22001%22%2C%22003%22%2C%22005%22%2C%22007%22%2C%22009%22%2C%22011%22%2C%22013%22%2C%22015%22%5D%2C%0A%20%20%20%20%20%20%20%20%22county_ns_8d%22%3A%20%5B%22212794%22%2C%22212338%22%2C%22212796%22%2C%22212797%22%2C%22212798%22%2C%22212799%22%2C%22212668%22%2C%22212801%22%5D%2C%0A%20%20%20%20%20%20%20%20%22county_name%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22Fairfield%20County%22%2C%22Hartford%20County%22%2C%22Litchfield%20County%22%2C%22Middlesex%20County%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22New%20Haven%20County%22%2C%22New%20London%20County%22%2C%22Tolland%20County%22%2C%22Windham%20County%22%0A%20%20%20%20%20%20%20%20%5D%2C%0A%20%20%20%20%20%20%20%20%22class_fp%22%3A%20%5B%22H4%22%5D*8%2C%0A%20%20%20%20%20%20%20%20%22func_stat%22%3A%20%5B%22N%22%5D*8%2C%0A%20%20%20%20%20%20%20%20%22state_abbrev%22%3A%20%5B%22CT%22%5D*8%0A%20%20%20%20%7D)%0A%20%20%20%20ct_historical_counties%5B%22county_ns_8d%22%5D%20%3D%20ct_historical_counties%5B%22county_ns_8d%22%5D.str.zfill(8)%0A%20%20%20%20ct_historical_counties%20%3D%20ct_historical_counties.merge(%0A%20%20%20%20%20%20%20%20states%5B%5B%22state_fips_2d%22%2C%20%22state_name%22%5D%5D%2C%0A%20%20%20%20%20%20%20%20on%3D%22state_fips_2d%22%2C%0A%20%20%20%20%20%20%20%20how%3D%22left%22%0A%20%20%20%20)%0A%0A%20%20%20%20%23%20New%20CT%20planning%20regions%20(2023%2B)%0A%20%20%20%20ct_planning_regions%20%3D%20pd.DataFrame(%7B%0A%20%20%20%20%20%20%20%20%22state_fips_2d%22%3A%20%5B%2209%22%5D*9%2C%0A%20%20%20%20%20%20%20%20%22county_fips_3d%22%3A%20%5B%22110%22%2C%22120%22%2C%22130%22%2C%22140%22%2C%22150%22%2C%22160%22%2C%22170%22%2C%22180%22%2C%22190%22%5D%2C%0A%20%20%20%20%20%20%20%20%22county_ns_8d%22%3A%20%5B%222830244%22%2C%222830245%22%2C%222830246%22%2C%222830249%22%2C%222830250%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%222830251%22%2C%222830252%22%2C%222830253%22%2C%222830254%22%5D%2C%0A%20%20%20%20%20%20%20%20%22county_name%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22Capitol%20Planning%20Region%22%2C%22Greater%20Bridgeport%20Planning%20Region%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22Lower%20Connecticut%20River%20Valley%20Planning%20Region%22%2C%22Naugatuck%20Valley%20Planning%20Region%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22Northeastern%20Connecticut%20Planning%20Region%22%2C%22Northwest%20Hills%20Planning%20Region%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22South%20Central%20Connecticut%20Planning%20Region%22%2C%22Southeastern%20Connecticut%20Planning%20Region%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22Western%20Connecticut%20Planning%20Region%22%0A%20%20%20%20%20%20%20%20%5D%2C%0A%20%20%20%20%20%20%20%20%22class_fp%22%3A%20%5B%22H1%22%5D*9%2C%0A%20%20%20%20%20%20%20%20%22func_stat%22%3A%20%5B%22S%22%5D*9%2C%0A%20%20%20%20%20%20%20%20%22state_abbrev%22%3A%20%5B%22CT%22%5D*9%0A%20%20%20%20%7D)%0A%20%20%20%20ct_planning_regions%5B%22county_ns_8d%22%5D%20%3D%20ct_planning_regions%5B%22county_ns_8d%22%5D.str.zfill(8)%0A%20%20%20%20ct_planning_regions%20%3D%20ct_planning_regions.merge(%0A%20%20%20%20%20%20%20%20states%5B%5B%22state_fips_2d%22%2C%20%22state_name%22%5D%5D%2C%0A%20%20%20%20%20%20%20%20on%3D%22state_fips_2d%22%2C%0A%20%20%20%20%20%20%20%20how%3D%22left%22%0A%20%20%20%20)%0A%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20%23%207.%20BUILD%20CENSUS-STYLE%20STANDARD%20TABLE%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20%23%20Exclude%20official%20CT%20counties%20to%20avoid%20duplicates%0A%20%20%20%20counties_no_ct%20%3D%20counties%5Bcounties%5B%22state_fips_2d%22%5D%20!%3D%20%2209%22%5D%0A%0A%20%20%20%20standard_table%20%3D%20pd.concat(%5Bcounties_no_ct%2C%20ct_historical_counties%2C%20ct_planning_regions%5D%2C%20ignore_index%3DTrue)%0A%0A%20%20%20%20%23%20Create%20full%20FIPS%20(5-digit)%20for%20BI%20and%20reference%0A%20%20%20%20standard_table%5B%22full_fips_5d%22%5D%20%3D%20standard_table%5B%22state_fips_2d%22%5D.str.zfill(2)%20%2B%20standard_table%5B%22county_fips_3d%22%5D.str.zfill(3)%0A%0A%20%20%20%20%23%20Save%20raw%20Census-style%20table%20for%20reference%0A%20%20%20%20%23%20---%2010a.%20Standard%20Census-style%20table%20(raw)%20---%0A%0A%20%20%20%20%23%20TXT%20version%20(pipe-delimited%2C%20preserves%20raw%20structure)%0A%20%20%20%20standard_output_txt%20%3D%20PROCESSED_DIR%20%2F%20%22census_county_equivalent_reference.txt%22%0A%20%20%20%20standard_table.to_csv(%0A%20%20%20%20%20%20%20%20standard_output_txt%2C%0A%20%20%20%20%20%20%20%20sep%3D%22%7C%22%2C%0A%20%20%20%20%20%20%20%20index%3DFalse%0A%20%20%20%20)%0A%20%20%20%20print(%22Standard%20Census-style%20TXT%20table%20created%3A%22%2C%20standard_output_txt)%0A%0A%20%20%20%20%23%20CSV%20version%20(comma-delimited%2C%20quotes%20FIPS%20columns%20to%20preserve%20leading%20zeros)%0A%20%20%20%20standard_output_csv%20%3D%20PROCESSED_DIR%20%2F%20%22census_county_equivalent_reference.csv%22%0A%20%20%20%20standard_table.to_csv(%0A%20%20%20%20%20%20%20%20standard_output_csv%2C%0A%20%20%20%20%20%20%20%20index%3DFalse%0A%20%20%20%20)%0A%20%20%20%20print(%22Standard%20Census-style%20CSV%20table%20created%3A%22%2C%20standard_output_csv)%0A%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20%23%208.%20BUILD%20BI-FRIENDLY%20CURATED%20TABLE%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20curated_table%20%3D%20standard_table.copy()%0A%0A%20%20%20%20%23%20Explicitly%20label%20numeric%20version%20for%20calculations%0A%20%20%20%20curated_table%5B%22full_fips_numeric%22%5D%20%3D%20curated_table%5B%22full_fips_5d%22%5D.astype(int)%0A%0A%20%20%20%20%23%20Reorder%20columns%20with%20explicit%20names%0A%20%20%20%20curated_table%20%3D%20curated_table%5B%0A%20%20%20%20%20%20%20%20%5B%22state_name%22%2C%22state_abbrev%22%2C%22state_fips_2d%22%2C%0A%20%20%20%20%20%20%20%20%20%22county_name%22%2C%22county_fips_3d%22%2C%22county_ns_8d%22%2C%0A%20%20%20%20%20%20%20%20%20%22class_fp%22%2C%22func_stat%22%2C%0A%20%20%20%20%20%20%20%20%20%22full_fips_5d%22%2C%22full_fips_numeric%22%5D%0A%20%20%20%20%5D%0A%0A%20%20%20%20%23%20Save%20curated%20BI-friendly%20table%20(CSV%20ensures%20FIPS%20columns%20remain%20text)%0A%20%20%20%20%23%20CSV%20version%20(primary%20for%20Tableau%2FExcel%2FPower%20BI)%0A%20%20%20%20curated_output_csv%20%3D%20PROCESSED_DIR%20%2F%20%22us_geography_reference.csv%22%0A%20%20%20%20curated_table.to_csv(%0A%20%20%20%20%20%20%20%20curated_output_csv%2C%0A%20%20%20%20%20%20%20%20index%3DFalse%0A%20%20%20%20)%0A%20%20%20%20print(%22Curated%20BI-friendly%20CSV%20table%20created%3A%22%2C%20curated_output_csv)%0A%0A%20%20%20%20%23%20Optional%20TXT%20version%20of%20curated%20table%20(pipe-delimited)%0A%20%20%20%20curated_output_txt%20%3D%20PROCESSED_DIR%20%2F%20%22us_geography_reference.txt%22%0A%20%20%20%20curated_table.to_csv(%0A%20%20%20%20%20%20%20%20curated_output_txt%2C%0A%20%20%20%20%20%20%20%20sep%3D%22%7C%22%2C%0A%20%20%20%20%20%20%20%20index%3DFalse%0A%20%20%20%20)%0A%20%20%20%20print(%22Curated%20BI-friendly%20TXT%20table%20created%3A%22%2C%20curated_output_txt)%0A%20%20%20%20return%20curated_table%2C%20standard_table%0A%0A%0A%40app.cell%0Adef%20_(standard_table)%3A%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20%23%203.%20Display%20DataFrame%20(Standard%20Table)%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20standard_table%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(curated_table)%3A%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20%23%204.%20Display%20DataFrame%20(Curated%20Table)%0A%20%20%20%20%23%20----------------------------------------------------------%0A%20%20%20%20curated_table%0A%20%20%20%20return%0A%0A%0Aif%20__name__%20%3D%3D%20%22__main__%22%3A%0A%20%20%20%20app.run()%0A