Pandas Dataframe .loc update on a non-unique Datetime Index?-CodePudding

Hopefully a really simple one that I just haven't managed to figure out the solution to yet. I have a dataframe with a timestamp (%Y-%m-%d) index and I want to be able to update a single row, knowing the datetime index value, using .loc.

I know the index is non-unique which means occasionally I will get more than just the row I want to update. So I have sorted the dataframe on a second column, such that the row I want to update will always be the last for that index value.

The dataframe in question is a subset copy of a much larger one, and setting a value without using .loc doesn't work - hence my restriction to using .loc.

My question: Is there a way I can reference this last row for the index value and update it, based purely on the index? Obviously using just the index value will update all rows with that index, argmax seemingly doesn't work for timestamps, and although I could temporarily recreate the index to use the other column which I sorted on, I would rather not use this three-step solution if there is a direct way of doing it.

Please see my code below for a sample dataframe - what I am after is a way to update just the row with df['sort']=='d', which I know will be the last one for the index, based solely on the index value if this possible. Many thanks in advance!

l1 = [datetime.today().date()] * 4
l2 = list(range(1,5))
l3 = ['a','b','c','d']
df= pd.DataFrame(list(zip(l1,l2,l3)),columns=['datetime','value','sort'])
df.set_index('datetime',inplace=True)

df
Out[1317]: 
            value sort
datetime              
2021-10-22      1    a
2021-10-22      2    b
2021-10-22      3    c
2021-10-22      4    d

df.loc[df.index[-1].argmax()]
Traceback (most recent call last):

  File "<ipython-input-1318-e1758b122814>", line 1, in <module>
    df.loc[df.index[-1].argmax()]

AttributeError: 'datetime.date' object has no attribute 'argmax'

df.loc[df.index[-1],'value'] = 5

df
Out[1320]: 
            value sort
datetime              
2021-10-22      5    a
2021-10-22      5    b
2021-10-22      5    c
2021-10-22      5    d

CodePudding user response：

If you want to change value of a particular column in the last row of data frame, you can try below code

df.iloc[-1,0] = 5

-1 -> last row of data frame

0 -> Index of column 'value'

CodePudding user response：

So using Udaya's answer above, the solution I need is df.iloc[-1, df.columns.get_loc('value')] = 5