Python: Updating a single value in a Pandas Dataframe using loc[]+iloc[]+loc[]

VV33 0
Viewed 23

Trying to update a very specific value in a dataframe. First looking at the last row of a given value in the index and then selecting a column of that row. However, I am unable to update it, as you can see. I have tried using df.at[] too but I am unable too, as it does not give me the utility I want.

I would appreciate help.

Code below:

df = pandas.DataFrame([['ABBOTSFORD', 427000, 448000],['ABBOTSFORD', 427000, 448000],
                    ['ABERFELDIE', 534000, 600000],['ABBOTSFORD', 427000, 66666]],
                   columns=['Locality', 2005, 2006])
                   
df.set_index('Locality', inplace=True)
 
print(df)
print('\n')
 
df.loc['ABBOTSFORD'].iloc[-1].loc[2006] = 3000
print(df.loc['ABBOTSFORD'].iloc[-1].loc[2006])
 
print('\n')
print(df)
 
Output: 
Locality     2005     2006        
ABBOTSFORD  427000  448000
ABBOTSFORD  427000  448000
ABERFELDIE  534000  600000
ABBOTSFORD  427000   66666
 
 
66666
 
 

Locality     2005     2006       
ABBOTSFORD  427000  448000
ABBOTSFORD  427000  448000
ABERFELDIE  534000  600000
ABBOTSFORD  427000   66666

Updating a specific value in a dataframe.

dataframe pandas python
1 Answers

What you try to do is tricky as you have duplicated indices. Also, you cannot chain loc operations for assignment (you risk to update a copy).

From what I understand you want to update the last 'ABBOTSFORD' for 2006.

You can get help from numpy to determine its position to be able to use iloc. For the columns, use get_loc

idx = np.arange(df.shape[0])[df.index=='ABBOTSFORD'][-1]
col = df.columns.get_loc(2006)

df.iloc[idx, col] = 3000

output:

              2005    2006
Locality                  
ABBOTSFORD  427000  448000
ABBOTSFORD  427000  448000
ABERFELDIE  534000  600000
ABBOTSFORD  427000    3000
mozway0
Related