<jupyter_start><jupyter_text>Packages<jupyter_code>import matplotlib.pyplot as plt
from matplotlib import style
import time
from IPython.display import clear_output
import pandas as pd
import numpy as np
import os<jupyter_output><empty_output><jupyter_text>Data_original<jupyter_code>train_raw = pd.read_csv("/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/data/sales_train_validation.csv")
test_raw = pd.read_csv("/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/data/sales_train_evaluation.csv")
prices_raw = pd.read_csv("/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/data/sell_prices.csv")
calendar_raw = pd.read_csv("/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/data/calendar.csv")
submit_sample = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/data/sample_submission.csv')

test_raw.head(10)<jupyter_output><empty_output><jupyter_text>基本圖表<jupyter_code>test_raw['state_id'].hist(xlabelsize=15,ylabelsize=15, bins=5,legend=True)  
test_raw['store_id'].hist(xlabelsize=8,ylabelsize=15, bins=20,legend=True ,figsize = (10,5))  
train_raw[train_raw['store_id']=="CA_1"]['dept_id'].hist(xlabelsize=10,ylabelsize=15, bins=15,legend=True,figsize = (10,5))
print(train_raw['dept_id'].describe())
print("FOODS 品項數目為： ", train_raw[train_raw['store_id']=="CA_1"][train_raw['cat_id']=="FOODS"]["cat_id"].count())
print("HOBBIES 品項數目為： ", train_raw[train_raw['store_id']=="CA_1"][train_raw['cat_id']=="HOBBIES"]["cat_id"].count())
print("HOUSEHOLD 品項數目為： ", train_raw[train_raw['store_id']=="CA_1"][train_raw['cat_id']=="HOUSEHOLD"]["cat_id"].count())
calendar_raw['event_type_1'].sort_values(ascending=True).hist(xlabelsize=15,ylabelsize=15, bins=10,legend=True,figsize = (10,5))
print("2011 年總促銷活動數量： ",calendar_raw[calendar_raw['year']==2011]['event_type_1'].count() + calendar_raw[calendar_raw['year']==2011]['event_type_2'].count())
print("2012 年總促銷活動數量： ",calendar_raw[calendar_raw['year']==2012]['event_type_1'].count() + calendar_raw[calendar_raw['year']==2012]['event_type_2'].count())
print("2013 年總促銷活動數量： ",calendar_raw[calendar_raw['year']==2013]['event_type_1'].count() + calendar_raw[calendar_raw['year']==2013]['event_type_2'].count())
print("2014 年總促銷活動數量： ",calendar_raw[calendar_raw['year']==2014]['event_type_1'].count() + calendar_raw[calendar_raw['year']==2014]['event_type_2'].count())
print("2015 年總促銷活動數量： ",calendar_raw[calendar_raw['year']==2015]['event_type_1'].count() + calendar_raw[calendar_raw['year']==2015]['event_type_2'].count())
print("2016 年總促銷活動數量： ",calendar_raw[calendar_raw['year']==2016]['event_type_1'].count() + calendar_raw[calendar_raw['year']==2016]['event_type_2'].count())
calendar_raw['event_type_2'].sort_values(ascending=True).hist(xlabelsize=15,ylabelsize=15, bins=10,legend=True,figsize = (10,5))
calendar_raw[calendar_raw['year']==2015]['event_type_1'].sort_values(ascending=True).hist(xlabelsize=15,ylabelsize=15, bins=10,legend=True,figsize = (10,5))

calendar_raw['snap_WI'].hist(xlabelsize=15,ylabelsize=15,legend=True,figsize = (10,5))
calendar_raw[calendar_raw['year']==2015]['snap_WI'].hist(xlabelsize=15,ylabelsize=15,legend=True,figsize = (10,5))
print("CA 總天數: ", len(calendar_raw["snap_CA"]))
print("CA SNAP天數: ", calendar_raw["snap_CA"].sum())
print("TX 總天數: ", len(calendar_raw["snap_TX"]))
print("TX SNAP天數: ", calendar_raw["snap_TX"].sum())
print("WI 總天數: ", len(calendar_raw["snap_WI"]))
print("WI SNAP天數: ", calendar_raw["snap_WI"].sum())<jupyter_output>CA 總天數:  1969
CA SNAP天數:  650
TX 總天數:  1969
TX SNAP天數:  650
WI 總天數:  1969
WI SNAP天數:  650<jupyter_text>檢視資料集<jupyter_code>calendar_raw
test_raw
prices_raw
prices_raw['wm_yr_wk'].describe()<jupyter_output><empty_output><jupyter_text>資料整理 - part1 calender info to prices data<jupyter_code>analysis_df = calendar_raw[['d','wm_yr_wk']].copy()
analysis_df.drop_duplicates('d','first',inplace=True)
analysis_df
prices_clean1 = prices_raw.merge(analysis_df, on='wm_yr_wk')
prices_clean1
train_new = pd.DataFrame(columns=['store_id','item_id','wm_yr_wk','sell_price', 'd',	'units'])
start = time.time()

