How to merge 2 dataframes with common and also different indices, but prefer valid values against NaN values? [duplicate]

  dataframe, datetime, merge, pandas, python

How to combine

                     A  B    C
2010-01-01 10:00:00  1  1  1.0
2010-01-01 11:00:00  2  2  2.0
2010-01-01 12:00:00  3  3  NaN
2010-01-01 13:00:00  4  4  4.0
2010-01-01 14:00:00  5  5  NaN
2010-01-01 15:00:00  6  6  6.0
2010-01-01 16:00:00  7  7  7.0
2010-01-01 17:00:00  8  8  8.0
2010-01-01 18:00:00  9  9  9.0

                     A    B   C
2010-01-01 11:00:00  NaN  2   2
2010-01-01 12:00:00  3    3  99
2010-01-01 13:00:00  4    4   4
2010-01-01 14:00:00  5    5  99
2010-01-01 15:00:00  6    6   6
2010-01-01 16:00:00  7    7   7
2010-01-01 17:00:00  8    8   8
2010-01-01 18:00:00  9  NaN   9
2010-01-01 19:00:00 10   10  10
 

to receive:

                     A  B    C
2010-01-01 10:00:00  1  1  1.0
2010-01-01 11:00:00  2  2  2.0
2010-01-01 12:00:00  3  3   99
2010-01-01 13:00:00  4  4  4.0
2010-01-01 14:00:00  5  5   99
2010-01-01 15:00:00  6  6  6.0
2010-01-01 16:00:00  7  7  7.0
2010-01-01 17:00:00  8  8  8.0
2010-01-01 18:00:00  9  9  9.0
2010-01-01 19:00:00 10 10   10

Is there a way to receive this in relation to the index?
Already tryed this: (reproducable sample?)

import pandas as pd, numpy as np

a= pd.DataFrame({"A":[1,2,3,4,5,6,7,8,9],"B":[1,2,3,4,5,6,7,8,9],"C":[1,2,np.nan,4,np.nan,6,7,8,9]}, 
                index=pd.DatetimeIndex(["01.01.2010 10:00:00",
                "01.01.2010 11:00:00","01.01.2010 12:00:00","01.01.2010 13:00:00","01.01.2010 14:00:00",
                "01.01.2010 15:00:00","01.01.2010 16:00:00","01.01.2010 17:00:00","01.01.2010 18:00:00"]))
b= pd.DataFrame({"A":[2,3,4,5,6,7,8,9,10],"B":[2,3,4,5,6,7,8,9,10],"C":[2,99,4,99,6,7,8,9,10]}, 
                index=pd.DatetimeIndex(["01.01.2010 11:00:00","01.01.2010 12:00:00","01.01.2010 13:00:00",
                                        "01.01.2010 14:00:00","01.01.2010 15:00:00","01.01.2010 16:00:00",
                                        "01.01.2010 17:00:00","01.01.2010 18:00:00","01.01.2010 19:00:00"]))
print(a)
print(b)
a.index.name ="Time"
b.index.name ="Time"

print(pd.merge(???, on="Time"))

What to do if one index and column has a different value? (in my case this is only happening at NaN values -> prefer valid values against NaNs, otherwise if both are valid -> prefer the first df)

Source: Python Questions

LEAVE A COMMENT