#installing pandas libraries
!pip install pandas-datareader
!pip install --upgrade html5lib==1.0b8

#There is a bug in the latest version of html5lib so install an earlier version
#Restart kernel after installing html5lib

"""
Explanation: <h1>Pandas</h1>
End of explanation
"""


import pandas as pd #pandas library
from pandas_datareader import data #data readers (google, html, etc.)
#The following line ensures that graphs are rendered in the notebook
%matplotlib inline 
import numpy as np
import matplotlib.pyplot as plt #Plotting library
import datetime as dt #datetime for timeseries support

"""
Explanation: <h2>Imports</h2>
End of explanation
"""


pd.DataFrame([[1,2,3],[1,2,3]],columns=['A','B','C'])

"""
Explanation: <h2>The structure of a dataframe</h2>
End of explanation
"""


df = pd.DataFrame([['r1','00','01','02'],['r2','10','11','12'],['r3','20','21','22']],columns=['row_label','A','B','C'])
print(id(df))
df.set_index('row_label',inplace=True)
print(id(df))
df

data = {'nationality': ['UK', 'China', 'US', 'UK', 'Japan', 'China', 'UK', 'UK', 'Japan', 'US'],
        'age': [25, 30, 15, np.nan, 25, 22, np.nan,45 ,18, 33],
        'type': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],
        'diabetes': ['yes', 'yes', 'no', 'yes', 'no', 'no', 'no', 'yes', 'no', 'no']}

labels = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j']
df=pd.DataFrame(data=data,index=labels)
#print(df[df['age'].between(20,30)])
#print(df.groupby('nationality').mean()['age'])
#print(df.sort_values(by=['age','type'],ascending=[False,True]))
df['nationality'] = df['nationality'].replace('US','United States')
print(df)

df.ix[1]

"""
Explanation: <h3>Accessing columns and rows</h3>
End of explanation
"""


df['B']

"""
Explanation: <h3>Getting column data</h3>
End of explanation
"""


df.loc['r1']

"""
Explanation: <h3>Getting row data</h3>
End of explanation
"""


df.iloc[0]

"""
Explanation: <h3>Getting a row by row number</h3>
End of explanation
"""


df[['B','A']] #Note that the column identifiers are in a list

"""
Explanation: <h3>Getting multiple columns<h3>
End of explanation
"""


df.loc['r2','B']

df.loc['r2']['A']

"""
Explanation: <h3>Getting a specific cell</h3>
End of explanation
"""


print(df)
print(df.loc['r1':'r2'])

df.loc['r1':'r2','B':'C']

"""
Explanation: <h3>Slicing</h3>
End of explanation
"""


#df_list = pd.read_html('http://www.bloomberg.com/markets/currencies/major')
df_list = pd.read_html('http://www.waihuipaijia.cn/'
                      , encoding='utf-8')
print(len(df_list))

"""
Explanation: <h2>Pandas datareader</h2>
<li>Access data from html tables on any web page</li>
<li>Get data from google finance</li>
<li>Get data from the federal reserve</li>

<h3>HTML Tables</h3>
<li>Pandas datareader can read a table in an html page into a dataframe
<li>the read_html function returns a list of all dataframes with one dataframe for each html table on the page

<h4>Example: Read the tables on the google finance page</h4>
End of explanation
"""


df = df_list[0]
print(df)

"""
Explanation: <h4>The page contains only one table so the read_html function returns a list of one element</h4>
End of explanation
"""


df.set_index('Currency',inplace=True)
print(df)

"""
Explanation: <h4>Note that the read_html function has automatically detected the header columns</h4>
<h4>If an index is necessary, we need to explicitly specify it</h4>
End of explanation
"""


df.loc['EUR-CHF','Value']

"""
Explanation: <h4>Now we can use .loc to extract specific currency rates</h4>
End of explanation
"""


eur_usd = df.loc['EUR-USD']['Change'] #This is chained indexing
df.loc['EUR-USD']['Change'] = 1.0 #Here we are changing a value in a copy of the dataframe
print(eur_usd)
print(df.loc['EUR-USD']['Change']) #Neither eur_usd, nor the dataframe are changed

eur_usd = df.loc['EUR-USD','Change'] #eur_usd points to the value inside the dataframe
df.loc['EUR-USD','Change'] = 1.0 #Change the value in the view 
print(eur_usd) #eur_usd is changed (because it points to the view)
print(df.loc['EUR-USD']['Change']) #The dataframe has been correctly updated

"""
Explanation: <h3>Working with views and copies</h3>

<h4>Chained indexing creates a copy and changes to the copy won't be reflected in the original dataframe</h4>
End of explanation
"""


from pandas_datareader import data
import datetime as dt
start=dt.datetime(2017, 1, 1)
end=dt.datetime.today()


print(start,end)


df = data.DataReader('IBM', 'google', start, end)


df

"""
Explanation: <h2>Getting historical stock prices from Google financs</h2>
Usage: DataReader(ticker,source,startdate,enddate)<br>
Unfortunately, the Yahoo finance datareader has stopped working because of a change to Yahoo's website
End of explanation
"""


df['UP']=np.where(df['Close']>df['Open'],1,0)
df

