Home | 简体中文 | 繁体中文 | 杂文 | 知乎专栏 | 51CTO学院 | CSDN程序员研修院 | Github | OSChina 博客 | 腾讯云社区 | 阿里云栖社区 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
知乎专栏多维度架构

10.2. 数据输入与输出

我们都知道 Pandas 是做数据分析的,Pandas 在做数据分析之前需要加载数据,加载数据的方法有很多种,数据来源渠道也很多。例如数据可以从 HTML页面中的表格,Excel,JSON,CSV以及关系型数据库等等。

10.2.1. HTML 表格处理

工作中,我们常常需要提取HTML网页中的表格数据,多少会用到爬虫技术。

例如使用 requests 下载网页,然后使用HTML萃取工具,将HTML页面内部table表格中的数据提出去来。这种方法比较复杂,今天介绍的是 Pandas 读取网页中的表格,难度几乎是傻瓜级别的。

在不使用爬虫的情况下,这种方式是最佳选择。

10.2.1.1. 安装依赖包

		
pip install lxml		
		
		

10.2.1.2. read_html 参数详解

read_html() 可以萃取 HTML table 标签中的数据。

pandas.read_html(io, match='.+', flavor=None, header=None, index_col=None, skiprows=None, attrs=None, parse_dates=False, tupleize_cols=None, thousands=', ', encoding=None, decimal='.', converters=None, na_values=None, keep_default_na=True, displayed_only=True)

详细参数

		
io: 		str, path object 或 file-like objectURL,file-like对象或包含HTML的原始字符串。请注意,lxml仅接受http,ftp和文件url协议。如果您的网址以'https'您可以尝试删除's'。
match: 		str 或 compiled regular expression, 可选参数将返回包含与该正则表达式或字符串匹配的文本的表集。除非HTML非常简单,否则您可能需要在此处传递非空字符串。默认为“。+”(匹配任何非空字符串)。默认值将返回页面上包含的所有表。此值转换为正则表达式,以便Beautiful Soup和lxml之间具有一致的行为。
flavor: 	str 或 None要使用的解析引擎。 ‘bs4’和‘html5lib’彼此同义,它们都是为了向后兼容。默认值None尝试使用lxml解析,如果失败,它会重新出现bs4+html5lib。
header: 	int 或 list-like 或 None, 可选参数该行(或MultiIndex)用于创建列标题。
index_col: int 或 list-like 或 None, 可选参数用于创建索引的列(或列列表)。
skiprows: 	int 或 list-like 或 slice 或 None, 可选参数解析列整数后要跳过的行数。从0开始。如果给出整数序列或切片,将跳过该序列索引的行。请注意,单个元素序列的意思是“跳过第n行”,而整数的意思是“跳过n行”。
attrs: 		dict 或 None, 可选参数这是属性的词典,您可以传递该属性以用于标识HTML中的表。在传递给lxml或Beautiful Soup之前,不会检查它们的有效性。但是,这些属性必须是有效的HTML表属性才能正常工作。例如, attrs = {'id': 'table'} 是有效的属性字典,因为‘id’ HTML标记属性是任何HTML标记的有效HTML属性,这个文件。 attrs = {'asdf': 'table'} 不是有效的属性字典,因为‘asdf’即使是有效的XML属性,也不是有效的HTML属性。可以找到有效的HTML 4.01表属性这里。可以找到HTML 5规范的工作草案这里。它包含有关现代Web表属性的最新信息。
parse_dates: bool, 可选参数参考read_csv()更多细节。
thousands: 	str, 可选参数用来解析成千上万个分隔符。默认为','。
encoding: 	str 或 None, 可选参数用于解码网页的编码。默认为NoneNone保留先前的编码行为,这取决于基础解析器库(例如,解析器库将尝试使用文档提供的编码)。
decimal: 	str, 默认为 ‘.’可以识别为小数点的字符(例如,对于欧洲数据,请使用“,”)。
converters: dict, 默认为 None用于在某些列中转换值的函数的字典。键可以是整数或列标签,值是采用一个输入参数,单元格(而非列)内容并返回转换后内容的函数。
na_values: 	iterable, 默认为 None自定义NA值。
keep_default_na: bool, 默认为 True如果指定了na_values并且keep_default_na为False,则默认的NaN值将被覆盖,否则将附加它们。
displayed_only: bool, 默认为 True是否应解析具有“display:none”的元素。
		
		

10.2.1.3. 从文本变量中提取数据

		
import pandas as pd

