Untitled

import pandas as pd

# Read data from Excel file
df = pd.read_excel("output_file (8).xlsx")

# Function to find unique values
def find_unique_values(row):
    set1 = set(str(row['LeftColumn1']).strip().split(','))
    set2 = set(str(row['Rightcolumn1']).strip().split(','))
    unique_to_col1 = ', '.join(sorted(set1 - set2))
    unique_to_col2 = ', '.join(sorted(set2 - set1))
    return pd.Series([unique_to_col1, unique_to_col2], index=['Column3', 'Column4'])

# Apply the function to each row
df[['Column3', 'Column4']] = df.apply(find_unique_values, axis=1)

# Create Column5 by finding common values from both columns
def find_common_values(row):
    set1 = set(str(row['LeftColumn1']).strip().split(','))
    set2 = set(str(row['Rightcolumn1']).strip().split(','))
    common_values = ', '.join(sorted(set1.intersection(set2)))
    return common_values

df['Column5'] = df.apply(find_common_values, axis=1)

# Reorder columns
df = df[['LeftColumn1', 'Rightcolumn1', 'Column3', 'Column4', 'Column5']]

# Save output to Excel file
df.to_excel("okji.xlsx", index=False)
print("Output saved to 'okji.xlsx'")