for i in range(1941):
  a = i + 1
  day = 'd_' + str(a)
  train_new = pd.concat([train_new , prices_clean1[prices_clean1['d']==day].merge(test_raw[['store_id','item_id', day]], on=['store_id','item_id']).rename(columns={day:"units"})], ignore_index=True)

  end = time.time()

  print(end-start, " loop:", a)

  clear_output(wait=True)
analysis_df1 = calendar_raw[['d', 'date']].copy()
analysis_df1.drop_duplicates('date','first',inplace=True)
train_new = train_new.merge(analysis_df1, on='d')
train_new['sales'] = train_new['sell_price'] * train_new['units']
train_new['item_type'] = train_new.item_id.str[0:-6]
event_info = calendar_raw[['date', 'event_name_1', 'event_type_1', 'event_name_2', 'event_type_2']].fillna(0)
train_new = train_new.merge(event_info, on='date')
train_new.to_csv('train_new.csv',index=False)
predict_new = pd.DataFrame(columns=['store_id','item_id','wm_yr_wk','sell_price', 'd',	'units'])
start = time.time()

for i in range(1941,1969):
  a = i + 1
  day = 'd_' + str(a)
  predict_new = pd.concat([predict_new , prices_clean1[prices_clean1['d']==day].merge(test_raw[['store_id','item_id']], on=['store_id','item_id']).rename(columns={day:"units"})], ignore_index=True)

  end = time.time()

  print(end-start, " loop:", a)

  clear_output(wait=True)
analysis_df1 = calendar_raw[['d', 'date']].copy()
analysis_df1.drop_duplicates('date','first',inplace=True)
predict_new = predict_new.merge(analysis_df1, on='d')
predict_new['item_type'] = predict_new.item_id.str[0:-6]
event_info = calendar_raw[['date', 'event_name_1', 'event_type_1', 'event_name_2', 'event_type_2']].fillna(0)
predict_new = predict_new.merge(event_info, on='date')
# predict_new.to_csv('predict_new.csv',index=False)<jupyter_output><empty_output><jupyter_text>Data_new<jupyter_code>train_new_cols =['store_id',	'item_id',	'wm_yr_wk',	'sell_price',	'd',	'units',	'date',	'sales',	'item_type',	'event_name_1',	'event_type_1',	'event_name_2',	'event_type_2']
# train_new_cols =['item_id','units',"d",'store_id']
train_new = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/train_new.csv', usecols=train_new_cols, dtype={'item_id':"category","units": "int16"})

predict_new_cols = ['store_id',	'item_id',	'wm_yr_wk',	'sell_price',	'd',	'units']
predict_new = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/predict_new.csv', usecols=predict_new_cols)
len(train_new[train_new['d']=="d_1"])
len(prices_raw[prices_raw['wm_yr_wk'] == prices_raw['wm_yr_wk'].min()])
predict_new.head(10)<jupyter_output><empty_output><jupyter_text>資料整理 - part2 Overall Sales Analysis<jupyter_code>overall_sales = train_new[['date','sales']].groupby(by=["date"]).sum()
overall_sales.reset_index(inplace=True)
overall_sales['date'] = pd.to_datetime(overall_sales['date'])
import matplotlib.pyplot as plt
from matplotlib import style

style.use('ggplot')
%matplotlib inline

plt.figure(figsize = (15,10))
plt.plot(overall_sales["date"], overall_sales["sales"],c = "b")  

# 設定圖例，參數為標籤、位置
plt.title("Overall_Sales (d_1 - d_1941)")
plt.legend(labels=["day sales"])
plt.xlabel("date")
plt.ylabel("sales (units * sell_price)")

plt.show()
# 逢聖誕節 (12/25) walmart 多數分店沒有營業 
overall_sales[overall_sales['sales']<500]
overall_sales_op = train_new[train_new['item_id'].isin(train_new[train_new['d']=="d_1"]['item_id'])][['date','sales']].groupby(by=["date"]).sum()
overall_sales_op.reset_index(inplace=True)
overall_sales_op['date'] = pd.to_datetime(overall_sales_op['date'])
style.use('ggplot')
%matplotlib inline

