Pandas: merge two dataframes with different index and missing values

Question

I am trying to merge two dataframe together. df2 has more sample points than df. I want to merge them base on the index of df in a way that for each timestamp for the closest non missing value to timestamp be the value.

my original data set is categorical that is why I made the column as strings.

from datetime import datetime, timedelta
import pandas as pd
import numpy as np
import random
    ##Generate the Data
np.random.seed(12) 
date_today = datetime.now()
ndays = 5
df = pd.DataFrame({'date': [date_today + timedelta(days=x) for x in range(ndays)], 
                       'test': pd.Series(np.random.randn(ndays)),     'test2':pd.Series(np.random.randn(ndays))})
df = df.set_index('date').sort_index()
df = df.mask(np.random.random(df.shape) < .7)
print(df)

df2 = pd.DataFrame({'date': [date_today + timedelta(days=(abs(np.random.randn(1))*0.25)[0]*x) for x in range(ndays*2)], 
                       'test3': pd.Series(np.random.randn(ndays*2))})
df2 = df2.set_index('date').sort_index() 

df2 = df2.mask(np.random.random(df2.shape) < .3)
df['test']=df['test'].astype(str)
df['test2']=df['test2'].astype(str)
df2['test3']=df2['test3'].astype(str)



print(df2)
df2.reindex(df.index, method='bfill')

current output:

                                test3
date    
2018-03-12 22:31:52.177918  -1.6817565103951275
2018-03-13 22:31:52.177918  nan
2018-03-14 22:31:52.177918  nan
2018-03-15 22:31:52.177918  nan
2018-03-16 22:31:52.177918  nan

Desired out put:

                                test3
date    
2018-03-12 22:31:52.177918  -1.6817565103951275
2018-03-13 22:31:52.177918   0.214975948415751
2018-03-14 22:31:52.177918  nan
2018-03-15 22:31:52.177918  nan
2018-03-16 22:31:52.177918  nan

Thanks in advance,

piRSquared · Accepted Answer · 2018-03-13 03:44:45Z

2

Use the method='nearest' parameter setting in your reindex

df2.reindex(df.index, method='nearest')

date                                      
2018-03-12 20:44:02.753549   -1.6817565104
2018-03-13 20:44:02.753549  0.214975948416
2018-03-14 20:44:02.753549             nan
2018-03-15 20:44:02.753549             nan
2018-03-16 20:44:02.753549             nan

answered Mar 13, 2018 at 3:44

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

Collectives™ on Stack Overflow

Pandas: merge two dataframes with different index and missing values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related