>>> from env_helper import info; info()
页面更新时间： 2020-03-08 18:01:49
操作系统/OS: Linux-4.19.0-8-amd64-x86_64-with-debian-10.3 ；Python: 3.7.3
7.8. Pandas与SQL比较¶
由于许多潜在的Pandas用户对SQL有一定的了解，因此本文章旨在提供一些如何使用Pandas执行各种SQL操作的示例。
>>> import pandas as pd
>>> url = 'tips.csv'
>>> tips=pd.read_csv(url)
>>> print (tips.head())
total_bill tip sex smoker day time size
0 16.99 1.01 Female No Sun Dinner 2
1 10.34 1.66 Male No Sun Dinner 3
2 21.01 3.50 Male No Sun Dinner 3
3 23.68 3.31 Male No Sun Dinner 2
4 24.59 3.61 Female No Sun Dinner 4
文件：tips.csv -
total_bill,tip,sex,smoker,day,time,size
0,16.99,1.01,Female,No,Sun,Dinner,2
1,10.34,1.66,Male,No,Sun,Dinner,3
2,21.01,3.50,Male,No,Sun,Dinner,3
3,23.68,3.31,Male,No,Sun,Dinner,2
4,24.59,3.61,Female,No,Sun,Dinner,4
选择(Select)¶
在SQL中，选择是使用逗号分隔的列列表(或选择所有列)来完成的，例如 -
SELECT total_bill, tip, smoker, time
FROM tips
LIMIT 5;
在Pandas中，列的选择是通过传递列名到DataFrame -
tips[['total_bill', 'tip', 'smoker', 'time']].head(5)
下面来看看完整的程序 -
>>> import pandas as pd
>>>
>>> url = 'tips.csv'
>>>
>>> tips=pd.read_csv(url)
>>> rs = tips[['total_bill', 'tip', 'smoker', 'time']].head(5)
>>> print(rs)
total_bill tip smoker time
0 16.99 1.01 No Dinner
1 10.34 1.66 No Dinner
2 21.01 3.50 No Dinner
3 23.68 3.31 No Dinner
4 24.59 3.61 No Dinner
调用没有列名称列表的DataFrame将显示所有列(类似于SQL的*)。
WHERE条件¶
SELECT * FROM tips WHERE time = 'Dinner' LIMIT 5;
数据帧可以通过多种方式进行过滤; 最直观的是使用布尔索引。
tips[tips['time'] == 'Dinner'].head(5)
下面来看看完整的程序 -
>>> import pandas as pd
>>>
>>> url = 'tips.csv'
>>>
>>> tips=pd.read_csv(url)
>>> rs = tips[tips['time'] == 'Dinner'].head(5)
>>> print(rs)
total_bill tip sex smoker day time size
0 16.99 1.01 Female No Sun Dinner 2
1 10.34 1.66 Male No Sun Dinner 3
2 21.01 3.50 Male No Sun Dinner 3
3 23.68 3.31 Male No Sun Dinner 2
4 24.59 3.61 Female No Sun Dinner 4
上述语句将一系列True/False对象传递给DataFrame，并将所有行返回True。 通过GroupBy分组
此操作将获取整个数据集中每个组的记录数。 例如，一个查询提取性别的数量(即，按性别分组) -
SELECT sex, count(*)
FROM tips
GROUP BY sex;
在Pandas中的等值语句将是 -
>>> tips.groupby('sex').size()
sex
Female 2
Male 3
dtype: int64
下面来看看完整的程序 -
>>> import pandas as pd
>>>
>>> url = 'tips.csv'
>>>
>>> tips=pd.read_csv(url)
>>> rs = tips.groupby('sex').size()
>>> print(rs)
sex
Female 2
Male 3
dtype: int64
前N行¶
SQL(MySQL数据库)使用LIMIT返回前n行 -
SELECT * FROM tips
LIMIT 5 ;
在Pandas中的等值语句将是 -
>>> tips.head(5)
| total_bill | tip | sex | smoker | day | time | size | |
|---|---|---|---|---|---|---|---|
| 0 | 16.99 | 1.01 | Female | No | Sun | Dinner | 2 |
| 1 | 10.34 | 1.66 | Male | No | Sun | Dinner | 3 |
| 2 | 21.01 | 3.50 | Male | No | Sun | Dinner | 3 |
| 3 | 23.68 | 3.31 | Male | No | Sun | Dinner | 2 |
| 4 | 24.59 | 3.61 | Female | No | Sun | Dinner | 4 |
下面来看看完整的程序 -
>>> import pandas as pd
>>>
>>> url = 'tips.csv'
>>>
>>> tips=pd.read_csv(url)
>>> rs = tips[['smoker', 'day', 'time']].head(5)
>>> print(rs)
smoker day time
0 No Sun Dinner
1 No Sun Dinner
2 No Sun Dinner
3 No Sun Dinner
4 No Sun Dinner
这些是比较的几个基本操作，在前几章的Pandas库中学到的。