<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Data Exploration
Learning objectives
Step1:  Let's look at the tables and columns we have for analysis.
Learning objective 1.
Step2:  Price History
Retrieve Google's stock price history.
Step3:  Compare google to S&P
Step4:  Learning objective 2
Step5:  Let's see how the price of stocks change over time on a yearly basis. Using the LAG function we can compute the change in stock price year-over-year. 
Let's compute average close difference for each year. This line could, of course, be done in Pandas. Often times it's useful to use some combination of BigQuery and Pandas for exploration analysis. In general, it's most effective to let BigQuery do the heavy-duty processing and then use Pandas for smaller data and visualization.
Learning objective 1, 2
Step6:  Compute the year-over-year percentage increase.
Step7:  Let's visualize some yearly stock
Step8:  There have been some major fluctations in individual stocks. For example, there were major drops during the early 2000's for tech companies.
Step9:  Stock splits can also impact our data - causing a stock price to rapidly drop. In practice, we would need to clean all of our stock data to account for this. This would be a major effort! Fortunately, in the case of IBM, for example, all stock splits occurred before the year 2000.
Learning objective 2
Step10:  S&P companies list
Step11:  We can join the price histories table with the S&P 500 table to compare industries
Step12:  Using pandas we can "unstack" our table so that each industry has it's own column. This will be useful for plotting.
Step13:  Let's scale each industry using min/max scaling. This will put all of the stocks on the same scale. Currently it can be hard to see the changes in stocks over time across industries.
Learning objective 1
Step14:  We can also create a smoothed version of the plot above using a rolling mean. This is a useful transformation to make when visualizing time-series data.
<ASSISTANT_TASK:>
Python Code:
PROJECT = 'qwiklabs-gcp-ml-489e10787faf'  # Change to your project.
import numpy as np
import pandas as pd
import seaborn as sns
from matplotlib import pyplot as plt
from google.cloud import bigquery
from IPython.core.magic import register_cell_magic
from IPython import get_ipython
bq = bigquery.Client(project=PROJECT)
# Allow you to easily have Python variables in SQL query.
@register_cell_magic('with_globals')
def with_globals(line, cell):
    contents = cell.format(**globals())
    if 'print' in line:
        print(contents)
    get_ipython().run_cell(contents)
Explanation: Data Exploration
Learning objectives:
Learn useful patterns for exploring data before modeling
Gain an understanding of the dataset and identify any data issues.
The goal of this notebook is to explore our base tables before we began feature engineering and modeling. We will explore the price history of stock in the S&P 500.
Price history : Price history of stocks
S&P 500 : A list of all companies and symbols for companies in the S&P 500
For our analysis, let's limit price history since 2000. In general, the further back historical data is used the lower it's predictive power can be.
End of explanation
%%with_globals
%%bigquery --project {PROJECT}
SELECT table_name, column_name, data_type
FROM `asl-ml-immersion.stock_src.INFORMATION_SCHEMA.COLUMNS`
ORDER BY table_name, ordinal_position
Explanation: Let's look at the tables and columns we have for analysis.
Learning objective 1.
End of explanation
def query_stock(symbol):
    return bq.query('''
    SELECT *
    FROM `asl-ml-immersion.stock_src.price_history`
    WHERE symbol="{0}"
    ORDER BY Date
    '''.format(symbol)).to_dataframe()
df_stock = query_stock('GOOG')
df_stock.Date = pd.to_datetime(df_stock.Date)
ax = df_stock.plot(x='Date', y='Close', title='Google stock')
# Add smoothed plot.
df_stock['Close_smoothed'] = df_stock.Close.rolling(100, center=True).mean()
df_stock.plot(x='Date', y='Close_smoothed', ax=ax);
Explanation: Price History
Retrieve Google's stock price history.
End of explanation
df_sp = query_stock('gspc')
def plot_with_sp(symbol):
    df_stock = query_stock(symbol)
    df_stock.Date = pd.to_datetime(df_stock.Date)
    df_stock.Date = pd.to_datetime(df_stock.Date)
    fig = plt.figure()
    ax1 = fig.add_subplot(111)
    ax2 = ax1.twinx()
    ax = df_sp.plot(x='Date', y='Close', label='S&P', color='green', ax=ax1,
                    alpha=0.7)
    ax = df_stock.plot(x='Date', y='Close', label=symbol,
                       title=symbol + ' and S&P index', ax=ax2, alpha=0.7)
    ax1.legend(loc=3)
    ax2.legend(loc=4)
    ax1.set_ylabel('S&P price')
    ax2.set_ylabel(symbol + ' price')
    ax.set_xlim(pd.to_datetime('2004-08-05'), pd.to_datetime('2013-08-05'))
plot_with_sp('GOOG')
Explanation: Compare google to S&P
End of explanation
plot_with_sp('IBM')
Explanation: Learning objective 2
End of explanation
%%with_globals
%%bigquery --project {PROJECT} df
WITH 
with_year AS
    (
    SELECT symbol, 
    EXTRACT(YEAR FROM date) AS year,
    close
    FROM `asl-ml-immersion.stock_src.price_history`
    WHERE symbol in (SELECT symbol FROM `asl-ml-immersion.stock_src.snp500`)
    ),
