python數(shù)據(jù)分析我覺得可以用pandasql，真香！

python非常好用，pandas也不差，但是，SQL仍然是最香的語言，如果把兩者結(jié)合起來怎么樣？請看~

下載、導(dǎo)入第三方庫

下載：python -m pip install pandasql
導(dǎo)入：from pandasql import sqldf,load_births,load_meat

1from?pandasql?import?sqldf,load_births,load_meat

加載內(nèi)置數(shù)據(jù)集

1df1?=?load_births()
2df2?=?load_meat()

預(yù)覽數(shù)據(jù)查看前幾行

1df1.head()

預(yù)覽數(shù)據(jù)查看后幾行

1df2.tail()

調(diào)用sqldf方法，參數(shù)為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]

對比一下，結(jié)果是相同的

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'})

# Result
beef        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