How to merge two dataframe in pandas to replace nan

Question

I want to do this in pandas:

I have 2 dataframes, A and B, I want to replace only NaN of A with B values.

A                                                
2014-04-17 12:59:00  146.06250  146.0625  145.93750  145.93750
2014-04-17 13:00:00  145.90625  145.9375  145.87500  145.90625
2014-04-17 13:01:00  145.90625       NaN  145.90625        NaN
2014-04-17 13:02:00        NaN       NaN  145.93750  145.96875

B
2014-04-17 12:59:00   146 2/32   146 2/32  145 30/32  145 30/32
2014-04-17 13:00:00  145 29/32  145 30/32  145 28/32  145 29/32
2014-04-17 13:01:00  145 29/32        146  145 29/32        147
2014-04-17 13:02:00        146        146  145 30/32  145 31/32

Result:
2014-04-17 12:59:00  146.06250  146.0625  145.93750  145.93750
2014-04-17 13:00:00  145.90625  145.9375  145.87500  145.90625
2014-04-17 13:01:00  145.90625       146  145.90625        147
2014-04-17 13:02:00        146       146  145.93750  145.96875

If I remember correctly, Result = A.fillna(B) should do it. — anonymous_user_13
– anonymous_user_13, Commented Aug 2, 2014 at 14:41
It kind of works, but only if the two dataframes have the same index (see @Camilo's comment to Foobar's answer). Notice that if instead you want to replace A with only non-NaN values in B (that is, replacing values in A with existing values in B), A.update(b) is perfect. — Pietro Battiston
– Pietro Battiston, Commented Feb 10, 2015 at 11:12

FooBar · Accepted Answer · 2014-08-02 14:49:03Z

18

The official way promoted exactly to do this is A.combine_first(B). Further information are in the official documentation.

However, it gets outperformed massively with large databases from A.fillna(B) (performed tests with 25000 elements):

In[891]: %timeit df.fillna(df2)
1000 loops, best of 3: 333 µs per loop
In[892]: %timeit df.combine_first(df2)
100 loops, best of 3: 2.15 ms per loop
In[894]: (df.fillna(df2) == df.combine_first(df2)).all().all()
Out[890]: True

edited Aug 2, 2014 at 14:49

answered Aug 2, 2014 at 14:42

FooBar

16.7k20 gold badges94 silver badges188 bronze badges

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

2 Comments

Camilo Avella Over a year ago

thx, combine_firs works do the job. But, fillna isn't working, is matching B first row with the nan A in other row

Abhishek Divekar Over a year ago

"fillna isn't working, is matching B first row with the nan A in other row" > use the same index and do df = df.sort_index() for both.

Community · Accepted Answer · 2017-05-23 11:45:54Z

1

Get the numpy arrays for A and B.
Make a mask of A where A == numpy.NaN
Assign B to A using the mask as a boolean index for both.

Similar to this:

>>> a
array([[  0.,   1.,   2.],
       [  3.,  nan,   5.],
       [  6.,   7.,   8.]], dtype=float16)
>>> b
array([[ 1000.,  1000.,  1000.],
       [ 1000.,  1000.,  1000.],
       [ 1000.,  1000.,  1000.]])
>>> mask = np.isnan(a)
>>> mask
array([[False, False, False],
       [False,  True, False],
       [False, False, False]], dtype=bool)
>>> a[mask] = b[mask]
>>> a
array([[    0.,     1.,     2.],
       [    3.,  1000.,     5.],
       [    6.,     7.,     8.]], dtype=float16)

Alternatively, use numpy.where():

>>> a
array([[  0.,   1.,   2.],
       [  3.,  nan,   5.],
       [  6.,   7.,   8.]], dtype=float16)
>>> a = np.where(np.isnan(a), b, a)
>>> a
array([[    0.,     1.,     2.],
       [    3.,  1000.,     5.],
       [    6.,     7.,     8.]])
>>>

https://stackoverflow.com/a/13062410/2823755 suggests the first (boolean indexing) method may work with the dataframe itself. ... and it does (wasn't satisfied, so i installed pandas):

>>> a = pandas.DataFrame(np.arange(25, dtype = np.float16).reshape(5,5))
>>> a.values[3,2] = np.NaN
>>> b = pandas.DataFrame(np.arange(1000, 1025, dtype = np.float16).reshape(5,5))
>>> a[np.isnan(a)] = b[np.isnan(a)]
>>> a
    0   1     2   3   4
0   0   1     2   3   4
1   5   6     7   8   9
2  10  11    12  13  14
3  15  16  1017  18  19
4  20  21    22  23  24
>>>

pandas.DataFrame.where also works.

a.where(~np.isnan(a), other = b, inplace = True)

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Aug 2, 2014 at 14:31

wwii

23.9k7 gold badges42 silver badges81 bronze badges

3 Comments

Camilo Avella Over a year ago

I will try this option also because combine_first is taking too much time. Do you know a fast way to convert dataframe to numpy array ? thx again

wwii Over a year ago

Sorry, no. I don't have pandas installed and am not familiar with it - but i think it should be easy, pandas seems to use numpy extensively.

wwii Over a year ago

stackoverflow.com/a/17682662/2823755, pandas.pydata.org/pandas-docs/stable/generated/…, pandas.pydata.org/pandas-docs/stable/generated/…

Collectives™ on Stack Overflow

How to merge two dataframe in pandas to replace nan

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related