Replace a certain value in python pandas dataframe

Question

I am new to python panda, I am trying to read an excel file and do some calculations and fill up some rows. When I try to fill up the value and print the result, the results is still the same before I assign the value to it.

Here is what I did.

df.loc[1][13] = 2

df is just what I got from my excel file like this

df = pd.read_excel('test.xlsx',header=5)

I already checked the data in df, they are perfectly fine.

so the original value in row 1, column 13 is nan, I want to replace it with a number, such as a 2, but the code above just would not work.

df.iat[1, 13] = 2 pandas.pydata.org/pandas-docs/version/0.23/… — Alexander
– Alexander, Commented Sep 19, 2019 at 18:49
Thanks! It worked! Still I don't know why the way I did it did not work — Jingyu
– Jingyu, Commented Sep 19, 2019 at 18:59
Your indexing .loc[row][column] is called a chained assignment. Avoid it like the plague... pandas.pydata.org/pandas-docs/stable/user_guide/… — Alexander
– Alexander, Commented Sep 19, 2019 at 19:02

Deven · Accepted Answer · 2019-09-19 18:56:18Z

2

As mentioned in this doc https://pandas.pydata.org/pandas-docs/version/0.23/10min.html#selection-by-position, .loc method provides selection by label whereas .iloc method provides selection by position. Also, since output of your selection is scalar you can also use .iat method. So, either of the below will work in your case:

df.iloc[1, 13] = 2 
df.iat[1,13] = 2

In general, I prefer to use one of the .loc or .iloc depending upon whether the selection is by label or by position.

answered Sep 19, 2019 at 18:56

Deven

7411 gold badge7 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jingyu Over a year ago

Thank you! This is really helpful

Deven Over a year ago

It would be really nice if you accept this answer too.

MarkH · Accepted Answer · 2019-09-19 19:02:03Z

1

check out the pandas documentation for iloc

Based on that a very simple example for replacing specifically the value in one given cell could look like this:

import pandas as pd
mydict = [{'a': 1, 'b': 2, 'c': 3, 'd': 4},\
          {'a': 100, 'b': 200, 'c': 300, 'd': 400},\
          {'a': 1000, 'b': 2000, 'c': 3000, 'd': 4000 }]
df = pd.DataFrame(mydict)

print(df.iloc[0,2]) # print original value from row 0, column 2
df.iloc[0,2] = 9 # replace value in row 0, column 2
print(df.iloc[0,2]) #verify replaced value in row 0, column 2

Hope I got the question right...

answered Sep 19, 2019 at 19:02

MarkH

3201 silver badge13 bronze badges

Collectives™ on Stack Overflow

Replace a certain value in python pandas dataframe

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related