html = """
<table border="1">
  <tr>
    <th>Month</th>
    <th>Savings</th>
  </tr>
  <tr>
    <td>January</td>
    <td>$100</td>
  </tr>
</table>
"""
data = pd.read_html(html)[0]

print(data)
		
		

提取多个表格

		
import pandas as pd

html = """
<table border="1">
  <tr><th>月份</th><th>节约</th></tr>
  <tr><td>一月</td><td>100</td></tr>
</table>

<table border="1">
  <tr><th>月份</th><th>节约</th></tr>
  <tr><td>二月</td><td>200</td></tr>
</table>
"""
dfs = pd.read_html(html)
print("发现HTML表格数量:%s" % len(dfs))
for data in dfs:
    print(data)
		
		
		

10.2.1.4. 从文件获取表格数据

准备 table.html 文件

		
<table border="1">
    <thead>
        <tr>
            <th>Month</th>
            <th>Savings</th>
        </tr>
    </thead>

    <tfoot>
        <tr>
            <td>Sum</td>
            <td>$180</td>
        </tr>
    </tfoot>

    <tbody>
        <tr>
            <td>January</td>
            <td>$100</td>
        </tr>
        <tr>
            <td>February</td>
            <td>$80</td>
        </tr>
    </tbody>
</table>		
		
		
		
import pandas as pd

# 错误方法
html = open('table.html', 'r').read()
data = pd.read_html(html)[0]
print(data)

# 正确方法
data = pd.read_html('table.html')[0]
print(data)
		
		
		

10.2.1.5. 从网址获取表格数据

获取页面中所有table的数据

		
import pandas as pd

url = "http://www.stats.gov.cn/tjsj/zxfb/202103/t20210330_1815829.html"
data = pd.read_html(url)

print(data)
		
		

10.2.1.6. 处理多个表格

多个 table 会返回一个数组,通过数组下标可以读取指定表格。

		
import pandas as pd

url = "http://www.stats.gov.cn/tjsj/zxfb/202103/t20210330_1815829.html"
data = pd.read_html(url)[1]

print(data)		
		
		

10.2.1.7. 获取指定属性的表格

通过 id 属性,精确提取指定表格中的数据。

		
import pandas as pd

html = """
<table id="first">
  <tr><th>姓名</th><th>性别</th></tr>
  <tr><td>张三</td><td>男</td></tr>
</table>
<table id="second">
  <tr><th>姓名</th><th>性别</th></tr>
  <tr><td>李四</td><td>男</td></tr>
</table>
"""
data = pd.read_html(html, attrs={'id': 'second'})
print(data[0])
		
		
		

获取 html table 标签 id 属性为 oTable 的表格数据

		
import pandas as pd

url = "http://fund.eastmoney.com/fund.html"
data = pd.read_html(url,attrs = {'id': 'oTable'})

print(data)		
		
		

10.2.1.8. 结合 Xpath 使用

HTML 属性,只有 id 是唯一的,其他属性都允许重复使用,例如 class,所以使用 class 会选中多张 HTML 表格。

		
data = pd.read_html(text, attrs={'class': 'netkiller'})
print(len(data))		
		
		

我的做法是使用 xpath 精准匹配,为什么部用数组下标了,因为页面的变化很可能造成数字顺序错乱。

		
import pandas as pd
from lxml import etree

text = """
<table class="netkiller">
  <tr><th>姓名</th><th>性别</th></tr>
  <tr><td>张三</td><td>男</td></tr>
</table>
<table class="netkiller">
  <tr><th>姓名</th><th>性别</th></tr>
  <tr><td>李四</td><td>男</td></tr>
</table>
"""

html = etree.HTML(text)
result = html.xpath('//table[@class="netkiller"][last()]')
text = etree.tostring(result[0], encoding='utf-8').decode('utf-8')

data = pd.read_html(text)
print(data[0])
		
		

10.2.1.9. 指定编码

目前仍有部分中文网站使用 GB2312和GBK编码,遇到输出乱码的情况,使用encoding指定编码即可。

建议:中文建议使用 GB18030,GB18038 包含了中日韩三国字符集,也就是说GB18030 是涵盖 GB2312和GBK的。

		
import pandas as pd

url = "http://www.tianqihoubao.com/weather/top/shenzhen.html"
data = pd.read_html(url, encoding="GB18030")
print(data[0])
		
		
		

10.2.2. Excel 处理

本节主要介绍和excel的交互。

10.2.2.1. 安装依赖库

		
neo@MacBook-Pro-Neo ~ % pip install openpyxl		
		
		

10.2.2.2. 创建 Excel 文档

			
from pandas import DataFrame

