Pandas载入数据（csv, JSON,XML,Excel,二进制数据,网页数据，数据库数据）

数据载入，存储及文件格式

这里用到的样本数据git地址：https://github.com/wesm/pydata-book

访问数据是使用各类工具所必须的第一步（获取数据）。这里重点讨论使用pandas进行数据的输入输出，尽管其他工具也可以帮助读取和写入各种格式的数据。

---

输入和输出通常有以下几种类型：

1. 读取文本文件以及硬盘上其他更高效的格式文件
2. 从数据库中载入数据
3. 与网络资源进行交互（比如WEB API）

Table of Contents

文本格式数据的读写

将表格数据读取为DataFrame是pandas的重要特性。下表总结了部分实现该功能的函数，read_csv, read_table可能是后期我们使用比较多的。对于excel数据我们使用read_excel。

• Pandas的解析函数：

函数	描述
read_csv	从文件，URL或文件型对象读取分隔好的数据，逗号是默认分隔符
read_table	从文件，URL或文件型对象读取分隔好的数据，制表符（\t）是默认分隔符
read_fwf	从特定宽度格式的文件中读取数据（无分隔符）
read_clipboard	read_table的剪贴板版本，将表格从WEB页面上转换成数据时有用
read_excel	从excel的xls, xlsx文件中读取表格数据
read_hdf	读取用pandas存储的HDF5文件
read_html	从html文件中读取所有的表格数据
read_json	从JSON（JavaScript Object Notation）字符串中读取数据
read_msgpack	读取MessagePack二进制格式的pandas数据
read_pickle	读取以python pickle 格式存储的任意对象
read_sas	读取存储在SAS系统中定制存储格式的SAS数据集
read_sql	将SQL查询的记过（使用SQLAlchemy）读取为pandas的DataFrame
read_stata	读取Stata格式的数据集
read_Feather	读取Feather二进制格式

这些函的主要可选参数有以下几种类型：

1. 索引
   
   可以将一或者多个列作为返回的DataFrame，从文件中或用户处获得列名，或者没有列明。
2. 类型推断和数据转换
   
   包括用户自定义的值转换和自定义的缺失值符号列表
3. 日期和时间解析
   
   包括组合功能，也包括将分散在多个列上的日期和时间信息组合成结果中的单个列
4. 迭代
   
   支持对大型文件的分块迭代
5. 未清洗数据问题
   
   跳过行、页脚、注释以及其他次要数据，比如使用逗号分割千位的数字

随着时间的推移，数据加载函数变得比较复杂，read_csv()就有50+的参数选择，好在官方文档提供了很好的帮助。

# %load pydata-book/examples/ex1.csv
a,b,c,d,message
1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo

这是jupyter加载数据的一种方式，我们可以看到数据是用逗号分隔的。我们可以使用read_csv将他读入一个DataFrame:

import numpy as np
import pandas as pd

df = pd.read_csv(r'pydata-book/examples/ex1.csv')
df

	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制定分隔符：

df = pd.read_table(r'pydata-book/examples/ex1.csv', sep=',')
df

	a	b	c	d	message
0	1	2	3	4	hello
1	5	6	7	8	world
2	9	10	11	12	foo

有的文件并没有表头行，例如：

# %load pydata-book/examples/ex2.csv
1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo

我们需要制定不含表头，否则会将第一行作为表头，而这不是我们期望的。

header参数提供了跳过表头的方式，pandas会提供默认的表头（0-N默认索引），我们也可以自定义列名：

df = pd.read_csv(r'pydata-book/examples/ex2.csv', header=None) # 不读取表头，用默认表头
df

	0	1	2	3	4
0	1	2	3	4	hello
1	5	6	7	8	world
2	9	10	11	12	foo

df = pd.read_csv(r'pydata-book/examples/ex2.csv', header=None, 
                 names=['a', 'b', 'c', 'd', 'message'])    # header=None 可以缺省，因为已经自定义表头了
df

	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参数：

names = ['a', 'b', 'c', 'd', 'message']
pd.read_csv(r'pydata-book/examples/ex2.csv', names=names, 
            index_col='message') # message 可替换成4，指定作为行索引的列的位置

	a	b	c	d
message
hello	1	2	3	4
world	5	6	7	8
foo	9	10	11	12

如果我们需要一个多级索引的数据，需要用列表传递列名：

# %load pydata-book/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

parsed = pd.read_csv(r'pydata-book/examples/csv_mindex.csv',
                    index_col=['key1', 'key2'])
