How to merge two Excel files in Python?

Question

I want to merge two excel files in Python.

File1:

Number  - Date
11      - 2020-10-10
2       - 2020-10-11
30      - 2020-10-11
14      - 2020-10-11

File2:

Number - Type
19     - 110
23     - 110
65     - 110
2      - 134
14     - 260
31     - 260
30     - 299
11     - 299

This is what I tried:

import pandas as pd

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

df1['Type'] = df1['Number'].map(df2.set_index('Number')['Type'])

This is what I got:

InvalidIndexError: Reindexing only valid with uniquely valued Index objects

This is what I expected:

Number  - Date         -  Type
11      - 2020-10-10   -  299
2       - 2020-10-11   -  134
30      - 2020-10-11   -  299
14      - 2020-10-11   -  260

I checked other questions in SO but I could not find a proper answers

Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — It_is_Chris, Jan 21 '21 at 15:05
maybe you have duplicates in the second df probably in `Number` column . how do you want to handle them? check `df.duplicated('Number',keep=False)` — anky, Jan 21 '21 at 15:06
@TangerCity yes, it does answer your question if you actually read the answer. — It_is_Chris, Jan 21 '21 at 15:08

Kenan · Answer 1 · 2021-01-21T15:10:37.943

1

Dosen't this work?

df1.merge(df2, on='Number', how='left')

   Number        Date  Type
0      11  2020-10-10   299
1       2  2020-10-11   134
2      30  2020-10-11   299
3      14  2020-10-11   260

edited Jan 21 '21 at 15:10

answered Jan 21 '21 at 15:06

Kenan

13,156
8
43
50

based on the example you provided my output looks correct – Kenan Jan 21 '21 at 15:11
Thats true, you are right. I didnt made a variable of it. Thank you lad. Would appreciate it if you upvoted my question. – TangerCity Jan 21 '21 at 15:13
can you please post an example that this solution doesn't work so we can help you – Kenan Jan 21 '21 at 15:20
If the solution I provided works for you please accept the answer so this question can be closed – Kenan Jan 21 '21 at 15:44

How to merge two Excel files in Python?

1 Answers1