"""
Explanation: <h2>Datareader documentation</h2>
http://pandas-datareader.readthedocs.io/en/latest/</h2>
<h3>Working with a timeseries data frame</h3>
<li>The data is organized by time with the index serving as the timeline


<h4>Creating new columns</h4>
<li>Add a column to a dataframe
<li>Base the elements of the column on some combination of data in the existing columns
<h4>Example: Number of Days that the stock closed higher than it opened
<li>We'll create a new column with the header "UP"
<li>And use np.where to decide what to put in the column
End of explanation
"""


df.describe()

"""
Explanation: <h3>Get summary statistics</h3>
<li>The "describe" function returns a dataframe containing summary stats for all numerical columns
<li>Columns containing non-numerical data are ignored
End of explanation
"""


df['UP'].sum()/df['UP'].count()

"""
Explanation: <h4>Calculate the percentage of days that the stock has closed higher than its open</h4>
End of explanation
"""


df['Close'].pct_change() #One timeperiod percent change

n=2
df['Close'].pct_change(n) #n timeperiods percent change

"""
Explanation: <h4>Calculate percent changes</h4>
<li>The function pct_change computes a percent change between successive rows (times in  timeseries data)
<li>Defaults to a single time delta
<li>With an argument, the time delta can be changed
End of explanation
"""


n=13
df['Close'].pct_change(n).mean()

"""
Explanation: <h3>NaN support</h3>
Pandas functions can ignore NaNs
End of explanation
"""


df['Close'].pct_change(n).rolling(21)

"""
Explanation: <h3>Rolling windows</h3>
<li>"rolling" function extracts rolling windows
<li>For example, the 21 period rolling window of the 13 period percent change
End of explanation
"""


n=13
df['Close'].pct_change(n).rolling(21).mean()

"""
Explanation: <h4>Calculate something on the rolling windows</h4>

<h4>Example: mean (the 21 day moving average of the 13 day percent change)
End of explanation
"""


ma_8 = df['Close'].pct_change(n).rolling(window=8).mean()
ma_13= df['Close'].pct_change(n).rolling(window=13).mean()
ma_21= df['Close'].pct_change(n).rolling(window=21).mean()
ma_34= df['Close'].pct_change(n).rolling(window=34).mean()
ma_55= df['Close'].pct_change(n).rolling(window=55).mean()

ma_8.plot()
ma_34.plot()

"""
Explanation: <h4>Calculate several moving averages and graph them</h4>
End of explanation
"""


import datetime
import pandas_datareader as data
start = datetime.datetime(2015,7,1)
end = datetime.datetime(2016,6,1)
solar_df = data.DataReader(['FSLR', 'TAN','RGSE','SCTY'],'google', start=start,end=end)['Close']

solar_df

"""
Explanation: <h2>Linear regression with pandas</h2>
<h4>Example: TAN is the ticker for a solar ETF. FSLR, RGSE, and SCTY are tickers of companies that build or lease solar panels. Each has a different business model. We'll use pandas to study the risk reward tradeoff between the 4 investments and also see how correlated they are</h4>
End of explanation
"""


rets = solar_df.pct_change()
print(rets)

"""
Explanation: <h4>Let's calculate returns (the 1 day percent change)</h4>
End of explanation
"""


import matplotlib.pyplot as plt
plt.scatter(rets.FSLR,rets.TAN)

plt.scatter(rets.RGSE,rets.TAN)

plt.scatter(rets.SCTY,rets.TAN)

"""
Explanation: <h4>Let's visualize the relationship between each stock and the ETF</h4>
End of explanation
"""


solar_corr = rets.corr()
print(solar_corr)

"""
Explanation: <h4>The correlation matrix</h4>
End of explanation
"""


plt.scatter(rets.mean(), rets.std())
plt.xlabel('Expected returns')
plt.ylabel('Standard deviations')
for label, x, y in zip(rets.columns, rets.mean(), rets.std()):
    plt.annotate(
        label, 
        xy = (x, y), xytext = (20, -20),
        textcoords = 'offset points', ha = 'right', va = 'bottom',
        bbox = dict(boxstyle = 'round,pad=0.5', fc = 'yellow', alpha = 0.5),
        arrowprops = dict(arrowstyle = '->', connectionstyle = 'arc3,rad=0'))
plt.show()


"""
Explanation: <h3>Basic risk analysis</h3>
<h4>We'll plot the mean and std or returns for each ticker to get a sense of the risk return profile</h4>
End of explanation
"""


import numpy as np
import statsmodels.api as sm
X=solar_df[['FSLR','RGSE','SCTY']]
X = sm.add_constant(X)
y=solar_df['TAN']
model = sm.OLS(y,X,missing='drop')
result = model.fit()
print(result.summary())

"""
Explanation: <h2>Regressions</h2>
http://statsmodels.sourceforge.net/
<h3>Steps for regression</h3>
<li>Construct y (dependent variable series)
<li>Construct matrix (dataframe) of X (independent variable series)
<li>Add intercept
<li>Model the regression
<li>Get the results
<h3>The statsmodels library contains various regression packages. We'll use the OLS (Ordinary Least Squares) model
End of explanation
"""


fig, ax = plt.subplots(figsize=(8,6))
ax.plot(y)
ax.plot(result.fittedvalues)

"""
Explanation: <h4>Finally plot the fitted line with the actual y values
End of explanation
"""


