from pandas import DataFrame, read_csv
import pandas as pd
import numpy as np
import plotly.offline as py
import cufflinks
from plotly import tools
py.init_notebook_mode(connected=True)
import plotly.graph_objs as go
import time
import datetime

df = pd.read_csv('SEG_Saleshistory_Stores.csv')
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(
            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')

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

items_with_sales.index

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

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


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

import itertools

beg_date = df['date'].min()
end_date = df['date'].max()
dates = pd.DatetimeIndex(start=beg_date, end=end_date, freq='D')

items = df['item'].value_counts().index
stores = df['store'].value_counts().index
all_periods = pd.DataFrame(list(itertools.product(dates, stores, items)), columns=['date', 'store', 'item'])

all_periods['sales'] = 0
all_periods.date = pd.to_datetime(df.date)
all_periods.item = df.item.astype('category')
all_periods.store = df.store.astype('category')

incl_periods = df.groupby(['store', 'item', 'date']).sum().fillna(0).reset_index()

df_all = pd.concat([all_periods, df]).groupby(['store', 'item', 'date']).sum().fillna(0).reset_index()
len(df_all)

df_f = df_all.copy()

# Assign each record to its respective intra-week group.
#
# These are the intra-week periods that each day of the week belongs to.
freq = list([[0, 0, 1, 1, 1, 0, 0],
            [0, 0, 1, 1, 2, 2, 0]
            ])

# Map the day of the week of each record to its respective intra-week period.
for i, f in enumerate(freq):
    df_f['freq' + str(i + 2) + '_per'] = df_f['date'].dt.weekday.map(pd.Series(f))

# Assign each record to its respective group within each series of intra-week groups.
# Group membership is indicated in a separate column by the end date of the group.
#
#     1. Calculate the numeric day of the week for each date in the range of dates
#        in the data.
#     2. Create a boolean array with an entry for each record indicating whether
#        the date of the record falls on a day of the week on which an intra-week
#        period ends.
#     3. Calculate the cumulative sum of the boolean array for the range of dates, which
#        will then represent the sequential period each date in our range belongs to.
#     4. Index the cumulative sums by the range of dates to create a lookup table.
#     5. Map the 'date' column in our data to the sequence number using the lookup table.
#     6. Group the lookup table by the period, aggregating the date column by max, which
#        represents the end date of each sequential period, to create another lookup table.
#     7. Map the sequence number series we created earlier to the period ending date using
#        the new lookup table and add it to our data frame.
#

# These are the days of the week that new periods begin on for order frequencies of two
# and three times per week.
period_ends = list([[2, 5],
                   [2, 4, 6]
                   ])

# Execute the same process for each of our order frequencies
for i, p in enumerate(period_ends):
    # Steps 1 through 4
    periods = pd.Series(dates.weekday).isin(period_ends[i]).cumsum()
    date_lookup = pd.DataFrame({'date': dates, 'period': periods})
    date_lookup.set_index('date', inplace=True)
    
    # Step 5
    seq_col = df_f['date'].map(date_lookup.period)

    # Step 6
    period_lookup = date_lookup.reset_index().groupby('period').max()
    period_lookup.to_csv('freq' + str(i+2) + '.csv')

    # Step 7
    df_f['freq' + str(i+2) + '_end'] = seq_col.map(period_lookup.date)

df_f[df_f['date'] > '2016-10-06'].head(10)

df_final = calc_trailing(3)
df_final.head(20)

df_final_masked = remove_missing(df_final)
store = 'SEGWD104'
item = 41783
df_final_masked.query('(store == @store) & (item == @item)').head()

data = encode_cat_vars(df_final_masked)
data.head()

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

predict = clf.predict(X_test)
predict_neg = predict < 0
error = predict - y_test
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(sum(error)-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))

data_predict = data_test.iloc[:,:5]
data_predict[freq_avg] = data[freq_avg] * std + mu
data_predict['predict'] = predict
data_predict['variance'] = predict - data_predict['sales']
data_predict.head()

py.iplot(data_predict.groupby(freq).sum()[['sales', 'predict']].iplot(asFigure=True,
                               kind='bar',xTitle='Dates',yTitle='Sales',title='Actual vs. Predicted'))

pivot = (pd.pivot_table(data_predict,index=['store','item'],columns=[freq],values=['sales', 'predict', 'variance'],aggfunc=np.sum,margins=True, margins_name='Total')
         .swaplevel(axis=1)
         .sortlevel(0, axis=1, sort_remaining=False)
        )
pivot