How to handle NaNs in pandas dataframe integer column to postgresql database

Question

I have a pandas dataframe with a "year" column. However some rows have a np.NaN value due to an outer merge. The data type of the column in pandas is therefore converted to float64 instead of integer (integer cannot store NaNs?). Next, I want to store the dataframe on a postGreSQL database. For this I use:

df.to_sql()

Everything works fine but my postGreSQL column is now type "double precision" and the np.NaN values are now [null]. This all makes sense since the input column type was float64 and not integer type.

I was wondering if there is a way to store the results in an integer type column with [nans].

Example Notebook

Result of Ami's answer:

@coldspeed that changes the table schema - not sure it's warranted. — Ami Tavory
– Ami Tavory, Commented May 18, 2018 at 14:51
@AmiTavory If the schema is already defined, then I don't think so. By the way, fillna will not downcast... :) — cs95
– cs95, Commented May 18, 2018 at 14:52
@coldspeed Ooh, excellent point - missed that. Will update. thanks! — Ami Tavory
– Ami Tavory, Commented May 18, 2018 at 14:53
@coldspeed, the result is still double precision in postgreSQL. I've added a notebook to my question to check if my implememtation is wrong. — Rutger Hofste
– Rutger Hofste, Commented May 18, 2018 at 15:10

Ami Tavory · Accepted Answer · 2018-05-18 14:50:41Z

6

(integer cannot store NaNs?)

No, they cannot. If you look at the postgresql numeric documentation, you can see that the number of bytes, and ranges, are completely specified, and integers cannot store this.

A common solution in this case is to decide, by convention, that some number is logically a nan. In your case, if it is year, you might choose a negative value (or just -1) as that. Before writing, you could use

df.year = df.year.fillna(-1).astype(int)

Alternatively, you can define another column as year_is_none.

Alternatively, you can store them as floats.

These solutions range from most efficient, to least efficient in terms of memory.

answered May 18, 2018 at 14:50

Ami Tavory

76.7k13 gold badges152 silver badges196 bronze badges

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

1 Comment

oneturkmen Over a year ago

Alternatively, if you use NUMERIC data type, you can store Infinity, -Infinity as well as NaN values in the database.

Mesut Aslan · Accepted Answer · 2018-05-18 20:06:50Z

1

You should use it;

df.year = df.year.fillna(-1) OR 0

answered May 18, 2018 at 20:06

Mesut Aslan

311 bronze badge

Collectives™ on Stack Overflow

How to handle NaNs in pandas dataframe integer column to postgresql database

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related