data = {
    'name': ['Neo', 'Tom', 'Jerry'],
    'age': [11, 12, 13],
    'gender': ['Y', 'Y', 'N']
}
df = DataFrame(data)
df.to_excel('netkiller.xlsx')			
			
		

写入指定工作表

			
from pandas import DataFrame

data = {
    '姓名': ['Neo', 'Tom', 'Jerry'],
    '年龄': [11, 12, 13],
    '性别': ['Y', 'Y', 'N']
}
df = DataFrame(data)
df.to_excel('neo.xlsx', sheet_name='我的工作表')			
			
		

设置 sheet_name 参数将数据写入指定的工作表

startrow=2 从第几行开始写入, index=False 关闭序号列, header=False 不写入表头

			
from pandas import DataFrame

data = {
    '姓名': ['Neo', 'Tom', 'Jerry'],
    '年龄': [11, 12, 13],
    '性别': ['Y', 'Y', 'N']
}
df = DataFrame(data)
df.to_excel('neo.xlsx', sheet_name='我的工作表',
            startrow=2, index=False, header=False)
			
			
		

写入多个工作表

			
import pandas as pd

data = pd.DataFrame(
    {
        '姓名': ['Neo', 'Tom', 'Jerry'],
        '年龄': [11, 12, 13],
        '性别': ['Y', 'Y', 'N']
    }
)
excel = pd.ExcelWriter("sheet.xlsx")
data.to_excel(excel, sheet_name="Sheet1", index=False)
data.to_excel(excel, sheet_name="Sheet2", index=False)
data.to_excel(excel, sheet_name="Sheet3", index=False)
excel.save()
excel.close()			
			
		

向Excel中追加工作表

		
# 追加工作表,首先创建 Excel 文件
with pd.ExcelWriter("sheet1.xlsx") as writer:
    data.to_excel(writer, sheet_name="Sheet1", index=False)
    data.to_excel(writer, sheet_name="Sheet2", index=False)
    data.to_excel(writer, sheet_name="Sheet3", index=False)
    	
    	
append = pd.DataFrame(
    {
        '姓名': ['Neo', 'Tom', 'Jerry'],
        '年龄': [11, 12, 13],
        '性别': ['Y', 'Y', 'N']
    }
)
# 默认ExcelWriter是覆盖模式,需要使用 mode='a' 参数,才能Excel文件中增加工作表
with pd.ExcelWriter("sheet1.xlsx", mode='a', engine='openpyxl') as writer:
    append.to_excel(writer, sheet_name="Sheet4", index=False)
    append.to_excel(writer, sheet_name="Sheet5", index=False)
		
		

		
		

10.2.2.3. 读取 Excel 文件

read_excel() 参数详解:

			
加载函数为read_excel(),其具体参数如下。

read_excel(io, sheetname=0, header=0, skiprows=None, skip_footer=0, index_col=None,names=None, parse_cols=None, parse_dates=False,date_parser=None,na_values=None,thousands=None, convert_float=True, has_index_names=None, converters=None,dtype=None, true_values=None, false_values=None, engine=None, squeeze=False, **kwds)

常用参数解析:
io : string, path object ; excel 路径。
sheetname : string, int, mixed list of strings/ints, or None, default 0 返回多表使用sheetname=[0,1],若sheetname=None是返回全表 
header : int, list of ints, default 0 指定列表头,默认0,即取第一行,数据没有表头设定
skiprows : list-like,Rows to skip at the beginning,跳过指定行数的数据
skip_footer : int,default 0, 省略从尾部数的行数据,默认是 0
index_col : int, list of ints, default None 指定列为索引列
names : array-like, default None, 指定列的名字。		
			
		
		
#!/usr/bin/python3
# -*- coding: UTF-8 -*-
import pandas as pd
# 默认读取第一个工作表
df = pd.read_excel("团购2021.xlsx")
data = df.head()  # 默认读取前5行的数据
print("当前默认工作表:\n{0}".format(data))  # 格式化输出
		
		
10.2.2.3.1. 读取指定列
			
import pandas as pd

df = pd.read_excel("../netkiller.xlsx")
print(df.head())
print("=" * 50)
# 读取B~E列的数据
df = pd.read_excel("../netkiller.xlsx", index_col=False, usecols="B:E")
print(df)
			
			

10.2.2.4. 工作表

10.2.2.4.1. 显示所有工作表

获取Excel文件中的工作表

			
import pandas as pd
xls = pd.ExcelFile("团购2021.xlsx")
sheet_names = xls.sheet_names
print(sheet_names)
			
			

