Untitled

# This is a sample Python script.
from datetime import datetime

import numpy as np
import pandas as pd


def load_xlsx(path):
    return pd.read_excel(path, index_col=0, header=[0, 1])

def rest_index_and_drop_level(df):
    r = df.reset_index(level=0, names="Name")
    df_copy = r.copy()
    df_copy.columns = df_copy.columns.droplevel()
    return df_copy

# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    iplan_file = load_xlsx(r"C:\Users\galdahan\OneDrive - Microsoft\Desktop\iplan\iplan.xls")
    wiwi_file = load_xlsx(r"C:\Users\galdahan\OneDrive - Microsoft\Desktop\wiwi\guests-iplan.xls")

    df_singlelevel = rest_index_and_drop_level(iplan_file)
    df_singlelevel2 = rest_index_and_drop_level(wiwi_file)

    # Merge DataFrames based on the "Name" column
    merged_df = pd.merge(df_singlelevel, df_singlelevel2, on='', how='outer', suffixes=('_df1', '_df2'))
    # Filter rows where the values in the "Name" column are different
    different_names_df = merged_df[merged_df[''].isin(df_singlelevel['']) ^ merged_df[''].isin(df_singlelevel2[''])]

    missing_lines_in_iplan = wiwi_file.loc[different_names_df[""].to_list()]

    print("missing values are: {}  ".format(different_names_df[""].to_list()))
    missing_lines_in_iplan.apply(lambda x: x.map(str))

    missing_lines_in_iplan.to_excel(r"C:\Users\galdahan\Downloads\guests-iplan_wiwi_" +
                                      datetime.now().strftime("%Y_%m_%d-%I_%M_%S_%p") +
                                      ".xls",
                                      header=[0, 1],
                                      engine='openpyxl')