清洗数据，高效率7步走！
大数据DT
共 10933字，需浏览 22分钟
· 2020-10-08
导读：本文使用泰坦尼克数据集，完整介绍数据清洗 7 步的具体操作过程。
数据清洗 (data cleaning) 是机器学习和深度学习进入算法步前的一项重要任务，我平时比较习惯使用的 7 个步骤，总结如下：
Step1 : read csv
Step2 : preview data
Step3: check null value for every column
Step4: complete null value
Step5: feature engineering
Step 5.1: delete some features
Step 5.2: create new feature
Step6: encode for categories columns
Step 6.1: Sklearn LabelEncode
Step 6.2: Pandas get_dummies
Step 7: check for data cleaning
01 读入数据
这不废话吗，第一步就是读入数据。
data_raw = pd.read_csv('../input/titanicdataset-traincsv/train.csv')
data_raw结果：
PassengerId Survived Pclass Name Sex Age SibSp Parch Ticket Fare Cabin Embarked
0 1 0 3 Braund, Mr. Owen Harris male 22.0 1 0 A/5 21171 7.2500 NaN S
1 2 1 1 Cumings, Mrs. John Bradley (Florence Briggs Th... female 38.0 1 0 PC 17599 71.2833 C85 C
2 3 1 3 Heikkinen, Miss. Laina female 26.0 0 0 STON/O2. 3101282 7.9250 NaN S
3 4 1 1 Futrelle, Mrs. Jacques Heath (Lily May Peel) female 35.0 1 0 113803 53.1000 C123 S
4 5 0 3 Allen, Mr. William Henry male 35.0 0 0 373450 8.0500 NaN S
... ... ... ... ... ... ... ... ... ... ... ... ...
886 887 0 2 Montvila, Rev. Juozas male 27.0 0 0 211536 13.0000 NaN S
887 888 1 1 Graham, Miss. Margaret Edith female 19.0 0 0 112053 30.0000 B42 S
888 889 0 3 Johnston, Miss. Catherine Helen "Carrie" female NaN 1 2 W./C. 6607 23.4500 NaN S
889 890 1 1 Behr, Mr. Karl Howell male 26.0 0 0 111369 30.0000 C148 C
890 891 0 3 Dooley, Mr. Patrick male 32.0 0 0 370376 7.7500 NaN Q
891 rows × 12 columns02 数据预览
data_raw.info()
data_raw.describe(include='all')结果：
<class 'pandas.core.frame.DataFrame'>
RangeIndex:891 entries, 0 to 890
Data columns (total 12 columns):
PassengerId 891 non-null int64
Survived 891 non-null int64
Pclass 891 non-null int64
Name 891 non-null object
Sex 891 non-null object
Age 714 non-null float64
SibSp 891 non-null int64
Parch 891 non-null int64
Ticket 891 non-null object
Fare 891 non-null float64
Cabin 204 non-null object
Embarked 889 non-null object
dtypes: float64(2), int64(5), object(5)
memory usage: 83.7+ KB
PassengerId Survived Pclass Name Sex Age SibSp Parch Ticket Fare Cabin Embarked
count 891.000000891.000000891.000000891891714.000000891.000000891.000000891891.000000204889
unique NaN NaN NaN 8912 NaN NaN NaN 681 NaN 1473
top NaN NaN NaN Hakkarainen, Mr. Pekka Pietari male NaN NaN NaN 1601 NaN G6 S
freq NaN NaN NaN 1577 NaN NaN NaN 7 NaN 4644
mean 446.0000000.3838382.308642 NaN NaN 29.6991180.5230080.381594 NaN 32.204208 NaN NaN
std 257.3538420.4865920.836071 NaN NaN 14.5264971.1027430.806057 NaN 49.693429 NaN NaN
min 1.0000000.0000001.000000 NaN NaN 0.4200000.0000000.000000 NaN 0.000000 NaN NaN
25% 223.5000000.0000002.000000 NaN NaN 20.1250000.0000000.000000 NaN 7.910400 NaN NaN
50% 446.0000000.0000003.000000 NaN NaN 28.0000000.0000000.000000 NaN 14.454200 NaN NaN
75% 668.5000001.0000003.000000 NaN NaN 38.0000001.0000000.000000 NaN 31.000000 NaN NaN
max 891.0000001.0000003.000000 NaN NaN 80.0000008.0000006.000000 NaN 512.329200 NaN N03 检查null值
data1 = data_raw.copy(deep=True)
data1.isnull().sum()结果：
PassengerId 0
Survived 0
Pclass 0
Name 0
Sex 0
Age 177
SibSp 0
Parch 0
Ticket 0
Fare 0
Cabin 687
Embarked 2
dtype: int64Age 列 177 个空值，Cabin 687 个空值，一共才 891 行，估计没啥价值了！Embarked 2 个。
04 补全空值
data1['Age'].fillna(data1['Age'].median(), inplace = True)
data1['Embarked'].fillna(data1['Embarked'].mode()[0], inplace = True)
data1.isnull().sum()补全操作check：
PassengerId 0
Survived 0
Pclass 0
Name 0
Sex 0
Age 0
SibSp 0
Parch 0
Ticket 0
Fare 0
Cabin 687
Embarked 0
dtype: int6405 特征工程
1. 干掉 3 列
drop_column = ['PassengerId','Cabin', 'Ticket']
data1.drop(drop_column, axis=1, inplace = True)2. 增加 3 列
增加一列 FamilySize
data1['FamilySize'] = data1 ['SibSp'] + data1['Parch'] + 1
data1打印结果：
Survived Pclass Name Sex Age SibSp Parch Fare Embarked FamilySize
003 Braund, Mr. Owen Harris male 22.0107.2500 S 2
111 Cumings, Mrs. John Bradley (Florence Briggs Th... female 38.01071.2833 C 2
213 Heikkinen, Miss. Laina female 26.0007.9250 S 1
311 Futrelle, Mrs. Jacques Heath (Lily May Peel) female 35.01053.1000 S 2
403 Allen, Mr. William Henry male 35.0008.0500 S 1
... ... ... ... ... ... ... ... ... ... ...
88602 Montvila, Rev. Juozas male 27.00013.0000 S 1
88711 Graham, Miss. Margaret Edith female 19.00030.0000 S 1
88803 Johnston, Miss. Catherine Helen "Carrie" female 28.01223.4500 S 4
88911 Behr, Mr. Karl Howell male 26.00030.0000 C 1
89003 Dooley, Mr. Patrick male 32.0007.7500 Q 1
891 rows × 10 columns再创建一列：
data1['IsAlone'] = np.where(data1['FamilySize'] > 1,0,1)再创建一列：
data1['Title'] = data1['Name'].str.split(", ", expand=True)[1].str.split(".", expand=True)[0]
data1结果：
Survived Pclass Name Sex Age SibSp Parch Fare Embarked FamilySize IsAlone Title
003 Braund, Mr. Owen Harris male 22.0107.2500 S 20 Mr
111 Cumings, Mrs. John Bradley (Florence Briggs Th... female 38.01071.2833 C 20 Mrs
213 Heikkinen, Miss. Laina female 26.0007.9250 S 11 Miss
311 Futrelle, Mrs. Jacques Heath (Lily May Peel) female 35.01053.1000 S 20 Mrs
403 Allen, Mr. William Henry male 35.0008.0500 S 11 Mr
... ... ... ... ... ... ... ... ... ... ... ... ...
88602 Montvila, Rev. Juozas male 27.00013.0000 S 11 Rev
88711 Graham, Miss. Margaret Edith female 19.00030.0000 S 11 Miss
88803 Johnston, Miss. Catherine Helen "Carrie" female 28.01223.4500 S 40 Miss
88911 Behr, Mr. Karl Howell male 26.00030.0000 C 11 Mr
89003 Dooley, Mr. Patrick male 32.0007.7500 Q 11 Mr
891 rows × 12 columns3. 分箱走起
data1['FareCut'] = pd.qcut(data1['Fare'], 4)
data1['AgeCut'] = pd.cut(data1['Age'].astype(int), 6)
data1结果：
Survived Pclass Name Sex Age SibSp Parch Fare Embarked FamilySize IsAlone Title FareCut AgeCut
0 0 3 Braund, Mr. Owen Harris male 22.0 1 0 7.2500 S 2 0 Mr (-0.001, 7.91] (13.333, 26.667]
1 1 1 Cumings, Mrs. John Bradley (Florence Briggs Th... female 38.0 1 0 71.2833 C 2 0 Mrs (31.0, 512.329] (26.667, 40.0]
2 1 3 Heikkinen, Miss. Laina female 26.0 0 0 7.9250 S 1 1 Miss (7.91, 14.454] (13.333, 26.667]
3 1 1 Futrelle, Mrs. Jacques Heath (Lily May Peel) female 35.0 1 0 53.1000 S 2 0 Mrs (31.0, 512.329] (26.667, 40.0]
4 0 3 Allen, Mr. William Henry male 35.0 0 0 8.0500 S 1 1 Mr (7.91, 14.454] (26.667, 40.0]
... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
886 0 2 Montvila, Rev. Juozas male 27.0 0 0 13.0000 S 1 1 Rev (7.91, 14.454] (26.667, 40.0]
887 1 1 Graham, Miss. Margaret Edith female 19.0 0 0 30.0000 S 1 1 Miss (14.454, 31.0] (13.333, 26.667]
888 0 3 Johnston, Miss. Catherine Helen "Carrie" female 28.0 1 2 23.4500 S 4 0 Miss (14.454, 31.0] (26.667, 40.0]
889 1 1 Behr, Mr. Karl Howell male 26.0 0 0 30.0000 C 1 1 Mr (14.454, 31.0] (13.333, 26.667]
890 0 3 Dooley, Mr. Patrick male 32.0 0 0 7.7500 Q 1 1 Mr (-0.001, 7.91] (26.667, 40.0]
891 rows × 14 columns06 编码
1. LabelEncoder 方法
使用 Sklearn 的 LabelEncoder
from sklearn.preprocessing import LabelEncoder
label = LabelEncoder()
data1['Sex_Code'] = label.fit_transform(data1['Sex'])
data1['Embarked_Code'] = label.fit_transform(data1['Embarked'])
data1['Title_Code'] = label.fit_transform(data1['Title'])
data1['AgeBin_Code'] = label.fit_transform(data1['AgeCut'])
data1['FareBin_Code'] = label.fit_transform(data1['FareCut'])
data1结果 data1 选取某些列，算法模型终于能认出它们了，多不容易！
2. get_dummies 方法
get_dummies 将长 DataFrame 变为宽 DataFrame:
pd.get_dummies(data1['Sex'])结果：
female male
0 0 1
1 1 0
2 1 0
3 1 0
4 0 1
... ... ...
886 0 1
887 1 0
888 1 0
889 0 1
890 0 1
891 rows × 2 columns而 LabelEncoder 编码后，仅仅是把 Female 编码为 0， male 编码为 1.
label.fit_transform(data1['Sex'])
0 1
1 0
2 0
3 0
4 1
..
886 1
887 0
888 0
889 1
890 1
Name: Sex_Code, Length: 891, dtype: int6407 再 check
# Step 7: data cleaning check
data1[data1_x_alg].info()
print('-'*50)
data1_dummy.info()结果：
<class 'pandas.core.frame.DataFrame'>
RangeIndex:891 entries, 0 to 890
Data columns (total 8 columns):
Sex_Code 891 non-null int64
Pclass 891 non-null int64
Embarked_Code 891 non-null int64
Title_Code 891 non-null int64
SibSp 891 non-null int64
Parch 891 non-null int64
Age 891 non-null float64
Fare 891 non-null float64
dtypes: float64(2), int64(6)
memory usage: 55.8 KB
--------------------------------------------------
<class 'pandas.core.frame.DataFrame'>
RangeIndex:891 entries, 0 to 890
Data columns (total 29 columns):
Pclass 891 non-null int64
SibSp 891 non-null int64
Parch 891 non-null int64
Age 891 non-null float64
Fare 891 non-null float64
FamilySize 891 non-null int64
IsAlone 891 non-null int64
Sex_female 891 non-null uint8
Sex_male 891 non-null uint8
Embarked_C 891 non-null uint8
Embarked_Q 891 non-null uint8
Embarked_S 891 non-null uint8
Title_Capt 891 non-null uint8
Title_Col 891 non-null uint8
Title_Don 891 non-null uint8
Title_Dr 891 non-null uint8
Title_Jonkheer 891 non-null uint8
Title_Lady 891 non-null uint8
Title_Major 891 non-null uint8
Title_Master 891 non-null uint8
Title_Miss 891 non-null uint8
Title_Mlle 891 non-null uint8
Title_Mme 891 non-null uint8
Title_Mr 891 non-null uint8
Title_Mrs 891 non-null uint8
Title_Ms 891 non-null uint8
Title_Rev 891 non-null uint8
Title_Sir 891 non-null uint8
Title_the Countess 891 non-null uint8
dtypes: float64(2), int64(5), uint8(22)
memory usage: 68.0 KB
Great !
Done~
评论
21.3K star！推荐一款可视化自动化测试/爬虫/数据采集神器！功能免费且强大！
【温馨提示】由于公众号更改了推送规则，不再按照时间顺序排列，如果不想错过测试开发技术精心准备的的干货文章，请将测试开发技术设为“星标☆”，看完文章在文尾处点亮“在看”！大家好，我是狂师！在大数据时代，信息的获取与分析变得尤为重要。对于开发者、数据分析师乃至非技术人员来说，能够高效地采集网络数据并进行
测试开发技术
4
小美播报|3月IPTV数据排行榜发布！
小美播报3月IPTV数据排行榜:《与凤行》登顶连续剧榜榜首拥有4.05亿家庭用户的中国IPTV平台已经成为国内主流视听平台，IPTV平台数据对视听产业各环节都具有重要意义。截至2024年4月，全国已有29个省级IPTV加入“看中国”，覆盖全国超2.25亿户家庭、辐射近7亿人。点击查看详情湖南广电与马
流媒体网
0
python读取一个文件里面几百个csv数据集然后按照列名合并一个数据集
点击上方“Python爬虫与数据挖掘”，进行关注回复“书籍”即可获赠Python从入门到进阶共10本电子书今日鸡汤但使龙城飞将在，不教胡马度阴山。大家好，我是Python进阶者。一、前言前几天在Python最强王者交流群【FiNε_】问了一个Python自动化办公，问题如下：python 读取一个文
Python爬虫与数据挖掘
3
AKShare-股票数据-恐惧贪婪指数
作者寄语本次更新股票数据-恐惧贪婪指数接口。主要修复该接口，目前该接口可以获取 A 股 恐惧贪婪指数 数据。欢迎加入专注于财经数据和量化投资的【数据科学实战】社区，加入方式参见文末！更新接口"index_fear_greed_funddb" # 恐惧贪婪指数恐惧贪婪指数接口: index
数据科学实战
10
越有力量，人生越走越向上
点击蓝色关注我们痛苦是对的，焦虑也是对的痛苦的本质来源于，你对现状的不满焦虑的本质来源于，你嫌成长速度太慢你可能会一个人走过一段艰难的夜路但是后来别人问你的时候你会说，那晚漫天星光、一路歌唱你不是刻意美化人生你只是提醒自己不要沉沦在逆境里一个人的定力最终决定了他能走多远跟聪明没太大关系聪明很多时候，
小Q聊产品
18
实战必备-数据清洗、数据存储、数据可视化-《AKShare-初阶-实战应用》
✔️课程介绍本课程主要给大家介绍 AKShare 的实战应用部分，主要包括对 AKShare 获取到的数据进行数据清洗，数据存储和数据可视化。课中会提供我们在进行数据处理中的最佳实践！课程目录✔️获取方式如下： 直接购买订阅： ¥39.8/年🔗扫码进入课程店铺：🔅懒得
数据科学实战
31
怎么样python把这个excel列数据中双引号去掉呀？
点击上方“Python爬虫与数据挖掘”，进行关注回复“书籍”即可获赠Python从入门到进阶共10本电子书今日鸡汤万籁此都寂，但余钟磬音。大家好，我是皮皮。一、前言前几天在Python最强王者交流群【6G】问了一个Pandas数据分析的问题，这里拿出来给大家分享下。数据截图如下：二、实现过程其实Ex
Python爬虫与数据挖掘
4
C# 优雅的处理 TCP 数据
前言Tcp是一个面向连接的流数据传输协议，用人话说就是传输是一个已经建立好连接的管道，数据都在管道里像流水一样流淌到对端。那么数据必然存在几个问题，比如数据如何持续的读取，数据包的边界等。Nagle's算法Nagle 算法的核心思想是，在一个 TCP 连接上，最多只能有一个未被确认的小数据包（小于
dotNET全栈开发
10