10分钟学会Pandas 由xiaoshao创建,最终由qxiao更新于2024-06-11 08:57 被浏览 1267 用户
SELECT date, open, high, low, close
FROM bar1d_CN_STOCK_A
WHERE instrument = '000005.SZA'
AND date BETWEEN '2017-01-06' AND '2017-02-10'
ORDER BY date;
10分钟学会Pandas
Pandas最初被作为金融数据分析工具而开发出来,在金融领域被广泛使用。Pandas纳入了大量库和一些标准的数据模型,提供了高效地操作大型数据集所需的工具、函数和方法。
本文是针对pandas新手的快速入门学习指南。你可以在 AI量化平台-编写策略 里,一步一步的学习和实践。
# 导入库
import pandas as pd
import numpy as np
Pandas数据结构
主要数据结构:Series和DataFrame,Series是一种类似于一维数组的对象,它由一组数据以及一组与之相关的数据标签(即索引)组成。DataFrame是一个表格型 的数据结构,可以简单地理解为是由多个具有相同的索引的Series组成。DataFrame是金融数据分析中特别常用的数据结构,也是我们平台最常遇到的数据结构。
# 创建Series,以日期为索引
dates = pd.date_range('2/6/2017', periods=5, freq='D')
s = pd.Series([1,3,5,np.nan,6,],index=dates)
s
2017-02-06 1.0
2017-02-07 3.0
2017-02-08 5.0
2017-02-09 NaN
2017-02-10 6.0
Freq: D, dtype: float64
# 创建DataFrame,以日期为索引
df = pd.DataFrame(np.random.randn(5,4), index=dates, columns=list('ABCD'))
df
A
B
C
D
2017-02-06
-1.738581
-1.750260
-0.532282
-0.409738
2017-02-07
-0.871625
0.188268
-0.689427
1.128257
2017-02-08
1.504632
-0.229489
-2.775763
1.867307
2017-02-09
-1.753426
1.252952
0.053105
0.870436
2017-02-10
-1.162485
-0.376172
1.548791
-0.928139
sql=f""
SELECT date, open, high, low, close
FROM bar1d_CN_STOCK_A
WHERE instrument = '000005.SZA'
AND date BETWEEN '2017-01-06' AND '2017-01-16'
ORDER BY date;""
# 选择某一列数据,它会返回一个Series,等同于df.close:
df['close']
df
date
instrument
open
high
low
close
2017-01-06
000005.SZA
64.502495
66.077988
64.224464
65.151230
2017-01-09
000005.SZA
64.502495
65.892639
64.409821
65.521935
2017-01-10
000005.SZA
65.521935
65.614609
64.965874
64.965874
2017-01-11
000005.SZA
64.965874
64.965874
63.575737
63.575737
2017-01-12
000005.SZA
63.205032
63.761089
62.741653
62.927006
2017-01-13
000005.SZA
62.927006
63.205032
61.907570
61.907570
2017-01-16
000005.SZA
62.185596
62.278271
56.810387
59.683346
查看数据
# 显示DataFrame前几行(默认是5)的数据
df.head()
date
instrument
open
high
low
close
2017-01-06
000005.SZA
64.502495
66.077988
64.224464
65.151230
2017-01-09
000005.SZA
64.502495
65.892639
64.409821
65.521935
2017-01-10
000005.SZA
65.521935
65.614609
64.965874
64.965874
2017-01-11
000005.SZA
64.965874
64.965874
63.575737
63.575737
2017-01-12
000005.SZA
63.205032
63.761089
62.741653
62.927006
# 显示DataFrame后行的数据
df.tail(2)
date
instrument
open
high
low
close
2017-01-13
000005.SZA
62.927006
63.205032
61.907570
61.907570
2017-01-16
000005.SZA
62.185596
62.278271
56.810387
59.683346
显示索引、列名以及底层的numpy数据
# 数据索引
df.index
# output:
DatetimeIndex(['2017-01-06', '2017-01-09', '2017-01-10', '2017-01-11',
'2017-01-12', '2017-01-13', '2017-01-16'],
dtype='datetime64[ns]', name='date', freq=None)
# 数据列名
df.columns
# output:
Index(['instrument', 'open', 'high', 'low', 'close'], dtype='object')
# 底层numpy数据
df.values
# output:
array([['000005.SZA', 64.50249481201172, 66.07798767089844,
64.2244644165039, 65.15122985839844],
['000005.SZA', 64.50249481201172, 65.89263916015625,
64.40982055664062, 65.52193450927734],
['000005.SZA', 65.52193450927734, 65.61460876464844,
64.96587371826172, 64.96587371826172],
['000005.SZA', 64.96587371826172, 64.96587371826172,
63.57573699951172, 63.57573699951172],
['000005.SZA', 63.20503234863281, 63.76108932495117,
62.74165344238281, 62.927005767822266],
['000005.SZA', 62.927005767822266, 63.20503234863281,
61.907569885253906, 61.907569885253906],
['000005.SZA', 62.18559646606445, 62.27827072143555,
56.810386657714844, 59.683345794677734]], dtype=object)
# 数据类型
df.dtypes
# output:
instrument object
open float32
high float32
low float32
close float32
dtype: object
转置、排序、偏移
# 转置
df.T
date
2017-01-06 00: 00: 00
2017-01-09 00: 00: 00
2017-01-10 00: 00: 00
2017-01-11 00: 00: 00
2017-01-12 00: 00: 00
2017-01-13 00: 00: 00
2017-01-16 00: 00: 00
instrument
000005.SZA
000005.SZA
000005.SZA
000005.SZA
000005.SZA
000005.SZA
000005.SZA
open
64.5025
64.5025
65.5219
64.9659
63.205
62.927
62.1856
high
66.078
65.8926
65.6146
64.9659
63.7611
63.205
62.2783
low
64.2245
64.4098
64.9659
63.5757
62.7417
61.9076
56.8104
close
65.1512
65.5219
64.9659
63.5757
62.927
61.9076
59.6833
# 按轴进行排序
df.sort_index(axis=1, ascending=False)
date
open
low
instrument
high
close
2017-01-06
64.502495
64.224464
000005.SZA
66.077988
65.151230
2017-01-09
64.502495
64.409821
000005.SZA
65.892639
65.521935
2017-01-10
65.521935
64.965874
000005.SZA
65.614609
64.965874
2017-01-11
64.965874
63.575737
000005.SZA
64.965874
63.575737
2017-01-12
63.205032
62.741653
000005.SZA
63.761089
62.927006
2017-01-13
62.927006
61.907570
000005.SZA
63.205032
61.907570
2017-01-16
62.185596
56.810387
000005.SZA
62.278271
59.683346
# 按值进行排序
df.sort_values(by='close',ascending=False) # ascending默认升序,ascending=False降序
date
instrument
open
high
low
close
2017-01-09
000005.SZA
64.502495
65.892639
64.409821
65.521935
2017-01-06
000005.SZA
64.502495
66.077988
64.224464
65.151230
2017-01-10
000005.SZA
65.521935
65.614609
64.965874
64.965874
2017-01-11
000005.SZA
64.965874
64.965874
63.575737
63.575737
2017-01-12
000005.SZA
63.205032
63.761089
62.741653
62.927006
2017-01-13
000005.SZA
62.927006
63.205032
61.907570
61.907570
2017-01-16
000005.SZA
62.185596
62.278271
56.810387
59.683346
shift数据偏移(可理解为回溯)
# 利用pct_change计算每日收益
sql=f""SELECT date, close
FROM bar1d_CN_STOCK_A
WHERE instrument = '000001.SZA'
AND date BETWEEN '2010-01-01' AND '2016-12-31'
ORDER BY date;""
rets.head()
date
2010-01-04 NaN
2010-01-05 -0.017292
2010-01-06 -0.017167
2010-01-07 -0.010917
2010-01-08 -0.002208
Name: close, dtype: float32
# 计算每日收益,等价于pct_change函数
daily_returns = df / df.shift(1) -1 # 正数表示向前偏移,负数表示向后偏移
daily_returns.head()
date
2010-01-04 NaN
2010-01-05 -0.017292
2010-01-06 -0.017167
2010-01-07 -0.010917
2010-01-08 -0.002208
Name: close, dtype: float32
数据选择
选取
sql=""WITH df1 AS(
SELECT date, open, high, low, close
FROM bar1d_CN_STOCK_A
WHERE instrument = '000005.SZA'
AND date BETWEEN '2017-01-06' AND '2017-02-10'
ORDER BY date;)""
df = df.set_index('date')
# 选择某一列数据,它会返回一个Series,等同于df.close:
df['close']
date
2017-01-06 65.151230
2017-01-09 65.521935
2017-01-10 64.965874
2017-01-11 63.575737
2017-01-12 62.927006
2017-01-13 61.907570
2017-01-16 59.683346
2017-01-17 60.054047
2017-01-18 60.702782
2017-01-19 59.683346
2017-01-20 60.239399
2017-01-23 60.517429
2017-01-24 60.332077
2017-01-25 60.424751
2017-01-26 60.795456
2017-02-03 60.517429
2017-02-06 60.795456
2017-02-07 61.166161
2017-02-08 61.073483
2017-02-09 61.444187
2017-02-10 61.536865
Name: close, dtype: float32
# 获取2017-02-06到2017-02-09的所有数据
df.loc['20170206':'20170209']
date instrument open high low close
2017-02-06 000005.SZA 60.517429 60.795456 60.146725 60.795456
2017-02-07 000005.SZA 60.795456 61.629539 60.424751 61.166161
2017-02-08 000005.SZA 60.980808 61.166161 60.424751 61.073483
2017-02-09 000005.SZA 61.073483 61.536865 60.888130 61.444187
# 获取2017-02-06的所有数据
df.loc['2017-02-06':'2017-02-06']
date
instrument
open
high
low
close
2017-02-06
000005.SZA
60.517429
60.795456
60.146725
60.795456
# 获取2017-02-06的开盘价和收盘价数据
df.loc['2017-02-06':'2017-02-06'][['open','close']]
date
open
close
2017-02-06
60.517429
60.795456
# 获取2017-02-06和2017-02-09间断两天的开盘价和收盘价数据
df.loc['2017-02-06':'2017-02-09'][['open','close']]
date
open
close
2017-02-06
60.517429
60.795456
2017-02-07
60.795456
61.166161
2017-02-08
60.980808
61.073483
2017-02-09
61.073483
61.444187
# 获取2017-02-06的开盘价,返回DataFrame
df.loc['2017-02-06':'2017-02-06'][['open']]
date open
2017-02-06 60.517429
# 获取2017-02-06的收盘价,返回标量
df.loc['2017-02-06','close']
60.517429
布尔索引
# 获取最高价大于64的交易日数据
df[df.high > 64]
date
instrument
open
high
low
close
2017-01-06
000005.SZA
64.502495
66.077988
64.224464
65.151230
2017-01-09
000005.SZA
64.502495
65.892639
64.409821
65.521935
2017-01-10
000005.SZA
65.521935
65.614609
64.965874
64.965874
2017-01-11
000005.SZA
64.965874
64.965874
63.575737
63.575737
2017-01-18
000005.SZA
60.888130
64.687851
60.517429
60.702782
where 索引
# 选出大于60的元素
df[df > 60]
date
instrument
open
high
low
close
2017-01-06
000005.SZA
64.502495
66.077988
64.224464
65.151230
2017-01-09
000005.SZA
64.502495
65.892639
64.409821
65.521935
2017-01-10
000005.SZA
65.521935
65.614609
64.965874
64.965874
2017-01-11
000005.SZA
64.965874
64.965874
63.575737
63.575737
2017-01-12
000005.SZA
63.205032
63.761089
62.741653
62.927006
2017-01-13
000005.SZA
62.927006
63.205032
61.907570
61.907570
2017-01-16
000005.SZA
62.185596
62.278271
NaN
NaN
2017-01-17
000005.SZA
NaN
60.424751
NaN
60.054047
2017-01-18
000005.SZA
60.888130
64.687851
60.517429
60.702782
2017-01-19
000005.SZA
NaN
60.980808
NaN
NaN
2017-01-20
000005.SZA
60.239399
60.610104
NaN
60.239399
2017-01-23
000005.SZA
60.332077
60.888130
60.146725
60.517429
2017-01-24
000005.SZA
60.610104
60.610104
60.239399
60.332077
2017-01-25
000005.SZA
60.424751
60.517429
NaN
60.424751
2017-01-26
000005.SZA
60.517429
60.795456
60.424751
60.795456
2017-02-03
000005.SZA
60.888130
60.888130
60.239399
60.517429
2017-02-06
000005.SZA
60.517429
60.795456
60.146725
60.795456
2017-02-07
000005.SZA
60.795456
61.629539
60.424751
61.166161
2017-02-08
000005.SZA
60.980808
61.166161
60.424751
61.073483
2017-02-09
000005.SZA
61.073483
61.536865
60.888130
61.444187
2017-02-10
000005.SZA
61.629539
61.629539
61.258835
61.536865
丢弃数据
# 丢弃行
df.drop([pd.Timestamp('2017-01-06')]) # 丢弃行
date
instrument
open
high
low
close
2017-01-09
000005.SZA
64.502495
65.892639
64.409821
65.521935
2017-01-10
000005.SZA
65.521935
65.614609
64.965874
64.965874
2017-01-11
000005.SZA
64.965874
64.965874
63.575737
63.575737
2017-01-12
000005.SZA
63.205032
63.761089
62.741653
62.927006
2017-01-13
000005.SZA
62.927006
63.205032
61.907570
61.907570
2017-01-16
000005.SZA
62.185596
62.278271
56.810387
59.683346
2017-01-17
000005.SZA
59.497993
60.424751
58.385880
60.054047
2017-01-18
000005.SZA
60.888130
64.687851
60.517429
60.702782
2017-01-19
000005.SZA
59.776020
60.980808
59.497993
59.683346
2017-01-20
000005.SZA
60.239399
60.610104
59.776020
60.239399
2017-01-23
000005.SZA
60.332077
60.888130
60.146725
60.517429
2017-01-24
000005.SZA
60.610104
60.610104
60.239399
60.332077
2017-01-25
000005.SZA
60.424751
60.517429
59.961372
60.424751
2017-01-26
000005.SZA
60.517429
60.795456
60.424751
60.795456
2017-02-03
000005.SZA
60.888130
60.888130
60.239399
60.517429
2017-02-06
000005.SZA
60.517429
60.795456
60.146725
60.795456
2017-02-07
000005.SZA
60.795456
61.629539
60.424751
61.166161
2017-02-08
000005.SZA
60.980808
61.166161
60.424751
61.073483
2017-02-09
000005.SZA
61.073483
61.536865
60.888130
61.444187
2017-02-10
000005.SZA
61.629539
61.629539
61.258835
61.536865
# 丢弃列 ,del df['open'],原地修改
df.drop('open',axis=1) # 非原地修改
date
instrument
high
low
close
2017-01-06
000005.SZA
66.077988
64.224464
65.151230
2017-01-09
000005.SZA
65.892639
64.409821
65.521935
2017-01-10
000005.SZA
65.614609
64.965874
64.965874
2017-01-11
000005.SZA
64.965874
63.575737
63.575737
2017-01-12
000005.SZA
63.761089
62.741653
62.927006
2017-01-13
000005.SZA
63.205032
61.907570
61.907570
2017-01-16
000005.SZA
62.278271
56.810387
59.683346
2017-01-17
000005.SZA
60.424751
58.385880
60.054047
2017-01-18
000005.SZA
64.687851
60.517429
60.702782
2017-01-19
000005.SZA
60.980808
59.497993
59.683346
2017-01-20
000005.SZA
60.610104
59.776020
60.239399
2017-01-23
000005.SZA
60.888130
60.146725
60.517429
2017-01-24
000005.SZA
60.610104
60.239399
60.332077
2017-01-25
000005.SZA
60.517429
59.961372
60.424751
2017-01-26
000005.SZA
60.795456
60.424751
60.795456
2017-02-03
000005.SZA
60.888130
60.239399
60.517429
2017-02-06
000005.SZA
60.795456
60.146725
60.795456
2017-02-07
000005.SZA
61.629539
60.424751
61.166161
2017-02-08
000005.SZA
61.166161
60.424751
61.073483
2017-02-09
000005.SZA
61.536865
60.888130
61.444187
2017-02-10
000005.SZA
61.629539
61.258835
61.536865
对齐、合并、赋值
sql=""WITH df1 AS (
SELECT
date,
open,
high,
low,
close
FROM
bar1d_CN_STOCK_A
WHERE
instrument = '000005.SZA'
AND date BETWEEN '2017-02-01' AND '2017-02-10'
) , df2 AS (
SELECT
date,
market_cap,
pe_ttm
FROM
market_value_CN_STOCK_A
WHERE
instrument = '000005.SZA'
AND date BETWEEN '2017-01-01' AND '2017-02-10'
)""
# 取索引的交集进行合并
df1.merge(df2,left_index=True,right_index=True,how='inner')
date
instrument_x
open
high
low
close
instrument_y
market_cap
pe_ttm
2017-02-03
000005.SZA
60.888130
60.888130
60.239399
60.517429
000005.SZA
6.912246e+09
63.786068
2017-02-06
000005.SZA
60.517429
60.795456
60.146725
60.795456
000005.SZA
6.944002e+09
64.079109
2017-02-07
000005.SZA
60.795456
61.629539
60.424751
61.166161
000005.SZA
6.986343e+09
64.469841
2017-02-08
000005.SZA
60.980808
61.166161
60.424751
61.073483
000005.SZA
6.975758e+09
64.372154
2017-02-09
000005.SZA
61.073483
61.536865
60.888130
61.444187
000005.SZA
7.018099e+09
64.762886
2017-02-10
000005.SZA
61.629539
61.629539
61.258835
61.536865
000005.SZA
7.028685e+09
64.860565
# 取索引的并集进行合并
df1.merge(df2,left_index=True,right_index=True,how='outer')
date
instrument_x
open
high
low
close
instrument_y
market_cap
pe_ttm
2017-01-03
NaN
NaN
NaN
NaN
NaN
000005.SZA
7.229807e+09
66.716515
2017-01-04
NaN
NaN
NaN
NaN
NaN
000005.SZA
7.420343e+09
68.474785
2017-01-05
NaN
NaN
NaN
NaN
NaN
000005.SZA
7.378002e+09
68.084053
2017-01-06
NaN
NaN
NaN
NaN
NaN
000005.SZA
7.441514e+09
68.670143
2017-01-09
NaN
NaN
NaN
NaN
NaN
000005.SZA
7.483855e+09
69.060875
2017-01-10
NaN
NaN
NaN
NaN
NaN
000005.SZA
7.420343e+09
68.474785
2017-01-11
NaN
NaN
NaN
NaN
NaN
000005.SZA
7.261563e+09
67.009560
2017-01-12
NaN
NaN
NaN
NaN
NaN
000005.SZA
7.187465e+09
66.325790
2017-01-13
NaN
NaN
NaN
NaN
NaN
000005.SZA
7.071026e+09
65.251289
2017-01-16
NaN
NaN
NaN
NaN
NaN
000005.SZA
6.816977e+09
62.906933
2017-01-17
NaN
NaN
NaN
NaN
NaN
000005.SZA
6.859319e+09
63.297661
2017-01-18
NaN
NaN
NaN
NaN
NaN
000005.SZA
6.933416e+09
63.981430
2017-01-19
NaN
NaN
NaN
NaN
NaN
000005.SZA
6.816977e+09
62.906933
2017-01-20
NaN
NaN
NaN
NaN
NaN
000005.SZA
6.880489e+09
63.493023
2017-01-23
NaN
NaN
NaN
NaN
NaN
000005.SZA
6.912246e+09
63.786068
2017-01-24
NaN
NaN
NaN
NaN
NaN
000005.SZA
6.891075e+09
63.590702
2017-01-25
NaN
NaN
NaN
NaN
NaN
000005.SZA
6.901660e+09
63.688385
2017-01-26
NaN
NaN
NaN
NaN
NaN
000005.SZA
6.944002e+09
64.079109
2017-02-03
000005.SZA
60.888130
60.888130
60.239399
60.517429
000005.SZA
6.912246e+09
63.786068
2017-02-06
000005.SZA
60.517429
60.795456
60.146725
60.795456
000005.SZA
6.944002e+09
64.079109
2017-02-07
000005.SZA
60.795456
61.629539
60.424751
61.166161
000005.SZA
6.986343e+09
64.469841
2017-02-08
000005.SZA
60.980808
61.166161
60.424751
61.073483
000005.SZA
6.975758e+09
64.372154
2017-02-09
000005.SZA
61.073483
61.536865
60.888130
61.444187
000005.SZA
7.018099e+09
64.762886
2017-02-10
000005.SZA
61.629539
61.629539
61.258835
61.536865
000005.SZA
7.028685e+09
64.860565
# 通过标签赋值
df1.loc['2017-02-03','market_cap'] = 0
date
instrument
open
high
low
close
market_cap
2017-02-03
000005.SZA
60.888130
60.888130
60.239399
60.517429
0.0
2017-02-06
000005.SZA
60.517429
60.795456
60.146725
60.795456
NaN
2017-02-07
000005.SZA
60.795456
61.629539
60.424751
61.166161
NaN
2017-02-08
000005.SZA
60.980808
61.166161
60.424751
61.073483
NaN
2017-02-09
000005.SZA
61.073483
61.536865
60.888130
61.444187
NaN
2017-02-10
000005.SZA
61.629539
61.629539
61.258835
61.536865
NaN
# 通过位置赋值
df1.iat[0,0] ='600519.SHA'
df1
date
instrument
open
high
low
close
market_cap
2017-02-03
600519.SHA
60.888130
60.888130
60.239399
60.517429
0.0
2017-02-06
000005.SZA
60.517429
60.795456
60.146725
60.795456
NaN
2017-02-07
000005.SZA
60.795456
61.629539
60.424751
61.166161
NaN
2017-02-08
000005.SZA
60.980808
61.166161
60.424751
61.073483
NaN
2017-02-09
000005.SZA
61.073483
61.536865
60.888130
61.444187
NaN
2017-02-10
000005.SZA
61.629539
61.629539
61.258835
61.536865
NaN
# 通过where操作来赋值
df1[df1['market_cap'] == 0] = 111
df1.head(4)
date
instrument
open
high
low
close
market_cap
2017-02-03
111
111.000000
111.000000
111.000000
111.000000
111.0
2017-02-06
000005.SZA
60.517429
60.795456
60.146725
60.795456
NaN
2017-02-07
000005.SZA
60.795456
61.629539
60.424751
61.166161
NaN
2017-02-08
000005.SZA
60.980808
61.166161
60.424751
61.073483
NaN
缺失值处理
在pandas中,用np.nan来代表缺失值,这些值默认不会参与运算。
# 通过set_index设置某一列为索引
sql=""WITH df2 AS(
SELECT date, open, high, low, close
FROM bar1d_CN_STOCK_A
WHERE instrument = '000005.SZA'
AND date BETWEEN '2016-11-15' AND '2016-12-01'
ORDER BY date;)""
df2
date
instrument
open
high
low
close
2016-11-15
000005.SZA
69.785027
70.619110
69.507004
70.526436
2016-11-16
000005.SZA
71.360519
71.545876
69.785027
70.155731
2016-11-17
000005.SZA
69.970383
70.155731
69.136299
69.970383
2016-11-18
000005.SZA
69.970383
70.433762
69.321648
70.063057
2016-11-21
000005.SZA
69.970383
70.711784
69.692352
69.877701
2016-11-22
000005.SZA
69.970383
70.619110
69.692352
70.341087
2016-11-23
000005.SZA
70.433762
70.897141
69.785027
70.526436
2016-11-24
000005.SZA
70.155731
70.619110
69.507004
69.970383
2016-11-25
000005.SZA
69.970383
70.433762
68.672913
70.063057
2016-11-28
000005.SZA
70.433762
71.638550
69.785027
70.711784
2016-11-29
000005.SZA
70.433762
71.082489
69.228973
69.321648
2016-11-30
000005.SZA
69.043617
69.599678
68.580238
68.765594
2016-12-01
000005.SZA
68.950943
69.321648
68.672913
69.043617
# 通过resset_index来将DataFrame的索引转化为列
df2.reset_index()
date
instrument
open
high
low
close
0
2016-11-15
000005.SZA
69.785027
70.619110
69.507004
70.526436
1
2016-11-16
000005.SZA
71.360519
71.545876
69.785027
70.155731
2
2016-11-17
000005.SZA
69.970383
70.155731
69.136299
69.970383
3
2016-11-18
000005.SZA
69.970383
70.433762
69.321648
70.063057
4
2016-11-21
000005.SZA
69.970383
70.711784
69.692352
69.877701
5
2016-11-22
000005.SZA
69.970383
70.619110
69.692352
70.341087
6
2016-11-23
000005.SZA
70.433762
70.897141
69.785027
70.526436
7
2016-11-24
000005.SZA
70.155731
70.619110
69.507004
69.970383
8
2016-11-25
000005.SZA
69.970383
70.433762
68.672913
70.063057
9
2016-11-28
000005.SZA
70.433762
71.638550
69.785027
70.711784
10
2016-11-29
000005.SZA
70.433762
71.082489
69.228973
69.321648
11
2016-11-30
000005.SZA
69.043617
69.599678
68.580238
68.765594
12
2016-12-01
000005.SZA
68.950943
69.321648
68.672913
69.043617
# 将2016-11-22以后的数据用缺失值代替
df2.loc['2016-11-22':,:] = np.nan
df2
date
instrument
open
high
low
close
2016-11-15
000005.SZA
69.785027
70.619110
69.507004
70.526436
2016-11-16
000005.SZA
71.360519
71.545876
69.785027
70.155731
2016-11-17
000005.SZA
69.970383
70.155731
69.136299
69.970383
2016-11-18
000005.SZA
69.970383
70.433762
69.321648
70.063057
2016-11-21
000005.SZA
69.970383
70.711784
69.692352
69.877701
2016-11-22
NaN
NaN
NaN
NaN
NaN
2016-11-23
NaN
NaN
NaN
NaN
NaN
2016-11-24
NaN
NaN
NaN
NaN
NaN
2016-11-25
NaN
NaN
NaN
NaN
NaN
2016-11-28
NaN
NaN
NaN
NaN
NaN
2016-11-29
NaN
NaN
NaN
NaN
NaN
2016-11-30
NaN
NaN
NaN
NaN
NaN
2016-12-01
NaN
NaN
NaN
NaN
NaN
# 剔除所有包含缺失值的行数据
df2.dropna(how='any')
date
instrument
open
high
low
close
2016-11-15
000005.SZA
69.785027
70.619110
69.507004
70.526436
2016-11-16
000005.SZA
71.360519
71.545876
69.785027
70.155731
2016-11-17
000005.SZA
69.970383
70.155731
69.136299
69.970383
2016-11-18
000005.SZA
69.970383
70.433762
69.321648
70.063057
2016-11-21
000005.SZA
69.970383
70.711784
69.692352
69.877701
# 填充缺失值
df2.fillna(value=154)
date
instrument
open
high
low
close
2016-11-15
000005.SZA
69.785027
70.619110
69.507004
70.526436
2016-11-16
000005.SZA
71.360519
71.545876
69.785027
70.155731
2016-11-17
000005.SZA
69.970383
70.155731
69.136299
69.970383
2016-11-18
000005.SZA
69.970383
70.433762
69.321648
70.063057
2016-11-21
000005.SZA
69.970383
70.711784
69.692352
69.877701
2016-11-22
154
154.000000
154.000000
154.000000
154.000000
2016-11-23
154
154.000000
154.000000
154.000000
154.000000
2016-11-24
154
154.000000
154.000000
154.000000
154.000000
2016-11-25
154
154.000000
154.000000
154.000000
154.000000
2016-11-28
154
154.000000
154.000000
154.000000
154.000000
2016-11-29
154
154.000000
154.000000
154.000000
154.000000
2016-11-30
154
154.000000
154.000000
154.000000
154.000000
2016-12-01
154
154.000000
154.000000
154.000000
154.000000
# 获取值是否为nan的布尔标记
pd.isnull(df2)
date
instrument
open
high
low
close
2016-11-15
False
False
False
False
False
2016-11-16
False
False
False
False
False
2016-11-17
False
False
False
False
False
2016-11-18
False
False
False
False
False
2016-11-21
False
False
False
False
False
2016-11-22
True
True
True
True
True
2016-11-23
True
True
True
True
True
2016-11-24
True
True
True
True
True
2016-11-25
True
True
True
True
True
2016-11-28
True
True
True
True
True
2016-11-29
True
True
True
True
True
2016-11-30
True
True
True
True
True
2016-12-01
True
True
True
True
True
运算
# describe()能对数据做一个快速统计汇总
df2.describe()
open
high
low
close
count
5.000000
5.000000
5.000000
5.000000
mean
70.211342
70.693253
69.488464
70.118668
std
0.647406
0.521793
0.265382
0.250397
min
69.785027
70.155731
69.136299
69.877701
25%
69.970383
70.433762
69.321648
69.970383
50%
69.970383
70.619110
69.507004
70.063057
75%
69.970383
70.711784
69.692352
70.155731
max
71.360519
71.545876
69.785027
70.526436
运算过程中,通常不包含缺失值。
# 按列计算平均值
df2.mean()
open 70.211342
high 70.693253
low 69.488464
close 70.118668
dtype: float32
# 按行计算平均值
df2.mean(1)
date
2016-11-15 70.109398
2016-11-16 70.711792
2016-11-17 69.808197
2016-11-18 69.947212
2016-11-21 70.063049
2016-11-22 NaN
2016-11-23 NaN
2016-11-24 NaN
2016-11-25 NaN
2016-11-28 NaN
2016-11-29 NaN
2016-11-30 NaN
2016-12-01 NaN
dtype: float32
# pandas会按照指定的索引对齐
dates = ['2016-11-15','2016-11-16','2016-11-17','2016-11-18','2016-11-23']
for i in range(len(dates)):
dates[i] = pd.Timestamp(dates[i])
pd.DataFrame(df2,index=dates)
instrument
open
high
low
close
2016-11-15
000005.SZA
69.785027
70.619110
69.507004
70.526436
2016-11-16
000005.SZA
71.360519
71.545876
69.785027
70.155731
2016-11-17
000005.SZA
69.970383
70.155731
69.136299
69.970383
2016-11-18
000005.SZA
69.970383
70.433762
69.321648
70.063057
2016-11-23
NaN
NaN
NaN
NaN
NaN
Apply 函数
# 通过apply函数求每个交易日高开低收价格的平均值
df3 = df2[['open','high','low','close']]
df3.apply(np.mean,axis=1)
date
2016-11-15 70.109398
2016-11-16 70.711792
2016-11-17 69.808197
2016-11-18 69.947212
2016-11-21 70.063049
2016-11-22 NaN
2016-11-23 NaN
2016-11-24 NaN
2016-11-25 NaN
2016-11-28 NaN
2016-11-29 NaN
2016-11-30 NaN
2016-12-01 NaN
dtype: float64
# 计算每日股价振幅,振幅=最高价-最低价
df3.apply(lambda x:x.max()-x.min())
open 1.575493
high 1.390144
low 0.648727
close 0.648735
dtype: float64
# 通过apply对各个元素调用字符串方法,比如将股票代码的后缀改成小写
temp = df2.ix['2016-11-15':'2016-11-21']
temp['instrument'].apply(lambda x : x.lower())
date
2016-11-15 000005.sza
2016-11-16 000005.sza
2016-11-17 000005.sza
2016-11-18 000005.sza
2016-11-21 000005.sza
Name: instrument,
dtype: object
rolling_apply的使用
# 获取数据
sql=""WITH data AS(
SELECT date, close
FROM bar1d_CN_STOCK_A
WHERE instrument = '000001.SZA'
AND date BETWEEN '2016-01-01' AND '2016-10-01'
ORDER BY date;)""
# 计算5日均线和20日均线
data['MA_' + str(5)] = data['close'].rolling(5).mean()
data['MA_' + str(20)] = data['close'].rolling(20).mean()
data.head(12)
instrument
close
MA_5
MA_20
date
2016-01-04
000001.SZA
974.312988
NaN
NaN
2016-01-05
000001.SZA
980.332520
NaN
NaN
2016-01-06
000001.SZA
991.511780
NaN
NaN
2016-01-07
000001.SZA
940.775269
NaN
NaN
2016-01-08
000001.SZA
956.254211
968.637354
NaN
2016-01-11
000001.SZA
925.296326
958.834021
NaN
2016-01-12
000001.SZA
929.596008
948.686719
NaN
2016-01-13
000001.SZA
920.996643
934.583691
NaN
2016-01-14
000001.SZA
926.156250
931.659888
NaN
2016-01-15
000001.SZA
899.498108
920.308667
NaN
2016-01-18
000001.SZA
895.198425
914.289087
NaN
2016-01-19
000001.SZA
920.996643
912.569214
NaN
合并
Concat 连接
# 通过concat()来连接pandas对象
a = df2.loc['2016-11-15':'2016-11-16']
b = df2.loc['2016-11-20':'2016-11-26']
pd.concat([a,b])
date
instrument
open
high
low
close
2016-11-15
000005.SZA
69.785027
70.619110
69.507004
70.526436
2016-11-16
000005.SZA
71.360519
71.545876
69.785027
70.155731
2016-11-21
000005.SZA
69.970383
70.711784
69.692352
69.877701
2016-11-22
NaN
NaN
NaN
NaN
NaN
2016-11-23
NaN
NaN
NaN
NaN
NaN
2016-11-24
NaN
NaN
NaN
NaN
NaN
2016-11-25
NaN
NaN
NaN
NaN
NaN
Join 合并
# 合并,和merge功能类似
sql=""WITH df5 AS (
SELECT
date,
open,
high,
low,
close
FROM
bar1d_CN_STOCK_A
WHERE
instrument = '000005.SZA'
AND date BETWEEN '2017-02-01' AND '2017-02-10'
) , df6 AS (
SELECT
date,
market_cap,
pe_ttm
FROM
market_value_CN_STOCK_A
WHERE
instrument = '000005.SZA'
AND date BETWEEN '2017-01-01' AND '2017-02-10'
)""
# 删除股票代码列
del df5['instrument']
del df6['instrument']
# 按日期轴合并数据
df5.join(df6)
date
open
high
low
close
market_cap
pe_ttm
2017-02-03
60.888130
60.888130
60.239399
60.517429
6.912246e+09
63.786068
2017-02-06
60.517429
60.795456
60.146725
60.795456
6.944002e+09
64.079109
2017-02-07
60.795456
61.629539
60.424751
61.166161
6.986343e+09
64.469841
2017-02-08
60.980808
61.166161
60.424751
61.073483
6.975758e+09
64.372154
2017-02-09
61.073483
61.536865
60.888130
61.444187
7.018099e+09
64.762886
2017-02-10
61.629539
61.629539
61.258835
61.536865
7.028685e+09
64.860565
Append 添加
# 通过append方法,将若干行添加到dataFrame后面
c = df1.loc['2017-02-03':'2017-02-06']
d = df1.loc['2017-02-08':'2017-02-10']
c.append(d)
date
instrument
open
high
low
close
market_cap
2017-02-03
111
111.000000
111.000000
111.000000
111.000000
111.0
2017-02-06
000005.SZA
60.517429
60.795456
60.146725
60.795456
NaN
2017-02-08
000005.SZA
60.980808
61.166161
60.424751
61.073483
NaN
2017-02-09
000005.SZA
61.073483
61.536865
60.888130
61.444187
NaN
2017-02-10
000005.SZA
61.629539
61.629539
61.258835
61.536865
NaN
数据透视表
也许大多数人都有在Excel中使用数据透视表的经历,其实Pandas也提供了一个类似的功能,名为pivot_table
df = pd.DataFrame({'A' : ['one', 'one', 'two', 'three'] * 3,
'B' : ['A', 'B', 'C'] * 4,
'C' : ['foo', 'foo', 'foo', 'bar', 'bar', 'bar'] * 2,
'D' : np.random.randn(12),
'E' : np.random.randn(12)})
df
A \n
B
C
D
E
0
one
A
foo
-2.216199
0.288395
1
one
B
foo
1.572496
0.159725
2
two
C
foo
-0.559229
0.204685
3
three
A
bar
2.044115
-0.032094
4
one
B
bar
1.022778
1.650399
5
one
C
bar
-0.558943
1.074547
6
two
A
foo
-0.147329
0.150598
7
three
B
foo
0.659760
1.779248
8
one
C
foo
0.506593
-0.343586
9
one
A
bar
0.941787
-0.277070
10
two
B
bar
1.662566
-0.558867
11
three
C
bar
-1.671826
-0.939832
# 我们可以轻松得到数据透视表
pd.pivot_table(df, values='D', index=['A', 'B'], columns=['C'])
\n C
bar
foo
A
B
one
A
0.941787
-2.216199
B
1.022778
1.572496
C
-0.558943
0.506593
three
A
2.044115
NaN
B
NaN
0.659760
C
-1.671826
NaN
two
A
NaN
-0.147329
B
1.662566
NaN
C
NaN
-0.559229
# 查看每个交易日多只股票的收盘价数据(每列为股票)
df = D.history_data(['000001.SZA','000002.SZA'],'2016-12-15','2017-01-01',fields=['close'],groupped_by_instrument=False)
pd.pivot_table(df,values='close',index=['date'],columns=['instrument'])
instrument
000001.SZA
000002.SZA
date
2016-12-15
969.013855
2965.032471
2016-12-16
969.013855
2982.294189
2016-12-19
963.775940
2801.710205
2016-12-20
954.347656
2699.467529
2016-12-21
959.585571
2719.385010
2016-12-22
957.490417
2736.646729
2016-12-23
951.204956
2695.484131
2016-12-26
955.395264
2741.958008
2016-12-27
951.204956
2844.200439
2016-12-28
949.109802
2814.988281
2016-12-29
951.204956
2767.186768
2016-12-30
953.300110
2728.679688
# 查看不同股票每个交易日的收盘价数据(每列为交易日)
pd.pivot_table(df,values = 'close',index=['instrument'],columns=['date'])
date
2016-12-15 00 :00 :00
2016-12-16 00 :00 :00
2016-12-19 00 :00 :00
2016-12-20 00 :00 :00
2016-12-21 00 :00 :00
2016-12-22 00 :00 :00
2016-12-23 00 :00 :00
2016-12-26 00 :00 :00
2016-12-27 00 :00 :00
2016-12-28 00 :00 :00
2016-12-29 00 :00 :00
2016-12-30 00 :00 :00
instrument
000001.SZA
969.013855
969.013855
963.775940
954.347656
959.585571
957.490417
951.204956
955.395264
951.204956
949.109802
951.204956
953.300110
000002.SZA
2965.032471
2982.294189
2801.710205
2699.467529
2719.385010
2736.646729
2695.484131
2741.958008
2844.200439
2814.988281
2767.186768
2728.679688
分组
对于“group by”操作,我们通常是指以下一个或几个步骤: * 划分 按照某些标准将数据分为不同的组 * 应用 对每组数据分别执行一个函数 * 组合 将结果组合到一个数据结构
df = pd.DataFrame({'A' : ['foo', 'bar', 'foo', 'bar',
'foo', 'bar', 'foo', 'bar'],
'B' : ['one', 'one', 'two', 'three',
'two', 'two', 'one', 'three'],
'C' : np.random.randn(8),
'D' : np.random.randn(8)})
df
A
B
C
D
0
foo
one
-0.160349
-0.086323
1
bar
one
-0.994189
-1.444457
2
foo
two
-0.095365
0.227690
3
bar
three
1.502585
-0.286937
4
foo
two
-0.258329
-0.123057
5
bar
two
0.944706
-0.211243
6
foo
one
1.542726
-0.164639
7
bar
three
0.874764
-0.075149
# 分组并对每个分组应用sum函数
df.groupby('A').sum()
A
C
D
bar
2.327866
-2.017786
foo
1.028683
-0.146328
# 按多个列分组形成层级索引,然后应用函数
df.groupby(['A','B']).sum()
A
B
C
D
bar
one
-0.994189
-1.444457
three
2.377349
-0.362086
two
0.944706
-0.211243
foo
one
1.382377
-0.250962
two
-0.353694
0.104634
# 通过groupby函数来计算各股票和大盘指数的相关系数(按年划分)
# 获取数据
stocks = ['000005.SZA','000010.SZA','603998.SHA','603898.SHA']
sql=""WITH df AS(
SELECT instrument, date, close
FROM bar1d_CN_STOCK_A
WHERE instrument IN ('000005.SZA','000010.SZA','603998.SHA','603898.SHA')
AND date BETWEEN '2010-01-01' AND '2016-12-31'
ORDER BY instrument, date;)""
# 每列转化为每日多只股票的收盘价数据
df = pd.pivot_table(df,values='close',index=['date'],columns=['instrument'])
# 计算日收益率
rets = df.pct_change().dropna()
# 定义求相关系数的函数
corr_func = lambda x:x.corrwith(x['000005.SZA'])
# 求每只股票和大盘相关性
by_year = rets.groupby(lambda x:x.year)
by_year.apply(corr_func)
instrument
000005.SZA
000010.SZA
603898.SHA
603998.SHA
2015
1.0
0.628609
0.178597
0.523903
2016
1.0
0.643711
0.627512
0.548929
时间序列
pandas在对频率转换进行重新采样时拥有着简单,强大而且高效的功能(例如把按秒采样的数据转换为按5分钟采样的数据)。这在金融领域很常见,但又不限于此。
# 按秒产生5000个时间、起始时间是2017-01-01的时间序列
rng = pd.date_range('1/1/2017', periods=5000, freq='S')
rng
DatetimeIndex(['2017-01-01 00 :00 :00 ', '2017-01-01 00 :00 :01 ',
'2017-01-01 00 :00 :02 ', '2017-01-01 00 :00 :03 ',
'2017-01-01 00 :00 :04 ', '2017-01-01 00 :00 :05 ',
'2017-01-01 00 :00 :06 ', '2017-01-01 00 :00 :07 ',
'2017-01-01 00 :00 :08 ', '2017-01-01 00 :00 :09 ',
...
'2017-01-01 01 :23 :10 ', '2017-01-01 01 :23 :11 ',
'2017-01-01 01 :23 :12 ', '2017-01-01 01 :23 :13 ',
'2017-01-01 01 :23 :14 ', '2017-01-01 01 :23 :15 ',
'2017-01-01 01 :23 :16 ', '2017-01-01 01 :23 :17 ',
'2017-01-01 01 :23 :18 ', '2017-01-01 01 :23 :19 '],
dtype='datetime64[ns]', length=5000, freq='S')
# 重采样,计算每五分钟的总和
ts = pd.Series(np.random.randint(0,5000,len(rng)), index=rng)
ts.resample('5Min', how='sum')
2017-01-01 00:00:00 765477
2017-01-01 00:05:00 755782
2017-01-01 00:10:00 759474
2017-01-01 00:15:00 738611
2017-01-01 00:20:00 782165
2017-01-01 00:25:00 741331
2017-01-01 00:30:00 771220
2017-01-01 00:35:00 790782
2017-01-01 00:40:00 739392
2017-01-01 00:45:00 800815
2017-01-01 00:50:00 725954
2017-01-01 00:55:00 725840
2017-01-01 01:00:00 739548
2017-01-01 01:05:00 756409
2017-01-01 01:10:00 727530
2017-01-01 01:15:00 776613
2017-01-01 01:20:00 522169
Freq: 5T, dtype: int64
# 频率转化,将日K线数据装换成周K
sql=""WITH df AS(
SELECT date, close
FROM bar1d_CN_STOCK_A
WHERE instrument = '000001.SZA'
AND date BETWEEN '2016-11-01' AND '2016-12-31'
ORDER BY date;)""
df.resample('W').ohlc()
close
date
open
high
low
close
2016-11-06
957.490417
957.490417
950.157349
954.347656
2016-11-13
955.395264
961.680786
950.157349
961.680786
2016-11-20
965.871094
966.918701
961.680786
961.680786
2016-11-27
967.966248
1007.774414
967.966248
1007.774414
2016-12-04
1008.821960
1008.821960
1000.441345
1000.441345
2016-12-11
991.013062
1010.917114
991.013062
1010.917114
2016-12-18
995.203430
995.203430
969.013855
969.013855
2016-12-25
963.775940
963.775940
951.204956
951.204956
2017-01-01
955.395264
955.395264
949.109802
953.300110
绘图
数据可视化:可交互图表 (文档: 数据可视化)
# 绘制沪深300走势图
history_hs300 = DataSource('bar1d_CN_STOCK_A').read(instruments = ['000300.HIX'], start_date='2016-01-01', end_date='2017-01-01', fields=['close'])
T.plot(history_hs300[['date', 'close']].set_index('date'), chart_type='spline')
# 绘制多只股票走势图
sql="" WITH data AS(
SELECT instrument, date, close
FROM bar1d_CN_STOCK_A
WHERE instrument IN ('000005.SZA', '000010.SZA', '603998.SHA', '603898.SHA')
AND date BETWEEN '2016-01-01' AND '2017-01-01'
ORDER BY instrument, date;)""
data = pd.pivot_table(data,values='close',index=['date'],columns=['instrument'])
T.plot(data, chart_type='spline')