The NaN values in data frame cannot be replaced by another data frame values by either df.update() or df.fillna()

Question

What I am trying to do is to replace the values of df['ME'] column by the act['ME'] column, with df size 10000 rows × 4 columns and act size 40000 rows × 1 column respectively. For both data frames, the time is used as the index. An example of df and act is shown as follows.

df =
                    x       y          
Time             
2020-01-01 01:00    115.1   20.0      
2020-01-01 01:01    115.0   20.1       
2020-01-01 01:02    114.9   19.9        
2020-01-01 01:03    123.1   20.0        
2020-01-01 01:04    115.0   18.9 

act =
                    ME                
Time 
2020-01-01 00:59    Sport    
2020-01-01 01:00    Home
2020-01-01 01:01    Food
2020-01-01 01:02    School
2020-01-01 01:03    Food
2020-01-01 01:04    Movie
2020-01-01 01:05    Sport

My expected results:

 df=
                       x       y      ME     
   Time             
   2020-01-01 01:00    115.1   20.0   Home   
   2020-01-01 01:01    115.0   20.1   Food    
   2020-01-01 01:02    114.9   19.9   School    
   2020-01-01 01:03    123.1   20.0   Food     
   2020-01-01 01:04    115.0   18.9   Movie

To get the expected results, I added a 'ME' column to df by

df['ME']=np.nan

Then, I have tried 2 functions, either

df['ME'] = act['ME'].fillna(df['ME'])

or

df.update(act)

However, df does not update the 'ME' column, which is shown as follows.

     df=
  Time                x       y        ME
  2020-01-01 01:00    115.1   20.0     NaN
  2020-01-01 01:01    115.0   20.1     NaN
  2020-01-01 01:02    114.9   19.9     NaN
  2020-01-01 01:03    123.1   20.0     NaN
  2020-01-01 01:04    115.0   18.9     NaN

I am not sure why the 'ME' cannot be replaced as I have tried using df.update() before and no problem has exceeded.

@jezrael Problem solved. Thx a lot! – soso Dec 10 '20 at 07:42 — soso, Dec 10 '20 at 07:42

score 0 · Answer 1 · answered Dec 10 '20 at 07:27

0

I guess you can simply do it with

df['ME'] = act.ME

answered Dec 10 '20 at 07:27

Amir Esmaeili

88
2
8

This is not working for my code, still showing NaN. Thx! – soso Dec 10 '20 at 07:31
Just don't put NANs in the df. directly copy the 'ME' column into df. @ahsojai – Amir Esmaeili Dec 10 '20 at 07:36
Still not working for me. But the function df = df.join(act) has solved my problem. Thx for your help! – soso Dec 10 '20 at 07:43

The NaN values in data frame cannot be replaced by another data frame values by either df.update() or df.fillna()

1 Answers1