CODEWITHSUNDEEP
pythonpandas
Shekhar
Shekhar

Reputation: 387

Mapping or Merging two csv files in python based on two columns in python?

I have two files one contains 

    0       1

0   0.0     0.0
1   2.0     8.0
2   7.0     3.0
3   6.0     1.0
4   5.0     0.0
5   4.0     NaN
6   9.0     NaN
7   0.0     NaN

and another one contains: 

    0

0   A
1   B
2   C
3   D
4   E
5   F
6   G
7   H
8   I
9   J

I want to map the second file with two columns (0 and 1) of the first file.

Expected output is like this: 

1_st_column   2nd_column 
A             A
C             I
H             D
G             B
F             A
E
J
A

I tried with merging but couldn't get it work. How can i solve this? Thank you!

Upvotes: 1

Views: 187

Answers (1)

anky
anky

Reputation: 75080

You can use df.replace() here:

file1.replace(dict(enumerate(file2['0']))) #replace '0' with original column name
#if column name is int , use: file1.replace(dict(enumerate(file2[0])))
# if needed to create dict with index: file1.replace(dict(zip(file2.index,file2['0'])))

   0    1
0  A    A
1  C    I
2  H    D
3  G    B
4  F    A
5  E  NaN
6  J  NaN
7  A  NaN

Upvotes: 3

Related Questions