Merging two sheets of one excel into single sheet

Question

I am trying to merge 2 sheets from excel.xlsx using python script. I want when sheet1('CLASS') matches to sheet2('C_MAP') then merge DSC and ASC after CLASS in sheet1 or in a new sheet.

To clarify it i am attaching my excel sheets.

this is my Sheet1:

  P_MAP  Q_GROUP    CLASS
0   ram        2     pink
1              4   silver
2  sham        5    green
3              0  default
4   nil        2     pink

it contains P_MAP,Q_GROUP,CLASS

this is my Sheet2:

    C_MAP DSC    ASC
0    pink  h1  match
1   green  h2  match
2  silver  h3  match

it contains C_MAP,ASC,DSC

So, I want when the CLASS matches to C_MAP it should add ASC and DSC and if it doesnt match add NA.

The output i want will be like this:

  P_MAP  Q_GROUP    CLASS DSC    ASC
0   ram        2     pink  h1  match
1              4   silver  h3  match
2  sham        5    green  h2  match
3              0  default   0     NA
4   nil        2     pink  h1  match

Answer 1

What you want is pd.merge:

df1 = pd.read_excel('filename.xlsx', sheet_name='Sheet1') # fill in the correct excel filename
df2 = pd.read_excel('filename.xlsx', sheet_name='Sheet2') # fill in the correct excel filename

df_final = df1.merge(df2, 
                     left_on='CLASS', 
                     right_on='C_MAP', 
                     how='left').drop('C_MAP', axis=1)

df_final.to_excel('filename2.xlsx')

Output

  P_MAP  Q_GROUP    CLASS  DSC    ASC
0   ram        2     pink   h1  match
1              4   silver   h3  match
2  sham        5    green   h2  match
3              0  default  NaN    NaN
4   nil        2     pink   h1  match