1

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
Ian Thompson
  • 2,914
  • 2
  • 18
  • 31
sonali
  • 219
  • 2
  • 11

1 Answers1

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
Erfan
  • 40,971
  • 8
  • 66
  • 78
  • yes same output i need as you have shown in Output.. but i am not getting anything against silver and default. just it is coming for pink and green – sonali Jul 04 '19 at 19:16
  • Then your question is asked badly, since in your example dataframe we get the correct results. You can try to do `df1['CLASS'] = df1['CLASS'].str.strip()` and `df2['C_MAP'] = df2['C_MAP'].str.strip()` before the line `pd.merge..` @sonali – Erfan Jul 04 '19 at 19:18
  • Small tip from my side: it will be good for your learning to read the [following post](https://stackoverflow.com/questions/53645882/pandas-merging-101) about merging @sonali – Erfan Jul 04 '19 at 19:27