CODEWITHSUNDEEP
pythonpandasdataframetime-series
user7378715
user7378715

Reputation: 71

How to add column to existing DataFrame with non matching times?

In an existing DataFrame;

2019-12-02 | 1.000000 
2019-12-04 | 1.020100
2019-12-05 | 1.030301
2019-12-06 | 1.040604
2019-12-09 | 1.051010
2019-12-10 | 1.061520

I want to add an new columns based an existing DF or TS, with a non matching index;

2019-12-04  00:00:00 | A
2019-12-05  15:40:00 | B
2019-12-07  00:30:00 | C
2019-12-10  15:00:00 | D

with as result;

2019-12-02 | 1.000000 NaN
2019-12-04 | 1.020100 A
2019-12-05 | 1.030301 B
2019-12-06 | 1.040604 C
2019-12-09 | 1.051010 NaN
2019-12-10 | 1.061520 D

The interval in the existing DataFrame can also be smaller than 24 hours.

Thx!

Upvotes: 1

Views: 145

Answers (1)

jezrael
jezrael

Reputation: 862541

Use merge_asof:

df = pd.merge_asof(df1, 
                   df2, 
                   left_index=True, 
                   right_index=True, 
                   tolerance=pd.Timedelta(24, 'H'), 
                   direction='forward')
print (df)
                   A    B
2019-12-02  1.000000  NaN
2019-12-04  1.020100    A
2019-12-05  1.030301    B
2019-12-06  1.040604  NaN
2019-12-09  1.051010  NaN
2019-12-10  1.061520    D

df = pd.merge_asof(df1, 
                   df2, 
                   left_index=True, 
                   right_index=True, 
                   tolerance=pd.Timedelta(25, 'H'), 
                   direction='forward')
print (df)
                   A    B
2019-12-02  1.000000  NaN
2019-12-04  1.020100    A
2019-12-05  1.030301    B
2019-12-06  1.040604    C
2019-12-09  1.051010  NaN
2019-12-10  1.061520    D

Upvotes: 3

Related Questions