python数据分析我觉得可以用pandasql,真香!
python非常好用,pandas也不差,但是,SQL仍然是最香的语言,如果把两者结合起来怎么样?请看~
下载、导入第三方库
下载:python -m pip install pandasql
导入:from pandasql import sqldf,load_births,load_meat
1from pandasql import sqldf,load_births,load_meat
加载内置数据集
1df1 = load_births()
2df2 = load_meat()
预览数据查看前几行
1df1.head()
预览数据查看后几行
1df2.tail()
调用sqldf方法,参数为sql语句
1sql = """
2 select
3 date,count(*) as n
4 from df1
5 group by date
6 order by n desc;
7 """
8result = sqldf(sql)
9result[result['n']==3]
对比一下,结果是相同的
1df1['date'].value_counts().head(12)
11991-12-01 3
21991-10-01 3
31991-06-01 3
41991-08-01 3
51991-09-01 3
61991-04-01 3
71991-02-01 3
81991-01-01 3
91991-03-01 3
101991-07-01 3
111991-11-01 3
121991-05-01 3
13Name: date, dtype: int64
聚合效果
1sql2 = """
2 select max(beef),min(pork),sum(turkey),count(veal)
3 from df2;
4 """
5result2 = sqldf(sql2)
6result2
1df2.agg({'beef':max,'pork':min,'turkey':sum,'veal':'count'})
# Resultbeef 2512.0
pork 124.0
turkey 185937.3
veal 827.0
dtype: float64
多表连接也是可以的哦~
1df1.merge(df2,on='date',how = 'inner')
1sql3 = """
2 select df1.*,df2.*
3 from df1 inner join df2
4 on df1.date = df2.date;
5 """
6result3 = sqldf(sql3)
1result3
后记:
这个库使用起来非常简单,只要你会写SQL语句就可以用,df与sql,哪个方便用哪个。说实话,我还是喜欢用SQL,当我把excel文件读取为df后,用SQL查询、分析,它不香吗?!
评论