Pandas Dataframe NaN values replace by no values

Question

I wanted to replace the NaN value by an empty value to write it into an mysql database. I don't want to drop (df.dropna()) the the full row neither to replace it by 0 using df.fillna(0). When using df.fillna('') or df.fillna('NULL') gives an error message:

(mysql.connector.errors.DatabaseError) 1265 (01000): Data truncated for column 'log_return' at row

The data in the dataframe looks like the following:

          date     price     log_return                 
0   2017-02-14     105.800   -0.006125                          
1   2017-02-13     106.450   0.004236 
2   2017-02-10     106.000   NaN

What I want is the following:

          date     price     log_return                 
0   2017-02-14     105.800   -0.006125                          
1   2017-02-13     106.450   0.004236 
2   2017-02-10     106.000

how do you want the data represented in MYSQL?

parsethis
– parsethis

2017-02-15 16:55:32 +00:00
Commented Feb 15, 2017 at 16:55 — parsethis
– parsethis, Commented Feb 15, 2017 at 16:55

parsethis · Accepted Answer · 2017-02-16 16:12:42Z

4

try this:

df.where(pd.notnull(df), None)

example

df = pd.DataFrame(np.eye(3))
df = df.where(lambda x: x==1, np.nan)
df = df.where(pd.notnull(df), None)

Note that pd.fillna(None) will not work, it leaves the NaN values untouched.

source https://github.com/pandas-dev/pandas/issues/1972

edited Feb 16, 2017 at 16:12

answered Feb 15, 2017 at 17:01

parsethis

8,0883 gold badges32 silver badges32 bronze badges

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

2 Comments

MCM Over a year ago

thanks a lot. Did not know that with ´None´ this is possible :).

Bryan S Over a year ago

I was surprised to learn that pd.fillna(None) doesn't work. That's discussed in the link, but since it fails quietly, and isn't directly mentioned in either the question or the answer, it might be helpful to add a note not to try it to the answer.

Collectives™ on Stack Overflow

Pandas Dataframe NaN values replace by no values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related