CODEWITHSUNDEEP
pandas
Spring
Spring

Reputation: 193

Duplicated rows after merge

I have a random sampled output dataframe looks like this. 

df1  
     ID   lifetime
  0  10     11
  1  12     13
  2  11     12
  3  10     11

I want to map the lifetime value in df1 to my second datafarme df2:

 df2
     ID  Class   El  Price
  0  10   pc1     0    20 
  1  10   pc1     1    21 
  2  10   pc1     2    20
  3  10   pc2     0    20
  4  10   pc2     1    20 
  5  10   pc2     2    21 
  6  10   pc2     3    20
  7  11   pc1     0    20
  8  11   pc1     1    20 
  9  11   pc1     2    21 
 10  11   pc2     0    20
 11  11   pc2     1    20
 12  11   pc2     2    20 
 13  11   pc2     3    21 
 14  10   pc1     0    20
 15  10   pc1     1    21
 16  10   pc1     2    23
 17  10   pc1     0    22
 18  10   pc2     1    24 
 19  10   pc2     2    20 
 20  10   pc2     3    20 
 .....

I tried:

  results=df1.merge(df2, on='ID')

I also tried:

  results=df1.merge(df2, left_on='ID', right_on='ID')

I ended up have extra rows and noticed sample ID 10 has doubled its row from 14 to 28 rows. How can I fix the problem?

Upvotes: 0

Views: 110

Answers (1)

ansev
ansev

Reputation: 30920

Use DataFrame.drop_duplicates + Series.map this will work as long as different values ​​are not assigned to the same ID

df2['lifetime']=df2['ID'].map(df1.drop_duplicates('ID').set_index('ID')['lifetime'])
print(df2)

    ID Class  El  Price  lifetime
0   10   pc1   0     20        11
1   10   pc1   1     21        11
2   10   pc1   2     20        11
3   10   pc2   0     20        11
4   10   pc2   1     20        11
5   10   pc2   2     21        11
6   10   pc2   3     20        11
7   11   pc1   0     20        12
8   11   pc1   1     20        12
9   11   pc1   2     21        12
10  11   pc2   0     20        12
11  11   pc2   1     20        12
12  11   pc2   2     20        12
13  11   pc2   3     21        12
14  10   pc1   0     20        11
15  10   pc1   1     21        11
16  10   pc1   2     23        11
17  10   pc1   0     22        11
18  10   pc2   1     24        11
19  10   pc2   2     20        11
20  10   pc2   3     20        11

Upvotes: 2

Related Questions