6

I am trying to use resample method to fill the gaps in timeseries data. But I also want to know which row was used to fill the missed data.

This is my input series.

In [28]: data
Out[28]: 
Date
2002-09-09    233.25
2002-09-11    233.05
2002-09-16    230.25
2002-09-18    230.10
2002-09-19    230.05
Name: Price

With resample, I will get this

In [29]: data.resample("D", fill_method='bfill')
Out[29]: 
Date
2002-09-09    233.25
2002-09-10    233.05
2002-09-11    233.05
2002-09-12    230.25
2002-09-13    230.25
2002-09-14    230.25
2002-09-15    230.25
2002-09-16    230.25
2002-09-17    230.10
2002-09-18    230.10
2002-09-19    230.05
Freq: D

I am looking for 

Out[29]: 
Date
2002-09-09    233.25  2002-09-09
2002-09-10    233.05  2012-09-11
2002-09-11    233.05  2012-09-11
2002-09-12    230.25  2012-09-16
2002-09-13    230.25  2012-09-16
2002-09-14    230.25  2012-09-16
2002-09-15    230.25  2012-09-16
2002-09-16    230.25  2012-09-16
2002-09-17    230.10  2012-09-18  
2002-09-18    230.10  2012-09-18
2002-09-19    230.05  2012-09-19

Any help?

pvncad
  • 177
  • 1
  • 1
  • 8

1 Answers1

5

After converting the Series to a DataFrame, copy the index into it's own column. (DatetimeIndex.format() is useful here as it returns a string representation of the index, rather than Timestamp/datetime objects.)

In [510]: df = pd.DataFrame(data)

In [511]: df['OrigDate'] = df.index.format()

In [513]: df
Out[513]: 
             Price    OrigDate
Date                          
2002-09-09  233.25  2002-09-09
2002-09-11  233.05  2002-09-11
2002-09-16  230.25  2002-09-16
2002-09-18  230.10  2002-09-18
2002-09-19  230.05  2002-09-19

For resampling without aggregation, there is a helper method asfreq().

In [528]: df.asfreq("D", method='bfill')
Out[528]: 
             Price    OrigDate
2002-09-09  233.25  2002-09-09
2002-09-10  233.05  2002-09-11
2002-09-11  233.05  2002-09-11
2002-09-12  230.25  2002-09-16
2002-09-13  230.25  2002-09-16
2002-09-14  230.25  2002-09-16
2002-09-15  230.25  2002-09-16
2002-09-16  230.25  2002-09-16
2002-09-17  230.10  2002-09-18
2002-09-18  230.10  2002-09-18
2002-09-19  230.05  2002-09-19

This is effectively short-hand for the following, where last() is invoked on the intermediate DataFrameGroupBy objects.

In [529]: df.resample("D", how='last', fill_method='bfill')
Out[529]: 
             Price    OrigDate
Date                          
2002-09-09  233.25  2002-09-09
2002-09-10  233.05  2002-09-11
2002-09-11  233.05  2002-09-11
2002-09-12  230.25  2002-09-16
2002-09-13  230.25  2002-09-16
2002-09-14  230.25  2002-09-16
2002-09-15  230.25  2002-09-16
2002-09-16  230.25  2002-09-16
2002-09-17  230.10  2002-09-18
2002-09-18  230.10  2002-09-18
2002-09-19  230.05  2002-09-19
Garrett
  • 47,045
  • 6
  • 61
  • 50