Pandas统计分析——读/写不同数据源的数据
前言
统计分析除了包含单一数值型特征的数据集中趋势、离散趋势和峰度与偏度等统计知识外,还包含了多个特征比较计算等知识。
数据读取是进行数据预处理、建模与分析的前提。不同的数据源,需要使用不同的函数读取。pandas内置了10多种数据源读取函数和对应的数据写入函数。
常见的数据源有3种。分别是数据库数据、文本文件(一般文本文件和csv文件)和Excel文件。
例如,数据存在于3个系统中,3个系统中的数据源并不相同,所以需要使用多种数据读取方式读取相应数据。
1 读/写数据库数据
在生产环境中,绝大多数的数据都存储在数据库中。pandas提供了读取与存储关系型数据库数据的函数与方法。除了pandas库外,还需要使用SQLAlchemy库建立对应的数据库连接,SQLAlchemy配合相应数据库的python连接工具(例如,MySQL数据库需要安装mysqlclient或者pymysql库,Oracle数据库需要安装cx_oracle库),使用create_engine函数,建立一个数据库连接。
pandas支持MySQL、postgresql、Oracle、SQL Server和SQLite等主流数据库。
以MySQL数据库为例,介绍pandas数据库数据的读取与存储。
1.1 数据库数据读取
pandas实现数据库数据读取有3个函数
- read_sql_table:只能读取数据库的某一个表格,不能实现查询的操作
- read_sql_query:只能实现查询操作,不能直接读取数据库中的某个表
- read_sql:上述两者的综合
函数形式如下:
pandas.read_sql_table(table_name,con,schema=None,index_col=None, coerce_float=True, columns=None)
pandas.read_sql_query(sql,con,index_col = None, coerce_float = True)
pandas.read.sql(sql,con,index_col=None,coerce_float=True,columns=None)
SQLAlchemy连接MySQL数据库的代码如下:
import pandas as pd
from sqlalchemy import create_engine
# 数据库连接配置
username = 'root' # 替换为你的数据库用户名
password = '1234' # 替换为你的数据库密码
host = 'localhost' # 数据库地址
database = 'testdb' # 数据库名称
# 创建数据库连接
engine = create_engine(f'mysql+pymysql://{username}:{password}@{host}/{database}')
print(engine)
输出:
在creat_engine中输入的是一个连接字符串。在使用python的SQLAlchemy时,MySQL和Oracle数据库连接字符串的格式如下:
数据库产品名+连接工具名://用户名:密码@数据库IP地址:数据库端口号/数据库名称?charset=数据库数据编码
pandas的3个数据库数据读取函数的参数几乎完全一致,唯一的区别在于传入的是语句还是表名。3个函数的参数说明如下:
参数名称 | 说明 |
---|---|
sql or table_name | 接收string,表示读取的数据的表名或者SQL语句。无默认 |
con | 接收数据库连接。表示数据库连接信息。无默认 |
index_col | 接收int、sequence或者false。表示设定的列作为行名,如果是一个数列,则是多重索引。默认None |
coerce_float | 接收boolean。将数据库中的decimal类型的数据转换为pandas中的float64类型的数据。默认True |
columns | 接收list,表示读取数据的列名。默认为None |
创建完数据库连接后,就能通过3个pandas函数读取数据库中的数据,如下
import pandas as pd
## 使用read_sql_query查看tesdb中的数据表数目
formlist = pd.read_sql_query('show tables', con = engine)
print('testdb数据库数据表清单为:','\n',formlist)
输出:
## 使用read_sql_table读取订单详情表
detail1 = pd.read_sql_table('meal_order_detail1',con = engine)
print('使用read_sql_table读取订单详情表的长度为:',len(detail1))
输出:
## 使用read_sql读取订单详情表
detail2 = pd.read_sql('select * from meal_order_detail2',con = engine)
print('使用read_sql函数+sql语句读取的订单详情表长度为:',len(detail2))
detail3 = pd.read_sql('meal_order_detail3',con = engine)
print('使用read_sql函数+表格名称读取的订单详情表长度为:',len(detail3))
输出:
1.2 数据库数据存储
将DateFrame写入数据库中,同样也要依赖SQLAlchemy库的create_engine函数创建数据库连接。数据库数据读取有3个函数,但数据存储则只有一个to_sql方法。to_sql方法的函数形式如下:
DataFrame.to_sql(name,con,schema=None,if_exists='fail',index=True,index_label=None,dtype=None)
to_sql方法常用参数说明
参数名称 | 说明 |
---|---|
name | 接收string,代表数据库表名,无默认 |
con | 接收数据库连接,无默认 |
if_exists | 接收fail,replace和append。fail表示如果表名存在,则不执行写入操作;replace表示如果存在,则将元数据库表删除,再重新创建;append表示在原数据库表的基础上追加数据。默认为fail |
index | 接收boolean。表示是否将行索引作为数据传入数据库。默认为True |
index_label | 接收string或者sequence,代表是否引用索引名称,如果index参数为True,此参数为None,则使用默认名称。如果为多重索引,则必须使用sequence形式。默认为None |
dtype | 接收dict,代表写入的数据类型(列名为key,数据格式为values)。默认为None |
## 使用to_sql存储orderData
detail1.to_sql('test1',con = engine,index = False, if_exists = 'replace')
## 使用read_sql读取test表
formlist1 = pd.read_sql_query('show tables',con = engine)
print('新增一个表格后testdb数据库数据表清单为:','\n',formlist1)
输出:
2 读/写文本文件
文本文件是一种由若干行字符构成的计算机文件。
csv是一种用分隔符分隔的文件格式,因为其分隔符不一定是逗号,因此又称为字符分隔文件。文件以纯文本形式存储表格数据(数据和文本)。它是一种通用、相对简单的文件格式,所以大量程序都支持csv或者其变体,可以作为大多数程序的输入和输出格式。
2.1 文本文件读取
在数据读取过程中可以使用文本文件的读取函数对csv文件进行读取,pandas提供了read_table来读取文本文件,提供了read_csv函数读取csv文件。
函数形式如下:
pandas.read_table(filepath,sep='\t',header='infer',names=None,index_col=None,
dtype=None,encoding=utf-8,engine=None,nrows=None)
pandas.read_csv(filepath,sep=',',header='infer',names=None,index_col=None,
dtype=None,encoding=utf-8,engine=None,nrows=None)
read_table和read_csv常用参数如下
参数名称 | 说明 |
---|---|
filepath | 接收string,代表文件路径,无默认 |
sep | 接收string,代表分隔符,read_csv默认为“,”,read_table默认为制表符“Tab” |
header | 接收int或sequence,代表将某行数据作为列名。默认为infer,表示自动识别 |
names | 接收array,表示列名。默认为None |
index_col | 接收int、sequence或False,表示索引列的位置,取值为sequence则代表多重索引。默认为None |
dtype | 接收dict,代表写入的数据类型(列名为key,数据格式为values)。默认为None |
engine | 接收c或者python,表示数据解析引擎。默认为c |
nrows | 接收int,表示读取前n行,默认为None |
以菜品订单信息表为例,使用这两个函数读取数据如下:
## 使用read_table读取订单信息表
order = pd.read_table('D:/MySQLsoftware/mysql-5.7.24-winx64/data/testdb/meal_order_info.csv',sep = ',',encoding = 'gbk')
print('使用read_table读取的订单信息表的长度为:',len(order))
输出:
## 使用read_csv读取订单信息表
order1 = pd.read_csv('D:/MySQLsoftware/mysql-5.7.24-winx64/data/testdb/meal_order_info.csv',encoding = 'gbk')
print('使用read_csv读取的订单信息表的长度为:',len(order1))
输出:
read_table和read_csv函数中的sep参数是指定文本的分隔符,如果分隔符指定错误,在读取数据时,每一行数据会连成一片。
header是用来指定列名的,如果是None,则会添加一个默认列名。
encoding代表文件的编码格式,常用的编码UTF-8、UTF-16、GBK、GB2312等。如果编码指定错误,则数据将无法读取,ipython解释器会报解析错误。
## 使用read_table读取菜品订单信息表,sep = ';'
order2 = pd.read_table('D:/MySQLsoftware/mysql-5.7.24-winx64/data/testdb/meal_order_info.csv',sep = ';',encoding = 'gbk')
print('分隔符为;时订单信息表为:\n',order2)
输出:
## 使用read_csv读取菜品订单信息表,header=None
order3 = pd.read_csv('D:/MySQLsoftware/mysql-5.7.24-winx64/data/testdb/meal_order_info.csv',sep = ',',header = None,encoding = 'gbk')
print('订单信息表为:','\n',order3)
输出:
2.2 文本文件存储
文本文件的存储和读取类似,对于结构化数据,可以通过pandas中的to_csv函数实现以csv文件格式存储。
to_csv函数形式如下:
DataFrame.to_csv(path_or_buf=None,sep=',',na_rep='',columns=None,header=True,index=True,index_label=None,mode='w',encoding=None)
参数说明如下:
参数名称 | 说明 |
---|---|
path_or_buf | 接收string,代表文件路径,无默认 |
sep | 接收string,代表分隔符,默认为“,” |
na_rep | 接收string,代表缺失值,默认为“” |
colums | 接收list,代表写出的列名。默认为None |
header | 接收boolean。代表是否将列名写出。默认为True |
index | 接收boolean,代表是否将行名(索引)写出。默认为True |
index_label | 接收sequence,表示索引名。默认为None |
mode | 接收特定string。代表数据写入模式。默认为w |
encoding | 接收特定string。代表存储文件的编码格式。默认为None |
3 读/写Excel文件
3.1 excel文件读取
pandas提供了read_excel函数来读取xls、xlsx两种Excel文件,其语法和常用参数如下:
pandas.read_excel(io,sheetname=0,header=0,index_col=None,names=None,dtype=None)
参数名称 | 说明 |
---|---|
io | 接收string,代表文件路径,无默认 |
sheetname | 接收string、int,代表Excel内部表内数据的分表位置,默认为0 |
header | 接收int或sequence。表示将某行数据作为列名,取值为int的时候,代表将该列作为列名,取值为sequence时,则代表多重列索引。默认为infer,表示自动识别 |
names | 接收array,表示列名,默认为None |
colums | 接收list,代表写出的列名。默认为None |
index_col | 接收int。sequence或者False,表示索引列的位置。取值为sequence时代表多重索引。默认为None |
dtype | 接收dict,代表写入的数据类型(列名为key,数据格式为values)。默认为None |
当将餐饮的菜品订单信息表从数据库中导出为xlsx文件时,则读取数据,如下:
user = pd.read_excel('C:/Users/Lenovo/jupyter/data/users.xlsx')## 读取user.xlsx文件
print('客户信息表长度为:',len(user))
输出:
3.2 excel文件存储
将文件存储为Excel文件,可以使用to_excel函数,函数形式如下:
DataFrame.to_excel(excel_writer=None,sheetname='None',na_rep='',header=True,index=True,index_label=None,mode='w',encoding=None)
to_excel函数和to_csv函数的常用参数基本一致,区别在于to_excel函数指定存储文件的文件路径参数名称为Excel_writer,并且没有sep参数;to_excel函数增加了一个sheetname参数,用来指定存储的excel sheet的名称,默认为sheet1。
4 小案例
4.1 读取订单详情数据库数据
## 导入SQLAlchemy库的creat_engine函数
from sqlalchemy import create_engine
import pandas as pd
## 创建一个mysql连接器,用户名为root,密码为1234
username = 'root' # 替换为你的数据库用户名
password = '1234' # 替换为你的数据库密码
host = 'localhost' # 数据库地址
database = 'testdb' # 数据库名称
# 创建数据库连接
engine = create_engine(f'mysql+pymysql://{username}:{password}@{host}/{database}')
## 使用read_sql_table读取订单详情表格
order1 = pd.read_sql_table('meal_order_detail1',con = engine)
print('订单详情表1的长度为:',len(order1))
order2 = pd.read_sql_table('meal_order_detail2',con = engine)
print('订单详情表2的长度为:',len(order2))
order3 = pd.read_sql_table('meal_order_detail3',con = engine)
print('订单详情表3的长度为:',len(order3))
输出:
4.2 读取订单信息csv数据
## 使用read_table读取订单信息表
orderInfo = pd.read_table('C:/Users/Lenovo/jupyter/data/meal_order_info.csv',
sep = ',',encoding = 'gbk')
print('订单信息表的长度为:',len(orderInfo))
输出:
4.3 读取客户信息excel数据
## 读取user.xlsx文件_
userInfo = pd.read_excel('C:/Users/Lenovo/jupyter/data/users.xlsx', sheet_name = 'users1')
print('客户信息表的长度为:',len(userInfo))
输出: