Pandas雅虎金融数据获取与分析

import pandas as pd
import numpy as np
from pandas_datareader import data, wb # 需要安装 pip install pandas_datareader
import datetime
import matplotlib.pyplot as plt
import matplotlib
matplotlib.style.use('ggplot')
%matplotlib inline

# 定义获取数据的时间段
start = datetime.datetime(2010, 1, 1)
end = datetime.datetime(2016,5,20)

sh = data.DataReader("000001.SS", 'yahoo', start, end)

sh.head(3) # 数据获取成功

sh.describe() # 数据整体概览

sh['Close'].plot(); #看一下收盘趋势

sh.to_csv('sh.csv',header=None)

names = ['Date','Open','High','Low','Close','Volume','Adj Close']
sh1 = pd.read_csv('sh.csv',names=names,index_col='Date')

sh1.tail(2)

# drop Volume列，所以数据都为0
sh = sh.drop('Volume',axis=1)

sh.head(2) # Volume列消失了

# Detect missing values,用isnull函数
pd.isnull(sh).head() # or sh.isnull()

#参考 http://stackoverflow.com/questions/29530232/python-pandas-check-if-any-value-is-nan-in-dataframe
sh.isnull().values.any() # 数据很完整

False

sh.isnull().values.sum() # 没有值

0

sh.head()

sh.iloc[0,:] = np.nan
sh.iloc[[1,3],1] = np.nan
sh.iloc[2,2] = np.nan
sh.iloc[3,3] = np.nan

sh.head(4)

# check NaN number per column
def num_missing(x):
    return x.isnull().sum()

print "Missing values count per column:"
print sh.apply(num_missing, axis=0)

Missing values count per column:
Open         1
High         3
Low          2
Close        2
Adj Close    1
dtype: int64

sh.dropna(how='all',inplace=True); 

sh.head(3)

sh['High'].fillna(sh['High'].mean(),inplace=True) #fill in with mean
sh['Low'].fillna(method='ffill',inplace=True) #fill in with value from previous row
sh['Close'].fillna(method='bfill',inplace=True) # fill in with value from the row behind

sh.head()

sh.isnull().values.sum()

0

change = sh.Close.diff()
change.fillna(change.mean(),inplace=True)
sh['Change'] = change

sh.head(3)

# 按涨跌额排序,未改变sh，若需要，可以`inplace=True`
sh.sort_values(by='Change',ascending=False).head(3)

sh.head()

# 用shift方法错位
# sh['pct_change'] = ((sh['Change'] - sh['Change'].shift(1)) / sh['Change'])
# 或用pct_Change函数
sh['pct_change'] = sh.Change.pct_change()
sh.iloc[5:9]

sh.head(2)

format = lambda x: '%.2f' % x
sh = sh.applymap(format)

sh.head(2)

sh = sh.applymap(float) # 可以重新把数据类型转换为float类型

sh.ix[1]

Open          3254.22
High          3254.22
Low           3282.18
Close         3254.22
Adj Close     3254.22
Change         -27.96
pct_change      94.26
Name: 2010-01-06 00:00:00, dtype: float64

def f(x):
    return pd.Series([x.min(),x.max(),x.max()-x.min()],index=['min','max','range'])
sh.apply(f)

# 按月分组
sh['group_index'] = sh.index.map(lambda x: 100*x.year + x.month)

sh.head(2).append(sh.tail(2)) # 查看group_index, as expected

sh.groupby('group_index') # 返回一个groupby对象

<pandas.core.groupby.DataFrameGroupBy object at 0x10f7a9110>

# 对不同的列运用不同的函数聚合
def peak_to_peak(arr):
    return arr.max() - arr.min()

grouped = sh.groupby('group_index').agg({
        'Open' : peak_to_peak,
        'High' : 'max',
        'Low' : 'min',
        'Change' : 'mean'
    })
grouped.head(3).append(grouped.tail(3))

sh.index # DatetimeIndex 提供很多便捷操作

DatetimeIndex(['2010-01-05', '2010-01-06', '2010-01-07', '2010-01-08',
               '2010-01-11', '2010-01-12', '2010-01-13', '2010-01-14',
               '2010-01-15', '2010-01-18',
               ...
               '2016-05-09', '2016-05-10', '2016-05-11', '2016-05-12',
               '2016-05-13', '2016-05-16', '2016-05-17', '2016-05-18',
               '2016-05-19', '2016-05-20'],
              dtype='datetime64[ns]', name=u'Date', length=1557, freq=None)

grouped_1 = sh.groupby(pd.TimeGrouper("M")).agg({
        'Open' : peak_to_peak,
        'High' : 'max',
        'Low' : 'min',
        'Change' : 'mean'
    })
grouped_1.head(3).append(grouped_1.tail(3))

# resample('M',how='mean')这样的语法将来不支持，推荐.mean()以及.apply()
# 可以传入词典给apply
grouped_2 = sh[['Open','High','Low','Change']].resample('M').apply({
        'Open' : peak_to_peak,
        'High' : 'max',
        'Low' : 'min',
        'Change' : 'mean'
    })
grouped_2.head(3).append(grouped_2.tail(3))

sh[['Open']].plot();
plt.show()