Pandas系列（十一）-文件IO操作

主要内容

• 文件读取
• 1.read_csv
• 2.read_excel
• 3.read_html
• 4.read_sql
• 5.read_sql_table
• 文件保存
• 1.to_csv
• 2.to_excel
• 3.to_sql

1 read_csv

pandas.read_csv(filepath_or_buffer, sep=', ', delimiter=None, header='infer', names=None, index_col=None, usecols=None, squeeze=False, prefix=None, mangle_dupe_cols=True, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skipinitialspace=False, skiprows=None, skipfooter=0, nrows=None, na_values=None, keep_default_na=True, na_filter=True, verbose=False, skip_blank_lines=True, parse_dates=False, infer_datetime_format=False, keep_date_col=False, date_parser=None, dayfirst=False, iterator=False, chunksize=None, compression='infer', thousands=None, decimal=b'.', lineterminator=None, quotechar='"', quoting=0, doublequote=True, escapechar=None, comment=None, encoding=None, dialect=None, tupleize_cols=None, error_bad_lines=True, warn_bad_lines=True, delim_whitespace=False, low_memory=True, memory_map=False, float_precision=None)[source]¶

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)[source]

#-*- coding: utf-8 -*-

"""@Datetime: 2018/11/11
@Author: Zhang Yafei
"""
from multiprocessing importPool

importpandas
importrequests
importos

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
HTML_DIR = os.path.join(BASE_DIR,'药品商品名通用名称数据库')

if notos.path.exists(HTML_DIR):
    os.mkdir(HTML_DIR)

name_list =[] 
if os.path.exists('drug_name.csv'):
    data = pandas.read_csv('drug_name.csv',encoding='utf-8')
    
header ={
        'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8',
        'Accept-Encoding': 'gzip, deflate',
        'Accept-Language': 'zh-CN,zh;q=0.9',
        'Cache-Control': 'max-age=0',
        'Connection': 'keep-alive',
        'Content-Length': '248',
        'Content-Type': 'application/x-www-form-urlencoded',
        'Cookie': 'JSESSIONID=0000ixyj6Mwe6Be4heuHcvtSW4C:-1; Hm_lvt_3849dadba32c9735c8c87ef59de6783c=1541937281; Hm_lpvt_3849dadba32c9735c8c87ef59de6783c=1541940406',
        'Upgrade-Insecure-Requests': '1',
        'Origin': 'http://pharm.ncmi.cn',
        'Referer': 'http://pharm.ncmi.cn/dataContent/dataSearch.do?did=27',
        'User-Agent': 'Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36',
    }


defspider(page):
    adverse_url = 'http://pharm.ncmi.cn/dataContent/dataSearch.do?did=27'form_data ={
        'method': 'list',
        'did': 27,
        'ec_i': 'ec',
        'ec_crd': 15,
        'ec_p': page,
        'ec_rd': 15,
        'ec_pd': page,
    }
    response = requests.post(url=adverse_url,headers=header,data=form_data)
    filename = '{}.html'.format(page)
    with open(filename,'w',encoding='utf-8') as f:
        f.write(response.text)
    print(filename,'下载完成')


defget_response(page):
    file = os.path.join(HTML_DIR,'{}.html')
    with open(file.format(page),'r',encoding='utf-8') as f:
        response =f.read()
    returnresponse


defparse(page):
    response =get_response(page)
    result = pandas.read_html(response,attrs={'id':'ec_table'})[0]
    data = result.iloc[:,:5]
    data.columns = ['序号','批准文号','药品中文名称','药品商品名称','生产单位']
    if page==1:
        data.to_csv('drug_name.csv',mode='w',encoding='utf_8_sig',index=False)
    else:
        data.to_csv('drug_name.csv',mode='a',encoding='utf_8_sig',header=False,index=False)
    print('第{}页数据存取完毕'.format(page))

defget_unparse_data():
    if os.path.exists('drug_name.csv'):
        pages = data['序号']
        pages = list(set(range(1,492))-set(pages.values))
    else:
        pages = list(range(1,492))
    returnpages
    
defdownload():
     pool =Pool()
     pool.map(spider,list(range(1,492)))
     pool.close()
     pool.join()
    
    
defwrite_to_csv():
    pages =get_unparse_data()
    print(pages)
    list(map(parse,pages))    
    
defnew_data(chinese_name):
    trade_name = '/'.join(set(data[data.药品中文名称==chinese_name].药品商品名称))
    name_list.append(trade_name)
      
