{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import matplotlib.pyplot as plt\nfrom datetime import datetime\nimport numpy as np\nimport pandas as pd\nfrom sklearn.preprocessing import StandardScaler\nfrom sklearn.linear_model import LinearRegression, Lasso, Ridge","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:30:45.100944Z","iopub.execute_input":"2022-07-22T06:30:45.102310Z","iopub.status.idle":"2022-07-22T06:30:45.108554Z","shell.execute_reply.started":"2022-07-22T06:30:45.102228Z","shell.execute_reply":"2022-07-22T06:30:45.107365Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train = pd.read_csv(\"../input/store-sales-time-series-forecasting/train.csv\")\ntest = pd.read_csv(\"../input/store-sales-time-series-forecasting/test.csv\")\noil = pd.read_csv(\"../input/store-sales-time-series-forecasting/oil.csv\")\nholidays = pd.read_csv(\"../input/store-sales-time-series-forecasting/holidays_events.csv\")\ntransactions = pd.read_csv(\"../input/store-sales-time-series-forecasting/transactions.csv\")\nstores = pd.read_csv(\"../input/store-sales-time-series-forecasting/stores.csv\")\nsubmission = pd.read_csv(\"../input/store-sales-time-series-forecasting/sample_submission.csv\")","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:30:45.360274Z","iopub.execute_input":"2022-07-22T06:30:45.360948Z","iopub.status.idle":"2022-07-22T06:30:46.850891Z","shell.execute_reply.started":"2022-07-22T06:30:45.360906Z","shell.execute_reply":"2022-07-22T06:30:46.849476Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# dateカラムが現状文字列データ(オブジェクト)なので，timestamp型に変更\ntrain['date'] = pd.to_datetime(train['date'])\ntest['date'] = pd.to_datetime(test['date'])\noil['date'] = pd.to_datetime(oil['date'])\nholidays['date'] = pd.to_datetime(holidays['date'])\ntransactions['date'] = pd.to_datetime(transactions['date'])","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:30:46.852678Z","iopub.execute_input":"2022-07-22T06:30:46.853097Z","iopub.status.idle":"2022-07-22T06:30:47.222709Z","shell.execute_reply.started":"2022-07-22T06:30:46.853059Z","shell.execute_reply":"2022-07-22T06:30:47.221392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_org = train.copy()","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:30:47.224661Z","iopub.execute_input":"2022-07-22T06:30:47.225240Z","iopub.status.idle":"2022-07-22T06:30:47.266661Z","shell.execute_reply.started":"2022-07-22T06:30:47.225183Z","shell.execute_reply":"2022-07-22T06:30:47.265174Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 店舗の売り上げが0である日はその店舗が休みであると判断し，除去\ntmp = train_org.groupby(['date', 'store_nbr'])['sales'].sum().reset_index()\ntmp['delete1'] = 0\ntmp.loc[tmp['sales'] == 0, 'delete1'] = 1\ntmp.drop('sales', axis=1, inplace=True)\ntrain = pd.merge(train, tmp, on=['date', 'store_nbr'])","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:30:47.269173Z","iopub.execute_input":"2022-07-22T06:30:47.269714Z","iopub.status.idle":"2022-07-22T06:30:47.946761Z","shell.execute_reply.started":"2022-07-22T06:30:47.269680Z","shell.execute_reply":"2022-07-22T06:30:47.945594Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 全店舗で売り上げが0であるfamilyは，そのそもその期間に販売していないと判断し，除去\ntmp = train_org.groupby(['date', 'family'])['sales'].sum().reset_index()\ntmp['delete2'] = 0\ntmp.loc[tmp['sales'] == 0, 'delete2'] = 1\ntmp.drop('sales', axis=1, inplace=True)\ntrain = pd.merge(train, tmp, on=['date', 'family'])","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:30:47.948318Z","iopub.execute_input":"2022-07-22T06:30:47.948981Z","iopub.status.idle":"2022-07-22T06:30:48.842562Z","shell.execute_reply.started":"2022-07-22T06:30:47.948933Z","shell.execute_reply":"2022-07-22T06:30:48.841403Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# oilの欠損を線形補完\noil_new = pd.DataFrame(pd.date_range(start=oil['date'].min(), end=oil['date'].max(), freq='D'), columns=['date'])\noil_new = pd.merge(oil_new, oil, how='left', on='date')\noil_new['dcoilwtico'] = oil_new.drop('date', axis=1).interpolate(limit_direction='both')['dcoilwtico']\n\n# trainとtestに分ける\noil_train = oil_new[(train['date'].min() <= oil_new['date']) & (oil_new['date'] <= train['date'].max())]\noil_test = oil_new[(test['date'].min() <= oil_new['date']) & (oil_new['date'] <= test['date'].max())]\n\n# oilを結合\ntrain = pd.merge(train, oil_train, how='left', on='date')\ntest = pd.merge(test, oil_test, how='left', on='date')","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:30:48.844343Z","iopub.execute_input":"2022-07-22T06:30:48.844696Z","iopub.status.idle":"2022-07-22T06:30:49.323108Z","shell.execute_reply.started":"2022-07-22T06:30:48.844656Z","shell.execute_reply":"2022-07-22T06:30:49.321911Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# storesを結合\ntrain = pd.merge(train, stores, how='left', on='store_nbr')\ntest = pd.merge(test, stores, how='left', on='store_nbr')","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:30:49.324532Z","iopub.execute_input":"2022-07-22T06:30:49.324860Z","iopub.status.idle":"2022-07-22T06:30:49.902001Z","shell.execute_reply.started":"2022-07-22T06:30:49.324828Z","shell.execute_reply":"2022-07-22T06:30:49.900679Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# holidaysの重複を消す\n# a = holidays[holidays['locale'] holidays['type'] == 'Holiday']\n# a = holidays[holidays['type'] == 'Holiday']\n# a.groupby('date')\n# holidays[holidays['date'] == datetime(2017,12,22)]\n# holidays.groupby(['date']).count()","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:30:49.903798Z","iopub.execute_input":"2022-07-22T06:30:49.904140Z","iopub.status.idle":"2022-07-22T06:30:49.908882Z","shell.execute_reply.started":"2022-07-22T06:30:49.904097Z","shell.execute_reply":"2022-07-22T06:30:49.907305Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# holidaysを結合\nholidays_new = pd.DataFrame(pd.date_range(start=holidays['date'].min(), end=holidays['date'].max(), freq='D'), columns=['date'])\nholidays_new = pd.merge(holidays_new, holidays, how='left', on='date')\nholidays_train = holidays_new[(train['date'].min() <= holidays_new['date']) & (holidays_new['date'] <= train['date'].max())]\nholidays_test = holidays_new[(test['date'].min() <= holidays_new['date']) & (holidays_new['date'] <= test['date'].max())]\ntrain = pd.merge(train, holidays_train, how='left', on='date')\n# train = pd.merge(train, holidays_train, how='left', left_on=['date', 'city'], right_on=['date', 'locale_name'])\ntest = pd.merge(test, holidays_test, how='left', on='date')\n# test = pd.merge(test, holidays_test, how='left', left_on=['date', 'city'], right_on=['date', 'locale_name'])","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:30:49.913643Z","iopub.execute_input":"2022-07-22T06:30:49.914215Z","iopub.status.idle":"2022-07-22T06:30:50.682739Z","shell.execute_reply.started":"2022-07-22T06:30:49.914177Z","shell.execute_reply":"2022-07-22T06:30:50.681790Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# deleteフラグがたっている行を削除\ntrain = train[(train['delete1'] == 0) & (train['delete2'] == 0)]\n\n# delete1，delete2カラムを削除\ntrain.drop('delete1', axis=1, inplace=True)\ntrain.drop('delete2', axis=1, inplace=True)\n\n# idカラムを削除\ntrain.drop('id', axis=1, inplace=True)\ntest.drop('id', axis=1, inplace=True)\n\n# store_nbrカラムを削除\ntrain.drop('store_nbr', axis=1, inplace=True)\ntest.drop('store_nbr', axis=1, inplace=True)\n\n# 地震の発生をone-hotで表現\ntrain['earthquake'] = 0\ntrain.loc[train['date'] >= datetime(2016, 4, 16), 'earthquake'] = 1\ntest['earthquake'] = 0\ntest.loc[test['date'] >= datetime(2016, 4, 16), 'earthquake'] = 1\n\n# dateカラムを変更\ntrain['day']  = pd.to_datetime(train['date']).dt.day\ntrain['month']  = pd.to_datetime(train['date']).dt.month\ntrain['year']  = pd.to_datetime(train['date']).dt.year\ntest['day']  = pd.to_datetime(test['date']).dt.day\ntest['month']  = pd.to_datetime(test['date']).dt.month\ntest['year']  = pd.to_datetime(test['date']).dt.year\ntrain.drop('date', axis=1, inplace=True)\ntest.drop('date', axis=1, inplace=True)\n\n# salesを分離\ntrain_y = train['sales']\ntrain.drop('sales', inplace=True, axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:30:50.684245Z","iopub.execute_input":"2022-07-22T06:30:50.684850Z","iopub.status.idle":"2022-07-22T06:30:54.721287Z","shell.execute_reply.started":"2022-07-22T06:30:50.684816Z","shell.execute_reply":"2022-07-22T06:30:54.720113Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"scaling_columns = ['onpromotion', 'dcoilwtico']\nscaler = StandardScaler()\nscaler.fit(train[scaling_columns])\nscaled_train = pd.DataFrame(scaler.transform(train[scaling_columns]), columns=scaling_columns, index=train.index)\ntrain.update(scaled_train)\nscaled_test = pd.DataFrame(scaler.transform(test[scaling_columns]), columns=scaling_columns, index=test.index)\ntest.update(scaled_test)","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:30:54.722489Z","iopub.execute_input":"2022-07-22T06:30:54.722810Z","iopub.status.idle":"2022-07-22T06:30:55.039902Z","shell.execute_reply.started":"2022-07-22T06:30:54.722780Z","shell.execute_reply":"2022-07-22T06:30:55.038800Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"dummies_columns = ['family', 'city', 'state', 'type_x', 'cluster', 'type_y', 'locale', 'locale_name', 'description', 'transferred']\n# dummies_columns = ['day', 'month', 'year', 'family', 'type_x', 'locale', 'locale_name', 'description', 'transferred', 'city', 'state', 'type_y', 'cluster']\ntrain_objs_num = len(train)\ndataset = pd.concat(objs=[train, test], axis=0)\ndataset_preprocessed = pd.get_dummies(dataset, columns=dummies_columns, drop_first=True, dummy_na=True)\ntrain = dataset_preprocessed[:train_objs_num]\ntest = dataset_preprocessed[train_objs_num:]","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:30:55.041319Z","iopub.execute_input":"2022-07-22T06:30:55.041656Z","iopub.status.idle":"2022-07-22T06:31:01.527716Z","shell.execute_reply.started":"2022-07-22T06:30:55.041613Z","shell.execute_reply":"2022-07-22T06:31:01.526472Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"model = LinearRegression()\nmodel.fit(train, train_y)","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:31:01.529265Z","iopub.execute_input":"2022-07-22T06:31:01.529719Z","iopub.status.idle":"2022-07-22T06:31:44.339025Z","shell.execute_reply.started":"2022-07-22T06:31:01.529673Z","shell.execute_reply":"2022-07-22T06:31:44.337909Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pred = model.predict(test)\nsubmission['sales'] = pred","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:31:44.340995Z","iopub.execute_input":"2022-07-22T06:31:44.341794Z","iopub.status.idle":"2022-07-22T06:31:44.408355Z","shell.execute_reply.started":"2022-07-22T06:31:44.341749Z","shell.execute_reply":"2022-07-22T06:31:44.406963Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission.loc[submission['sales'] < 0, 'sales'] = 0","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:31:44.409839Z","iopub.execute_input":"2022-07-22T06:31:44.410299Z","iopub.status.idle":"2022-07-22T06:31:44.419715Z","shell.execute_reply.started":"2022-07-22T06:31:44.410256Z","shell.execute_reply":"2022-07-22T06:31:44.418230Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission.to_csv('submission.csv',index=False)","metadata":{"execution":{"iopub.status.busy":"2022-07-22T06:31:44.421345Z","iopub.execute_input":"2022-07-22T06:31:44.422267Z","iopub.status.idle":"2022-07-22T06:31:44.534894Z","shell.execute_reply.started":"2022-07-22T06:31:44.422110Z","shell.execute_reply":"2022-07-22T06:31:44.533691Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 1. データの内容を確認\n## trainの各column\n#### id\n\n#### date\n- 2013-01-01 ~ 2017-08-15\n\n#### store_nbr\n- 店の識別番号．storesに識別番号ごとの詳細情報あり．\n- 1 ~ 54\n\n#### family\n- 商品のカテゴリ\n- 33種類\n\n#### sales\n- 応答変数\n\n#### onpromotion\n- 商品カテゴリごとの宣伝された商品の個数\n- 0 ~ 741","metadata":{}},{"cell_type":"markdown","source":"## データの確認：test  \n- id\n- date  \n    2017-08-16 ~ 2017-08-31  \n    testデータ後15日分\n- store_nbr\n- family\n- onpromotion","metadata":{}},{"cell_type":"markdown","source":"## データの確認：oil  \n- date  \n    2013-01-01 ~ 2017-08-31  \n    テストデータ分も含まれている\n- dcoilwtico  \n欠損あり","metadata":{}},{"cell_type":"markdown","source":"## データの確認：holidays\n- date  \n    2012-03-02 ~ 2017-12-26  \n    テストデータ分も含まれている\n- type  \n    6種類\n- locale\n- locale_name\n- description\n- transferred","metadata":{}},{"cell_type":"markdown","source":"## データの確認：transactions\nテストデータ分が含まれていない  \n使えない？","metadata":{}},{"cell_type":"markdown","source":"## データの確認：stores\ncityはholidaysと内容が被る","metadata":{}}]}