CODEWITHSUNDEEP
excelpython-3.xpandasopenpyxlxlsxwriter
Uraforclain
Uraforclain

Reputation: 365

Concat 2 excel files to a new excel file using Python

Excel file 1:

ColumnA   ColumnB    ColumnC
1.        ice        Chicken
2.        ice        Butter
3.        ice        Mutton
4.        fire       Spinach
5.        fire       Beef 
6.        fire       Cucumber
7.        fire       Egg

Excel file 2:

ColumnA    ColumnB   
ice        Mutton
fire       Egg

I have 2 excel files. Using python i want to combine both excel files together to look like this: 

ColumnA   ColumnB    ColumnC    ColumnD
1.        ice        Chicken    Mutton
2.        ice        Butter     Mutton
3.        ice        Mutton     Mutton
4.        fire       Spinach    Egg
5.        fire       Beef       Egg
6.        fire       Cucumber   Egg
7.        fire       Egg        Egg

What i want in the end is to create a new excel file that has all 3 columns of excel file 1, and ONLY column B of excel file 2 based on the values of column A that is similar to excel file 1 Column B. This new column will be Column D.

I searched for hours only getting how to compare two excel files to delete the differences. Any help would be appreciated.

Upvotes: 0

Views: 694

Answers (1)

jezrael
jezrael

Reputation: 862801

I think you need first read_excel for DataFrames and then map and last to_excel:

import pandas as pd 

df1 = pd.read_excel('file1.xlsx')
df2 = pd.read_excel('file2.xlsx')

s = df2.set_index('ColumnA')['ColumnB']
print (s)
ColumnA
ice     Mutton
fire       Egg
Name: ColumnB, dtype: object

df1['ColumnD'] = df1['ColumnB'].map(s)
print (df1)
   ColumnA ColumnB   ColumnC ColumnD
0      1.0     ice   Chicken  Mutton
1      2.0     ice    Butter  Mutton
2      3.0     ice    Mutton  Mutton
3      4.0    fire   Spinach     Egg
4      5.0    fire      Beef     Egg
5      6.0    fire  Cucumber     Egg
6      7.0    fire       Egg     Egg


df1.to_excel('file3.excel',index=False)

Upvotes: 2

Related Questions