title: ‘初识Pandas系列三:数据读写（下）’
hidden: false
hide: false
tags: Pandas
categories:

初识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

  1. 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为例，下载

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

查看数据：

初识Pandas系列三-数据读写（下） - 图1

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

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

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

初识Pandas系列三-数据读写（下） - 图2

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

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

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

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

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

重点看看 data[‘Sheet2’]，可以看到原先的Sepal Width（cm）和Petal Width（cm）已经合并为新的索引

初识Pandas系列三-数据读写（下） - 图3

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

  1. #已下两种方式是等价的
  2. data = {}
  3. with pd.ExcelFile('iris.xls') as xls:
  4.     data['Sheet1'] = pd.read_excel(xls, 'Sheet1', index_col=None,
  5.                                    na_values=['NA'])
  6.     data['Sheet2'] = pd.read_excel(xls, 'Sheet2', index_col=None,
  7.                                    na_values=['NA'])
  9. data = pd.read_excel('iris.xls', ['Sheet1', 'Sheet2'],
  10.                      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方法。

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

写入Excel文件到内存

把Excel文件写入类缓存区对象，如StringIO或BytesIO，使用ExcelWriter方法。

  1. # Safe import for either Python 2.x or 3.x
  2. try:
  3.     from io import BytesIO
  4. except ImportError:
  5.     from cStringIO import StringIO as BytesIO
  7. bio = BytesIO()
  9. # By setting the 'engine' in the ExcelWriter constructor.
  10. writer = pd.ExcelWriter(bio, engine='xlsxwriter')
  11. df.to_excel(writer, sheet_name='Sheet1')
  13. # Save the workbook
  14. writer.save()
  16. # Seek to the beginning and read to copy the workbook to a variable in memory
  17. bio.seek(0)
  18. workbook = bio.read()

为了指定你想要使用的写入方式，可以设置to_excel和ExcelWriter的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，每次输出的行数就是提供的值的大小。

举例：

  1. from sqlalchemy import create_engine
  3. engine = create_engine("mysql+pymysql://root:12345678@localhost/test")
  5. #方式1:
  6. data = pd.read_sql_table('example_wp_log_peyton_manning', engine)
  8. #方式2使用上下文管理器：
  9. with engine.connect() as conn, conn.begin():
  10.     data = pd.read_sql_table('example_wp_log_peyton_manning', conn)
  11. print(data)
  12. """
  13.               C1         C2
  14. 0    2007-12-10   9.590761
  15. 1    2007-12-11   8.519590
  16. 2    2007-12-12   8.183677
  17. 3    2007-12-13   8.072467
  18. 4    2007-12-14   7.893572
  19.          ...        ...
  20. 2900 2016-01-16   7.817223
  21. 2901 2016-01-17   9.273878
  22. 2902 2016-01-18  10.333775
  23. 2903 2016-01-19   9.125871
  24. 2904 2016-01-20   8.891374
  25. [2905 rows x 2 columns]
  26. """

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

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

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

  1. 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参数。

举例

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

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

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

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

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的签名

举例：

  1. from sqlalchemy import create_engine
  2. import numpy as  np
  4. engine = create_engine("mysql+pymysql://root:12345678@localhost/test")
  5. data = pd.DataFrame(np.random.randn(20, 3), columns=list('abc'))
  6. data.to_sql('example', engine)
  8. ###需要read_sql把刚写入的数据读出来
  9. df = pd.read_sql_query("SELECT * FROM example", engine)
  10. print(df)
  11. """
  12.  index         a         b         c
  13. 0       0 -0.241888  1.923429 -1.829121
  14. 1       1  1.584111  0.093706 -0.896667
  15. 2       2 -0.550159  0.761196 -0.822968
  16. 3       3 -0.697310 -2.719859 -0.724598
  17. 4       4  0.024913  0.185186  1.357379
  18. 5       5 -1.178684 -1.135220 -1.374831
  19. 6       6 -0.523441  0.265599  2.434427
  20. 7       7 -0.704997  0.552516 -1.764400
  21. 8       8  1.015634  0.332741  1.003711
  22. 9       9 -0.190019  0.344205  1.178451
  23. 10     10 -1.552339 -0.391571 -1.359062
  24. 11     11  1.667465 -0.574156 -1.369869
  25. 12     12  1.364349 -0.362797 -0.462540
  26. 13     13  0.271511  0.042608  1.154660
  27. 14     14 -2.551152 -0.088523 -1.512747
  28. 15     15  1.212263 -0.057304 -1.935750
  29. 16     16  1.283456 -1.794413  1.312542
  30. 17     17  1.858652 -1.247200  0.572234
  31. 18     18  1.944444 -1.063413 -1.386975
  32. 19     19 -0.496769 -0.215597 -0.047551
  33. """

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

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

初识Pandas系列三-数据读写（下） - 图4

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

  1. from sqlalchemy import create_engine
  2. from sqlalchemy import Text
  3. import numpy as  np
  5. engine = create_engine("mysql+pymysql://root:12345678@localhost/test")
  6. data = pd.DataFrame(np.random.randn(20, 3), columns=list('abc'))
  7. data.to_sql('example', engine,if_exists='replace',dtype={'a': Text})

初识Pandas系列三-数据读写（下） - 图5

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