The Python library is used to manipulate the Python library

Pandas is a library for data processing and analysis. Pandas is a library for data processing and analysis.

But admittedly, not all programmers can use Python, and not all Pythoners can use Pandas.

The good news is that with PandassQL, you can use SQL to manipulate the DataFrame.

# import related libraries
import numpy as np
import pandas as pd

from pandasql import sqldf, load_meat, load_births
Copy the code

basis

The main function in pandasQL is SQLDF, which takes two arguments:

• An SQL query statement
• A set of session/environment variables (locals() 或 globals())

For convenience, we can define a function that we can call easily.

pysqldf = lambda sql: sqldf(sql, globals())
Copy the code

Next we import some data.

meat = load_meat()
meat.head()
Copy the code

births = load_births()
births.head()
Copy the code

The query

The syntax used by PandassQL is SQLite syntax. Any DataFrame will be automatically detected by PandassQL and you can query them as such.

Limit the number of

Let’s see how to limit the number of data items. So let’s get the first two.

sql = "select * from births limit 2"
pysqldf(sql)
Copy the code

In addition to limiting the first N pieces of data from scratch, we can also set offsets. Let’s get the first two pieces of data from the second row.

sql = "select * from births limit 2 offset 2"
pysqldf(sql)
Copy the code

Finite field

Since this is SQL, we can of course restrict the required fields in the query. We limit the acquisition of the indicated points here.

sql = "select births from births limit 2"
pysqldf(sql)
Copy the code

The sorting

Sorting functionality is also very common and PandassQL supports it perfectly. We have programmed the holocene progression from date to date.

sql = "select * from births order by date desc, births asc limit 2"
pysqldf(sql)
Copy the code

Restricted query conditions

We can specify where to query data that meets the requirements. Here we select the data with Turkey not empty and date after 1974-12-31.

sql = """
select *
from meat
where turkey not null
and date > '1974-12-31'
limit 5
"""
pysqldf(sql)
Copy the code

The aggregation

Aggregation is essential for data analysis, and PandassQL certainly supports it. Here we group the points by year and sum up, mean, Max and minimum the points.

sql = """ select strftime('%Y', date) as year, sum(births), avg(births), max(births), min(births) from births group by strftime('%Y', date) limit 3 """
pysqldf(sql)
Copy the code

associated

Association is also a very common operation. Here we associate the meat and moisture of the DataFrame according to the field date as the condition.

sql = """ select m.date, b.births, m.beef from meat m inner join births b on m.date = b.date order by m.date limit 5; "" "
pysqldf(sql)
Copy the code

In addition to the common functions listed above, PandassQL supports many more operations that are based on SQLite syntax, which you can explore for yourself.

To learn more about artificial intelligence, please follow our official account: AI Pai