plt.figure(figsize = (15,10))
plt.plot(overall_sales_op["date"], overall_sales_op["sales"],c = "b")  

# 設定圖例，參數為標籤、位置
plt.title("Overall_Sales (d_1 - d_1941) - Specific Old Product")
plt.legend(labels=["day sales"])
plt.xlabel("date")
plt.ylabel("sales (units * sell_price)")

plt.show()
len(train_new['item_id'].unique())
train_new['item_type'] = train_new.item_id.str[0:-6]
overall_sales_bp = train_new[['item_id','sales']].groupby(by=["item_id"]).sum()
overall_sales_bp.reset_index(inplace=True)
overall_sales_bp = overall_sales_bp.sort_values(by='sales', ascending=True)

fig, ax = plt.subplots(figsize = (12,8))

ax.barh(overall_sales_bp["item_id"][-10:], overall_sales_bp["sales"][-10:], align='center', alpha=0.4)
ax.ticklabel_format(axis='x', useMathText=True)
ax.set_title("Sales Top 10 - by Product (d_1 - d_1941)")
ax.set_xlabel("sales (units * sell_price)")
ax.set_ylabel("product")

plt.show()
overall_sales_bs = train_new[['store_id','sales']].groupby(by=["store_id"]).sum()
overall_sales_bs.reset_index(inplace=True)
overall_sales_bs = overall_sales_bs.sort_values(by='sales', ascending=True)

fig, ax = plt.subplots(figsize = (12,8))

ax.barh(overall_sales_bs["store_id"], overall_sales_bs["sales"], align='center', alpha=1)
ax.ticklabel_format(axis='x', useMathText=True)
ax.set_title("Sales Ranking - by Branch (d_1 - d_1941)")
ax.set_xlabel("sales (units * sell_price)")
ax.set_ylabel("branch")

plt.show()
fig1, ax1 = plt.subplots(figsize = (12,8))
explode = (0, 0, 0, 0, 0, 0, 0, 0, 0, 0.1)
ax1.pie(overall_sales_bs["sales"], labels=overall_sales_bs["store_id"], autopct='%1.1f%%',explode=explode,textprops={'fontsize': 18})
ax1.axis('equal')

plt.show()<jupyter_output><empty_output><jupyter_text>ML part  Training Data Preparation<jupyter_code>### 資料準備

### 資料整理 efficient way: https://towardsdatascience.com/%EF%B8%8F-load-the-same-csv-file-10x-times-faster-and-with-10x-less-memory-%EF%B8%8F-e93b485086c7

### 特徵值 - 店家別(one hot)、年度、月份(one hot)、商品類別(one hot)、商品項目(one hot)、商品價格、活動類別1(one hot)、活動類別2(one hot)

store_oh = pd.get_dummies(train_new['store_id'])
year = pd.DataFrame(pd.DatetimeIndex(train_new['date']).year)
month = pd.get_dummies(pd.DatetimeIndex(train_new['date']).month)
item_type_oh = pd.get_dummies(train_new['item_type'])
prices = train_new['sell_price']

event_1_name = pd.get_dummies(train_new[['event_name_1']])
event_1_type = pd.get_dummies(train_new[['event_type_1']])
event_2_name = pd.get_dummies(train_new[['event_name_2']])
event_2_type = pd.get_dummies(train_new[['event_type_2']])
overall_sales_pq = train_new[['item_id','units']].groupby(by=["item_id"]).sum()
overall_sales_pq.reset_index(inplace=True)
overall_sales_pq = overall_sales_pq.sort_values(by='units', ascending=True)
overall_sales_pq['item_index'] = range(0,3049)
# item_id_train = train_new[['item_id']].merge(overall_sales_pq[['item_id','item_index']], on='item_id')['item_index']
# os.mkdir('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns')

# store_oh.to_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/store_oh.csv',index=False)
# year.to_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/year.csv',index=False)
# month.to_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/month.csv',index=False)
# item_type_oh.to_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/item_type_oh.csv',index=False)
# prices.to_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/prices.csv',index=False)

# event_1_name[event_1_name.columns[2:].tolist()].to_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/event_1_name.csv',index=False)
# event_1_type[event_1_type.columns[2:].tolist()].to_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/event_1_type.csv',index=False)
# event_2_name[event_2_name.columns[2:].tolist()].to_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/event_2_name.csv',index=False)
# event_2_type[event_2_type.columns[2:].tolist()].to_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/event_2_type.csv',index=False)

