Untitled

import pandas as pd

# Load the files
file1 = pd.read_csv("file1.csv")  # Replace with actual file path
file2 = pd.read_csv("file2.csv")  # Replace with actual file path
file3 = pd.read_csv("file3.csv")  # Replace with actual file path

# Ensure relevant columns are treated as strings to avoid issues
file1["GP_LGD_CODE"] = file1["GP_LGD_CODE"].astype(str)
file2["GP_LGD_CODE"] = file2["GP_LGD_CODE"].astype(str)
file2["pincode"] = file2["pincode"].astype(str)
file3["GP_LGD_CODE"] = file3["GP_LGD_CODE"].astype(str)
file3["25k pin code"] = file3["25k pin code"].astype(str)

# Initialize the remarks column
file1["Remarks"] = ""

for index, row in file1.iterrows():
    gp_lgd_code = row["GP_LGD_CODE"]

    # Step 1: Filter file2 based on GP_LGD_CODE
    filtered_file2 = file2[file2["GP_LGD_CODE"] == gp_lgd_code]
    count_file2 = len(filtered_file2)

    if count_file2 == 0:
        continue  # Skip if no matching GP_LGD_CODE found in file2

    # Step 2: Search in file3 for the same GP_LGD_CODE
    filtered_file3 = file3[file3["GP_LGD_CODE"] == gp_lgd_code]

    if filtered_file3.empty:
        continue  # Skip if no match found in file3

    # Step 3: Get pincode from file3
    pincode = filtered_file3.iloc[0]["25k pin code"]

    # Step 4: Search in file2 based on pincode
    filtered_by_pincode = file2[file2["pincode"] == pincode]
    count_pincode = len(filtered_by_pincode)

    # Step 5: Compute the difference and generate remark
    reduced_count = count_file2 - count_pincode
    remark = f"Reduced count by {reduced_count} acc to pincode {count_pincode}"
    file1.at[index, "Remarks"] = remark

# Save the output
file1.to_csv("output.csv", index=False)
print("Processing complete. Output saved as output.csv")