How to read sql table in float format instead of object?

Question

I'm reading a sql table full of stock prices with pandas, but I am having the problem that the prices are object type when using them on my python code. In my sql table i have some #N/A values that I cannot get rid of (I don't want to).

I know that it would be possible to give the prices the float type on my code, but I would like them to be floats when I import them from the sql server.

I tried changing the "," separator and keeping it but i don't get results.

Thanks for your attention

import pandas as pd
import sqlite3

conn = sqlite3.connect('C:\\Users\\Desktop\\db.sqlite3')
df = pd.read_sql("SELECT replace(IndexLevel, ',', '.') AS prices, PriceDate FROM IndexLevel",
                  conn, parse_dates="PriceDate")

What are the actual column types in the SQL database schema? Please read minimal reproducible example and try to make it possible for others to reproduce the problem exactly. — Karl Knechtel
– Karl Knechtel, Commented Jun 13, 2022 at 21:06
Date: TEXT, Ticker: TEXT, IndexLevel: NUMERIC (also tried with INT) — Arturo M
– Arturo M, Commented Jun 13, 2022 at 21:08
Have you seen the documentation for read_sql? In particular, does the coerce_float argument solve the problem? — Karl Knechtel
– Karl Knechtel, Commented Jun 13, 2022 at 21:10

Corralien · Accepted Answer · 2022-06-13 21:08:54Z

1

You can convert your column after extracting it:

mapping = {'#N/A': np.nan, ',': '.'}
df['prices2'] = df['prices'].replace(mapping, regex=True).astype(float)
print(df)

# Output
    prices  prices2
0     #N/A      NaN
1  1234,32  1234.32
2  5689,23  5689.23

answered Jun 13, 2022 at 21:08

Corralien

121k8 gold badges43 silver badges68 bronze badges

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

Comments

Nik · Accepted Answer · 2022-06-13 21:09:55Z

0

in order for the column to be float type, the values have to be numeric. Thus you'll have to get rid off the N/A values first then convert the column to float.

# doesn't have to be 0, can be any value that works for your dataset
df['Price'] = df['Price'].fillna(0)
df['Price'] = df['Price'].astype('float64')

now your column is float

answered Jun 13, 2022 at 21:09

Nik

1714 silver badges16 bronze badges

Comments

leon gxl · Accepted Answer · 2022-06-13 21:31:52Z

0

You can convert '#N/A' to null values in pandas. Also, you will need to remove the thousands separator (for example '1,000' to '1000') before converting to float values.

import numpy as np

df['prices'] = df['prices'].str.replace(',', '', regex=True).str.replace('#N/A', np.nan).astype('float')

answered Jun 13, 2022 at 21:31

leon gxl

754 bronze badges

Collectives™ on Stack Overflow

How to read sql table in float format instead of object?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related