import pandas as pd
import numpy as np

# Read both Excel files
rahnamaie = pd.read_excel("1-rahnamaie.xlsx", na_values=['NA'])
parking = pd.read_excel("2-parking.xlsx", na_values=['NA'])

# Inner Join
#inner_join_df = rahnamaie.merge(parking, how="inner", on="motor")
#inner_join_df.to_excel("InnerJoin.xlsx")

# Left Join
#left_join_df = rahnamaie.merge(parking, how="left", on="motor")
#left_join_df.to_excel("LeftJoin.xlsx")
#left_join_df.to_excel("hamsanha.xlsx")

# Right Join
#right_join_df = rahnamaie.merge(parking, how="right", on="motor")
#right_join_df.to_excel("RightJoin.xlsx")

# Outer Join
#outer_join_df = rahnamaie.merge(parking, how="outer", on="motor")
#outer_join_df.to_excel("OuterJoin.xlsx")

df2 = rahnamaie
df1 = parking

#result = df2.loc[~df2['motor'].isin(df1['motor'])]
#result.to_excel('tafavot-parking-mojood-rahnamaie-na-mojood.xlsx',index=False)

#result = df1[~df1['pelak'].isin(df2['pelak'])].dropna()
#common = df1.merge(df2,on=['motor','motor'])
#result = rahnamaie[~rahnamaie.isin(common)].dropna()
#result.to_excel('tafavot.xlsx',index=False)

#x = pd.concat([df1, df2])

#y.to_excel('tafavot.xlsx',index=False)

#result = df1[~df1.index.isin(df1.merge(df2, how='inner', on=['pelak', 'pelak']).index)]

#result2 = result.merge(result.drop_duplicates(), on=['motor','motor'], 
               #how='left', indicator=True)

#result2.to_excel('tafavot.xlsx',index=False)

#df = df1.drop_duplicates().merge(df2.drop_duplicates(), on=df2.columns.to_list(), 
                   #how='left', indicator=True)
#df.loc[df._merge=='left_only',df.columns!='_merge']

#df_temp = df1.merge(df2[['motor', 'pelak']].drop_duplicates(), on=['motor', 'pelak'], how='left', indicator=True)
#res = df_temp.loc[df_temp['_merge'] == 'left_only'].drop('_merge', axis=1)

res = df1[df1['badane'].isin(df2['badane'].unique())]
                   
res2 = df2[df2['badane'].isin(df1['badane'].unique())]               

res.to_excel('tafavot1-dar-parking-hast-rahnamaie-nist-badane.xlsx',index=False)
res2.to_excel('tafavot2-dar-rahnamaie-hast-parking-nist-badane.xlsx',index=False)