# item_id_train.to_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/item_id_train.csv',index=False)<jupyter_output><empty_output><jupyter_text>Load columns data<jupyter_code>store_oh_cols = ['CA_1', 'CA_2', 'CA_3', 'CA_4', 'TX_1', 'TX_2', 'TX_3', 'WI_1', 'WI_2','WI_3']
year_cols = ['date']
month_cols = ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12']
item_type_oh_cols = ['FOODS', 'HOBBIES', 'HOUSEHOLD']
prices_cols = ['sell_price']
item_id_train_cols = ['item_index']

event_1_name_cols = ['event_name_1_Chanukah End', 'event_name_1_Christmas',
       'event_name_1_Cinco De Mayo', 'event_name_1_ColumbusDay',
       'event_name_1_Easter', 'event_name_1_Eid al-Fitr',
       'event_name_1_EidAlAdha', "event_name_1_Father's day",
       'event_name_1_Halloween', 'event_name_1_IndependenceDay',
       'event_name_1_LaborDay', 'event_name_1_LentStart',
       'event_name_1_LentWeek2', 'event_name_1_MartinLutherKingDay',
       'event_name_1_MemorialDay', "event_name_1_Mother's day",
       'event_name_1_NBAFinalsEnd', 'event_name_1_NBAFinalsStart',
       'event_name_1_NewYear', 'event_name_1_OrthodoxChristmas',
       'event_name_1_OrthodoxEaster', 'event_name_1_Pesach End',
       'event_name_1_PresidentsDay', 'event_name_1_Purim End',
       'event_name_1_Ramadan starts', 'event_name_1_StPatricksDay',
       'event_name_1_SuperBowl', 'event_name_1_Thanksgiving',
       'event_name_1_ValentinesDay', 'event_name_1_VeteransDay']

event_1_type_cols = ['event_type_1_Cultural', 'event_type_1_National',
       'event_type_1_Religious', 'event_type_1_Sporting']

event_2_name_cols = ['event_name_2_<NAME>', 'event_name_2_Easter',
       "event_name_2_Father's day", 'event_name_2_OrthodoxEaster']

event_2_type_cols = ['event_type_2_Cultural', 'event_type_2_Religious']
store_oh = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/store_oh.csv', dtype="int8", usecols=store_oh_cols)
year = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/year.csv', dtype="int16", usecols=year_cols)
month = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/month.csv', dtype="int8", usecols=month_cols)
item_type_oh = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/item_type_oh.csv', dtype="int8", usecols=item_type_oh_cols)
prices = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/prices.csv', dtype="float32", usecols=prices_cols)
item_id_train = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/item_id_train.csv', dtype={"item_index": "category"}, usecols=item_id_train_cols)
event_1_name = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/event_1_name.csv', dtype="int8", usecols=event_1_name_cols)
event_1_type = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/event_1_type.csv', dtype="int8", usecols=event_1_type_cols)
event_2_name = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/event_2_name.csv', dtype="int8", usecols=event_2_name_cols)
event_2_type = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/training_columns/event_2_type.csv', dtype="int8", usecols=event_2_type_cols)
train_new['d'] = train_new.d.str[2:]
training_data = pd.concat([store_oh, year, month, item_type_oh, prices, item_id_train, event_1_name, event_1_type, event_2_name, event_2_type, train_new[['units']],train_new[['d']]], axis=1)
training_data.info()
# training_data.tail(1500000).head(500000).cov()[['units']].abs().sort_values(by='units',ascending=False)[training_data.tail(1500000).head(500000).cov()[['units']].abs().sort_values(by='units',ascending=False)['units']>=0.05].index.tolist()
att_all = []
for i in range(30):
  a = i+1
  att = training_data.tail(a*500000).head(500000).cov()[['units']].abs().sort_values(by='units',ascending=False)[training_data.tail(a*500000).head(500000).cov()[['units']].abs().sort_values(by='units',ascending=False)['units']>=0.05].index.tolist()
  att_all+=att

set(att_all)
# selected_columns = list(set(att_all))
# selected_columns.remove('units')

