6.数据分析-Pandas_③

import pandas as pd
# pd.Series(data=None,index=None,dtype=None)

import numpy as np
import matplotlib.pyplot as plt
import random
from pylab import mpl
#显示中文字体
mpl.rcParams["font.sans-serif"] = ["SimHei"]
#设置正常显示字符
mpl.rcParams["axes.unicode_minus"] = False

---

九、高级处理-合并

9.1 pd.concat()数据合并

# pd.concat实现数据合并 
#     pd.concat([data1, data2], axis=1) 
#    按照行或列进行合并,axis=0为列索引，axis=1为行索引

#读取数据表数据
data = pd.read_csv("./2.code/data/stock_day.csv") 
p_change = data['p_change']

#离散化处理得到哑变量矩阵 dummies
bins = [-100, -7, -5, -3, 0, 3, 5, 7, 100] 
p_counts = pd.cut(p_change, bins)
dummies = pd.get_dummies(p_counts, prefix="rise")

#将两个表进行合并（通过行合并）
pd.concat([data,dummies],axis=1).head()

	open	high	close	low	volume	price_change	p_change	ma5	ma10	ma20	...	v_ma20	turnover	rise_(-100, -7]	rise_(-7, -5]	rise_(-5, -3]	rise_(-3, 0]	rise_(0, 3]	rise_(3, 5]	rise_(5, 7]	rise_(7, 100]
2018-02-27	23.53	25.88	24.16	23.53	95578.03	0.63	2.68	22.942	22.142	22.875	...	55576.11	2.39	0	0	0	0	1	0	0	0
2018-02-26	22.80	23.78	23.53	22.80	60985.11	0.69	3.02	22.406	21.955	22.942	...	56007.50	1.53	0	0	0	0	0	1	0	0
2018-02-23	22.88	23.37	22.82	22.71	52914.01	0.54	2.42	21.938	21.929	23.022	...	56372.85	1.32	0	0	0	0	1	0	0	0
2018-02-22	22.25	22.76	22.28	22.02	36105.01	0.36	1.64	21.446	21.909	23.137	...	60149.60	0.90	0	0	0	0	1	0	0	0
2018-02-14	21.49	21.99	21.92	21.48	23331.04	0.44	2.05	21.366	21.923	23.253	...	61716.11	0.58	0	0	0	0	1	0	0	0

5 rows × 22 columns

9.2 pd.merge

# pd.merge(left, right, how='inner', on=None) 
# 可以指定按照两组数据的共同键值对合并或者左右各自 
#     left : DataFrame 
#     right : 另一个DataFrame 
#     on : 指定的共同键 
#     how:按照什么方式连接

left = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'], 'key2': ['K0', 'K1', 'K0', 'K1'], 'A': ['A0', 'A1', 'A2', 'A3'], 'B': ['B0', 'B1', 'B2', 'B3']})

right = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'], 'key2': ['K0', 'K0', 'K0', 'K0'], 'C': ['C0', 'C1', 'C2', 'C3'], 'D': ['D0', 'D1', 'D2', 'D3']})

# 默认内连接 
pd.merge(left, right, on=['key1', 'key2'])

	key1	key2	A	B	C	D
0	K0	K0	A0	B0	C0	D0
1	K1	K0	A2	B2	C1	D1
2	K1	K0	A2	B2	C2	D2

# 左连接 
pd.merge(left, right, how='left', on=['key1', 'key2'])

	key1	key2	A	B	C	D
0	K0	K0	A0	B0	C0	D0
1	K0	K1	A1	B1	NaN	NaN
2	K1	K0	A2	B2	C1	D1
3	K1	K0	A2	B2	C2	D2
4	K2	K1	A3	B3	NaN	NaN

# 右连接 
pd.merge(left, right, how='right', on=['key1', 'key2'])

	key1	key2	A	B	C	D
0	K0	K0	A0	B0	C0	D0
1	K1	K0	A2	B2	C1	D1
2	K1	K0	A2	B2	C2	D2
3	K2	K0	NaN	NaN	C3	D3

# 外链接
pd.merge(left, right, how='outer', on=['key1', 'key2'])

	key1	key2	A	B	C	D
0	K0	K0	A0	B0	C0	D0
1	K0	K1	A1	B1	NaN	NaN
2	K1	K0	A2	B2	C1	D1
3	K1	K0	A2	B2	C2	D2
4	K2	K1	A3	B3	NaN	NaN
5	K2	K0	NaN	NaN	C3	D3

---

十、高级处理-交叉表与透视表

# 交叉表：交叉表用于计算一列数据对于另外一列数据的分组个数(用于统计分组频率的特殊透视表)
# pd.crosstab(value1, value2)

# 透视表：透视表是将原有的DataFrame的列分别作为行索引和列索引，然后对指定的列应用聚集函数 
# data.pivot_table(）

10.1 数据准备

#1、获取数据，将索引转换成星期几
data = pd.read_csv("./2.code/data/stock_day.csv") 
data['week'] = pd.to_datetime(data.index).weekday

# 2、假如把p_change按照大小去分个类0为界限 升的为1 降的为2
data['posi_neg'] = np.where(data['p_change'] > 0, 1, 0)

# 通过交叉表找寻两列数据的关系 
count = pd.crosstab(data['week'], data['posi_neg'])
# count

# 算数运算，先求和 
sumw = count.sum(axis=1).astype(np.float32) 
sumw

# 进行相除操作，得出比例 
pro = count.div(sumw, axis=0)
pro

posi_neg	0	1
week
0	0.504000	0.496000
1	0.419847	0.580153
2	0.462121	0.537879
3	0.492188	0.507812
4	0.464567	0.535433

10.2 查看效果

pro.plot(kind='bar', stacked=True) 
plt.show()

10.3 使用pivot_table(透视表)实现

# 通过透视表，将整个过程变成更简单一些 
data.pivot_table(['posi_neg'], index='week')

	posi_neg
week
0	0.496000
1	0.580153
2	0.537879
3	0.507812
4	0.535433

---

十一、高级处理-分组与聚合

# 应用groupby和聚合函数实现数据的分组与聚合

# DataFrame.groupby(key, as_index=False) 
#     key:分组的列数据，可以多个 
# 案例:不同颜色的不同笔的价格数据

col =pd.DataFrame({'color': ['white','red','green','red','green'], 'object': ['pen','pencil','pencil','ashtray','pen'],'price1':[5.56,4.20,1.30,0.56 ,2.75],'price2':[4.75,4.12,1.60,0.75,3.15]})
col

	color	object	price1	price2
0	white	pen	5.56	4.75
1	red	pencil	4.20	4.12
2	green	pencil	1.30	1.60
3	red	ashtray	0.56	0.75
4	green	pen	2.75	3.15

# 分组，求平均值 
col.groupby(['color'])['price1'].mean()

color
green    2.025
red      2.380
white    5.560
Name: price1, dtype: float64

col['price1'].groupby(col['color']).mean()

color
green    2.025
red      2.380
white    5.560
Name: price1, dtype: float64

# 分组，数据的结构不变 
col.groupby(['color'], as_index=False)['price1'].mean()

	color	price1
0	green	2.025
1	red	2.380
2	white	5.560

---

十二、资料网盘

百度云盘：https://pan.baidu.com/s/1puAwMn8QFGiUcrmQtnoiPw ；
提取码：echo