defread_from_csv():
    name = data['药品中文名称'].values
    print(len(name))
    chinese_name = list(set(data['药品中文名称'].values))
    list(map(new_data,chinese_name))
    df_data = {'药品中文名称':chinese_name,'药品商品名称':name_list}
    new_dataframe =pandas.DataFrame(df_data)
    new_dataframe.to_csv('unique_chinese_name.csv',mode='w',encoding='utf_8_sig',index=False)
    returnnew_dataframe    
    
defmain():
#download()    #write_to_csv()
    returnread_from_csv()

if __name__ == '__main__':
    drugname_dataframe = main()

药品商品名和通用名信息下载

4 read_sql

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

效果：将SQL查询或数据库表读入DataFrame。

此功能是一个方便的包装和（为了向后兼容）。它将根据提供的输入委派给特定的功能。SQL查询将被路由到，而数据库表名将被路由到。请注意，委派的功能可能有更多关于其功能的特定说明，此处未列出。

参数详解

• sql: string or SQLAlchemy Selectable (select or text object)

SQL query to be executed or a table name.

要执行的SQL查询或表名。

• con: SQLAlchemy connectable (engine/connection) or database string URI

or DBAPI2 connection (fallback mode)

Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.

或DBAPI2连接（后备模式）

使用SQLAlchemy可以使用该库支持的任何数据库。如果是DBAPI2对象，则仅支持sqlite3。

• index_col: string or list of strings, optional, default: None

Column(s) to set as index(MultiIndex).

要设置为索引的列（MultiIndex）。

• coerce_float: boolean, default True

Attempts to convert values of non-string, non-numeric objects (like decimal.Decimal) to floating point, useful for SQL result sets.

尝试将非字符串，非数字对象（如decimal.Decimal）的值转换为浮点，这对SQL结果集很有用。

• params: list, tuple or dict, optional, default: None

List of parameters to pass to execute method. The syntax used to pass parameters is database driver dependent. Check your database driver documentation for which of the five syntax styles, described in PEP 249’s paramstyle, is supported. Eg. for psycopg2, uses %(name)s so use params={‘name’ : ‘value’}

• parse_dates: list or dict, default: None
• List of column names to parse as dates.

要解析为日期的列名列表。

• Dict of{column_name:formatstring}where format string is strftime compatible in case of parsing string times, or is one of (D, s, ns, ms, us) in case of parsing integer timestamps.

在解析字符串时，格式字符串是strftime兼容的格式字符串，或者是（D、s、ns、ms、us），以防解析整型时间戳。

• Dict of{column_name:argdict}, where the arg dict corresponds to the keyword arguments ofpandas.to_datetime()Especially useful with databases without native Datetime support, such as SQLite.

{column_name：arg dict}的字典，其中arg dict对应于pandas.to_datetime（）的关键字参数。对于没有本机Datetime支持的数据库（如SQLite）特别有用。

• columns: list, default: None

List of column names to select from SQL table (only used when reading a table).

从SQL表中选择的列名列表（仅在读取表时使用）。

• chunksize: int, default None

If specified, return an iterator wherechunksizeis the number of rows to include in each chunk.

如果指定，则返回一个迭代器，其中chunksize是要包含在每个块中的行数。

使用案例

import pymysql
import pandas as pd
 
con = pymysql.connect(host="127.0.0.1",user="root",password="password",db="world")
# 读取sql
data_sql=pd.read_sql("SQL查询语句",con)
# 存储
data_sql.to_csv("test.csv")

5 read_sql_table

pandas.read_sql_table(table_name,con,schema=None,index_col=None,coerce_float=True,parse_dates=None,columns=None,chunksize=None)[source]

效果：将SQL数据库表读入DataFrame。

给定一个表名和一个SQLAlchemy可连接，返回一个DataFrame。此功能不支持DBAPI连接。

参数详解

• table_name: string

Name of SQL table in database.

数据库中SQL表的名称。

• con: SQLAlchemy connectable (or database string URI)

SQLite DBAPI connection mode not supported.

不支持SQLite DBAPI连接模式。

• schema: string, default None

Name of SQL schema in database to query (if database flavor supports this). Uses default schema if None (default).

要查询的数据库中的SQL模式的名称（如果数据库flavor支持此功能）。如果为None（默认值），则使用默认架构。

• index_col: string or list of strings, optional, default: None

Column(s) to set as index(MultiIndex).

