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, May 18 '18 at 14:51
@AmiTavory If the schema is already defined, then I don't think so. By the way, fillna will not downcast... :) — cs95, May 18 '18 at 14:52
@coldspeed Ooh, excellent point - missed that. Will update. thanks! — Ami Tavory, May 18 '18 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, May 18 '18 at 15:10
See this: https://stackoverflow.com/questions/61138888/convert-pandas-column-containing-floats-and-nan-values-from-float64-to-nullabl/63381205#63381205 — deponovo, Sep 05 '20 at 11:16

score 5 · Answer 1 · answered May 18 '18 at 14:50

(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.

score 0 · Answer 2 · answered May 18 '18 at 20:06

0

You should use it;

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

answered May 18 '18 at 20:06

Mesut Aslan

21
1

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

2 Answers2