Upsampling a time-series with different conditions for float64 vs object and int64 columns

Question

I have a df similar to this:

print(df)
                        A     B  C
DATE_TIME                         
2016-10-08 13:57:00   in   5.61  0
2016-10-08 14:02:00   in   8.05  0
2016-10-08 14:07:00  out   7.92  0
2016-10-08 14:12:00   in   7.98  1
2016-10-08 14:17:00  out   8.18  0
2016-10-08 14:22:00  out   7.59  0

print (df.dtypes)
A     object
B    float64
C      int64
dtype: object

I want to resample this df to a 1S frecuency, so that I can concatenate it with another df. The problem I can't solve is that for the columns type object and int64 I want the same value repeated for the newly created time rows, which could be done by this function:

df=df.resample('S', fill_method='pad')

whereas for the float64 columns I am looking for this:

df=df.interpolate()

I thought about applying an IF statement, but I also figuered that I first have to do the resample step before the interpolation step. When I resample just by df=df.resample('S') I can interpolate afterwards, which works for the float64 columns but not for the object and Int64 ones. Could anybody help me please? Thanks.

score 1 · Accepted Answer · answered Aug 27 '16 at 01:35

1

Here is a method that use reindex:

index = pd.date_range(df.index[0], df.index[-1], freq="s")
df2 = df.reindex(index)
for col, s in df2.iteritems():
    if s.dtype == float:
        s.interpolate(inplace=True)
    else:
        s.ffill(inplace=True)

answered Aug 27 '16 at 01:35

HYRY

94,853
25
187
187

MaxU - stand with Ukraine · Answer 2 · 2016-08-28T10:42:47.213

UPDATE: I think you still can use vectorized approach (not looping through your data frame), even when you have multiple float columns - it should be much faster:

assuming you have a following DF (columns: ['B','D'] are of float dtype):

In [18]: df
Out[18]:
                       A     B  C       D
DATE_TIME
2016-10-08 13:57:00   in  5.61  0  6.2271
2016-10-08 14:02:00   in  8.05  0  8.9355
2016-10-08 14:07:00  out  7.92  0  8.7912
2016-10-08 14:12:00   in  7.98  1  8.8578
2016-10-08 14:17:00  out  8.18  0  9.0798
2016-10-08 14:22:00  out  7.59  0  8.4249

In [19]: df.dtypes
Out[19]:
A     object
B    float64
C      int64
D    float64
dtype: object

you can do the following (it'll work for pandas versions: 0.18.0+):

rsmpl = df.resample('S')
pd.concat([rsmpl.pad()[df.select_dtypes(exclude=['float']).columns], 
           rsmpl.interpolate()[df.select_dtypes(include=['float']).columns]],
          axis=1)

Example:

In [23]: pd.concat([rsmpl.pad()[df.select_dtypes(exclude=['float']).columns],
   ....:            rsmpl.interpolate()[df.select_dtypes(include=['float']).columns]],
   ....:           axis=1).head()
Out[23]:
                      A  C         B         D
DATE_TIME
2016-10-08 13:57:00  in  0  5.610000  6.227100
2016-10-08 13:57:01  in  0  5.618133  6.236128
2016-10-08 13:57:02  in  0  5.626267  6.245156
2016-10-08 13:57:03  in  0  5.634400  6.254184
2016-10-08 13:57:04  in  0  5.642533  6.263212

OLD answer:

You can first resample('S') and pad() and then reassign your float64 column B with df.resample('S').interpolate().B:

In [96]: df.resample('S').pad().assign(B=df.resample('S').interpolate().B)
Out[96]:
                       A         B  C
DATE_TIME
2016-10-08 13:57:00   in  5.610000  0
2016-10-08 13:57:01   in  5.618133  0
2016-10-08 13:57:02   in  5.626267  0
2016-10-08 13:57:03   in  5.634400  0
2016-10-08 13:57:04   in  5.642533  0
2016-10-08 13:57:05   in  5.650667  0
2016-10-08 13:57:06   in  5.658800  0
2016-10-08 13:57:07   in  5.666933  0
2016-10-08 13:57:08   in  5.675067  0
2016-10-08 13:57:09   in  5.683200  0
2016-10-08 13:57:10   in  5.691333  0
2016-10-08 13:57:11   in  5.699467  0
2016-10-08 13:57:12   in  5.707600  0
2016-10-08 13:57:13   in  5.715733  0
2016-10-08 13:57:14   in  5.723867  0
2016-10-08 13:57:15   in  5.732000  0
2016-10-08 13:57:16   in  5.740133  0
2016-10-08 13:57:17   in  5.748267  0
2016-10-08 13:57:18   in  5.756400  0
2016-10-08 13:57:19   in  5.764533  0
2016-10-08 13:57:20   in  5.772667  0
2016-10-08 13:57:21   in  5.780800  0
2016-10-08 13:57:22   in  5.788933  0
2016-10-08 13:57:23   in  5.797067  0
2016-10-08 13:57:24   in  5.805200  0
2016-10-08 13:57:25   in  5.813333  0
2016-10-08 13:57:26   in  5.821467  0
2016-10-08 13:57:27   in  5.829600  0
...                  ...       ... ..

Or a bit faster version (one resample() call instead of two):

rsmpl = df.resample('S')
rsmpl.pad().assign(B=rsmpl.interpolate().B)

I chose the first answer because I can apply it more generically to my `df` which has a lot more columns, without having to specify them individually. Note: I had to adjust the code to `df.resample('S', fill_method='pad').assign(B=df.resample('S').interpolate().B)` if not I would get the error message ` AttributeError: 'DataFrame' object has no attribute 'pad'`. I could not figure out how to modify the faster version to not get that error message. Thanks to both of you very much! — vera, Aug 27 '16 at 23:00
@vera, starting from pandas 0.18.0 the resample API [has been changed](http://pandas.pydata.org/pandas-docs/version/0.18.0/whatsnew.html#whatsnew-0180-breaking-resample) - most probably you are using older pandas version. — MaxU - stand with Ukraine, Aug 28 '16 at 10:24
@vera, i've updated my answer - now it'll take care of multiple `float` columns and will still use vectorized approach — MaxU - stand with Ukraine, Aug 28 '16 at 10:43
Cool, thank you! That solutions looks more straightforward and I really want to test it. I m still doing my reading on how to update my pandas version (0.17.0) on Spyder (Python 3.5). I tried with ` conda update pandas` ...which doesn't work, when I type `help(conda)` it says `NameError: name 'conda' is not defined`. I feel I am missing some basics here..... — vera, Aug 29 '16 at 12:33

Upsampling a time-series with different conditions for float64 vs object and int64 columns

2 Answers2

Linked