打开默认工作表

			
import pandas as pd			
df = pd.read_excel("团购2021.xlsx", None)
print(df.keys())
for k,v in df.items():
    print(k)
			
			
10.2.2.4.2. 打开工作表

打开指定工作表

			
#!/usr/bin/python3
# -*- coding: UTF-8 -*-
import pandas as pd

file_path = r'团购.xlsx'
df = pd.read_excel(file_path, sheet_name="3月2日", header=1) 
			
			

打开 Excel 并返回指定列数据

		
import pandas as pd
sheet = pd.read_excel(io="new.xlsx", usecols=['name'])
print(sheet)		
		
			

打开多个工作表

			
import pandas as pd
sheet = pd.read_excel(io="测试数据.xlsx", sheet_name=[0, 1])
print(sheet[0])
			
			

例 10.1. Pandas 打开工作表的四种方法

				
import pandas as pd

# 打开一张工作表
df = pd.read_excel("sheet1.xlsx", sheet_name="Sheet1")
print(df)

# 指定并打开多张工作表
df = pd.read_excel("sheet1.xlsx", sheet_name=["Sheet1", "Sheet2", "Sheet3"])
for sheet, data in df.items():
    print("=" * 20, sheet, "=" * 20)
    print(data)

# 使用数字索引打开多张工作表
df = pd.read_excel("sheet1.xlsx", sheet_name=[0, 1, 2])
for sheet, data in df.items():
    print("=" * 20, sheet, "=" * 20)
    print(data)

# 使用 ExcelFile 对象打开多个工作表
sheet = {}
with pd.ExcelFile("sheet1.xlsx") as xls:
    sheet["Sheet1"] = pd.read_excel(
        xls, "Sheet1", index_col=None, na_values=["NA"])
    sheet["Sheet2"] = pd.read_excel(xls, "Sheet2", index_col=1)

for sheet, data in sheet.items():
    print("=" * 20, sheet, "=" * 20)
    print(data)
				
				

10.2.2.4.3. 合并工作表
			
import pandas as pd
sheet = pd.read_excel("测试.xlsx", sheet_name=[1, 0])
st = pd.concat(sheet,ignore_index = True)
			
			

			
import pandas as pd
sheet = pd.read_excel("sheet1.xlsx", sheet_name=[1, 0])
df = pd.concat(sheet, ignore_index=True)
print(df)
df.to_excel('merge.xlsx', index=False)
			
			
10.2.2.4.4. 打开工作表,指定返回列数据
			
import pandas as pd

df = pd.read_excel("../netkiller.xlsx", index_col=False, usecols="B,C:E")
print("=" * 20, "读取B,C:E列的数据", "=" * 20)
print(df.head())

# 读取B~E列的数据
df = pd.read_excel("../netkiller.xlsx", index_col=False, usecols="B:E")
print("=" * 20, "读取B~E列的数据", "=" * 20)
print(df)		

df = pd.read_excel("netkiller.xlsx", index_col=False, usecols=[1, 2, 3])
print("=" * 20, "读取[1, 2, 3]列的数据", "=" * 20)
print(df)	
			
			
10.2.2.4.5. 跳过不需要的数据
			
import pandas as pd
from pandas import DataFrame
xlsx = 'skip.xlsx'
data = {
    '姓名': ['张三', '李四', '王五', '牛七', '马八', '赵九'],
    '年龄': [11, 12, 13, 14, 15, 17],
    '性别': ['Y', 'Y', 'N', 'Y', 'N', 'Y']
}
df = DataFrame(data)
df.to_excel(xlsx, index=True, index_label='序号')

df = pd.read_excel(xlsx, skiprows=[1, 3, 4])
print("跳过数据 [1, 3, 4]:\n{0}".format(df))

df = pd.read_excel(xlsx, skiprows=3)
print("跳过前3条数据:\n{0}".format(df))

df = pd.read_excel(xlsx, skipfooter=2)
print("从尾部剪掉1条数据:\n{0}".format(df))

			
			

			
跳过数据 [1, 3, 4]:
   序号  姓名  年龄 性别
0   1  李四  12  Y
1   4  马八  15  N
2   5  赵九  17  Y
跳过 top10 数据:
   2  王五  13  N
0  3  牛七  14  Y
1  4  马八  15  N
2  5  赵九  17  Y
从尾部剪掉1条数据:
   序号  姓名  年龄 性别
0   0  张三  11  Y
1   1  李四  12  Y
2   2  王五  13  N
3   3  牛七  14  Y			
			
			

10.2.2.5. 数据操作

10.2.2.5.1. 打印头部/尾部数据

