初识Pandas系列三:数据读写(上) 中介绍了Pandas如何读取CSV、TXT和JSON,本篇继续讲解2个常用的数据格式,即Excel和Sql。

Excel的读写

read_excel

常用的Excel表格有Excel 2003(.xls)和Excel 2007+ (.xlsx)版本,read_excel()使用Python的xlrd和openpyxl模块来读取数据,其中xlrd支持.xls.xlsx,openpyxl只支持.xlsx,使用的基本语法和CSV类似。

需要安装xlrd

pip install xlrd

基本参数

  • sheet_name :字符串,int,字符串/整数的混合列表或None,默认为0。

表名用字符串表示,索引表位置用整数表示;字符串/整数列表用于请求多个表;没有设置时将会自动获取所有表;

Defaults : 第一页作为数据文件 1 :第二页作为数据文件 “Sheet1” :第一页作为数据文件 [0,1,“SEET5”] :第一、第二和第五作为作为数据文件 None :所有表为作为数据文件

  • header : 整型,或者整型列表,默认为0
  • 用于解析的DataFrame的列标签。如果一个整数列表被传递,那么这些行位置将被合并成一个索引。如果没有标题,请使用None。
  • skiprows :类列表,开始时跳过的行
  • skip_footer :整型, 默认为 0,结束时的行
  • index_col : 整型, 整型列表, 默认 None
  • 用作DataFrame的行标签。 如果传递一个列表,这些列将被组合成一个MultiIndex。 如果使用usecols选择数据子集,则index_col基于该子集。
  • names :类似数组,默认无,要使用的列名列表。
  • converters :字典 , 默认 None,在某些列中转换值的函数的命令。键可以是整数或列标签,值是接受一个输入参数的函数,Excel单元格内容,并返回转换后的内容。
  • dtype : 类型名称或dict的列,其他类型默认为None用于数据或列的数据类型。 如果指定了转换器,则将应用INSTEAD进行dtype转换。
  • true_values :列表, 默认 None,值视为Ture
  • false_values : 列表, 默认 None,值视为False
  • usecols :*整型或者列表, 默认为 None

如果为None,则解析所有列, 如果为int,则某列将被解析 如果为ints,则列表要解析的列号列表将使用 如果为字符串表示逗号分隔的Excel列字母和列范围列表(例如“A:E”或“A,C,E:F”)。 范围包括边界两个。

  • squeeze :** 布尔, 默认为 False,如果解析的数据只包含一列,则返回一个Series
  • na_values :标量,字符串,列表类,或字典,默认None,某些字符串可以识别为 NA / NaN。 默认情况下,以下值将被解释为NaN:

”,’#N / A’,’#N / AN / A’,’#NA’,’-1.#IND’,’1.#QNAN’, ‘-NNN’, ‘-nan’,’1.#IND’,’1.#QNAN’,’N/A’,’NA’,’NULL’,’NaN’,’n / a’,’nan ‘, ‘null’

另外当na_values为字典时,可以为具体的列来指定缺失值的样子

其余参数不常使用,不做补充。

以iris.xls为例,下载

import pandas as pd

