读写⽂本格式的数据
pandas提供了⼀些⽤于将表格型数据读取为DataFrame对象的函数，其中read_csv和
read_table⽤得最多
将⽂本数据转换为DataFrame时所⽤到的⼀些技术
索引：将⼀个或多个列当做返回的DataFrame处理，以及是否从⽂件、⽤户获取列名
类型推断和数据转换：包括⽤户定义值的转换、和⾃定义的缺失值标记列表等
⽇期解析：包括组合功能，⽐如将分散在多个列中的⽇期时间信息组合成结果中的单个列
迭代：⽀持对⼤⽂件进⾏逐块迭代
不规整数据问题：跳过⼀些⾏、⻚脚、注释或其他⼀些不重要的东⻄（⽐如由成千上万个逗号
隔开的数值数据）
read_csv有超过50个参数
以逗号分隔的（CSV）⽂本⽂件例⼦
# Windows，你可以使⽤type达到同样的效果
In [8]: !cat examples/ex1.csv
a,b,c,d,message
1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo
In [9]: df = pd.read_csv('examples/ex1.csv')
In [10]: df
Out[10]:
 a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo
使⽤read_table，并指定分隔符
In [11]: pd.read_table('examples/ex1.csv', sep=',')
Out[11]:
 a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo
没有标题的⽂件
In [12]: !cat examples/ex2.csv
1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo
可以让pandas为其分配默认的列名，也可以⾃⼰定义列名
# 试下不加header
In [13]: pd.read_csv('examples/ex2.csv', header=None)
Out[13]:
 0 1 2 3 4
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo
In [14]: pd.read_csv('examples/ex2.csv', names=['a', 'b', 'c', 'd', 'message'])
Out[14]:
 a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo
假设你希望将message列做成DataFrame的索引。你可以明确表示要将该列放到索引4的位置
上，也可以通过index_col参数指定”message”
In [15]: names = ['a', 'b', 'c', 'd', 'message']
In [16]: pd.read_csv('examples/ex2.csv', names=names, index_col='message')
Out[16]:
 a b c d
message
hello 1 2 3 4
world 5 6 7 8
foo 9 10 11 12
将多个列做成⼀个层次化索引，只需传⼊由列编号或列名组成的列表即可
In [17]: !cat examples/csv_mindex.csv
key1,key2,value1,value2
one,a,1,2
one,b,3,4
one,c,5,6
one,d,7,8
two,a,9,10
two,b,11,12
two,c,13,14
two,d,15,16
In [18]: parsed = pd.read_csv('examples/csv_mindex.csv',
 ....: index_col=['key1', 'key2'])
In [19]: parsed
Out[19]:
 value1 value2
key1 key2
one a 1 2
 b 3 4
 c 5 6
 d 7 8
two a 9 10
 b 11 12
 c 13 14
 d 15 16
有些表格可能不是⽤固定的分隔符去分隔字段的（⽐如空⽩符或其它模式
In [20]: list(open('examples/ex3.txt'))
Out[20]:
[' A B C\n',
 'aaa -0.264438 -1.026059 -0.619500\n',
 'bbb 0.927272 0.302904 -0.032399\n',
 'ccc -0.264273 -0.386314 -0.217601\n',
 'ddd -0.871858 -0.348382 1.100491\n']
虽然可以⼿动对数据进⾏规整，这⾥的字段是被数量不同的空⽩字符间隔开的。这种情况下，
你可以传递⼀个正则表达式作为read_table的分隔符。可以⽤正则表达式表达为\s+
# 由于列名⽐数据⾏的数量少，所以read_table推断第⼀列应该是DataFrame的索引
In [21]: result = pd.read_table('examples/ex3.txt', sep='\s+')
In [22]: result
Out[22]:
 A B C
aaa -0.264438 -1.026059 -0.619500
bbb 0.927272 0.302904 -0.032399
ccc -0.264273 -0.386314 -0.217601
ddd -0.871858 -0.348382 1.100491
异形⽂件格式处理，你可以⽤skiprows跳过⽂件的第⼀⾏、第三⾏和第四⾏
In [23]: !cat examples/ex4.csv
# hey!
a,b,c,d,message
# just wanted to make things more difficult for you
# who reads CSV files with computers, anyway?
1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo
In [24]: pd.read_csv('examples/ex4.csv', skiprows=[0, 2, 3])
Out[24]:
 a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo
缺失值处理是⽂件解析任务中的⼀个重要组成部分。缺失数据经常是要么没有（空字符串），
要么⽤某个标记值表示。默认情况下，pandas会⽤⼀组经常出现的标记值进⾏识别，⽐如NA
及NULL
In [25]: !cat examples/ex5.csv
something,a,b,c,d,message
one,1,2,3,4,NA
two,5,6,,8,world
three,9,10,11,12,foo
In [26]: result = pd.read_csv('examples/ex5.csv')
In [27]: result
Out[27]:
 something a b c d message
0 one 1 2 3.0 4 NaN
1 two 5 6 NaN 8 world
2 three 9 10 11.0 12 foo
In [28]: pd.isnull(result)
Out[28]:
 something a b c d message
0 False False False False False True
1 False False False True False False
2 False False False False False False
na_values可以⽤⼀个列表或集合的字符串表示缺失值
In [29]: result = pd.read_csv('examples/ex5.csv', na_values=['NULL'])
In [30]: result
Out[30]:
 something a b c d message