要设置为索引的列（MultiIndex）。

• coerce_float: boolean, default True

Attempts to convert values of non-string, non-numeric objects (like decimal.Decimal) to floating point. Can result in loss of Precision.

尝试将非字符串，非数字对象（如decimal.Decimal）的值转换为浮点值。可能导致精度损失。

• parse_dates: list or dict, default: None
• List of column names to parse as dates.

要解析为日期的列名列表。

• Dict of{column_name:formatstring}where format string is strftime compatible in case of parsing string times or is one of (D, s, ns, ms, us) in case of parsing integer timestamps.

{column_name：format string}的字典，其中格式字符串在解析字符串时间时与strftime兼容，或者在解析整 数时间戳的情况下是（D，s，ns，ms，us）之一。

• Dict of{column_name:argdict}, where the arg dict corresponds to the keyword arguments ofpandas.to_datetime()Especially useful with databases without native Datetime support, such as SQLite.

{column_name：arg dict}的字典，其中arg dict对应于pandas.to_datetime（）的关键字参数。对于没有本机Datetime支持的数据库（如SQLite）特别有用。

• columns: list, default: None

List of column names to select from SQL table

从SQL表中选择的列名列表

• chunksize: int, default None

If specified, returns an iterator wherechunksizeis the number of rows to include in each chunk.

如果指定，则返回一个迭代器，其中chunksize是要包含在每个块中的行数。

使用案例

import pandas as pd
import pymysql
from sqlalchemy import create_engine
 
con = create_engine('mysql+pymysql://user_name:password@127.0.0.1:3306/database_name')
data = pd.read_sql_table("table_name", con)
data.to_csv("table_name.csv")

文件保存

　1 to_csv

DataFrame.to_csv(path_or_buf=None, sep=', ', na_rep='', float_format=None, columns=None, header=True, index=True, index_label=None, mode='w', encoding=None, compression='infer', quoting=None, quotechar='"', line_terminator=None, chunksize=None, tupleize_cols=None, date_format=None, doublequote=True, escapechar=None, decimal='.')[source]¶

参数详解

• path_or_buf=None： string or file handle, default None

File path or object, if None is provided the result is returned as a string.

字符串或文件句柄，默认无文件

路径或对象，如果没有提供，结果将返回为字符串。

• sep : character, default ‘,’

Field delimiter for the output file.

默认字符 ‘ ，’

输出文件的字段分隔符。

• na_rep : string, default ‘’

Missing data representation

字符串，默认为 ‘’

浮点数格式字符串

• float_format : string, default None

Format string for floating point numbers

字符串，默认为 None

浮点数格式字符串

• columns : sequence, optional Columns to write

顺序，可选列写入

• header : boolean or list of string, default True

Write out the column names. If a list of strings is given it is assumed to be aliases for the column names

字符串或布尔列表，默认为true

写出列名。如果给定字符串列表，则假定为列名的别名。

• index : boolean, default True

Write row names (index)

布尔值，默认为Ture

写入行名称（索引）

• index_label : string or sequence, or False, default None。Column label for index column(s) if desired. If None is given, and header and index are True, then the index names are used. A sequence should be given if the DataFrame uses MultiIndex. If False do not print fields for index names. Use index_label=False for easier importing in R

字符串或序列，或False,默认为None

如果需要，可以使用索引列的列标签。如果没有给出，且标题和索引为True，则使用索引名称。如果数据文件使用多索引，则应该使用这个序列。如果值为False，不打印索引字段。在R中使用index_label=False 更容易导入索引.

• mode : str

模式：值为‘str’，字符串

Python写模式，默认“w”

• encoding : string, optional

编码：字符串，可选

表示在输出文件中使用的编码的字符串，Python 2上默认为“ASCII”和Python 3上默认为“UTF-8”。

• compression : string, optional

字符串，可选项

表示在输出文件中使用的压缩的字符串，允许值为“gzip”、“bz2”、“xz”，仅在第一个参数是文件名时使用。

• line_terminator : string, default ‘ ’

字符串，默认为 ‘ ’

在输出文件中使用的换行字符或字符序列

• quoting : optional constant from csv module

CSV模块的可选常量

默认值为to_csv.QUOTE_MINIMAL。如果设置了浮点格式，那么浮点将转换为字符串，因此csv.QUOTE_NONNUMERIC会将它们视为非数值的。

• quotechar : string (length 1), default ‘”’

字符串（长度1），默认“”

用于引用字段的字符

