Pandas replace Na using merge or join-CodePudding

I want to replace Na in column A with based on shared values of column B so column rows with x in column B have 1 in column A and rows with y in column B have 2 in column A

A    B   C   D  E
1    x   d   e  q
Na   x   v   s  f 
Na   x   v   e  j
2    y   w   e  v
Na   y   b   d  g
'''

CodePudding user response：

Use groupby.transform('first'), eventually combined with convert_dtypes:

df['A'] = df.groupby('B')['A'].transform('first').convert_dtypes()

output:

   A  B  C  D  E
0  1  x  d  e  q
1  1  x  v  s  f
2  1  x  v  e  j
3  2  y  w  e  v
4  2  y  b  d  g

CodePudding user response：

With the conditions you mention, we create a new series deduced from column B, and then fill na values from column A with this new series' values:

mapping = {"x": 1, "y": 2}
Aprime = df.replace({"B": mapping})
df.A = df.A.fillna(Aprime)