from fastai2.tabular.all import *

path = Config().data/'rossmann'
path.ls()

table_names = ['train', 'store', 'store_states', 'state_names', 'googletrend', 'weather', 'test']
tables = [pd.read_csv(path/f'{fname}.csv', low_memory=False) for fname in table_names]
train, store, store_states, state_names, googletrend, weather, test = tables

len(train), len(test)

train.head()

test.head()

store.head().T

store_states.head()

state_names.head()

weather.head().T

googletrend.head()

def join_df(left, right, left_on, right_on=None, suffix='_y'):
    if right_on is None: right_on = left_on
    return left.merge(right, how='left', left_on=left_on, right_on=right_on, 
                      suffixes=("", suffix))

weather = join_df(weather, state_names, "file", "StateName")
weather[['file', 'Date', 'State', 'StateName']].head()

len(weather[weather.State.isnull()])

weather.drop(columns=['file', 'StateName'], inplace=True)

store = join_df(store, store_states, 'Store')
store = join_df(store, weather, 'State')

len(store[store.Mean_TemperatureC.isnull()])

googletrend.head()

googletrend['Date'] = googletrend.week.str.split(' - ', expand=True)[0]
googletrend['State'] = googletrend.file.str.split('_', expand=True)[2]
googletrend.head()

store['State'].unique(),googletrend['State'].unique()

googletrend.loc[googletrend.State=='NI', "State"] = 'HB,NI'

trend_de = googletrend[googletrend.file == 'Rossmann_DE'][['Date', 'trend']]

googletrend = join_df(googletrend, trend_de, 'Date', suffix='_DE')

googletrend.drop(columns=['file', 'week'], axis=1, inplace=True)
googletrend = googletrend[~googletrend['State'].isnull()]

googletrend.head()

googletrend = add_datepart(googletrend, 'Date', drop=False)

googletrend.head().T

googletrend = googletrend[['trend', 'State', 'trend_DE', 'Week', 'Year']]
store = add_datepart(store, 'Date', drop=False)
store = join_df(store, googletrend, ['Week', 'Year', 'State'])

make_date(train, 'Date')
make_date(test, 'Date')
train_fe = join_df(train, store, ['Store', 'Date'])
test_fe = join_df(test, store, ['Store', 'Date'])

all_ftrs = train_fe.append(test_fe, sort=False)

all_ftrs['StateHoliday'].unique()

all_ftrs.StateHoliday = all_ftrs.StateHoliday!='0'

all_ftrs = add_elapsed_times(all_ftrs, ['Promo', 'StateHoliday', 'SchoolHoliday'], 
                             date_field='Date', base_field='Store')

[c for c in all_ftrs.columns if 'StateHoliday' in c]

train_df = all_ftrs.iloc[:len(train_fe)]
test_df  = all_ftrs.iloc[len(train_fe):]

train_df = train_df[train_df.Sales != 0.]

train_df.to_pickle(path/'train_clean')
test_df.to_pickle(path/'test_clean')