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

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")

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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')