I am trying to query a date column in pandas dataframe and return price column value - however, if the date isn't matched (say holiday or a weekend date) the next available date column and price value need to be returned...is there a method in pandas or numpy that can help with this?
Asked
Active
Viewed 190 times
-1
-
Does this answer your question? [Find the closest date to a given date](https://stackoverflow.com/questions/32237862/find-the-closest-date-to-a-given-date) – J. Choi Jul 08 '22 at 03:28
-
I think it does - but it gets in 2 steps: find the next date and get the column value corresponding to that....I was thinking if we can get it in one hop... – sud Jul 08 '22 at 03:47
2 Answers
1
You can use this one-liner:
out = (df.loc[df['date'].sub(pd.to_datetime('2022-07-03'))
.loc[lambda x: x.dt.days>=0].head(1).index])
print(out)
# Output
date price
2 2022-07-05 30
Input dataframe:
>>> df
date price
0 2022-07-01 10
1 2022-07-02 20
2 2022-07-05 30
Corralien
- 109,409
- 8
- 28
- 52
0
A generic solution (to match one or more values), could be to use merge_asof:
df['date'] = pd.to_datetime(df['date'])
target = ['2022-07-03']
out= (pd.merge_asof(pd.to_datetime(pd.Series(target, name='date_drop')),
df, left_on='date_drop', right_on='date', direction='forward')
.filter(regex='^((?!_drop).)*$')
)
output:
date price
0 2022-07-05 30
mozway
- 194,879
- 13
- 39
- 75