0 one 1 2 3.0 4 NaN
1 two 5 6 NaN 8 world
2 three 9 10 11.0 12 foo
字典的各列可以使⽤不同的NA标记值
In [31]: sentinels = {'message': ['foo', 'NA'], 'something': ['two']}
In [32]: pd.read_csv('examples/ex5.csv', na_values=sentinels)
Out[32]:
something a b c d message
0 one 1 2 3.0 4 NaN
1 NaN 5 6 NaN 8 world
2 three 9 10 11.0 12 NaN
逐块读取⽂本⽂件
在处理很⼤的⽂件时，或找出⼤⽂件中的参数集以便于后续处理时，可以读取⽂件的⼀⼩部分
或逐块对⽂件进⾏迭代
设置pandas显示地更紧些
In [33]: pd.options.display.max_rows = 10
In [34]: result = pd.read_csv('examples/ex6.csv')
In [35]: result
Out[35]:
 one two three four key
0 0.467976 -0.038649 -0.295344 -1.824726 L
1 -0.358893 1.404453 0.704965 -0.200638 B
2 -0.501840 0.659254 -0.421691 -0.057688 G
3 0.204886 1.074134 1.388361 -0.982404 R
4 0.354628 -0.133116 0.283763 -0.837063 Q
... ... ... ... ... ..
9995 2.311896 -0.417070 -1.409599 -0.515821 L
9996 -0.479893 -0.650419 0.745152 -0.646038 E
9997 0.523331 0.787112 0.486066 1.093156 K
9998 -0.362559 0.598894 -1.843201 0.887292 G
9999 -0.096376 -1.012999 -0.657431 -0.573315 0
[10000 rows x 5 columns]
如果只想读取⼏⾏（避免读取整个⽂件），通过nrows进⾏指定即可
In [36]: pd.read_csv('examples/ex6.csv', nrows=5)
Out[36]:
 one two three four key
0 0.467976 -0.038649 -0.295344 -1.824726 L
1 -0.358893 1.404453 0.704965 -0.200638 B
2 -0.501840 0.659254 -0.421691 -0.057688 G
3 0.204886 1.074134 1.388361 -0.982404 R
4 0.354628 -0.133116 0.283763 -0.837063 Q
要逐块读取⽂件，可以指定chunksize（⾏数）
In [874]: chunker = pd.read_csv('examples/ex6.csv', chunksize=1000)
In [875]: chunker
Out[875]: <pandas.io.parsers.TextParser at 0x8398150>
read_csv所返回的这个TextParser对象使你可以根据chunksize对⽂件进⾏逐块迭代。⽐如说，
我们可以迭代处理ex6.csv，将值计数聚合到”key”列中
tot = pd.Series([])
for piece in chunker:
 tot = tot.add(piece['key'].value_counts(), fill_value=0)
tot = tot.sort_values(ascending=False)
In [40]: tot[:10]
Out[40]:
E 368.0
X 364.0
L 346.0
O 343.0
Q 340.0
M 338.0
J 337.0
F 335.0
K 334.0
H 330.0
dtype: float64
将数据写出到⽂本格式
数据也可以被输出为分隔符格式的⽂本
In [41]: data = pd.read_csv('examples/ex5.csv')
In [42]: data
Out[42]:
 something a b c d message
0 one 1 2 3.0 4 NaN
1 two 5 6 NaN 8 world
2 three 9 10 11.0 12 foo
DataFrame的to_csv⽅法，我们可以将数据写到⼀个以逗号分隔的⽂件中
In [43]: data.to_csv('examples/out.csv')
In [44]: !cat examples/out.csv
,something,a,b,c,d,message
0,one,1,2,3.0,4,
1,two,5,6,,8,world
2,three,9,10,11.0,12,foo
使⽤其他分隔符（由于这⾥直接写出到sys.stdout，所以仅仅是打印出⽂本结果⽽已）
In [45]: import sys
In [46]: data.to_csv(sys.stdout, sep='|')
|something|a|b|c|d|message
0|one|1|2|3.0|4|
1|two|5|6||8|world
2|three|9|10|11.0|12|foo
缺失值在输出结果中会被表示为空字符串。你可能希望将其表示为别的标记值
In [47]: data.to_csv(sys.stdout, na_rep='NULL')
,something,a,b,c,d,message
0,one,1,2,3.0,4,NULL
1,two,5,6,NULL,8,world
2,three,9,10,11.0,12,foo
没有设置其他选项，则会写出⾏和列的标签。当然，它们也都可以被禁⽤
In [48]: data.to_csv(sys.stdout, index=False, header=False)
one,1,2,3.0,4,
two,5,6,,8,world
three,9,10,11.0,12,foo
你还可以只写出⼀部分的列，并以你指定的顺序排列
In [49]: data.to_csv(sys.stdout, index=False, columns=['a', 'b', 'c'])
a,b,c
1,2,3.0
5,6,
9,10,11.0
In [50]: dates = pd.date_range('1/1/2000', periods=7)
In [51]: ts = pd.Series(np.arange(7), index=dates)
In [52]: ts.to_csv('examples/tseries.csv')
In [53]: !cat examples/tseries.csv
2000-01-01,0
2000-01-02,1
2000-01-03,2
2000-01-04,3
2000-01-05,4
2000-01-06,5
2000-01-07,6
处理分隔符格式
JSON数据
pandas.read_json可以⾃动将特别格式的JSON数据集转换为Series或DataFrame
In [68]: !cat examples/example.json
[{"a": 1, "b": 2, "c": 3},
 {"a": 4, "b": 5, "c": 6},
 {"a": 7, "b": 8, "c": 9}]
In [69]: data = pd.read_json('examples/example.json')
In [70]: data
Out[70]:
 a b c
