pandas: df.loc[-1,col] sometimes work and sometimes add extra row with NAN

Question

I have a pandas dataframe. I am trying to modify name column value in the last row

I try

df.loc[-1,'name'] = "something"

this works

Now I filter few rows from the df with a query and call it df_query

and my last row in df_query is

    id  name
21  965 kris

I check the index -1

df_query.loc['name'].iloc[-1]

it shows "kris"

now on df_query i try

df_query.loc[-1,'name'] = "something"

it adds an extra row instead of replacing kris with something

    id  name
21  965.0 kris
-1  NaN "something"

also convers id into float from int

why sometimes it works and sometimes it doesnt

later after searching i found at https://stackoverflow.com/a/49510469

Just using iloc[-1, 'a] won't work as -1 is not in the index.

I couldnt understand the reason given above

and says to try:

df_query.loc[df_query.loc.index[-1],'name'] = "something"

and now it works.

Can someone explain whats happening

Answer 1

You can select last value of name different way - if use DataFrame.loc use df.index for last value of index if index values are unique:

df.loc[df.index[-1],'name'] = "something"

Or if use DataFrame.iloc get position of column name by Index.get_loc:

df.iloc[-1,df.columns.get_loc('name')] = "something"

---

If use:

df.loc[-1,'name'] = "something"

Pandas try set row with index=-1 if exist, else create new row with index -1. Problem is if last index has no -1, but e.g. first index, it replace not last, but first row.

So is possible use:

#tested last value of index
if df.index[-1] == -1:
    #last value is set
    df.loc[-1,'name'] = "something"

#tested all values if index
elif (df.index == -1).any():
    #some value with -1 is set
    df.loc[-1,'name'] = "something"
else:
    #new row with -1 is created
    df.loc[-1,'name'] = "something"