Concat 2 excel files to a new excel file using Python

Question

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.

Actually the problem has been solved by @jezrael. Thanks Charlie :D — Uraforclain, Aug 30 '17 at 09:25

jezrael · Accepted Answer · 2017-08-30T05:56:43.023

2

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)

edited Aug 30 '17 at 05:56

answered Aug 30 '17 at 05:22

jezrael

822,522
95
1,334
1,252

Thanks for the prompt reply jezrael. But I'm not getting it. What do i need to import? – Uraforclain Aug 30 '17 at 05:36
ahhh ic you mean import pandas as pd but i get NaN for Column D mutton the Egg is fine Also how to remove the 0123456 in the first column – Uraforclain Aug 30 '17 at 05:52
Hmmm, I think `ice` has some whitespaces like `' ice'` in one or second excel file, so dont match and get NaNs. Can you check it? – jezrael Aug 30 '17 at 06:02
WOW!! thanks so much!! Quick response and problem solved! thank you so much you made my day! thanks!!! :D – Uraforclain Aug 30 '17 at 06:08
Superrrrr. It is super and I am really glad can help you! Nice day! – jezrael Aug 30 '17 at 06:09
hey jezrael could you look at this for me https://stackoverflow.com/questions/45985358/wrap-text-for-an-entire-column-using-pandas-python :) – Uraforclain Sep 03 '17 at 11:32
thanks for comment, I add solution. Please check it. – jezrael Sep 03 '17 at 12:49

Concat 2 excel files to a new excel file using Python

1 Answers1