0 1 2 3
1 4 5 6
2 7 8 9
从pandas输出到JSON，使⽤to_json⽅法
In [71]: print(data.to_json())
{"a":{"0":1,"1":4,"2":7},"b":{"0":2,"1":5,"2":8},"c":{"0":3,"1":6,"2":9}}
In [72]: print(data.to_json(orient='records'))
[{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6},{"a":7,"b":8,"c":9}]
XML和HTML：Web信息收集
pandas有⼀个内置的功能，read_html，它可以使⽤lxml和Beautiful Soup⾃动将HTML⽂件
中的表格解析为DataFrame对象。
使⽤例⼦数据：美国联邦存款保险公司⼀个HTML⽂件，它记录了银⾏倒闭的情况。
安装read_html⽤到的库
conda install lxml
pip install beautifulsoup4 html5lib
In [73]: tables = pd.read_html('examples/fdic_failed_bank_list.html')
In [74]: len(tables)
Out[74]: 1
In [75]: failures = tables[0]
In [76]: failures.head()
Out[76]:
 Bank Name City ST CERT \
0 Allied Bank Mulberry AR 91
1 The Woodbury Banking Company Woodbury GA 11297
2 First CornerStone Bank King of Prussia PA 35312
3 Trust Company Bank Memphis TN 9956
4 North Milwaukee State Bank Milwaukee WI 20364
做⼀些数据清洗和分析，⽐如计算按年份计算倒闭的银⾏数
In [77]: close_timestamps = pd.to_datetime(failures['Closing Date'])
In [78]: close_timestamps.dt.year.value_counts()
Out[78]:
2010 157
2009 140
2011 92
2012 51
2008 25
 ...
2004 4
2001 4
2007 3
2003 3
2000 2
Name: Closing Date, Length: 15, dtype: int64
⼆进制数据格式
pandas对象都有⼀个⽤于将数据以pickle格式保存到磁盘上的to_pickle⽅法
In [87]: frame = pd.read_csv('examples/ex1.csv')
In [88]: frame
Out[88]:
 a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo
In [89]: frame.to_pickle('examples/frame_pickle')
读取pickle数据
In [90]: pd.read_pickle('examples/frame_pickle')
Out[90]:
 a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo
注意： pickle仅建议⽤于短期存储格式。其原因是很难保证该格式永远是稳定的；今天pickle
的对象可能⽆法被后续版本的库unpickle出来
使⽤HDF5格式
HDF5是⼀种存储⼤规模科学数组数据的⾮常好的⽂件格式。它可以被作为C标准库，带有许
多语⾔的接⼝，如Java、Python和MATLAB等。HDF5中的HDF指的是层次型数据格式
（hierarchical data format）。每个HDF5⽂件都含有⼀个⽂件系统式的节点结构，它使你能
够存储多个数据集并⽀持元数据。与其他简单格式相⽐，HDF5⽀持多种压缩器的即时压缩，
还能更⾼效地存储重复模式数据。对于那些⾮常⼤的⽆法直接放⼊内存的数据集，HDF5就是
不错的选择，因为它可以⾼效地分块读写。
In [92]: frame = pd.DataFrame({'a': np.random.randn(100)})
In [93]: store = pd.HDFStore('mydata.h5')
In [94]: store['obj1'] = frame
In [95]: store['obj1_col'] = frame['a']
In [96]: store
Out[96]:
<class 'pandas.io.pytables.HDFStore'>
File path: mydata.h5
HDF5⽂件中的对象可以通过与字典⼀样的API进⾏获取
In [97]: store['obj1']
Out[97]:
 a
0 -0.204708
1 0.478943
2 -0.519439
3 -0.555730
4 1.965781
.. ...
95 0.795253
96 0.118110
97 -0.748532
98 0.584970
99 0.152677
[100 rows x 1 columns]
HDFStore⽀持两种存储模式，’fixed’和’table’。后者通常会更慢，但是⽀持使⽤特殊语法进⾏
查询操作
In [98]: store.put('obj2', frame, format='table')
In [99]: store.select('obj2', where=['index >= 10 and index <= 15'])
Out[99]:
 a
10 1.007189
11 -1.296221
12 0.274992
13 0.228913
14 1.352917
15 0.886429
In [100]: store.close()
pandas.read_hdf函数可以快捷使⽤这些⼯具
In [101]: frame.to_hdf('mydata.h5', 'obj3', format='table')
In [102]: pd.read_hdf('mydata.h5', 'obj3', where=['index < 5'])
Out[102]:
 a
0 -0.204708
1 0.478943
2 -0.519439
3 -0.555730
4 1.965781
读取Microsoft Excel⽂件
pandas的ExcelFile类或pandas.read_excel函数⽀持读取存储在Excel 2003（或更⾼版本）中
的表格型数据。这两个⼯具分别使⽤扩展包xlrd和openpyxl读取XLS和XLSX⽂件。你可以⽤
pip或conda安装它们
In [104]: xlsx = pd.ExcelFile('examples/ex1.xlsx')
In [105]: pd.read_excel(xlsx, 'Sheet1')
Out[105]:
 a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo
In [106]: frame = pd.read_excel('examples/ex1.xlsx', 'Sheet1')
In [107]: frame
Out[107]:
 a b c d message
0 1 2 3 4 hello
1 5 6 7 8 world
2 9 10 11 12 foo
将pandas数据写⼊为Excel格式
In [108]: writer = pd.ExcelWriter('examples/ex2.xlsx')
In [109]: frame.to_excel(writer, 'Sheet1')
In [110]: writer.save()
也可以这样存
In [111]: frame.to_excel('examples/ex2.xlsx')
Web APIs交互
In [113]: import requests
In [114]: url = 'https://api.github.com/repos/pandas-dev/pandas/issues'
In [115]: resp = requests.get(url)
In [116]: resp
Out[116]: <Response [200]>
In [117]: data = resp.json()
In [118]: data[0]['title']
Out[118]: 'Period does not round down for frequencies less that 1 hour'
In [119]: issues = pd.DataFrame(data, columns=['number', 'title',
 .....: 'labels', 'state'])
