（python）Pandas数据统计分析、连接数据库操作、DataFrame操作、透视表与交叉表

读写数据

读写数据库数据

• pandas提供了读取与存储关系型数据库数据的函数与方法。但还需要使用SQLAlchemy库建立对应的数据库连接。SQLAlchemy配合相应数据库的Python连接工具（例如MySQL数据库需要安装mysqlclient或者pymysql库），使用create_engine函数，建立一个数据库连接。
• creat_engine中填入连接字符串，MySQL和Oracle的链接字符串格式为：
  数据库产品名+连接名：//用户名:密码@数据库IP:端口号/数据库名称？charset = 编码格式
• 如：

from sqlalchemy import create_engine
import pandas as pd
enging = create_engine('mysql+pymysql://root:1123@127.0.0.1:3306/bigdata?charset=utf8')

读取操作

1. read_sql_table只能读取库中的某一个表格，不能实现查找语句

pandas.read_sql_table(table_name,con,schema=None,index_col=None,coerce_float=True,columms=None)

2. read_sql_query则只能实现查询操作，不能直接读取数据库中的某个表。

pandas.read_sql_quuery(sql,con,index_col=None,coerce_float=True)

3. read_sql是两者的综合，既能够读取数据库中的某一个表，也能够实现查询操作。

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

• 参数

参数名称	说明
sql table_name	接收string，表示读取数据库的表名或者sql语句。无默认
con	接收数据库连接，表示数据库连接信息，无默认
index_col	接收int，sequence或者False。表示设定的列作为行名，如果是一个数列则是多行索引。默认为None
coerce_float	接收Boolean，将数据库中的decimal类型的数据转换为pandas中的float64类型的数据。默认为True。
columns	接收list。表示读取数据的列名。默认为None。

写入操作

DataFrame.to_sql(name, con, schema=None, if_exists=’fail’, index=True, index_label=None, dtype=None)

• 参数

参数名称	说明
name	接收string，代表数据库表名。无默认
con	接收数据库连接，表示数据库连接信息，无默认
if_exists	接收fail,replace,append。fail表示表不存在就不执行写入，replace表示表存在会删除后重新建立表，append表示不管存在不存在都在表后追加。
index	接收Boolean。表示是否将行索引作为数据传入数据库，默认True
index_label	接收string或者sequence。代表是否引用索引名称，如果index参数为True此参数为None则使用默认名称。如果为多重索引必须使用sequence形式。默认为None。。
dtype	接收dict，代表写入的数据类型，列为Key，数据为values。默认None

读写文本文件

• 文本文件是一种由若干行字符构成的计算机文件，它是一种典型的顺序文件。
• csv是一种逗号分隔的文件格式，因为其分隔符不一定是逗号，又被称为字符分隔文件，文件以纯文本形式存储表格数据（数字和文本）。

读取

• 使用read_table来读取文本文件。

pandas.read_table(filepath_or_buffer, sep=’\t’, header=’infer’, names=None, index_col=None, dtype=None, engine=None, nrows=None)

*使用read_csv函数来读取csv文件。

pandas.read_csv(filepath_or_buffer, sep=’,’, header=’infer’, names=None, index_col=None, dtype=None, engine=None, nrows=None)

• read_table和read_csv函数中的sep参数是指定文本的分隔符的，如果分隔符指定错误，在读取数据的时候，每一行数据将连成一片。
• header参数是用来指定列名的，如果是None则会添加一个默认的列名。
• encoding代表文件的编码格式，常用的编码有utf-8、utf-16、gbk、gb2312、gb18030等。如果编码指定错误数据将无法读取，IPython解释器会报解析错误。

文件存储

• 文本文件的存储和读取类似，结构化数据可以通过pandas中的to_csv函数实现以csv文件格式存储文件。

DataFrame.to_csv(path_or_buf=None, sep=’,’, na_rep=”, columns=None, header=True, index=True,index_label=None,mode=’w’,encoding=None)

读写Excel

读取

pandas提供了read_excel函数来读取“xls”“xlsx”两种Excel文件。

pandas.read_excel(io, sheetname=0, header=0, index_col=None, names=None, dtype=None)

写入

• 将文件存储为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_csv方法的常用参数基本一致，区别之处在于指定存储文件的文件路径参数名称为excel_writer，并且没有sep参数，增加了一个sheetnames参数用来指定存储的Excel sheet的名称，默认为sheet1。

DataFrame

所有的pandas读取的文件类型都是DataFrame

DataFrame基础属性

函数	返回值
values	元素
index	索引
columns	列名
dtypes	类型
size 元素	个数
ndim	维度数
shape	数据形状（行列数目）

