How to read excel file into df and query and analyze it with SQL in python

This article is about how to read excel files into df in python and query and analyze them with SQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Download and import third-party libraries

Download: python-m pip install pandasql

Import: from pandasql import sqldf,load_births,load_meat

From pandasql import sqldf,load_births,load_meat loads the built-in dataset df1 = load_births () df2 = load_meat ()

Preview the data to view the first few rows

Df1.head ()

Call the sqldf method with the argument sql statement sql = "select date,count (*) as n from df1 group by date order by n desc;" result = sqldf (sql) result [result ['n'] = = 3]

By comparison, the result is the same.

Df1 ['date']. Value_counts (). Head (12) 1991-12-01 31991-10-01 31991-06-01 31991-08-01 31991-09-01 31991-04-01 31991-02-01 3 1991-01 31 1991-03-01 3 1991-07-01 1991-01 3 1991-11-01 3 1991-05-01 3Name: date, dtype: int64

Aggregation effect sql2 = "" select max (beef), min (pork), sum (turkey), count (veal) from df2; "" result2 = sqldf (sql2) result2 multi-table join is also possible. Oh ~ df1.merge (df2,on='date',how = 'inner') sql3 = "select df1.*,df2.* from df1 inner join df2 on df1.date = df2.date "" result3 = sqldf (sql3) resul Thank you for reading! On "how to read excel files in python as df and use SQL query, analysis" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!