04 load cross refs

%23!%2Fusr%2Fbin%2Fenv%20python3%0A%22%22%22%0ANAICS%20Cross-References%20Loader%0A%0ALoad%20and%20parse%20cross-references%20(exclusions%2Finclusions)%20for%20classification%20guidance.%0ARun%20as%20script%3A%20python%2004_load_cross_refs.py%0ARun%20interactive%3A%20marimo%20edit%2004_load_cross_refs.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%20Cross-References%20Loader%0A%0A%20%20%20%20This%20notebook%20loads%20cross-references%20from%20%602022_NAICS_Cross_References.xlsx%60.%0A%0A%20%20%20%20Cross-references%20are%20**critical%20for%20classification%20accuracy**%3A%0A%20%20%20%20-%20They%20tell%20you%20what's%20**excluded**%20from%20a%20code%0A%20%20%20%20-%20They%20point%20to%20the%20**correct%20code**%20for%20excluded%20activities%0A%20%20%20%20-%20Example%3A%20%22Retail%20bakeries%22%20are%20excluded%20from%20Manufacturing%20(311)%20%E2%86%92%20classified%20in%20Retail%20(445)%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%20%20%20%20import%20re%0A%0A%20%20%20%20return%20Path%2C%20duckdb%2C%20pd%2C%20re%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%20DB_PATH%20%3D%20PROJECT_ROOT%20%2F%20%22data%22%20%2F%20%22naics.duckdb%22%0A%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%20Cross-References%20File%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%20cross-references%0A%20%20%20%20xref_file%20%3D%20RAW_DATA_DIR%20%2F%20%222022_NAICS_Cross_References.xlsx%22%0A%20%20%20%20df_xref%20%3D%20pd.read_excel(xref_file)%0A%0A%20%20%20%20print(f%22Columns%3A%20%7Blist(df_xref.columns)%7D%22)%0A%20%20%20%20print(f%22Loaded%20%7Blen(df_xref)%7D%20cross-references%22)%0A%0A%20%20%20%20%23%20Rename%20columns%0A%20%20%20%20df_xref%20%3D%20df_xref.rename(columns%3D%7B%0A%20%20%20%20%20%20%20%20%22Code%22%3A%20%22source_code%22%2C%0A%20%20%20%20%20%20%20%20%22Cross-Reference%22%3A%20%22reference_text%22%0A%20%20%20%20%7D)%0A%0A%20%20%20%20%23%20Convert%20code%20to%20string%0A%20%20%20%20df_xref%5B%22source_code%22%5D%20%3D%20df_xref%5B%22source_code%22%5D.astype(str).str.strip()%0A%0A%20%20%20%20df_xref.head()%0A%20%20%20%20return%20(df_xref%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%20Analyze%20Cross-Reference%20Patterns%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(df_xref)%3A%0A%20%20%20%20%23%20Look%20at%20sample%20cross-references%0A%20%20%20%20print(%22Sample%20cross-references%3A%5Cn%22)%0A%20%20%20%20for%20_%2C%20row%20in%20df_xref.head(5).iterrows()%3A%0A%20%20%20%20%20%20%20%20print(f%22Code%20%7Brow%5B'source_code'%5D%7D%3A%22)%0A%20%20%20%20%20%20%20%20print(f%22%20%20%7Brow%5B'reference_text'%5D%5B%3A200%5D%7D...%22)%0A%20%20%20%20%20%20%20%20print()%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(df_xref)%3A%0A%20%20%20%20%23%20Pattern%20analysis%20-%20what%20types%20of%20references%20exist%3F%0A%20%20%20%20def%20detect_reference_type(text%3A%20str)%20-%3E%20str%3A%0A%20%20%20%20%20%20%20%20%22%22%22Detect%20the%20type%20of%20cross-reference.%22%22%22%0A%20%20%20%20%20%20%20%20if%20not%20isinstance(text%2C%20str)%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20%22unknown%22%0A%20%20%20%20%20%20%20%20text_lower%20%3D%20text.lower()%0A%0A%20%20%20%20%20%20%20%20if%20%22are%20classified%20in%22%20in%20text_lower%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20%22excludes%22%0A%20%20%20%20%20%20%20%20elif%20%22see%20industry%22%20in%20text_lower%20or%20%22see%20%22%20in%20text_lower%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20%22see_also%22%0A%20%20%20%20%20%20%20%20elif%20%22includes%22%20in%20text_lower%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20%22includes%22%0A%20%20%20%20%20%20%20%20else%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20%22general%22%0A%0A%20%20%20%20df_xref%5B%22reference_type%22%5D%20%3D%20df_xref%5B%22reference_text%22%5D.apply(detect_reference_type)%0A%0A%20%20%20%20print(%22Reference%20types%3A%22)%0A%20%20%20%20print(df_xref%5B%22reference_type%22%5D.value_counts())%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%20Step%203%3A%20Parse%20Target%20Codes%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(df_xref%2C%20re)%3A%0A%20%20%20%20def%20extract_target_codes(text%3A%20str)%20-%3E%20list%3A%0A%20%20%20%20%20%20%20%20%22%22%22Extract%20NAICS%20codes%20mentioned%20in%20cross-reference%20text.%22%22%22%0A%20%20%20%20%20%20%20%20if%20not%20isinstance(text%2C%20str)%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20%5B%5D%0A%0A%20%20%20%20%20%20%20%20%23%20Pattern%3A%20%22Industry%20XXXXXX%22%20or%20%22U.S.%20Industry%20XXXXXX%22%0A%20%20%20%20%20%20%20%20patterns%20%3D%20%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20r'Industry%5Cs%2B(%5Cd%7B6%7D)'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20r'Industry%5Cs%2B(%5Cd%7B5%7D)'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20r'Industry%5Cs%2B(%5Cd%7B4%7D)'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20r'Subsector%5Cs%2B(%5Cd%7B3%7D)'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20r'Sector%5Cs%2B(%5Cd%7B2%7D)'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20r'classified%20in%5Cs%2B(%5Cd%7B6%7D)'%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20r'classified%20in%5Cs%2B(%5Cd%7B5%7D)'%2C%0A%20%20%20%20%20%20%20%20%5D%0A%0A%20%20%20%20%20%20%20%20codes%20%3D%20%5B%5D%0A%20%20%20%20%20%20%20%20for%20pattern%20in%20patterns%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20matches%20%3D%20re.findall(pattern%2C%20text%2C%20re.IGNORECASE)%0A%20%20%20%20%20%20%20%20%20%20%20%20codes.extend(matches)%0A%0A%20%20%20%20%20%20%20%20return%20list(set(codes))%20%20%23%20Deduplicate%0A%0A%20%20%20%20%23%20Extract%20target%20codes%0A%20%20%20%20df_xref%5B%22target_codes%22%5D%20%3D%20df_xref%5B%22reference_text%22%5D.apply(extract_target_codes)%0A%0A%20%20%20%20%23%20Flatten%20to%20primary%20target%20(first%20mentioned)%0A%20%20%20%20df_xref%5B%22target_code%22%5D%20%3D%20df_xref%5B%22target_codes%22%5D.apply(%0A%20%20%20%20%20%20%20%20lambda%20x%3A%20x%5B0%5D%20if%20x%20else%20None%0A%20%20%20%20)%0A%0A%20%20%20%20%23%20Extract%20excluded%20activity%20from%20%22excludes%22%20type%20references%0A%20%20%20%20%23%20Pattern%3A%20%22Activity%20name--are%20classified%20in%20Industry%20XXXXXX%22%0A%20%20%20%20def%20extract_excluded_activity(row)%20-%3E%20str%3A%0A%20%20%20%20%20%20%20%20if%20row%5B%22reference_type%22%5D%20!%3D%20%22excludes%22%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20None%0A%20%20%20%20%20%20%20%20text%20%3D%20row%5B%22reference_text%22%5D%0A%20%20%20%20%20%20%20%20if%20not%20isinstance(text%2C%20str)%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20None%0A%20%20%20%20%20%20%20%20%23%20Look%20for%20text%20before%20%22--are%20classified%22%20or%20%22are%20classified%22%0A%20%20%20%20%20%20%20%20match%20%3D%20re.search(r'%5E(.%2B%3F)(%3F%3A--%7C%E2%80%94%7C-)%3F%5Cs*are%20classified'%2C%20text%2C%20re.IGNORECASE)%0A%20%20%20%20%20%20%20%20if%20match%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20match.group(1).strip()%0A%20%20%20%20%20%20%20%20return%20None%0A%0A%20%20%20%20df_xref%5B%22excluded_activity%22%5D%20%3D%20df_xref.apply(extract_excluded_activity%2C%20axis%3D1)%0A%0A%20%20%20%20print(%22Sample%20parsed%20references%3A%22)%0A%20%20%20%20df_xref%5B%5B%22source_code%22%2C%20%22reference_type%22%2C%20%22target_code%22%2C%20%22excluded_activity%22%2C%20%22reference_text%22%5D%5D.head(10)%0A%0A%20%20%20%20%23%20Stats%20on%20excluded_activity%20extraction%0A%20%20%20%20has_activity%20%3D%20df_xref%5B%22excluded_activity%22%5D.notna().sum()%0A%20%20%20%20excludes_count%20%3D%20(df_xref%5B%22reference_type%22%5D%20%3D%3D%20%22excludes%22).sum()%0A%20%20%20%20print(f%22%5CnExcluded%20activities%20extracted%3A%20%7Bhas_activity%7D%2F%7Bexcludes_count%7D%20excludes%20refs%20(%7Bhas_activity%2Fexcludes_count%3A.1%25%7D)%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(df_xref)%3A%0A%20%20%20%20%23%20Statistics%20on%20target%20code%20extraction%0A%20%20%20%20has_target%20%3D%20df_xref%5B%22target_code%22%5D.notna().sum()%0A%20%20%20%20total%20%3D%20len(df_xref)%0A%20%20%20%20print(f%22Cross-references%20with%20parsed%20target%20codes%3A%20%7Bhas_target%7D%2F%7Btotal%7D%20(%7Bhas_target%2Ftotal%3A.1%25%7D)%22)%0A%0A%20%20%20%20%23%20Show%20some%20that%20we%20couldn't%20parse%0A%20%20%20%20unparsed%20%3D%20df_xref%5Bdf_xref%5B%22target_code%22%5D.isna()%5D%5B%22reference_text%22%5D.head(5).tolist()%0A%20%20%20%20print(%22%5CnSamples%20without%20parsed%20target%20codes%3A%22)%0A%20%20%20%20for%20text%20in%20unparsed%3A%0A%20%20%20%20%20%20%20%20print(f%22%20%20-%20%7Btext%5B%3A100%5D%7D...%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%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%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%20cross-references%20table%20(drop%20first%20to%20ensure%20clean%20schema)%0A%20%20%20%20conn.execute(%22DROP%20TABLE%20IF%20EXISTS%20naics_cross_references%22)%0A%20%20%20%20conn.execute(%22%22%22%0A%20%20%20%20%20%20%20%20CREATE%20TABLE%20naics_cross_references%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20ref_id%20INTEGER%20PRIMARY%20KEY%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20source_code%20VARCHAR%20NOT%20NULL%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20reference_type%20VARCHAR%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20reference_text%20TEXT%20NOT%20NULL%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20target_code%20VARCHAR%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20excluded_activity%20VARCHAR%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%20(conn%2C)%0A%0A%0A%40app.cell%0Adef%20_(conn%2C%20df_xref)%3A%0A%20%20%20%20%23%20Prepare%20for%20insert%0A%20%20%20%20df_insert%20%3D%20df_xref%5B%5B%22source_code%22%2C%20%22reference_type%22%2C%20%22reference_text%22%2C%20%22target_code%22%2C%20%22excluded_activity%22%5D%5D.copy()%0A%20%20%20%20df_insert%5B%22ref_id%22%5D%20%3D%20range(1%2C%20len(df_insert)%20%2B%201)%0A%20%20%20%20df_insert%20%3D%20df_insert%5B%5B%22ref_id%22%2C%20%22source_code%22%2C%20%22reference_type%22%2C%20%22reference_text%22%2C%20%22target_code%22%2C%20%22excluded_activity%22%5D%5D%0A%0A%20%20%20%20%23%20Convert%20StringDtype%20to%20object%20for%20DuckDB%20compatibility%20(pandas%203.0)%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%0A%20%20%20%20%23%20Insert%0A%20%20%20%20conn.execute(%22INSERT%20INTO%20naics_cross_references%20SELECT%20*%20FROM%20df_insert%22)%0A%0A%20%20%20%20%23%20Verify%0A%20%20%20%20count%20%3D%20conn.execute(%22SELECT%20COUNT(*)%20FROM%20naics_cross_references%22).fetchone()%5B0%5D%0A%20%20%20%20print(f%22Loaded%20%7Bcount%3A%2C%7D%20cross-references%20to%20database%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(conn)%3A%0A%20%20%20%20%23%20Create%20indexes%0A%20%20%20%20conn.execute(%22%22%22%0A%20%20%20%20%20%20%20%20CREATE%20INDEX%20IF%20NOT%20EXISTS%20idx_xref_source%0A%20%20%20%20%20%20%20%20ON%20naics_cross_references(source_code)%0A%20%20%20%20%22%22%22)%0A%0A%20%20%20%20conn.execute(%22%22%22%0A%20%20%20%20%20%20%20%20CREATE%20INDEX%20IF%20NOT%20EXISTS%20idx_xref_target%0A%20%20%20%20%20%20%20%20ON%20naics_cross_references(target_code)%0A%20%20%20%20%22%22%22)%0A%0A%20%20%20%20conn.execute(%22%22%22%0A%20%20%20%20%20%20%20%20CREATE%20INDEX%20IF%20NOT%20EXISTS%20idx_xref_type%0A%20%20%20%20%20%20%20%20ON%20naics_cross_references(reference_type)%0A%20%20%20%20%22%22%22)%0A%0A%20%20%20%20print(%22Created%20search%20indexes%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%20Step%205%3A%20Verify%20Cross-Reference%20Lookups%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(conn)%3A%0A%20%20%20%20%23%20Test%3A%20Get%20exclusions%20for%20a%20specific%20code%0A%20%20%20%20test_code%20%3D%20%22311811%22%20%20%23%20Retail%20Bakeries%0A%20%20%20%20exclusions%20%3D%20conn.execute(f%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20source_code%2C%20reference_type%2C%20target_code%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LEFT(reference_text%2C%20100)%20as%20text_preview%0A%20%20%20%20%20%20%20%20FROM%20naics_cross_references%0A%20%20%20%20%20%20%20%20WHERE%20source_code%20%3D%20'%7Btest_code%7D'%0A%20%20%20%20%22%22%22).fetchdf()%0A%0A%20%20%20%20print(f%22Cross-references%20for%20%7Btest_code%7D%3A%22)%0A%20%20%20%20exclusions%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(conn)%3A%0A%20%20%20%20%23%20Test%3A%20Find%20all%20codes%20that%20reference%20a%20target%0A%20%20%20%20target%20%3D%20%22445110%22%20%20%23%20Supermarkets%0A%20%20%20%20incoming%20%3D%20conn.execute(f%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20source_code%2C%20reference_type%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LEFT(reference_text%2C%2080)%20as%20text_preview%0A%20%20%20%20%20%20%20%20FROM%20naics_cross_references%0A%20%20%20%20%20%20%20%20WHERE%20target_code%20%3D%20'%7Btarget%7D'%0A%20%20%20%20%20%20%20%20LIMIT%2010%0A%20%20%20%20%22%22%22).fetchdf()%0A%0A%20%20%20%20print(f%22Codes%20that%20reference%20%7Btarget%7D%20(Supermarkets)%3A%22)%0A%20%20%20%20incoming%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(conn)%3A%0A%20%20%20%20%23%20Statistics%20by%20reference%20type%0A%20%20%20%20stats%20%3D%20conn.execute(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20reference_type%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20COUNT(*)%20as%20count%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20SUM(CASE%20WHEN%20target_code%20IS%20NOT%20NULL%20THEN%201%20ELSE%200%20END)%20as%20with_target%0A%20%20%20%20%20%20%20%20FROM%20naics_cross_references%0A%20%20%20%20%20%20%20%20GROUP%20BY%20reference_type%0A%20%20%20%20%20%20%20%20ORDER%20BY%20count%20DESC%0A%20%20%20%20%22%22%22).fetchdf()%0A%20%20%20%20print(%22Cross-reference%20statistics%3A%22)%0A%20%20%20%20stats%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(conn)%3A%0A%20%20%20%20conn.close()%0A%20%20%20%20print(%22Database%20connection%20closed.%20Cross-references%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%20cross-references%20with%3A%0A%20%20%20%20-%20Reference%20type%20classification%20(excludes%2C%20see_also%2C%20includes)%0A%20%20%20%20-%20Parsed%20target%20codes%20for%20linked%20navigation%0A%20%20%20%20-%20Indexed%20for%20fast%20lookup%0A%0A%20%20%20%20**Cross-references%20are%20critical%20for%20classification%20accuracy!**%0A%20%20%20%20When%20a%20query%20matches%20an%20exclusion%2C%20we%20should%3A%0A%20%20%20%201.%20Warn%20the%20user%0A%20%20%20%202.%20Suggest%20the%20correct%20target%20code%0A%0A%20%20%20%20**Next%20step%3A**%20Run%20%6005_generate_embeddings.py%60%20to%20create%20vector%20embeddings.%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