One to Many comparison in pandas two excel column comparison?

user_6851208 0
Viewed 167

I am comparing two spreadsheets, the second spreadsheet has repeated occurrences of the value in one column, the first spreadsheet has values in which some values match with the first spreadsheet. I want to create another one column in the second spreadsheet and copy the related phone number from the first sheet

df1:

df1 = pd.Dataframe({"name":["a","b","c"],"phone number":[1,2,3]})

df2:

df2 = pd.Dataframe({"name":["a","a","a","a","b","b","b","b"]})

Expected Output:

**name phno**
a      1
a      1
a      1 
a      1 
b      2
b      2
b      2
b      2

comparing two excel name based on one_to_many relations and create one new column in dataframe2 as phno and copy the related phone number value from dataframe1

Thanks for your contribution

pandas dataframe excel python
2 Answers

Use map. create a dict of df1['name']: df1['phone number'] and map to df2['name']

 df2['phno']=df2.name.map(dict(zip(df1.name, df1['phone number'])))



 name  phno
0    a     1
1    a     1
2    a     1
3    a     1
4    b     2
5    b     2
6    b     2
7    b     2
user_89869750

You can use standard pd.merge here:

In [1302]: df2.merge(df1, on='name')
Out[1302]: 
  name  phone number
0    a             1
1    a             1
2    a             1
3    a             1
4    b             2
5    b             2
6    b             2
7    b             2
user_58208140
Related