0

I have two dataframes, dataframe A, and its son dataframe B, since I need to change some values in B according to A using matching columns to input the comparison result in dataframe B.

A:
name id 
321  1
18   1
234  2
12   2
234  2

... ...
B:
name id  matching
321  2
14   2
13   2
234  1
235  1

My searching rule is if a cell in column name of df B (eg. name = 321) has the same name as A, then check the id of the name (eg. id = 1 in B, id = 2 in A).

After that, label all items who have the same id in B (eg. in B, name = 321,14,13 all have id =2) as matching = 1.

I want the output like:

B:
name id  matching
321  2   1
14   2   1
13   2   1
234  1   2
235  7   2

My code takes a long time to work and still does not achieve my goal:

for i in range(0,len(A)):
    for j in range(0,len(B)):

        if A.name[i]== B.name[j]:
            B.matching[j] = A.id[i]

I want the output like:

OutB:
name id  matching
321  2   1
14   2   1
13   2   1
234  1   2
235  7   2

Is there any function or algorithm that could help me complete my goal?

Mihai Chelaru
  • 7,614
  • 14
  • 45
  • 51
Qiaoji Xu
  • 11
  • 1

1 Answers1

1
pd.merge(A,B, on=name)

Should do what you are looking for. See the documentation:

https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html#database-style-dataframe-or-named-series-joining-merging

Example from the documentation

left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'], 
'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3']})
right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
'C': ['C0', 'C1', 'C2', 'C3'],
'D': ['D0', 'D1', 'D2', 'D3']}) 
>>> pd.merge(left,right,on='key')
  key   A   B   C   D
0  K0  A0  B0  C0  D0
1  K1  A1  B1  C1  D1
2  K2  A2  B2  C2  D2
3  K3  A3  B3  C3  D3
Peter Mølgaard Pallesen
  • 1,470
  • 1
  • 15
  • 26