{"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":"## Hey Everyone !! ** please upvote this notebook if it helped **","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19"}},{"cell_type":"markdown","source":"### This notebook acts a data descriptor for all data files in this [competition](https://www.kaggle.com/c/mlb-player-digital-engagement-forecasting/overview) ","metadata":{}},{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:04:18.144861Z","iopub.execute_input":"2021-06-13T15:04:18.145251Z","iopub.status.idle":"2021-06-13T15:04:18.166313Z","shell.execute_reply.started":"2021-06-13T15:04:18.145166Z","shell.execute_reply":"2021-06-13T15:04:18.164994Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample_submission = pd.read_csv(\"../input/mlb-player-digital-engagement-forecasting/example_sample_submission.csv\")\nplayers = pd.read_csv(\"../input/mlb-player-digital-engagement-forecasting/players.csv\")\nseasons = pd.read_csv(\"../input/mlb-player-digital-engagement-forecasting/seasons.csv\")\nawards = pd.read_csv(\"../input/mlb-player-digital-engagement-forecasting/awards.csv\")\nteams = pd.read_csv(\"../input/mlb-player-digital-engagement-forecasting/teams.csv\")\ntrain = pd.read_csv(\"../input/mlb-player-digital-engagement-forecasting/train.csv\")\nexample_test = pd.read_csv(\"../input/mlb-player-digital-engagement-forecasting/example_test.csv\")","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:04:18.168684Z","iopub.execute_input":"2021-06-13T15:04:18.169226Z","iopub.status.idle":"2021-06-13T15:05:03.228423Z","shell.execute_reply.started":"2021-06-13T15:04:18.169087Z","shell.execute_reply":"2021-06-13T15:05:03.227154Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 1. Sample submission !!","metadata":{"execution":{"iopub.status.busy":"2021-06-12T09:21:32.619357Z","iopub.execute_input":"2021-06-12T09:21:32.619718Z","iopub.status.idle":"2021-06-12T09:21:32.624037Z","shell.execute_reply.started":"2021-06-12T09:21:32.619688Z","shell.execute_reply":"2021-06-12T09:21:32.623127Z"}}},{"cell_type":"code","source":"# For each playerid, a competition that predicts the numerical value (target) of how much the fan will engage \n# in digital content (such as \"reaction\" and \"action\") the next day (future).\n\n# The targets are 1 to 4, and there are 4 different indicators, each of which is quantified on a scale of 0-100.\n\nsample_submission.head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.230206Z","iopub.execute_input":"2021-06-13T15:05:03.230563Z","iopub.status.idle":"2021-06-13T15:05:03.254878Z","shell.execute_reply.started":"2021-06-13T15:05:03.230512Z","shell.execute_reply":"2021-06-13T15:05:03.253685Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 2. Example test !!","metadata":{}},{"cell_type":"code","source":"# At first glance, it doesn't seem like you can immediately see the player ID that appears in the submission. \n# It seems that various information is included in json format.\n\n# The test data consists of one line of data per day.\n\n# For example, using a function from the starter code, it can be expanded as follows.\n\nexample_test.head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.256576Z","iopub.execute_input":"2021-06-13T15:05:03.256960Z","iopub.status.idle":"2021-06-13T15:05:03.375373Z","shell.execute_reply.started":"2021-06-13T15:05:03.256924Z","shell.execute_reply":"2021-06-13T15:05:03.374554Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## A small helper function !! to extract data from the json fomrat !!\n\ndef unpack_json(json_str):\n    return np.nan if pd.isna(json_str) else pd.read_json(json_str)\n\nunpack_json(example_test[\"playerBoxScores\"].iloc[0]).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.376620Z","iopub.execute_input":"2021-06-13T15:05:03.377139Z","iopub.status.idle":"2021-06-13T15:05:03.468237Z","shell.execute_reply.started":"2021-06-13T15:05:03.377104Z","shell.execute_reply":"2021-06-13T15:05:03.466927Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# From the information in this area, I think it is a competition to estimate the expected value \n# of the evaluation items target1 to 4 on the next day for each player id.\n\nunpack_json(example_test[\"games\"].iloc[0]).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.469830Z","iopub.execute_input":"2021-06-13T15:05:03.470192Z","iopub.status.idle":"2021-06-13T15:05:03.516464Z","shell.execute_reply.started":"2021-06-13T15:05:03.470157Z","shell.execute_reply":"2021-06-13T15:05:03.515367Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 3. Training !!","metadata":{}},{"cell_type":"code","source":"# Data for 1216 days. No null data. There are nan data here and there.\n\ntrain['date'] = pd.to_datetime(train['date'], format=\"%Y%m%d\")\ntrain.info()","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.518725Z","iopub.execute_input":"2021-06-13T15:05:03.519177Z","iopub.status.idle":"2021-06-13T15:05:03.539848Z","shell.execute_reply.started":"2021-06-13T15:05:03.519126Z","shell.execute_reply":"2021-06-13T15:05:03.538745Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Data for 1216 days. No null data. There are nan data here and there.","metadata":{}},{"cell_type":"code","source":"# From here, let's take a look at one json where there is data for each column as an example.\n\n# As mentioned above, the contents of train.csv are also in json file format in one cell, and \n# it has a complicated shape like dataframe is further contained.\n\n# (From the result, it is an image that about 11 DataFrames (although it is a little small even if there is nan) \n#  are hanging as the amount of information for one day of data for 1216 days in total, which is a considerable amount of information. is.\n\n# So it's a little long from here. You may just grab the image and read it through.)\n\ntrain.columns","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.543691Z","iopub.execute_input":"2021-06-13T15:05:03.544339Z","iopub.status.idle":"2021-06-13T15:05:03.560644Z","shell.execute_reply.started":"2021-06-13T15:05:03.544286Z","shell.execute_reply":"2021-06-13T15:05:03.559463Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# It is troublesome to input one by one, so I will omit na and make the nth (0 is the top) sample as \n# a dataframe and create a function to see the column name and contents.\n\ndef exshow(col,n):\n    tmp = train[col]\n    tmp = tmp.dropna()\n    tmpdf = unpack_json(tmp.iloc[n])\n    print(tmpdf.columns)\n    return tmpdf","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.562905Z","iopub.execute_input":"2021-06-13T15:05:03.563554Z","iopub.status.idle":"2021-06-13T15:05:03.582323Z","shell.execute_reply.started":"2021-06-13T15:05:03.563487Z","shell.execute_reply":"2021-06-13T15:05:03.581477Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Nested JSON containing all modeling targets from the next day onwards.\n\ntrain.head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.583720Z","iopub.execute_input":"2021-06-13T15:05:03.584316Z","iopub.status.idle":"2021-06-13T15:05:03.618111Z","shell.execute_reply.started":"2021-06-13T15:05:03.584269Z","shell.execute_reply":"2021-06-13T15:05:03.617026Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# engagementMetricsDate-Date of player engagement metrics based on US Pacific Time \n# (matches previous day's games, addresses, field statistics, transactions, awards, and more).\n#     playerId\n#     target1\n#     target2\n#     target3\n#     target4\n\n# target1-target4 is a daily index of digital engagement on a scale of 0 to 100.\n\n# From here, plyaerId and targets1 to 4 from the next day onwards are pulled out.\n\nexshow(\"nextDayPlayerEngagement\",0).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.620019Z","iopub.execute_input":"2021-06-13T15:05:03.620593Z","iopub.status.idle":"2021-06-13T15:05:03.666117Z","shell.execute_reply.started":"2021-06-13T15:05:03.620542Z","shell.execute_reply":"2021-06-13T15:05:03.664474Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Nested JSON that contains all the game information for a particular day. \n# Includes spring training and exhibition games in addition to regular season, postseason and all-star games.\n\nexshow(\"games\",1).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.668152Z","iopub.execute_input":"2021-06-13T15:05:03.668670Z","iopub.status.idle":"2021-06-13T15:05:03.717298Z","shell.execute_reply.started":"2021-06-13T15:05:03.668611Z","shell.execute_reply":"2021-06-13T15:05:03.716126Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Nested JSON that contains all the roster information for a particular day. \n# Includes in-season and off-season team rosters.\n\n# playerId-Unique identifier for the player.\n#     gameDate\n#     teamId-The teamId that player is on that day.\n#     statusCode-Abbreviation for list status.\n#     status-The status of the descriptive roster.\n\n# 1.4 playerBoxScores (4th column in train.csv)\n\nexshow(\"rosters\",0).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.719109Z","iopub.execute_input":"2021-06-13T15:05:03.719595Z","iopub.status.idle":"2021-06-13T15:05:03.745760Z","shell.execute_reply.started":"2021-06-13T15:05:03.719542Z","shell.execute_reply":"2021-06-13T15:05:03.744755Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Nested JSON containing game stats aggregated at the player game level for a particular day. \n# Includes regular season, postseason and all-star games.\n\nexshow(\"playerBoxScores\",0).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.747641Z","iopub.execute_input":"2021-06-13T15:05:03.748130Z","iopub.status.idle":"2021-06-13T15:05:03.844175Z","shell.execute_reply.started":"2021-06-13T15:05:03.748077Z","shell.execute_reply":"2021-06-13T15:05:03.842886Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Please open ↓ to translate the meaning of the column. (Because it is long, I hide it.)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.846082Z","iopub.execute_input":"2021-06-13T15:05:03.846560Z","iopub.status.idle":"2021-06-13T15:05:03.851795Z","shell.execute_reply.started":"2021-06-13T15:05:03.846488Z","shell.execute_reply":"2021-06-13T15:05:03.850251Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# home: Binary, 1 for home team, 0 for distant.\n# gamePk: Unique identifier for the game.\n# gameDate:\n# gameTimeUTC: Ceremonial first pitch in UTC.\n# teamId: A unique identifier for the team.\n# teamName:\n# playerId: Unique identifier for the player.\n# playerName:\n# jerseyNum:\n# positionCode: The position code of the number, details are here.\n# positionName: Text position display, details are here.\n# positionType: Position group, details are here.\n# battingOrder: Format: \"###\". The first digit indicates the batting order spot, and the next two digits indicate the order in which the player occupies the batting order spot. Example: \"300\" indicates the starter of the third spot in batting order. \"903\" indicates that the fourth person (900, 901, 902 or later) occupies the ninth place in the batting order. Entered only when it appears in the game.\n# gamesPlayedBatting: 1. If the player participates in the game as a batter, runner, or fielder.\n# flyOuts: Total flyouts for the game.\n# groundOuts: Total ground outs for the game.\n# runsScored: The total run of the game has been recorded.\n# doubles: The total number of games is doubled.\n# triples: The total triples of the game.\n# homeRuns: Game home runs.\n# strikeOuts: Total strikeouts in the game.\n# baseOnBalls: Total walk of the game.\n# intentionalWalks: Intentional walks in the game.\n# hits: Total number of hits in the game.\n# hitByPitch: The total hit of the game by pitch.\n# Game total at atBats:\n# caughtStealing: The total of the game caught the stolen base.\n# stolenBases: Total number of stolen bases in the game.\n# groundIntoDoublePlay: The total double play of the game is based on.\n# groundIntoTriplePlay: Based on a total of 3 games played.\n# plateAppearances: Total at-bats in the game.\n# totalBases: The total number of bases in the game.\n# rbi: Total RBI of the game.\n# leftOnBase: The total runners in the game remain on the base.\n# sacBunts: Total sacrifice bunts for the game.\n# sacFlies: Total sacrifice fly for the game.\n# catchersInterference: The game's total catcher interference has occurred.\n# pickoffs: The total number of games is off the base.\n# gamesPlayedPitching: Binary, 1 if the player participates in the game as a pitcher.\n# gamesStartedPitching: Binary, 1 if the player was the starting pitcher of the game.\n# completeGamesPitching: Binary, 1 if credited for complete game.\n# shutoutsPitching: Binary, 1 if shutout and credited.\n# winsPitching: Binary, 1 if credited for winning.\n# lossesPitching: Binary, 1 if losses are credited.\n# flyOutsPitching: Total allowed flyout games.\n# airOutsPitching: Airout (flyout + popout) game totals are allowed.\n# groundOutsPitching: Allows total ground outs for the game.\n# runsPitching: Allows total running of the game.\n# doublesPitching: The total game is doubled.\n# triplesPitching: A total of triples in the game are allowed.\n# homeRunsPitching: Total home runs for the game are allowed.\n# strikeOutsPitching: A total of strikeouts in the game is allowed.\n# baseOnBallsPitching: The total walking of the game is allowed.\n# intentionalWalksPitching: Allows a total of intentional walks in the game.\n# hitsPitching: The total number of hits in the game allowed.\n# hitByPitchPitching: The total number of hits in the game with the allowed pitch.\n# Total games at atBatsPitching:\n# caughtStealingPitching: The game total caught theft.\n# stolenBasesPitching: Total stolen bases in the game are allowed.\n# inningsPitched: Total pitched times of the game.\n# saveOpportunities: Binary, 1 if you have the opportunity to save.\n# earnedRuns: The total earned run of the game is allowed.\n# battersFaced: The total batter of the game faced.\n# outsPitching: The total outs of the game have been recorded.\n# pitchesThrown: Total number of pitched games.\n# balls: The total balls of the thrown game.\n# strikes: The total strikes of the thrown game.\n# hitBatsmen: The total hit by pitch batter in the game.\n# balks: The total of the game will balk.\n# wildPitches: Total number of wild pitch games thrown.\n# pickoffsPitching: The total number of pickoffs in the game.\n# rbiPitching: The total number of RBI games is allowed.\n# inheritedRunners: Assuming the total number of inherited runner games.\n# inheritedRunnersScored: The total number of inherited runners scored.\n# catchersInterferencePitching: The game total of catcher interference was caused by the battery.\n# sacBuntsPitching: The total sacrifice bunts in the game are allowed.\n# sacFliesPitching: Game sacrifice flies are allowed.\n# saves: Binary, 1 if credited with saves.\n# holds: Binary, 1 if the hold is credited.\n# blownSaves: Binary, 1 if credited with blowsave.\n# assists: Total number of assists in the game.\n# putOuts: Total number of game stabs.\n# errors: Total number of game errors.\n# chances: Total fielding chances for the game.","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2021-06-13T15:05:03.853365Z","iopub.execute_input":"2021-06-13T15:05:03.853744Z","iopub.status.idle":"2021-06-13T15:05:03.866920Z","shell.execute_reply.started":"2021-06-13T15:05:03.853695Z","shell.execute_reply":"2021-06-13T15:05:03.865609Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Nested JSON containing game stats aggregated at the team game level for a particular day.\n# Includes regular season, postseason and all-star games.\n\nexshow(\"teamBoxScores\",0).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.870239Z","iopub.execute_input":"2021-06-13T15:05:03.870640Z","iopub.status.idle":"2021-06-13T15:05:03.930894Z","shell.execute_reply.started":"2021-06-13T15:05:03.870603Z","shell.execute_reply":"2021-06-13T15:05:03.929818Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Please open ↓ to translate the meaning of the column. (Because it is long, I hide it.)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.932838Z","iopub.execute_input":"2021-06-13T15:05:03.933320Z","iopub.status.idle":"2021-06-13T15:05:03.940177Z","shell.execute_reply.started":"2021-06-13T15:05:03.933270Z","shell.execute_reply":"2021-06-13T15:05:03.938885Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Column Meaning home: Binary, 1 for home team, 0 for distant.\n# teamId: A unique identifier for the team.\n# gamePk: Unique identifier for the game.\n# gameDate:\n# gameTimeUTC: Ceremonial first pitch in UTC.\n# flyOuts: Total flyouts for the game.\n# groundOuts: Total ground outs for the game.\n# runsScored: The total run of the game has been recorded.\n# doubles: The total number of games is doubled.\n# triples: The total triples of the game.\n# homeRuns: Game home runs.\n# strikeOuts: Total strikeouts in the game.\n# baseOnBalls: Total walk of the game.\n# intentionalWalks: Intentional walks in the game.\n# hits: Total number of hits in the game.\n# hitByPitch: The total hit of the game by pitch.\n# Game total at atBats:\n# caughtStealing: The total of the game caught the stolen base. Please open ↓ for translation. (Because it is long, I hide it.)\n# stolenBases: Total number of stolen bases in the game.\n# groundIntoDoublePlay: The total double play of the game is based on.\n# groundIntoTriplePlay: Based on a total of 3 games played.\n# plateAppearances: Total at-bats in the game.\n# totalBases: The total number of bases in the game.\n# rbi: Total RBI of the game.\n# leftOnBase: The total runners in the game remain on the base.\n# sacBunts: Total sacrifice bunts for the game.\n# sacFlies: Total sacrifice fly for the game.\n# catchersInterference: The game's total catcher interference has occurred.\n# pickoffs: The total number of games is off the base.\n# airOutsPitching: Airout (flyout + popout) game totals are allowed.\n# groundOutsPitching: Allows total ground outs for the game.\n# runsPitching: Allows total running of the game.\n# doublesPitching: The total game is doubled.\n# triplesPitching: A total of triples in the game are allowed.\n# homeRunsPitching: Total home runs for the game are allowed.\n# strikeOutsPitching: A total of strikeouts in the game is allowed.\n# baseOnBallsPitching: The total walking of the game is allowed.\n# intentionalWalksPitching: Allows a total of intentional walks in the game.\n# hitsPitching: The total number of hits in the game allowed.\n# hitByPitchPitching: The total number of hits in the game with the allowed pitch.\n# Total games at atBatsPitching:\n# caughtStealingPitching: The game total caught theft.\n# stolenBasesPitching: Total stolen bases in the game are allowed.\n# inningsPitched: Total pitched times of the game.\n# earnedRuns: The total earned run of the game is allowed.\n# battersFaced: The total batter of the game faced.\n# outsPitching: The total outs of the game have been recorded.\n# hitBatsmen: The total hit by pitch batter in the game.\n# balks: The total of the game will balk.\n# wildPitches: Total number of wild pitch games thrown.\n# pickoffsPitching: The total number of pickoffs in the game.\n# rbiPitching: The total number of RBI games is allowed.\n# inheritedRunners: Assuming the total number of inherited runner games.\n# inheritedRunnersScored: The total number of inherited runners scored.\n# catchersInterferencePitching: The game total of catcher interference was caused by the battery.\n# sacBuntsPitching: The total sacrifice bunts in the game are allowed.\n# sacFliesPitching: Game sacrifice flies are allowed.","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2021-06-13T15:05:03.941607Z","iopub.execute_input":"2021-06-13T15:05:03.941924Z","iopub.status.idle":"2021-06-13T15:05:03.950726Z","shell.execute_reply.started":"2021-06-13T15:05:03.941894Z","shell.execute_reply":"2021-06-13T15:05:03.949701Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Nested JSON that contains all transaction information related to the MLB team for a particular day.\n\n# transactionId: A unique identifier for the transaction.\n# playerId: Unique identifier for the player.\n# playerName:\n# date: date:\n# fromTeamId: A unique identifier for the player's team of origin.\n# fromTeamName: fromTeamName:\n# toTeamId: A unique identifier for the team the player goes to.\n# toTeamName:\n# effectiveDate:\n# resolutionDate:\n# typeCode: Abbreviation for transaction status.\n# typeDesc: A description of the transaction status.\n# description: A textual description of the transaction.\n\nexshow(\"transactions\",1) .head(2)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:03.951991Z","iopub.execute_input":"2021-06-13T15:05:03.952574Z","iopub.status.idle":"2021-06-13T15:05:04.000384Z","shell.execute_reply.started":"2021-06-13T15:05:03.952503Z","shell.execute_reply":"2021-06-13T15:05:03.998859Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# A nested JSON that contains all the ranking information about the MLB team for a particular day.\n\nexshow(\"standings\",0).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:04.003005Z","iopub.execute_input":"2021-06-13T15:05:04.003651Z","iopub.status.idle":"2021-06-13T15:05:04.057161Z","shell.execute_reply.started":"2021-06-13T15:05:04.003502Z","shell.execute_reply":"2021-06-13T15:05:04.056196Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Please open ↓ to translate the meaning of the column. (Because it is long, I hide it.)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:04.059741Z","iopub.execute_input":"2021-06-13T15:05:04.060252Z","iopub.status.idle":"2021-06-13T15:05:04.064929Z","shell.execute_reply.started":"2021-06-13T15:05:04.060197Z","shell.execute_reply":"2021-06-13T15:05:04.063591Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# season: season:\n# gameDate:\n# divisionId: A unique identifier that represents the department to which this team belongs.\n# teamId: A unique identifier for the team.\n# teamName:\n# streakCode: Abbreviation for the team's current wins and losses. The first letter indicates win or loss, and the number is the number of games.\n# divisionRank: The current rank of the team's division.\n# leagueRank: The current rank of the team in the league.\n# wildCardRank: The current rank of the wildcard berth team.\n# leagueGamesBack: The game returns to the team's league.\n# sportGamesBack: Games back to all of MLB.\n# divisionGamesBack: The game is back in the team division.\n# wins: Current victory.\n# losses: Current losses.\n# pct: Current win rate.\n# runsAllowed: Allowed runs during the season.\n# runsScored: Runs scored in the season.\n# divisionChamp: true if the team wins the division title.\n# divisionLeader: true if the team is leading the division race.\n#     wildCardLeader: true if the team is a wildcard reader.\n# eliminationNumber: The number of games to be eliminated from the division race (team defeat + opponent win).\n# wildCardEliminationNumber: The number of games before being eliminated from a wildcard race (team defeat + opponent win).\n# homeWins: Home wins the season.\n# homeLosses: Home loss during the season.\n# awayWins: Away wins the season.\n# awayLosses: Seasonal away loss.\n# lastTenWins: You have won the last 10 games.\n# lastTenLosses: I lost in the last 10 games.\n# extraInningWins: Win with additional innings of the season.\n# extraInningLosses: Losses in additional innings of the season.\n# oneRunWins: Win one run during the season.\n# oneRunLosses: You lose one run during the season.\n# dayWins: Day games win the season.\n# dayLosses Day game losses on the season.:\n# nightWins: Night games win the season.\n# nightLosses: Defeat of night games during the season.\n# grassWins: The grass field wins the season.\n# grassLosses: Seasonal grassland loss.\n# turfWins: The turf field wins the season.\n# turfLosses: Loss of turf field during the season.\n# divWins: Win division opponents during the season.\n# divLosses: Defeat of a division's opponent during the season.\n# alWins: Win the AL team during the season.\n# alLosses: Defeat against AL team during the season.\n# nlWins: Win the NL team during the season.\n# nlLosses: Defeat to the NL team during the season.\n# xWinLossPct: Expected win rate based on scoring and allowed runs.","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2021-06-13T15:05:04.066423Z","iopub.execute_input":"2021-06-13T15:05:04.066855Z","iopub.status.idle":"2021-06-13T15:05:04.079373Z","shell.execute_reply.started":"2021-06-13T15:05:04.066811Z","shell.execute_reply":"2021-06-13T15:05:04.078237Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Nested JSON containing all awards or honors distributed on a particular day.\n\n# awardId:\n# awardName:\n# awardDate: A date award was given.\n# awardSeason: The season award was empty.\n# playerId: Unique identifier for the player.\n# playerName:\n# awardPlayerTeamId:\n\nexshow(\"awards\",0).head(3)","metadata":{"jupyter":{"source_hidden":true},"execution":{"iopub.status.busy":"2021-06-13T15:05:04.080941Z","iopub.execute_input":"2021-06-13T15:05:04.081275Z","iopub.status.idle":"2021-06-13T15:05:04.114606Z","shell.execute_reply.started":"2021-06-13T15:05:04.081244Z","shell.execute_reply":"2021-06-13T15:05:04.113437Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Nested JSON that contains all the on-field game events for a particular day. \n# Includes regular and postseason matches.\n\nexshow(\"events\",0).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:04.116211Z","iopub.execute_input":"2021-06-13T15:05:04.116615Z","iopub.status.idle":"2021-06-13T15:05:04.419441Z","shell.execute_reply.started":"2021-06-13T15:05:04.116565Z","shell.execute_reply":"2021-06-13T15:05:04.418224Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# A nested JSON that contains the number of Twitter followers for some players of the day.\n\n# Twitter follow-up data is collected by MLB from Major League Baseball players' Twitter API on the first day of every month and dates back to January 1, 2018. This data because not all players have / have a Twitter account, there are other scenarios where players randomly create / delete / restore accounts, or cannot collect follower data on a particular day. The set does not cover all players over every month.\n\n#     date: The date of the number of followers.\n#     playerId: Unique identifier for the player.\n#     playerName:\n#     accountName: The name of the player's Twitter account.\n#     twitterHandle: The player's Twitter handle.\n#     numberOfFollowers: Number of followers\n\nexshow(\"playerTwitterFollowers\",0).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:04.423534Z","iopub.execute_input":"2021-06-13T15:05:04.423909Z","iopub.status.idle":"2021-06-13T15:05:04.459279Z","shell.execute_reply.started":"2021-06-13T15:05:04.423876Z","shell.execute_reply":"2021-06-13T15:05:04.457966Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# A nested JSON that contains the number of teamTwitterFollowers followers for some players of the day.\n\n# Twitter follow-up data was collected by MLB from the Twitter APIs of all 30 Major League Baseball teams on the 1st of every month, dating back to January 1, 2018.\n\n#     date: The date of the number of followers.\n#     teamId: A unique identifier for the team.\n#     teamName:\n#     accountName: The name of your team's Twitter account.\n#     twitterHandle: Team Twitter handle.\n\nexshow(\"teamTwitterFollowers\",0).head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:04.461300Z","iopub.execute_input":"2021-06-13T15:05:04.461649Z","iopub.status.idle":"2021-06-13T15:05:04.487246Z","shell.execute_reply.started":"2021-06-13T15:05:04.461615Z","shell.execute_reply":"2021-06-13T15:05:04.486153Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 4. additional data ( awards.csv, players.csv, seasons.csv, teams.csv)","metadata":{"execution":{"iopub.status.busy":"2021-06-12T09:14:36.787864Z","iopub.execute_input":"2021-06-12T09:14:36.788263Z","iopub.status.idle":"2021-06-12T09:14:36.792689Z","shell.execute_reply.started":"2021-06-12T09:14:36.788232Z","shell.execute_reply":"2021-06-12T09:14:36.791558Z"}}},{"cell_type":"code","source":"# seasonId: Season ID\n# seasonStartDate: Season start date\n# seasonEndDate: Season end date\n# preSeasonStartDate: Previous season start date\n# preSeasonEndDate: The last day of the previous season\n# regularSeasonStartDate: The start date of the regular season\n# regularSeasonEndDate: The last day of the regular season\n# lastDate1stHalf: Last day of 1st half\n# allStarDate: Date of the All-Star match\n# firstDate2ndHalf: Start date of 2nd half\n# postSeasonStartDate: Start date for next season\n# postSeasonEndDate: The end of the next season\n    \nseasons.head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:04.488947Z","iopub.execute_input":"2021-06-13T15:05:04.489334Z","iopub.status.idle":"2021-06-13T15:05:04.523156Z","shell.execute_reply.started":"2021-06-13T15:05:04.489299Z","shell.execute_reply":"2021-06-13T15:05:04.522107Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# id --team ID\n# name: name\n# teamName: The name of the team\n# teamCode: Team code\n# shortName: Short name\n# abbreviation: Abbreviation\n# locationName: The name of the location\n# leagueId: league id\n# leagueName: The name of the league\n# divisionId: divisionid\n# divisionName: divisionname\n# venueId: Venue id\n# venueName: Venue name\n\nteams.head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:04.524846Z","iopub.execute_input":"2021-06-13T15:05:04.525290Z","iopub.status.idle":"2021-06-13T15:05:04.546586Z","shell.execute_reply.started":"2021-06-13T15:05:04.525240Z","shell.execute_reply":"2021-06-13T15:05:04.545242Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# playerId --Unique identifier for a player.: PlayerID-Unique identifier for a player.\n# playerName: The name of the player\n# DOB-Player ’s date of birth.: DOB-Player ’s date of birth.\n# mlbDebutDate: MLB debut date\n# birthCity: the town where you were born\n# birthStateProvince: birth state\n# birthCountry: Country of birth\n# heightInches: Height (inch)\n# weight: weight\n# primaryPositionCode --Player ’s primary position code: Primary position code\n# primaryPositionName --player ’s primary position: Primary position name\n# playerForTestSetAndFuturePreds --Boolean, true if player is among those for whom predictions are to be made in test data\n\nplayers.head(3)\n","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:04.548472Z","iopub.execute_input":"2021-06-13T15:05:04.548955Z","iopub.status.idle":"2021-06-13T15:05:04.570190Z","shell.execute_reply.started":"2021-06-13T15:05:04.548906Z","shell.execute_reply":"2021-06-13T15:05:04.568827Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# This file contains the awards won by the players in the training set before the start of the daily data (that is, before 2018).\n\n# awardDate --Date award was given.: Awarded date --Awarded date.\n# awardSeason --Season award was from .: Award Season-The season award was empty.\n# awardId: award id\n# awardName: award name\n# playerId --Unique identifier for a player.: PlayerID-Unique identifier for a player.\n# playerName: The name of the player\n# awardPlayerTeamId: Player's team ID\n\nawards.head(3)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:04.572493Z","iopub.execute_input":"2021-06-13T15:05:04.573022Z","iopub.status.idle":"2021-06-13T15:05:04.599463Z","shell.execute_reply.started":"2021-06-13T15:05:04.572964Z","shell.execute_reply":"2021-06-13T15:05:04.597978Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Data Merge\n\ndf_names = ['seasons', 'teams', 'players', 'awards']\nimport gc\npath = \"../input/mlb-player-digital-engagement-forecasting\"\n","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:04.601558Z","iopub.execute_input":"2021-06-13T15:05:04.601879Z","iopub.status.idle":"2021-06-13T15:05:04.612289Z","shell.execute_reply.started":"2021-06-13T15:05:04.601848Z","shell.execute_reply":"2021-06-13T15:05:04.610739Z"},"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\")\n\n# #### Unnest various nested data within train (daily) data ####\n# daily_data_unnested_dfs = pd.DataFrame(data = {\n#   'dfName': train.drop('date', axis = 1).columns.values.tolist()\n#   })\n\n# daily_data_unnested_dfs['df'] = [pd.DataFrame() for row in \n#   daily_data_unnested_dfs.iterrows()]\n\n# for df_index, df_row in daily_data_unnested_dfs.iterrows():\n#     nestedTableName = str(df_row['dfName'])\n    \n#     date_nested_table = train[['date', nestedTableName]]\n    \n#     date_nested_table = (date_nested_table[\n#       ~pd.isna(date_nested_table[nestedTableName])\n#       ].\n#       reset_index(drop = True)\n#       )\n    \n#     daily_dfs_collection = []\n    \n#     for date_index, date_row in date_nested_table.iterrows():\n#         daily_df = unpack_json(date_row[nestedTableName])\n        \n#         daily_df['dailyDataDate'] = date_row['date']\n        \n#         daily_dfs_collection = daily_dfs_collection + [daily_df]\n\n#     unnested_table = pd.concat(daily_dfs_collection,\n#       ignore_index = True).set_index('dailyDataDate').reset_index()\n\n#     # Creates 1 pandas df per unnested df from daily data read in, with same name\n#     globals()[df_row['dfName']] = unnested_table    \n    \n#     daily_data_unnested_dfs['df'][df_index] = unnested_table\n\n# del train\n# gc.collect()\n\n\n\n# #### Get some information on each date in daily data (using season dates of interest) ####\n# dates = pd.DataFrame(data = \n#   {'dailyDataDate': nextDayPlayerEngagement['dailyDataDate'].unique()})\n\n# dates['date'] = pd.to_datetime(dates['dailyDataDate'].astype(str))\n\n# dates['year'] = dates['date'].dt.year\n# dates['month'] = dates['date'].dt.month\n\n# dates_with_info = pd.merge(\n#   dates,\n#   seasons,\n#   left_on = 'year',\n#   right_on = 'seasonId'\n#   )\n\n# dates_with_info['inSeason'] = (\n#   dates_with_info['date'].between(\n#     dates_with_info['regularSeasonStartDate'],\n#     dates_with_info['postSeasonEndDate'],\n#     inclusive = True\n#     )\n#   )\n\n# dates_with_info['seasonPart'] = np.select(\n#   [\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#   ], \n#   [\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#   ], \n#   default = np.nan\n#   )\n\n# #### Add some pitching stats/pieces of info to player game level stats ####\n\n# player_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\n# # Adds in field for innings pitched as fraction (better for aggregation)\n# player_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)\n# player_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\n# # Add in criteria for no-hitter by pitcher (individual, not multiple pitchers)\n# player_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#   )\n\n# player_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\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\n# games_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\n# # Get games table from home team perspective\n# games_home_perspective = games_for_stats.copy()\n\n# # Change column names so that \"team\" is \"home\", \"opp\" is \"away\"\n# games_home_perspective.columns = [\n#   col_value.replace('home', 'team').replace('away', 'opp') for \n#     col_value in games_home_perspective.columns.values]\n\n# games_home_perspective['isHomeTeam'] = 1\n\n# # Get games table from away team perspective\n# games_away_perspective = games_for_stats.copy()\n\n# # Change column names so that \"opp\" is \"home\", \"team\" is \"away\"\n# games_away_perspective.columns = [\n#   col_value.replace('home', 'opp').replace('away', 'team') for \n#     col_value in games_away_perspective.columns.values]\n\n# games_away_perspective['isHomeTeam'] = 0\n\n# # Put together games from home/away perspective to get df w/ 1 row per team game\n# team_games = (pd.concat([\n#   games_home_perspective,\n#   games_away_perspective\n#   ],\n#   ignore_index = True)\n#   )\n\n# # Copy over team box scores data to modify\n# team_game_stats = teamBoxScores.copy()\n\n# # Change column names to reflect these are all \"team\" stats - helps \n# # to differentiate from individual player stats if/when joining later\n# team_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\n# # Merge games table with team game stats\n# team_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 train games, postponed games,\n#   # etc. from original games table, but this may be fine for purposes here \n#   how = 'inner'\n#   )\n\n# team_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\n# # Prepare standings table for merge w/ player digital engagement data\n# # Pick only certain fields of interest from standings for merge\n# standings_selected_fields = (standings[['dailyDataDate', 'teamId', \n#   'streakCode', 'divisionRank', 'leagueRank', 'wildCardRank', 'pct'\n#   ]].\n#   rename(columns = {'pct': 'winPct'})\n#   )\n\n# # Change column names to reflect these are all \"team\" standings - helps \n# # to differentiate from player-related fields if/when joining later\n# standings_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#   ]\n\n# standings_selected_fields['streakLengthTeam'] = (\n#   standings_selected_fields['streakCodeTeam'].\n#     str.replace('W', '').\n#     str.replace('L', '').\n#     astype(float)\n#     )\n\n# # Add fields to separate winning and losing streak from streak code\n# standings_selected_fields['winStreakTeam'] = np.where(\n#   standings_selected_fields['streakCodeTeam'].str[0] == 'W',\n#   standings_selected_fields['streakLengthTeam'],\n#   np.nan\n#   )\n\n# standings_selected_fields['lossStreakTeam'] = np.where(\n#   standings_selected_fields['streakCodeTeam'].str[0] == 'L',\n#   standings_selected_fields['streakLengthTeam'],\n#   np.nan\n#   )\n\n# standings_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\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\n# player_engagement_with_info = nextDayPlayerEngagement.copy()\n\n# # Take \"row mean\" across targets to add (helps with studying all 4 targets at once)\n# player_engagement_with_info['targetAvg'] = np.mean(\n#   player_engagement_with_info[['target1', 'target2', 'target3', 'target4']],\n#   axis = 1)\n\n# # Merge in date information\n# player_engagement_with_info = pd.merge(\n#   player_engagement_with_info,\n#   dates_with_info[['dailyDataDate', 'date', 'year', 'month', 'inSeason',\n#     'seasonPart']],\n#   on = ['dailyDataDate'],\n#   how = 'left'\n#   )\n\n# # Merge in some player information\n# player_engagement_with_info = pd.merge(\n#   player_engagement_with_info,\n#   players[['playerId', 'playerName', 'DOB', 'mlbDebutDate', 'birthCity',\n#     'birthStateProvince', 'birthCountry', 'primaryPositionName']],\n#    on = ['playerId'],\n#    how = 'left'\n#    )\n\n# # Merge in some player roster information by date\n# player_engagement_with_info = pd.merge(\n#   player_engagement_with_info,\n#   (rosters[['dailyDataDate', 'playerId', 'statusCode', 'status', 'teamId']].\n#     rename(columns = {\n#       'statusCode': 'rosterStatusCode',\n#       'status': 'rosterStatus',\n#       'teamId': 'rosterTeamId'\n#       })\n#     ),\n#   on = ['dailyDataDate', 'playerId'],\n#   how = 'left'\n#   )\n    \n# # Merge in team name from player's roster team\n# player_engagement_with_info = pd.merge(\n#   player_engagement_with_info,\n#   (teams[['id', 'teamName']].\n#     rename(columns = {\n#       'id': 'rosterTeamId',\n#       'teamName': 'rosterTeamName'\n#       })\n#     ),\n#   on = ['rosterTeamId'],\n#   how = 'left'\n#   )\n\n# # Merge in some player game stats (previously aggregated) from that date\n# player_engagement_with_info = pd.merge(\n#   player_engagement_with_info,\n#   player_date_stats_agg,\n#   on = ['dailyDataDate', 'playerId'],\n#   how = 'left'\n#   )\n\n# # Merge in team name from player's game team\n# player_engagement_with_info = pd.merge(\n#   player_engagement_with_info,\n#   (teams[['id', 'teamName']].\n#     rename(columns = {\n#       'id': 'gameTeamId',\n#       'teamName': 'gameTeamName'\n#       })\n#     ),\n#   on = ['gameTeamId'],\n#   how = 'left'\n#   )\n\n# # Merge in some team game stats/results (previously aggregated) from that date\n# player_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\n# # Merge in player transactions of note on that date\n    \n# # Merge in some pieces of team standings (previously filter/processed) from that date\n# player_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#   )\n\n# display(player_engagement_with_info)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:04.614237Z","iopub.execute_input":"2021-06-13T15:05:04.614622Z","iopub.status.idle":"2021-06-13T15:05:04.629287Z","shell.execute_reply.started":"2021-06-13T15:05:04.614587Z","shell.execute_reply":"2021-06-13T15:05:04.627838Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Saving the merged data: it has been uploadded !!\n# player_engagement_with_info.to_pickle(\"player_engagement_with_info.pkl\")\n\n## I have added this merged data !! it has been created from the above previous function !!\nplayer_engagement_with_info = pd.read_pickle('../input/mlb-player-digital-engagement-merged-data/player_engagement_with_info.pkl')","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:04.630861Z","iopub.execute_input":"2021-06-13T15:05:04.631221Z","iopub.status.idle":"2021-06-13T15:05:09.285351Z","shell.execute_reply.started":"2021-06-13T15:05:04.631189Z","shell.execute_reply":"2021-06-13T15:05:09.284243Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# player_engagement_with_info = pd.read_pickle('./player_engagement_with_info.pkl')","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:09.286744Z","iopub.execute_input":"2021-06-13T15:05:09.287116Z","iopub.status.idle":"2021-06-13T15:05:09.291738Z","shell.execute_reply.started":"2021-06-13T15:05:09.287080Z","shell.execute_reply":"2021-06-13T15:05:09.290255Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"player_engagement_with_info.columns","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:09.293027Z","iopub.execute_input":"2021-06-13T15:05:09.293385Z","iopub.status.idle":"2021-06-13T15:05:09.311082Z","shell.execute_reply.started":"2021-06-13T15:05:09.293350Z","shell.execute_reply":"2021-06-13T15:05:09.309973Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## getting mean of each player based on there target inform of list !!\n\nmean_player = {}\nfor Id in player_engagement_with_info['playerId'].unique().tolist():\n    mean_player[Id] = {}\n    mean_player[Id]['target1'] = player_engagement_with_info[player_engagement_with_info['playerId']== Id]['target1'].median()\n    mean_player[Id]['target2'] = player_engagement_with_info[player_engagement_with_info['playerId']== Id]['target2'].median()\n    mean_player[Id]['target3'] = player_engagement_with_info[player_engagement_with_info['playerId']== Id]['target3'].median()\n    mean_player[Id]['target4'] = player_engagement_with_info[player_engagement_with_info['playerId']== Id]['target4'].median()\n\nmean_player[656669]","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:05:09.313547Z","iopub.execute_input":"2021-06-13T15:05:09.314109Z","iopub.status.idle":"2021-06-13T15:06:49.809625Z","shell.execute_reply.started":"2021-06-13T15:05:09.313977Z","shell.execute_reply":"2021-06-13T15:06:49.808669Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Submission file creation : \n\n### Do reffere this notebook for any quries regarding submission !! [link](https://www.kaggle.com/chumajin/eda-of-mlb-for-starter-english-ver#-MLB-Player-Digital-Engagement-Competition%F0%9F%98%80)","metadata":{}},{"cell_type":"code","source":"import sys\nif 'kaggle_secrets' in sys.modules:  # only run while on Kaggle\n    import mlb","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:06:49.810923Z","iopub.execute_input":"2021-06-13T15:06:49.811426Z","iopub.status.idle":"2021-06-13T15:06:49.828558Z","shell.execute_reply.started":"2021-06-13T15:06:49.811377Z","shell.execute_reply":"2021-06-13T15:06:49.827717Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Execute this only for one !!\n\nenv = mlb.make_env()\niter_test = env.iter_test()","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:06:49.829973Z","iopub.execute_input":"2021-06-13T15:06:49.830492Z","iopub.status.idle":"2021-06-13T15:06:49.834422Z","shell.execute_reply.started":"2021-06-13T15:06:49.830444Z","shell.execute_reply":"2021-06-13T15:06:49.833665Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Getting first sample !!\n\n# 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-13T15:06:49.835628Z","iopub.execute_input":"2021-06-13T15:06:49.836095Z","iopub.status.idle":"2021-06-13T15:06:49.849336Z","shell.execute_reply.started":"2021-06-13T15:06:49.836047Z","shell.execute_reply":"2021-06-13T15:06:49.848438Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Function to create processed data that will be submitted !!\n\ndef process_pred(data):\n    data = data.reset_index()\n    data['playerId'] = data['date_playerId'].str.rsplit('_').apply(lambda x: int(x[-1]))\n\n    for ply in data['playerId'].unique().tolist():\n        indexes = (data[data['playerId'] == ply].index).tolist()\n        data.loc[indexes,'target1'] = mean_player[ply]['target1']\n        data.loc[indexes,'target2'] = mean_player[ply]['target2']\n        data.loc[indexes,'target3'] = mean_player[ply]['target3']\n        data.loc[indexes,'target4'] = mean_player[ply]['target4']\n    \n    data = data.set_index('date', drop = True)\n    data = data.drop(['playerId'], axis=1)\n    return data","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:06:49.851070Z","iopub.execute_input":"2021-06-13T15:06:49.851514Z","iopub.status.idle":"2021-06-13T15:06:49.864344Z","shell.execute_reply.started":"2021-06-13T15:06:49.851464Z","shell.execute_reply":"2021-06-13T15:06:49.863164Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# process_pred(sample_submission)\n# process_pred(sample_submission.set_index('date', drop = True))","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:06:49.865963Z","iopub.execute_input":"2021-06-13T15:06:49.866401Z","iopub.status.idle":"2021-06-13T15:06:49.880333Z","shell.execute_reply.started":"2021-06-13T15:06:49.866345Z","shell.execute_reply":"2021-06-13T15:06:49.878839Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## loop to get all prediction !!\n\nfor (test_df, sample_prediction_df) in iter_test:\n    sample_prediction_df = process_pred(sample_prediction_df)\n    env.predict(sample_prediction_df)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:06:49.882040Z","iopub.execute_input":"2021-06-13T15:06:49.882500Z","iopub.status.idle":"2021-06-13T15:07:05.966830Z","shell.execute_reply.started":"2021-06-13T15:06:49.882448Z","shell.execute_reply":"2021-06-13T15:07:05.965882Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample_prediction_df.head()","metadata":{"execution":{"iopub.status.busy":"2021-06-13T15:07:05.967943Z","iopub.execute_input":"2021-06-13T15:07:05.968393Z","iopub.status.idle":"2021-06-13T15:07:05.980860Z","shell.execute_reply.started":"2021-06-13T15:07:05.968360Z","shell.execute_reply":"2021-06-13T15:07:05.980091Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}