selected_columns = ["CA_1", "CA_2", "CA_3", "CA_4",
                    "TX_1", "TX_2", "TX_3",
                    "WI_1", "WI_2", "WI_3",
                    "3", "4", "9", "10",
                    "item_index", "d",
                    "FOODS", "HOBBIES", "HOUSEHOLD",
                    'event_name_1_Christmas',
                    'event_type_1_National',
                    'sell_price']<jupyter_output><empty_output><jupyter_text>Data shuffle<jupyter_code>### method1 - Data set too large, out of RAM

# from sklearn.utils import shuffle
# training_data = shuffle(training_data)
### method2 - 

rm_sampling = np.random.choice(training_data.tail(15000000)[selected_columns].index.values, 12000000, replace = False)
sample_index = np.setdiff1d(training_data.tail(15000000)[selected_columns].index.values, rm_sampling)<jupyter_output><empty_output><jupyter_text>Training & Result - 1<jupyter_code>from sklearn.tree import DecisionTreeRegressor
from sklearn.metrics import r2_score
from sklearn.metrics import mean_squared_error
from math import sqrt

dtree = DecisionTreeRegressor(max_depth=12, min_samples_leaf=1, random_state=8)
dtree.fit(training_data.tail(15000000).loc[sample_index][selected_columns], 
          train_new.tail(15000000).loc[sample_index]["units"])
pred_train_tree= dtree.predict(training_data.tail(50000)[selected_columns])
print(np.sqrt(mean_squared_error(train_new['units'].tail(50000),pred_train_tree)))
print(r2_score(train_new['units'].tail(50000), pred_train_tree))
training_data.tail()<jupyter_output><empty_output><jupyter_text>Prediction - 1<jupyter_code>store_oh_p = pd.get_dummies(predict_new['store_id'])
year_p = pd.DataFrame(pd.DatetimeIndex(predict_new['date']).year)
month_p = pd.get_dummies(pd.DatetimeIndex(predict_new['date']).month)
item_type_oh_p = pd.get_dummies(predict_new['item_type'])
prices_p = predict_new['sell_price']

event_1_name_p = pd.get_dummies(predict_new[['event_name_1']])
event_1_type_p = pd.get_dummies(predict_new[['event_type_1']])
event_2_name_p = pd.get_dummies(predict_new[['event_name_2']])
event_2_type_p = pd.get_dummies(predict_new[['event_type_2']])
overall_sales_pq = train_new[['item_id','units']].groupby(by=["item_id"]).sum()
overall_sales_pq.reset_index(inplace=True)
overall_sales_pq = overall_sales_pq.sort_values(by='units', ascending=True)
overall_sales_pq['item_index'] = range(0,3049)
item_id_train_p = predict_new[['item_id']].merge(overall_sales_pq[['item_id','item_index']], on='item_id')['item_index']
predicting_data = pd.concat([store_oh_p, year_p, month_p, item_type_oh_p, prices_p, item_id_train_p, event_1_name_p, event_1_type_p, event_2_name_p, event_2_type_p], axis=1)
predicting_data.columns
p_cols_append =[]
for i in range(len(selected_columns)):
  if selected_columns[i] in predicting_data.columns:
    None
  else:
    p_cols_append.append(selected_columns[i])
predicting_data[p_cols_append] = 0
predict_result= dtree.predict(predicting_data[selected_columns])
predict_result
submit_sample
ml_1_result = pd.DataFrame(columns=["item_id", "store_id", "d", "units"], dtype=np.int8)
ml_1_result['item_id'] = predict_new['item_id']
ml_1_result['store_id'] = predict_new['store_id']
ml_1_result['d'] = predict_new['d']
ml_1_result['units'] = predict_result
ml_1_result.to_csv("ml_1_result2.csv",index=False)
ml_1_result
ml_1_result = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/ml_1_result.csv')
ml_1_result
### to submission format
ml_1_result["id"] = ml_1_result["item_id"] + "_" +ml_1_result["store_id"] + "_evaluation"
submission_1 = ml_1_result[["id","d","units"]].pivot(index="id",columns="d", values="units")
submission_1
submission_1 = ml_1_result[["id","d","units"]].pivot(index="id",columns="d", values="units")
submission_1 = submission_1.reset_index().rename_axis(None, axis=1)

for i in range(28):
  a = i + 1942
  b = i + 1

  old_name = "d_" + str(a)
  new_name = "F" + str(b)

  submission_1 = submission_1.rename(columns={old_name: new_name})

submission_1
predict_result_va = dtree.predict(training_data.tail(853720)[selected_columns])

ml_1_result_va = pd.DataFrame(columns=["item_id", "store_id", "d", "units"], dtype=np.int8)