iris = pd.read_excel('iris.xls', sheet_name='Sheet1')
print(iris)
"""
    Sepal Length (cm)  Sepal Width (cm)  ...  Unnamed: 8  Unnamed: 9
0                 7.0               3.2  ...           0           1
1                 6.4               3.2  ...           0           1
2                 6.9               3.1  ...           0           1
3                 5.5               2.3  ...           0           1
4                 6.5               2.8  ...           0           1
..                ...               ...  ...         ...         ...
95                4.8               3.0  ...           0           1
96                5.1               3.8  ...           0           1
97                4.6               3.2  ...           0           1
98                5.3               3.7  ...           0           1
99                5.0               3.3  ...           0           1
[100 rows x 10 columns]

查看数据:

image-20200722152327485

Pandas提供ExcelFile 更方便地读取同一个文件中的多张表格,ExcelFile类可用来打包文件并传递给read_excel

xlsx = pd.ExcelFile('iris.xls')
print(xlsx)

可以打印看看ExcelFile具体的属性,sheet_names属性能将文件中的所有表格名字生成一组列表:

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

ExcelFile类也能用来作为上下文管理器。

with pd.ExcelFile('iris.xls') as xls:
    df1 = pd.read_excel(xls, 'Sheet1')
    df2 = pd.read_excel(xls, 'Sheet2')

ExcelFile一个主要的用法就是用来解析多张表格的不同参数:

data = {}
with pd.ExcelFile('iris.xls') as xls:
    #读取Sheet1,不指定索引,指定NA值解释为NaN
    data['Sheet1'] = pd.read_excel(xls, 'Sheet1', index_col=None,na_values=['NA'])
     #读取Sheet2,指定表格第二、三列为组合索引
    data['Sheet2'] = pd.read_excel(xls, 'Sheet2', index_col=[1,2])

重点看看 data['Sheet2'],可以看到原先的Sepal Width(cm)和Petal Width(cm)已经合并为新的索引

如果多张表格解析的参数相同,可以直接使用read_excel,效率和性能是一致的。

#已下两种方式是等价的
data = {}
with pd.ExcelFile('iris.xls') as xls:
    data['Sheet1'] = pd.read_excel(xls, 'Sheet1', index_col=None,
                                   na_values=['NA'])
    data['Sheet2'] = pd.read_excel(xls, 'Sheet2', index_col=None,
                                   na_values=['NA'])

data = pd.read_excel('iris.xls', ['Sheet1', 'Sheet2'],
                     index_col=None, na_values=['NA'])

to_excel

基本参数

  • excel_writer:文件路径或现有的ExcelWriter。
  • sheet_name:它是指包含DataFrame的工作表的名称。
  • na_repr:缺少数据表示形式。
  • float_format:这是一个可选参数, 用于格式化浮点数字符串。
  • 列:指要写入的列。
  • header:写出列名。如果给出了字符串列表, 则假定它是列名的别名。
  • index:写入索引。
  • index_label:引用索引列的列标签。如果未指定, 并且标头和索引为True, 则使用索引名称。如果DataFrame使用MultiIndex, 则应给出一个序列。
  • startrow:默认值0。它指向转储DataFrame的左上单元格行。
  • startcol:默认值0。它指向转储DataFrame的左上方单元格列。
  • engine:这是一个可选参数, 用于写入要使用的引擎, openpyxl或xlsxwriter。
  • merge_cells:返回布尔值, 其默认值为True。它将MultiIndex和Hierarchical行写为合并的单元格。
  • encoding:这是一个可选参数, 可对生成的excel文件进行编码。仅对于xlwt是必需的。
  • inf_rep:它也是一个可选参数, 默认值为inf。它通常表示无穷大。
  • verbose:返回一个布尔值。它的默认值为True。它用于在错误日志中显示更多信息。
  • Frozen_panes:它也是一个可选参数, 用于指定要冻结的最底部一行和最右边一列。

写入Excel文件到磁盘

read_excel的参数大部分与read_exceld相同,第一个参数是excel文件的名字,而可选的第二个参数是DataFrame应该写入的表格名称。由于Excel版本的不同,写入操作也存在差异。例如.xls文件使用xlwt.xlsx使用xlsxwriter或者openpyxl.xlsm使用openpyxl

为了把DataFrames数据分开写入Excel文件的不同表格中,可以使用ExcelWriter方法。

with pd.ExcelWriter('test_file.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1')
    df2.to_excel(writer, sheet_name='Sheet2')

写入Excel文件到内存

把Excel文件写入类缓存区对象,如StringIOBytesIO,使用ExcelWriter方法。

# Safe import for either Python 2.x or 3.x
try:
    from io import BytesIO
except ImportError:
    from cStringIO import StringIO as BytesIO

bio = BytesIO()

# By setting the 'engine' in the ExcelWriter constructor.
writer = pd.ExcelWriter(bio, engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')

# Save the workbook
writer.save()

# Seek to the beginning and read to copy the workbook to a variable in memory
bio.seek(0)
workbook = bio.read()

为了指定你想要使用的写入方式,可以设置to_excelExcelWriter的engine参数。支持以下几种格式:

  • openpyxl: 要求2.4或者更高的版本。
  • xlsxwriter
  • xlwt

Sql Queries

需要事先安装SQLAlchemy ,它是 Python 中一个通过 ORM 操作数据库的框架。用于将用户定义的Python类与数据库表相关联,并将这些类(对象)的实例与其对应表中的行相关联。每种SQL语言支持的驱动程序不同,例如PostgreSQL的psycopg2或用于MySQL的pymysql。对于SQLite,默认情况下将其包含在Python的标准库中。

列出几个常用的方法:

Method Description
read_sql_table(table_name, con[, schema, …]) Read SQL database table into a DataFrame.
read_sql_query(sql, con[, index_col, …]) Read SQL query into a DataFrame.
read_sql(sql, con[, index_col, …]) Read SQL query or database table into a DataFrame.
DataFrame.to_sql(self, name, con[, schema, …]) Write records stored in a DataFrame to a SQL database.

在以下示例中,使用MySQL数据库,已将example_wp_log_peyton_manning.csv导入MySQL

read_sql_table

基本参数

  • table_name:string。数据库中SQL表的名称

  • con:连接sql数据库的engine,一般可以用SQLalchemy或者pymysql之类的包建立

  • schema:string,默认None。要查询的数据库中的SQL模式的名称(如果数据库flavor支持此功能)。

  • index_col: 选择某一列作为index

  • coerce_float: 将数字形式的字符串直接以float型读入

  • parse_dates:将某一列日期型字符串转换为datetime型数据,与pd.to_datetime函数功能类似。可以直接提供需要转换的列名以默认的日期形式转换,也可以用字典的格式提供列名和转换的日期格式,比如{column_name: format string}(format string:"%Y:%m:%H:%M:%S")。
  • columns:要选取的列

  • chunksize:如果提供了一个整数值,那么就会返回一个generator,每次输出的行数就是提供的值的大小。

举例:

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:12345678@localhost/test")

#方式1:
data = pd.read_sql_table('example_wp_log_peyton_manning', engine)

#方式2使用上下文管理器:
with engine.connect() as conn, conn.begin():
    data = pd.read_sql_table('example_wp_log_peyton_manning', conn)
print(data)
"""
             C1         C2
