{"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":"markdown","source":"参考文献  \nhttps://www.kaggle.com/fumiyakomatsu/explanation-of-train-csv-each-variable-ver\nhttps://www.kaggle.com/chumajin/eda-of-mlb-for-starter-version","metadata":{}},{"cell_type":"markdown","source":"# 0.モジュールのインポート","metadata":{}},{"cell_type":"code","source":"import gc\nimport sys\nimport warnings\nfrom pathlib import Path\nimport os\nimport ipywidgets as widgets\nimport matplotlib.pyplot as plt\nimport numpy as np\nimport pandas as pd\nimport seaborn as sns\nwarnings.simplefilter(\"ignore\")\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:55:37.101534Z","iopub.execute_input":"2021-06-27T10:55:37.102230Z","iopub.status.idle":"2021-06-27T10:55:38.023190Z","shell.execute_reply.started":"2021-06-27T10:55:37.102093Z","shell.execute_reply":"2021-06-27T10:55:38.022187Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 1.何を予測するか確認する","metadata":{}},{"cell_type":"code","source":"example_sample_submission = pd.read_csv(\"../input/mlb-player-digital-engagement-forecasting/example_sample_submission.csv\")\nexample_sample_submission","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:55:38.024395Z","iopub.execute_input":"2021-06-27T10:55:38.024638Z","iopub.status.idle":"2021-06-27T10:55:38.076376Z","shell.execute_reply.started":"2021-06-27T10:55:38.024613Z","shell.execute_reply":"2021-06-27T10:55:38.075393Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2.どんな情報から推測するか確認する","metadata":{}},{"cell_type":"code","source":"example_test = pd.read_csv(\"../input/mlb-player-digital-engagement-forecasting/example_test.csv\")\nexample_test","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:55:38.077927Z","iopub.execute_input":"2021-06-27T10:55:38.078215Z","iopub.status.idle":"2021-06-27T10:55:38.869442Z","shell.execute_reply.started":"2021-06-27T10:55:38.078186Z","shell.execute_reply":"2021-06-27T10:55:38.868277Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Helper function to unpack json found in daily data\ndef unpack_json(json_str):\n    return np.nan if pd.isna(json_str) else pd.read_json(json_str)","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:55:38.871257Z","iopub.execute_input":"2021-06-27T10:55:38.871657Z","iopub.status.idle":"2021-06-27T10:55:38.877228Z","shell.execute_reply.started":"2021-06-27T10:55:38.871611Z","shell.execute_reply":"2021-06-27T10:55:38.876203Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"example_test.head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:55:38.878598Z","iopub.execute_input":"2021-06-27T10:55:38.878957Z","iopub.status.idle":"2021-06-27T10:55:39.004522Z","shell.execute_reply.started":"2021-06-27T10:55:38.878925Z","shell.execute_reply":"2021-06-27T10:55:39.003368Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unpack_json(example_test[\"games\"].iloc[0])","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:55:39.005592Z","iopub.execute_input":"2021-06-27T10:55:39.005846Z","iopub.status.idle":"2021-06-27T10:55:39.067658Z","shell.execute_reply.started":"2021-06-27T10:55:39.005821Z","shell.execute_reply":"2021-06-27T10:55:39.066440Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unpack_json(example_test[\"rosters\"].iloc[0])","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:55:39.068978Z","iopub.execute_input":"2021-06-27T10:55:39.069291Z","iopub.status.idle":"2021-06-27T10:55:39.095138Z","shell.execute_reply.started":"2021-06-27T10:55:39.069262Z","shell.execute_reply":"2021-06-27T10:55:39.093897Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3.train.csv","metadata":{}},{"cell_type":"code","source":"training = pd.read_csv(\"../input/mlb-player-digital-engagement-forecasting/train.csv\")\ntraining['date'] = pd.to_datetime(training['date'], format=\"%Y%m%d\")\ndisplay(training.info())","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:55:39.098623Z","iopub.execute_input":"2021-06-27T10:55:39.098927Z","iopub.status.idle":"2021-06-27T10:56:45.131532Z","shell.execute_reply.started":"2021-06-27T10:55:39.098897Z","shell.execute_reply":"2021-06-27T10:56:45.130465Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"training.head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.134690Z","iopub.execute_input":"2021-06-27T10:56:45.134967Z","iopub.status.idle":"2021-06-27T10:56:45.159073Z","shell.execute_reply.started":"2021-06-27T10:56:45.134937Z","shell.execute_reply":"2021-06-27T10:56:45.157905Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"training['date'] = pd.to_datetime(training['date'], format=\"%Y%m%d\")","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.160644Z","iopub.execute_input":"2021-06-27T10:56:45.161038Z","iopub.status.idle":"2021-06-27T10:56:45.173847Z","shell.execute_reply.started":"2021-06-27T10:56:45.160994Z","shell.execute_reply":"2021-06-27T10:56:45.173026Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3.1 nextDayPlayerEngagement\n目的変数(予測したい情報)を含んだデータ  \nこのtarget1~4を予測します","metadata":{}},{"cell_type":"code","source":"nextDayPlayerEngagement = unpack_json(training['nextDayPlayerEngagement'].iloc[0])\nnextDayPlayerEngagement.columns","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.175066Z","iopub.execute_input":"2021-06-27T10:56:45.175566Z","iopub.status.idle":"2021-06-27T10:56:45.203507Z","shell.execute_reply.started":"2021-06-27T10:56:45.175534Z","shell.execute_reply":"2021-06-27T10:56:45.202799Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nextDayPlayerEngagement","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.204611Z","iopub.execute_input":"2021-06-27T10:56:45.205053Z","iopub.status.idle":"2021-06-27T10:56:45.225123Z","shell.execute_reply.started":"2021-06-27T10:56:45.205024Z","shell.execute_reply":"2021-06-27T10:56:45.224428Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3.2 games\n試合情報  \n初めのデータが2018-2-23でこの日から'gameType'がSであることから、春季トレーニングのデータが入っているとわかる\n","metadata":{}},{"cell_type":"code","source":"games = unpack_json(training['games'].iloc[53])\ngames.columns","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.226016Z","iopub.execute_input":"2021-06-27T10:56:45.226438Z","iopub.status.idle":"2021-06-27T10:56:45.250041Z","shell.execute_reply.started":"2021-06-27T10:56:45.226410Z","shell.execute_reply":"2021-06-27T10:56:45.249364Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"games","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.250976Z","iopub.execute_input":"2021-06-27T10:56:45.251355Z","iopub.status.idle":"2021-06-27T10:56:45.288192Z","shell.execute_reply.started":"2021-06-27T10:56:45.251327Z","shell.execute_reply":"2021-06-27T10:56:45.287510Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3.3 rosters\nチーム名簿情報  \nケガやマイナーへの降格情報など確認できる","metadata":{}},{"cell_type":"code","source":"rosters=unpack_json(training['rosters'].iloc[0])\nrosters.columns","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.289058Z","iopub.execute_input":"2021-06-27T10:56:45.289471Z","iopub.status.idle":"2021-06-27T10:56:45.309013Z","shell.execute_reply.started":"2021-06-27T10:56:45.289442Z","shell.execute_reply":"2021-06-27T10:56:45.308319Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"rosters","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.309882Z","iopub.execute_input":"2021-06-27T10:56:45.310263Z","iopub.status.idle":"2021-06-27T10:56:45.332848Z","shell.execute_reply.started":"2021-06-27T10:56:45.310235Z","shell.execute_reply":"2021-06-27T10:56:45.332153Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3.4 playerBoxScores\n選手の試合成績\n試合ごとに集計されていて、2018-3-29が初めてのデータだから、この日からレギュラーシーズンが始まったとわかる。","metadata":{}},{"cell_type":"code","source":"playerBoxScores = unpack_json(training['playerBoxScores'].iloc[87])\nplayerBoxScores.columns","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.333876Z","iopub.execute_input":"2021-06-27T10:56:45.334288Z","iopub.status.idle":"2021-06-27T10:56:45.386188Z","shell.execute_reply.started":"2021-06-27T10:56:45.334256Z","shell.execute_reply":"2021-06-27T10:56:45.385357Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"playerBoxScores","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.387322Z","iopub.execute_input":"2021-06-27T10:56:45.387685Z","iopub.status.idle":"2021-06-27T10:56:45.426570Z","shell.execute_reply.started":"2021-06-27T10:56:45.387654Z","shell.execute_reply":"2021-06-27T10:56:45.425855Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3.5 teamBoxScores\nチームごとの試合情報  \nその日に行われる試合数は異なるため、行数が日によって違う","metadata":{}},{"cell_type":"code","source":"teamBoxScores=unpack_json(training['teamBoxScores'].iloc[87])\nteamBoxScores.columns","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.427618Z","iopub.execute_input":"2021-06-27T10:56:45.427977Z","iopub.status.idle":"2021-06-27T10:56:45.461563Z","shell.execute_reply.started":"2021-06-27T10:56:45.427948Z","shell.execute_reply":"2021-06-27T10:56:45.460565Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"teamBoxScores","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.462879Z","iopub.execute_input":"2021-06-27T10:56:45.463173Z","iopub.status.idle":"2021-06-27T10:56:45.494495Z","shell.execute_reply.started":"2021-06-27T10:56:45.463132Z","shell.execute_reply":"2021-06-27T10:56:45.493490Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3.6 transactions\n選手やチームのトランザクション  \n一行目を見てみると、選手のトレード情報だとわかる。","metadata":{"execution":{"iopub.status.busy":"2021-06-27T01:18:21.15932Z","iopub.execute_input":"2021-06-27T01:18:21.159673Z","iopub.status.idle":"2021-06-27T01:18:21.197353Z","shell.execute_reply.started":"2021-06-27T01:18:21.159642Z","shell.execute_reply":"2021-06-27T01:18:21.196229Z"}}},{"cell_type":"code","source":"transactions=unpack_json(training['transactions'].iloc[1])\ntransactions.columns","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.495753Z","iopub.execute_input":"2021-06-27T10:56:45.496188Z","iopub.status.idle":"2021-06-27T10:56:45.520216Z","shell.execute_reply.started":"2021-06-27T10:56:45.496155Z","shell.execute_reply":"2021-06-27T10:56:45.519355Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"transactions","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.521370Z","iopub.execute_input":"2021-06-27T10:56:45.521622Z","iopub.status.idle":"2021-06-27T10:56:45.540091Z","shell.execute_reply.started":"2021-06-27T10:56:45.521589Z","shell.execute_reply":"2021-06-27T10:56:45.539137Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3.7 standings\nチームの順位情報で全30チーム分のデータがある","metadata":{}},{"cell_type":"code","source":"standings=unpack_json(training['standings'].iloc[87])\nstandings.columns","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.543016Z","iopub.execute_input":"2021-06-27T10:56:45.543321Z","iopub.status.idle":"2021-06-27T10:56:45.568952Z","shell.execute_reply.started":"2021-06-27T10:56:45.543292Z","shell.execute_reply":"2021-06-27T10:56:45.567773Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"standings","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.570595Z","iopub.execute_input":"2021-06-27T10:56:45.570852Z","iopub.status.idle":"2021-06-27T10:56:45.607338Z","shell.execute_reply.started":"2021-06-27T10:56:45.570827Z","shell.execute_reply":"2021-06-27T10:56:45.606178Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3.8 awards\n選手の表彰情報","metadata":{}},{"cell_type":"code","source":"awards=unpack_json(training['awards'].iloc[14])\nawards.columns","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.608848Z","iopub.execute_input":"2021-06-27T10:56:45.609178Z","iopub.status.idle":"2021-06-27T10:56:45.626056Z","shell.execute_reply.started":"2021-06-27T10:56:45.609106Z","shell.execute_reply":"2021-06-27T10:56:45.625044Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"awards","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.629050Z","iopub.execute_input":"2021-06-27T10:56:45.629377Z","iopub.status.idle":"2021-06-27T10:56:45.643338Z","shell.execute_reply.started":"2021-06-27T10:56:45.629348Z","shell.execute_reply":"2021-06-27T10:56:45.642302Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3.9 events\nフィールド上で起きた出来事のデータ","metadata":{}},{"cell_type":"code","source":"events=unpack_json(training['events'].iloc[87])\nevents.columns","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.644803Z","iopub.execute_input":"2021-06-27T10:56:45.645096Z","iopub.status.idle":"2021-06-27T10:56:45.855978Z","shell.execute_reply.started":"2021-06-27T10:56:45.645068Z","shell.execute_reply":"2021-06-27T10:56:45.854868Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"events","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.857457Z","iopub.execute_input":"2021-06-27T10:56:45.857852Z","iopub.status.idle":"2021-06-27T10:56:45.903297Z","shell.execute_reply.started":"2021-06-27T10:56:45.857810Z","shell.execute_reply":"2021-06-27T10:56:45.902314Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3.10 playerTwitterFollowers\n選手のTwitterフォロワー数","metadata":{}},{"cell_type":"code","source":"playerTwitterFollowers=unpack_json(training['playerTwitterFollowers'].iloc[0])\nplayerTwitterFollowers.columns","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.904624Z","iopub.execute_input":"2021-06-27T10:56:45.905257Z","iopub.status.idle":"2021-06-27T10:56:45.924742Z","shell.execute_reply.started":"2021-06-27T10:56:45.905216Z","shell.execute_reply":"2021-06-27T10:56:45.923808Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"playerTwitterFollowers","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.925985Z","iopub.execute_input":"2021-06-27T10:56:45.926615Z","iopub.status.idle":"2021-06-27T10:56:45.944210Z","shell.execute_reply.started":"2021-06-27T10:56:45.926573Z","shell.execute_reply":"2021-06-27T10:56:45.943263Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 3.11 teamTwitterFollowers\n全３０チーム公式Twitterアカウントのフォロワー数","metadata":{}},{"cell_type":"code","source":"teamTwitterFollowers=unpack_json(training['teamTwitterFollowers'].iloc[0])\nteamTwitterFollowers.columns","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.945608Z","iopub.execute_input":"2021-06-27T10:56:45.946214Z","iopub.status.idle":"2021-06-27T10:56:45.965599Z","shell.execute_reply.started":"2021-06-27T10:56:45.946172Z","shell.execute_reply":"2021-06-27T10:56:45.964840Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"teamTwitterFollowers","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.966816Z","iopub.execute_input":"2021-06-27T10:56:45.967403Z","iopub.status.idle":"2021-06-27T10:56:45.992716Z","shell.execute_reply.started":"2021-06-27T10:56:45.967360Z","shell.execute_reply":"2021-06-27T10:56:45.991537Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 4.Data Merge","metadata":{}},{"cell_type":"code","source":"df_names = ['seasons', 'teams', 'players', 'awards']\npath = \"../input/mlb-player-digital-engagement-forecasting\"\nkaggle_data_tabs = widgets.Tab()\nkaggle_data_tabs.children = list([widgets.Output() for df_name in df_names])","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:45.993925Z","iopub.execute_input":"2021-06-27T10:56:45.994194Z","iopub.status.idle":"2021-06-27T10:56:46.023898Z","shell.execute_reply.started":"2021-06-27T10:56:45.994167Z","shell.execute_reply":"2021-06-27T10:56:46.023210Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for index in range(len(df_names)):\n    kaggle_data_tabs.set_title(index, df_names[index])\n    df = pd.read_csv(os.path.join(path,df_names[index]) + \".csv\")\n    with kaggle_data_tabs.children[index]:\n        display(df)\ndisplay(kaggle_data_tabs)","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:46.025195Z","iopub.execute_input":"2021-06-27T10:56:46.025614Z","iopub.status.idle":"2021-06-27T10:56:46.172514Z","shell.execute_reply.started":"2021-06-27T10:56:46.025585Z","shell.execute_reply":"2021-06-27T10:56:46.171623Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for name in df_names:\n    globals()[name] = pd.read_csv(os.path.join(path,name)+ \".csv\")","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:46.173647Z","iopub.execute_input":"2021-06-27T10:56:46.173905Z","iopub.status.idle":"2021-06-27T10:56:46.207118Z","shell.execute_reply.started":"2021-06-27T10:56:46.173880Z","shell.execute_reply":"2021-06-27T10:56:46.206191Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#### Unnest various nested data within training (daily) data ####\ndaily_data_unnested_dfs = pd.DataFrame(data = {\n  'dfName': training.drop('date', axis = 1).columns.values.tolist()\n  })\ndaily_data_unnested_dfs['df'] = [pd.DataFrame() for row in \n  daily_data_unnested_dfs.iterrows()]\nfor df_index, df_row in daily_data_unnested_dfs.iterrows():\n    nestedTableName = str(df_row['dfName'])\n    date_nested_table = training[['date', nestedTableName]]\n    date_nested_table = (date_nested_table[\n      ~pd.isna(date_nested_table[nestedTableName])\n      ].\n      reset_index(drop = True)\n      )\n    daily_dfs_collection = []\n    for date_index, date_row in date_nested_table.iterrows():\n        daily_df = unpack_json(date_row[nestedTableName])\n        daily_df['dailyDataDate'] = date_row['date']\n        daily_dfs_collection = daily_dfs_collection + [daily_df]\n    unnested_table = pd.concat(daily_dfs_collection,ignore_index = True).set_index('dailyDataDate').reset_index()\n    # Creates 1 pandas df per unnested df from daily data read in, with same name\n    globals()[df_row['dfName']] = unnested_table    \n    daily_data_unnested_dfs['df'][df_index] = unnested_table\ndel training\ngc.collect()\n\n#### Get some information on each date in daily data (using season dates of interest) ####\ndates = pd.DataFrame(data = {'dailyDataDate': nextDayPlayerEngagement['dailyDataDate'].unique()})\ndates['date'] = pd.to_datetime(dates['dailyDataDate'].astype(str))\ndates['year'] = dates['date'].dt.year\ndates['month'] = dates['date'].dt.month\ndates_with_info = pd.merge(\n  dates,seasons,\n  left_on = 'year',right_on = 'seasonId'\n  )\ndates_with_info['inSeason'] = (\n  dates_with_info['date'].between(\n    dates_with_info['regularSeasonStartDate'],\n    dates_with_info['postSeasonEndDate'],\n    inclusive = True\n    )\n  )\ndates_with_info['seasonPart'] = np.select(\n  [ dates_with_info['date'] < dates_with_info['preSeasonStartDate'],  \n    dates_with_info['date'] < dates_with_info['regularSeasonStartDate'],\n    dates_with_info['date'] <= dates_with_info['lastDate1stHalf'],\n    dates_with_info['date'] < dates_with_info['firstDate2ndHalf'],\n    dates_with_info['date'] <= dates_with_info['regularSeasonEndDate'],\n    dates_with_info['date'] < dates_with_info['postSeasonStartDate'],\n    dates_with_info['date'] <= dates_with_info['postSeasonEndDate'],\n    dates_with_info['date'] > dates_with_info['postSeasonEndDate']], \n  [ 'Offseason',\n    'Preseason',\n    'Reg Season 1st Half',\n    'All-Star Break',\n    'Reg Season 2nd Half',\n    'Between Reg and Postseason',\n    'Postseason',\n    'Offseason'], \n  default = np.nan\n  )\n#### Add some pitching stats/pieces of info to player game level stats ####\nplayer_game_stats = (playerBoxScores.copy().\n  # Change team Id/name to reflect these come from player game, not roster\n  rename(columns = {'teamId': 'gameTeamId', 'teamName': 'gameTeamName'})\n  )\n# Adds in field for innings pitched as fraction (better for aggregation)\nplayer_game_stats['inningsPitchedAsFrac'] = np.where(\n  pd.isna(player_game_stats['inningsPitched']),\n  np.nan,\n  np.floor(player_game_stats['inningsPitched']) +\n    (player_game_stats['inningsPitched'] -\n      np.floor(player_game_stats['inningsPitched'])) * 10/3\n  )\n\n# Add in Tom Tango pitching game score (https://www.mlb.com/glossary/advanced-stats/game-score)\nplayer_game_stats['pitchingGameScore'] = (40\n#     + 2 * player_game_stats['outs']\n    + 1 * player_game_stats['strikeOutsPitching']\n    - 2 * player_game_stats['baseOnBallsPitching']\n    - 2 * player_game_stats['hitsPitching']\n    - 3 * player_game_stats['runsPitching']\n    - 6 * player_game_stats['homeRunsPitching']\n    )\n# Add in criteria for no-hitter by pitcher (individual, not multiple pitchers)\nplayer_game_stats['noHitter'] = np.where(\n  (player_game_stats['gamesStartedPitching'] == 1) &\n  (player_game_stats['inningsPitched'] >= 9) &\n  (player_game_stats['hitsPitching'] == 0),\n  1, 0\n  )\nplayer_date_stats_agg = pd.merge(\n  (player_game_stats.\n    groupby(['dailyDataDate', 'playerId'], as_index = False).\n    # Some aggregations that are not simple sums\n    agg(\n      numGames = ('gamePk', 'nunique'),\n      # Should be 1 team per player per day, but adding here for 1 exception:\n      # playerId 518617 (Jake Diekman) had 2 games for different teams marked\n      # as played on 5/19/19, due to resumption of game after he was traded\n      numTeams = ('gameTeamId', 'nunique'),\n      # Should be only 1 team for almost all player-dates, taking min to simplify\n      gameTeamId = ('gameTeamId', 'min')\n      )\n    ),\n  # Merge with a bunch of player stats that can be summed at date/player level\n  (player_game_stats.\n    groupby(['dailyDataDate', 'playerId'], as_index = False)\n    [['runsScored', 'homeRuns', 'strikeOuts', 'baseOnBalls', 'hits',\n      'hitByPitch', 'atBats', 'caughtStealing', 'stolenBases',\n      'groundIntoDoublePlay', 'groundIntoTriplePlay', 'plateAppearances',\n      'totalBases', 'rbi', 'leftOnBase', 'sacBunts', 'sacFlies',\n      'gamesStartedPitching', 'runsPitching', 'homeRunsPitching', \n      'strikeOutsPitching', 'baseOnBallsPitching', 'hitsPitching',\n      'inningsPitchedAsFrac', 'earnedRuns', \n      'battersFaced','saves', 'blownSaves', 'pitchingGameScore', \n      'noHitter'\n      ]].\n    sum()\n    ),\n  on = ['dailyDataDate', 'playerId'],\n  how = 'inner'\n  )\n#### Turn games table into 1 row per team-game, then merge with team box scores ####\n# Filter to regular or Postseason games w/ valid scores for this part\ngames_for_stats = games[\n  np.isin(games['gameType'], ['R', 'F', 'D', 'L', 'W', 'C', 'P']) &\n  ~pd.isna(games['homeScore']) &\n  ~pd.isna(games['awayScore'])\n  ]\n# Get games table from home team perspective\ngames_home_perspective = games_for_stats.copy()\n# Change column names so that \"team\" is \"home\", \"opp\" is \"away\"\ngames_home_perspective.columns = [\n  col_value.replace('home', 'team').replace('away', 'opp') for \n    col_value in games_home_perspective.columns.values]\ngames_home_perspective['isHomeTeam'] = 1\n# Get games table from away team perspective\ngames_away_perspective = games_for_stats.copy()\n# Change column names so that \"opp\" is \"home\", \"team\" is \"away\"\ngames_away_perspective.columns = [\n  col_value.replace('home', 'opp').replace('away', 'team') for \n    col_value in games_away_perspective.columns.values]\ngames_away_perspective['isHomeTeam'] = 0\n# Put together games from home/away perspective to get df w/ 1 row per team game\nteam_games = (pd.concat([\n  games_home_perspective,\n  games_away_perspective\n  ],\n  ignore_index = True)\n  )\n# Copy over team box scores data to modify\nteam_game_stats = teamBoxScores.copy()\n# Change column names to reflect these are all \"team\" stats - helps \n# to differentiate from individual player stats if/when joining later\nteam_game_stats.columns = [\n  (col_value + 'Team') \n  if (col_value not in ['dailyDataDate', 'home', 'teamId', 'gamePk',\n    'gameDate', 'gameTimeUTC'])\n    else col_value\n  for col_value in team_game_stats.columns.values\n  ]\n# Merge games table with team game stats\nteam_games_with_stats = pd.merge(\n  team_games,\n  team_game_stats.\n    # Drop some fields that are already present in team_games table\n    drop(['home', 'gameDate', 'gameTimeUTC'], axis = 1),\n  on = ['dailyDataDate', 'gamePk', 'teamId'],\n  # Doing this as 'inner' join excludes spring training games, postponed games,\n  # etc. from original games table, but this may be fine for purposes here \n  how = 'inner'\n  )\nteam_date_stats_agg = (team_games_with_stats.\n  groupby(['dailyDataDate', 'teamId', 'gameType', 'oppId', 'oppName'], \n    as_index = False).\n  agg(\n    numGamesTeam = ('gamePk', 'nunique'),\n    winsTeam = ('teamWinner', 'sum'),\n    lossesTeam = ('oppWinner', 'sum'),\n    runsScoredTeam = ('teamScore', 'sum'),\n    runsAllowedTeam = ('oppScore', 'sum')\n    )\n   )\n# Prepare standings table for merge w/ player digital engagement data\n# Pick only certain fields of interest from standings for merge\nstandings_selected_fields = (standings[['dailyDataDate', 'teamId', \n  'streakCode', 'divisionRank', 'leagueRank', 'wildCardRank', 'pct'\n  ]].\n  rename(columns = {'pct': 'winPct'})\n  )\n# Change column names to reflect these are all \"team\" standings - helps \n# to differentiate from player-related fields if/when joining later\nstandings_selected_fields.columns = [\n  (col_value + 'Team') \n  if (col_value not in ['dailyDataDate', 'teamId'])\n    else col_value\n  for col_value in standings_selected_fields.columns.values\n  ]\nstandings_selected_fields['streakLengthTeam'] = (\n  standings_selected_fields['streakCodeTeam'].\n    str.replace('W', '').\n    str.replace('L', '').\n    astype(float)\n    )\n# Add fields to separate winning and losing streak from streak code\nstandings_selected_fields['winStreakTeam'] = np.where(\n  standings_selected_fields['streakCodeTeam'].str[0] == 'W',\n  standings_selected_fields['streakLengthTeam'],\n  np.nan\n  )\nstandings_selected_fields['lossStreakTeam'] = np.where(\n  standings_selected_fields['streakCodeTeam'].str[0] == 'L',\n  standings_selected_fields['streakLengthTeam'],\n  np.nan\n  )\nstandings_for_digital_engagement_merge = (pd.merge(\n  standings_selected_fields,\n  dates_with_info[['dailyDataDate', 'inSeason']],\n  on = ['dailyDataDate'],\n  how = 'left'\n  ).\n  # Limit down standings to only in season version\n  query(\"inSeason\").\n  # Drop fields no longer necessary (in derived values, etc.)\n  drop(['streakCodeTeam', 'streakLengthTeam', 'inSeason'], axis = 1).\n  reset_index(drop = True)\n  )\n#### Merge together various data frames to add date, player, roster, and team info ####\n# Copy over player engagement df to add various pieces to it\nplayer_engagement_with_info = nextDayPlayerEngagement.copy()\n# Take \"row mean\" across targets to add (helps with studying all 4 targets at once)\nplayer_engagement_with_info['targetAvg'] = np.mean(\n  player_engagement_with_info[['target1', 'target2', 'target3', 'target4']],\n  axis = 1)\n# Merge in date information\nplayer_engagement_with_info = pd.merge(\n  player_engagement_with_info,\n  dates_with_info[['dailyDataDate', 'date', 'year', 'month', 'inSeason','seasonPart']],\n  on = ['dailyDataDate'],\n  how = 'left'\n  )\n# Merge in some player information\nplayer_engagement_with_info = pd.merge(\n  player_engagement_with_info,\n  players[['playerId', 'playerName', 'DOB', 'mlbDebutDate', 'birthCity','birthStateProvince', 'birthCountry', 'primaryPositionName']],\n   on = ['playerId'],\n   how = 'left'\n   )\n# Merge in some player roster information by date\nplayer_engagement_with_info = pd.merge(\n  player_engagement_with_info,\n  (rosters[['dailyDataDate', 'playerId', 'statusCode', 'status', 'teamId']].\n    rename(columns = {'statusCode': 'rosterStatusCode','status': 'rosterStatus','teamId': 'rosterTeamId'})\n    ),\n  on = ['dailyDataDate', 'playerId'],\n  how = 'left'\n  )\n# Merge in team name from player's roster team\nplayer_engagement_with_info = pd.merge(\n  player_engagement_with_info,\n  (teams[['id', 'teamName']].\n    rename(columns = {'id': 'rosterTeamId','teamName': 'rosterTeamName'})\n    ),\n  on = ['rosterTeamId'],\n  how = 'left'\n  )\n# Merge in some player game stats (previously aggregated) from that date\nplayer_engagement_with_info = pd.merge(\n  player_engagement_with_info,\n  player_date_stats_agg,\n  on = ['dailyDataDate', 'playerId'],\n  how = 'left'\n  )\n# Merge in team name from player's game team\nplayer_engagement_with_info = pd.merge(\n  player_engagement_with_info,\n  (teams[['id', 'teamName']].\n    rename(columns = {'id': 'gameTeamId','teamName': 'gameTeamName'})\n    ),\n  on = ['gameTeamId'],\n  how = 'left'\n  )\n# Merge in some team game stats/results (previously aggregated) from that date\nplayer_engagement_with_info = pd.merge(\n  player_engagement_with_info,\n  team_date_stats_agg.rename(columns = {'teamId': 'gameTeamId'}),\n  on = ['dailyDataDate', 'gameTeamId'],\n  how = 'left'\n  )\n# Merge in player transactions of note on that date\n# Merge in some pieces of team standings (previously filter/processed) from that date\nplayer_engagement_with_info = pd.merge(\n  player_engagement_with_info,\n  standings_for_digital_engagement_merge.\n    rename(columns = {'teamId': 'gameTeamId'}),\n  on = ['dailyDataDate', 'gameTeamId'],\n  how = 'left'\n  )\ndisplay(player_engagement_with_info)","metadata":{"execution":{"iopub.status.busy":"2021-06-27T10:56:46.208719Z","iopub.execute_input":"2021-06-27T10:56:46.209112Z","iopub.status.idle":"2021-06-27T11:00:41.233384Z","shell.execute_reply.started":"2021-06-27T10:56:46.209070Z","shell.execute_reply":"2021-06-27T11:00:41.232390Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"player_engagement_with_info.info()","metadata":{"execution":{"iopub.status.busy":"2021-06-27T11:00:41.234668Z","iopub.execute_input":"2021-06-27T11:00:41.234942Z","iopub.status.idle":"2021-06-27T11:00:41.252560Z","shell.execute_reply.started":"2021-06-27T11:00:41.234914Z","shell.execute_reply":"2021-06-27T11:00:41.251865Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"player_engagement_with_info.to_pickle(\"player_engagement_with_info.pkl\")","metadata":{"execution":{"iopub.status.busy":"2021-06-27T11:00:41.253675Z","iopub.execute_input":"2021-06-27T11:00:41.254097Z","iopub.status.idle":"2021-06-27T11:00:46.910800Z","shell.execute_reply.started":"2021-06-27T11:00:41.254069Z","shell.execute_reply":"2021-06-27T11:00:46.909825Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"t1_median = player_engagement_with_info[\"target1\"].median()\nt2_median = player_engagement_with_info[\"target2\"].median()\nt3_median = player_engagement_with_info[\"target3\"].median()\nt4_median = player_engagement_with_info[\"target4\"].median()","metadata":{"execution":{"iopub.status.busy":"2021-06-27T11:00:46.912415Z","iopub.execute_input":"2021-06-27T11:00:46.912853Z","iopub.status.idle":"2021-06-27T11:00:47.105464Z","shell.execute_reply.started":"2021-06-27T11:00:46.912808Z","shell.execute_reply":"2021-06-27T11:00:47.104413Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(t1_median,t2_median,t3_median,t4_median)","metadata":{"execution":{"iopub.status.busy":"2021-06-27T11:00:47.107409Z","iopub.execute_input":"2021-06-27T11:00:47.107807Z","iopub.status.idle":"2021-06-27T11:00:48.375382Z","shell.execute_reply.started":"2021-06-27T11:00:47.107771Z","shell.execute_reply":"2021-06-27T11:00:48.374336Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\"\"\"\nif 'kaggle_secrets' in sys.modules:  # only run while on Kaggle\n    import mlb\n\n    env = mlb.make_env()\n    iter_test = env.iter_test()\n\n    for (test_df, sample_prediction_df) in iter_test:\n    \n        # Example: unpack a dataframe from a json column\n        today_games = unpack_json(test_df['games'].iloc[0])\n    \n        # Make your predictions for the next day's engagement\n        sample_prediction_df['target1'] = 100.00\n    \n        # Submit your predictions \n        env.predict(sample_prediction_df)\n\n\n\"\"\"","metadata":{"execution":{"iopub.status.busy":"2021-06-27T11:00:48.376620Z","iopub.execute_input":"2021-06-27T11:00:48.377220Z","iopub.status.idle":"2021-06-27T11:00:49.479248Z","shell.execute_reply.started":"2021-06-27T11:00:48.377168Z","shell.execute_reply":"2021-06-27T11:00:49.478336Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"if 'kaggle_secrets' in sys.modules:  # only run while on Kaggle\n    import mlb","metadata":{"execution":{"iopub.status.busy":"2021-06-27T11:00:49.480490Z","iopub.execute_input":"2021-06-27T11:00:49.480779Z","iopub.status.idle":"2021-06-27T11:00:49.992555Z","shell.execute_reply.started":"2021-06-27T11:00:49.480752Z","shell.execute_reply":"2021-06-27T11:00:49.991367Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"env = mlb.make_env()\niter_test = env.iter_test()","metadata":{"execution":{"iopub.status.busy":"2021-06-27T11:00:49.996675Z","iopub.execute_input":"2021-06-27T11:00:49.996967Z","iopub.status.idle":"2021-06-27T11:00:50.074665Z","shell.execute_reply.started":"2021-06-27T11:00:49.996939Z","shell.execute_reply":"2021-06-27T11:00:50.073203Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for (test_df, sample_prediction_df) in iter_test:\n    display(test_df)\n    display(sample_prediction_df)\n    break","metadata":{"execution":{"iopub.status.busy":"2021-06-27T11:00:50.076449Z","iopub.execute_input":"2021-06-27T11:00:50.077270Z","iopub.status.idle":"2021-06-27T11:00:50.793848Z","shell.execute_reply.started":"2021-06-27T11:00:50.077221Z","shell.execute_reply":"2021-06-27T11:00:50.792866Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample_prediction_df[\"target1\"] = t1_median\nsample_prediction_df[\"target2\"] = t2_median\nsample_prediction_df[\"target3\"] = t3_median\nsample_prediction_df[\"target4\"] = t4_median\nsample_prediction_df","metadata":{"execution":{"iopub.status.busy":"2021-06-27T11:00:50.794845Z","iopub.execute_input":"2021-06-27T11:00:50.795250Z","iopub.status.idle":"2021-06-27T11:00:50.813506Z","shell.execute_reply.started":"2021-06-27T11:00:50.795214Z","shell.execute_reply":"2021-06-27T11:00:50.812615Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"env.predict(sample_prediction_df)","metadata":{"execution":{"iopub.status.busy":"2021-06-27T11:00:50.814931Z","iopub.execute_input":"2021-06-27T11:00:50.815242Z","iopub.status.idle":"2021-06-27T11:00:50.828001Z","shell.execute_reply.started":"2021-06-27T11:00:50.815213Z","shell.execute_reply":"2021-06-27T11:00:50.827127Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for (test_df, sample_prediction_df) in iter_test:\n    display(test_df)\n    display(sample_prediction_df)\n    break","metadata":{"execution":{"iopub.status.busy":"2021-06-27T11:00:50.829195Z","iopub.execute_input":"2021-06-27T11:00:50.830120Z","iopub.status.idle":"2021-06-27T11:00:50.985170Z","shell.execute_reply.started":"2021-06-27T11:00:50.830029Z","shell.execute_reply":"2021-06-27T11:00:50.984450Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\"\"\"\nif 'kaggle_secrets' in sys.modules:  # only run while on Kaggle\n    import mlb\n\n    env = mlb.make_env()\n    iter_test = env.iter_test()\n\n    for (test_df, sample_prediction_df) in iter_test:\n    \n        # Example: unpack a dataframe from a json column\n        today_games = unpack_json(test_df['games'].iloc[0])\n    \n        # Make your predictions for the next day's engagement\n        sample_prediction_df['target1'] = 100.00\n    \n        # Submit your predictions \n        env.predict(sample_prediction_df)\n\n\n\"\"\"","metadata":{"execution":{"iopub.status.busy":"2021-06-27T11:00:50.986073Z","iopub.execute_input":"2021-06-27T11:00:50.986454Z","iopub.status.idle":"2021-06-27T11:00:50.991966Z","shell.execute_reply.started":"2021-06-27T11:00:50.986424Z","shell.execute_reply":"2021-06-27T11:00:50.990675Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 2回目の提出\n\nsample_prediction_df[\"target1\"] = t1_median\nsample_prediction_df[\"target2\"] = t2_median\nsample_prediction_df[\"target3\"] = t3_median\nsample_prediction_df[\"target4\"] = t4_median\nenv.predict(sample_prediction_df)","metadata":{"execution":{"iopub.status.busy":"2021-06-27T11:00:50.993232Z","iopub.execute_input":"2021-06-27T11:00:50.993512Z","iopub.status.idle":"2021-06-27T11:00:51.014367Z","shell.execute_reply.started":"2021-06-27T11:00:50.993485Z","shell.execute_reply":"2021-06-27T11:00:51.013062Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 残り最後まで\n\nfor (test_df, sample_prediction_df) in iter_test:\n    \n        # Example: unpack a dataframe from a json column\n        #today_games = unpack_json(test_df['games'].iloc[0])\n    \n        # Make your predictions for the next day's engagement\n        sample_prediction_df[\"target1\"] = t1_median\n        sample_prediction_df[\"target2\"] = t2_median\n        sample_prediction_df[\"target3\"] = t3_median\n        sample_prediction_df[\"target4\"] = t4_median\n    \n        # Submit your predictions \n        env.predict(sample_prediction_df)","metadata":{"execution":{"iopub.status.busy":"2021-06-27T11:00:51.015952Z","iopub.execute_input":"2021-06-27T11:00:51.016453Z","iopub.status.idle":"2021-06-27T11:00:51.327891Z","shell.execute_reply.started":"2021-06-27T11:00:51.016404Z","shell.execute_reply":"2021-06-27T11:00:51.326759Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}