CODEWITHSUNDEEP
pythonpandasnan
Alex
Alex

Reputation: 41

Replace NaN values with values from other table

Please help.

My first table looks like:

id val1 val2
0  4    30
1  5    NaN
2  3    10
3  2    8
4  3    NaN

My second table looks like

id val1 val2_estimate
0  1    8
1  2    12
2  3    13
3  4    16
4  5    22

I want to replace Nan in 1st table with estimated values from column val2_estimate from 2nd table where val1 are the same. val1 in 2nd table are unique. End result need to look like that:

id val1 val2
0  4    30
1  5    22
2  3    10
3  2    8
4  3    13

I want to replace NaN values only.

Upvotes: 1

Views: 666

Answers (2)

Scott Boston
Scott Boston

Reputation: 153500

Use fillna with map from a pd.Series created using set_index:

df['val2'] = df['val2'].fillna(df['val1'].map(df2.set_index('val1')['val2_estimate']))
df

Output:

    val1  val2
id            
0      4  30.0
1      5  22.0
2      3  10.0
3      2   8.0
4      3  13.0

Upvotes: 1

cs95
cs95

Reputation: 402844

Use merge to get the corresponding df2's estimate for df1, then use fillna:

df['val2'] = df['val2'].fillna(
    df.merge(df2, on=['val1'], how='left')['val2_estimate'])
df

   id  val1  val2
0   0     4  30.0
1   1     5  22.0
2   2     3  10.0
3   3     2   8.0
4   4     3  13.0

Many ways to skin a cat, this is one of them.

Upvotes: 1

Related Questions