0    2007-12-10   9.590761
1    2007-12-11   8.519590
2    2007-12-12   8.183677
3    2007-12-13   8.072467
4    2007-12-14   7.893572
         ...        ...
2900 2016-01-16   7.817223
2901 2016-01-17   9.273878
2902 2016-01-18  10.333775
2903 2016-01-19   9.125871
2904 2016-01-20   8.891374
[2905 rows x 2 columns]
"""

使用index_col将列的名称指定为DataFrame索引,并通过columns指定要读取的列的子集

data = pd.read_sql_table('example_wp_log_peyton_manning', engine, index_col='C1',columns=['C2'])
print(data)
"""
                          C2
C1                          
2007-12-10  9.59076113897809
2007-12-11  8.51959031601596
2007-12-12  8.18367658262066
2007-12-13  8.07246736935477
                      ...
2016-01-16  7.81722278550817
2016-01-17  9.27387839278017
2016-01-18  10.3337753460756
2016-01-19  9.12587121534973
2016-01-20  8.89137400948464
[2906 rows x 1 columns]
"""

可以显式强制将列解析为日期:

data = pd.read_sql_table('example_wp_log_peyton_manning', engine, parse_dates={'C1': '%Y-%m-%d %H:%M:%S'})

read_sql_query

支持直接使用原始SQL语句查询。

基本参数与read_sql_table类似,区别是read_sql_query第一个参数为sql查询语句,也不需要通过columns指定要选取的列,并且不支持schema参数。

举例

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:12345678@localhost/test")
data = pd.read_sql_query('SELECT * FROM example_wp_log_peyton_manning', engine)
print(data)
"""
 C1         C2
0     2007-12-10   9.590761
1     2007-12-11   8.519590
2     2007-12-12   8.183677
3     2007-12-13   8.072467
4     2007-12-14   7.893572
          ...        ...
2900  2016-01-16   7.817223
2901  2016-01-17   9.273878
2902  2016-01-18  10.333775
2903  2016-01-19   9.125871
2904  2016-01-20   8.891374
[2905 rows x 2 columns]
“”“
#查询结果与pd.read_sql_table('example_wp_log_peyton_manning', engine)相同

使用chunksize参数,指定一个迭代器,每次处理对应的行数,例如chunksize=5,每次输出5条数据,这里需要注意的是:

read_sql_query是根据SQL语句全部读取出来后,再按chunksize一批一批地转为iterator然后再返回

for chunk in pd.read_sql_query("SELECT * FROM example_wp_log_peyton_manning", 
                               engine, chunksize=5):
    print(chunk)
