How to show float values with pandas.read_sql from a sqlite query

Question

I need to get this result for an assignment using python/sqlite3. required result

I did my query in MYSQL, and I got the answer already to the assignment question. Since I am learning, I find it easier to do the queries using MySQL Workbench first. result in MySQLWorkbench

However, When I try to do it in Jupyter notebook with Sqlite3, it only shows the zeros on the percentage column. I am using the function pd.read_sql_query. I went to the documentation and could not find any arguments there that would do what I want, or I did not understand it. I played with the coarse_float argument, but it did not make a difference. I am learning, so sometimes, I do not understand the documentation completely.

query_results = pd.read_sql_query(query1,conn)

This is what I get in my Jupyter notebook: Output un Jupyter Notebook

I know the numbers are there because if I multiple the column ”percentage_female_only_movie” fly 100, I see them. I would like to know how to show them like in MYSQLWorkbench.

Thank you for any help. An if you know any link where I can learn about this type of issues, I would love if you can share it.

Shubham Periwal · Accepted Answer · 2021-04-27 00:49:39Z

1

Try df[colname] = df[colname].astype(float).

This would convert your column to float and you should see the values

answered Apr 27, 2021 at 0:49

Shubham Periwal

2,2582 gold badges10 silver badges29 bronze badges

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

1 Comment

simplemente humano Over a year ago

I tried it before and it did not work. ""q11b_results = pd.read_sql_query(q11b,conn) q11b_results["Percentage_Female_only_Movie"].astype(float)""". I also tried """pd.options.display.float_format = "{:,.4}".format""", but it did not work either

simplemente humano · Accepted Answer · 2021-05-12 15:19:43Z

0

I found the solution. I needed to CAST the numerator and denominator of the column I was generating in SELECT statement of my query.

SELECT SUBSTR(TRIM(m.year),-4) AS Movie_year,
    ROUND(CAST(fcm.Female_Cast_Only_Movies*100 AS FLOAT)/ CAST(tm.movies_total AS FLOAT),2) AS Percentage_Female_only_Movie,
   tm.movies_total As Total_movies

FROM Movie AS m

output:

enter image description here

answered May 12, 2021 at 15:19

simplemente humano

551 silver badge10 bronze badges

Collectives™ on Stack Overflow

How to show float values with pandas.read_sql from a sqlite query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related