03 load index terms

%23!%2Fusr%2Fbin%2Fenv%20python3%0A%22%22%22%0ANAICS%20Index%20Terms%20Loader%0A%0ALoad%2020%2C398%20official%20index%20terms%20from%20Census%20Bureau.%0ARun%20as%20script%3A%20python%2003_load_index_terms.py%0ARun%20interactive%3A%20marimo%20edit%2003_load_index_terms.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%20Index%20Terms%20Loader%0A%0A%20%20%20%20This%20notebook%20loads%20the%20official%20**20%2C398%20index%20terms**%20from%0A%20%20%20%20%602022_NAICS_Index_File.xlsx%60.%0A%0A%20%20%20%20Index%20terms%20are%3A%0A%20%20%20%20-%20Official%20search%20keywords%20from%20Census%20Bureau%0A%20%20%20%20-%20Mapped%20to%20specific%206-digit%20NAICS%20codes%0A%20%20%20%20-%20Critical%20for%20accurate%20classification%20(boost%20search%20hits)%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%20Index%20Terms%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%20index%20terms%0A%20%20%20%20index_file%20%3D%20RAW_DATA_DIR%20%2F%20%222022_NAICS_Index_File.xlsx%22%0A%20%20%20%20df_index%20%3D%20pd.read_excel(index_file)%0A%0A%20%20%20%20print(f%22Columns%3A%20%7Blist(df_index.columns)%7D%22)%0A%20%20%20%20print(f%22Loaded%20%7Blen(df_index)%7D%20index%20terms%22)%0A%0A%20%20%20%20%23%20Rename%20columns%0A%20%20%20%20df_index%20%3D%20df_index.rename(columns%3D%7B%0A%20%20%20%20%20%20%20%20%22NAICS22%22%3A%20%22naics_code%22%2C%0A%20%20%20%20%20%20%20%20%22INDEX%20ITEM%20DESCRIPTION%22%3A%20%22index_term%22%0A%20%20%20%20%7D)%0A%0A%20%20%20%20%23%20Convert%20code%20to%20string%0A%20%20%20%20df_index%5B%22naics_code%22%5D%20%3D%20df_index%5B%22naics_code%22%5D.astype(str).str.strip()%0A%0A%20%20%20%20df_index.head(10)%0A%20%20%20%20return%20(df_index%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%20Index%20Terms%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(df_index)%3A%0A%20%20%20%20%23%20Distribution%20of%20terms%20by%20code%0A%20%20%20%20terms_per_code%20%3D%20df_index.groupby(%22naics_code%22).size()%0A%20%20%20%20print(%22Index%20terms%20per%20code%20statistics%3A%22)%0A%20%20%20%20print(terms_per_code.describe())%0A%0A%20%20%20%20print(f%22%5CnCodes%20with%20most%20terms%3A%22)%0A%20%20%20%20print(terms_per_code.sort_values(ascending%3DFalse).head(10))%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(df_index)%3A%0A%20%20%20%20%23%20Sample%20terms%20for%20a%20specific%20code%0A%20%20%20%20sample_code%20%3D%20%22445110%22%20%20%23%20Supermarkets%0A%20%20%20%20sample_terms%20%3D%20df_index%5Bdf_index%5B%22naics_code%22%5D%20%3D%3D%20sample_code%5D%5B%22index_term%22%5D.tolist()%0A%20%20%20%20print(f%22%5CnIndex%20terms%20for%20%7Bsample_code%7D%20(Supermarkets)%3A%22)%0A%20%20%20%20for%20term%20in%20sample_terms%5B%3A15%5D%3A%0A%20%20%20%20%20%20%20%20print(f%22%20%20-%20%7Bterm%7D%22)%0A%20%20%20%20if%20len(sample_terms)%20%3E%2015%3A%0A%20%20%20%20%20%20%20%20print(f%22%20%20...%20and%20%7Blen(sample_terms)%20-%2015%7D%20more%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%203%3A%20Normalize%20Terms%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(df_index%2C%20re)%3A%0A%20%20%20%20def%20normalize_term(term%3A%20str)%20-%3E%20str%3A%0A%20%20%20%20%20%20%20%20%22%22%22Normalize%20term%20for%20search%20matching.%22%22%22%0A%20%20%20%20%20%20%20%20if%20not%20isinstance(term%2C%20str)%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%20%22%22%0A%20%20%20%20%20%20%20%20%23%20Lowercase%0A%20%20%20%20%20%20%20%20term%20%3D%20term.lower()%0A%20%20%20%20%20%20%20%20%23%20Remove%20punctuation%20except%20hyphens%0A%20%20%20%20%20%20%20%20term%20%3D%20re.sub(r'%5B%5E%5Cw%5Cs-%5D'%2C%20''%2C%20term)%0A%20%20%20%20%20%20%20%20%23%20Collapse%20whitespace%0A%20%20%20%20%20%20%20%20term%20%3D%20re.sub(r'%5Cs%2B'%2C%20'%20'%2C%20term).strip()%0A%20%20%20%20%20%20%20%20return%20term%0A%0A%20%20%20%20%23%20Add%20normalized%20column%0A%20%20%20%20df_index%5B%22term_normalized%22%5D%20%3D%20df_index%5B%22index_term%22%5D.apply(normalize_term)%0A%0A%20%20%20%20%23%20Add%20term_id%0A%20%20%20%20df_index%5B%22term_id%22%5D%20%3D%20range(1%2C%20len(df_index)%20%2B%201)%0A%0A%20%20%20%20print(%22Sample%20normalized%20terms%3A%22)%0A%20%20%20%20df_index%5B%5B%22index_term%22%2C%20%22term_normalized%22%5D%5D.head(10)%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%20index%20terms%20table%20if%20not%20exists%0A%20%20%20%20conn.execute(%22%22%22%0A%20%20%20%20%20%20%20%20CREATE%20TABLE%20IF%20NOT%20EXISTS%20naics_index_terms%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20term_id%20INTEGER%20PRIMARY%20KEY%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20naics_code%20VARCHAR%20NOT%20NULL%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20index_term%20VARCHAR%20NOT%20NULL%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20term_normalized%20VARCHAR%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%20and%20insert%0A%20%20%20%20conn.execute(%22DELETE%20FROM%20naics_index_terms%22)%0A%20%20%20%20return%20(conn%2C)%0A%0A%0A%40app.cell%0Adef%20_(conn%2C%20df_index)%3A%0A%20%20%20%20%23%20Prepare%20for%20insert%0A%20%20%20%20df_insert%20%3D%20df_index%5B%5B%22term_id%22%2C%20%22naics_code%22%2C%20%22index_term%22%2C%20%22term_normalized%22%5D%5D.copy()%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_index_terms%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_index_terms%22).fetchone()%5B0%5D%0A%20%20%20%20print(f%22Loaded%20%7Bcount%3A%2C%7D%20index%20terms%20to%20database%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(conn)%3A%0A%20%20%20%20%23%20Create%20index%20for%20fast%20search%0A%20%20%20%20conn.execute(%22%22%22%0A%20%20%20%20%20%20%20%20CREATE%20INDEX%20IF%20NOT%20EXISTS%20idx_index_terms_code%0A%20%20%20%20%20%20%20%20ON%20naics_index_terms(naics_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_index_terms_normalized%0A%20%20%20%20%20%20%20%20ON%20naics_index_terms(term_normalized)%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%20Update%20Embedding%20Text%20with%20Index%20Terms%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%20Add%20top%20index%20terms%20to%20raw_embedding_text%20for%20better%20semantic%20search%0A%20%20%20%20%23%20Get%20top%2010%20index%20terms%20per%20code%0A%20%20%20%20conn.execute(%22%22%22%0A%20%20%20%20%20%20%20%20WITH%20ranked_terms%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20naics_code%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20index_term%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ROW_NUMBER()%20OVER%20(PARTITION%20BY%20naics_code%20ORDER%20BY%20LENGTH(index_term)%20DESC)%20as%20rn%0A%20%20%20%20%20%20%20%20%20%20%20%20FROM%20naics_index_terms%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20aggregated%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20naics_code%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20STRING_AGG(index_term%2C%20'%2C%20'%20ORDER%20BY%20rn)%20as%20top_terms%0A%20%20%20%20%20%20%20%20%20%20%20%20FROM%20ranked_terms%0A%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20rn%20%3C%3D%2010%0A%20%20%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20naics_code%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20UPDATE%20naics_nodes%0A%20%20%20%20%20%20%20%20SET%20raw_embedding_text%20%3D%20raw_embedding_text%20%7C%7C%20'%20%7C%20Index%3A%20'%20%7C%7C%20aggregated.top_terms%0A%20%20%20%20%20%20%20%20FROM%20aggregated%0A%20%20%20%20%20%20%20%20WHERE%20naics_nodes.node_code%20%3D%20aggregated.naics_code%0A%20%20%20%20%22%22%22)%0A%0A%20%20%20%20print(%22Updated%20embedding%20text%20with%20index%20terms%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(conn)%3A%0A%20%20%20%20%23%20Verify%20embedding%20text%20update%0A%20%20%20%20sample%20%3D%20conn.execute(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20node_code%2C%20title%2C%20LENGTH(raw_embedding_text)%20as%20embed_len%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%20ORDER%20BY%20embed_len%20DESC%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%20Statistics%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%20COUNT(DISTINCT%20naics_code)%20as%20codes_with_terms%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20COUNT(*)%20as%20total_terms%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20ROUND(AVG(cnt)%2C%201)%20as%20avg_terms_per_code%0A%20%20%20%20%20%20%20%20FROM%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20naics_code%2C%20COUNT(*)%20as%20cnt%0A%20%20%20%20%20%20%20%20%20%20%20%20FROM%20naics_index_terms%0A%20%20%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20naics_code%0A%20%20%20%20%20%20%20%20)%20t%0A%20%20%20%20%22%22%22).fetchdf()%0A%20%20%20%20print(%22Index%20term%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.%20Index%20terms%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%20**20%2C398%20official%20index%20terms**%20with%3A%0A%20%20%20%20-%20Normalized%20versions%20for%20search%20matching%0A%20%20%20%20-%20Search%20indexes%20for%20fast%20lookup%0A%20%20%20%20-%20Updated%20embedding%20text%20with%20top%20terms%0A%0A%20%20%20%20**Next%20step%3A**%20Run%20%6004_load_cross_refs.py%60%20to%20load%20cross-references.%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