ml_1_result_va['item_id'] = train_new.tail(853720)['item_id'].astype("object")
ml_1_result_va['store_id'] = train_new.tail(853720)['store_id'].astype("object")
ml_1_result_va['d'] = train_new.tail(853720)['d'].astype("object")
ml_1_result_va['units'] = predict_result_va.astype("float16")
# ml_1_result_va.to_csv("ml_1_result2_va.csv",index=False)
ml_1_result_va
ml_1_result_va["item_id"] = ml_1_result_va["item_id"].astype('str')
ml_1_result_va["store_id"] = ml_1_result_va["store_id"].astype('str')
ml_1_result_va["id"] = ml_1_result_va["item_id"] + "_" +ml_1_result_va["store_id"] + "_validation"
submission_1_va = ml_1_result_va[["id","d","units"]].pivot(index="id",columns="d", values="units")

submission_1_va = submission_1_va.reset_index().rename_axis(None, axis=1)

for i in range(28):
  a = i + 1914
  b = i + 1

  old_name = "d_" + str(a)
  new_name = "F" + str(b)

  submission_1_va = submission_1_va.rename(columns={old_name: new_name})

submission_1_va
submission_completed = pd.concat([submission_1_va,submission_1], sort=False)
submission_completed.to_csv("sumission_V1.csv", index=False)<jupyter_output><empty_output><jupyter_text>Training & Result -2 (incompleted)<jupyter_code>import lightgbm as lgb
np.random.seed(8)

# sampling = np.random.choice(training_data.index.values, 1200000, replace = False)
# sample_index = np.setdiff1d(training_data.index.values, sampling)

x_train = lgb.Dataset(training_data.tail(15000000).loc[sample_index], label = train_new.tail(15000000).loc[sample_index], categorical_feature=["item_index"], free_raw_data=False)
valid_data = lgb.Dataset(training_data.tail(500000), label = train_new.tail(500000),categorical_feature=["item_index"],free_raw_data=False)
params = {
    "objective" : "poisson",
    "force_row_wise" : True,
    "learning_rate" : 0.1,
    "sub_row" : 0.7,
    "lambda_l2" : 0.01,
    "metric": ["rmse"],
    'num_iterations' : 1000,
    'num_leaves': 128,
    "min_data_in_leaf": 1024,
}
m_lgb = lgb.train(params, x_train, valid_sets = [valid_data], verbose_eval=20) 
m_lgb<jupyter_output><empty_output><jupyter_text>AI Part  觀察單一產品趨勢變化<jupyter_code>train_new
food_3_586 = train_new[train_new['item_id']=="FOODS_3_586"][["store_id","date","units"]]
food_3_586 = food_3_586.groupby(by=["date"]).sum()
food_3_586.reset_index(inplace=True)
food_3_586['date'] = pd.to_datetime(food_3_586['date'])
food_3_586
plt.figure(figsize = (15,10))
plt.plot(food_3_586['date'], food_3_586['units'],c = "g")  

# 設定圖例，參數為標籤、位置
plt.title("FOODS_3_586 units sold (d_1 - d_1941)")
plt.legend(labels=["units sold"])
plt.xlabel("date")
plt.ylabel("units")

plt.show()<jupyter_output><empty_output><jupyter_text>AI training data preparation - 1 (incompleted)<jupyter_code>test_raw = pd.read_csv("/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/data/sales_train_evaluation.csv")
for i in range(1941):
  a = i + 1
  days = "d_" + str(a)
  test_raw[days] = test_raw[days].astype('int16')
ai_train = test_raw[test_raw.columns.to_list()[6:]].T
from sklearn.preprocessing import MinMaxScaler

scaler = MinMaxScaler()
ai_train_scaled = scaler.fit_transform(ai_train)
ai_train_scaled
type(ai_train_scaled)
x_train = []
y_train = []
for i in range(21, 1941):
    x_train.append(ai_train_scaled[i-21:i])
    y_train.append(ai_train_scaled[i][0:30490])


x_train = np.array(x_train,dtype="float32")
y_train = np.array(y_train,dtype="float32")

x_train.shape<jupyter_output><empty_output><jupyter_text>Model Building -1 (incompleted)<jupyter_code>import tensorflow as tf

model = tf.keras.Sequential()
model.add(tf.keras.layers.LSTM(units =64,input_shape = (np.array(x_train).shape[1], np.array(x_train).shape[2]),return_sequences=True))
model.add(tf.keras.layers.Dropout(0.2))
model.add(tf.keras.layers.LSTM(units =64))
model.add(tf.keras.layers.Dense(30490))

