In dataframe, if column is not available in sheet1 then ask to and ignore case and space sensitive-CodePudding

Match all column's names from sheet2 to sheet1 if anything new, ask to add in sheet3, and ignore case sensitive

In Sheet1 "fName" and in Sheet2 "fname" that should consider same (here, case sensitive) and replace name fName from sheet2

Same "Full Name" and in sheet2 "fullname(Ignore space and case sensitive) but that is same So, replace name fName from sheet2

And "Zip" is not in sheet. So, ask if you want to add zip(Y/N) if yes then in sheet3 add separate column

No change in sheet1 and replace column name in sheet2 and add new columns in sheet3

Sheet1

Id fName   Address1     Full Name 
1  Parth   1, street    Parth Hirpara
2  Ujas    10, avenue   Ujas Gajera

Sheet2

Id fname  Zip  fullname
3  Keval  123  Keval Borad 
4  Vivek  456  Keyur Jasani

Sheet2 after changing columns name

Id fName  Zip  Full Name
3  Keval  123  Keval Borad 
4  Vivek  456  Keyur Jasani

Final Data Sheet Columns

Ignore data-related things

Sheet3

Id fName   Address1    Full Name  Zip
...
...
...
...

CodePudding user response：

Try this below code after loading sheet1 & sheet2 as df1 & df2 respectively...

df3 = df1.copy()

lst_col1 = df1.columns.to_list()
lst_col2 = df2.columns.to_list()

lst_col1_temp = [str(col).lower().replace(" ","") for col in lst_col1]
lst_col2_temp = [str(col).lower().replace(" ","") for col in lst_col2]

# Renaming columns in Sheet2 (df2)
for col1 in lst_col1:
    col1_temp = str(col1).lower().replace(" ","")
    if col1 not in lst_col2 and col1_temp in lst_col2_temp:
        lst_col2[lst_col2_temp.index(col1_temp)] = col1
        
# Adding columns in Sheet3 (df3)
for col2 in lst_col2:
    col2_temp = str(col2).lower().replace(" ","")
    if col2_temp not in lst_col1_temp:
        df3[col2] = df2[col2]

Hope this Helps...