CODEWITHSUNDEEP
datepandastime-series
Gere
Gere

Reputation: 12687

How to join a date to date ranges in Pandas?

I have one table with dates and another table where there is rather weekly data. My weeks start at Tuesday and the second table's date is supposed to determine the week (basically the Tuesday before the date is the start of the week; alternatively that date is an example day in that week).

How can I join the dates to information about weeks?

Here is the setup:

from datetime import datetime as dt
import pandas as pd

df=pd.DataFrame([dt(2016,2,3), dt(2016,2,8), dt(2016,2,9), dt(2016,2,15)])

df_week=pd.DataFrame([(dt(2016,2,4),"a"), (dt(2016,2,11),"b")], columns=["week", "val"])
# note the actual start of the weeks are the Tuesdays: 2.2., 9.2.

# I expect a new column df["val"]=["a", "a", "b", "b"]

I've seen pandas date_range, but I cannot see how to do that from there.

Upvotes: 2

Views: 1232

Answers (1)

IanS
IanS

Reputation: 16241

You're looking for DatetimeIndex.asof:

This will give you the closest index up to the day in df:

df_week.set_index('week', inplace=True)
df_week.index.asof(df['day'][1])

You can now use it to select the corresponding value:

df_week.loc[df_week.index.asof(df['day'][1])]

Finally, apply it to the entire dataframe:

df = pd.DataFrame([dt(2016,2,8), dt(2016,2,9), dt(2016,2,15)], columns=['day'])
df['val'] = df.apply(lambda row: df_week.loc[df_week.index.asof(row['day'])]['val'], axis=1)

I removed the first value from df because I didn't want to deal with edge cases.

Result:

         day val
0 2016-02-08   a
1 2016-02-09   a
2 2016-02-15   b

Upvotes: 2

Related Questions