Replacing empty cells with values Python-Pandas

Question

I have a csv file that looks like this

col1,col2,col3,col4,col5

value1,value2,value3,value4,value5

,,value6,value7,value8

,,value10,value11,value12

I would need to insert values in the empty cells.

I am reading the data with pandas like this

import pandas as pd

data = pd.read_csv(file).fillna('yellow', 'blue')

any sugestions?

Update: the error is solved The rows in my csv where an editing mistake. The main problem is how to write in to the empty cells custom values.

Expected result

col1,col2,col3,col4,col5

value1,value2,value3,value4,value5

yellow,blue,value6,value7,value8

yellow,blue,value10,value11,value12

actual result

col1,col2,col3,col4,col5

value1,value2,value3,value4,value5

NaN,NaN,value6,value7,value8

NaN,NaN,value10,value11,value12

Initial problem -> Solved

I am getting an error like this:

pandas.errors.ParserError: Error tokenizing data. C error: Expected 5 fields in line 3, saw 6

you have 5 columns and at line 5 you have 6 values so due to this It is throwing error — Anurag Dabas
– Anurag Dabas, Commented May 11, 2021 at 13:42

Sırrı Kırımlıoğlu · Accepted Answer · 2021-05-11 14:16:42Z

2

You have 2 empty columns in lines 3 and 4. So, that makes it 6 columns but you have 5 headers. Just delete the first comma like this:

,value6,value7,value8,value9

For the second question you can try this:

df['col1'] = df['col1'].fillna('yellow')

edited May 11, 2021 at 14:16

answered May 11, 2021 at 13:46

Sırrı Kırımlıoğlu

6074 silver badges14 bronze badges

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

4 Comments

Scilla Over a year ago

hah. that was easy. was so focused on filling the empty cells that i missed the counting. so, how do I write values in my empty cells? what i have doesn't work. It replaces with NAN not with value

Sırrı Kırımlıoğlu Over a year ago

@Scilla like how? column based?

Scilla Over a year ago

yes, could you read my post again? I just updated it

Sırrı Kırımlıoğlu Over a year ago

@Scilla okay, I updated my answer, too. Like in it just select the column name and apply the function to it.

ryanongwx · Accepted Answer · 2021-05-11 14:00:35Z

1

The topmost row of your csv file if the headers and it will determine the number of columns in your data. As you have 5 headers,

col1, col2, col 3, col 4, col 5

The csv reader will expect there to be 5 columns in every subsequent row of data. As such, your 3rd and 4th row in your csv will be invalid and unalbe to be read by the csv reader.

Eg. ,,value6,value7,value8,value9 has a total of 6 columns.

answered May 11, 2021 at 14:00

ryanongwx

381 silver badge6 bronze badges

Comments

Scilla · Accepted Answer · 2021-05-11 14:21:51Z

0

This is what I need.

data = pd.read_csv(file)
hello = data.append({'col1': yello, 'col2': 'blue'}, ignore_index=True)

Now I'd need to write it again to the file.

answered May 11, 2021 at 14:21

Scilla

3953 silver badges17 bronze badges

Collectives™ on Stack Overflow

Replacing empty cells with values Python-Pandas

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related