pandas如何读写源数据

前言

说到pandas，相信每个数据分析师都不陌生。在日常分析工作中，免不了需要读写数据。而数据的来源往往各式各样，有csv文件、excel内容、关系型数据库等等。

pandas为了支持这些数据源的读写，都有相关的方法来实现。这里就来总结下各类数据的读写方式把。

csv、excel的读写

csv文件的读取

先说说csv文件，读取csv内容的函数是：read_csv。可传参数有非常多，我们看看用得多的：

pandas.read_csv(filepath_or_buffer, header="infer", index_col=None, usecols=None, nrows=None)

• filepath_or_buffer: csv文件路径/URL地址
• header: int/list of int，默认自动推断是否有表头。填入int时如header=3是设置第3行为表头。
• index_col：int/str。index_col=4表示将第4列设置为index列
• usecols: list-like or callable, 可选参数。表示选取部分列的数据
• nrows： int，可选参数。读取的行数。在读取较大文件时，可设置此项，不加载所有行数据

举个例子：

import pandas as pd
df = pd.read_csv('/data/a.csv')

设置第2行为表头，只读5000行。

import pandas as pd
df = pd.read_csv('/data/a.csv', header=2, nrows=5000)

csv文件的写入

函数是to_csv，主要参数有：

df.to_csv(path_or_buf, header=True, index=True)

• path_or_buff: 写入文件的路径
• header: 是否存在表头，默认为True
• index: 是否写入index，默认为True

举个例子：

import pandas as pd
df = pd.read_csv('/data/a.csv')
df['col1'] = 1
# 写入到b文件中
df.to_csv('/data/b.csv', index=False)

excel文件的读取

函数是read_excel，主要参数有：

pandas.read_excel(io, sheet_name=0,header=0,index_col=None)

• io: 文件路径或url地址
• sheet_name：str,int,list,默认为0。表示默认读取第一个sheet, str时表示加载该sheetname的内容，list表示加载列表内的sheet.
• header: int,list of int,默认为0
• index_col: int, list of int, 默认None。index字段

示例：

import pandas as pd
# 读取a.xlsx表sheet_name是‘测试数据’的数据
df = pd.read_excel('data/a.xlsx', sheet_name='测试数据')

excel文件的写入

函数是to_excel,主要参数有：

df.to_excel(excel_writer, sheet_name='Sheet1', index=True)

• excel_writer：Str或Excel Writer对象
• sheet_name：str, sheet名称
• index: bool，默认True。是否保存index

小例子：

import pandas as pd

df = pd.read_excel('data/a.xlsx', sheet_name='测试数据')
with pd.ExcelWriter('/data/b.xlsx') as writer:
    df.to_excel(writer, sheet_name='测试数据',index=False)

另外像json文件读写都与excel、csv相似，大家可参考官方文档。

数据库读写

数据库也是我们最常遇到的读写场景，我们这里主要以MySQL为例。

MySQL的读取

函数read_sql, 常用参数：

pandas.read_sql(sql, con, index_col=None)

• sql: sql查询语句
• con: 数据库连接对象，主要是sqlalchemy、sqlite3连接
• index_col: index字段的设置

mysql的连接，我们通常用sqlalchemy来作为连接对象，请看下面示例：

from sqlalchemy import create_engine
import  pandas as pd

# 填写mysql的连接url
con = "mysql+pymysql://{user}:{pwd}@{host}:{port}/{db}?charset=utf8"
engine = create_engine(con, connect_args={'connect_timeout': 20})

sql = "select id, name from users where id"
df = pd.read_sql(sql, con)

MySQL的写入

函数to_sql, 常用参数：

DataFrame.to_sql(name, con, if_exists='fail', index=True, index_label=None, method=None)

• name: MySQL的表名
• con: 连接对象
• if_exists: fail/replace/append, 默认fail。
• index：默认True， 是否保存index
• index_label: 索引标签
• method: 控制sql插入的自定义方法

示例：

from sqlalchemy import create_engine
import  pandas as pd

# 填写mysql的连接url
con = "mysql+pymysql://{user}:{pwd}@{host}:{port}/{db}?charset=utf8"
engine = create_engine(con, connect_args={'connect_timeout': 20})

sql = "select id, name from users where id"
df = pd.read_sql(sql, con)

# 数据写入到a表
df.to_sql(sql, con=con, if_exists="append", index=False,index_label=False)

需要注意的是：如果往一个表添加数据，参数if_exists="append"即可。如果设置为if_exists="replace"将会抹去表的数据，再往表里插入数据。

MySQL数据的更新问题

因实际工作中我们经常会有更新数据的场景。to_sql最基本的方法无法达到我们的预期，我们需要用别的方法去实现。

• 第一个方法就是自定义to_sql的method方法，具体操作就不细讲了。

• 第二个方法就是结合pandas结合MySQL操作，分步完成数据更新。 这里提供一个思路：

1. 建立MySQL临时表，将要更新的数据用pandas插入到临时表中
2. sql语句更新目标表的数据
3. 删除临时表

示例代码如下：

import pandas as pd
from sqlalchemy import create_engine

# 填写mysql的连接url
con = "mysql+pymysql://{user}:{pwd}@{host}:{port}/{db}?charset=utf8"
engine = create_engine(con, connect_args={'connect_timeout': 20})

sql = "select * from a where status=0"
df = pd.read_sql(sql, con=engine)
df['status'] = 1

# 创建临时表
sql = "create table tmp_data (xxx)"
...

# 更新目标表
update_sql = """
UPDATE table_to_update AS f
     set m3 = t.m3
     from
        temp_table AS t
     where
        f.id=t.id
"""
...

# 删除临时表
del_sql = "drop table temp_table"
...

小结

本文主要介绍了pandas读写常用数据源的方法，其他数据源我们也可通过官方文档来查阅使用方法。