model.compile(
  loss='mean_squared_error',
  optimizer=tf.keras.optimizers.Adam(0.001)
)
model.summary()
model.fit(x_train, y_train, epochs = 12, batch_size = 28)
test_d_1942= ai_train[-21:]
test_d_1942_scaled = scaler.transform(test_d_1942)

x_test = []
x_test.append(test_d_1942_scaled)
x_test = np.array(x_test, dtype="float32")

predict_d_1942=model.predict([x_test])
predict_d_1942
ai_train[-14:]
predict_d_1942 = scaler.inverse_transform(predict_d_1942)
predict_d_1942
predict_d_1942.max()
x_train[0].shape
try1 = x_train[0]
try2 = x_train[0]

A = np.vstack((try1,try2))

A.shape<jupyter_output><empty_output><jupyter_text>AI training data preparation -2<jupyter_code>test_raw = pd.read_csv("/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/data/sales_train_evaluation.csv")
prices_raw = pd.read_csv("/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/data/sell_prices.csv")
calendar_raw = pd.read_csv("/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/data/calendar.csv")
submit_sample = pd.read_csv('/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/data/sample_submission.csv')
### 轉換資料格式 (降低 memory usage)

for i in test_raw:
  if test_raw[i].dtype == "int64":
    test_raw[i] = test_raw[i].astype("int16")
  elif test_raw[i].dtype == "float64":
    test_raw[i] = test_raw[i].astype("float32")

for i in prices_raw:
  if prices_raw[i].dtype == "int64":
    prices_raw[i] = prices_raw[i].astype("int16")
  elif prices_raw[i].dtype == "float64":
    prices_raw[i] = prices_raw[i].astype("float32")

for i in calendar_raw:
  if calendar_raw[i].dtype == "int64":
     calendar_raw[i] = calendar_raw[i].astype("int16")
  elif calendar_raw[i].dtype == "float64":
    calendar_raw[i] = calendar_raw[i].astype("float32")
ai_train = test_raw.sort_values(by='id', ascending=True)[test_raw.columns.to_list()[6:]].T
ai_train.info()
prices_raw['id'] = prices_raw['item_id'] + "_" + prices_raw['store_id']
price_date = prices_raw[["id","wm_yr_wk","sell_price"]].merge(calendar_raw[['d','wm_yr_wk']], on='wm_yr_wk')
prices_att = price_date[["id","d","sell_price"]].pivot(index="id",columns="d", values="sell_price")
prices_att =  prices_att.reset_index().rename_axis(None, axis=1).sort_values(by='id', ascending=True).fillna(0)

### 只到 d_1941
prices_att1 = prices_att[test_raw.columns.to_list()[6:]]
prices_train = prices_att1.T
prices_train.info()
calendar_raw['timing'] = 0
c = 0
for i in calendar_raw['event_name_1'].isnull():
  if i is False:
    calendar_raw['timing'][c] += 1
  c += 1

b = 0
for i in calendar_raw['event_name_2'].isnull():
  if i is False:
    calendar_raw['timing'][b] += 1
  b += 1
event = price_date[["id","d","sell_price"]].merge(calendar_raw[['d','snap_CA','snap_TX','snap_WI','timing']], on='d')

event['timing'] = (event['snap_CA']+1) * event['timing']
event['timing'] = (event['snap_TX']+1) * event['timing']
event['timing'] = (event['snap_WI']+1) * event['timing']
event['timing'] = event['timing'].astype('int8')
event_att = event[["id","d","timing"]].pivot(index="id",columns="d", values="timing")
event_att = event_att.reset_index().rename_axis(None, axis=1).sort_values(by='id', ascending=True).fillna(0)

event_att1 = event_att[test_raw.columns.to_list()[6:]]
event_train = event_att1.T
event_train.info()
from sklearn.preprocessing import MinMaxScaler

scaler_units = MinMaxScaler()
ai_train_scaled = scaler_units.fit_transform(ai_train).astype('float32')

scaler_prices = MinMaxScaler()
prices_train_scaled = scaler_prices.fit_transform(prices_train).astype('float32')

scaler_event = MinMaxScaler()
event_train_scaled = scaler_event.fit_transform(event_train).astype('float32')
x_train = []
y_train = []
for i in range(1941-750, 1941):
    x_train.append(np.vstack((prices_train_scaled[i-7:i+1], event_train_scaled[i-7:i+1], ai_train_scaled[i-7:i])))
    y_train.append(ai_train_scaled[i][0:30490])


