1 資料查詢的工具
資料分析過程中,少不了資料查詢和資料清洗的工作,相關的工具有Excel、SQL、Python等。
對于少量資料:Excel是圖形化操作的辦公軟體,處理少量資料不成問題。處理方法是:使用工具欄的工具或函數,“查詢”功能對應的是篩選,“比對”功能對應的vlookup函數。
對于大量資料:一般使用sql或python處理。sql是結構化查詢語言,資料一般存儲在資料庫中,使用sql查詢出來。python是一門程式設計語言,有很多資料處理的包,比如pandas包就廣泛的應用于資料處理。
2 需求和資料源介紹
2.1 資料查詢的需求
一般情況下需求有:了解資料概覽、查詢某一列資料、分組、聚合、表的連接配接、自定義排序、子查詢等。
2.2 資料源介紹
兩個表的資料
一共2個表,sales表是使用者和購買商品的資訊,product表是産品資訊。已經使用mysql建立表和添加資料,pandas已經加載資料。
3 SQL和Python資料查詢的對比
在資料查詢方面,SQL非常經典,是以本文把SQL作為标準,使用Pandas來實作SQL類似的查詢功能。
3.1 資料概覽
拿到一個表,首先需要了解表的基本資訊,有哪些字段,分别是什麼意思,快速獲得資料概覽。
- SQL:desc sales;
- Pandas:sales.describe()
3.2 查詢前n行記錄
- SQL:select * from sales limit 5;
- Pandas:sales.head(5)
3.3 查詢某幾列資料
- SQL:select id, date from sales;
- Pandas:sales[['id', 'date']]、sales.loc[:, ['id', 'date']]、sales.iloc[:, [0, 2]]
3.4 使用篩選條件查詢資料
- SQL:
select * from sales where id > 2;
select * from sales where id=2 and price>=5;
- Pandas:
sales[sales['id'] > 2]
sales[(sales['id'] == 2) & (sales['price'] >= 5)] # pandas中用&符号實作多條件
3.5 判斷空值
- SQL:select * from sales where name is null;
- Pandas:sales[sales['name'].isnull()]
3.6 剔除重複值
- SQL:select distinct id , name from sales;
- Pandas:sales[sales.duplicated(subset=['id', 'name'])]
3.7 分組
- SQL:select id,name from sales group by id;
- Pandas:[x[1] for x in sales.groupby(by='id', axis=0)]
3.8 聚合
- SQL:select count(*) from sales;
- Pandas:sales.count()
3.9 分組後聚合
- SQL:select count(id) , sum(amount) from sales group by id;
- Pandas:sales.groupby(by='id').agg({'productId': 'count', 'amount': 'sum'}
3.10 排序
- SQL:select * from sales order by id asc, date desc;
- Pandas:sales.sort_values(by=['id', 'date'],ascending=[True, False])
3.11 case-when
- SQL:select case when amount=0 then'沒有購買' when amount<=5 then '購買數量少' else '購買數量多' end from sales;
- Pandas:pd.cut(sales['amount'], bins=[-0.01, 0, 5, 99], labels=['沒有購買', '購買數量少', '購買數量多'])
3.12 連接配接
- SQL:select a.*, b.* from sales a join product b on a.productId=b.productId;
- Pandas:pd.merge(sales, product, how='inner', on='productId')
3.13 合并
- SQL:select * from sales where id>3 union all select * from sales where id>4;
- Pandas:pd.concat([sales[sales['id'] > 3], sales[sales['id'] > 4]], axis=0)
3.14 值的計數
- SQL:select * from(select id, count(id) as count from sales group by id ) as t order by t.count desc;
- Pandas:pd.value_counts(sales['id'])
3.15 自定義排序
- SQL:select * from sales order by field(name,'david','mike','beck','john','kobe','jason');
- Pandas:
name_order = ['david','mike','beck','john','kobe','jason'] # 排序字典
sales['name_order'] = sales['name'].astype('category').cat.set_categories(name_order) # 新增一列用于排序
sales.sort_values(by='name_order') # 自定義排序結果
3.16 子查詢
- SQL:select * from sales where productId in (select productId from product where productName='蘋果');
- Pandas:sales.loc[sales['productId'].isin(product.loc[product['productName'] == '蘋果', 'productId']), :] # 先從product取出productName為蘋果的productId,再比對sales表的productId構構造出布爾索引,最後再從sales篩選這些資料。
4 總結
SQL和Pandas對比總結
不同的人會使用不同的工具,sql和pandas适用于不同的人群,工具之間本身并沒有誰好誰壞,因為都是程式去實作的。資料分析師一般需要同時掌握sql和pandas,為了不混淆文法,是以做此對比。