import pandas as pd import numpy as np from scipy.sparse import csr_matrix import gensim.downloader as api from sentence_transformers import SentenceTransformer, util from sklearn.feature_extraction.text import CountVectorizer from gensim.utils import simple_preprocess from concurrent.futures import ThreadPoolExecutor import torch device = torch.device("cuda" if torch.cuda.is_available() else "cpu") from sentence_transformers import SentenceTransformer # where we saved the models. due to not able to connect to huggingface.co, we are running the models offline mode model_path = './models' def cosineSimilarity(input_df, mdr_df, model): # model is the model instance, not model name # Encode the existing descriptions from the MDR dataset into embeddings definitionEmbeddingsB = model.encode(input_df['description'].tolist(), convert_to_tensor=True).to(device) definitionEmbeddingsA = model.encode(mdr_df["definition"].tolist(), convert_to_tensor=True).to(device) # Initialize a list to store results results = [] for i, embeddingA in enumerate(definitionEmbeddingsA): similarities = util.pytorch_cos_sim(embeddingA, definitionEmbeddingsB) most_similar_idx = similarities.argmax().item() similarity_score = round(similarities[0][most_similar_idx].item() * 100, 1) result = { "input_name": input_df.iloc[most_similar_idx][["variable"][0]] if ["variable"] and ["variable"][0] in input_df.columns else None, "input_descr": input_df['description'].iloc[most_similar_idx], "mdr_name": mdr_df.iloc[i][["name"][0]] if ["name"] and ["name"][0] in mdr_df.columns else None, "mdr_descr": mdr_df["definition"].iloc[i], "similarity_score": similarity_score } results.append(result) result_df = pd.DataFrame(results) # result_df = pd.read_csv(r"C:\Users\hamme040\Documents\work\ECON\sematch\data_for_testing\results\abs_outcome_noflag.csv") # for demo, faster display return result_df def fast_jaccard_similarity(X1, X2): # intersection is a sparse matrix intersection = X2 @ X1.T # Fast sparse matrix multiplication # X1 and X2 are numpy.matrix, convert to ndarray for newer version of numpy # then convert X3 to back to sparse matrix X3 = np.asarray(X2.sum(axis=1)[:, None]) + np.asarray(X1.sum(axis=1)[None, :]) union = csr_matrix(X3[0]) - intersection return intersection / union # Keep it as a sparse matrix def cosineJaccardSimilarity(input_df, mdr_df, model): # model is the model instance, not model name # Encode the existing descriptions from the MDR dataset into embeddings dfBEmbeddings = model.encode(input_df['description'].tolist(), convert_to_tensor=True).to(device) dfAEmbeddings = model.encode(mdr_df["definition"].tolist(), convert_to_tensor=True).to(device) # Compute cosine similarity similarity_matrix = util.cos_sim(dfAEmbeddings, dfBEmbeddings) dfA = mdr_df dfB = input_df descriptionB='description' descriptionA="definition" variableNameA=["name"] variableNameB=["variable"] rowsToPrintB=[] rowsToPrintA=[] num_matches = 1 # Only find 1 match per description top_n_match_indices = torch.argsort(similarity_matrix, dim=1, descending=True)[:, :num_matches] vectorizer = CountVectorizer(binary=True, stop_words="english") dfAsparse = vectorizer.fit_transform(dfA[descriptionA]) dfBsparse = vectorizer.transform(dfB[descriptionB]) cosine_weight = 0.85 jaccard_weight = 0.15 expanded_rows = [] for row_idx in range(dfAsparse.shape[0]): row = dfA.iloc[row_idx] match_idx = top_n_match_indices[row_idx, 0].item() combined_score = 0.0 cosine_score = 0.0 jaccard_score_value = 0.0 matched_mdr_definition = None matched_row_data = [] if match_idx < dfB.shape[0]: matched_row = dfB.iloc[match_idx] matched_mdr_definition = matched_row[descriptionB] cosine_score = similarity_matrix[row_idx, match_idx].item() jaccard_score_value = np.asarray(fast_jaccard_similarity(dfAsparse[row_idx], dfBsparse[match_idx]))[0, 0] # newer version of convert matrix to array combined_score = (cosine_score * cosine_weight) + (jaccard_score_value * jaccard_weight) # Collect additional row info from datasetB matched_row_data = [matched_row[col] for col in rowsToPrintB] # Combine all data into a single row full_row = [row[col] for col in variableNameA] + [matched_row[col] for col in variableNameB] + [ row[descriptionA], # Original description matched_mdr_definition, # Matched description round(combined_score * 100, 2), # Combined score round(cosine_score * 100, 2), # Cosine similarity score round(jaccard_score_value * 100, 2) # Jaccard similarity score ] full_row.extend([row[col] for col in rowsToPrintA]) full_row.extend(matched_row_data if matched_row_data else [""] * len(rowsToPrintB)) expanded_rows.append(full_row) # Debugging prints to check column alignment expected_columns = ["VariableNameA", "VariableNameB", "descriptionA", "descriptionB", "similarity_score", "Cosine Score", "Jaccard Score"] + rowsToPrintA + rowsToPrintB print(f"Expected columns count: {len(expected_columns)}") print(f"Actual row length: {len(expanded_rows[0]) if expanded_rows else 'No data'}") print(expanded_rows[0]) df_final = pd.DataFrame(expanded_rows, columns=expected_columns).rename(columns={'VariableNameB':'input_name', 'descriptionB':'input_descr', 'VariableNameA':'mdr_name', 'descriptionA':'mdr_descr'}) # re-arange columns cols = ['input_name','input_descr','mdr_name','mdr_descr'] cols_other = [col for col in df_final.columns if col not in cols] df_final = df_final[cols + cols_other ] # df_final = pd.read_csv(r"C:\Users\hamme040\Documents\work\ECON\sematch\data_for_testing\results\abs_outcome_jaccard_cosine.csv") # for demo, faster display return df_final def read_clean_model(input_df, mdr_df, model_select): input_columns_to_check = ["variable", "description"] mdr_columns_to_check = ["name", "definition"] # drop missing, drop dups, trim the spaces around the names/vars, that mess up the display input_df = input_df.dropna(subset=input_columns_to_check) mdr_df = mdr_df.dropna(subset=mdr_columns_to_check) input_df = input_df.drop_duplicates(subset=['variable']) mdr_df = mdr_df.drop_duplicates(subset=['name']) input_df['variable'] = input_df['variable'].str.strip() mdr_df['name'] = mdr_df['name'].str.strip() # Remove place holders like TBD placeholder_vars = ['tbd'] # lower case for var in placeholder_vars: for col in mdr_columns_to_check: to_drop = mdr_df[mdr_df[col].str.lower()==var].index mdr_df = mdr_df.drop(to_drop) for var in placeholder_vars: for col in input_columns_to_check: to_drop = input_df[input_df[col].str.lower()==var].index input_df = input_df.drop(to_drop) # removed derived derived_data_type_list = ['_DVAL', '_DSUM'] mdr_df['derived'] = 'no' mask_derived = (mdr_df['name'].str.contains('|'.join(derived_data_type_list), case=False, na=False)) mdr_df.loc[mask_derived, 'derived'] = 'yes' mdr_df = mdr_df[mdr_df['derived'] == 'no'] input_df['derived'] = 'no' mask_derived = (input_df['variable'].str.contains('|'.join(derived_data_type_list), case=False, na=False)) input_df.loc[mask_derived, 'derived'] = 'yes' input_df = input_df[input_df['derived'] == 'no'] print(input_df.shape) print(mdr_df.shape) ## Model learning ... # model = SentenceTransformer('all-MiniLM-L6-v2').to(device) # mannually load the model due to not able to connect to huggingface.co at running time from census laptop : model = SentenceTransformer(f'{model_path}/all-MiniLM-L6-v2').to(device) if model_select == 'Cosine': result_df = cosineSimilarity(input_df, mdr_df, model) elif model_select == 'CosJaccard': result_df = cosineJaccardSimilarity(input_df, mdr_df, model) else: result_df = pd.DataFrame(data=None, columns=['input_name','input_descr','mdr_name','mdr_descr']) sorted_df = result_df.sort_values(by=['similarity_score'], ascending=[False]) # sorted_df['similarity_score'] = sorted_df['similarity_score'].apply(lambda x: str(x) + '%') not converting to string, otherwise slider function doesn't work sorted_df = sorted_df.drop_duplicates(subset=["input_descr", "mdr_descr"]).reset_index(drop=True) return sorted_df if __name__ == '__main__': print('something') # # Load datasets # file_path_input = "data/raw/ABS-MOPS Variables - December 11 2024.xlsm" # file_path_mdr = "data/raw/mdr Variables 1.xlsx" # input_df = pd.read_excel(file_path_input, sheet_name="Data Sheet", header=12).rename(columns={'Unnamed: 3':'Legacy Variable'}) # mdr_df = pd.read_excel(file_path_mdr) # output_df = read_clean_model(input_df, mdr_df)