x_train = np.array(x_train,dtype="float32")
y_train = np.array(y_train,dtype="float32")

x_train.shape

# x_train.append(np.vstack((prices_train_scaled[i-7:i], event_train_scaled[i-7:i], ai_train_scaled[i-7:i])))<jupyter_output><empty_output><jupyter_text>model building -2<jupyter_code>import tensorflow as tf

model = tf.keras.Sequential()
model.add(tf.keras.layers.LSTM(units =64,input_shape = (np.array(x_train).shape[1], np.array(x_train).shape[2]),return_sequences=True))
model.add(tf.keras.layers.Dropout(0.2))
model.add(tf.keras.layers.LSTM(units =64))
model.add(tf.keras.layers.Dense(30490, activation='relu'))

model.compile(
  loss='mean_squared_error',
  optimizer=tf.keras.optimizers.Adam(0.001)
)
model.summary()
model.fit(x_train, y_train, epochs = 60, batch_size = 28)
x_valid = []
y_valid = []
for i in range(1913, 1941):
    x_valid.append(np.vstack((prices_train_scaled[i-7:i+1], event_train_scaled[i-7:i+1], ai_train_scaled[i-7:i])))
    y_valid.append(ai_train_scaled[i][0:30490])

x_valid = np.array(x_valid,dtype="float32")
y_valid = np.array(y_valid,dtype="float32")

x_valid.shape
predict2 = model.predict(x_valid)
predict2 = scaler_units.inverse_transform(predict2)
predict2.shape
columns_p = []
for i in range(28):
  a = i +1
  day = "F" + str(a)
  columns_p.append(day)
pre_valid = pd.DataFrame(predict2.T, columns=columns_p)
pre_valid
pre_valid.describe()<jupyter_output><empty_output><jupyter_text>evaluation set<jupyter_code>columns_e = []
for i in range(35):
  a = i + 1935
  day = "d_" + str(a)
  columns_e.append(day)
prices_eva_scaled = scaler_prices.transform(prices_att[columns_e].T)
event_eva_scaled = scaler_event.transform(event_att[columns_e].T)
for i in range(28):
  f_att = np.vstack((prices_train_scaled[i:i+8], event_train_scaled[i:i+8], ai_train_scaled[-7:]))
  predict_f = model.predict(np.array([f_att]))
  ai_train_scaled = np.vstack((ai_train_scaled,predict_f))
pre_eva = pd.DataFrame(scaler_units.inverse_transform(ai_train_scaled[-28:]).T, columns=columns_p)
pre_eva 
pre_eva.describe()
pre_valid['id'] = prices_att['id'] + "_validation"
pre_eva['id'] = prices_att['id'] + "_evaluation"
columns_f = ['id', 'F1', 'F2', 'F3', 'F4', 'F5', 'F6', 'F7', 'F8', 'F9', 'F10',
             'F11', 'F12', 'F13', 'F14', 'F15', 'F16', 'F17', 'F18', 'F19', 'F20',
             'F21', 'F22', 'F23', 'F24', 'F25', 'F26', 'F27', 'F28']

sumbit_final = pd.concat([pre_valid[columns_f], pre_eva[columns_f]])
# sumbit_final = pd.concat([CCC[columns_f], pre_eva[columns_f]])
### 去除 負值

columns_gg = ['F1', 'F2', 'F3', 'F4', 'F5', 'F6', 'F7', 'F8', 'F9', 'F10',
             'F11', 'F12', 'F13', 'F14', 'F15', 'F16', 'F17', 'F18', 'F19', 'F20',
             'F21', 'F22', 'F23', 'F24', 'F25', 'F26', 'F27', 'F28']

sumbit_final[sumbit_final[columns_gg] < 0] = np.nan
sumbit_final = sumbit_final.fillna(0)
sumbit_final.to_csv("/content/drive/MyDrive/ken_lab/Kaggle_M5 Forecasting - Accuracy/sumission_V13.csv", index=False)
sumbit_final.describe()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17427665096606748, 'fraction_numerical': 0.0389310775737768, 'mean_word_length': 8.109748978400466, 'pattern_counts': {'":': 10, '<': 79, '<?xml version=': 0, '>': 79, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '612762', 'license': 'MIT', 'n_tokens_mistral': 13450, 'n_tokens_neox': 12737, 'n_words': 1473, 'path': 'Interview.ipynb', 'repo_name': 'chenkenanalytic/test', 'size': '31216'}