jake wong jake wong - 6 months ago 68
Python Question

Pandas could not convert string to float when reading from SQL database

I have a SQLite database created with SQLAlchemy which has the format below

Code Names Amt1 Amt2 Amt3 Amt4
502 Real Management 11.4 1.3 - -
5TG 80Commercial 85.8 152 4.845 4.12%
AZG Equipment Love 11.6 117.1 - -


But when I tried to read this into a pandas dataframe by using

pandas.read_sql('sqlite_table', con=engine)


It returns me with an error
ValueError: could not convert string to float: '-'


I get that pandas can't register
-
in the Dataframe, but how can I work around this? Is it possible to read it
-
as
0
or something??

Answer

Update all rows in Amt3 with - (if you have set up your login auths and defined cursor) will be something like this:

cur.execute("UPDATE sqlite_table SET Amt3 = 0 WHERE Amt3 = '-'")

This seems to work fine for me, even with -, what is the type of your Atm3?

import pandas as pd
import sqlite3

con = sqlite3.connect(r"/Users/hugohonorem/Desktop/mytable.db") #create database

conn = sqlite3.connect('mytable.db') #connect to database
c = con.cursor() #set 

c.execute('''CREATE TABLE mytable
             (Code text, Names text, Atm1 integer, Atm2 integer, Atm3 integer, Atm4 integer)''')

c.execute("INSERT INTO mytable VALUES ('502', 'Real Management', '11.4', '1.3', '-' , '-')")
conn.commit()

So we have replicated your table, we can now remove your -

c.execute("UPDATE mytable SET Atm3 = NULL WHERE Atm3 = '-'") #set it to null

df = pd.read_sql("SELECT * from mytable", con)
print df

This will give us the output:

  Code            Names  Atm1 Atm2  Atm3 Atm4
   502  Real Management  11.4  1.3  None    -

However, as you can see I can retrieve the table with Atm4 being -

Comments