import pandas as pd
import os
os.chdir(r'C:\Users\17567\Desktop\ppt\大数据处理编程开发\第4章\data') // 将路径改为文件所在路径，当前文件夹是这个
cs = pd.read_csv('Training_LogInfo.csv')
size = cs.size
shape = cs.shape
col = cs.columns
dt = cs.dtypes
print("大小",size)
print("shape",shape)
print("列名",col)
print("类型",dt)

增删查改DataFrame数据

数据的基本查看方式

单列访问

• DataFrame的单列数据为一个Series。根据DataFrame的定义可以知晓DataFrame是一个带有标签的二维数组，每个标签相当每一列的列名。有以下两种方式来实现对单列数据的访问。
  • 以字典访问某一个key的值的方式使用对应的列名，实现单列数据的访问。
  • 以属性的方式访问，实现单列数据的访问。（不建议使用，易引起混淆）

import pandas as pd
import os
os.chdir(r'C:\Users\17567\Desktop\ppt\大数据处理编程开发\第4章\data')
cs = pd.read_csv('Training_LogInfo.csv')
print(cs['Listinginfo1'])

对某一列的某几行访问

• 访问DataFrame中某一列的某几行时，单独一列的DataFrame可以视为一个Series（另一种pandas提供的类，可以看作是只有一列的DataFrame），而访问一个Series基本和访问一个一维的ndarray相同。

print(cs['Listinginfo1'][:20])

对多列进行访问访问

• DataFrame多列数据可以将多个列索引名称视为一个列表，同时访问DataFrame多列数据中的多行数据和访问单列数据的多行数据方法基本相同。

import pandas as pd
import os
os.chdir(r'C:\Users\17567\Desktop\ppt\大数据处理编程开发\第4章\data')
cs = pd.read_csv('Training_LogInfo.csv')
print(cs[['Idx','Listinginfo1']]) //注意双[]
print(cs[['Idx','Listinginfo1']][:5])

对某几行进行访问

• 如果只是需要访问DataFrame某几行数据的实现方式则和上述的访问多列多行相似，选择所有列，使用“:”代替即可。

print(cs[5:9])

• head和tail也可以得到多行数据，但是用这两种方法得到的数据都是从开始或者末尾获取的连续数据。默认参数为访问5行，只要在方法后方的“()”中填入访问行数即可实现目标行数的查看。

print(cs.head(2))
print(cs.tail(2))

loc、iloc访问方式

loc方法是针对DataFrame索引名称的切片方法，如果传入的不是索引名称，那么切片操作将无法执行

• 利用loc方法，能够实现所有单层索引切片操作。loc方法使用方法如下。

DataFrame.loc[行索引名称或条件, 列索引名称]

print(cs.loc[1,'LogInfo1' ])

• iloc和loc区别是iloc接收的必须是行索引和列索引的位置。iloc方法的使用方法如下。

DataFrame.iloc[行索引位置, 列索引位置]

print(cs.iloc[1:4,1])

• 使用loc方法和iloc实现多列切片，其原理的通俗解释就是将多列的列名或者位置作为一个列表或者数据传入。
• 使用loc，iloc方法可以取出DataFrame中的任意数据。
• 在loc使用的时候内部传入的行索引名称如果为一个区间，则前后均为闭区间；iloc方法使用时内部传入的行索引位置或列索引位置为区间时，则为前闭后开区间。
• loc内部还可以传入表达式，结果会返回满足表达式的所有值。

转换与处理时间序列数据

转换字符串时间为标准时间

pd.to_datetime(arg, format=None,errors='ignore')

参数	说明
arg	时间序列
format	时间格式
errors	当产生错误使继续执行，默认None，错误不输出

print(order.lock_time.dtypes)
order['lock_time'] = pd.to_datetime(order['lock_time'])
print(order['lock_time'].dtypes)
>>>
>转换前: object
转换后 datetime64[ns]

pandas时间相关的类

• 在多数情况下，对时间类型数据进行分析的前提就是将原本为字符串的时间转换为标准时间类型。pandas继承了NumPy库和datetime库的时间相关模块，提供了6种时间相关的类。

类名称	说明
Timestamp	最基础的时间类。表示某个时间点。在绝大多数的场景中的时间数据都是Timestamp形式的时间。
Period	表示单个时间跨度，或者某个时间段，例如某一天，某一小时等。
Timedelta	表示不同单位的时间，例如1天，1.5小时，3分钟，4秒等，而非具体的某个时间段。
DatetimeIndex	一组Timestamp构成的Index，可以用来作为Series或者DataFrame的索引。
PeriodtimeIndex	一组Period构成的Index，可以用来作为Series或者DataFrame的索引。
TimedeltaIndex	一组Timedelta构成的Index，可以用来作为Series或者DataFrame的索引。

