-1

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!

Arthur Zhang
  • 107
  • 8
  • Please trim your code to make it easier to find your problem. Follow these guidelines to create a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). – Community Feb 15 '23 at 10:07

1 Answers1

0

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.

  • thanks, but i tried converting everthing to datetime but python gives the keyerror nonetheless – Arthur Zhang Feb 15 '23 at 07:19
  • tri only filter? df filter df.loc[df.['your column'] > 'your-date' ], if it works – Tornike Kharitonishvili Feb 15 '23 at 07:21
  • nah still no luck, this gives me ''Traceback (most recent call last): File "....\base.py", line 3802, in get_loc return self._engine.get_loc(casted_key) File "pandas\_libs\index.pyx", line 138, in pandas._libs.index.IndexEngine.get_loc File "pandas\_libs\index.pyx", line 146, in pandas._libs.index.IndexEngine.get_loc File "pandas\_libs\index_class_helper.pxi", line 49, in pandas._libs.index.Int64Engine._check_type KeyError: datetime.date(2023, 2, 3). i am guessing this is not a type problem maybe? – Arthur Zhang Feb 15 '23 at 07:38
  • As far as I understand this is not a code problem. – Tornike Kharitonishvili Feb 15 '23 at 08:01
  • indeed, there is an innate problem under the hood with my excel data. now everything is working fine. thanks bro. – Arthur Zhang Feb 16 '23 at 03:49
  • Very good. you will learn many things at the same time while working. – Tornike Kharitonishvili Feb 16 '23 at 06:31