Hello the Pandas team,
It's me again ;-)
Consider the following:
rng = pd.date_range('1/1/2011', periods=5, freq='T')
ts = pd.DataFrame({'A' : np.random.randn(len(rng))}, index=rng)
ts.drop(ts.index[[2,3]],inplace=True)
# note how I delete some rows so that the index contains holes
ts
Out[37]:
A
2011-01-01 00:00:00 0.873475
2011-01-01 00:01:00 0.035362
2011-01-01 00:04:00 -0.903640
I can shift my data in many different ways
ts.A.shift(1)
ts.A.shift(1, freq='T')
ts['index']=ts.index.shift(1, freq='T')
but I am unable to generate a variable that contains the lagged values, say, 1 minute ago (which is different from one observation ago as in ts.A.shift(1)).
In other words, is it possible to get the following output?
A
2011-01-01 00:00:00 NaN
2011-01-01 00:01:00 0.873475
2011-01-01 00:04:00 NaN
I get a NaN at row 3 because there is no data at 2011-01-01 00:03:00 (1 minute ago)
I thought a timeindex was able to understand the time component of the index. Am I missing something here?
Thanks!
Comment From: jreback
In [18]: ts
Out[18]:
A
2011-01-01 00:00:00 0.760494
2011-01-01 00:01:00 0.327515
2011-01-01 00:04:00 -1.216276
In [19]: ts.asfreq('1T')
Out[19]:
A
2011-01-01 00:00:00 0.760494
2011-01-01 00:01:00 0.327515
2011-01-01 00:02:00 NaN
2011-01-01 00:03:00 NaN
2011-01-01 00:04:00 -1.216276
In [20]: ts.asfreq('1T').shift(-1)
Out[20]:
A
2011-01-01 00:00:00 0.327515
2011-01-01 00:01:00 NaN
2011-01-01 00:02:00 NaN
2011-01-01 00:03:00 -1.216276
2011-01-01 00:04:00 NaN
Comment From: randomgambit
thanks Jeff thats a nice solution, but assume I want to subtract the 1 minute lagged value of A to that in the original dataframe.
Your solution means that I need to asfreq, shift and then merge the result back to the original dataframe?
Comment From: jreback
I don't really understand what you want to do all of the tools are there / try asking on. the mailing list or stack overflow for usage questions
Comment From: randomgambit
sure sorry and thanks for the heads up