# This Python 3 environment comes with many helpful analytics libraries installed
# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python
# For example, here's several helpful packages to load in 

import numpy as np # linear algebra
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

# Input data files are available in the "../input/" directory.
# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory

from subprocess import check_output
print(check_output(["ls", "../input"]).decode("utf8"))

# Any results you write to the current directory are saved as output.

train = pd.read_csv('../input/train.csv',
                    dtype={'Agencia_ID': 'uint16',
                           'Canal_ID': 'int8',
                           'Ruta_SAK': 'uint16',
                           'Cliente_ID': 'int32',
                           'Producto_ID': 'uint16',
                           'Demanda_uni_equil': 'float32'},
                    usecols=['Agencia_ID',
                             'Canal_ID',
                             'Ruta_SAK',
                             'Cliente_ID',
                             'Producto_ID',
                             'Demanda_uni_equil'])

train['Demanda_uni_equil'] = train['Demanda_uni_equil'].apply(lambda x: 1.006*np.log1p(x + 0.01) - 0.01)
train['MeanP'] = train.groupby('Producto_ID')['Demanda_uni_equil'].transform(np.mean).astype('float32')
train['MeanC'] = train.groupby('Cliente_ID')['Demanda_uni_equil'].transform(np.mean).astype('float32')
train['MeanPA'] = train.groupby(['Producto_ID',
                                 'Agencia_ID'])['Demanda_uni_equil'].transform(np.mean).astype('float32')
train['MeanPR'] = train.groupby(['Producto_ID',
                                 'Ruta_SAK'])['Demanda_uni_equil'].transform(np.mean).astype('float32')
train['MeanPCA'] = train.groupby(['Producto_ID',
                                  'Cliente_ID',
                                  'Agencia_ID'])['Demanda_uni_equil'].transform(np.mean).astype('float32')
mean = train['Demanda_uni_equil'].mean()
train.drop_duplicates(inplace=True)
test = pd.read_csv('../input/train.csv', dtype={'Agencia_ID': 'uint16',
                                                'Canal_ID': 'int8',
                                                'Ruta_SAK': 'uint16',
                                                'Cliente_ID': 'int32',
                                                'Producto_ID': 'uint16',
                                                'Demanda_uni_equil': 'float32',
                                                'id': 'int32'})


test = test.merge(train[['Producto_ID',
                             'Ruta_SAK',
                             'Agencia_ID',
                             'Cliente_ID',
                             'MeanPR',
                             'MeanP',
                             'MeanC',
                             'MeanPA',
                             'MeanPCA']],
                      how='left',
                      on=['Producto_ID', 'Ruta_SAK', 'Agencia_ID', 'Cliente_ID'])

test.loc[:, 'Demanda_uni_equil'] = test.loc[:, 'MeanPCA'].apply(np.expm1)*0.72 +\
                            test.loc[:, 'MeanPR'].apply(np.expm1)*0.189 + 0.1
indeks = test['Demanda_uni_equil'].isnull()
test.loc[indeks, 'Demanda_uni_equil'] = test.loc[indeks, 'MeanPR'].apply(np.expm1)*0.745 + 0.19
indeks = test['Demanda_uni_equil'].isnull()
test.loc[indeks, 'Demanda_uni_equil'] = test.loc[indeks, 'MeanC'].apply(np.expm1)*0.82 + 0.87
indeks = test['Demanda_uni_equil'].isnull()
test.loc[indeks, 'Demanda_uni_equil'] = test.loc[indeks, 'MeanPA'].apply(np.expm1)*0.54 + 0.8
indeks = test['Demanda_uni_equil'].isnull()
test.loc[indeks, 'Demanda_uni_equil'] = test.loc[indeks, 'MeanP'].apply(np.expm1)*0.45 + 1
indeks = test['Demanda_uni_equil'].isnull()
test.loc[indeks, 'Demanda_uni_equil'] = np.expm1(mean)

test.loc[:, ['id', 'Demanda_uni_equil']].to_csv('submission.csv', index=False)