In [120]: issues
Out[120]:
 number title \
0 17666 Period does not round down for frequencies les...
1 17665 DOC: improve docstring of function where
2 17664 COMPAT: skip 32-bit test on int repr
3 17662 implement Delegator class
4 17654 BUG: Fix series rename called with str alterin...
.. ... ...
25 17603 BUG: Correctly localize naive datetime strings...
26 17599 core.dtypes.generic --> cython
27 17596 Merge cdate_range functionality into bdate_range 
28 17587 Time Grouper bug fix when applied for list gro...
29 17583 BUG: fix tz-aware DatetimeIndex + TimedeltaInd...
[30 rows x 4 columns]
数据库交互
在商业场景下，⼤多数数据可能不是存储在⽂本或Excel⽂件中。基于SQL的关系型数据库
（如SQL Server、PostgreSQL和MySQL等）使⽤⾮常⼴泛。
In [121]: import sqlite3
In [122]: query = """
 .....: CREATE TABLE test
 .....: (a VARCHAR(20), b VARCHAR(20),
 .....: c REAL, d INTEGER
 .....: );"""
In [123]: con = sqlite3.connect('mydata.sqlite')
In [124]: con.execute(query)
Out[124]: <sqlite3.Cursor at 0x7f6b12a50f10>
In [125]: con.commit()
In [126]: data = [('Atlanta', 'Georgia', 1.25, 6),
 .....: ('Tallahassee', 'Florida', 2.6, 3),
 .....: ('Sacramento', 'California', 1.7, 5)]
In [127]: stmt = "INSERT INTO test VALUES(?, ?, ?, ?)"
In [128]: con.executemany(stmt, data)
Out[128]: <sqlite3.Cursor at 0x7f6b15c66ce0>
In [130]: cursor = con.execute('select * from test')
In [131]: rows = cursor.fetchall()
In [132]: rows
Out[132]:
[('Atlanta', 'Georgia', 1.25, 6),
 ('Tallahassee', 'Florida', 2.6, 3),
 ('Sacramento', 'California', 1.7, 5)]
In [133]: cursor.description
Out[133]:
(('a', None, None, None, None, None, None),
 ('b', None, None, None, None, None, None),
 ('c', None, None, None, None, None, None),
 ('d', None, None, None, None, None, None))
In [134]: pd.DataFrame(rows, columns=[x[0] for x in cursor.description])
Out[134]:
 a b c d
