Abstract
During the course fo doing data analysis and modeling, a significant amount of time is spend on data preparation: loading, cleaning, transforming, and rearrangin.
在整个数据分析建模过程中, 大量的时间(80%)的时间是用在了数据的预处理中, 如数据清洗, 加载, 标准化, 重塑等.
Such tasks are often reported to take 80% or more of an analyst's time. Sometimes the way that data is store in(存储于) files or databases is not in the right format for a particular task (数据存储在文件, 或数据库中, 而且格式, 内容不是你预想的那样能直接用的) Many reseachers choose to do ad hoc processing of data from one form to another using a general-purpose programming language, like Python, R, Java or Unix text-processing tools like sed or awk.(研究者通长使用一些通用的编程语言如Python, Java, R等, 对数据进行格式转换再进行处理) Fortunately(幸运地是), pandas, alone with the built-in Python language features, provides you with a high-level, flexible, and fast set of tools to enable you to manipulate data into the right form. (
pandas是一个第三库, 提供了高效, 灵活的工具去帮助我们进行数据清理, 转换, 重塑等操作)
if you identify a type of data manipulation that isn't anywhere in this book or else-where in the pandas library, feel free to share your use case on one of the Python mailing lists or on the pandas GitHub site(如果有什么新的数据处理需求, pandas没有的话, 欢迎在Github 来留言) Indeed(事实上), much of the design and implementation of pandas has been driven by the needs of real-world applications.(panas的功能都是来自人们真实的生活工作中提出的需求哦)
In this chapter I discuss tools for missing data, duplicate data, string manipulation and some other analytical data transformations. (本章讨论缺失值, 重复值, 字符转换等, 格式等一些操作) In next chapter, I focus on combining and rearranging datasets in various ways.(下章讨论数据和合并, 连接, 重塑等操作)
import numpy as np
import pandas as pd
Missing data occurs commonly in many data analysis applications. One of the goals of pandas is to make working with missing data as panless as possible.(pandas的一个小目标就是要让处理缺失数据变得尽可能简单) For example, all of the descriptive atatistics on pandas objects exlude missing data by defalult.(默认就过滤掉缺失数据了)
The way that missing data(直接丢弃缺失值) is represented(表示) in pandas objects is somewhat(稍微) imperfect(不友好), but it is functional for a lot of users.(尽管直接忽略缺失值, 有点暴力操作,但确实方便了很多用户) For numeric data, pandas uses the floating-point value NaN to represent missing data. We call this a sentinel value(哨兵值) that can be easily detected(被检测到):
string_data = pd.Series(['aardvark', 'artichoke', np.nan, 'cj'])
string_data
0 aardvark
1 artichoke
2 NaN
3 cj
dtype: object
"string_data.notnull()"
"检测缺失值"
string_data.isnull()
'string_data.notnull()'
'检测缺失值'
0 False
1 False
2 True
3 False
dtype: bool
In pandas, we've adopted a convention used in the R programming language by refering to missing data as NA, which stands for not available(在pandas中, 我们采用了R语言中使用的规定, 将不可用的值用NA表示). In statistics (NA在统计学中,表示不存在或者不可观测)applications, NA data may either be data that does not exist or that exist but was not observed(through problems with with data collections(数据没有采集到)) When cleaning up data for analysis, it is often important to do analysis on the missing data itself to identify data collection problems or potential biases in the data cause by missing data.(数据分析通常需要专门对缺失值进行处理, 不然会影响整个分析的结果哦)
The built-in Python None value is also treated as NA in objects arrays:
string_data[0] = None
"None 是作为缺失值"
string_data.isnull()
'None 是作为缺失值'
0 True
1 False
2 True
3 False
dtype: bool
There is work onging(不间断地) in the pandas project to improve the internal details of how missing data is handled, but the user API functions, like pandas.isnull, abstract away many of the annoying(令人烦恼的) details.
See Table 7-1 for a list of some functions related to missing data handing.
| Argument | Description |
|---|---|
| dropna | 根据轴标签来过滤其缺失值 |
| fillna | 填充的值, 或者用默认方法, 如"ffill(向前填充)" or "bfill" |
| isnull | 返回布尔索引数组True表示NA |
| notnull | 跟isnull相反 |
string_data.notnull()
0 False
1 True
2 False
3 True
dtype: bool
Filter Out Missing Data
There are a few ways to filter out missing data. While you always have the options to to dy hand using pandas. isnull and boolean indexing, the dropna can be helpful. On a Series, it retruns the Series with the non-null data and index values: (Series中, dropna直接干掉了NA)
data = pd.Series([1, np.nan, 3.5, np.nan, 7])
"dropna 直接干掉缺失值 - 非原地(深拷贝)"
data.dropna()
'dropna 直接干掉缺失值 - 非原地(深拷贝)'
0 1.0
2 3.5
4 7.0
dtype: float64
data
0 1.0
1 NaN
2 3.5
3 NaN
4 7.0
dtype: float64
"this is equivalent to:"
data[data.notnull()]
'this is equivalent to:'
0 1.0
2 3.5
4 7.0
dtype: float64
With DataFrame objects, things are a bit more complex. You may want to drop rows or columns that all NA or only those containing any Na. dropna by default drops any row containing a missing value.
(就DF删除缺失值而言, 可能有删除包含NA的整条记录(row), 或整个column, 默认是删除整行(row)该条记录样本作废)
data = pd.DataFrame([
[1, 6.5, 3],
[np.nan, np.nan, np.nan],
[np.nan, 6.5, 3]
])
data
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | 1.0 | 6.5 | 3.0 |
| 1 | NaN | NaN | NaN |
| 2 | NaN | 6.5 | 3.0 |
"默认axis=0, 删除行, how=any, 只要有NA就干掉整条记录"
data.dropna()
"Passing how='all' will only drop rows that ann NA"
data.dropna(how='all')
'默认axis=0, 删除行, how=any, 只要有NA就干掉整条记录'
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | 1.0 | 6.5 | 3.0 |
"Passing how='all' will only drop rows that ann NA"
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | 1.0 | 6.5 | 3.0 |
| 2 | NaN | 6.5 | 3.0 |
To drop columns in the same way, pass axis=1
"新增一列4, 值为NA"
data[4] = np.nan
data
'新增一列4, 值为NA'
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | 4 | |
|---|---|---|---|---|
| 0 | 1.0 | 6.5 | 3.0 | NaN |
| 1 | NaN | NaN | NaN | NaN |
| 2 | NaN | 6.5 | 3.0 | NaN |
"dropna(axis=1, how='all') 表示删除全为缺失值的列, 这应该不常用吧, 变量都干掉了"
data.dropna(axis=1, how='all')
"dropna(axis=1, how='all') 表示删除全为缺失值的列, 这应该不常用吧, 变量都干掉了"
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | 1.0 | 6.5 | 3.0 |
| 1 | NaN | NaN | NaN |
| 2 | NaN | 6.5 | 3.0 |
A related ways to filter out(过滤掉行) DataFrame rows tends to(倾向于) concern(涉及) time series data. Suppose you want to keep only containing a certain nuber of observations. You can indicate this with the thresh argument.
df = pd.DataFrame(np.random.randn(7,3))
"前4行, 第2列的区域 赋值为 NA"
df.iloc[:4, 1] = np.nan
"前2行, 第3列的区域, 赋值为NA"
df.iloc[:2, 2] = np.nan
df
'前4行, 第2列的区域 赋值为 NA'
'前2行, 第3列的区域, 赋值为NA'
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | -0.737352 | NaN | NaN |
| 1 | 0.537057 | NaN | NaN |
| 2 | -0.972371 | NaN | 0.763062 |
| 3 | 0.852537 | NaN | -0.845470 |
| 4 | 0.194360 | -0.646723 | -0.244883 |
| 5 | 0.275051 | 1.139541 | -0.080366 |
| 6 | 0.544598 | -1.875903 | 1.006292 |
"删除含有缺失值的行-注意, 都是非原地哦"
df.dropna()
'删除含有缺失值的行-注意, 都是非原地哦'
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 4 | 0.194360 | -0.646723 | -0.244883 |
| 5 | 0.275051 | 1.139541 | -0.080366 |
| 6 | 0.544598 | -1.875903 | 1.006292 |
"thresh=n, 表示保留至少n个非NA值的行, n越小, 保留行越多"
df.dropna(thresh=1)
'thresh=n, 表示保留至少n个非NA值的行, n越小, 保留行越多'
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | -0.737352 | NaN | NaN |
| 1 | 0.537057 | NaN | NaN |
| 2 | -0.972371 | NaN | 0.763062 |
| 3 | 0.852537 | NaN | -0.845470 |
| 4 | 0.194360 | -0.646723 | -0.244883 |
| 5 | 0.275051 | 1.139541 | -0.080366 |
| 6 | 0.544598 | -1.875903 | 1.006292 |
Filling In Missing Data
Rather than(相比于过滤掉缺失数据的行列) filtering out missing data(and potentially(可能) discarding(丢弃) other data along with it), you may want to fiil in the 'holes' in any number of ways(也许对缺失值进行填充不失为一种好的方案, 针对于样本量少的情况下). For most purpose(目标), the fillna method is the workehorse function to use. Calling fillna with a constant(常量) replaces missing values with that value
(调用fiina, 用常量取替换掉缺失值)
df
"用0去填充DF中的缺失值 - 非原地的哦"
df.fillna(0)
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | -0.737352 | NaN | NaN |
| 1 | 0.537057 | NaN | NaN |
| 2 | -0.972371 | NaN | 0.763062 |
| 3 | 0.852537 | NaN | -0.845470 |
| 4 | 0.194360 | -0.646723 | -0.244883 |
| 5 | 0.275051 | 1.139541 | -0.080366 |
| 6 | 0.544598 | -1.875903 | 1.006292 |
'用0去填充DF中的缺失值 - 非原地的哦'
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | -0.737352 | 0.000000 | 0.000000 |
| 1 | 0.537057 | 0.000000 | 0.000000 |
| 2 | -0.972371 | 0.000000 | 0.763062 |
| 3 | 0.852537 | 0.000000 | -0.845470 |
| 4 | 0.194360 | -0.646723 | -0.244883 |
| 5 | 0.275051 | 1.139541 | -0.080366 |
| 6 | 0.544598 | -1.875903 | 1.006292 |
"除非赋值操作, 几乎绝大部分DF操作都是非原地的-深拷贝"
df
'除非赋值操作, 几乎绝大部分DF操作都是非原地的-深拷贝'
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | -0.737352 | NaN | NaN |
| 1 | 0.537057 | NaN | NaN |
| 2 | -0.972371 | NaN | 0.763062 |
| 3 | 0.852537 | NaN | -0.845470 |
| 4 | 0.194360 | -0.646723 | -0.244883 |
| 5 | 0.275051 | 1.139541 | -0.080366 |
| 6 | 0.544598 | -1.875903 | 1.006292 |
Calling fillna with a dict, you can use a different fill value for each column
# 这是之前自己爬招聘网站的数据,做练习用
data = pd.read_csv("../job2.csv")
# data.info()
data.head(10)
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| position_name | salary | work_place | experience | degree | people_num, | publish_time | work_honey | job_info | company_addr | company_name | company_link | company_type | company_staff | company_business | collect_time | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | Python开发工程师 | 0.8-1万/月 | 北京-朝阳区 | 5-7年经验 | 大专 | 招若干人 | 03-16发布 | [] | ['精通Excel宏(VBA/VBS)和Python开发', '职位要求：', '5年经验，... | NaN | 北京裕翔创新工程技术有限公司 | https://jobs.51job.com/all/co5414592.html | 民营公司 | 50-150人 | 计算机软件 | 2019-03-16 17:23:54.793818 |
| 1 | 软件开发工程师(Python/Java)(006307)(职位编号：BGI006307) | 20-30万/年 | 深圳-盐田区 | 无工作经验 | 本科 | 招1人 | 03-16发布 | ['五险一金', '交通补贴', '餐饮补贴', '通讯补贴', '年终奖金', '定期体检'] | ['1.负责基础组件的开发工作，包括算法模块、计算引擎、流程容器、通信接口等。', '2.负... | NaN | 深圳华大基因股份有限公司 | https://jobs.51job.com/all/co5405940.html | 民营公司 | 1000-5000人 | 检测，认证,医疗设备/器械 | 2019-03-16 17:23:55.300749 |
| 2 | PHP/Python程序员 | 1-1.5万/月 | 深圳-南山区 | 1年经验 | 大专 | 招1人 | 03-16发布 | ['五险一金', '员工旅游', '年终奖金', '弹性工作'] | [''] | NaN | 深圳齐采科技有限公司 | https://jobs.51job.com/all/co3957494.html | 创业公司 | 50-150人 | 互联网/电子商务 | 2019-03-16 17:23:56.189117 |
| 3 | Python开发工程师 | 0.8-1.5万/月 | 郑州-高新区 | 1年经验 | 大专 | 招2人 | 03-16发布 | ['五险一金', '年终奖金', '股票期权', '通讯补贴', '餐饮补贴', '定期体检... | ['1、基于Python进行需求的开发、部署、优化。', '1、拥有较强的Python编程能... | NaN | 广州佰聆数据股份有限公司 | https://jobs.51job.com/all/co3803005.html | 民营公司 | 150-500人 | 计算机服务(系统、数据服务、维修),计算机软件 | 2019-03-16 17:24:10.626950 |
| 4 | Python开发实习 | 4-6千/月 | 南京-雨花台区 | 无工作经验 | 大专 | 招6人 | 03-16发布 | ['五险一金', '补充医疗保险', '员工旅游', '定期体检', '弹性工作', '年终... | ['岗位职责：', ''] | NaN | 西安金堆金能源科技有限公司 | https://jobs.51job.com/all/co5379100.html | 民营公司 | 50-150人 | 新能源 | 2019-03-16 17:24:10.652288 |
| 5 | 运维工程师（8K+提成周末双休） | 0.8-1.5万/月 | 广州-天河区 | 3-4年经验 | 大专 | 招2人 | 03-12发布 | ['五险一金', '餐饮补贴', '交通补贴', '年终奖金', '绩效奖金', '带薪年假... | ['1.工作时间：周一至周五，9:00-18:00或9:30-18:30', '2.社会保险... | NaN | 广州创思云网络科技有限公司 | https://jobs.51job.com/all/co5067556.html | 创业公司 | 少于50人 | 计算机软件 | 2019-03-16 17:24:12.513511 |
| 6 | 运维主管 | 1-1.5万/月 | 上海-黄浦区 | 5-7年经验 | 本科 | 招1人 | 03-12发布 | ['五险一金', '绩效奖金', '弹性工作'] | [] | NaN | 百可录（北京）科技有限公司 | https://jobs.51job.com/all/co4545403.html | 创业公司 | 50-150人 | 计算机软件,金融/投资/证券 | 2019-03-16 17:24:13.948386 |
| 7 | 数据库开发工程师 | 1-1.5万/月 | 广州-番禺区 | 3-4年经验 | 本科 | 招1人 | 03-12发布 | ['五险一金', '年终奖金', '周末双休'] | ['1.参与项目需求分析,并抽象出数据层的需求', '2根据业务需求进行数据库设计(包括数据... | NaN | 广州策码信息技术咨询有限公司 | https://jobs.51job.com/all/co5274492.html | 创业公司 | 50-150人 | 计算机软件 | 2019-03-16 17:24:13.963832 |
| 8 | SeniorRetailSystemEngineer(Python) | 1.5-2万/月 | 北京-朝阳区 | 5-7年经验 | 本科 | 招1人 | 03-16发布 | ['五险一金', '补充医疗保险', '绩效奖金', '年终奖金'] | ['', ''] | NaN | 绫致时装（天津）有限公司 | https://jobs.51job.com/all/co181210.html | 外资（欧美） | 10000人以上 | 服装/纺织/皮革,批发/零售 | 2019-03-16 17:24:15.067126 |
| 9 | 11215N-Python开发工程师(职位编号：11215N) | 30-40万/年 | 北京-朝阳区 | 5-7年经验 | 本科 | 招3人 | 03-16发布 | ['五险一金', '带薪年假', '节日福利', '绩效奖金', '定期体检'] | [] | NaN | 平安科技（深圳）有限公司 | https://jobs.51job.com/all/co2155678.html | 民营公司 | 1000-5000人 | 互联网/电子商务,计算机软件 | 2019-03-16 17:24:15.095420 |
"以字典方式, 缺失值的列进行对应值填充"
"col 1 中缺失值用 0.5 填充, col 2 用 0 填充"
df.fillna({1:0.5, 2:0})
'以字典方式, 缺失值的列进行对应值填充'
'col 1 中缺失值用 0.5 填充, col 2 用 0 填充'
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | -0.737352 | 0.500000 | 0.000000 |
| 1 | 0.537057 | 0.500000 | 0.000000 |
| 2 | -0.972371 | 0.500000 | 0.763062 |
| 3 | 0.852537 | 0.500000 | -0.845470 |
| 4 | 0.194360 | -0.646723 | -0.244883 |
| 5 | 0.275051 | 1.139541 | -0.080366 |
| 6 | 0.544598 | -1.875903 | 1.006292 |
fillna returns a new object, but you can modify the existing object in-place
(默认是非原地的, 可以指定原地修改 inplace=True)
"inplace=True 原地修改 _ 表示不知用啥变量"
_ = df.fillna(0, inplace=True)
df
'inplace=True 原地修改 _ 表示不知用啥变量'
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | -0.737352 | 0.000000 | 0.000000 |
| 1 | 0.537057 | 0.000000 | 0.000000 |
| 2 | -0.972371 | 0.000000 | 0.763062 |
| 3 | 0.852537 | 0.000000 | -0.845470 |
| 4 | 0.194360 | -0.646723 | -0.244883 |
| 5 | 0.275051 | 1.139541 | -0.080366 |
| 6 | 0.544598 | -1.875903 | 1.006292 |
The same interpolation methods available for reindexing can be used with fillna:
df = pd.DataFrame(np.random.randn(6,3))
df.iloc[2:, 1] = np.nan # 不包括第二行
df.iloc[4:, 2] = np.nan
df
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | 0.166483 | -1.231651 | -0.812065 |
| 1 | -0.849753 | 1.941498 | -0.782602 |
| 2 | -1.296790 | NaN | 0.838213 |
| 3 | -1.033377 | NaN | 1.514566 |
| 4 | -2.002896 | NaN | NaN |
| 5 | -0.144666 | NaN | NaN |
"先前填充 method=ffill"
df.fillna(method='ffill') # forward
'先前填充 method=ffill'
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | 0.166483 | -1.231651 | -0.812065 |
| 1 | -0.849753 | 1.941498 | -0.782602 |
| 2 | -1.296790 | 1.941498 | 0.838213 |
| 3 | -1.033377 | 1.941498 | 1.514566 |
| 4 | -2.002896 | 1.941498 | 1.514566 |
| 5 | -0.144666 | 1.941498 | 1.514566 |
"这是啥??"
df.fillna(method='ffill', limit=2)
.dataframe tbody tr th:only-of-type {
vertical-align: middle;
}
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
| 0 | 1 | 2 | |
|---|---|---|---|
| 0 | 0.166483 | -1.231651 | -0.812065 |
| 1 | -0.849753 | 1.941498 | -0.782602 |
| 2 | -1.296790 | 1.941498 | 0.838213 |
| 3 | -1.033377 | 1.941498 | 1.514566 |
| 4 | -2.002896 | NaN | 1.514566 |
| 5 | -0.144666 | NaN | 1.514566 |
With fillna you can do lots of ohter things with a little creativity. For example, you might pass the mean or median value of Series:
(处理缺失值, 其实就两个方案, 要么干掉整条数据, 要么填充, 填充用得比较多, 用ffill方法填充, 用值, 如mean, median...等填充, 花样很多, 而操作很简单)
"用平均值填充(其实中位数会更好)NA"
data = pd.Series([1, np.nan, 3.5, np.nan, 7.5])
data.fillna(data.mean())
"中位数填充, cj推荐"
data.fillna(data.mean())
'用平均值填充(其实中位数会更好)NA'
0 1.0
1 4.0
2 3.5
3 4.0
4 7.5
dtype: float64
'中位数填充, cj推荐'
0 1.0
1 4.0
2 3.5
3 4.0
4 7.5
dtype: float64
See Table 7-2 for a refrence on fillna.
| 参数 | 描述 |
|---|---|
| value | 标量值or字典(col:value) 填充缺失值 |
| method | 默认'ffill' 向前填充 |
| axis | 默认axis=0, 按行填充, axis=1则按列 |
| inplace | 默认是深拷贝, 非原地, 可 inplace=True |
| limit | 不明白暂时 |