parsed

		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

遇到分隔符不是固定的情况，例子如下：

# %load pydata-book/examples/ex3.txt
            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

不同数量的空格！这时我们需要使用正则表达式作为分隔符，这里使用的正则表达式是’\s+’(至少1个或多个空格)：

result = pd.read_csv('pydata-book/examples/ex3.txt', sep='\s+')
result

	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

这个例子另一个有趣的地方，没有指定第一列作为索引，但是由于列名数据少一个，自动推断第一列作为DataFrame的行索引

---

假如我们遇到异常数据，考虑下面的示例，我们需要使用skiprows来跳过第一行、第三行和第四行：

# %load pydata-book/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

pd.read_csv('pydata-book/examples/ex4.csv', skiprows=[0, 2, 3])

	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(这两个是pandas默认的)，我的经验有的时候处理时会有-99，-999等

# %load pydata-book/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

pd.read_csv('pydata-book/examples/ex5.csv') #这里自动标记了NA， 和空字符串

	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_values自定义的缺失值

pd.read_csv('pydata-book/examples/ex5.csv', na_values=['one'])  # 默认缺失值始终生效

	something	a	b	c	d	message
0	NaN	1	2	3.0	4	NaN
1	two	5	6	NaN	8	world
2	three	9	10	11.0	12	foo

通过字典来传递每列数据的缺失值：（个人感觉挺实用的，不同的列可能对缺失值的定义不同）

sentinels = {'message':['foo', 'NA'], 'something':'two'}
pd.read_csv('pydata-book/examples/ex5.csv', na_values=sentinels)

	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

• read_csv, read_table常用参数：

参数	描述
path	表明文件系统位置的字符串，URL或文件型对象
sep或delimiter	用于分割每行字段的字符或正则表达式
header	用作列名的行号，默认0（第一行），如果没有的话设置成None
index_col	用作结果行索引的列名或列号，可以是单一的名称/数字，也可以是一个分层索引
names	自定义列名，和header=None一起使用
skiprows	从文件开头处起，需要跳过的行数或行号列表
comment	在行尾处分割注释的字符
parse_dates	尝试将数据解析为datatime，默认为False。如果为True，将尝试解析所有的列。也可以指定列号或列名列表来进行解析。如果列表的元素是元组或列表，将会把多个列组合在一起进行解析（例如日期/时间将拆分为两列）
keep_date_col	如果连接到解析日期上，保留被连接的列，默认是False
converters	包含列名映射到函数的字典（例如{‘foo’:f}’会把函数f应用到’foo’列）
dayfrist	解析非明确的日期时，按照国际格式处理（例如7/6/2012$\to$June 7,2012），默认False
date_parser	用于解析日期的函数
nrows	从文件开头处读取的行数
iterator	返回一个TextParser对象，用于零散地读入文件
chunksize	用于迭代块的大小
skip_footer	忽略文件尾部的行数
verbose	打印各种解析器输出的信息，比如位于非数值列中的缺失值数量
encoding	Unicode文件编码（例如’utf-8’用于表示UTF-8编码的文本）
squeeze	如果解析数据只包含一列，返回一个Series
thousands	千分位分隔符（例如’,‘或’.’）

分块读入文本文件

当我们处理大型文件或找出正确的参数来正确处理大文件时，可能需要读入文件的一个小片段或者按小块遍历文件。

---

尝试大文件之前，我们先设置pandas的显示，使得显示更加紧凑：

pd.options.display.max_rows = 10 # 最多显示10行数据

result = pd.read_csv('pydata-book/examples/ex6.csv')
result

	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 × 5 columns

这里有10000行数据，如果我们只想读取一小部分，通过nrows指明：

pd.read_csv('pydata-book/examples/ex6.csv', nrows=5)

	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来指定每一块的行数：

chunker = pd.read_csv('pydata-book/examples/ex6.csv', chunksize=1000)
chunker

<pandas.io.parsers.TextFileReader at 0x230d3e22be0>

返回了TextFileReader对象，该对象允许根据chunksize遍历文件。例如我们可以遍历该文件并对’key’列聚合获得计数值：

tot = pd.Series([])
for piece in chunker:
    tot = tot.add(piece['key'].value_counts(), fill_value=0)
tot = tot.sort_values(ascending=False)
tot[:10]

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

TextFileReader还具有get_chunk方法，允许按照任意大小读取数据块：

