update missing values in Python Pandas dataframe with matching conditions

Question

I have a dataframe df1 with 3 columns (A,B,C), NaN represents missing value here

A     B      C  
1     2    NaN
2     1    2.3
2     3    2.5

I have a dataframe df2 with 3 columns (A,B,D)

A     B     D
1     2     2
2     1     2
2     3     4

The expected output would be

A     B      C
1     2      2
2     1      2.3
2     3      2.5

I want to have values in column C in df1 intact if not missing, replaced by corresponding value in D with other two columns value equal, i.e, df1.A==df2.A and df1.B==df2.B

any good solution?

Also, add sample df2 and expected output like df1

Zero
– Zero

2017-07-04 17:06:24 +00:00
Commented Jul 4, 2017 at 17:06 — Zero
– Zero, Commented Jul 4, 2017 at 17:06

user2285236 · Accepted Answer · 2017-07-04 18:08:06Z

1

One way would be to use the columns A and B as the index. If you use fillna then, pandas will align the indices and give you the correct result:

df1.set_index(['A', 'B'])['C'].fillna(df2.set_index(['A', 'B'])['D']).reset_index()
Out: 
   A  B    C
0  1  2  2.0
1  2  1  2.3
2  2  3  2.5

answered Jul 4, 2017 at 18:08

user2285236

Sign up to request clarification or add additional context in comments.

Comments

shivsn · Accepted Answer · 2017-07-04 18:27:46Z

1

IIUC:

In [100]: df['C'] = np.where((np.isnan(df.C))&(df.A==df1.A)&(df.B==df1.B),df1.D,df.C)

In [101]: df
Out[101]: 
     A    B    C
0  1.0  2.0  2.0
1  2.0  1.0  2.3
2  2.3  1.2  2.5

np.where is faster when compared:

In [102]: %timeit df['C'] = np.where((np.isnan(df.C))&(df.A==df1.A)&(df.B==df1.B),df1.D,df.C)
1000 loops, best of 3: 1.3 ms per loop


In [103]: %timeit df.set_index(['A', 'B'])['C'].fillna(df1.set_index(['A', 'B'])['D']).reset_index()
100 loops, best of 3: 5.92 ms per loop

answered Jul 4, 2017 at 18:27

shivsn

7,9141 gold badge28 silver badges34 bronze badges

3 Comments

user2285236 Over a year ago

This will only work if df1 and df2 have the same length, and common observations are in the same order. df1['C'].fillna(df2['D']) would work in that case too.

Jin Over a year ago

yes, what if df1 and df2 does not have the same length?

shivsn Over a year ago

@ayhan I have not thought about that i will try and let you know. Thank you for pointing out the mistake.

Collectives™ on Stack Overflow

update missing values in Python Pandas dataframe with matching conditions

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related