0 Atlanta Georgia 1.25 6
1 Tallahassee Florida 2.60 3
2 Sacramento California 1.70 5
这种数据规整操作相当多，你肯定不想每查⼀次数据库就重写⼀次。SQLAlchemy项⽬是⼀个
流⾏的Python SQL⼯具，它抽象出了SQL数据库中的许多常⻅差异。pandas有⼀个read_sql
函数，可以让你轻松的从SQLAlchemy连接读取数据。
In [135]: import sqlalchemy as sqla
db= sqla.create_engine('mysql+pymysql://root:123456@127.0.0.1/taobao?
charset=utf8')
In [137]: pd.read_sql('select * from product', db)

在数据分析和建模的过程中，相当多的时间要⽤在数据准备上：加载、清理、转换以及重塑。
这些⼯作会占到分析师时间的80%或更多。
pandas和内置的Python标准库提供了⼀组⾼级的、灵活的、快速的⼯具，可以让你轻松地将
数据规整为想要的格式。
处理缺失数据
检测缺失数据
In [10]: string_data = pd.Series(['aardvark', 'artichoke', np.nan, 'avocado'])
In [11]: string_data
Out[11]:
0 aardvark
1 artichoke
2 NaN
3 avocado
dtype: object
In [12]: string_data.isnull()
Out[12]:
0 False
1 False
2 True
3 False
dtype: bool
在统计应⽤中，NA数据可能是不存在的数据或者虽然存在，但是没有观察到（例如，数据采
集中发⽣了问题）。当进⾏数据清洗以进⾏分析时，最好直接对缺失数据进⾏分析，以判断数
据采集的问题或缺失数据可能导致的偏差。
Python内置的None值在对象数组中也可以作为NA：
In [13]: string_data[0] = None
In [14]: string_data.isnull()
Out[14]:
0 True
1 False
2 True
3 False
dtype: bool
滤除缺失数据
In [15]: from numpy import nan as NA
In [16]: data = pd.Series([1, NA, 3.5, NA, 7])
In [17]: data.dropna()
Out[17]:
0 1.0
2 3.5
4 7.0
dtype: float64
和这个效果⼀样
In [18]: data[data.notnull()]
Out[18]:
0 1.0
2 3.5
4 7.0
dtype: float64
DataFrame对象，dropna默认丢弃任何含有缺失值的⾏
In [19]: data = pd.DataFrame([[1., 6.5, 3.], [1., NA, NA],
 ....: [NA, NA, NA], [NA, 6.5, 3.]])
In [20]: cleaned = data.dropna()
In [21]: data
Out[21]:
 0 1 2
0 1.0 6.5 3.0
1 1.0 NaN NaN
2 NaN NaN NaN
3 NaN 6.5 3.0
In [22]: cleaned
Out[22]:
 0 1 2
0 1.0 6.5 3.0
传⼊how=‘all’将只丢弃全为NA的那些⾏
In [23]: data.dropna(how='all')
Out[23]:
 0 1 2
0 1.0 6.5 3.0
1 1.0 NaN NaN
3 NaN 6.5 3.0
In [24]: data[4] = NA
In [25]: data
Out[25]:
 0 1 2 4
0 1.0 6.5 3.0 NaN
1 1.0 NaN NaN NaN
2 NaN NaN NaN NaN
3 NaN 6.5 3.0 NaN
In [26]: data.dropna(axis=1, how='all')
Out[26]: 
 0 1 2
0 1.0 6.5 3.0
1 1.0 NaN NaN
2 NaN NaN NaN
3 NaN 6.5 3.0
In [27]: df = pd.DataFrame(np.random.randn(7, 3))
In [28]: df.iloc[:4, 1] = NA
In [29]: df.iloc[:2, 2] = NA
In [30]: df
Out[30]:
 0 1 2
0 -0.204708 NaN NaN
1 -0.555730 NaN NaN
2 0.092908 NaN 0.769023
3 1.246435 NaN -1.296221
4 0.274992 0.228913 1.352917
5 0.886429 -2.001637 -0.371843
6 1.669025 -0.438570 -0.539741
In [31]: df.dropna()
Out[31]:
 0 1 2
4 0.274992 0.228913 1.352917
5 0.886429 -2.001637 -0.371843
6 1.669025 -0.438570 -0.539741
# 删除⼩于n个⾮空值的⾏
In [32]: df.dropna(thresh=2)
Out[32]:
 0 1 2
2 0.092908 NaN 0.769023
3 1.246435 NaN -1.296221
4 0.274992 0.228913 1.352917
5 0.886429 -2.001637 -0.371843
6 1.669025 -0.438570 -0.539741
填充缺失数据
fillna⽅法是最主要的函数。通过⼀个常数调⽤fillna就会将缺失值替换为那个常数值
In [33]: df.fillna(0)
Out[33]:
 0 1 2
0 -0.204708 0.000000 0.000000
1 -0.555730 0.000000 0.000000
2 0.092908 0.000000 0.769023
3 1.246435 0.000000 -1.296221
4 0.274992 0.228913 1.352917
5 0.886429 -2.001637 -0.371843
6 1.669025 -0.438570 -0.539741
通过⼀个字典调⽤fillna，就可以实现对不同的列填充不同的值
In [34]: df.fillna({1: 0.5, 2: 0})
Out[34]:
 0 1 2
0 -0.204708 0.500000 0.000000
1 -0.555730 0.500000 0.000000
2 0.092908 0.500000 0.769023
3 1.246435 0.500000 -1.296221
4 0.274992 0.228913 1.352917
5 0.886429 -2.001637 -0.371843
6 1.669025 -0.438570 -0.539741
fillna默认会返回新对象，但也可以对现有对象进⾏就地修改
In [35]: _ = df.fillna(0, inplace=True)
In [36]: df
Out[36]:
 0 1 2
0 -0.204708 0.000000 0.000000
1 -0.555730 0.000000 0.000000
2 0.092908 0.000000 0.769023
3 1.246435 0.000000 -1.296221
4 0.274992 0.228913 1.352917
5 0.886429 -2.001637 -0.371843
6 1.669025 -0.438570 -0.539741
对reindexing有效的那些插值⽅法也可⽤于fillna
In [37]: df = pd.DataFrame(np.random.randn(6, 3))
In [38]: df.iloc[2:, 1] = NA
In [39]: df.iloc[4:, 2] = NA
In [40]: df
Out[40]:
 0 1 2
0 0.476985 3.248944 -1.021228
1 -0.577087 0.124121 0.302614
2 0.523772 NaN 1.343810
3 -0.713544 NaN -2.370232
4 -1.860761 NaN NaN
5 -1.265934 NaN NaN
In [41]: df.fillna(method='ffill')
Out[41]:
 0 1 2
0 0.476985 3.248944 -1.021228
1 -0.577087 0.124121 0.302614
2 0.523772 0.124121 1.343810
3 -0.713544 0.124121 -2.370232
4 -1.860761 0.124121 -2.370232
5 -1.265934 0.124121 -2.370232
In [42]: df.fillna(method='ffill', limit=2)
Out[42]:
 0 1 2
0 0.476985 3.248944 -1.021228
1 -0.577087 0.124121 0.302614
2 0.523772 0.124121 1.343810
3 -0.713544 0.124121 -2.370232
4 -1.860761 NaN -2.370232
5 -1.265934 NaN -2.370232
传⼊Series的平均值或中位数
In [43]: data = pd.Series([1., NA, 3.5, NA, 7])
In [44]: data.fillna(data.mean())
Out[44]:
0 1.000000
1 3.833333
2 3.500000
3 3.833333
4 7.000000
dtype: float64
数据转换
移除重复数据
In [45]: data = pd.DataFrame({'k1': ['one', 'two'] * 3 + ['two'],
 ....: 'k2': [1, 1, 2, 3, 3, 4, 4]})
In [46]: data
Out[46]:
 k1 k2
0 one 1
1 two 1
2 one 2
3 two 3
4 one 3
5 two 4
6 two 4
DataFrame的duplicated⽅法返回⼀个布尔型Series，表示各⾏是否是重复⾏
In [47]: data.duplicated()
Out[47]:
0 False
1 False
2 False
3 False
4 False
5 False
6 True
dtype: bool
drop_duplicates⽅法，它会返回⼀个DataFrame，重复的数组会标为False
In [48]: data.drop_duplicates()
Out[48]:
 k1 k2
0 one 1
1 two 1
2 one 2
3 two 3
4 one 3
5 two 4
只希望根据k1列过滤重复项
In [49]: data['v1'] = range(7)
In [50]: data.drop_duplicates(['k1'])
Out[50]:
 k1 k2 v1
0 one 1 0
1 two 1 1
duplicated和drop_duplicates默认保留的是第⼀个出现的值组合。传⼊keep=‘last’则保留最
后⼀个
In [51]: data.drop_duplicates(['k1', 'k2'], keep='last')
Out[51]:
 k1 k2 v1
0 one 1 0
1 two 1 1
2 one 2 2
3 two 3 3
4 one 3 4
6 two 4 6
利⽤函数或映射进⾏数据转换
根据数组、Series或DataFrame列中的值来实现转换⼯作
In [52]: data = pd.DataFrame({'food': ['bacon', 'pulled pork', 'bacon',
 ....: 'Pastrami', 'corned beef', 'Bacon',
 ....: 'pastrami', 'honey ham', 'nova lox'],
 ....: 'ounces': [4, 3, 12, 6, 7.5, 8, 3, 5, 6]})
In [53]: data
Out[53]:
 food ounces
0 bacon 4.0
1 pulled pork 3.0
2 bacon 12.0
3 Pastrami 6.0
4 corned beef 7.5
5 Bacon 8.0
6 pastrami 3.0
7 honey ham 5.0
8 nova lox 6.0
添加⼀列表示该⾁类⻝物来源的动物类型。我们先编写⼀个不同⾁类到动物的映射
meat_to_animal = {
 'bacon': 'pig',
 'pulled pork': 'pig',
 'pastrami': 'cow',
 'corned beef': 'cow',
 'honey ham': 'pig',
 'nova lox': 'salmon'
}
使⽤Series的str.lower⽅法，将各个值转换为⼩写
In [55]: lowercased = data['food'].str.lower()
In [56]: lowercased
Out[56]:
0 bacon
1 pulled pork
2 bacon
3 pastrami
4 corned beef
5 bacon
6 pastrami
7 honey ham
8 nova lox
Name: food, dtype: object
In [57]: data['animal'] = lowercased.map(meat_to_animal)
In [58]: data
Out[58]:
 food ounces animal
0 bacon 4.0 pig
1 pulled pork 3.0 pig
2 bacon 12.0 pig
3 Pastrami 6.0 cow
4 corned beef 7.5 cow
5 Bacon 8.0 pig
6 pastrami 3.0 cow
7 honey ham 5.0 pig
8 nova lox 6.0 salmon
也可以传⼊⼀个能够完成全部这些⼯作的函数
In [59]: data['food'].map(lambda x: meat_to_animal[x.lower()])
Out[59]:
0 pig
1 pig
2 pig
3 cow
4 cow
5 pig
6 cow
7 pig
8 salmon
Name: food, dtype: object
替换值
In [60]: data = pd.Series([1., -999., 2., -999., -1000., 3.])
In [61]: data
Out[61]:
0 1.0
1 -999.0
2 2.0
3 -999.0
4 -1000.0
5 3.0
-999这个值可能是⼀个表示缺失数据的标记值。要将其替换为pandas能够理解的NA值
In [62]: data.replace(-999, np.nan)
Out[62]:
0 1.0
1 NaN
2 2.0
3 NaN
4 -1000.0
5 3.0
dtype: float64
⼀次性替换多个值
In [63]: data.replace([-999, -1000], np.nan)
Out[63]:
0 1.0
1 NaN
2 2.0
3 NaN
4 NaN
5 3.0
dtype: float64
让每个值有不同的替换值，可以传递⼀个替换列表
In [64]: data.replace([-999, -1000], [np.nan, 0])
Out[64]:
0 1.0
1 NaN
2 2.0
3 NaN
4 0.0
5 3.0
dtype: float64
In [65]: data.replace({-999: np.nan, -1000: 0})
Out[65]:
0 1.0
1 NaN
2 2.0
3 NaN
4 0.0
5 3.0
dtype: float64
重命名轴索引
In [66]: data = pd.DataFrame(np.arange(12).reshape((3, 4)),
 ....: index=['Ohio', 'Colorado', 'New York'],
 ....: columns=['one', 'two', 'three', 'four'])
In [67]: transform = lambda x: x[:4].upper()
In [68]: data.index.map(transform)
Out[68]: Index(['OHIO', 'COLO', 'NEW '], dtype='object')
In [69]: data.index = data.index.map(transform)
In [70]: data
Out[70]:
one two three four
OHIO 0 1 2 3
COLO 4 5 6 7
NEW 8 9 10 11
如果想要创建数据集的转换版（⽽不是修改原始数据）
In [71]: data.rename(index=str.title, columns=str.upper)
Out[71]:
 ONE TWO THREE FOUR
Ohio 0 1 2 3
Colo 4 5 6 7
New 8 9 10 11
rename可以结合字典型对象实现对部分轴标签的更新
In [72]: data.rename(index={'OHIO': 'INDIANA'},
 ....: columns={'three': 'peekaboo'})
Out[72]:
one two peekaboo four
INDIANA 0 1 2 3
COLO 4 5 6 7
NEW 8 9 10 11
就地修改某个数据集，传⼊inplace=True即可
In [73]: data.rename(index={'OHIO': 'INDIANA'}, inplace=True)
In [74]: data
Out[74]:
 one two three four
INDIANA 0 1 2 3
COLO 4 5 6 7
NEW 8 9 10 11
离散化和⾯元划分
为了便于分析，连续数据常常被离散化或拆分为“⾯元”（bin）。假设有⼀组⼈员数据，⽽你
希望将它们划分为不同的年龄组
In [75]: ages = [20, 22, 25, 27, 21, 23, 37, 31, 61, 45, 41, 32]
将这些数据划分为“18到25”、“26到35”、“35到60”以及“60以上”⼏个⾯元
In [76]: bins = [18, 25, 35, 60, 100]
In [77]: cats = pd.cut(ages, bins)
In [78]: cats
Out[78]:
[(18, 25], (18, 25], (18, 25], (25, 35], (18, 25], ..., (25, 35], (60, 100],
(35,60], (35, 60], (25, 35]]
Length: 12
Categories (4, interval[int64]): [(18, 25] < (25, 35] < (35, 60] < (60, 100]]
pandas返回的是⼀个特殊的Categorical对象。结果展示了pandas.cut划分的⾯元。你可以将
其看做⼀组表示⾯元名称的字符串
In [79]: cats.codes
Out[79]: array([0, 0, 0, 1, 0, 0, 2, 1, 3, 2, 2, 1], dtype=int8)
In [80]: cats.categories
Out[80]:
IntervalIndex([(18, 25], (25, 35], (35, 60], (60, 100]]
 closed='right',
 dtype='interval[int64]')
# ⾯元计数
In [81]: pd.value_counts(cats)
Out[81]:
(18, 25] 5
(35, 60] 3
(25, 35] 3
(60, 100] 1
dtype: int64
跟“区间”的数学符号⼀样，圆括号表示开端，⽽⽅括号则表示闭端（包括）。哪边是闭端可以
通过right=False进⾏修改
In [82]: pd.cut(ages, [18, 26, 36, 61, 100], right=False)
Out[82]:
[[18, 26), [18, 26), [18, 26), [26, 36), [18, 26), ..., [26, 36), [61, 100),
[36,
 61), [36, 61), [26, 36)]
Length: 12
Categories (4, interval[int64]): [[18, 26) < [26, 36) < [36, 61) < [61, 100)]
通过传递⼀个列表或数组到labels，设置⾯元名称
In [83]: group_names = ['Youth', 'YoungAdult', 'MiddleAged', 'Senior']
In [84]: pd.cut(ages, bins, labels=group_names)
Out[84]:
[Youth, Youth, Youth, YoungAdult, Youth, ..., YoungAdult, Senior, MiddleAged,
Mid
dleAged, YoungAdult]
Length: 12
Categories (4, object): [Youth < YoungAdult < MiddleAged < Senior]
向cut传⼊的是⾯元的数量⽽不是确切的⾯元边界，则它会根据数据的最⼩值和最⼤值计算等
⻓⾯元。下⾯这个例⼦中，我们将⼀些均匀分布的数据分成四组，选项precision=2，限定⼩
数只有两位
In [85]: data = np.random.rand(20)
In [86]: pd.cut(data, 4, precision=2)
Out[86]:
[(0.34, 0.55], (0.34, 0.55], (0.76, 0.97], (0.76, 0.97], (0.34, 0.55], ...,
(0.34
, 0.55], (0.34, 0.55], (0.55, 0.76], (0.34, 0.55], (0.12, 0.34]]
Length: 20
Categories (4, interval[float64]): [(0.12, 0.34] < (0.34, 0.55] < (0.55, 0.76]
<
(0.76, 0.97]]
qcut是⼀个⾮常类似于cut的函数，它可以根据样本分位数对数据进⾏⾯元划分。
In [87]: data = np.random.randn(1000) # Normally distributed
In [88]: cats = pd.qcut(data, 4) # Cut into quartiles
In [89]: cats
Out[89]:
[(-0.0265, 0.62], (0.62, 3.928], (-0.68, -0.0265], (0.62, 3.928], (-0.0265,
0.62]
, ..., (-0.68, -0.0265], (-0.68, -0.0265], (-2.95, -0.68], (0.62, 3.928],
(-0.68,
 -0.0265]]
Length: 1000
Categories (4, interval[float64]): [(-2.95, -0.68] < (-0.68, -0.0265] <
(-0.0265,
 0.62] <
 (0.62, 3.928]]
In [90]: pd.value_counts(cats)
Out[90]:
(0.62, 3.928] 250
(-0.0265, 0.62] 250
(-0.68, -0.0265] 250
(-2.95, -0.68] 250
dtype: int64
检测和过滤异常值
In [92]: data = pd.DataFrame(np.random.randn(1000, 4))
In [93]: data.describe()
Out[93]:
 0 1 2 3
count 1000.000000 1000.000000 1000.000000 1000.000000
mean 0.049091 0.026112 -0.002544 -0.051827
std 0.996947 1.007458 0.995232 0.998311
min -3.645860 -3.184377 -3.745356 -3.428254
25% -0.599807 -0.612162 -0.687373 -0.747478
50% 0.047101 -0.013609 -0.022158 -0.088274
75% 0.756646 0.695298 0.699046 0.623331
max 2.653656 3.525865 2.735527 3.366626
某列中绝对值⼤⼩超过3的值
In [94]: col = data[2]
In [95]: col[np.abs(col) > 3]
Out[95]:
41 -3.399312
136 -3.745356
Name: 2, dtype: float64
选出全部含有“超过3或－3的值”的⾏
In [96]: data[(np.abs(data) > 3).any(1)]
Out[96]:
 0 1 2 3
41 0.457246 -0.025907 -3.399312 -0.974657
60 1.951312 3.260383 0.963301 1.201206
136 0.508391 -0.196713 -3.745356 -1.520113
235 -0.242459 -3.056990 1.918403 -0.578828
258 0.682841 0.326045 0.425384 -3.428254
322 1.179227 -3.184377 1.369891 -1.074833
544 -3.548824 1.553205 -2.186301 1.277104
635 -0.578093 0.193299 1.397822 3.366626
782 -0.207434 3.525865 0.283070 0.544635
803 -3.645860 0.255475 -0.549574 -1.907459
np.sign(data)可以⽣成1和-1
In [99]: np.sign(data).head()
Out[99]:
 0 1 2 3
0 -1.0 1.0 -1.0 1.0
1 1.0 -1.0 1.0 -1.0
2 1.0 1.0 1.0 -1.0
3 -1.0 -1.0 1.0 -1.0
4 -1.0 1.0 -1.0 -1.0
排列和随机采样
利⽤numpy.random.permutation函数可以轻松实现对Series或DataFrame的列的排列⼯作
（permuting，随机重排序）
In [100]: df = pd.DataFrame(np.arange(20).reshape((5, 4)))
In [101]: sampler = np.random.permutation(5)
In [102]: sampler
Out[102]: array([3, 1, 4, 2, 0])
In [103]: df
Out[103]:
 0 1 2 3
0 0 1 2 3
1 4 5 6 7
2 8 9 10 11
3 12 13 14 15
4 16 17 18 19
In [104]: df.take(sampler)
Out[104]:
 0 1 2 3
3 12 13 14 15
1 4 5 6 7
4 16 17 18 19
2 8 9 10 11
0 0 1 2 3
In [105]: df.sample(n=3)
Out[105]:
 0 1 2 3
3 12 13 14 15
4 16 17 18 19
2 8 9 10 11
要通过替换的⽅式产⽣样本（允许重复选择），可以传递replace=True到sample
In [106]: choices = pd.Series([5, 7, -1, 6, 4])
In [107]: draws = choices.sample(n=10, replace=True)
In [108]: draws
Out[108]:
4 4
1 7
4 4
2 -1
0 5
3 6
1 7
4 4
0 5
4 4
dtype: int64
计算指标/哑变量
将分类变量（categorical variable）转换为“哑变量”或“指标矩阵”
In [109]: df = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'b'],
 .....: 'data1': range(6)})