将数据写入文本格式

数据可以导出分隔的形式，从CSV开始：

data = pd.read_csv('pydata-book/examples/ex5.csv')
data

	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

使用to_csv()方法，将数据导出为逗号分隔的文件：

data.to_csv('pydata-book/examples/out.csv')

# %load pydata-book/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

值得注意的是行索引也被导出了，有的时候是不需要的。可以通过index=None来设定。

当然其他的分隔符也是可以的：

import sys

# 控制台中打印文本结果, 方便显示不写如文本再查看文本了
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

缺失值默认用空字符串表示，也许我们需要特殊标注缺失值：

data.to_csv(sys.stdout, sep='|', 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

指定不写列名和行索引：

data.to_csv(sys.stdout, index=None, header=None) # None等效False

one,1,2,3.0,4,
two,5,6,,8,world
three,9,10,11.0,12,foo

也可以仅仅写入数据的子集，且按照自定义的顺序写入：

data.to_csv(sys.stdout, index=False, columns=['c', 'a', 'b'])

c,a,b
3.0,1,2
,5,6
11.0,9,10

Series对象也有to_csv方法：

dates = pd.date_range('1/1/2000', periods=7)
ts = pd.Series(np.arange(7), index=dates)
ts.to_csv(sys.stdout)

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数据

JSON(JavaScript Object Notation)已经成为Web浏览器和其他应用之间通过HTTP请求发送数据的标准格式。它是一种比CSV等表格文本更为自由的数据形式，示例如下：

obj = """
{"name": "Wes",
"places_lived": ["United States", "Spain", "Germany"],
"pet": null,
"siblings": [{"name": "Scott", "age": 30, "pets": ["Zeus", "Zuko"]},
             {"name": "Katie", "age": 38,
              "pets": ["Sixes", "Stache", "Cisco"]}]
}
"""

JSON非常接近有效的Python字典代码，除了null空值和其他一些细微的差别（例如不允许列表末尾的逗号）之外。

这里我们可以使用python的json库：

import json

result = json.loads(obj)
result

{'name': 'Wes',
 'places_lived': ['United States', 'Spain', 'Germany'],
 'pet': None,
 'siblings': [{'name': 'Scott', 'age': 30, 'pets': ['Zeus', 'Zuko']},
  {'name': 'Katie', 'age': 38, 'pets': ['Sixes', 'Stache', 'Cisco']}]}

dumps是loads的逆方法，可以将字典转成json字符串。

获取了字典对象后，我们可以使用字典或其子集构成DataFrame数据：

siblings = pd.DataFrame(result['siblings'], columns=['name', 'age'])
siblings

	name	age
0	Scott	30
1	Katie	38

使用pandas.read_json()读取JSON数据集按照指定的次序转化成Series或DataFrame。

# %load pydata-book/examples/example.json
[{"a": 1, "b": 2, "c": 3},
 {"a": 4, "b": 5, "c": 6},
 {"a": 7, "b": 8, "c": 9}]

pd.read_json('pydata-book/examples/example.json')

	a	b	c
0	1	2	3
1	4	5	6
2	7	8	9

XML和HTML:网络抓取

Python拥有很多对HTML和XML格式进行读取，写入数据的库，例如lxml,Beautiful Soup和html5lib。lxml读取比较快，但是其他库可以更好处理HTML和XML的异常文件。

尝试获取美国银行倒闭的数据，read_html需要使用到的附加库可以使用以下的安装命令：

• pip install lxml
• pip install beautifulsoup4 html5lib

tables = pd.read_html('pydata-book/examples/fdic_failed_bank_list.html')
len(tables)

1

failures = tables[0]
failures.head()

	Bank Name	City	ST	CERT	Acquiring Institution	Closing Date	Updated Date
0	Allied Bank	Mulberry	AR	91	Today's Bank	September 23, 2016	November 17, 2016
1	The Woodbury Banking Company	Woodbury	GA	11297	United Bank	August 19, 2016	November 17, 2016
2	First CornerStone Bank	King of Prussia	PA	35312	First-Citizens Bank & Trust Company	May 6, 2016	September 6, 2016
3	Trust Company Bank	Memphis	TN	9956	The Bank of Fayette County	April 29, 2016	September 6, 2016
4	North Milwaukee State Bank	Milwaukee	WI	20364	First-Citizens Bank & Trust Company	March 11, 2016	June 16, 2016

计算每年银行倒闭的数量：

close_timestamps = pd.to_datetime(failures['Closing Date'])

close_timestamps.dt.year.value_counts()

2010    157
2009    140
2011     92
2012     51
2008     25
2013     24
2014     18
2002     11
2015      8
2016      5
2004      4
2001      4
2007      3
2003      3
2000      2
Name: Closing Date, dtype: int64

使用lxml.objectify解析XML

XML(eXtensible Markup Language)是另一种常用的结构化数据格式，它使用元数据支持分层，嵌套数据。

---

之前，展示了pandas.read_html函数，使用lxml或Beautiful Soup从HTML中解析数据。XML和HTML结构类似，但是XML更通用，下面展示使用lxml解析XML数据。

---

下面的数据来自纽约大都会交通局发布的公交，火车服务的数据集。文件记录了按月的数据,下面是数据样例，2011年12月的数据：

<INDICATOR>
  <INDICATOR_SEQ>373889</INDICATOR_SEQ>
  <PARENT_SEQ></PARENT_SEQ>
  <AGENCY_NAME>Metro-North Railroad</AGENCY_NAME>
  <INDICATOR_NAME>Escalator Availability</INDICATOR_NAME>
  <DESCRIPTION>Percent of the time that escalators are operational systemwide. The availability rate is based on physical observations performed the morning of regular business days only. This is a new indicator the agency began reporting in 2009.</DESCRIPTION>
  <PERIOD_YEAR>2011</PERIOD_YEAR>
  <PERIOD_MONTH>12</PERIOD_MONTH>
  <CATEGORY>Service Indicators</CATEGORY>
  <FREQUENCY>M</FREQUENCY>
  <DESIRED_CHANGE>U</DESIRED_CHANGE>
  <INDICATOR_UNIT>%</INDICATOR_UNIT>
  <DECIMAL_PLACES>1</DECIMAL_PLACES>
  <YTD_TARGET>97.00</YTD_TARGET>
  <YTD_ACTUAL></YTD_ACTUAL>
  <MONTHLY_TARGET>97.00</MONTHLY_TARGET>
  <MONTHLY_ACTUAL></MONTHLY_ACTUAL>
</INDICATOR>

from lxml import objectify

path = r'pydata-book\datasets\mta_perf\Performance_MNR.xml'
parsed = objectify.parse(open(path))
root = parsed.getroot()

root.INDICATOR返回一个生成器，可以产生每一个<INDICATOR>XML元素。对于每条记录，我们可以将标签名称的字典（如YTD_ACTUAL）填充为数据值（不包括几个标签）：

data = []
skip_fields = ['PARENT_SEQ', 'INDICATOR_SEQ',
               'DESIRED_CHANGE', 'DECIMAL_PLACES']
for elt in root.INDICATOR:
    el_data = {}
    for child in elt.getchildren():
        if child.tag in skip_fields:
            continue
        el_data[child.tag] = child.pyval
    data.append(el_data)

# 转化成DataFrame
perf = pd.DataFrame(data)
perf.head()

	AGENCY_NAME	CATEGORY	DESCRIPTION	FREQUENCY	INDICATOR_NAME	INDICATOR_UNIT	MONTHLY_ACTUAL	MONTHLY_TARGET	PERIOD_MONTH	PERIOD_YEAR	YTD_ACTUAL	YTD_TARGET
0	Metro-North Railroad	Service Indicators	Percent of commuter trains that arrive at thei...	M	On-Time Performance (West of Hudson)	%	96.9	95	1	2008	96.9	95
1	Metro-North Railroad	Service Indicators	Percent of commuter trains that arrive at thei...	M	On-Time Performance (West of Hudson)	%	95	95	2	2008	96	95
2	Metro-North Railroad	Service Indicators	Percent of commuter trains that arrive at thei...	M	On-Time Performance (West of Hudson)	%	96.9	95	3	2008	96.3	95
3	Metro-North Railroad	Service Indicators	Percent of commuter trains that arrive at thei...	M	On-Time Performance (West of Hudson)	%	98.3	95	4	2008	96.8	95
4	Metro-North Railroad	Service Indicators	Percent of commuter trains that arrive at thei...	M	On-Time Performance (West of Hudson)	%	95.8	95	5	2008	96.6	95

xml很多时候用以记录多字段数据在数据库导入导出中都有使用，所以作为一项标准的格式化数据还是有必要了解的。

二进制格式数据

这里介绍python原生的序列化数据pickle，是数据使用比较高效的一种方式。

frame = pd.read_csv(r'pydata-book\examples\ex1.csv')
frame

	a	b	c	d	message
0	1	2	3	4	hello
1	5	6	7	8	world
2	9	10	11	12	foo

将数据帧存储到pickle序列化的二进制文件：

frame.to_pickle(r'pydata-book\examples\frame_pickle')

获取存储的pickle文件：

pd.read_pickle(r'pydata-book\examples\frame_pickle')

	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格式之外还有HDF5，MessagePack二进制格式。

使用HDF5格式

HDF5是备受好评的文件格式，用于存储大量的科学数组数据。以C库的形式提供。Java、Julia、MATLAB及python都拥有相应的接口。HDF5支持多种压缩模式的即时压缩，使得重复模式的数据可以更高效地存储。

---

尽管PyTables和h5py等库提供了直接访问HDF5文件，但是pandas提供了更高效的接口。HDFStore类像字典一样工作并出路低级别的细节：

frame = pd.DataFrame({'a':np.random.randn(100)})
store = pd.HDFStore('mydata.h5')
store['obj1'] = frame
store['obj1_col'] = frame['a']
store

<class 'pandas.io.pytables.HDFStore'>
File path: mydata.h5

store['obj1']

	a
0	-1.017535
1	1.344019
2	0.628052
3	-0.322921
4	1.241260
...	...
95	0.198721
96	-1.684693
97	-1.484701
98	1.805135
99	-1.059568

100 rows × 1 columns

HDFStore支持两种存储模式，fixed, table。后者速度慢，但是支持特殊查询操作：

store.put('obj2', frame, format='table')
store

<class 'pandas.io.pytables.HDFStore'>
File path: mydata.h5

print(store.info())

<class 'pandas.io.pytables.HDFStore'>
File path: mydata.h5
/obj1                frame        (shape->[100,1])                                       
/obj1_col            series       (shape->[100])                                         
/obj2                frame_table  (typ->appendable,nrows->100,ncols->1,indexers->[index])

store.select('obj2', where=['index>=10 and index<=15']) # 类似数据库的查询操作

	a
10	0.809328
11	0.067670
12	-0.274139
13	0.350302
14	-1.327402
15	0.219045

store.close() # 关闭交互

可以将frame存储HDF5，或载入：

frame.to_hdf('mydata.h5', 'obj3', format='table')

pd.read_hdf('mydata.h5', 'obj3', where=['index<5'])

	a
0	-1.017535
1	1.344019
2	0.628052
3	-0.322921
4	1.241260

store = pd.HDFStore('mydata.h5')
print(store.info())
store.close()

<class 'pandas.io.pytables.HDFStore'>
File path: mydata.h5
/obj1                frame        (shape->[100,1])                                       
/obj1_col            series       (shape->[100])                                         
/obj2                frame_table  (typ->appendable,nrows->100,ncols->1,indexers->[index])
/obj3                frame_table  (typ->appendable,nrows->100,ncols->1,indexers->[index])

说明HDF5操作是动态的，书本上讲解的时候没有侧重这点导致很难理解，希望大家使用的时候可以注意，使用得当的话可以当作个人"数据库"来使用。

---

HDF5并不是数据库，多个使用者持续写入会损坏文件，个人使用可以试试一次写入多次读取，任意时间将数据添加到文件中。

读取Mircrosoft Excel文件

pandas支持通过ExcelFile类或read_excel()函数来读取Excel文件。内部依赖xlrd和openpyxl来读取XLS和XLSX文件。

使用ExcelFile，将xls或xlsx路径转化成实例：

xlsx = pd.ExcelFile(r'pydata-book\examples\ex1.xlsx')

pd.read_excel(xlsx, sheet_name='Sheet1')

	a	b	c	d	message
0	1	2	3	4	hello
1	5	6	7	8	world
2	9	10	11	12	foo

read_excel也可以直接使用路径读取：

frame = pd.read_excel(r'pydata-book\examples\ex1.xlsx') # 默认读取第一个sheet
frame

	a	b	c	d	message
0	1	2	3	4	hello
1	5	6	7	8	world
2	9	10	11	12	foo

存储数据：

writer = pd.ExcelWriter(r'pydata-book\examples\ex2.xlsx')
frame.to_excel(writer)
writer.save()

同样的写入数据的时候也可以使用to_excel()函数来避免直接使用ExcelWriter

frame.to_excel(r'pydata-book\examples\ex2.xlsx')

• read_excel()及to_excel()方法可以在官网中获取详细的说明，read_csv里介绍的多种参数同样在excel中也有体现。

与Web API交互

和web的交互体现了爬虫的思想，基础使用的是requests包。

---

获取github上的最新30条关于pandas的问题：

import requests

url = 'https://api.github.com/repos/pandas-dev/pandas/issues'
resp = requests.get(url)

resp.status_code  # 200说明链接成功

200

data = resp.json() # 获取response的json字典数据
data[0]['title']

'BUG: assignment to multiple columns when some column do not exist'

issues = pd.DataFrame(data, columns=['number', 'title', 
                                     'labels', 'state'])  # 具体的字段细节可以在网站上获取
issues

	number	title	labels	state
0	29334	BUG: assignment to multiple columns when some ...	[]	open
1	29333	CLN: type annotations	[]	open
2	29332	maybe_upcast_putmask: require other to be a sc...	[]	open
3	29331	REF: implement maybe_promote_scalar	[]	open
4	29330	conda env create --file environment.yaml Fails...	[]	open
...	...	...	...	...
25	29288	Fix missing tick labels on twinned axes.	[{'id': 2413328, 'node_id': 'MDU6TGFiZWwyNDEzM...	open
26	29284	combination of groupby & nlargest gives differ...	[{'id': 233160, 'node_id': 'MDU6TGFiZWwyMzMxNj...	open
27	29282	Inconsistency of copy and selection methods of...	[]	open
28	29279	DataFrame.replace: TypeError: “Cannot compare ...	[{'id': 307649777, 'node_id': 'MDU6TGFiZWwzMDc...	open
29	29270	Reduce Benchmark Times of stat_ops, particular...	[{'id': 732775912, 'node_id': 'MDU6TGFiZWw3MzI...	open

30 rows × 4 columns

与数据库交互

数据库在现在业务场景中已经很常见了，数据库的操作本身就是一个很大的命题。这边利用python自带的数据库SQLite。

import sqlite3

# 创建数据库，建立test表，abcd字段
query = '''
CREATE TABLE test
(a VARCHAR(20), b VARCHAR(20), c REAL, d INTEGER);
'''

con = sqlite3.connect('mydata.sqlite')

con.execute(query) #执行

<sqlite3.Cursor at 0x2a73397a650>

con.commit()#提交

# 创建数据和插入指令
data = [('Atlanta', 'Georgia', 1.25, 6),
        ('Tallahassee', 'Florida', 2.6, 3),
        ('Sacramento', 'California', 1.7, 5)]
stmt = "INSERT INTO test VALUES(?,?,?,?)"

# 执行并提交
con.executemany(stmt, data)
con.commit()

目前我们已经有了数据库表并已经插入了基础数据，下面开始查询数据：

---

增删改查是数据库执行的基础

cursor = con.execute("SELECT * from test")
rows = cursor.fetchall()
rows

[('Atlanta', 'Georgia', 1.25, 6),
 ('Tallahassee', 'Florida', 2.6, 3),
 ('Sacramento', 'California', 1.7, 5)]

获取的rows之后自己创建数据帧：

cursor.description # 获取游标的表述

(('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))

pd.DataFrame(rows, columns=[x[0] for x in cursor.description])

	a	b	c	d
0	Atlanta	Georgia	1.25	6
1	Tallahassee	Florida	2.60	3
2	Sacramento	California	1.70	5

这样的操作是很麻烦，而且重点在数据库的交互上，数据库的交互可以交给pandas来完成，我们可以使用sql语句和数据库的链接来完成数据表查询：

import sqlalchemy as sqla

db = sqla.create_engine('sqlite:///mydata.sqlite')

pd.read_sql("SELECT *FROM test", db) # 使用第三方数据库工具

	a	b	c	d
0	Atlanta	Georgia	1.25	6
1	Tallahassee	Florida	2.60	3
2	Sacramento	California	1.70	5

pd.read_sql("SELECT *FROM test", con) # 使用链接，sql指令读取数据库

	a	b	c	d
0	Atlanta	Georgia	1.25	6
1	Tallahassee	Florida	2.60	3
2	Sacramento	California	1.70	5

con.close() # 使用连接获取数据，记得关闭
'''短链接的操作可以借助上下文管理实现（with）'''

其他数据库在操作的细节上会有些许的变化，但是整体的思路是一致的，常用sqlserver,mysql同样支持。