Python - Pandas read_sql_query automatically rounds after 6 Digits

Question

I'm trying to read data from sqlite3 database into a Dataframe. The problem is, that my numbers in the DB look like following 21.639929701230166. And if I print my df the number gets rounded to 21.639930. But I need the exact number for my PCA.

db_conn = sqlite3.connect(DATABASE_FILE)
sql_query = pd.read_sql_query('''SELECT AvgRT FROM sensordata''', db_conn)
df = pd.DataFrame(sql_query)

I saw that there is float_precision for csv. https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html

Is there anything for SQL?

I already tried to cast it (like seen here: https://stackoverflow.com/a/70603287:

sql_query = pd.read_sql_query('''SELECT cast(AvgRT as decimal(25,25)) as AvgRT FROM sensordata''', db_conn)

but this results in the same number 21.639930 as before.

try doing this might work pd.options.display.precision = 0 — Amey
– Amey, Commented Jul 4, 2022 at 18:27
If you load them as varchar/strings, your code works: db-fiddle.com/f/pZhq1bL8QRomixKQjC2nRc/0. — lemon
– lemon, Commented Jul 4, 2022 at 18:35
@Ameya thanks that worked.Maybe my question were unclear, but I needed to change it to 20 or similar., to show the following decimals. Now a following Question for understanding. Does it just display 6 decimals and calculates still with the normal number or does it calculate with the rounded number? — ngerber
– ngerber, Commented Jul 4, 2022 at 18:41
@lemon Thanks for your fast answer. pd.options.display.precision = 20, did it for me — ngerber
– ngerber, Commented Jul 4, 2022 at 18:43
Post it as the answer of this question and mark it as the accepted answer. @ngerber — lemon
– lemon, Commented Jul 4, 2022 at 18:44

Amey · Accepted Answer · 2022-07-04 18:48:23Z

0

Yeah, the default precision in pandas is 6.

either you can use this pd.options.display.precision = 0 handy thing or use one of the below.

# (1) Round to specific decimal places – Single DataFrame column
df['DataFrame column'].round(decimals=number of decimal places needed)

# (2) Round up – Single DataFrame column
df['DataFrame column'].apply(np.ceil)

# (3) Round down – Single DataFrame column
df['DataFrame column'].apply(np.floor)

# (4) Round to specific decimals places – Entire DataFrame
df.round(decimals=number of decimal places needed)

answered Jul 4, 2022 at 18:48

Amey

4804 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Python - Pandas read_sql_query automatically rounds after 6 Digits

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related