Timestamp

• Timestamp最为最基础的、最常用的、在多数情况下，时间相关的字符串装换为Timestamp。pandas提供了to_datetime函数，能够实现这一目标
• Timestamp类型时间是有限的

print('最大时间',pd.Timestamp.max)
print('最小时间',pd.Timestamp.min)
>>>
>最大时间 2262-04-11 23:47:16.854775807
最小时间 1677-09-21 00:12:43.145225

DatetimeIndex与PeriodIndex函数

• 除了将数据字原始DataFrame中直接转换为Timestamp格式外，还可以将数据单独提取出来将其转换为DatetimeIndex或者PeriodIndex。
• 转换为PeriodIndex的时候需要注意，需要通过freq参数指定时间间隔，常用的时间间隔有Y为年，M为月，D为日，H为小时，T为分钟，S为秒。两个函数可以用来转换数据还可以用来创建时间序列数据，其参数非常类似。

DatetimeIndex与PeriodIndex函数及其参数说明

• DatetimeIndex和PeriodIndex两者区别在日常使用的过程中相对较小，其中DatetimeIndex是用来指代一系列时间点的一种数据结构，而PeriodIndex则是用来指代一系列时间段的数据结构。

参数名称	说明
data	接收array。表示DatetimeIndex的值。无默认。
freq	接收string。表示时间的间隔频率。无默认。
start	接收string。表示生成规则时间数据的起始点。无默认。
periods	表示需要生成的周期数目。无默认。
end	接收string。表示生成规则时间数据的终结点。无默认。
tz	接收timezone。表示数据的时区。默认为None。
name	接收int，string。默认为空。指定DatetimeIndex的名字。

提取时间序列数据信息

Timestamp类常用属性

• 在多数涉及时间相关的数据处理，统计分析的过程中，需要提取时间中的年份，月份等数据。使用对应的Timestamp类属性就能够实现这一目的。

year1 = [i.year for i in data]
print('年份：',year1)

month1 = [i.month for i in data]
print('月份：',month1)

day1 = [i.day for i in data]
print('日子，',day1)

weekday1 = [i.weekday_name for i in data]
print('星期：',weekday1)

dayofweek1 = [i.dayofweek for i in data]
print('一周第几天：',dayofweek1)

• 结合Python列表推导式，可以实现对DataFrame某一列时间信息数据的提取。

属性名称	说明	属性名称	说明
year	年	week	一年中第几周
month	月	quarter	季节
day	日	weekofyear	一年中第几周
hour	小时	dayofyear	一年中的第几天
minute	分钟	dayofweek	一周第几天
second	秒	weekday	一周第几天
date	日期	weekday_name	星期名称
time	时间	is_leap_year	是否闰年

在DatetimeIndex和PeriodIndex中提取数据

• 在DatetimeIndex和PeriodIndex中提取对应信息可以以类属性方式实现。
• 值得注意的是PeriodIndex相比于DatetimeIndex少了weekday_name属性，所以不能够用该属性提取星期名称数据。若想要提取信息名称可以通过提取weekday属性，而后将0-6七个标签分别赋值为Monday至Sunday。

加减时间数据

Timedelta类

• Timedelta是时间相关的类中的一个异类，不仅能够使用正数，还能够使用负数表示单位时间，例如1秒，2分钟，3小时等。使用Timedelta类，配合常规的时间相关类能够轻松实现时间的算术运算。目前Timedelta函数中时间周期中没有年和月。

time1 = data + pd.Timedelta(days=1)
print(time1)
>>>前
>0     2016-08-01 11:11:46
1     2016-08-01 11:31:55
2     2016-08-01 12:54:37
>>>后
>0     2016-08-02 11:11:46
1     2016-08-02 11:31:55
2     2016-08-02 12:54:37

• 所有周期名称，对应单位及其说明如下表所示。

周期名称	单位	说明	周期名称	单位	说明
weeks	无	星期	seconds	s	秒
days	D	天	milliseconds	ms	毫秒
hours	h	小时	microseconds	us	微妙
minutes	m	分	nanoseconds	ns	纳秒

• 使用Timedelta ，可以很轻松地实现在某个时间上加减一段时间 。
• 除了使用Timedelta实现时间的平移外，还能够直接对两个时间序列进行相减，从而得出一个Timedelta。

分组聚合进行组内计算

groupby方法拆分数据

groupby方法的参数及其说明

• 提供的是分组聚合步骤中的查分功能，能根据索引或者字段对数据进行分组

