CODEWITHSUNDEEP
pythonindexingpandastime-seriesresampling
piRSquared
piRSquared

Reputation: 294278

Resample a time series with the index of another time series

I have 2 data frames with identical columns but different datetime indices. I want to resample one of them to use the index of the other and forward fill data from the one on any dates in the index of the other in which there wasn't data for.

import pandas as pd
import numpy as np
from datetime import datetime as dt

a_values = np.random.randn(4, 4)
a_index = [dt(2012, 3, 16), dt(2012, 3, 19), dt(2012, 3, 20), dt(2012, 3, 21)]
a = pd.DataFrame(data=a_values, index=a_index)

b_values = np.trunc(np.random.randn(3, 4) * 1000)
b_index = [dt(2012, 3, 16), dt(2012, 3, 19), dt(2012, 3, 21)]
b = pd.DataFrame(data=b_values, index=b_index)

c_insert = a.ix['2012-03-20']
c = b.append(c_insert).sort()
c.ix['2012-03-20'] = c.ix['2012-03-19']

'a' represents the data frame whose index I'd like to use as the resampling reference. 'b' represents the data frame I'd like to resample and forward fill data. 'c' represents what I'd like the results to look like.

Notice that 'b' is missing the '2012-03-20' index that exists in 'a'. 'c' populates the columns for index '2012-03-20' with the data in the columns from 'b' for index '2012-03-19'

Does pandas have the functionality to do this.

Thanks in advance.

PiR

Upvotes: 15

Views: 8630

Answers (1)

Dan Allan
Dan Allan

Reputation: 35245

To resample by a reference index, use reindex.

In [11]: b.reindex(a.index, method='ffill')
Out[11]: 
               0     1     2     3
2012-03-16  -926  -625   736   457
2012-03-19 -1024   742   732 -1020
2012-03-20 -1024   742   732 -1020
2012-03-21  1090 -1163  1652   -94

Upvotes: 16

Related Questions