Edit CSV cell using pandas

Question

How to edit CSV cell value using pandas.

I'm trying like this

obj.set_value(rowindex, 'C', val)

Where I expect the old value in row = rowindex and column = C i.e.. df[rowindex][C] should get replced by val and my original csv should get update.

I know pandas provides, inplace=True, to do that, but set_value doesn't support that. How can I do this ?

I think the behavior of set_value() is inplace=True by default. Can you show us the error you are facing ? — michaelg, Mar 30 '18 at 12:07
@michaelg No error. It just doesn't update my original csv file. Just a warning `FutureWarning: set_value is deprecated and will be removed in a future release. Please use .at[] or .iat[] accessors instead` — prashantitis, Mar 30 '18 at 12:16
The pandas data frame is not the same thing as the original csv file. Making changes in the data frame doesn't change the file unless you explicitly write those changes back. — Mees de Vries, Mar 30 '18 at 12:18
@MeesdeVries That is what my questions is. How to do that? I tried with `inplace=True` that gives error while @michaelg says it is by default. — prashantitis, Mar 30 '18 at 12:19
When you write CSV, you mean the original file of the dataframe ? — michaelg, Mar 30 '18 at 12:19
I see, then you would use df.to_csv() to save back on your original file — michaelg, Mar 30 '18 at 12:21
I even tried `df.to_csv()` which was failing, catch was to use `df.to_csv("file.csv", index=False)` — prashantitis, Mar 30 '18 at 12:28

score 3 · Answer 1 · answered Mar 30 '18 at 12:28

3

Here is the content of the original file student.csv:

StudentID,Name,Username,Password,Moderator
0001,Foo Bar,test,abc123,N
0002,Baz Qux,bob,bcd986,Y

Load CSV

df = pd.read_csv('students.csv')

Change the cell and save back to the original file

df.set_value(0,'Name','changed')
df.to_csv('students.csv', index=False)

result

StudentID,Name,Username,Password,Moderator
1,changed,test,abc123,N
2,Baz Qux,bob,bcd986,Y

answered Mar 30 '18 at 12:28

michaelg

944
6
12

exactly, though it is working for most of the rows, can you tell why it is failing for some rows. This is the stack trace `PermissionError: [Errno 13] Permission denied: 'file.csv'` – prashantitis Mar 30 '18 at 12:31
I cannot reproduce your error. I am using python 3.6. – michaelg Mar 30 '18 at 12:37
If you can show the exact code that produces your error, we may spot the problem. – michaelg Mar 30 '18 at 12:39

score 1 · Answer 2 · answered Mar 30 '18 at 12:37

Changing the data frame doesn't change the csv file since they are not the same thing. You have a few options.

Read the csv into a 2D list update the desired cell and the write the 2D list back to a csv file. Help
Update the dataframe then use df.to_csv()

Try something like this:

df..set_value(rowindex, 'C', val)
df.to_csv(file_name, encoding='utf-8', index=False)

Edit CSV cell using pandas

2 Answers2

Load CSV

Change the cell and save back to the original file

result