from pandas import DataFrame, read_csv
import pandas as pd
import numpy as np
import plotly.offline as py
py.init_notebook_mode()
import plotly.graph_objs as go
from plotly import tools

df = pd.read_csv('sales.csv')
df.OrderDate = pd.to_datetime(df.OrderDate)
df.columns = ['date', 'store', 'item', 'sales']
df.date = pd.to_datetime(df.date)
df.item = df.item.astype('category')
df.store = df.store.astype('category')
df.dtypes

df.describe(include='all')

df_total = df.groupby(pd.Grouper(freq='W', key='date')).sum().fillna(0).unstack('date', 0)
df_total.index.levels[1]

len(df_total) == len(df_total.index.levels[1])

trace = go.Scatter(
    x = df_total.index.levels[1],
    y = df_total
)

layout = go.Layout(
    title='Total Sales'
)


fig = go.Figure(data=[trace], layout=layout)
py.iplot(fig, filename='total-sales')

df_1w = df.groupby(['store']+[pd.Grouper(freq='W', key='date')]).sum().fillna(0).unstack('date', 0)

rows = 10
cols = 3
spidx = np.arange(rows*cols).reshape(rows,cols)

fig = tools.make_subplots(rows=rows, cols=cols, shared_yaxes=True, subplot_titles=df_1w.index[:rows*cols])

for i in range(rows):

    for j in range(cols):

        trace = go.Scatter(c
            x = df_1w.iloc[1].index.levels[1],
            y = df_1w.iloc[spidx[i,j]],
        )

        fig.append_trace(trace, i+1, j+1)

fig['layout'].update(height=250*rows, title='Sales by Store', showlegend=False);
py.iplot(fig, filename='sales-by-store')

store_sales = df.groupby(['store']+[pd.Grouper(freq='W', key='date')]).sum().fillna(0).unstack('date')
stores_with_sales = store_sales['sales'].where(store_sales.sales > 0).count()

stores_with_sales.index

trace = go.Bar(
    x = stores_with_sales.index,
    y = stores_with_sales
)

layout = go.Layout(
    title='No. of Stores with Sales'
)


fig = go.Figure(data=[trace], layout=layout)
py.iplot(fig, filename='stores-with-sales')

df_1w = df.groupby(['item']+[pd.Grouper(freq='W', key='date')]).sum().fillna(0).unstack('date', 0)
rows = 13
cols = 3

fig = tools.make_subplots(rows=rows, cols=cols, shared_yaxes=True, subplot_titles=df_1w.index[:rows*cols])

spidx = np.arange(rows*cols).reshape(rows,cols)

for i in range(rows):

    for j in range(cols):

        trace = go.Scatter(
            x = df_1w.iloc[1].index.levels[1],
            y = df_1w.iloc[spidx[i,j]],
        )

        fig.append_trace(trace, i+1, j+1)

fig['layout'].update(height=250*rows, title='Sales by Store', showlegend=False);
py.iplot(fig, filename='sales-by-store')

df_model = df.groupby(['store', 'item']+[pd.Grouper(freq='W', key='date')]).sum().fillna(0)
rolling_sum = df_model.apply(lambda x:x.rolling(window=3).mean())
rolling_sum.shift(-1)
df_model['sales_avg'] = rolling_sum.shift(-1)['sales']
df_model.head(10)

df_model['cum_sales'] = df_model.groupby(level=[0,1]).cumsum()['sales']
df_model.reset_index(inplace=True)
df_model.describe(include='all')

df_model_masked = df_model[df_model.cum_sales != 0]
df_model_masked.describe(include='all')

df_final.head()

df_final.describe()

data = df_final.dropna().as_matrix()
data.shape

from sklearn import linear_model
clf = linear_model.SGDRegressor(n_iter=200)
clf.fit(X_train, y_train)

predict = clf.predict(X_test)
predict_neg = predict < 0
error = y_test - predict
error_neg = predict_neg @ error
np.savetxt('modelparams.csv', clf.predict(np.eye(X_test.shape[1])), delimiter=",")
print('R-squared: {:.{p}f}'.format(clf.score(X_test, y_test), p=4))
print('Total error in sales quantity: {:.{p}f}'.format(sum(error), p=0))
print('Total error as a % of actual: {:.{p}f}%'.format(sum(error) / sum(y_test)*100, p=2))
print('Total error in sales quantity with zero min prediction: {:.{p}f}'.format(error_neg, p=0))
print('Total error as a % of actual with zero min prediction: {:.{p}f}%'.format((sum(error)+error_neg) / sum(y_test)*100, p=2))

predicted = go.Bar(
    name = 'predicted',
    y = clf.predict(X_test)
)

actual = go.Bar(
    name = 'actual',
    y = y_test
)

layout = go.Layout(
    title='Actual vs. Predicted'
)

fig = go.Figure(data=[actual, predicted], layout=layout)
py.iplot(fig, filename='actual-vs-predicted')