In [110]: pd.get_dummies(df['key'])
Out[110]:
 a b c
0 0 1 0
1 0 1 0
2 1 0 0
3 0 0 1
4 1 0 0
5 0 1 0
In [111]: dummies = pd.get_dummies(df['key'], prefix='key')
In [112]: df_with_dummy = df[['data1']].join(dummies)
In [113]: df_with_dummy
Out[113]:
 data1 key_a key_b key_c
0 0 0 1 0
1 1 0 1 0
2 2 1 0 0
3 3 0 0 1
4 4 1 0 0
5 5 0 1 0
In [114]: mnames = ['movie_id', 'title', 'genres']
In [115]: movies = pd.read_table('datasets/movielens/movies.dat', sep='::',
 .....: header=None, names=mnames, engine='python')
In [116]: movies[:10]
Out[116]:
 movie_id title genres
0 1 Toy Story (1995) Animation|Children's|Comedy
1 2 Jumanji (1995) Adventure|Children's|Fantasy
2 3 Grumpier Old Men (1995) Comedy|Romance
3 4 Waiting to Exhale (1995) Comedy|Drama
4 5 Father of the Bride Part II (1995) Comedy
5 6 Heat (1995) Action|Crime|Thriller
6 7 Sabrina (1995) Comedy|Romance
7 8 Tom and Huck (1995) Adventure|Children's
8 9 Sudden Death (1995)
Action
9 10 GoldenEye (1995) Action|Adventure|Thriller
In [117]: all_genres = []
In [118]: for x in movies.genres:
 .....: all_genres.extend(x.split('|'))