year_aggregated AS
    (
    SELECT year, symbol, AVG(close) as avg_close
    FROM with_year
    WHERE year >= 2000
    GROUP BY year, symbol
    )
SELECT year, symbol, avg_close as close,
(LAG(avg_close, 1) OVER (PARTITION BY symbol order by year DESC)) 
    AS next_yr_close
FROM year_aggregated
ORDER BY symbol, year
Explanation: Let's see how the price of stocks change over time on a yearly basis. Using the LAG function we can compute the change in stock price year-over-year. 
Let's compute average close difference for each year. This line could, of course, be done in Pandas. Often times it's useful to use some combination of BigQuery and Pandas for exploration analysis. In general, it's most effective to let BigQuery do the heavy-duty processing and then use Pandas for smaller data and visualization.
Learning objective 1, 2
End of explanation
df.dropna(inplace=True)
df['percent_increase'] = (df.next_yr_close - df.close) / df.close
Explanation: Compute the year-over-year percentage increase.
End of explanation
def get_random_stocks(n=5):
    random_stocks = df.symbol.sample(n=n, random_state=3)
    rand = df.merge(random_stocks)
    return rand[['year', 'symbol', 'percent_increase']]
rand = get_random_stocks()
for symbol, _df in rand.groupby('symbol'):
    plt.figure()
    sns.barplot(x='year', y="percent_increase", data=_df)
    plt.title(symbol)
Explanation: Let's visualize some yearly stock
End of explanation
df.sort_values('percent_increase').head()
stock_symbol = 'YHOO'
%%with_globals
%%bigquery --project {PROJECT} df
SELECT date, close
FROM `asl-ml-immersion.stock_src.price_history`
WHERE symbol='{stock_symbol}'
ORDER BY date
ax = df.plot(x='date', y='close')
Explanation: There have been some major fluctations in individual stocks. For example, there were major drops during the early 2000's for tech companies.
End of explanation
stock_symbol = 'IBM'
%%with_globals
%%bigquery --project {PROJECT} df
SELECT date, close
FROM `asl-ml-immersion.stock_src.price_history`
WHERE symbol='{stock_symbol}'
ORDER BY date
IBM_STOCK_SPLIT_DATE = '1979-05-10'
ax = df.plot(x='date', y='close')
ax.vlines(pd.to_datetime(IBM_STOCK_SPLIT_DATE),
          0, 500, linestyle='dashed', color='grey', alpha=0.7);
Explanation: Stock splits can also impact our data - causing a stock price to rapidly drop. In practice, we would need to clean all of our stock data to account for this. This would be a major effort! Fortunately, in the case of IBM, for example, all stock splits occurred before the year 2000.
Learning objective 2
End of explanation
%%with_globals
%%bigquery --project {PROJECT} df
SELECT *
FROM `asl-ml-immersion.stock_src.snp500`
df.industry.value_counts().plot(kind='barh');
Explanation: S&P companies list
End of explanation
%%with_globals
%%bigquery --project {PROJECT} df
WITH sp_prices AS
    (
    SELECT a.*, b.industry
    FROM `asl-ml-immersion.stock_src.price_history` a 
        JOIN `asl-ml-immersion.stock_src.snp500` b
    USING (symbol)
    WHERE date >= "2000-01-01"
    )
SELECT Date, industry, AVG(close) as close
FROM sp_prices
GROUP BY Date, industry
ORDER BY industry, Date
df.head()
Explanation: We can join the price histories table with the S&P 500 table to compare industries:
Learning objective 1,2
End of explanation
# Pandas `unstack` to make each industry a column. Useful for plotting.
df_ind = df.set_index(['industry', 'Date']).unstack(0).dropna()
df_ind.columns = [c[1] for c in df_ind.columns]
df_ind.head()
ax = df_ind.plot(figsize=(16, 8))
# Move legend down.
ax.legend(loc='upper center', bbox_to_anchor=(0.5, -0.05), shadow=True, ncol=2)
Explanation: Using pandas we can "unstack" our table so that each industry has it's own column. This will be useful for plotting.
End of explanation
def min_max_scale(df):
    return (df - df.min()) / df.max()
scaled = min_max_scale(df_ind)
ax = scaled.plot(figsize=(16, 8))
ax.legend(loc='upper center', bbox_to_anchor=(0.5, -0.05), shadow=True, ncol=2);
Explanation: Let's scale each industry using min/max scaling. This will put all of the stocks on the same scale. Currently it can be hard to see the changes in stocks over time across industries.
Learning objective 1
End of explanation
SMOOTHING_WINDOW = 30  # Days.
rolling = scaled.copy()
for col in scaled.columns:
    rolling[col] = scaled[col].rolling(SMOOTHING_WINDOW).mean()
ax = rolling.plot(figsize=(16, 8))
ax.legend(loc='upper center', bbox_to_anchor=(0.5, -0.05), shadow=True, ncol=2);
Explanation: We can also create a smoothed version of the plot above using a rolling mean. This is a useful transformation to make when visualizing time-series data.
End of explanation
<END_TASK>