Search code examples
pythonpandasdata-processing

extracting values matching timestamps by a new set of timestamps

sample table here

i am trying to look up corresponding commodity prices from columns(CU00.SHF,AU00.SHF,SC00.SHF,I8888.DCE C00.DCE), with a new set of timestamps, the dates of which are 32 days later than the dates in column 'history_date'.

i tried .loc and .at in a loop to extract the matching values with below functions:

latest_day = data.iloc[data.shape[0] - 1, 0].date()

def next_trade_day(x):
    x = pd.to_datetime(x).date() #imported is_workday funtion requires datetime type
    while True:
        if is_workday(x + timedelta(32)) != False:
            break
            return (pd.Timestamp((x + timedelta(32))))
        if is_workday(x + timedelta(32)) == False:
            x = x + timedelta(1)
    return pd.Timestamp(x + timedelta(32))

def end_price(x):
    x = pd.Timestamp(x)
    if x <= latest_day:
        return data.at[x,'CU00.SHF']
    if x > latest_day:
        return'None'
    return data.at[x,'CU00.SHF']

but it always gives KeyError: Timestamp('2023-02-03 00:00:00')

any idea how should i achieve the target?

thanks in advance!

Solution

  • if you want work datetime:

    1. convert column datetime

    2. check date converted, use filte

       pd.to_datetime(df['your column'],errors='ignore') 
 df.loc[df.['your column'] > 'your-date' ]

    if work both, then check your full code.