Pandas 使用入门
Pandas 数据基础
如何导入数据
方法一:通过词典导入
第一种导入的方法是把数据放在
data = {
'apples': [3, 2, 0, 1],
'oranges': [0, 3, 7, 2]
}
purchase = pd.DataFrame(data)
这样就得到了数据:
apples oranges
0 3 0
1 2 3
2 0 7
3 1 2
默认情况下每一行的被命名为 0、1、2、..., 我们在构建
purchase = pd.DataFrame(data, index=['June', 'Robert', 'Lily', 'David'])
apples oranges
June 3 0
Robert 2 3
Lily 0 7
David 1 2
方法二:通过 CSV 文件导入
比如对于如下的 CSV 文件:
apples oranges
June 3 0
Robert 2 3
Lily 0 7
David 1 2
可以通过如下方式读取:
purchase = pd.read_csv("./purchase.csv", index_col=0, delimiter="t")
参数解读:
-
参数指定了每一行名字对应在第几列
-
参数指定了 CSV 文件的分隔符, 默认是
如何观察数据
以 forsquare-nyc-and-tokyo-checkin-dataset 中的数据为例子来进行讨论. 导入数据:
nyc = pd.read_csv("./foursquare-nyc-and-tokyo-checkin-dataset/dataset_TSMC2014_NYC.csv")
首先要观察数据的大小:
nyc.shape
得到的结果是一个
然后可以观察每一个特征具体的数据类型:
nyc.info()
得到了:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 227428 entries, 0 to 227427
Data columns (total 8 columns):
userId 227428 non-null int64
venueId 227428 non-null object
venueCategoryId 227428 non-null object
venueCategory 227428 non-null object
latitude 227428 non-null float64
longitude 227428 non-null float64
timezoneOffset 227428 non-null int64
utcTimestamp 227428 non-null object
dtypes: float64(2), int64(2), object(4)
memory usage: 13.9+ MB
进一步的, 通过
userId latitude longitude timezoneOffset
count 227428.000000 227428.000000 227428.000000 227428.000000
mean 535.304817 40.754045 -73.974556 -253.392019
std 305.398774 0.072458 0.086209 43.234750
min 1.000000 40.550852 -74.274766 -420.000000
25% 287.000000 40.718330 -74.000633 -240.000000
50% 523.000000 40.747745 -73.983479 -240.000000
75% 793.000000 40.778374 -73.945709 -240.000000
max 1083.000000 40.988332 -73.683825 660.000000
可以通过
userId venueId venueCategoryId ... longitude timezoneOffset utcTimestamp
0 470 49bbd6c0f964a520f4531fe3 4bf58dd8d48988d127951735 ... -74.002581 -240 Tue Apr 03 18:00:09 +0000 2012
1 979 4a43c0aef964a520c6a61fe3 4bf58dd8d48988d1df941735 ... -74.044170 -240 Tue Apr 03 18:00:25 +0000 2012
2 69 4c5cc7b485a1e21e00d35711 4bf58dd8d48988d103941735 ... -73.883070 -240 Tue Apr 03 18:02:24 +0000 2012
3 395 4bc7086715a7ef3bef9878da 4bf58dd8d48988d104941735 ... -73.982519 -240 Tue Apr 03 18:02:41 +0000 2012
4 87 4cf2c5321d18a143951b5cec 4bf58dd8d48988d1cb941735 ... -73.989658 -240 Tue Apr 03 18:03:00 +0000 2012
如何得到具体统计信息
对于数值型变量, 可以通过
userId latitude longitude timezoneOffset
userId 1.000000 0.025320 -0.020197 0.059914
latitude 0.025320 1.000000 -0.117333 -0.028495
longitude -0.020197 -0.117333 1.000000 0.031681
timezoneOffset 0.059914 -0.028495 0.031681 1.000000
而对于类型变量 (categorical variable) , 可以通过
In [27]: nyc['venueCategory'].describe()
Out[27]:
count 227428
unique 251
top Bar
freq 15978
Name: venueCategory, dtype: object
也可以通过
In [29]: nyc['venueCategory'].value_counts()
Out[29]:
Bar 15978
Home (private) 15382
Office 12740
Subway 9348
Gym / Fitness Center 9171
...
Pet Service 3
Motorcycle Shop 2
Castle 2
Photography Lab 2
Music School 1
Name: venueCategory, Length: 251, dtype: int64
如果观察两个变量的同时出现的情况, 可以使用
In [13]: pd.crosstab(purchase.apples, purchase.oranges)
Out[13]:
oranges 0 2 3 7
apples
0 0 0 0 1
1 0 1 0 0
2 0 0 1 0
3 1 0 0 0
如何选择数据
选择具体的位置
使用
In [42]: nyc.iloc[0, 1]
Out[42]: '49bbd6c0f964a520f4531fe3'
这里同样可以指定范围:
In [49]: nyc.iloc[0:3, 0:4]
Out[49]:
userId venueId venueCategoryId venueCategory
0 470 49bbd6c0f964a520f4531fe3 4bf58dd8d48988d127951735 Arts & Crafts Store
1 979 4a43c0aef964a520c6a61fe3 4bf58dd8d48988d1df941735 Bridge
2 69 4c5cc7b485a1e21e00d35711 4bf58dd8d48988d103941735 Home (private)
按列选择
In [43]: nyc.columns
Out[43]: Index(['userId', 'venueId', 'venueCategoryId', 'venueCategory', 'latitude',
'longitude', 'timezoneOffset', 'utcTimestamp'],
dtype='object')
我们可以通过列的名字来选择具体第几列:
venue_col = nyc['venueCategory']
这样得到结果的数据类型是
venue_col = nyc[['venueCategory']]
当然可以通过列表选择多列:
multi_col = nyc[['userId', 'venueCategoryId', 'venueCategory']]
按行选择
In [9]: purchase.loc['June']
Out[9]:
apples 3
oranges 0
Name: June, dtype: int64
In [3]: purchase.iloc[0]
Out[3]:
apples 3
oranges 0
Name: June, dtype: int64
这里也可以指定范围, 比如选择前两行:
In [4]: purchase.iloc[0:2]
Out[4]:
apples oranges
June 3 0
Robert 2 3
如何对数据进行筛选
比如在 checkin 数据中得到所有位于酒吧的数据. 有如下几种方式:
一是使用
In [51]: nyc.query("venueCategory == 'Bar'")
Out[51]:
userId venueId venueCategoryId ... longitude timezoneOffset utcTimestamp
149 272 4f318c84e5e8657f88d830ac 4bf58dd8d48988d112941735 ... -73.792527 -240 Tue Apr 03 19:42:24 +0000 2012
207 445 4bae5fb6f964a520b4a93be3 4bf58dd8d48988d112941735 ... -73.983701 -240 Tue Apr 03 20:21:06 +0000 2012
208 516 4f297e04a17c6fd5208ea108 4bf58dd8d48988d116941735 ... -73.989551 -240 Tue Apr 03 20:21:12 +0000 2012
233 798 3fd66200f964a520e1f01ee3 4bf58dd8d48988d11d941735 ... -73.926031 -240 Tue Apr 03 20:39:38 +0000 2012
339 84 4a859ca3f964a520a5fe1fe3 4bf58dd8d48988d11b941735 ... -73.973054 -240 Tue Apr 03 21:35:09 +0000 2012
... ... ... ... ... ... ... ...
227405 126 4ad04456f964a520c7d720e3 4bf58dd8d48988d1d8941735 ... -73.990166 -300 Sat Feb 16 02:11:14 +0000 2013
227414 783 4bd8df990115c9b618ba7580 4bf58dd8d48988d1d8941735 ... -73.993319 -300 Sat Feb 16 02:17:49 +0000 2013
227416 751 41005500f964a5204e0b1fe3 4bf58dd8d48988d116941735 ... -73.988171 -300 Sat Feb 16 02:19:07 +0000 2013
227426 671 4514efe0f964a520e7391fe3 4bf58dd8d48988d11d941735 ... -74.029309 -300 Sat Feb 16 02:34:31 +0000 2013
227427 942 4a1e0ca0f964a520bf7b1fe3 4bf58dd8d48988d116941735 ... -73.957422 -300 Sat Feb 16 02:35:36 +0000 2013
[15978 rows x 8 columns]
第二种方法是类似于
In [52]: nyc[nyc['venueCategory'] == "Bar"]
Out[52]:
userId venueId venueCategoryId ... longitude timezoneOffset utcTimestamp
149 272 4f318c84e5e8657f88d830ac 4bf58dd8d48988d112941735 ... -73.792527 -240 Tue Apr 03 19:42:24 +0000 2012
207 445 4bae5fb6f964a520b4a93be3 4bf58dd8d48988d112941735 ... -73.983701 -240 Tue Apr 03 20:21:06 +0000 2012
208 516 4f297e04a17c6fd5208ea108 4bf58dd8d48988d116941735 ... -73.989551 -240 Tue Apr 03 20:21:12 +0000 2012
233 798 3fd66200f964a520e1f01ee3 4bf58dd8d48988d11d941735 ... -73.926031 -240 Tue Apr 03 20:39:38 +0000 2012
339 84 4a859ca3f964a520a5fe1fe3 4bf58dd8d48988d11b941735 ... -73.973054 -240 Tue Apr 03 21:35:09 +0000 2012
... ... ... ... ... ... ... ...
227405 126 4ad04456f964a520c7d720e3 4bf58dd8d48988d1d8941735 ... -73.990166 -300 Sat Feb 16 02:11:14 +0000 2013
227414 783 4bd8df990115c9b618ba7580 4bf58dd8d48988d1d8941735 ... -73.993319 -300 Sat Feb 16 02:17:49 +0000 2013
227416 751 41005500f964a5204e0b1fe3 4bf58dd8d48988d116941735 ... -73.988171 -300 Sat Feb 16 02:19:07 +0000 2013
227426 671 4514efe0f964a520e7391fe3 4bf58dd8d48988d11d941735 ... -74.029309 -300 Sat Feb 16 02:34:31 +0000 2013
227427 942 4a1e0ca0f964a520bf7b1fe3 4bf58dd8d48988d116941735 ... -73.957422 -300 Sat Feb 16 02:35:36 +0000 2013
[15978 rows x 8 columns]
可以使用的扩展有:
-
和
表示逻辑与和或
-
函数
- 算数运算
- 字符串的正则表达式匹配
比如我们可以通过如下两种等价形式选择在酒吧或者在家的数据, 注意第一条中的圆括号是必须的, 因为
nyc[nyc[("venueCategory"] == "Bar") | (nyc["venueCategory"] == "Home (private)")]
nyc[nyc["venueCategory"].isin(["Bar", "Home (private)"])
算数运算也比较符合直观:
In [12]: purchase[purchase['apples'] + purchase['oranges'] == 3]
Out[12]:
apples oranges
June 3 0
David 1 2
而正则表达式匹配就稍微麻烦一点点, 需要用到
nyc[nyc["venueCategory"].str.contains("^w{3,4}<img src="https://www.zhihu.com/equation?tex=" alt="")]
In [67]: nyc[nyc["venueCategory"].str.contains("^w{3,4}" eeimg="1">")]["venueCategory"].value_counts()
Out[67]:
Bar 15978
Park 4804
Road 3207
Bank 2233
Café 1585
Mall 1187
City 832
Taxi 185
Pool 184
Food 172
Zoo 104
Fair 24
Name: venueCategory, dtype: int64
三是通过
In [69]: nyc[nyc["venueCategory"].map(lambda x: x == "Bar" or x == "Zoo")]
Out[69]:
userId venueId venueCategoryId ... longitude timezoneOffset utcTimestamp
149 272 4f318c84e5e8657f88d830ac 4bf58dd8d48988d112941735 ... -73.792527 -240 Tue Apr 03 19:42:24 +0000 2012
207 445 4bae5fb6f964a520b4a93be3 4bf58dd8d48988d112941735 ... -73.983701 -240 Tue Apr 03 20:21:06 +0000 2012
208 516 4f297e04a17c6fd5208ea108 4bf58dd8d48988d116941735 ... -73.989551 -240 Tue Apr 03 20:21:12 +0000 2012
233 798 3fd66200f964a520e1f01ee3 4bf58dd8d48988d11d941735 ... -73.926031 -240 Tue Apr 03 20:39:38 +0000 2012
339 84 4a859ca3f964a520a5fe1fe3 4bf58dd8d48988d11b941735 ... -73.973054 -240 Tue Apr 03 21:35:09 +0000 2012
... ... ... ... ... ... ... ...
227405 126 4ad04456f964a520c7d720e3 4bf58dd8d48988d1d8941735 ... -73.990166 -300 Sat Feb 16 02:11:14 +0000 2013
227414 783 4bd8df990115c9b618ba7580 4bf58dd8d48988d1d8941735 ... -73.993319 -300 Sat Feb 16 02:17:49 +0000 2013
227416 751 41005500f964a5204e0b1fe3 4bf58dd8d48988d116941735 ... -73.988171 -300 Sat Feb 16 02:19:07 +0000 2013
227426 671 4514efe0f964a520e7391fe3 4bf58dd8d48988d11d941735 ... -74.029309 -300 Sat Feb 16 02:34:31 +0000 2013
227427 942 4a1e0ca0f964a520bf7b1fe3 4bf58dd8d48988d116941735 ... -73.957422 -300 Sat Feb 16 02:35:36 +0000 2013
或者:
nyc[nyc.apply(lambda x: x['venueCategory'] == "Bar" or x['venueCategory'] == "Zoo", axis=1)]
这样我们如果要就有了如下几种等价写法:
nyc[nyc['venueCategory'].isin(["Bar", "Zoo"])] # %time: 6.5ms
nyc.query("venueCategory == 'Bar' | venueCategory == 'Zoo'") # %time: 11.1ms
nyc[(nyc["venueCategory"] == "Bar") | (nyc["venueCategory"] == "Zoo")] # %time: 19.1ms
nyc[nyc["venueCategory"].map(lambda x: x == "Bar" or x == "Zoo")] # %time: 26.8ms
nyc[nyc["venueCategory"].str.contains("^(Bar|Zoo)")] # %time: 85.5ms
nyc[nyc.apply(lambda x: x['venueCategory'] == "Bar" or x['venueCategory'] == "Zoo", axis=1)] # %time: 2.9s
最快的当然是
如何进行变换
三个函数:
-
: 作用于
.
-
: 作用于
和
, 有一个参数
, 表示默认情况下对每一行进行操作, 如果这个值设置为1, 那么表示对列进行操作.
-
: 作用于
, 表示对每一个元素进行操作.
如何进行数据整理
离散化
nyc["scatter_latitude"] = pd.cut(nyc.latitude, 14)
此时的
userId venueId ... utcTimestamp scatter_latitude
0 470 49bbd6c0f964a520f4531fe3 ... Tue Apr 03 18:00:09 +0000 2012 (40.707, 40.738]
1 979 4a43c0aef964a520c6a61fe3 ... Tue Apr 03 18:00:25 +0000 2012 (40.582, 40.613]
2 69 4c5cc7b485a1e21e00d35711 ... Tue Apr 03 18:02:24 +0000 2012 (40.707, 40.738]
3 395 4bc7086715a7ef3bef9878da ... Tue Apr 03 18:02:41 +0000 2012 (40.738, 40.77]
4 87 4cf2c5321d18a143951b5cec ... Tue Apr 03 18:03:00 +0000 2012 (40.738, 40.77]
... ... ... ... ... ...
227423 688 3fd66200f964a52000e71ee3 ... Sat Feb 16 02:29:11 +0000 2013 (40.707, 40.738]
227424 560 4bca32ff0687ef3be789dbcc ... Sat Feb 16 02:31:35 +0000 2013 (40.738, 40.77]
227425 945 50a77716e4b0b5a9492f6f56 ... Sat Feb 16 02:33:16 +0000 2013 (40.832, 40.863]
227426 671 4514efe0f964a520e7391fe3 ... Sat Feb 16 02:34:31 +0000 2013 (40.707, 40.738]
227427 942 4a1e0ca0f964a520bf7b1fe3 ... Sat Feb 16 02:35:36 +0000 2013 (40.707, 40.738]
排序
按照某一列排序, 比如按照
In [123]: nyc.sort_values(by="userId")
Out[123]:
userId venueId ... utcTimestamp scatter_latitude
145520 1 43a52546f964a520532c1fe3 ... Fri Aug 10 16:40:07 +0000 2012 (40.645, 40.676]
62929 1 412d2800f964a520df0c1fe3 ... Sat May 12 18:40:38 +0000 2012 (40.77, 40.801]
22670 1 4bc1e64b4cdfc9b6892b9521 ... Wed Apr 18 16:07:03 +0000 2012 (40.707, 40.738]
28928 1 4e691e82b9930387355c629d ... Sat Apr 21 16:28:11 +0000 2012 (40.707, 40.738]
69909 1 3fd66200f964a52075ea1ee3 ... Tue May 15 23:18:43 +0000 2012 (40.77, 40.801]
... ... ... ... ... ...
210432 1083 4a53d9a7f964a520c7b21fe3 ... Fri Jan 11 15:00:35 +0000 2013 (40.738, 40.77]
26824 1083 4a53d9a7f964a520c7b21fe3 ... Fri Apr 20 14:32:35 +0000 2012 (40.738, 40.77]
156844 1083 3fd66200f964a52020e71ee3 ... Sat Sep 15 01:05:32 +0000 2012 (40.738, 40.77]
55053 1083 4a53d9a7f964a520c7b21fe3 ... Tue May 08 14:01:18 +0000 2012 (40.738, 40.77]
10960 1083 3fd66200f964a5206ae41ee3 ... Thu Apr 12 02:15:17 +0000 2012 (40.707, 40.738]
One-hot encoding
In [143]: nyc = pd.get_dummies(nyc, columns=["scatter_latitude"])
In [144]: nyc.iloc[0]
Out[144]:
userId 470
venueId 49bbd6c0f964a520f4531fe3
venueCategoryId 4bf58dd8d48988d127951735
venueCategory Arts & Crafts Store
latitude 40.7198
longitude -74.0026
timezoneOffset -240
utcTimestamp Tue Apr 03 18:00:09 +0000 2012
scatter_latitude_(40.55, 40.582] 0
scatter_latitude_(40.582, 40.613] 0
scatter_latitude_(40.613, 40.645] 0
scatter_latitude_(40.645, 40.676] 0
scatter_latitude_(40.676, 40.707] 0
scatter_latitude_(40.707, 40.738] 1
scatter_latitude_(40.738, 40.77] 0
scatter_latitude_(40.77, 40.801] 0
scatter_latitude_(40.801, 40.832] 0
scatter_latitude_(40.832, 40.863] 0
scatter_latitude_(40.863, 40.895] 0
scatter_latitude_(40.895, 40.926] 0
scatter_latitude_(40.926, 40.957] 0
scatter_latitude_(40.957, 40.988] 0
Name: 0, dtype: object