CODEWITHSUNDEEP
pythonpandas
Brian
Brian

Reputation: 13571

Pandas DatetimeIndex get next date excluding weekends

I have two variables a and b.

a is of type DataFrame.

b's type is Series.

a.index contains most of the nonweekend dates in the year of 2020.

b.index contains some dates that I picked up randomly.

import pandas as pd
from datetime import datetime
print(a.index)
DatetimeIndex(['2020-01-02', '2020-01-03', '2020-01-06', '2020-01-07',
               '2020-01-08', '2020-01-09', '2020-01-10', '2020-01-13',
               '2020-01-14', '2020-01-15',
               ...
               '2020-12-18', '2020-12-21', '2020-12-22', '2020-12-23',
               '2020-12-24', '2020-12-25', '2020-12-28', '2020-12-29',
               '2020-12-30', '2020-12-31'],
              dtype='datetime64[ns]', name='Date', length=245, freq=None)


print(b.index)
DatetimeIndex(['2020-02-03', '2020-03-02', '2020-03-04', '2020-03-10',
               '2020-03-13'],
              dtype='datetime64[ns]', name='Date', freq=None)

How can I get the next date of b.index within a.index?

My first idea is running

target_dates = b.index + datetime.timedelta(days=1)
a.loc[target_dates]

But this would result in KeyError: "[Timestamp('2020-03-14 00:00:00')] not in index" error.

This is because 2020-03-14 is weekend, so a.index doesn't contain this date.

The final result that I want to get is

DatetimeIndex(['2020-02-04', '2020-03-03', '2020-03-05', '2020-03-11',
               '2020-03-16'],
              dtype='datetime64[ns]', name='Date', freq=None)

Does anyone have any idea about how to create this DatetimeIndex by using a.index and b.index?

Upvotes: 2

Views: 239

Answers (2)

rpanai
rpanai

Reputation: 13437

I'm a little confused by the fact that b is a series with date as index. So let assume b is a dataframe (you can eventually use b.to_frame).

Data

import pandas as pd
a = pd.DataFrame({"Date": pd.date_range("2020-01-01", periods=245)})
b = pd.DataFrame({"Date":
                  ['2020-02-03', '2020-03-02', 
                   '2020-03-04', '2020-03-10',
                   '2020-03-13']})
b["Date"] = b["Date"].astype("M8")

Filter

Now you want dates in a that are equals to dates in b plus 1 day and not weekends. For the latter you should ask that a["Date"].dt.weekday is not 5 or 6.

a[a["Date"].isin(b["Date"] + pd.Timedelta(days=1)) &
  ~a["Date"].dt.weekday.isin([5,6])]

         Date
34 2020-02-04
62 2020-03-03
64 2020-03-05
70 2020-03-11

Upvotes: 1

BENY
BENY

Reputation: 323226

Try with searchsorted and make sure a was sorted

a[np.searchsorted(a.index,b.index)+1]

Upvotes: 2

Related Questions