使用SQL操作Pandas以及使用DataFrame的query函数过滤数据

pip install -U pandasql

import pandas as pd
from pandasql import sqldf
df1 = pd.DataFrame({
    'name': ['ZhangFei', 'GuanYu', 'a', 'b', 'c'],
    'data1': range(5)
})
df1

sqldf("select * from df1 where name ='ZhangFei'", globals())

pysqldf = lambda sql: sqldf(sql, globals())

pysqldf("select * from df1 order by data1 desc")

from pandasql import sqldf, load_meat, load_births
pysqldf = lambda q: sqldf(q, globals())
meat = load_meat()
births = load_births()
pysqldf("SELECT * FROM meat LIMIT 10;").head()
Out:
date beef veal pork lamb_and_mutton broilers other_chicken turkey
0 1944-01-01 00:00:00 751 85 1280 89 None None None
1 1944-02-01 00:00:00 713 77 1169 72 None None None
2 1944-03-01 00:00:00 741 90 1128 75 None None None
3 1944-04-01 00:00:00 650 89 978 66 None None None
4 1944-05-01 00:00:00 681 106 1029 78 None None None

q = """SELECT
  m.date, m.beef, b.births
FROM meat m 
INNER JOIN births b ON m.date = b.date;"""
joined = pysqldf(q)
joined.head()

q = """select
  strftime('%Y', date) as year,
  SUM(beef) as beef_total
FROM meat
GROUP BY year;"""
pysqldf(q).head()

pip install pandas  --upgrade

df = pd.DataFrame({'A': range(1, 6),
                   'B': range(10, 0, -2),
                   'C C': range(10, 5, -1)})
df

df[df.A > df.B]

df.query('A > B')

df.query('B == `C C`')