• doublequote : boolean, default True

布尔，默认为Ture

控制一个字段内的quotechar

• escapechar : string (length 1), default None

字符串（长度为1），默认为None

在适当的时候用来转义sep和quotechar的字符

• chunksize : int or None

一次写入行

• tupleize_cols : boolean, default False

布尔值 ，默认为False

从版本0.21.0中删除：此参数将被删除，并且总是将多索引的每行写入CSV文件中的单独行

（如果值为false）将多索引列作为元组列表（如果TRUE）或以新的、扩展的格式写入，其中每个多索引列是CSV中的一行。

• date_format : string, default None。字符串，默认为None.字符串对象转换为日期时间对象

• decimal: string, default ‘.’字符串，默认’。’字符识别为小数点分隔符。例如。欧洲数据使用 ​​’，’
  

注意事项：

• 1、一般情况下我们用utf-8编码进行保存，如果出现中文编码错误，则可以依次换用gbk，gb2312 , gb18030，一般总能成功的，本例中用utf-8
• 2、to_csv方法，具体参数还有很多，可以去看官方文档，这里提到一个index = False参数，表示保存csv的时候，我们不保存pandas 的Data frame的行索引1234这样的序号，默认情况不加的话是index = True，会有行号（如下图），这点在保存数据库mysql的时候体现尤其明显，不注意的话可能会出错

2 to_excel

to_excel(self, excel_writer, sheet_name='Sheet1', na_rep='', float_format=None,columns=None, 
header=True, index=True, index_label=None,startrow=0, startcol=0, engine=None, 
merge_cells=True, encoding=None,inf_rep='inf', verbose=True, freeze_panes=None)

常用参数解析

• excel_writer ： ExcelWriter目标路径
• sheet_name ：excel表名命名
• na_rep ： 缺失值填充 ，可以设置为字符串
• columns ：选择输出的的列存入
• header :指定作为列名的行，默认0，即取第一行，数据为列名行以下的数据；若数据不含列名，则设定 header = None；

• index：默认为True，显示index，当index=False 则不显示行索引（名字）
• index_label：设置索引列的列名

writer = pd.ExcelWriter('data/excel.xlsx')
df.to_excel(writer, sheet_name='user', index=False)
writer.save() 　

多张表保存到一个excel文件

with pd.ExcelWriter(path="结果.xlsx") as writer:
    exam_data.to_excel(excel_writer=writer, sheet_name='试题数据', index=False)
    student_total_score.to_excel(excel_writer=writer, sheet_name='学生总成绩', index=False)
    student_semester_total.to_excel(excel_writer=writer, sheet_name='每个学生各学期总成绩', index=False)
    course_avg_score.to_excel(excel_writer=writer, sheet_name='各门课程平均成绩', index=False)
    greater_than_avg_student.to_excel(excel_writer=writer, sheet_name='各学期大于本课程平均成绩的学生姓名及成绩', index=False)
    writer.save()

补充：固定输出列的顺序

data = pd.DataFrame(data=data_list)
# 固定列表的输出顺序
data = data.loc[:, columns]

3 to_sql

import pandas as pd
data = [
       {"name":"张三","age":18,"city":"北京"},
       {"name":"李四","age":19,"city":"上海"},
       {"name":"王五","age":20,"city":"广州"},
       {"name":"赵六","age":21,"city":"深圳"},
       {"name":"孙七","age":22,"city":"武汉"}
]
df = pd.DataFrame(data,columns=["name","age","city"])
df

from sqlalchemy import create_engine

table_name = "user"

engine = create_engine(
    "mysql+pymysql://root:0000@127.0.0.1:3306/db_test?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
conn = engine.connect()
df.to_sql(table_name, conn, if_exists='append',index=False)

• 上面代码已经实现将我们构造的df数据保存MySQL，现在提一些注意点

注意事项：
1、我们用的库是sqlalchemy，官方文档提到to_sql是被sqlalchemy支持
文档地址：
http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html
2、数据库配置用你自己的数据库配置，db_flag为数据库类型，根据不同情况更改，在保存数据之前，要先创建数据库字段。

3. engine_config为数据库连接配置信息
4、create_engine是根据数据库配置信息创建连接对象
5、if_exists = 'append',追加数据
6、index = False 保存时候，不保存df的行索引，这样刚好df的3个列和数据库的3个字段一一对应，正常保存，如果不设置为false的话，数据相当于4列，跟MySQL 3列对不上号，会报错