3.2 数据帧

原文：Pandas dataframes

译者：飞龙

协议：CC BY-NC-SA 4.0

我们将使用真实的 kaggle 比赛数据集来探索 Pandas 数据帧。获取rent.csv.zip文件并解压缩。

import pandas as pd
df = pd.read_csv("data/rent.csv", parse_dates=['created'])
df.head(2)

df.head(2).T

观察数据

df.info()

'''
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 49352 entries, 0 to 49351
Data columns (total 15 columns):
bathrooms          49352 non-null float64
bedrooms           49352 non-null int64
building_id        49352 non-null object
created            49352 non-null datetime64[ns]
description        47906 non-null object
display_address    49217 non-null object
features           49352 non-null object
interest_level     49352 non-null object
latitude           49352 non-null float64
listing_id         49352 non-null int64
longitude          49352 non-null float64
manager_id         49352 non-null object
photos             49352 non-null object
price              49352 non-null int64
street_address     49342 non-null object
dtypes: datetime64[ns](1), float64(3), int64(3), object(8)
memory usage: 5.6+ MB
'''

df.describe()

df.price.sort_values(ascending=False).head(10)

'''
19558    4490000
9590     1150000
30689    1070000
29665    1070000
10581     135000
25538     111111
45674     100000
29082      90000
7336       85000
47995      80000
Name: price, dtype: int64
'''

获取列

df.price.head(5) # works only on right hand side of assignment or in expression

'''
0    3000
1    5465
2    2850
3    3275
4    3350
Name: price, dtype: int64
'''

df['price'].head(5)

'''
0    3000
1    5465
2    2850
3    3275
4    3350
Name: price, dtype: int64
'''

列的计算

可以获取值并取平均值：

prices = df['price']
avg_rent = prices.mean()
print(f"Average rent is ${avg_rent:.0f}")

# Average rent is $3830

df.latitude.min(), df.latitude.max()

# (0.0, 44.8835)

bybaths = df.groupby(['bathrooms']).mean()
bybaths

bybaths = bybaths.reset_index() # overcome quirk in Pandas
bybaths.head(3)

bybaths[['bathrooms','price']] # print just num baths, avg price

列 VS 子集

# get column
df['bedrooms'].head(5)

'''
0    3
1    2
2    1
3    1
4    4
Name: bedrooms, dtype: int64
'''

df[['bedrooms','bathrooms']].head(3)

获取行

# get index 3 row
df.iloc[3] # same as df.iloc[3,:]

'''
bathrooms                                                          1
bedrooms                                                           1
building_id                         28d9ad350afeaab8027513a3e52ac8d5
created                                          2016-04-18 02:22:02
description        Building Amenities - Garage - Garden - fitness...
display_address                                     East 49th Street
features                               ['Hardwood Floors', 'No Fee']
interest_level                                                   low
latitude                                                     40.7539
listing_id                                                   6888711
longitude                                                   -73.9677
manager_id                          1067e078446a7897d2da493d2f741316
photos             ['https://photos.renthop.com/2/6888711_6e660ce...
price                                                           3275
street_address                                  333 East 49th Street
Name: 3, dtype: object
'''

df.iloc[0:2] # first two rows

df.iloc[0:2][['created','features']] # first two rows, show 2 columns

索引，通过索引的键获取行

df = df.set_index('listing_id')
df.head(3)

df.loc[7150865]

'''
bathrooms                                                          1
bedrooms                                                           2
building_id                         c5c8a357cba207596b04d1afd1e4f130
created                                          2016-06-12 12:19:27
description                                                         
display_address                                      Columbus Avenue
features           ['Doorman', 'Elevator', 'Fitness Center', 'Cat...
interest_level                                                   low
latitude                                                     40.7947
longitude                                                   -73.9667
manager_id                          7533621a882f71e25173b27e3139d83d
photos             ['https://photos.renthop.com/2/7150865_be3306c...
price                                                           5465
street_address                                   808 Columbus Avenue
Name: 7150865, dtype: object
'''

df = df.reset_index()
df.head(3)

Allows non-unique indexes

df_beds = df.set_index('bedrooms')
df_beds.loc[3].head(3)

检查缺失值

df.isnull().head(5)

df.isnull().any()

'''
listing_id         False
bathrooms          False
bedrooms           False
building_id        False
created            False
description         True
display_address     True
features           False
interest_level     False
latitude           False
longitude          False
manager_id         False
photos             False
price              False
street_address      True
dtype: bool
'''

查找数据帧中缺少描述的所有行：

df.description.isnull().head(5)

'''
0    False
1    False
2    False
3    False
4    False
Name: description, dtype: bool
'''

df[df.description.isnull()].head(5)

另一个查询，获得价格高于 1M 美金的所有apt行

(df.price>1000000).head(5)

'''
0    False
1    False
2    False
3    False
4    False
Name: price, dtype: bool
'''