"""
           C1        C2
0  2007-12-10  9.590761
1  2007-12-11  8.519590
2  2007-12-12  8.183677
3  2007-12-13  8.072467
4  2007-12-14  7.893572
           C1        C2
0  2007-12-15  7.783641
1  2007-12-16  8.414052
2  2007-12-17  8.829226
3  2007-12-18  8.382518
4  2007-12-19  8.069655
           C1        C2
0  2007-12-20  7.879291
1  2007-12-21  7.761745
2  2007-12-22  7.529406
3  2007-12-23  8.385261
4  2007-12-24  8.620111
.
.
.
"""

read_sql

基本参数与read_sql_table类似,区别是read_sql第一个参数为sql查询语句。其基本用法可以参考read_sql_table和read_sql_query,read_sql是综合read_sql_table和read_sql_query的,所以一般用read_sql就好了

to_sql

基本参数

  • name:数据库中表的名称

  • con:连接sql数据库的engine

  • schema:string,默认None。要查询的数据库中的SQL模式的名称,

  • if_exists: str = "fail",用于当目标表已经存在时的处理方式,默认是 fail,即目标表存在就失败,另外两个选项是 replace 表示替代原表,即删除再创建,append 选项仅添加数据
  • index: 布尔型,是否设置索引,默认为True,
  • index_label:索引标签,默认为None,

  • chunksize:如果提供了一个整数值,那么就会返回一个generator,每次写入的行数就是提供的值的大小。,

  • dtype:指定列的数据类型。如果使用字典,则键应为列名,值应为SQLAlchemy类型或sqlite3传统模式的字符串。如果提供了标量,它将应用于所有列

  • method:{None,'multi',callable},控制SQL插的方法,默认为None。None:使用标准SQL INSERT子句(每行一个)。 'multi':在单个INSERT子句中传递多个值。 callable用于pd_table,conn,keys,data_iter的签名

举例:

from sqlalchemy import create_engine
import numpy as  np

engine = create_engine("mysql+pymysql://root:12345678@localhost/test")
data = pd.DataFrame(np.random.randn(20, 3), columns=list('abc'))
data.to_sql('example', engine)

###需要read_sql把刚写入的数据读出来
df = pd.read_sql_query("SELECT * FROM example", engine)
print(df)
"""
 index         a         b         c
0       0 -0.241888  1.923429 -1.829121
1       1  1.584111  0.093706 -0.896667
2       2 -0.550159  0.761196 -0.822968
3       3 -0.697310 -2.719859 -0.724598
4       4  0.024913  0.185186  1.357379
5       5 -1.178684 -1.135220 -1.374831
6       6 -0.523441  0.265599  2.434427
7       7 -0.704997  0.552516 -1.764400
8       8  1.015634  0.332741  1.003711
9       9 -0.190019  0.344205  1.178451
10     10 -1.552339 -0.391571 -1.359062
11     11  1.667465 -0.574156 -1.369869
12     12  1.364349 -0.362797 -0.462540
13     13  0.271511  0.042608  1.154660
14     14 -2.551152 -0.088523 -1.512747
15     15  1.212263 -0.057304 -1.935750
16     16  1.283456 -1.794413  1.312542
17     17  1.858652 -1.247200  0.572234
18     18  1.944444 -1.063413 -1.386975
19     19 -0.496769 -0.215597 -0.047551
"""

to_sql会尝试根据数据的dtype将数据映射到适当的SQL数据类型。当您有dtype列时 object,pandas将尝试推断数据类型。

下面看看上文第一次写入MySQL的example表列的数据类型:

image-20200723151240571

可以通过使用dtype参数指定任何列的所需SQL类型来覆盖默认类型。此参数需要将列名称映射到SQLAlchemy类型(或sqlite3后备模式的字符串)的字典。例如,为字符串列指定使用sqlalchemy String类型而不是默认Text类型。

from sqlalchemy import create_engine
from sqlalchemy import Text
import numpy as  np

engine = create_engine("mysql+pymysql://root:12345678@localhost/test")
data = pd.DataFrame(np.random.randn(20, 3), columns=list('abc'))
data.to_sql('example', engine,if_exists='replace',dtype={'a': Text})

image-20200723151729705

就先介绍到这里,至于更高级的SQLAlchemy查询,可以查看https://www.pypandas.cn/docs/user_guide/io.html#sql-queries