Is there a way to get next row value or prior row value when the string match doesnt occur in pandas-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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