df[df.price>1000000]

df[(df.price>1000) & (df.price<10_000)].head(3) # parentheses are required in query!!!!

直方图变量

df.bathrooms.value_counts()

'''
1.0     39422
2.0      7660
3.0       745
1.5       645
0.0       313
2.5       277
4.0       159
3.5        70
4.5        29
5.0        20
5.5         5
6.0         4
6.5         1
10.0        1
7.0         1
Name: bathrooms, dtype: int64
'''

import matplotlib.pyplot as plt

plt.xlabel('Num Bathrooms')
plt.ylabel('Num Apts')
plt.hist(df.bathrooms, bins=20)
plt.show()

# <Figure size 640x480 with 1 Axes>

plt.xlabel('Num Bedrooms')
plt.ylabel('Num Apts')
plt.hist(df.bedrooms, bins=20)
plt.show()

plt.xlabel('Price')
plt.ylabel('Num Apts at that price')
plt.hist(df.price, bins=45) # not useful since loooong right tail
plt.show()

import numpy as np
df_log = df.copy()
df_log["price"] = np.log( df["price"] )
#OR:
#df_log["price"] = df["price"].apply(np.log)
df_log.price.head(3)

'''
0    8.006368
1    8.606119
2    7.955074
Name: price, dtype: float64
'''

plt.xlabel('Price')
plt.ylabel('Num Apts at that price')
plt.hist(df_log.price, bins=45) # not useful since loooong right tail
plt.show()

变量间的变化

bybaths.plot.line('bathrooms','price', style='-o')   
plt.show()

# OR, can do directly
plt.plot(bybaths.bathrooms, bybaths.price, marker='o') # note slightly different arguments
plt.show()

测试你的知识

从df获取浴室一列

迭代列的列表并将其打印出来

从df获取第 6 行

将df索引设置为卧室，然后使用索引获得所有带 3 间卧室的公寓

获取每月价格> 100_000的所有行

从df中删除列building_id

获取价格介于 1000 和 2000 之间的所有行。

将np.log()函数应用于价格并存储在名为log_price的新列中

将纬度和经度列放入到自己的数据帧中

答案

df.bathrooms

for colname in df:
    print(colname)

df.loc[5]

df = df.set_index('bedrooms')
df.loc[3]

df[df.price > 100_000]

#del df.building_id
df = df.drop('building_id', axis=1)

df[ (df.price>1000) & (df.price<2000) ]

df['log_price'] = np.log( df.price )

df[ ['longitude','latitude'] ]

清理

价格

df_clean = df[(df.price>1_000) & (df.price<10_000)]

plt.xlabel('Price')
plt.ylabel('Num Apts at that price')
plt.hist(df_clean.price, bins=45)
plt.show()

plt.scatter(df_clean.bedrooms, df_clean.price, alpha=0.1)
plt.xlabel("Bedrooms", fontsize=12) 
plt.ylabel("Rent price", fontsize=12)
plt.show()

位置

df_missing = df_clean[(df_clean.longitude==0) |
                      (df_clean.latitude==0)]
len(df_missing)

# 11

# only 11 filter out
df_clean = df_clean[(df_clean.longitude!=0) |
                    (df_clean.latitude!=0)]

Use & to get "and" whereas | is "or".

Using GPS checker gives a rough outline for New York City of latitude, longitude 40.55, -74.1 on the lower left and 40.94, -73.67 on the upper right. Let's filter others out.

df_clean = df_clean[(df_clean['latitude']>40.55) &
                    (df_clean['latitude']<40.94) &
                    (df_clean['longitude']>-74.1) &
                    (df_clean['longitude']<-73.67)]
print(len(df_clean), len(df))

# 48300 49352

热力图

from matplotlib import colors

fig = plt.figure(figsize=(8,8))
ax = fig.add_subplot(111)
plt.scatter(df_clean.longitude,
            df_clean.latitude,
            alpha=0.8,
            s=1.5, # pixel size
            vmin=1000, vmax=4000, # limit price range so 4000 is red
            c=df_clean['price'],
            cmap='rainbow', # color map
            marker='.')
plt.colorbar()
plt.show()

训练模型

仅仅获取数值字段

df_train = df_clean[['bathrooms', 'bedrooms', 'longitude', 'latitude', 'price']]
df_train.head(5)

X_train = df_train[['bedrooms','bathrooms','latitude','longitude']]
y_train = df_train['price']

from sklearn.ensemble import RandomForestRegressor
rf = RandomForestRegressor(n_estimators=100, n_jobs=-1, oob_score=True)

rf.fit(X_train, y_train)
print(f"OOB R^2 score is {rf.oob_score_:.3f} (range is -infinity to 1.0; 1.0 is perfect)")

# OOB R^2 score is 0.867 (range is -infinity to 1.0; 1.0 is perfect)

模型告诉我们特征的什么信息？