仅查看数据示例时常用

			
print(df.head())
print(df.tail())
			
			
10.2.2.5.2. 打印列标题
			
print(sheet.columns)			
			
			
10.2.2.5.3. 打印行
			
print(sheet.index)			
			
			
10.2.2.5.4. 描述数据
			
print(sheet.describe())			
			
			
10.2.2.5.5. 修改 Excel 数据
		
import pandas as pd
from pandas import DataFrame

file_path = r'new.xlsx'
df = pd.read_excel(file_path)

df['gender'][df['gender'] == 'N'] = 'Female'
df['gender'][df['gender'] == 'Y'] = 'Male'

print(df)

DataFrame(df).to_excel(
    file_path, sheet_name='Sheet1', index=False, header=True)
		
		
			
10.2.2.5.6. 新增行/列
		
import pandas as pd
from pandas import DataFrame

file_path = r'new.xlsx'
df = pd.read_excel(file_path)

# 新增一列
df['ctime'] = None

# 新增一行
df.loc[4] = [3, 'Alice', 20, 'Female',  '2021-5-11']

print(df)

DataFrame(df).to_excel(
    file_path, sheet_name='Sheet2', index=False, header=True)
		
		
			
10.2.2.5.7. 数据筛选
			
import pandas as pd
sheet = pd.read_excel("工资表.xlsx", usecols=['工资'])
high_salary = sheet[sheet['工资'] > 10000]
middle_salary = sheet[(sheet['工资'] >= 8000) & (sheet['工资'] <=10000)]
low_salary = sheet[sheet['工资'] < 8000]
			
			
			
high_salary = sheet[(sheet['工资'] >= 8000) & (sheet['工资'] <=10000)][['姓名','工资']]			
			
			
10.2.2.5.8. 数据排序

10.2.2.6. Excel 设置项

		
import numpy as np
import pandas as pd
pd.set_option('max_columns', 10)
pd.set_option('max_rows', 20)
# 禁用科学计数法
pd.set_option('display.float_format', lambda x: '%.2f' % x) 
		
		

10.2.3. Pandas 读写 CSV 文件

10.2.3.1. 将数据保存到CSV文件

		
import pandas as pd

months=[202001,202002,202003,202004,202005,202006,202007,202008,202009,202010,202011,202012]

for month in months:
    print(month)
    weather = pd.read_html(f'http://www.tianqihoubao.com/lishi/wanzhou/month/{month}.html', encoding='gb18030', header=0)[0]
    print(weather)
    weather.to_csv(f'/tmp/{month}天气预报数据.csv', mode='a+', index=False, header=False)		
		
		

10.2.4. Pandas SQL

10.2.4.1. 建立数据库链接

10.2.4.1.1. sqlite3
			
import pandas as pd
from pandas import DataFrame
import sqlite3
con = sqlite3.connect(":memory:")

data = DataFrame({
    '姓名': ['张三', '李四', '王五'],
    '年龄': [11, 12, 13],
    '性别': ['Y', 'Y', 'N']
})

data.to_sql("data", con)
table = pd.read_sql_query("SELECT * FROM data", con)
print(table)			
			
			
10.2.4.1.2. SQLAlchemy

安装依赖库

		
neo@MacBook-Pro-Neo ~ % pip install sqlalchemy		
		
			

创建链接引擎参考实例

		
from sqlalchemy import create_engine

engine = create_engine("postgresql://scott:tiger@localhost:5432/mydatabase")

engine = create_engine("mysql+mysqldb://scott:tiger@localhost/foo")

engine = create_engine("oracle://scott:tiger@127.0.0.1:1521/sidname")

engine = create_engine("mssql+pyodbc://mydsn")

# sqlite://<nohostname>/<path>
# where <path> is relative:
engine = create_engine("sqlite:///foo.db")

# or absolute, starting with a slash:
engine = create_engine("sqlite:////absolute/path/to/foo.db")		
		
			

10.2.4.2. DataFrame数据写入到数据库

10.2.4.2.1. 分批写入数据

DataFrame 结果集很大,写入时间过程很长,容易产生错误,这时可以使用 chunksize 切割数据,分批写入。

		
from sqlalchemy import create_engine
import pandas as pd
from pandas import DataFrame

engine = create_engine("sqlite:///:memory:")

data = DataFrame({
    '姓名': ['张三', '李四', '王五'],
    '年龄': [11, 12, 13],
    '性别': ['Y', 'Y', 'N']
})

data.to_sql("data", engine, chunksize=1000)

table = pd.read_sql_query("SELECT * FROM data", engine)
print(table)