Pandas基础
Pandas 可以对各种数据进行运算操作，比如归并、再成形、选择，还有数据清洗和数据加工特征。
Pandas 的主要数据结构是 Series （一维数据）与 DataFrame（二维数据）：
- Series 是一种类似于一维数组的对象，它由一组数据（各种Numpy数据类型）以及一组与之相关的数据标签（即索引）组成。
- DataFrame 是一个表格型的数据结构，它含有一组有序的列，每列可以是不同的值类型（数值、字符串、布尔型值）。DataFrame 既有行索引也有列索引，它可以被看做由 Series 组成的字典（共同用一个索引）。
1.数据的属性
| 属性 | 作用 |
|---|---|
| columns | 返回数据的列名 |
| ndim | 返回int，表示数组的维数 |
| shape | 返回tuple。表述尺寸 |
| size | 返回int，表示元素总数 |
| dtypes | 返回data-type，描述元素的类型 |
| index | 返回RangeIndex(start=0, stop=最后的数值下标值, step=数据步长值) |
| values | 返回全部数据元素值 |
import pandas as pd
from sqlalchemy import create_engine
engin=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql_table('meal_order_detail1',con=engin)
#数据索引
print(data.index) #输出为：RangeIndex(start=0, stop=2779, step=1)
#数据元素
print(data.values)
'''
输出为：
[['2956' '417' '610062' ... 'NA' 'caipu/104001.jpg' '1442']
['2958' '417' '609957' ... 'NA' 'caipu/202003.jpg' '1442']
['2961' '417' '609950' ... 'NA' 'caipu/303001.jpg' '1442']
...
['6756' '774' '609949' ... 'NA' 'caipu/404005.jpg' '1138']
['6763' '774' '610014' ... 'NA' 'caipu/302003.jpg' '1138']
['6764' '774' '610017' ... 'NA' 'caipu/302006.jpg' '1138']]
'''
#数据列名
print(data.columns)
'''
输出为:
Index(['detail_id', 'order_id', 'dishes_id', 'logicprn_name',
'parent_class_name', 'dishes_name', 'itemis_add', 'counts', 'amounts',
'cost', 'place_order_time', 'discount_amt', 'discount_reason',
'kick_back', 'add_inprice', 'add_info', 'bar_code', 'picture_file',
'emp_id'],
dtype='object')
'''
#数据类型
print(data.dtypes)
'''
输出为:
detail_id object
order_id object
dishes_id object
logicprn_name object
parent_class_name object
dishes_name object
itemis_add object
counts float64
amounts float64
cost object
place_order_time datetime64[ns]
discount_amt object
discount_reason object
kick_back object
add_inprice object
add_info object
bar_code object
picture_file object
emp_id object
dtype: object
'''
#数据元素个数
print(data.size) #输出为：52801
#数据形状
print(data.shape) #输出为：(2779, 19)
#数据维度数
print(data.ndim) #输出为：2
2.索引和切片
2.1 键值索引获取某一列数据
import pandas as pd
from sqlalchemy import create_engine
engin=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql_table('meal_order_detail1',con=engin)
#数据列名
print(data.columns)
'''
输出为:
Index(['detail_id', 'order_id', 'dishes_id', 'logicprn_name',
'parent_class_name', 'dishes_name', 'itemis_add', 'counts', 'amounts',
'cost', 'place_order_time', 'discount_amt', 'discount_reason',
'kick_back', 'add_inprice', 'add_info', 'bar_code', 'picture_file',
'emp_id'],
dtype='object')
'''
#通过键值对进行索引，获取某一列的数据
print(data['dishes_name'])
'''
输出为：
0 蒜蓉生蚝
1 蒙古烤羊腿
2 大蒜苋菜
3 芝麻烤紫菜
4 蒜香包
...
2774 白饭/大碗
2775 牛尾汤
2776 意文柠檬汁
2777 金玉良缘
2778 酸辣藕丁
Name: dishes_name, Length: 2779, dtype: object
'''
2.2 键值提取基础上使用切片提取
import pandas as pd
from sqlalchemy import create_engine
engin=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql_table('meal_order_detail1',con=engin)
#数据列名
print(data.columns)
'''
输出为:
Index(['detail_id', 'order_id', 'dishes_id', 'logicprn_name',
'parent_class_name', 'dishes_name', 'itemis_add', 'counts', 'amounts',
'cost', 'place_order_time', 'discount_amt', 'discount_reason',
'kick_back', 'add_inprice', 'add_info', 'bar_code', 'picture_file',
'emp_id'],
dtype='object')
'''
#使用切片提取数据
print(data['dishes_name'][:5])
'''
0 蒜蓉生蚝
1 蒙古烤羊腿
2 大蒜苋菜
3 芝麻烤紫菜
4 蒜香包
Name: dishes_name, dtype: object
'''
print(data['dishes_id'][:5][2]) #输出为：609950
2.3 iloc使用切片索引
import pandas as pd
from sqlalchemy import create_engine
engin=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql_table('meal_order_detail1',con=engin)
#查看第三行第三列的数据值
print(data.iloc[2,2]) #输出为：609950
#查看第三行到第五行的第三列数据
print(data.iloc[2:5,2])
'''
输出为：
2 609950
3 610038
4 610003
Name: dishes_id, dtype: object
'''
2.4 loc使用切片或字符索引
import pandas as pd
from sqlalchemy import create_engine
engin=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql_table('meal_order_detail1',con=engin)
#查看第三行dishes_id的数据
print(data.loc[2,'dishes_id']) #输出为：609950
#查看第3行到第六行dishes_id的数据值
print(data.loc[2:5,'dishes_id'])
'''
输出为：
2 609950
3 610038
4 610003
5 610019
Name: dishes_id, dtype: object
'''
3.数据的增、改、删
3.1 增加数据
import pandas as pd
from sqlalchemy import create_engine
engin=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql_table('meal_order_detail1',con=engin)
#提取
data2=data.loc[data['order_id']=='458',:]
#增加数据
data2['total_price']=data2['counts']*data2['amounts']
#展示前三行
print(data2.head(3))
'''
输出为：
detail_id order_id dishes_id ... picture_file emp_id total_price
145 3411 458 610063 ... caipu/104002.jpg 1455 45.0
146 3413 458 609943 ... caipu/103004.jpg 1455 55.0
147 3418 458 609954 ... caipu/304001.jpg 1455 29.0
'''
3.2 删除数据
import pandas as pd
from sqlalchemy import create_engine
engin=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql_table('meal_order_detail1',con=engin)
data2=data.loc[data['order_id']=='458',:]
data2['total_price']=data2['counts']*data2['amounts']
print(data2.head(3))
'''
输出为：
detail_id order_id dishes_id ... picture_file emp_id total_price
145 3411 458 610063 ... caipu/104002.jpg 1455 45.0
146 3413 458 609943 ... caipu/103004.jpg 1455 55.0
147 3418 458 609954 ... caipu/304001.jpg 1455 29.0
'''
#删除数据
data2.drop(labels='total_price',axis=1,inplace=True)
print(data2.columns)
'''
输出为：
Index(['detail_id', 'order_id', 'dishes_id', 'logicprn_name',
'parent_class_name', 'dishes_name', 'itemis_add', 'counts', 'amounts',
'cost', 'place_order_time', 'discount_amt', 'discount_reason',
'kick_back', 'add_inprice', 'add_info', 'bar_code', 'picture_file',
'emp_id'],
dtype='object')
'''
print(data2.head(3))
'''
输出为：
detail_id order_id dishes_id ... bar_code picture_file emp_id
145 3411 458 610063 ... NA caipu/104002.jpg 1455
146 3413 458 609943 ... NA caipu/103004.jpg 1455
147 3418 458 609954 ... NA caipu/304001.jpg 1455
'''
3.3 修改数据
import pandas as pd
from sqlalchemy import create_engine
engin=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql_table('meal_order_detail1',con=engin)
#提取
data2=data.loc[data['order_id']=='458',:]
#修改
data2['order_id']='45800'
4.数据读取与保存
4.1 数据库文件
import pandas as pd
from sqlalchemy import create_engine
engin=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
#测试是否成功
print(engin)
#读取数据库某表格数据，不能查询
data=pd.read_sql_table('meal_order_detail1',con=engin)
#可以使用sql查询语句
data=pd.read_sql_query('select * from meal_order_detail1',con=engin)
#上面两种都可以用
data=pd.read_sql('select * from meal_order_detail1',con=engin)
data=pd.read_sql('meal_order_detail1',con=engin)
#数据存储到数据库
data.to_sql('temp',con=engin,if_exists='replace')
4.2 csv文件
import pandas as pd
#读取csv数据
data=pd.read_table('./data/meal_order_info.csv',encoding='gbk',sep=',')
#读取csv数据
data=pd.read_table('./data/meal_order_info.csv',encoding='gbk')
#存储数据
data.to_csv('./tmp/temp.csv',index=False)
4.3 excel文件
import pandas as pd
# 读取excel文件
data=pd.read_excel('./data/meal_order_detail.xlsx')
#保存数据到excel，工作表为a
data.to_excel('./tmp/temp.xlsx',sheet_name='a')
#保存数据到excel，工作表为b
data.to_excel('./tmp/temp.xlsx',sheet_name='b')
#写入方式二
with pd.ExcelWriter('./tmp/temp.xlsx') as w:
data.to_excel(w,sheet_name='a')
data.to_excel(w,sheet_name='b')
5.时间类型数据处理
5.1 转换为datatime数据
import pandas as pd
data=pd.read_csv('./data/meal_order_info.csv',encoding='gbk')
print(data['lock_time'].head())
'''
输出为：
0 2016/8/1 11:11:46
1 2016/8/1 11:31:55
2 2016/8/1 12:54:37
3 2016/8/1 13:08:20
4 2016/8/1 13:07:16
Name: lock_time, dtype: object
'''
#转数据为datetime类
pd.to_datetime(data['lock_time'])
#赋值回去作为修改
data['lock_time']=pd.to_datetime(data['lock_time'])
print(data['lock_time'].head())
'''
输出为：
0 2016-08-01 11:11:46
1 2016-08-01 11:31:55
2 2016-08-01 12:54:37
3 2016-08-01 13:08:20
4 2016-08-01 13:07:16
Name: lock_time, dtype: datetime64[ns]
'''
5.2 特定时间戳
import pandas as pd
#时间戳最小值
print(pd.Timestamp.min) #输出为：1677-09-21 00:12:43.145224193
#时间戳最大值
print(pd.Timestamp.max) #输出为：2262-04-11 23:47:16.854775807
5.3 设置时间作为索引
.to_datetime仅转换格式，.DatetimeIndex还能设置为索引
import pandas as pd
data=pd.read_csv('./data/meal_order_info.csv',encoding='gbk')
print(data['lock_time'].head())
'''
输出为：
0 2016/8/1 11:11:46
1 2016/8/1 11:31:55
2 2016/8/1 12:54:37
3 2016/8/1 13:08:20
4 2016/8/1 13:07:16
Name: lock_time, dtype: object
'''
#转换时间
datatime=pd.DatetimeIndex(data['lock_time'][0:5])
print(datatime)
'''
输出为：
DatetimeIndex(['2016-08-01 11:11:46', '2016-08-01 11:31:55',
'2016-08-01 12:54:37', '2016-08-01 13:08:20',
'2016-08-01 13:07:16'],
dtype='datetime64[ns]', name='lock_time', freq=None)
'''
6.数据合并
6.1 横向合并
import pandas as pd
from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql('meal_order_detail1',con=engine)
#因为data是dataframe类型的数据，所以要用iloc
#因为数据量太大，所以用形状代替效果
a=data.iloc[:,:10]
print(a.shape) #输出为:(2779, 10)
b=data.iloc[:,10:]
print(b.shape) #输出为:(2779, 9)
#横向合并操作
print(pd.concat([a,b],axis=1).shape) #输出为:(2779, 19)
6.2 纵向合并
表名一致的纵向合并
import pandas as pd
from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql('meal_order_detail1',con=engine)
#因为data是dataframe类型的数据，所以要用iloc
#因为数据量太大，所以用形状代替效果
a=data.iloc[:100,:]
print(a.shape) #输出为:(100, 19)
b=data.iloc[100:,:]
print(b.shape) #输出为:(2679, 19)
#纵向堆叠
#两张表列名一致的纵向堆叠
print(a.append(b).shape) #输出为:(2779, 19)
表名不一致的纵向合并
import pandas as pd
from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql('meal_order_detail1',con=engine)
#因为data是dataframe类型的数据，所以要用iloc
#因为数据量太大，所以用形状代替效果
a=data.iloc[:100,:]
print(a.shape) #输出为:(100, 19)
b=data.iloc[100:,:]
print(b.shape) #输出为:(2679, 19)
#纵向堆叠
#两张表列名不一致的纵向堆叠，当然也可以用于一致的
print(pd.concat([a,b]).shape) #输出为:(2779, 19)
6.3 根据主键合并
import pandas as pd
from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql('meal_order_detail1',con=engine)
order=pd.read_csv('./data/meal_order_info.csv',encoding='gbk')
#属性和值不一致，所以让属性和值一致
order['info_id']=data['order_id'].astype(str)
#主键合并
pd.merge(order,data,left_on='info_id',right_on='order_id')
print(order.shape) #输出为：(945, 21)
print(data.shape) #输出为：(2779, 19)
print(pd.merge(order,data,left_on='info_id',right_on='order_id').shape) #输出为：(10741, 40)
6.4 重叠合并
相同值会不变，空缺值会对应填齐
import pandas as pd
import numpy as np
dis1={'id':list(range(1,10)),
'cpu':['i7','i5',np.nan,'i7','i7','i5',np.nan,np.nan,'i5']}
a=pd.DataFrame(dis1)
dis2={'id':list(range(1,10)),
'cpu':['i7','i5','i5',np.nan,'i7','i5','i5',np.nan,'i5']}
b=pd.DataFrame(dis2)
print(a)
'''
输出为：
id cpu
0 1 i7
1 2 i5
2 3 NaN
3 4 i7
4 5 i7
5 6 i5
6 7 NaN
7 8 NaN
8 9 i5
'''
print(b)
'''
输出为：
id cpu
0 1 i7
1 2 i5
2 3 i5
3 4 NaN
4 5 i7
5 6 i5
6 7 i5
7 8 NaN
8 9 i5
'''
#重叠合并
print(a.combine_first(b))
'''
输出为：
id cpu
0 1 i7
1 2 i5
2 3 i5
3 4 i7
4 5 i7
5 6 i5
6 7 i5
7 8 NaN
8 9 i5
'''
8.构造透视表
可以进行某些计算，如求和与计数等，根据一个或多个键对数据进行聚合
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql('meal_order_detail1',con=engine)
print(data.head())
'''
输出为：
detail_id order_id dishes_id ... bar_code picture_file emp_id
0 2956 417 610062 ... NA caipu/104001.jpg 1442
1 2958 417 609957 ... NA caipu/202003.jpg 1442
2 2961 417 609950 ... NA caipu/303001.jpg 1442
3 2966 417 610038 ... NA caipu/105002.jpg 1442
4 2968 417 610003 ... NA caipu/503002.jpg 1442
'''
#创建透视表
#选取三列进行根据order_id
print(pd.pivot_table(data[['order_id','counts','amounts']],index='order_id',aggfunc=np.mean).head())
'''
输出为：
amounts counts
order_id
1002 32.000 1.0000
1003 30.125 1.2500
1004 43.875 1.0625
1008 63.000 1.0000
1011 57.700 1.0000
'''
print(pd.pivot_table(data[['order_id','counts','amounts','dishes_name']],index=['order_id','dishes_name'],aggfunc=np.mean).head())
'''
输出为：
amounts counts
order_id dishes_name
1002 凉拌菠菜 27.0 1.0
南瓜枸杞小饼干 19.0 1.0
焖猪手 58.0 1.0
独家薄荷鲜虾牛肉卷 45.0 1.0
白胡椒胡萝卜羊肉汤 35.0 1.0
'''
9.构造交叉表
计算分组频率的特殊透视表
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql('meal_order_detail1',con=engine)
print(data.head())
'''
输出为：
detail_id order_id dishes_id ... bar_code picture_file emp_id
0 2956 417 610062 ... NA caipu/104001.jpg 1442
1 2958 417 609957 ... NA caipu/202003.jpg 1442
2 2961 417 609950 ... NA caipu/303001.jpg 1442
3 2966 417 610038 ... NA caipu/105002.jpg 1442
4 2968 417 610003 ... NA caipu/503002.jpg 1442
'''
#创建透视表
#选取三列进行根据order_id
print(pd.crosstab(index=data['order_id'],columns=['dishes_name'],values=data['counts'],
dropna=True,margins=True,aggfunc=np.sum).fillna(0).head())
'''
输出为：
col_0 dishes_name All
order_id
1002 7.0 7.0
1003 10.0 10.0
1004 17.0 17.0
1008 5.0 5.0
1011 10.0 10.0
'''
10.数据去重
import pandas as pd
from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql('meal_order_detail1',con=engine)
# 我们用数据的条数来显示数据去重效果
#数据去重前
print(data['dishes_name'])
'''
输出为：
0 蒜蓉生蚝
1 蒙古烤羊腿
2 大蒜苋菜
3 芝麻烤紫菜
4 蒜香包
...
2774 白饭/大碗
2775 牛尾汤
2776 意文柠檬汁
2777 金玉良缘
2778 酸辣藕丁
Name: dishes_name, Length: 2779, dtype: object
'''
print(data['dishes_name'].count()) #去重前数据数量:2779
#数据去重操作
print(data['dishes_name'].drop_duplicates())
'''
输出为：
0 蒜蓉生蚝
1 蒙古烤羊腿
2 大蒜苋菜
3 芝麻烤紫菜
4 蒜香包
...
1024 海带结豆腐汤
1169 冰镇花螺
1411 冬瓜炒苦瓜
1659 超人气广式肠粉
2438 百里香奶油烤紅酒牛肉
Name: dishes_name, Length: 145, dtype: object
'''
#数据去重后
print(data['dishes_name'].drop_duplicates().count()) #去重后数据数量：145
11.数据相似度计算
11.1 调用corr方法计算
import pandas as pd
from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql('meal_order_detail1',con=engine)
#求相似度
print(data[['counts','amounts']].corr())
'''
输出为：
counts amounts
counts 1.000000 -0.174648
amounts -0.174648 1.000000
'''
#使用corr()求相似度的弊端，需要选择数值型特征数据，如果是类别型数据corr()会自动忽略
print(data[['counts','amounts','dishes_name']].corr())
'''
输出为：
counts amounts
counts 1.000000 -0.174648
amounts -0.174648 1.000000
'''
11.2 利用循环实现
import pandas as pd
from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql('meal_order_detail1',con=engine)
#利用循环判断两两之间的相似度关系
#新建DataFrame类型数据
sim_dis=pd.DataFrame([],index=['counts','amounts','dishes_name'],
columns=['counts','amounts','dishes_name'])
#开始双层循环
for i in ['counts','amounts','dishes_name']:
for j in ['counts','amounts','dishes_name']:
sim_dis.loc[i,j]=data[i].equals(data[j])
print(sim_dis)
'''
输出为：
counts amounts dishes_name
counts True False False
amounts False True False
dishes_name False False True
'''
12.缺失值检测和处理
12.1 检测缺失值
import pandas as pd
from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql('meal_order_detail1',con=engine)
#检测数据是否有缺少值
print(data.isnull().sum())
'''
输出为:
detail_id 0
order_id 0
dishes_id 0
logicprn_name 0
parent_class_name 0
dishes_name 0
itemis_add 0
counts 0
amounts 0
cost 0
place_order_time 0
discount_amt 0
discount_reason 0
kick_back 0
add_inprice 0
add_info 0
bar_code 0
picture_file 0
emp_id 0
dtype: int64
'''
print(data.notnull().sum())
'''
输出为:
detail_id 2779
order_id 2779
dishes_id 2779
logicprn_name 2779
parent_class_name 2779
dishes_name 2779
itemis_add 2779
counts 2779
amounts 2779
cost 2779
place_order_time 2779
discount_amt 2779
discount_reason 2779
kick_back 2779
add_inprice 2779
add_info 2779
bar_code 2779
picture_file 2779
emp_id 2779
dtype: int64
'''
12.2 多种删除缺失值方式
import pandas as pd
import numpy as np
#删除缺失值的操作
dis1={'id':list(range(1,10)),
'cpu':['i7','i7',np.nan,'i7','i7','i5',np.nan,np.nan,'i5']}
a=pd.DataFrame(dis1)
#检测是否有缺失值
print(a.isnull())
'''
输出为：
id cpu
0 False False
1 False False
2 False True
3 False False
4 False False
5 False False
6 False True
7 False True
8 False False
'''
#删除含有缺失值的数据
print(a.dropna())
'''
输出为：
id cpu
0 1 i7
1 2 i7
3 4 i7
4 5 i7
5 6 i5
8 9 i5
'''
#选定某一列其中有缺失值我们才进行剔除
print(a.dropna(subset=['id']))
'''
输出为：
id cpu
0 1 i7
1 2 i7
2 3 NaN
3 4 i7
4 5 i7
5 6 i5
6 7 NaN
7 8 NaN
8 9 i5
'''
#删除含有缺失值的整个特征
print(a.dropna(axis=1))
'''
输出为：
id
0 1
1 2
2 3
3 4
4 5
5 6
6 7
7 8
8 9
'''
12.3 缺失值填充
均值填充，与数据量大的元素填充
import pandas as pd
import numpy as np
#删除缺失值的操作
dis1={'id':list(range(1,10)),
'cpu':['i7','i7',np.nan,'i7','i7','i5',np.nan,np.nan,'i5']}
a=pd.DataFrame(dis1)
print(a)
'''
输出为：
id cpu
0 1 i7
1 2 i7
2 3 NaN
3 4 i7
4 5 i7
5 6 i5
6 7 NaN
7 8 NaN
8 9 i5
'''
#看值的数量统计
#cpu这一列的i7与i5的个数统计，从大到小排列
a['cpu'].value_counts()
#进行元素替换，填充i7
print(a['cpu'].fillna(a['cpu'].value_counts().index[0]))
'''
输出为：
0 i7
1 i7
2 i7
3 i7
4 i7
5 i5
6 i7
7 i7
8 i5
Name: cpu, dtype: object
'''
dis2={'id':list(range(1,10)),
'cpu':[7,7,np.nan,7,6,5,np.nan,np.nan,5]}
b=pd.DataFrame(dis2)
print(b)
'''
输出为：
id cpu
0 1 7.0
1 2 7.0
2 3 NaN
3 4 7.0
4 5 6.0
5 6 5.0
6 7 NaN
7 8 NaN
8 9 5.0
'''
#采用均值替换元素
print(b['cpu'].fillna(b['cpu'].mean()))
'''
输出为：
0 7.000000
1 7.000000
2 6.166667
3 7.000000
4 6.000000
5 5.000000
6 6.166667
7 6.166667
8 5.000000
Name: cpu, dtype: float64
'''
13.异常值检测与处理
13.1 利用箱线图检测
import pandas as pd
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql('meal_order_detail1',con=engine)
#使用箱型图判断异常值
p=plt.boxplot(data['counts'])
plt.show()
#获取异常值数据
print(p['fliers'][0].get_ydata())
'''
输出为：
[ 2. 2. 2. 2. 5. 2. 2. 2. 2. 2. 3. 2. 2. 2. 2. 4. 2. 4.
4. 4. 2. 4. 10. 4. 6. 2. 6. 6. 8. 2. 2. 2. 6. 6. 4. 3.
4. 6. 2. 6. 6. 2. 8. 3. 2. 2. 2. 4. 2. 2. 2. 2. 3. 6.
8. 2. 2. 2. 2. 5. 2. 2. 5. 3. 4. 2. 3. 2. 2. 4. 8. 2.
2. 3. 3. 2. 2. 2. 4. 4. 2. 2. 2. 4. 6. 2. 3. 3. 3. 2.
2. 2. 2. 2. 2. 3. 2. 3. 3. 2. 3. 2. 4. 6. 2. 2. 2. 2.
2. 2. 2. 2. 2. 2. 4. 7. 2. 2. 4. 8. 8. 4. 3. 3. 3. 2.
2. 2. 2. 2. 2. 3. 4. 6. 7. 2. 2. 2. 2. 2. 2.]
'''
13.2 利用循环检测
import pandas as pd
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql('meal_order_detail1',con=engine)
#使用3setae判断异常值
u=data['counts'].mean()
o=data['counts'].std()
#求出在这个范围外的值，apply函数主要用于对DataFrame中的行或者列进行特定的函数计算。
ind=data['counts'].apply(lambda x:x>u+3*o or x<u-3*o)
print(ind)
'''
输出为：
0 False
1 False
2 False
3 False
4 False
...
2774 False
2775 False
2776 False
2777 False
2778 False
Name: counts, Length: 2779, dtype: bool
'''
#定位这些元素出来
print(data.loc[ind,'counts'])
'''
输出为：
94 5.0
176 3.0
238 4.0
270 4.0
346 4.0
...
2370 3.0
2458 3.0
2459 4.0
2461 6.0
2501 7.0
Name: counts, Length: 62, dtype: float64
'''
14.数据标准化与离散化
14.1 离差标准化
import pandas as pd
data=pd.read_csv('./data/detail.csv',encoding='gbk')
print(data[['counts','amounts']].head())
'''
输出为：
counts amounts
0 1 49
1 1 48
2 1 30
3 1 25
4 1 13
'''
#设计离差标准化函数
def MinMaxScale(data):
return ((data-data.min())/(data.max()-data.min()))
a=MinMaxScale(data['counts'])
b=MinMaxScale(data['amounts'])
print(pd.concat([a,b],axis=1).head())
'''
counts amounts
0 0.0 0.271186
1 0.0 0.265537
2 0.0 0.163842
3 0.0 0.135593
4 0.0 0.067797
'''
14.2 标准差标准化
import pandas as pd
data=pd.read_csv('./data/detail.csv',encoding='gbk')
print(data[['counts','amounts']].head())
'''
输出为：
counts amounts
0 1 49
1 1 48
2 1 30
3 1 25
4 1 13
'''
#设计标准差标准化函数
def StandScale(data):
return (data-data.mean())/data.std()
a=StandScale(data['counts'])
b=StandScale(data['amounts'])
print(pd.concat([a,b],axis=1).head())
'''
输出为：
counts amounts
0 -0.177571 0.116671
1 -0.177571 0.088751
2 -0.177571 -0.413826
3 -0.177571 -0.553431
4 -0.177571 -0.888482
'''
14.3 小数定标准化
import pandas as pd
import numpy as np
data=pd.read_csv('./data/detail.csv',encoding='gbk')
print(data[['counts','amounts']].head())
'''
输出为：
counts amounts
0 1 49
1 1 48
2 1 30
3 1 25
4 1 13
'''
#设计小数定标标准化函数
def DecimalScale(data):
return data/10**(np.ceil(np.log10(data.abs().max())))
a=DecimalScale(data['counts'])
b=DecimalScale(data['amounts'])
print(pd.concat([a,b],axis=1).head())
'''
输出为：
counts amounts
0 0.1 0.049
1 0.1 0.048
2 0.1 0.030
3 0.1 0.025
4 0.1 0.013
'''
14.4 调用cut方法离散化
import pandas as pd
import numpy as np
data=pd.read_csv('./data/detail.csv',encoding='gbk')
print(data['amounts'].head())
'''
0 49
1 48
2 30
3 25
4 13
'''
print(data['amounts'].value_counts().head())
'''
输出为：
35 935
48 516
58 460
10 414
29 408
'''
#使用等宽法进行离散化处理
print(pd.cut(data['amounts'],bins=5).head())
'''
输出为：
0 (36.4, 71.8]
1 (36.4, 71.8]
2 (0.823, 36.4]
3 (0.823, 36.4]
4 (0.823, 36.4]
Name: amounts, dtype: category
Categories (5, interval[float64, right]): [(0.823, 36.4] < (36.4, 71.8] < (71.8, 107.2] <
(107.2, 142.6] < (142.6, 178.0]]
'''
#展示效果
print(pd.cut(data['amounts'],bins=5).value_counts().head())
'''
输出为：
(0.823, 36.4] 5461
(36.4, 71.8] 3157
(71.8, 107.2] 839
(142.6, 178.0] 426
(107.2, 142.6] 154
'''
14.5 使用自定义函数离散化
import pandas as pd
import numpy as np
data=pd.read_csv('./data/detail.csv',encoding='gbk')
print(data['amounts'].head())
'''
0 49
1 48
2 30
3 25
4 13
'''
#使用等频法进行离散化处理
def samefreq(data,k):
w=data.quantile(np.arange(0,1+1/k,1/k))
return pd.cut(data,w)
print(samefreq(data['amounts'],k=5))
'''
输出为：
0 (39.0, 58.0]
1 (39.0, 58.0]
2 (18.0, 32.0]
3 (18.0, 32.0]
4 (1.0, 18.0]
...
10032 (32.0, 39.0]
10033 (32.0, 39.0]
10034 (32.0, 39.0]
10035 (1.0, 18.0]
10036 (18.0, 32.0]
Name: amounts, Length: 10037, dtype: category
Categories (5, interval[float64, right]): [(1.0, 18.0] < (18.0, 32.0] < (32.0, 39.0] <
(39.0, 58.0] < (58.0, 178.0]]
'''
print(samefreq(data['amounts'],k=5).value_counts())
'''
输出为：
(18.0, 32.0] 2107
(39.0, 58.0] 2080
(32.0, 39.0] 1910
(1.0, 18.0] 1891
(58.0, 178.0] 1863
Name: amounts, dtype: int64
'''
15.数据的计算
import pandas as pd
from sqlalchemy import create_engine
engin=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql_query('select * from meal_order_detail1',con=engin)
data2=data.loc[data['order_id']=='458',:]
#求平均值
print(data2.mean())
'''
输出为：
detail_id 2.436672e+54
order_id 3.274703e+40
dishes_id 4.357597e+82
itemis_add 0.000000e+00
counts 1.000000e+00
amounts 5.000000e+01
add_inprice 0.000000e+00
emp_id 1.039390e+54
dtype: float64
'''
#求最小值
print(data2.min())
'''
输出为：
detail_id 3411
order_id 458
dishes_id 609939
logicprn_name NA
parent_class_name NA
dishes_name 五香酱驴肉
itemis_add 0
counts 1.0
amounts 10.0
cost NA
place_order_time 2016-08-01 19:27:00
discount_amt NA
discount_reason NA
kick_back NA
add_inprice 0
add_info NA
bar_code NA
picture_file caipu/102004.jpg
emp_id 1455
dtype: object
'''
#返回所有数值计算结果
print(data2.describe())
'''
输出为：
counts amounts
count 14.0 14.000000
mean 1.0 50.000000
std 0.0 36.746533
min 1.0 10.000000
25% 1.0 29.750000
50% 1.0 46.500000
75% 1.0 54.250000
max 1.0 158.000000
'''
#返回类别数据
print(data['dishes_name'].value_counts())
'''
输出为：
白饭/大碗 92
凉拌菠菜 77
谷稻小庄 72
麻辣小龙虾 65
白饭/小碗 60
..
三丝鳝鱼 2
咖啡奶香面包 2
铁板牛肉 2
冰镇花螺 1
百里香奶油烤紅酒牛肉 1
Name: dishes_name, Length: 145, dtype: int64
'''
16.数据分组聚合
import pandas as pd
from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')
data=pd.read_sql('meal_order_detail1',con=engine)
print(data)
'''
输出为：
detail_id order_id dishes_id ... bar_code picture_file emp_id
0 2956 417 610062 ... NA caipu/104001.jpg 1442
1 2958 417 609957 ... NA caipu/202003.jpg 1442
2 2961 417 609950 ... NA caipu/303001.jpg 1442
...............................................................
2777 6763 774 610014 ... NA caipu/302003.jpg 1138
2778 6764 774 610017 ... NA caipu/302006.jpg 1138
[2779 rows x 19 columns]
'''
#根据order_id进行聚合
print(data.groupby(by='order_id').head())
'''
输出为：
detail_id order_id dishes_id ... bar_code picture_file emp_id
0 2956 417 610062 ... NA caipu/104001.jpg 1442
1 2958 417 609957 ... NA caipu/202003.jpg 1442
2 2961 417 609950 ... NA caipu/303001.jpg 1442
...............................................................
2774 6750 774 610011 ... NA caipu/601005.jpg 1138
2775 6742 774 609996 ... NA caipu/201006.jpg 1138
[1355 rows x 19 columns]
'''
#提取三列数据根据order_id进行聚合
print(data[['order_id','counts','amounts']].groupby(by='order_id').head())
'''
输出为：
order_id counts amounts
0 417 1.0 49.0
1 417 1.0 48.0
2 417 1.0 30.0
.....................
2774 774 1.0 10.0
2775 774 1.0 40.0
[1355 rows x 3 columns]
'''
17.参考文章
[Pandas数据合并与拼接的5种方法 - 云+社区 - 腾讯云](https://cloud.tencent.com/developer/article/1640799#:~:text=Pandas数据合并与拼接的5种方法 1 一、DataFrame.... 2 二、DataFrame.merge：类似 vlookup 3,三、DataFrame.join：主要用于索引上的合并 4 四、Series.append：纵向追加Series 5 五、DataFrame.... 6 总结)
[pandas文件读取与存储_IT之一小佬的博客-CSDN博客 ](https://blog.csdn.net/weixin_44799217/article/details/113954597#:~:text=pandas文件读取与存储,我们的数据大部分存在于文件当中，所以pandas会支持复杂的IO操作，pandas的API支持众多的文件格式，如CSV、SQL、XLS、JSON、HDF5。 注：最常用的HDF5和CSV文件)
使用pandas模块实现数据的标准化_一个回和的博客-CSDN ...
【创作不易，望点赞收藏，若有疑问，请留言，谢谢】