# pip install rfpimp
from rfpimp import *
I = oob_importances(rf, X_train, y_train)
I.plot(kind='barh', legend=False)
plt.show()

合成特征

df['one'] = 1
df.head(3)

添加适当长度的随机列

df2 = df.copy()
df2['random'] = np.random.random(size=len(df))
df2.head(2).T

df2['i'] = [i for i in range(len(df))]
df2.head(2).T

数据集具有features属性（类型为字符串），其中包含公寓的特性列表。

df.features.head(5)

'''
0                                                   []
1    ['Doorman', 'Elevator', 'Fitness Center', 'Cat...
2    ['Laundry In Building', 'Dishwasher', 'Hardwoo...
3                        ['Hardwood Floors', 'No Fee']
4                                          ['Pre-War']
Name: features, dtype: object
'''

让我们创建三个新的布尔列，指示公寓是否有门卫，停车或洗衣房。 首先制作数据帧的副本，因为我们将对其进行修改（否则我们将收到错误“正在尝试在DataFrame的切片副本上设置值”）：

df_aug = df[['bedrooms','bathrooms','latitude','longitude',
             'features','price']].copy()

然后我们规范化特性列，以便缺少的特性值变为空白，并且我们将所有字符串小写。

# rewrite features column
df_aug['features'] = df_aug['features'].fillna('') # fill missing w/blanks
df_aug['features'] = df_aug['features'].str.lower() # normalize to lower case

通过检查features列中是否存在字符串来创建三个布尔列。

df_aug['doorman'] = df_aug['features'].str.contains("doorman")
df_aug['parking'] = df_aug['features'].str.contains("parking|garage")
df_aug['laundry'] = df_aug['features'].str.contains("laundry")
del df_aug['features'] # don't need this anymore
df_aug.head(3)

删除del以外的列的另一种方法是使用drop()函数：

df2 = df.drop('description',axis=1) # drop doesn't affect df in place, returns new one
df2.head(2).T # kill this column, return new df without that column

让我们对数值特征做一些事情。

df_aug["beds_to_baths"] = df_aug["bedrooms"]/(df_aug["bathrooms"]+1)
df_aug.head(3)

超出我们的范围，但让我们重新训练模型，看看它是否提高了 OOB 得分。

df_clean = df_aug[(df.price>1_000) & (df.price<10_000)]
df_train = df_clean[['bathrooms', 'bedrooms', 'longitude', 'latitude',
                   'doorman', 'parking', 'laundry', 'beds_to_baths', 'price']]
X_train = df_train[['bedrooms','bathrooms','latitude','longitude',
                    'doorman', 'parking', 'laundry', 'beds_to_baths']]
y_train = df_train['price']

rf = RandomForestRegressor(n_estimators=100, n_jobs=-1, oob_score=True)
rf.fit(X_train, y_train)
print(f"OOB R^2 score is {rf.oob_score_:.3f} (range is -infinity to 1.0; 1.0 is perfect)")

# OOB R^2 score is 0.870 (range is -infinity to 1.0; 1.0 is perfect)

I = pd.DataFrame(data={'Feature':X_train.columns, 'Importance':rf.feature_importances_})

I.sort_values('Importance',ascending=False)

这个分数稍微好一些，但不是很多。

将类别转换为数值数据

这不是通用的，但适用于小型（有序）类别集：

df['interest_level'] = df['interest_level'].map({'low':1,'medium':2,'high':3})

df[['interest_level']].head(5)

转换类型

df['some_boolean'] = True

df['some_boolean'] = df['some_boolean'].astype('int8')
df.some_boolean.head()

'''
bedrooms
3    1
2    1
1    1
1    1
4    1
Name: some_boolean, dtype: int8
'''

转换日期

df['dayofweek'] = df['created'].dt.dayofweek  # add dow column
df['day'] = df['created'].dt.day
df['month'] = df['created'].dt.month
df.head(1).T

Feather 格式

df.to_feather("/tmp/rent.feather")

% time df = pd.read_feather("/tmp/rent.feather")

'''
CPU times: user 70.6 ms, sys: 43.9 ms, total: 114 ms
Wall time: 113 ms
'''

与加载 CSV 相比，似乎慢 5 倍：

% time df = pd.read_csv("data/rent.csv")

'''
CPU times: user 597 ms, sys: 73.5 ms, total: 671 ms
Wall time: 670 ms
'''

测试你的知识

过滤出超过 5 个浴室的所有行，并重新放入相同的数据帧

显示浴室与价格的散点图

显示热力图，其颜色是卧室数量的函数。

插入一个名为avg_price的列，它是所有价格的平均值

使用字典，将所有原始interest_level值转换为10, 20, 30，用于低，中和高类别

将manager_id列转换为类别而不是字符串类型

从created字段创建一个名为day_of_year的新列，带有每年的日期 1-365