To assign the values to the same name in different rows

Question

I am new in python. I have multiple same Name in the column df2, let say I don't want to drop the duplicate Name in df2.

How do I assign the values (Marks) from df1 to the same Name in df2 in different rows?

df1 = pd.DataFrame({'Name': ['rumul', 'rahul',
                             'ravi', 'imran'],
                    'Marks': [5, 20, 8, 12]})
 
df2 = pd.DataFrame({'Name': ['rumul', 'rahul',
                             'rahul', 'ravi',
                             'imran','ravi', 'ravi','imran','rahul','ravi'],
                    'Marks': ['','','','','','','','','','']})

df1

Name	Marks
rumul	5
rahul	20
ravi	8
imran	12

df2

Name	Marks
rumul
rahul
rahul
ravi
imran
ravi
ravi
imran
rahul
ravi

The expected output:

Name	Marks
rumul	5
rahul	20
rahul	20
ravi	8
imran	12
ravi	8
ravi	8
imran	12
rahul	20
ravi	8

I voted to reopen because 2 answers does not use `merge`. Merge is probably "too much" for this problem IMHO. — Corralien, Nov 18 '21 at 21:23

score 2 · Accepted Answer · answered Nov 18 '21 at 21:16

2

You can use a map to avoid the need of merge(). Because merge would add an extra column and not necessarily overwrite it.

df2['Marks'] = df2['Name'].map(df1.set_index('Name').to_dict()['Marks'])

This would output:

    Name  Marks
0  rumul      5
1  rahul     20
2  rahul     20
3   ravi      8
4  imran     12
5   ravi      8
6   ravi      8

For the same output, you can use pandas replace()

answered Nov 18 '21 at 21:16

Celius Stingher

17,835
6
23
53

Agree with you about `merge`. – Corralien Nov 18 '21 at 21:25

score 1 · Answer 2 · answered Nov 18 '21 at 21:19

Use Indexing operations:

>>> df1.set_index('Name').reindex(df2['Name']).reset_index()
    Name  Marks
0  rumul      5
1  rahul     20
2  rahul     20
3   ravi      8
4  imran     12
5   ravi      8
6   ravi      8
7  imran     12
8  rahul     20
9   ravi      8

score 0 · Answer 3 · answered Nov 18 '21 at 21:17

Simple 1-liner with merge:

df2[["Name"]].merge(df1, on="Name", how="left").rename(columns={"Marks_y": "Marks"})

    Name  Marks
0  rumul      5
1  rahul     20
2  rahul     20
3   ravi      8
4  imran     12
5   ravi      8
6   ravi      8
7  imran     12
8  rahul     20
9   ravi      8

To assign the values to the same name in different rows

3 Answers3