{"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":"# MLB Digital Engagement Forecasting \n### Introduction \nInspired by the amazing startup notebook [Getting Started with MLB Player Digital Engagement Forecasting](https://www.kaggle.com/ryanholbrook/getting-started-with-mlb-player-digital-engagement), I intend to reconstruct some cells and add some new ideas. Furthermore, there are some explanations written in Chinese **(blue blocks)**, making chinese native speakers easier to get the meanings of the specific features.\n\nHowever, I'm not very familiar with the terminologies used in baseball. If there's any mistake I make in this notebook, please feel free to correct me and discuss together!\n\n### Competition Goal \nForecast four measures of fan engagement (`target1`-`target4`) for the next day (i.e. for `date` d, you're going to predict the engagement for `day` d+1).\n
\n

比賽目的

\n

利用球員、比賽記錄、獲獎記錄等資料及參賽者自行提取之特徵，預測下一個時間點的粉絲參與度 (target1 - target4)，數值介於0~100；其中，時間序列資料以天為單位。

\n
\n\n### Data\n#### Static\n
\n

靜態資料

\n \n
\n\n#### Time-dependent (Daily)\n
\n

時間序列資校

\n \n
","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2021-06-13T01:22:13.72883Z","iopub.execute_input":"2021-06-13T01:22:13.729435Z","iopub.status.idle":"2021-06-13T01:22:14.482024Z","shell.execute_reply.started":"2021-06-13T01:22:13.729341Z","shell.execute_reply":"2021-06-13T01:22:14.480857Z"}}},{"cell_type":"code","source":"# Import packages\nimport os\nimport warnings \nimport gc\nfrom pprint import pprint\nfrom datetime import datetime \n\nimport pandas as pd \nimport numpy as np\nimport matplotlib.pyplot as plt \nimport plotly.express as px \nimport plotly.graph_objects as go\nimport seaborn as sns\n\n# Configurations\n# warnings.simplefilter(\"ignore\")\npd.set_option('max_columns', 100) # Enable complete display if the column number of dataframe is less than 100","metadata":{"execution":{"iopub.status.busy":"2021-06-15T12:03:15.503986Z","iopub.execute_input":"2021-06-15T12:03:15.504517Z","iopub.status.idle":"2021-06-15T12:03:18.109829Z","shell.execute_reply.started":"2021-06-15T12:03:15.504396Z","shell.execute_reply":"2021-06-15T12:03:18.108739Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Variable definitions\nDATA_PATH = \"../input/mlb-player-digital-engagement-forecasting\" \nDATA_FILES = [\"players.csv\", \"teams.csv\", \"seasons.csv\", \n \"awards.csv\", \"train.csv\"]","metadata":{"execution":{"iopub.status.busy":"2021-06-15T13:33:00.597057Z","iopub.execute_input":"2021-06-15T13:33:00.597609Z","iopub.status.idle":"2021-06-15T13:33:00.603058Z","shell.execute_reply.started":"2021-06-15T13:33:00.597562Z","shell.execute_reply":"2021-06-15T13:33:00.601679Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Utility functions \ndef describe(df, stats):\n '''Describe the basic information of the raw dataframe.\n \n Parameters:\n df: pd.DataFrame, raw dataframe to be analyzed\n stats: boolean, whether to get descriptive statistics \n \n Return:\n None\n '''\n df_ = df.copy(deep=True) # Copy of the raw dataframe\n n_features = df_.shape[1]\n if n_features > pd.get_option(\"max_columns\"):\n # If the feature (column) number is greater than max number of columns displayed\n warnings.warn(\"Please reset the display-related options max_columns \\\n to enable the complete display.\", \n UserWarning) \n print(\"=====Basic information=====\")\n display(df_.info())\n get_nan_ratios(df_)\n if stats:\n print(\"=====Description=====\")\n numeric_col_num = df_.select_dtypes(include=np.number).shape[1] # Number of cols in numeric type\n if numeric_col_num != 0:\n display(df_.describe())\n else:\n print(\"There's no description of numeric data to display!\")\n del df_\n gc.collect()\n\ndef get_nan_ratios(df):\n '''Get NaN ratios of columns with NaN values.\n \n Parameters:\n df: pd.DataFrame, raw dataframe to be analyzed\n \n Return:\n None\n '''\n df_ = df.copy() # Copy of the raw dataframe\n nan_ratios = df_.isnull().sum() / df_.shape[0] * 100 # Ratios of value nan in each column\n nan_ratios = pd.DataFrame([df_.columns, nan_ratios]).T # Take transpose \n nan_ratios.columns = [\"Columns\", \"NaN ratios\"]\n nan_ratios = nan_ratios[nan_ratios[\"NaN ratios\"] != 0.0]\n print(\"=====NaN ratios of columns with NaN values=====\")\n if len(nan_ratios) == 0:\n print(\"There isn't any NaN value in the dataset!\")\n else:\n display(nan_ratios)\n del df_\n gc.collect() ","metadata":{"execution":{"iopub.status.busy":"2021-06-15T13:33:01.817053Z","iopub.execute_input":"2021-06-15T13:33:01.817496Z","iopub.status.idle":"2021-06-15T13:33:01.830469Z","shell.execute_reply.started":"2021-06-15T13:33:01.817449Z","shell.execute_reply":"2021-06-15T13:33:01.828486Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **1. Basic Description of Data**\nLet's take a peek at the data files used in this competition! ","metadata":{}},{"cell_type":"code","source":"# Get basic information about train.csv \ntrain = pd.read_csv(os.path.join(DATA_PATH, \"train.csv\"))\ntrain['date'] = pd.to_datetime(train['date'], format=\"%Y%m%d\")\ndescribe(train, True)","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2021-06-15T13:33:03.912429Z","iopub.execute_input":"2021-06-15T13:33:03.912802Z","iopub.status.idle":"2021-06-15T13:34:30.934125Z","shell.execute_reply.started":"2021-06-15T13:33:03.912769Z","shell.execute_reply":"2021-06-15T13:34:30.932737Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Get basic information about static files\nsup_files = DATA_FILES[:-1]\nfor file in sup_files:\n sup_df = pd.read_csv(os.path.join(DATA_PATH, file))\n print(f\"====={file[:-4]}=====\")\n display(sup_df.head())\n print(f\"Shape: {sup_df.shape}\")\n describe(sup_df, False)\n globals()[file[:-4]] = sup_df # Assign supplementary dataframe into globally accessible dict","metadata":{"execution":{"iopub.status.busy":"2021-06-15T13:34:30.936192Z","iopub.execute_input":"2021-06-15T13:34:30.936576Z","iopub.status.idle":"2021-06-15T13:34:32.140831Z","shell.execute_reply.started":"2021-06-15T13:34:30.936518Z","shell.execute_reply":"2021-06-15T13:34:32.139210Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **2. Train DataFrame Unpacking**\nNow, let's unpack `train.csv` to enable the further analysis.","metadata":{}},{"cell_type":"code","source":"# Utility functions\ndef unpack_json(json_str):\n '''Convert json string found in daily dataframe to pandas object.\n \n Parameters:\n json_str: str, data entry in \"train\" dataframe in the format of json string\n \n Return:\n json_obj: np.nan or pandas object, nan if the entry is originally nan; or, the converted pandas object is returned \n '''\n return np.nan if pd.isna(json_str) else pd.read_json(json_str)","metadata":{"execution":{"iopub.status.busy":"2021-06-15T13:34:32.143721Z","iopub.execute_input":"2021-06-15T13:34:32.144205Z","iopub.status.idle":"2021-06-15T13:34:32.149297Z","shell.execute_reply.started":"2021-06-15T13:34:32.144154Z","shell.execute_reply":"2021-06-15T13:34:32.148154Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create mapping relationship between column names of train dataframe and the \"unpacked\" table\ndaily_unpacked_dfs = pd.DataFrame(train.columns[1:], columns=[\"dfName\"])\ndaily_unpacked_dfs[\"df\"] = [pd.DataFrame() for _ in range(len(daily_unpacked_dfs))]\ndaily_unpacked_dfs","metadata":{"execution":{"iopub.status.busy":"2021-06-15T13:34:32.151308Z","iopub.execute_input":"2021-06-15T13:34:32.151792Z","iopub.status.idle":"2021-06-15T13:34:32.189763Z","shell.execute_reply.started":"2021-06-15T13:34:32.151742Z","shell.execute_reply":"2021-06-15T13:34:32.188265Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Unpack each table residing in train dataframe\nfor df_idx, sub_df in daily_unpacked_dfs.iterrows():\n table_name = sub_df[\"dfName\"]\n table = train.loc[:, [\"date\", table_name]]\n table = (table[~pd.isna(table[table_name])].reset_index(drop=True)) # Get samples having no nan entries \n \n daily_unpacked_samples = [] \n for daily_idx, daily_sample in table.iterrows():\n daily_unpacked_sample = unpack_json(daily_sample[table_name])\n daily_unpacked_sample[\"dailyDataDate\"] = daily_sample[\"date\"]\n daily_unpacked_samples = daily_unpacked_samples + [daily_unpacked_sample]\n unpacked_table = pd.concat(daily_unpacked_samples, ignore_index=True).set_index(\"dailyDataDate\").reset_index()\n\n globals()[table_name] = unpacked_table # Assign unpacked table into globally accessible dict\n daily_unpacked_dfs[\"df\"][df_idx] = unpacked_table # Assign unpacked table to create the mapping relationship \n # (dfName (table name) <--> unpacked table)\n\n# Free up the memory\ndel train, table, daily_unpacked_samples\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2021-06-15T13:34:32.191601Z","iopub.execute_input":"2021-06-15T13:34:32.191974Z","iopub.status.idle":"2021-06-15T13:40:00.793789Z","shell.execute_reply.started":"2021-06-15T13:34:32.191939Z","shell.execute_reply":"2021-06-15T13:40:00.792772Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Dump dataframe to csv\ndaily_unpacked_dfs.to_csv(\"./daily_unpacked_dfs.csv\", index=False)","metadata":{"execution":{"iopub.status.busy":"2021-06-13T01:49:28.771723Z","iopub.execute_input":"2021-06-13T01:49:28.772257Z","iopub.status.idle":"2021-06-13T01:49:28.991125Z","shell.execute_reply.started":"2021-06-13T01:49:28.772206Z","shell.execute_reply":"2021-06-13T01:49:28.989703Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Check unpacked table by showing one single sample from each table \nfor table_name in daily_unpacked_dfs[\"dfName\"]:\n print(f\"=====Table: {table_name}=====\")\n table = globals()[table_name]\n display(table.head(1))\n print(f\"Shape: {table.shape}\\n\")","metadata":{"execution":{"iopub.status.busy":"2021-06-15T13:40:00.795175Z","iopub.execute_input":"2021-06-15T13:40:00.795705Z","iopub.status.idle":"2021-06-15T13:40:01.099786Z","shell.execute_reply.started":"2021-06-15T13:40:00.795660Z","shell.execute_reply":"2021-06-15T13:40:01.098650Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **3. Data Merging**\nIn this part, we are going to dig deeper into each table unpacked above and try to understand the basic meaning of each feature. Also, there will be some **feature extracting processes** aiming at finding potentially useful features.\n\n## *3.1 Feature Extraction for Dates and Seasons*\n### 3.1.1 Features related to `dates`\n* Decompositions of the date (e.g. year and month)\n* Weekday of the date ","metadata":{}},{"cell_type":"code","source":"dates = pd.DataFrame(nextDayPlayerEngagement['dailyDataDate'].unique(), columns=['dailyDataDate'])\ndates['date'] = pd.to_datetime(dates['dailyDataDate'].astype(str))\ndates['year'] = dates['date'].dt.year\ndates['month'] = dates['date'].dt.month\ndates['weekday'] = dates['date'].apply(lambda date: date.weekday()) # Retrieve weekday for each date\n\nprint(\"=====DataFrame: dates=====\")\ndisplay(dates.head())","metadata":{"execution":{"iopub.status.busy":"2021-06-15T13:40:01.101245Z","iopub.execute_input":"2021-06-15T13:40:01.101583Z","iopub.status.idle":"2021-06-15T13:40:01.159884Z","shell.execute_reply.started":"2021-06-15T13:40:01.101535Z","shell.execute_reply":"2021-06-15T13:40:01.158883Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.1.2 Features related to `seasons`:\n* Whether the date is in season or not\n* Season categories\n
\n \n
","metadata":{}},{"cell_type":"code","source":"dates_with_seasons = pd.merge(dates, seasons, left_on='year', right_on='seasonId') # Join two dfs with key 'seasonId' (i.e. year)\n\n# Determine whether the date is in season or not\ndates_with_seasons['inSeason'] = dates_with_seasons['date'].between(\n dates_with_seasons['regularSeasonStartDate'],\n dates_with_seasons['postSeasonEndDate'],\n inclusive=True # Include boudaries\n) \n\n# Categorize different game seasons\ndates_with_seasons['seasonPart'] = np.select(\n [\n dates_with_seasons['date'] < dates_with_seasons['preSeasonStartDate'], \n dates_with_seasons['date'] < dates_with_seasons['regularSeasonStartDate'],\n dates_with_seasons['date'] <= dates_with_seasons['lastDate1stHalf'],\n dates_with_seasons['date'] < dates_with_seasons['firstDate2ndHalf'],\n dates_with_seasons['date'] <= dates_with_seasons['regularSeasonEndDate'],\n dates_with_seasons['date'] < dates_with_seasons['postSeasonStartDate'],\n dates_with_seasons['date'] <= dates_with_seasons['postSeasonEndDate'],\n dates_with_seasons['date'] > dates_with_seasons['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\nprint(\"=====DataFrame: dates_with_seasons=====\")\ndisplay(dates_with_seasons.head())\n\n# Plot bar chart of season categories\nseason_counts = dates_with_seasons['seasonPart'].value_counts()\nfig = go.Figure()\nfig.add_trace(go.Pie(\n labels=season_counts.index,\n values=season_counts\n))\nfig.update_layout(\n title='Pie Chart of Season Categories'\n)\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2021-06-15T13:40:01.162172Z","iopub.execute_input":"2021-06-15T13:40:01.162534Z","iopub.status.idle":"2021-06-15T13:40:01.339873Z","shell.execute_reply.started":"2021-06-15T13:40:01.162498Z","shell.execute_reply":"2021-06-15T13:40:01.338390Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## *3.2 Feature Extraction for Game Stats at **Player Game** Level*\n
\n

以下為 playerBoxScores table中所有features的中文解釋，請點擊下方展開cell

\n
","metadata":{"_kg_hide-output":false}},{"cell_type":"markdown","source":"
\n \n
","metadata":{"_kg_hide-output":true}},{"cell_type":"markdown","source":"### 3.2.1 Features related to `game states`\n* Fractional representation of innings a pitcher pitches in a game \n
\n \n
\n* [Tom Tango pitching game score](https://www.mlb.com/glossary/advanced-stats/game-score) \n* Whether it's a no-hitter game or not\n
\n \n
\n\n * Because the feature is at **player game** level, no-hitter game discussed here is completed by **a single pitcher**.","metadata":{}},{"cell_type":"code","source":"# Rename columns to enhance recognizability, indicating these columns aren't come from 'roster'\nplayer_game_stats = playerBoxScores.copy().rename(\n columns={\n 'teamId': 'gameTeamId', \n 'teamName': 'gameTeamName'\n }\n)\n\n# Add in fractional representation of inningsPitched\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 \nplayer_game_stats['pitchingGameScore'] = (\n 40 + \n 2 * player_game_stats['outsPitching'] + # Game total outs recorderd \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 game completed by a single pitcher \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, \n 0\n)\n\nprint(\"=====DataFrame: player_game_stats=====\")\ndisplay(player_game_stats.head())","metadata":{"execution":{"iopub.status.busy":"2021-06-15T13:40:01.344010Z","iopub.execute_input":"2021-06-15T13:40:01.344390Z","iopub.status.idle":"2021-06-15T13:40:01.644438Z","shell.execute_reply.started":"2021-06-15T13:40:01.344357Z","shell.execute_reply":"2021-06-15T13:40:01.643373Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.2.2 Features related to results of applying aggregate functions on `game stats` \nAll the aggregate functions are applied at **date-player** level, meaning that the groupings are done by compound key **(dailyDataDate, playerId)**.\n* Number of games per player per day\n* Number of teams per player per day \nThis should be **one team per player per day**, but playerId **518617 (Jake Diekman)** had 2 games for different teams marked as played on 5/19/19, due to resumption of game after he was traded.\n* Team identifier for (dailyDataDate, playerId) pair\nThis should be only **one team for almost all (dailyDataDate, playerId) pairs**. \n* Sum of some player game stats ","metadata":{}},{"cell_type":"code","source":"# Apply aggregate functions on specific features\nplayer_date_stats_agg = pd.merge(\n player_game_stats.groupby(['dailyDataDate', 'playerId'], as_index=False).agg(\n numGames=('gamePk', 'nunique'), \n numTeams=('gameTeamId', 'nunique'),\n gameTeamId=('gameTeamId', 'min') # Take min to simplify the extraction \n ), \n player_game_stats.groupby(['dailyDataDate', 'playerId'], as_index = False)[\n ['runsScored', 'homeRuns', 'strikeOuts', 'baseOnBalls', \n 'hits', 'hitByPitch', 'atBats', 'caughtStealing', \n 'stolenBases', 'groundIntoDoublePlay', 'groundIntoTriplePlay', 'plateAppearances',\n 'totalBases', 'rbi', 'leftOnBase', 'sacBunts', \n 'sacFlies', 'gamesStartedPitching', 'runsPitching', 'homeRunsPitching', \n 'strikeOutsPitching', 'baseOnBallsPitching', 'hitsPitching', 'inningsPitchedAsFrac', \n 'earnedRuns', 'battersFaced', 'saves', 'blownSaves', \n 'pitchingGameScore', 'noHitter'\n ]\n ].sum(), \n on=['dailyDataDate', 'playerId'],\n how='inner'\n)\n\nprint(\"=====DataFrame: player_date_stats_agg=====\")\ndisplay(player_date_stats_agg.head())","metadata":{"execution":{"iopub.status.busy":"2021-06-15T13:40:01.646158Z","iopub.execute_input":"2021-06-15T13:40:01.646994Z","iopub.status.idle":"2021-06-15T13:40:02.568641Z","shell.execute_reply.started":"2021-06-15T13:40:01.646917Z","shell.execute_reply":"2021-06-15T13:40:02.567346Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## *3.3 Feature Extraction for Game Stats at **Team Game** Level*\n
\n

由於 teamBoxScores 中的features可以跟上面 playerBoxScores 作對照，故不另外作翻譯。兩者的差異在於統計數據的層次不同，playerBoxScores 是以單個球員的表現計算，teamBoxScores 則統計整個球隊的比賽表現。

\n

以下為 games 中所有features的中文解釋，請點擊下方展開cell

\n
","metadata":{}},{"cell_type":"markdown","source":"
\n \n
","metadata":{"_kg_hide-output":true}},{"cell_type":"markdown","source":"### 3.3.1 Games table reconstruction\nTo convert the `games` table into the format of **one row per team-game**, the following processing is done:\n* Extract specific game types and ensure the validity of scores.\n* Recreate two games tables from different perspectives (**home team** and **away team**).\n* Concatenate two games tables into final one.","metadata":{}},{"cell_type":"code","source":"# Extract games played in regular or post-season with valid scores (those without NaN)\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\n# Get games table from home team perspective\ngames_home_perspective = games_for_stats.copy()\ngames_home_perspective.columns = [\n col_value.replace('home', 'team').replace('away', 'opp') for \n col_value in games_home_perspective.columns.values\n] # Change column names so that \"team\" is \"home\", \"opp\" is \"away\"\ngames_home_perspective['isHomeTeam'] = 1\n\n# Get games table from away team perspective\ngames_away_perspective = games_for_stats.copy()\ngames_away_perspective.columns = [\n col_value.replace('home', 'opp').replace('away', 'team') for \n col_value in games_away_perspective.columns.values\n] # Change column names so that \"opp\" is \"home\", \"team\" is \"away\"\ngames_away_perspective['isHomeTeam'] = 0\n\n# Put together games tables from home/away perspective\nteam_games = (pd.concat(\n [\n games_home_perspective,\n games_away_perspective\n ],\n ignore_index=True)\n)\n\nprint(\"=====DataFrame: team_games=====\")\ndisplay(team_games.head())","metadata":{"execution":{"iopub.status.busy":"2021-06-15T13:40:02.570531Z","iopub.execute_input":"2021-06-15T13:40:02.570910Z","iopub.status.idle":"2021-06-15T13:40:02.678255Z","shell.execute_reply.started":"2021-06-15T13:40:02.570866Z","shell.execute_reply":"2021-06-15T13:40:02.676875Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.3.2 Features related to results of applying aggregate functions on `game stats`\nFirst, modification of column names is done on `team_game_stats` (`teamBoxScores`) table; then, we merge `team` table with `team_game_stats` table. The final extracted features are as follows:\n* Number of games per team per day\n* Number of winning games per team per day\n* Number of losing games per team per day\n* Total runs scored per team per day\n* Total runs allowd per team per day","metadata":{}},{"cell_type":"code","source":"# Copy over team box scores data (team-game level)\nteam_game_stats = teamBoxScores.copy()\n\n# Add suffix 'Team' to column names to reflect these stats are at team-game level,\n# helping differentiate from individual player stats (player-game level) when joining\nteam_game_stats.columns = [\n (col_value + 'Team') \n if (col_value not in ['dailyDataDate', 'home', 'teamId', \n 'gamePk','gameDate', 'gameTimeUTC'])\n else col_value for \n col_value in team_game_stats.columns.values\n]\n\n# Merge games table with team_game_stats table\nteam_games_with_stats = pd.merge(\n team_games,\n # Drop columns already present in team_games table\n team_game_stats.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)\n\nprint(\"=====DataFrame: team_games_with_stats=====\")\ndisplay(team_games_with_stats.head())\n\n# Apply aggregate functions on specific features\nteam_date_stats_agg = (\n team_games_with_stats.groupby(['dailyDataDate', 'teamId', 'gameType', \n 'oppId', 'oppName'], as_index = False).agg(\n numGamesTeam = ('gamePk', 'nunique'),\n winsTeam = ('teamWinner', 'sum'),\n lossesTeam = ('oppWinner', 'sum'),\n runsScoredTeam = ('teamScore', 'sum'),\n runsAllowedTeam = ('oppScore', 'sum')\n )\n)\n\nprint(\"=====DataFrame: team_date_stats_agg=====\")\ndisplay(team_date_stats_agg.head())","metadata":{"execution":{"iopub.status.busy":"2021-06-15T13:55:43.084053Z","iopub.execute_input":"2021-06-15T13:55:43.084582Z","iopub.status.idle":"2021-06-15T13:55:43.264975Z","shell.execute_reply.started":"2021-06-15T13:55:43.084518Z","shell.execute_reply":"2021-06-15T13:55:43.263693Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## *3.4 Feature Extraction for `Standings`*\nIn this part, only certain features of interest are picked from `standings` table.\n### 3.4.1 Features related to `streakCode`\n* Number of streak win\n* Number of streak loss","metadata":{}},{"cell_type":"code","source":"# Select features of interest from 'standings' table\nstandings_selected_fields = (\n standings[['dailyDataDate', 'teamId', 'streakCode', \n 'divisionRank', 'leagueRank', 'wildCardRank', \n 'pct']].rename(columns = {'pct': 'winPct'})\n)\n\n# Add suffix 'Team' to column names to reflect these features are at team level,\n# helping differentiate from those at player level when joining\nstandings_selected_fields.columns = [\n (col_value + 'Team') \n if (col_value not in ['dailyDataDate', 'teamId'])\n else col_value for \n col_value in standings_selected_fields.columns.values\n]\n\n# Process the streak (win/lose) information\n# Add fields to separate winning and losing streak from streak code\nstandings_selected_fields['streakLengthTeam'] = (\n standings_selected_fields['streakCodeTeam'].\n str.replace('W', '').\n str.replace('L', '').\n astype(float)\n) # Extract magnitude of streak\n\n# Process scenario of winning \nstandings_selected_fields['winStreakTeam'] = np.where(\n standings_selected_fields['streakCodeTeam'].str[0] == 'W',\n standings_selected_fields['streakLengthTeam'],\n np.nan\n)\n\n# Process scenario of losing \nstandings_selected_fields['lossStreakTeam'] = np.where(\n standings_selected_fields['streakCodeTeam'].str[0] == 'L',\n standings_selected_fields['streakLengthTeam'],\n np.nan\n)\n\nstandings_for_digital_engagement_merge = (\n pd.merge(\n standings_selected_fields,\n dates_with_seasons[['dailyDataDate', 'inSeason']],\n on=['dailyDataDate'],\n how='left'\n ).\n # Limit down standings to only in season version\n query(\"inSeason\").\n # Drop fields (features) no longer necessary\n drop(['streakCodeTeam', 'streakLengthTeam', 'inSeason'], axis=1).\n reset_index(drop=True)\n)\n\nprint(\"=====DataFrame: standings_for_digital_engagement_merge=====\")\ndisplay(standings_for_digital_engagement_merge.head())","metadata":{"execution":{"iopub.status.busy":"2021-06-15T14:13:51.708245Z","iopub.execute_input":"2021-06-15T14:13:51.708772Z","iopub.status.idle":"2021-06-15T14:13:51.856208Z","shell.execute_reply.started":"2021-06-15T14:13:51.708728Z","shell.execute_reply":"2021-06-15T14:13:51.854877Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"
\n

Work in Progress...

\n

更多中文翻譯及解釋即將釋出~~

\n

Thanks for your attention!!

\n
\n\n\n\n","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}