DataFrame.groupby(by = None, axis = 0,level = None, as_index = True, sort = True, group_keys = True, squeeze = False, **kwargs)

参数名称	说名
by	接收list，string，mapping或generator。用于确定分组的依据
axis	接收int。表示操作的轴向，默认对列进行操作为0。对行进行操作1
level	接收ing或者索引名。代表标签所在的级别。默认为None
as_index	接收布尔，表示聚合后的聚合标签是否以DataFrame索引形式输出。默认TRUE
sort	接收布尔，表示是否对分组依据分组标签进行排序。默认True
group_keys	接收布尔，表示是否显示分组标签的名称。默认TRUE
squeeze	接收布尔，表示是否将返回的数据进行降维。默认False

groupby常用统计方法

• 用groupby分组后的数据不可常查看而是存在内存中，输出的是内存地址，用下列方法来进行统计

方法名称	说明	方法名称	书名
count	计算数目，包含缺失	cumcount	对分组中的组员进行标记，0~n-1
head	返回每组的前n个值	size	返回每组的大小
max	返回每组的最大值	min	返回每组的最小值
mean	返回每组的均值	std	返回每组的标准差
median	返回每组的中位数	sum	返回每组的和

agg和aggregate函数

• 对某个分组应用某个函数，包含内置函数或者自定义函数。
• agg和aggregate对DataFrame缠足几乎相同

DataFrame.agg(func,axis=0,*args,**kwargs)
DataFrame.aggregate(func,axis=0,*args,**kwargs)

参数名称	说明
func	接收list，dict，function。表示应用于没行或者列的函数。无默认
axis	接收0或者1.代表操作的轴向，默认0.

• 如

detail[['counts','amounts']].agg([np.sum,np.mean])

分别求出’counts’,'amounts’的和与平均数

求counts的sum与amounts的mean

detail.agg({'counts':np.sum,'amounts':np.mean})

若想求一个字段的和与平均值

data.agg({'counts':np.sum,'amounts':[np.sum,np.mean]})

apply方法聚合

apply与agg差不多但是无法像agg一样对不同字段应用不同函数获取不同结果。

DataFrame.apply(func,axis=0,broadcast=Fales,raw=False,reduce=None,args=(),**kwds)

参数名称	说明
func	接收func，表示对行/列进行应用的函数。我默认
axis	0或1，操作的轴向，默认0
broadcast	接收布尔，是否进行广播。默认否
raw	接收布尔，是否与将ndarray对象传递给函数，默认否
reduce	接收布尔或者None，表示返回值的格式，默认None

transform方法

• 本方法能够对整个DataFrame进行所有元素的操作，且只有一个参数‘func’。
• 还可对分组后的对象进行操作，实现组内差标准化等。
• 
• 

创建透视表与交叉表

pivot_table函数创建透视表

pd.pivot_table(data,values=None,index=None,columns=None,aggfunc='mean',fill_value=None,margins=False,dropna=True,margins_name='All')

参数名称	说明
data	接收DataFrame。表数据，无默认
values	字符串，指定项聚合数据的字段，默认全部None
index	接收string或者list，表示行分组键，默认None
columns	接收string或者list，列分组键
aggfunc	接收函数，表示聚合函数，默认平均’mean’
margins	接收布尔，汇总功能的开关，设置为TRUE会出现all 的行和列。默认True
dropna	接收布尔，是否删除全为NaN的列，默认False

主要调节参数

• 在不指定aggfunc时，默认用np.mean进行聚合运算，mean会自动过滤掉非空数值类型。
• 在创建透视图标的时候可以分组键index可以有多个
• values可以指定某一列，或某几列。

data = lis[['order_id','counts','amounts']]
print(pd.pivot_table(data,index='order_id'))

print(pd.pivot_table(data,index='order_id',aggfunc='sum'))

print(pd.pivot_table(lis[['order_id','dishes_name','counts','amounts']],index = ['order_id','dishes_name'],aggfunc=np.sum))

print(pd.pivot_table(lis[['order_id','dishes_name','counts','amounts']],
                    index='order_id',
                    columns='dishes_name',aggfunc=np.sum)[:5])

crosstab交叉表（交叉表是透视表的一种）

与透视表差不多
不同的就是index、columns、values都是从DataFrame取出的某一列

• 语法

pd.crosstab(index,columns,values=None,rownames=None,colnames=None,aggfunc=None,margins=False,dropna=True,normalize=False)

• 实例

print(pd.crosstab(index=lis['order_id'],columns=lis['dishes_name'],
                 values=lis['counts'],aggfunc=np.sum)[:5])