Untitled
unknown
plain_text
a month ago
6.2 kB
4
Indexable
import pandas as pd # Load CSVs table1 = pd.read_csv("table1.csv") table2 = pd.read_csv("table2.csv") # Preprocessing: Join Patient First Name and Last Name in Table 2 table2["Patient Name"] = table2["Patient First Name"].str.strip() + " " + table2["Patient Last Name"].str.strip() # Create a dictionary for quick lookup of procedure codes from Table 1 procedure_map = table1.set_index("Claim_ID")["Procedure Codes"].to_dict() # Function to check if any procedure code from Table 2 exists in Table 1's procedure list def procedure_code_match(row): proc_codes = procedure_map.get(row["Claim_ID"], "") if isinstance(proc_codes, str): proc_list = [code.strip() for code in proc_codes.split(",")] # Clean and split else: proc_list = [] return row["Procedure Code"] in proc_list if row["Procedure Code"] else False # Condition 1: Either Benefit Category or Procedure Code must match table2["Condition 1"] = ( (table2["Benefit Category"].isin(["PDRNUS", "Private Duty Nursing(PDN) Services"])) | table2.apply(procedure_code_match, axis=1) ) # Condition 2: Patient Name must match table2["Condition 2"] = table2["Patient Name"].isin(table1["Patient Name"].values) # Create a dictionary for Date lookup date_map = table1.set_index("Claim_ID")["Date"].to_dict() # Condition 3: Line Begin DOS (Date of Service) must be >= Date in Table 1 def date_condition(row): claim_date = date_map.get(row["Claim_ID"]) if claim_date is not None: # Ensure date exists return row["Line Begin DOS"] >= claim_date return False # If no date available, condition fails table2["Condition 3"] = table2.apply(date_condition, axis=1) # Combine all conditions table2["Data Match"] = table2["Condition 1"] & table2["Condition 2"] & table2["Condition 3"] # Create a dictionary for Rejection Code lookup rejection_map = table1.set_index("Claim_ID")["RejectionCode"].to_dict() # Check if Rejection Code matches table2["Rejection Match"] = table2["Claim Rejection Code"] == table2["Claim_ID"].map(rejection_map) # Check if Claim Final STA Code is R, A, or B table2["Final STA Code RAB"] = table2["Claim Final STA Code"].isin(["R", "A", "B"]) # Function to determine the validation result def validation_status(row): if row["Data Match"]: if row["Rejection Match"] and row["Claim Final STA Code"] == "R": return "PASS" # Validation Condition 1 else: return "FAIL" # Validation Condition 2 else: if (not row["Rejection Match"]) and row["Final STA Code RAB"]: return "PASS" # Validation Condition 3 elif row["Claim Rejection Code"]: return "FAIL" # Validation Condition 4 else: return "UNCLEAR" # Edge case handling table2["Result"] = table2.apply(validation_status, axis=1) # Save the final result to a CSV table2.to_csv("validation_results.csv", index=False) print("Validation complete. Results saved to 'validation_results.csv'.") )
Editor is loading...
Leave a Comment