CODEWITHSUNDEEP
pythonpandas
motam79
motam79

Reputation: 3824

How to get the values of Pandas Series at previous business date at the same time

Lets say I have a pandas time-series as follows:

2012-01-02 17:16:00    0
2012-01-02 17:17:00    1
2012-01-03 17:16:00    2
2012-01-03 17:17:00    3
2012-01-03 17:18:00    4

I would like to get the values of the series at last day and the same time. If the same time does not exist the values should be NaN. So in this example the output is going to be:

2012-01-02 17:16:00    NA
2012-01-02 17:17:00    NA
2012-01-03 17:16:00    0
2012-01-03 17:17:00    1
2012-01-03 17:18:00    NA

The value corresponding to 2012-01-03 17:18:00 is NA because its time (i.e 17:18) does not exist in the previous day (i.e. 2012-01-02) Is there a way to do that in pandas?

Upvotes: 1

Views: 67

Answers (3)

GSBYBF
GSBYBF

Reputation: 160

You can also loop thru the dataframe and check

Upvotes: 0

Quang Hoang
Quang Hoang

Reputation: 150785

You can do so via merge:

(df.merge(df.assign(date=df['date']
                         .add(pd.offsets.BDay())
                   ),
          on='date',
          how='left',
          suffixes=['','_new'])
)

Output:

                 date  value  value_new
0 2012-01-02 17:16:00      0        NaN
1 2012-01-02 17:17:00      1        NaN
2 2012-01-03 17:16:00      2        0.0
3 2012-01-03 17:17:00      3        1.0
4 2012-01-03 17:18:00      4        NaN

Upvotes: 2

ALollz
ALollz

Reputation: 59579

You can reindex on an index that subtracts a business day, then add one back to the index after.

import pandas as pd

s = s.reindex(s.index - pd.offsets.BDay())  # NaN if no obs 1 BDay prior, else match
s.index = s.index + pd.offsets.BDay()       

#2012-01-02 17:16:00    NaN
#2012-01-02 17:17:00    NaN
#2012-01-03 17:16:00    0.0
#2012-01-03 17:17:00    1.0
#2012-01-03 17:18:00    NaN
#dtype: float64

Upvotes: 2

Related Questions