In [119]: genres = pd.unique(all_genres)
构建指标DataFrame的⽅法之⼀是从⼀个全零DataFrame开始
In [121]: zero_matrix = np.zeros((len(movies), len(genres)))
In [122]: dummies = pd.DataFrame(zero_matrix, columns=genres)
pandas的⽮量化字符串函数
In [167]: data = {'Dave': 'dave@google.com', 'Steve': 'steve@gmail.com',
 .....: 'Rob': 'rob@gmail.com', 'Wes': np.nan}
In [168]: data = pd.Series(data)
In [169]: data
Out[169]:
Dave dave@google.com
Rob rob@gmail.com
Steve steve@gmail.com
Wes NaN
dtype: object
In [170]: data.isnull()
Out[170]:
Dave False
Rob False
Steve False
Wes True
dtype: bool
通过data.map，所有字符串和正则表达式⽅法都能被应⽤于（传⼊lambda表达式或其他函
数）各个值，但是如果存在NA（null）就会报错。为了解决这个问题，Series有⼀些能够跳过
NA值的⾯向数组⽅法，进⾏字符串操作。通过Series的str属性即可访问这些⽅法。例如，我
们可以通过str.contains检查各个电⼦邮件地址是否含有"gmail"：
In [171]: data.str.contains('gmail')
Out[171]:
Dave False
Rob True
Steve True
Wes NaN
dtype: object
也可以使⽤正则表达式，还可以加上任意re选项（如IGNORECASE）
import re
pattern = r'([A-Z0-9._%+-]+)@([A-Z0-9.-]+)\.([A-Z]{2,4})'
In [172]: pattern
Out[172]: '([A-Z0-9._%+-]+)@([A-Z0-9.-]+)\\.([A-Z]{2,4})'
In [173]: data.str.findall(pattern, flags=re.IGNORECASE)
Out[173]:
Dave [(dave, google, com)]
Rob [(rob, gmail, com)]
Steve [(steve, gmail, com)]
Wes NaN
dtype: object
In [174]: matches = data.str.match(pattern, flags=re.IGNORECASE)
In [175]: matches
Out[175]:
Dave True
Rob True
Steve True
Wes NaN
dtype: object
字符串进⾏截取
In [178]: data.str[:5]
Out[178]:
Dave dave@
Rob rob@g
Steve steve
Wes NaN
dtype: object