CODEWITHSUNDEEP
pythonmysqlpandastypesread-sql
wawawa
wawawa

Reputation: 3355

How to resolve if `pandas.read.sql()` converts `bigint` to `float64` automatically?

I have a field ID, in MySQL Workbench it's Bigint(20) type, and if I query in the workbench, it looks like this:

ID
1111111111
2222222222
3333333333
...

If I use pandas.read_sql() then print out this column, it looks like this:

ID
1.111111e+09
2.222222e+09
3.333333e+09
...

and by checking df.dtypes, this columns is float64 type, if I try to convert it to str by using: df['ID'] = df['ID'].astype('str'), this column will become like this:

ID
1111111111.0
2222222222.0
3333333333.0

I'm confused... I thought the Bigint(20) after read_sql() will become int64, but why here it becomes float64, is there a way I can just converted it to int64 or str and it looks like 1111111111 (without the float .0?)

I also tried df['ID'] = df['ID'].astype('int64'), but it gave me error ValueError: Cannot convert non-finite values (NA or inf) to integer

Can someone help please... Thanks.

Upvotes: 1

Views: 1551

Answers (1)

trecoMP
trecoMP

Reputation: 1

This message:

ValueError: Cannot convert non-finite values (NA or inf) to integer

normally occurs when you have a null value in a row. Solve it by adding value 0 where it is null.

df['column'] = df['column'].fillna(0)

Then convert to int:

df['column'] = df['column'].astype(int)

Upvotes: 0

Related Questions