I have two dataframe and I want to replace null values with other dataframe on key(X) with how ='left' (DF1). Thank you so much.
DF1
X | Y
1 | a
2 | NaN
3 | c
DF2
X | Y
1 | a
2 | b
3 | NaN
4 | d
OUTPUT
X | Y
1 | a
2 | b
3 | c
Add a comment
|
2 Answers
df1['Y'] = df1['X'].map(df2.set_index('X')['Y']).fillna(df1['Y'])
Or using np.where as:
df1['Y'] = np.where(df1.Y.isnull(),df1['X'].map(df2.set_index('X')['Y']),df1['Y'])
print(df1)
X Y
0 1 a
1 2 b
2 3 c
Comments
You could create a dictionary from the rows of df2 and use that dictionary with fillna:
import numpy as np
import pandas as pd
da1 = [[1, 'a'],
[2, np.nan],
[3, 'c']]
df1 = pd.DataFrame(data=da1, columns=['X', 'Y'])
da2 = [[1, 'a'],
[2, 'b'],
[3, np.nan],
[4, 'd']]
df2 = pd.DataFrame(data=da2, columns=['X', 'Y'])
mapping = dict(zip(df2.X, df2.Y))
df1.Y = df1.Y.fillna(df1.X.map(mapping))
print(df1)
Output
X Y
0 1 a
1 2 b
2 3 c
