Untitled

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'.")
                                                                                                                                                                
                                )