<jupyter_start><jupyter_text>Modules<jupyter_code>%load_ext autoreload
%autoreload 2
import sys
import gc
from pathlib2 import Path
import numpy as np
import pandas as pd

sys.path.append('..')
from src import config

display_settings = {
    'max_columns': 999,
    'expand_frame_repr': True,
    'max_rows': 999,
    'precision': 4,
    'show_dimensions': True
}

for op, value in display_settings.items():
    pd.set_option("display.{}".format(op), value)

%matplotlib inline<jupyter_output><empty_output><jupyter_text>Data  Load datasets<jupyter_code># prices
dtypes_prices = {
    'store_id': 'str',
    'item_id': 'str',
    'wm_yr_wk': 'int16',
    'selle_price': 'float32'
}
cat_cols_prices = ['store_id', 'item_id']

df_prices = pd.read_csv(config.DATA_FILES['prices'], dtype=dtypes_prices)
df_prices[cat_cols_prices] = df_prices[cat_cols_prices].astype('category')

print(f'Prices series dataset:{df_prices.shape}')
df_prices.head()
# calendar
dtypes_calendar = {
    'date': 'str',
    'wm_yr_wk': 'int16',
    'weekday': 'str',
    'wday': 'int8',
    'month': 'int8',
    'year': 'int16',
    'd': 'str',
    'event_name_1': 'str',
    'event_type_1': 'str',
    'event_name_2': 'str',
    'event_type_2': 'str',
    'snap_CA': 'bool',
    'snap_TX': 'bool',
    'snap_WI': 'bool',
}
cat_cols_calendar = ['weekday', 'event_type_1', 'event_type_2']

df_calendar = pd.read_csv(config.DATA_FILES['calendar'], dtype=dtypes_calendar, parse_dates=['date'])
df_calendar[cat_cols_calendar] = df_calendar[cat_cols_calendar].astype('category')

print(f'Calendar dataset:{df_calendar.shape}')
df_calendar.head()
#df_calendar_test = df_calendar.copy()

#for col in ['event_type_1', 'event_type_2', 'snap_CA', 'snap_TX', 'snap_WI']:
#    df_calendar_test[f'{col}_days_since'] = df_calendar_test.apply(lambda x: np.argmax(df_calendar_test.iloc[x.name::-1][col].tolist()), axis=1)

#df_calendar_test
horizon = 28
last_day = 1913

dtypes_sales = {
    'id': 'str',
    'item_id': 'str',
    'dept_id': 'str',
    'cat_id': 'str',
    'store_id': 'str',
    'state_id': 'str',
}
dtypes_sales_ts = {f'd_{day}': 'int16' for day in range(1, last_day)}
cat_cols_sales = ['item_id', 'dept_id', 'cat_id', 'store_id', 'state_id']

df_sales = pd.read_csv(config.DATA_FILES['sales'], dtype=dict(dtypes_sales, **dtypes_sales_ts))
df_sales[cat_cols_sales] = df_sales[cat_cols_sales].astype('category')

# append days to forecast
for i in range (horizon):
    df_sales[f'd_{last_day + i + 1}'] = np.nan

print(f'Sales series dataset:{df_sales.shape}')
df_sales.head()<jupyter_output>Sales series dataset:(30490, 1947)<jupyter_text>Melt and merge<jupyter_code>id_vars = ['id'] + cat_cols_sales
df_sales = df_sales.melt(id_vars=id_vars, var_name='d', value_name='sold_qty')

df_sales.head()
df_sales = df_sales.merge(df_calendar, on='d', copy=False)
del df_calendar
gc.collect()

df_sales = df_sales.merge(df_prices, on=['store_id', 'item_id', 'wm_yr_wk'], copy=False)
del df_prices
gc.collect()

df_sales.d = df_sales.d.str.replace('d_', '').astype('int16')
df_sales.drop(['wm_yr_wk', 'weekday'], axis=1, inplace=True)
df_sales.rename({'d': 'day_ind'}, axis=1, inplace=True)

print(f'Consolidated sales series dataset:{df_sales.shape}')
df_sales.head()<jupyter_output>Consolidated sales series dataset:(46881677, 20)<jupyter_text>Save to parquet<jupyter_code>path = config.get_processed_filename('data_merged.parquet')
df_sales.to_parquet(path)

del df_sales
gc.collect()

print(f'Consolidated dataset size:{(Path(path).stat().st_size / 1e6):,.2f}Mo')<jupyter_output>Consolidated dataset size:354.31Mo
{'fraction_non_alphanumeric': 0.17047991071428573, 'fraction_numerical': 0.01953125, 'mean_word_length': 6.3765432098765435, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 14, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '102958', 'license': 'MIT', 'n_tokens_mistral': 1517, 'n_tokens_neox': 1429, 'n_words': 243, 'path': 'notebooks/01-melt_and_merge.ipynb', 'repo_name': 'clabrugere/M5-forecasting', 'size': '3584'}