01 load codes

%23!%2Fusr%2Fbin%2Fenv%20python3%0A%22%22%22%0ANAICS%20Code%20Loader%0A%0ALoad%20NAICS%202022%20codes%20from%20source%20Excel%20files%20into%20DuckDB.%0ARun%20as%20script%3A%20python%2001_load_codes.py%0ARun%20interactive%3A%20marimo%20edit%2001_load_codes.py%0A%22%22%22%0A%0Aimport%20marimo%0A%0A__generated_with%20%3D%20%220.19.11%22%0Aapp%20%3D%20marimo.App(width%3D%22medium%22)%0A%0A%0A%40app.cell%0Adef%20_()%3A%0A%20%20%20%20import%20marimo%20as%20mo%0A%0A%20%20%20%20return%20(mo%2C)%0A%0A%0A%40app.cell%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(%22%22%22%0A%20%20%20%20%23%20NAICS%202022%20Code%20Loader%0A%0A%20%20%20%20This%20notebook%20loads%20NAICS%20codes%20from%20the%20official%20Census%20Bureau%20Excel%20files%0A%20%20%20%20into%20our%20DuckDB%20database.%20It%20handles%3A%0A%0A%20%20%20%20-%20**2-6%20digit%20codes**%20from%20%602-6%20digit_2022_Codes.xlsx%60%0A%20%20%20%20-%20**Structure%20metadata**%20from%20%602022_NAICS_Structure.xlsx%60%0A%20%20%20%20-%20Hierarchy%20derivation%20(sector%2C%20subsector%2C%20industry%20group%2C%20etc.)%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%20pandas%20as%20pd%0A%20%20%20%20import%20duckdb%0A%20%20%20%20from%20pathlib%20import%20Path%0A%0A%20%20%20%20return%20Path%2C%20duckdb%2C%20pd%0A%0A%0A%40app.cell%0Adef%20_(Path)%3A%0A%20%20%20%20%23%20Configuration%0A%20%20%20%20PROJECT_ROOT%20%3D%20Path(__file__).parent.parent%20if%20%22__file__%22%20in%20dir()%20else%20Path.cwd().parent%0A%20%20%20%20RAW_DATA_DIR%20%3D%20PROJECT_ROOT%20%2F%20%22raw-data%22%0A%20%20%20%20DATA_DIR%20%3D%20PROJECT_ROOT%20%2F%20%22data%22%0A%20%20%20%20DB_PATH%20%3D%20DATA_DIR%20%2F%20%22naics.duckdb%22%0A%0A%20%20%20%20%23%20Ensure%20data%20directory%20exists%0A%20%20%20%20DATA_DIR.mkdir(parents%3DTrue%2C%20exist_ok%3DTrue)%0A%0A%20%20%20%20print(f%22Project%20root%3A%20%7BPROJECT_ROOT%7D%22)%0A%20%20%20%20print(f%22Raw%20data%20dir%3A%20%7BRAW_DATA_DIR%7D%22)%0A%20%20%20%20print(f%22Database%20path%3A%20%7BDB_PATH%7D%22)%0A%20%20%20%20return%20DB_PATH%2C%20RAW_DATA_DIR%0A%0A%0A%40app.cell%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(%22%22%22%0A%20%20%20%20%23%23%20Step%201%3A%20Load%20Raw%20Codes%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(RAW_DATA_DIR%2C%20pd)%3A%0A%20%20%20%20%23%20Load%20the%20main%20codes%20file%0A%20%20%20%20codes_file%20%3D%20RAW_DATA_DIR%20%2F%20%222-6%20digit_2022_Codes.xlsx%22%0A%20%20%20%20df_codes_raw%20%3D%20pd.read_excel(codes_file)%0A%0A%20%20%20%20%23%20Clean%20up%20-%20skip%20header%20row%2C%20rename%20columns%0A%20%20%20%20df_codes_raw%20%3D%20df_codes_raw.dropna(subset%3D%5B%222022%20NAICS%20US%20%20%20Code%22%5D)%0A%20%20%20%20df_codes_raw%20%3D%20df_codes_raw.rename(columns%3D%7B%0A%20%20%20%20%20%20%20%20%22Seq.%20No.%22%3A%20%22seq_no%22%2C%0A%20%20%20%20%20%20%20%20%222022%20NAICS%20US%20%20%20Code%22%3A%20%22code%22%2C%0A%20%20%20%20%20%20%20%20%222022%20NAICS%20US%20Title%22%3A%20%22title%22%0A%20%20%20%20%7D)%0A%0A%20%20%20%20%23%20Convert%20code%20to%20string%20(handle%20float%20formatting%20and%20range%20codes%20like%20%2231-33%22)%0A%20%20%20%20def%20clean_code(x)%3A%0A%20%20%20%20%20%20%20%20if%20pd.isna(x)%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20None%0A%20%20%20%20%20%20%20%20if%20isinstance(x%2C%20(int%2C%20float))%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20str(int(x))%0A%20%20%20%20%20%20%20%20return%20str(x).strip()%0A%0A%20%20%20%20df_codes_raw%5B%22code%22%5D%20%3D%20df_codes_raw%5B%22code%22%5D.apply(clean_code)%0A%0A%20%20%20%20print(f%22Loaded%20%7Blen(df_codes_raw)%7D%20codes%22)%0A%20%20%20%20df_codes_raw.head(10)%0A%20%20%20%20return%20(df_codes_raw%2C)%0A%0A%0A%40app.cell%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(%22%22%22%0A%20%20%20%20%23%23%20Step%202%3A%20Load%20Structure%20Metadata%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(RAW_DATA_DIR%2C%20pd)%3A%0A%20%20%20%20%23%20Load%20structure%20file%20for%20change%20indicators%0A%20%20%20%20structure_file%20%3D%20RAW_DATA_DIR%20%2F%20%222022_NAICS_Structure.xlsx%22%0A%20%20%20%20df_structure_raw%20%3D%20pd.read_excel(structure_file%2C%20skiprows%3D1)%0A%0A%20%20%20%20%23%20Rename%20columns%0A%20%20%20%20df_structure_raw.columns%20%3D%20%5B%22change_indicator%22%2C%20%22code%22%2C%20%22title%22%5D%0A%20%20%20%20df_structure_raw%20%3D%20df_structure_raw.dropna(subset%3D%5B%22code%22%5D)%0A%0A%20%20%20%20%23%20Clean%20code%20column%20-%20remove%20%22T%22%20suffix%20from%20titles%20that%20got%20merged%0A%20%20%20%20df_structure_raw%5B%22code%22%5D%20%3D%20df_structure_raw%5B%22code%22%5D.astype(str).str.strip()%0A%0A%20%20%20%20%23%20Clean%20title%20-%20remove%20trailing%20%22T%22%20(trilateral%20marker)%0A%20%20%20%20df_structure_raw%5B%22title%22%5D%20%3D%20df_structure_raw%5B%22title%22%5D.astype(str).str.rstrip(%22T%22).str.strip()%0A%0A%20%20%20%20print(f%22Loaded%20%7Blen(df_structure_raw)%7D%20structure%20entries%22)%0A%20%20%20%20df_structure_raw.head(10)%0A%20%20%20%20return%20(df_structure_raw%2C)%0A%0A%0A%40app.cell%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(%22%22%22%0A%20%20%20%20%23%23%20Step%203%3A%20Derive%20Hierarchy%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(df_codes_raw%2C%20pd)%3A%0A%20%20%20%20def%20derive_level(code%3A%20str)%20-%3E%20str%3A%0A%20%20%20%20%20%20%20%20%22%22%22Derive%20NAICS%20level%20from%20code%20length.%22%22%22%0A%20%20%20%20%20%20%20%20%23%20Handle%20range%20codes%20like%20%2231-33%22%2C%20%2244-45%22%2C%20%2248-49%22%0A%20%20%20%20%20%20%20%20if%20%22-%22%20in%20code%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20%22sector%22%0A%20%20%20%20%20%20%20%20length%20%3D%20len(code)%0A%20%20%20%20%20%20%20%20levels%20%3D%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%202%3A%20%22sector%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%203%3A%20%22subsector%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%204%3A%20%22industry_group%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%205%3A%20%22naics_industry%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%206%3A%20%22national_industry%22%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20return%20levels.get(length%2C%20%22unknown%22)%0A%0A%20%20%20%20def%20derive_hierarchy(code%3A%20str)%20-%3E%20dict%3A%0A%20%20%20%20%20%20%20%20%22%22%22Derive%20parent%20codes%20at%20each%20level.%22%22%22%0A%20%20%20%20%20%20%20%20%23%20Handle%20range%20codes%20like%20%2231-33%22%20-%20they%20are%20their%20own%20sector%0A%20%20%20%20%20%20%20%20if%20%22-%22%20in%20code%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22sector_code%22%3A%20code%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22subsector_code%22%3A%20None%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22industry_group_code%22%3A%20None%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22naics_industry_code%22%3A%20None%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20return%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22sector_code%22%3A%20code%5B%3A2%5D%20if%20len(code)%20%3E%3D%202%20else%20None%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22subsector_code%22%3A%20code%5B%3A3%5D%20if%20len(code)%20%3E%3D%203%20else%20None%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22industry_group_code%22%3A%20code%5B%3A4%5D%20if%20len(code)%20%3E%3D%204%20else%20None%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22naics_industry_code%22%3A%20code%5B%3A5%5D%20if%20len(code)%20%3E%3D%205%20else%20None%2C%0A%20%20%20%20%20%20%20%20%7D%0A%0A%20%20%20%20%23%20Build%20the%20codes%20dataframe%20with%20hierarchy%0A%20%20%20%20df_codes%20%3D%20df_codes_raw%5B%5B%22code%22%2C%20%22title%22%5D%5D.copy()%0A%20%20%20%20df_codes%5B%22level%22%5D%20%3D%20df_codes%5B%22code%22%5D.apply(derive_level)%0A%0A%20%20%20%20%23%20Add%20hierarchy%20columns%0A%20%20%20%20hierarchy_df%20%3D%20df_codes%5B%22code%22%5D.apply(derive_hierarchy).apply(pd.Series)%0A%20%20%20%20df_codes%20%3D%20pd.concat(%5Bdf_codes%2C%20hierarchy_df%5D%2C%20axis%3D1)%0A%0A%20%20%20%20%23%20Clean%20title%20-%20remove%20trailing%20%22T%22%20(trilateral%20marker)%0A%20%20%20%20df_codes%5B%22title%22%5D%20%3D%20df_codes%5B%22title%22%5D.str.rstrip(%22T%22).str.strip()%0A%0A%20%20%20%20print(f%22%5CnCodes%20by%20level%3A%22)%0A%20%20%20%20print(df_codes%5B%22level%22%5D.value_counts().sort_index())%0A%20%20%20%20df_codes.head(10)%0A%20%20%20%20return%20(df_codes%2C)%0A%0A%0A%40app.cell%0Adef%20_(df_codes%2C%20df_structure_raw)%3A%0A%20%20%20%20%23%20Merge%20in%20change%20indicators%20from%20structure%20file%0A%20%20%20%20structure_lookup%20%3D%20df_structure_raw%5B%5B%22code%22%2C%20%22change_indicator%22%5D%5D.drop_duplicates()%0A%20%20%20%20df_final%20%3D%20df_codes.merge(structure_lookup%2C%20on%3D%22code%22%2C%20how%3D%22left%22)%0A%0A%20%20%20%20%23%20Add%20trilateral%20flag%20(all%20NAICS%20codes%20are%20trilateral%20by%20default)%0A%20%20%20%20df_final%5B%22is_trilateral%22%5D%20%3D%20True%0A%0A%20%20%20%20print(f%22%5CnChange%20indicators%20found%3A%22)%0A%20%20%20%20print(df_final%5B%22change_indicator%22%5D.value_counts(dropna%3DFalse))%0A%20%20%20%20df_final.head(10)%0A%20%20%20%20return%20(df_final%2C)%0A%0A%0A%40app.cell%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(%22%22%22%0A%20%20%20%20%23%23%20Step%204%3A%20Load%20to%20Database%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(DB_PATH%2C%20df_final%2C%20duckdb)%3A%0A%20%20%20%20%23%20Connect%20to%20database%0A%20%20%20%20conn%20%3D%20duckdb.connect(str(DB_PATH))%0A%0A%20%20%20%20%23%20Create%20schema%20if%20not%20exists%20(from%20our%20database.py)%0A%20%20%20%20conn.execute(%22%22%22%0A%20%20%20%20%20%20%20%20CREATE%20TABLE%20IF%20NOT%20EXISTS%20naics_nodes%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20node_code%20VARCHAR%20PRIMARY%20KEY%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20level%20VARCHAR%20NOT%20NULL%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20title%20VARCHAR%20NOT%20NULL%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20description%20TEXT%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20sector_code%20VARCHAR%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20subsector_code%20VARCHAR%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20industry_group_code%20VARCHAR%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20naics_industry_code%20VARCHAR%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20raw_embedding_text%20TEXT%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20change_indicator%20VARCHAR%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20is_trilateral%20BOOLEAN%20DEFAULT%20true%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%22%22%22)%0A%0A%20%20%20%20%23%20Clear%20existing%20data%20and%20insert%0A%20%20%20%20conn.execute(%22DELETE%20FROM%20naics_nodes%22)%0A%0A%20%20%20%20%23%20Prepare%20dataframe%20for%20insert%0A%20%20%20%20df_insert%20%3D%20df_final.rename(columns%3D%7B%22code%22%3A%20%22node_code%22%7D)%0A%20%20%20%20df_insert%20%3D%20df_insert%5B%5B%0A%20%20%20%20%20%20%20%20%22node_code%22%2C%20%22level%22%2C%20%22title%22%2C%20%22sector_code%22%2C%20%22subsector_code%22%2C%0A%20%20%20%20%20%20%20%20%22industry_group_code%22%2C%20%22naics_industry_code%22%2C%20%22change_indicator%22%2C%20%22is_trilateral%22%0A%20%20%20%20%5D%5D%0A%0A%20%20%20%20%23%20Convert%20dtypes%20for%20DuckDB%20compatibility%20(pandas%203.0%20uses%20StringDtype%20by%20default)%0A%20%20%20%20for%20col%20in%20df_insert.select_dtypes(include%3D%5B%22string%22%5D).columns%3A%0A%20%20%20%20%20%20%20%20df_insert%5Bcol%5D%20%3D%20df_insert%5Bcol%5D.astype(object)%0A%20%20%20%20df_insert%5B%22is_trilateral%22%5D%20%3D%20df_insert%5B%22is_trilateral%22%5D.astype(bool)%0A%0A%20%20%20%20%23%20Insert%20using%20DuckDB's%20DataFrame%20integration%20with%20explicit%20columns%0A%20%20%20%20conn.execute(%22%22%22%0A%20%20%20%20%20%20%20%20INSERT%20INTO%20naics_nodes%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20node_code%2C%20level%2C%20title%2C%20sector_code%2C%20subsector_code%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20industry_group_code%2C%20naics_industry_code%2C%20change_indicator%2C%20is_trilateral%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20SELECT%20*%20FROM%20df_insert%0A%20%20%20%20%22%22%22)%0A%0A%20%20%20%20%23%20Verify%0A%20%20%20%20result%20%3D%20conn.execute(%22SELECT%20level%2C%20COUNT(*)%20as%20cnt%20FROM%20naics_nodes%20GROUP%20BY%20level%20ORDER%20BY%20level%22).fetchall()%0A%20%20%20%20print(%22Loaded%20to%20database%3A%22)%0A%20%20%20%20for%20level%2C%20cnt%20in%20result%3A%0A%20%20%20%20%20%20%20%20print(f%22%20%20%7Blevel%7D%3A%20%7Bcnt%7D%22)%0A%0A%20%20%20%20total%20%3D%20conn.execute(%22SELECT%20COUNT(*)%20FROM%20naics_nodes%22).fetchone()%5B0%5D%0A%20%20%20%20print(f%22%5CnTotal%20codes%3A%20%7Btotal%7D%22)%0A%20%20%20%20return%20(conn%2C)%0A%0A%0A%40app.cell%0Adef%20_(conn)%3A%0A%20%20%20%20%23%20Sample%20verification%0A%20%20%20%20sample%20%3D%20conn.execute(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20node_code%2C%20level%2C%20title%2C%20sector_code%2C%20change_indicator%0A%20%20%20%20%20%20%20%20FROM%20naics_nodes%0A%20%20%20%20%20%20%20%20WHERE%20level%20%3D%20'national_industry'%0A%20%20%20%20%20%20%20%20LIMIT%2010%0A%20%20%20%20%22%22%22).fetchdf()%0A%20%20%20%20sample%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(conn)%3A%0A%20%20%20%20%23%20Close%20connection%0A%20%20%20%20conn.close()%0A%20%20%20%20print(%22Database%20connection%20closed.%20Codes%20loaded%20successfully!%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(%22%22%22%0A%20%20%20%20%23%23%20Summary%0A%0A%20%20%20%20Loaded%20NAICS%202022%20codes%20with%3A%0A%20%20%20%20-%205-level%20hierarchy%20(Sector%20%E2%86%92%20National%20Industry)%0A%20%20%20%20-%20Change%20indicators%20from%202017%20revision%0A%20%20%20%20-%20Trilateral%20agreement%20flags%0A%0A%20%20%20%20**Next%20step%3A**%20Run%20%6002_load_descriptions.py%60%20to%20add%20full%20descriptions.%0A%20%20%20%20%22%22%22)%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