{"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":"## Notebook Setup","metadata":{"execution":{"iopub.execute_input":"2021-06-04T23:07:54.351665Z","iopub.status.busy":"2021-06-04T23:07:54.351302Z","iopub.status.idle":"2021-06-04T23:07:54.356206Z","shell.execute_reply":"2021-06-04T23:07:54.355039Z","shell.execute_reply.started":"2021-06-04T23:07:54.351637Z"}}},{"cell_type":"code","source":"#### Import Python Libraries and Set Script Options ####\nimport numpy as np\nimport pandas as pd\n\n# Plotly libraries\nimport plotly as pl\nimport plotly.express as px\nimport plotly.offline as pyo\nimport plotly.graph_objs as go\n\n# Library for interactive Python widgets\nimport ipywidgets as widgets\n\n# Utility libraries\nimport gc\nfrom pathlib import Path\n\n# Set notebook mode to make plotly graphics offline\npyo.init_notebook_mode()\n\n# Expand max column width when displaying data frames \npd.set_option('display.max_colwidth', 100)\n\n# Lists all input data files from \"../input/\" directory\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))","metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","execution":{"iopub.status.busy":"2021-06-10T08:56:33.254497Z","iopub.execute_input":"2021-06-10T08:56:33.255024Z","iopub.status.idle":"2021-06-10T08:56:34.533966Z","shell.execute_reply.started":"2021-06-10T08:56:33.254938Z","shell.execute_reply":"2021-06-10T08:56:34.533189Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Read in Kaggle Data Files","metadata":{}},{"cell_type":"markdown","source":"#### Read in MLB Player Digital Engagement Forecasting Data from CSVs into pandas DFs","metadata":{}},{"cell_type":"code","source":"# Start with input file path\ninput_file_path = Path('/kaggle/input/mlb-player-digital-engagement-forecasting/')\n\n# Create table with list of CSV files to be read in, w/ corresponding df name\n# This does include large 'train' data set (read in separately)\ncsv_and_df_names = pd.DataFrame(data = {\n  'csv_name': ['seasons', 'teams', 'players', 'awards',\n    'example_test', 'example_sample_submission'],\n  'df_name': ['seasons', 'teams', 'players', 'awards_pre2018',\n    'example_test', 'example_sample_submission'] \n  })\n\n# Set up for tabbed output\nkaggle_data_tabs = widgets.Tab()\n\n# Add Output widgets for each (eventual) DF as tabs' children\nkaggle_data_tabs.children = list([widgets.Output() for df_name \n  in csv_and_df_names['df_name']])\n\nfor index, row in csv_and_df_names.iterrows():\n    \n    csv_name = row['csv_name']\n    df_name = row['df_name']\n    \n    # Read from CSV and create df with specified name in environment\n    globals()[df_name] = pd.read_csv(input_file_path / f\"{csv_name}.csv\")\n\n    # Set tab title to df name\n    kaggle_data_tabs.set_title(index, df_name)\n    \n    # Display corresponding table output for this tab name\n    with kaggle_data_tabs.children[index]:\n        display(eval(df_name))\n\ndisplay(kaggle_data_tabs)","metadata":{"execution":{"iopub.status.busy":"2021-06-10T08:56:43.324633Z","iopub.execute_input":"2021-06-10T08:56:43.324979Z","iopub.status.idle":"2021-06-10T08:56:44.319333Z","shell.execute_reply.started":"2021-06-10T08:56:43.324949Z","shell.execute_reply":"2021-06-10T08:56:44.318656Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Read in Training Data CSV into pandas DF","metadata":{}},{"cell_type":"code","source":"train = pd.read_csv(input_file_path / 'train.csv')\n\n# Convert training data date field to pandas datetime type\ntrain['date'] = pd.to_datetime(train['date'], format = \"%Y%m%d\")\n\ndisplay(train.info())\n\ndisplay(train)","metadata":{"execution":{"iopub.status.busy":"2021-06-10T08:56:54.901109Z","iopub.execute_input":"2021-06-10T08:56:54.901665Z","iopub.status.idle":"2021-06-10T08:58:00.194496Z","shell.execute_reply.started":"2021-06-10T08:56:54.901619Z","shell.execute_reply":"2021-06-10T08:58:00.193398Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Unnest and look at data from each of the nested data frames within the daily data","metadata":{}},{"cell_type":"code","source":"# Get names of all \"nested\" data frames in daily training set\ndaily_data_nested_df_names = train.drop('date', axis = 1).columns.values.tolist()\n\nfor df_name in daily_data_nested_df_names:\n    date_nested_table = train[['date', df_name]]\n\n    date_nested_table = (date_nested_table[\n      ~pd.isna(date_nested_table[df_name])\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 = pd.read_json(date_row[df_name])\n        \n        daily_df['dailyDataDate'] = date_row['date']\n        \n        daily_dfs_collection = daily_dfs_collection + [daily_df]\n\n    # Concatenate all daily dfs into single df for each row\n    unnested_table = (pd.concat(daily_dfs_collection,\n      ignore_index = True).\n      # Set and reset index to move 'dailyDataDate' to front of df\n      set_index('dailyDataDate').\n      reset_index()\n      )\n    \n    # Creates 1 pandas df per unnested df from daily data read in, with same name\n    globals()[df_name] = unnested_table    \n    \n    # Clean up tables and collection of daily data frames for this df\n    del(date_nested_table, daily_dfs_collection, unnested_table)\n\n# Set up for tabbed output\ndaily_data_unnested_tabs = widgets.Tab()\n\n# Add Output widgets for each (eventual) DF as tabs' children\ndaily_data_unnested_tabs.children = list([widgets.Output() \n  for df_name in daily_data_nested_df_names])\n\nfor index in range(0, len(daily_data_nested_df_names)):\n    df_name = daily_data_nested_df_names[index]\n    \n    # Rename tab bar titles to df names\n    daily_data_unnested_tabs.set_title(index, df_name)\n\n    # Display corresponding table output for this tab name\n    with daily_data_unnested_tabs.children[index]:\n        display(eval(df_name))\n\ndisplay(daily_data_unnested_tabs)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T08:58:20.289059Z","iopub.execute_input":"2021-06-10T08:58:20.289399Z","iopub.status.idle":"2021-06-10T09:01:50.187195Z","shell.execute_reply.started":"2021-06-10T08:58:20.28937Z","shell.execute_reply":"2021-06-10T09:01:50.186255Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#### Delete original training data since it has all been extracted and garbage collect, to clear memory\n\ndel(train)\n\ngc.collect()","metadata":{"_kg_hide-input":true,"_kg_hide-output":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Exploratory Analysis of Target Variables","metadata":{}},{"cell_type":"markdown","source":"#### Look at some distribution summary values for each target","metadata":{}},{"cell_type":"code","source":"# Melt data to get 1 row per player-date per target\nplayer_engagement_targets_melted = pd.melt(\n  nextDayPlayerEngagement,\n  id_vars = ['dailyDataDate', 'playerId'],\n  value_vars = ['target1', 'target2', 'target3', 'target4'],\n  var_name = 'target'\n  )\n\n# Calculate some distribution summary values by target\nplayer_engagement_data_summary_by_target = (player_engagement_targets_melted.\n  groupby(['target'], as_index = False).\n  agg(\n    count = ('value', 'count'),\n    # Mean and standard deviation\n    mean = ('value', np.mean),\n    stdDev = ('value', np.std),\n    # A few percentiles of interest (including median)\n    pctle10 = ('value', lambda x: np.percentile(x, q = 10)),\n    pctle25 = ('value', lambda x: np.percentile(x, q = 25)),\n    median = ('value', np.median),\n    pctle75 = ('value', lambda x: np.percentile(x, q = 75)),\n    pctle90 = ('value', lambda x: np.percentile(x, q = 90)),\n    # Percentage of all target values equal to min (0) or max (100)\n    pctValues0 = ('value', lambda x: np.mean(x == 0) * 100),\n    pctValues100 = ('value', lambda x: np.mean(x == 100) * 100)\n    )\n  )\n\ndisplay(player_engagement_data_summary_by_target.round(decimals = 3))","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:03:07.11247Z","iopub.execute_input":"2021-06-10T09:03:07.11284Z","iopub.status.idle":"2021-06-10T09:03:10.303762Z","shell.execute_reply.started":"2021-06-10T09:03:07.112798Z","shell.execute_reply":"2021-06-10T09:03:10.302831Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Interactive distribution plot for each prediction target over all training data ####","metadata":{}},{"cell_type":"code","source":"# Decimal places to round target values to for grouping in distribution plot\nROUND_DECIMALS = 0\n\n# Round target values so they can be grouped by for distributions\nplayer_engagement_targets_melted['roundedValue'] = (\n  player_engagement_targets_melted['value'].round(ROUND_DECIMALS))\n\n# Group by target and rounded value\nplayer_engagement_targets_dist = (player_engagement_targets_melted.\n  groupby(['target', 'roundedValue'], as_index = False).\n  agg(numPlayerDates = ('playerId', 'count'))\n  )\n\nplayer_engagement_targets_dist['cumPlayerDates'] = (player_engagement_targets_dist.\n  groupby(['target'])['numPlayerDates'].cumsum())\n\nplayer_engagement_targets_dist['cumPctPlayerDates'] = (\n  player_engagement_targets_dist['cumPlayerDates'] /\n  # Divide by total # of player-dates in original data set\n  nextDayPlayerEngagement.shape[0]\n  ) * 100\n    \nplayer_engagement_targets_dist_plot = px.bar(\n  player_engagement_targets_dist,\n  x = 'roundedValue',\n  y = 'numPlayerDates',\n  facet_row = 'target',\n  hover_data = player_engagement_targets_dist.columns,\n  labels = {\n    'roundedValue': 'Rounded Target Value',\n    'numPlayerDates': '# of Player-Dates',\n    'cumPlayerDates': 'Cumulative # of Player-Dates',\n    'cumPctPlayerDates': 'Cumulative % of Player-Dates'\n    },\n  title = 'Target Value Distributions Across Player-Dates',\n  width = 900,\n  height = 900\n  )\n\npyo.iplot(player_engagement_targets_dist_plot)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:03:24.982879Z","iopub.execute_input":"2021-06-10T09:03:24.983226Z","iopub.status.idle":"2021-06-10T09:03:27.065799Z","shell.execute_reply.started":"2021-06-10T09:03:24.983198Z","shell.execute_reply":"2021-06-10T09:03:27.064791Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Look at correlations among target metrics across player-dates","metadata":{}},{"cell_type":"code","source":"player_engagement_targets_correlations = (nextDayPlayerEngagement[\n  ['target1', 'target2', 'target3', 'target4']].\n  corr()\n  )\n\ndisplay(player_engagement_targets_correlations.round(decimals = 3))","metadata":{"execution":{"iopub.status.busy":"2021-06-10T09:03:43.403299Z","iopub.execute_input":"2021-06-10T09:03:43.403648Z","iopub.status.idle":"2021-06-10T09:03:43.761825Z","shell.execute_reply.started":"2021-06-10T09:03:43.403618Z","shell.execute_reply":"2021-06-10T09:03:43.760839Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#### Remove large melted player engagement data frame to clear memory\ndel(player_engagement_targets_melted)\n\ngc.collect()","metadata":{"_kg_hide-input":true,"_kg_hide-output":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Exploratory Analysis and Preparation of Data for Potential Predictors of Digital Engagement","metadata":{}},{"cell_type":"markdown","source":"### Dates Relative to Season","metadata":{}},{"cell_type":"markdown","source":"#### Get some information on each date in daily data (using season dates of interest)","metadata":{}},{"cell_type":"code","source":"dates = pd.DataFrame(data = \n  {'dailyDataDate': nextDayPlayerEngagement['dailyDataDate'].unique()})\n\ndates['year'] = dates['dailyDataDate'].dt.year\ndates['month'] = dates['dailyDataDate'].dt.month\n\ndates_with_info = pd.merge(\n  dates,\n  seasons,\n  left_on = 'year',\n  right_on = 'seasonId'\n  )\n\n# Count anything between regular and Postseason as \"in season\"\ndates_with_info['inSeason'] = (\n  dates_with_info['dailyDataDate'].between(\n    dates_with_info['regularSeasonStartDate'],\n    dates_with_info['postSeasonEndDate'],\n    inclusive = True\n    )\n  )\n\n# Separate dates into different parts of MLB season\ndates_with_info['seasonPart'] = np.select(\n  [\n    dates_with_info['dailyDataDate'] < dates_with_info['preSeasonStartDate'], \n    dates_with_info['dailyDataDate'] < dates_with_info['regularSeasonStartDate'],\n    dates_with_info['dailyDataDate'] <= dates_with_info['lastDate1stHalf'],\n    dates_with_info['dailyDataDate'] < dates_with_info['firstDate2ndHalf'],\n    dates_with_info['dailyDataDate'] <= dates_with_info['regularSeasonEndDate'],\n    dates_with_info['dailyDataDate'] < dates_with_info['postSeasonStartDate'],\n    dates_with_info['dailyDataDate'] <= dates_with_info['postSeasonEndDate'],\n    dates_with_info['dailyDataDate'] > 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\ndates_with_season_part = (dates_with_info[['dailyDataDate', 'year',\n  'seasonId', 'month', 'inSeason', 'seasonPart']].\n  rename(columns = {'seasonId': 'season'})\n  )\n\ndisplay(dates_with_season_part)","metadata":{"execution":{"iopub.status.busy":"2021-06-10T09:03:55.937225Z","iopub.execute_input":"2021-06-10T09:03:55.937573Z","iopub.status.idle":"2021-06-10T09:03:55.997415Z","shell.execute_reply.started":"2021-06-10T09:03:55.937544Z","shell.execute_reply":"2021-06-10T09:03:55.996744Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Rosters","metadata":{}},{"cell_type":"markdown","source":"#### Look at different roster status values and frequency in data","metadata":{}},{"cell_type":"code","source":"roster_status_values = (rosters.\n  groupby(['statusCode', 'status'], as_index = False).\n  agg(\n    numPlayerDates = ('playerId', 'count')\n    ).\n  sort_values(['numPlayerDates'], ascending = False, \n    ignore_index = True)\n  )\n\ndisplay(roster_status_values)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:04:50.907403Z","iopub.execute_input":"2021-06-10T09:04:50.908154Z","iopub.status.idle":"2021-06-10T09:04:51.39142Z","shell.execute_reply.started":"2021-06-10T09:04:50.908109Z","shell.execute_reply":"2021-06-10T09:04:51.390416Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Check for any cases of multiple roster rows per player-date\nIf output below has 0 rows, can proceed without worrying about duplicate player-date roster rows.","metadata":{}},{"cell_type":"code","source":"player_dates_multiple_roster_entries = (rosters.\n  groupby(['dailyDataDate', 'playerId'], as_index = False).\n  agg(\n    numPlayerDateRosterEntries = ('playerId', 'count')\n    ).\n  query(\"numPlayerDateRosterEntries > 1\")\n  )\n\ndisplay(player_dates_multiple_roster_entries)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:24:10.60401Z","iopub.execute_input":"2021-06-10T09:24:10.60449Z","iopub.status.idle":"2021-06-10T09:24:11.176378Z","shell.execute_reply.started":"2021-06-10T09:24:10.60445Z","shell.execute_reply":"2021-06-10T09:24:11.175368Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Team Games and Game Stats","metadata":{}},{"cell_type":"markdown","source":"#### Turn games table into 1 row per team game, then merge with team box scores","metadata":{}},{"cell_type":"code","source":"# Filter to regular/Postseason & All-Star games marked \"final\" in games table\ngames_for_stats = games[\n  np.isin(games['gameType'], ['R', 'F', 'D', 'L', 'W', 'C', 'P', 'A']) &\n  (games['codedGameState'] == 'F')\n  ]\n\n# Get games table from home team perspective\ngames_home_perspective = games_for_stats.copy()\n\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]\n\ngames_home_perspective['isHomeTeam'] = 1\n\n# Get games table from away team perspective\ngames_away_perspective = games_for_stats.copy()\n\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]\n\ngames_away_perspective['isHomeTeam'] = 0\n\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\n# Copy over team box scores data to modify\nteam_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\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\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', 'gameTimeUTC'], axis = 1),\n  on = ['dailyDataDate', 'gamePk', 'gameDate', '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\ndisplay(team_games_with_stats)","metadata":{"execution":{"iopub.status.busy":"2021-06-10T09:12:14.834235Z","iopub.execute_input":"2021-06-10T09:12:14.834786Z","iopub.status.idle":"2021-06-10T09:12:14.96886Z","shell.execute_reply.started":"2021-06-10T09:12:14.834751Z","shell.execute_reply":"2021-06-10T09:12:14.96804Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Aggregate team game-level stats to daily date (accounts for multiple games per day)\nIf 1st output below has 0 rows, can proceed without worrying about duplicate team dates.","metadata":{}},{"cell_type":"code","source":"# Verify that no team played 2 different opponents or in 2 different game types\n# Allows opp and gameType to be used in aggregation w/o getting multiple rows\nteam_date_gameTypes_opps_agg = (team_games_with_stats.\n  groupby(['dailyDataDate', 'teamId'], as_index = False).\n  agg(\n    numGameTypes = ('gameType', 'nunique'),\n    numOppIds = ('oppId', 'nunique'),\n    numOppNames = ('oppName', 'nunique')\n    )\n  )\n\n# Can proceed w/o worrying about duplicate team-dates as long as this returns 0 rows\ndisplay(team_date_gameTypes_opps_agg[\n  (team_date_gameTypes_opps_agg['numGameTypes'] != 1) |\n  (team_date_gameTypes_opps_agg['numOppIds'] != 1) |\n  (team_date_gameTypes_opps_agg['numOppNames'] != 1)\n  ])\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\ndisplay(team_date_stats_agg)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:16:23.365469Z","iopub.execute_input":"2021-06-10T09:16:23.365815Z","iopub.status.idle":"2021-06-10T09:16:23.476898Z","shell.execute_reply.started":"2021-06-10T09:16:23.365781Z","shell.execute_reply":"2021-06-10T09:16:23.4758Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Player Game Stats","metadata":{}},{"cell_type":"markdown","source":"#### Add some stats/info to player game level stats, look at notable results","metadata":{}},{"cell_type":"code","source":"# Copy over player box scores df and rename team fields\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\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'] = np.where(\n  # pitching game score doesn't apply if player didn't pitch, set to NA\n  pd.isna(player_game_stats['pitchesThrown']) | \n    (player_game_stats['pitchesThrown'] == 0),\n  np.nan,\n  (40\n    + 2 * player_game_stats['outsPitching']\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\n# Look at top pitching game scores in span of data\nplayer_game_top_pitching_game_scores = (player_game_stats\n  [['gameDate', 'playerName', 'gameTeamName', 'outsPitching',\n    'strikeOutsPitching', 'baseOnBallsPitching', 'hitsPitching',\n    'runsPitching', 'homeRunsPitching', 'pitchingGameScore']].\n  sort_values(['pitchingGameScore'], ascending = False,\n    ignore_index = True).\n  head(n = 10)\n  )\n    \nprint('Top Pitching Game Scores in Span of Data')\ndisplay(player_game_top_pitching_game_scores)\n\n# Add in criteria for no-hitter by pitcher (individual, not multiple pitchers)\nplayer_game_stats['noHitter'] = np.where(\n  (player_game_stats['completeGamesPitching'] == 1) &\n  (player_game_stats['inningsPitched'] >= 9) &\n  (player_game_stats['hitsPitching'] == 0),\n  1, 0\n  )\n\nplayer_game_no_hitters = (player_game_stats\n  [player_game_stats['noHitter'] == 1]\n  [['gameDate', 'playerName', 'gameTeamName', 'completeGamesPitching', \n    'inningsPitched', 'hitsPitching', 'noHitter', 'pitchingGameScore']].\n  sort_values(['gameDate'], ascending = False, ignore_index = True)\n  )\n\nprint('Individual No-Hitters in Span of Data')\ndisplay(player_game_no_hitters)\n# Can check vs MLB official list: https://www.mlb.com/news/no-hitter-c265779246","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:16:53.952048Z","iopub.execute_input":"2021-06-10T09:16:53.95238Z","iopub.status.idle":"2021-06-10T09:16:54.48859Z","shell.execute_reply.started":"2021-06-10T09:16:53.952337Z","shell.execute_reply":"2021-06-10T09:16:54.487558Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Aggregate player game-level stats to daily date (accounts for multiple games per day)","metadata":{}},{"cell_type":"code","source":"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 all player-dates, taking min to make sure\n      gameTeamId = ('gameTeamId', 'min'),\n      gameTeamName = ('gameTeamName', '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    [[# Stats as hitter/baserunner\n      'gamesPlayedBatting', 'runsScored', 'doubles', 'triples', 'homeRuns',\n      'strikeOuts', 'baseOnBalls', 'hits', 'hitByPitch', 'atBats',\n      'caughtStealing', 'stolenBases', 'groundIntoDoublePlay',\n      'groundIntoTriplePlay', 'plateAppearances', 'totalBases', 'rbi',\n      # Stats as pitcher\n      'gamesPlayedPitching', 'gamesStartedPitching', 'completeGamesPitching',\n      'shutoutsPitching', 'winsPitching', 'lossesPitching', 'runsPitching',\n      'homeRunsPitching', 'strikeOutsPitching', 'baseOnBallsPitching',\n      'hitsPitching', 'earnedRuns', 'battersFaced', 'outsPitching',\n      'pitchesThrown', 'balls', 'strikes', 'saves', 'holds', 'blownSaves',\n      'inningsPitchedAsFrac', 'pitchingGameScore', 'noHitter',\n      # Stats as fielder (quite basic)\n      'assists', 'putOuts', 'errors'  \n      ]].\n    sum()\n    ),\n  on = ['dailyDataDate', 'playerId'],\n  how = 'inner'\n  )\n\ndisplay(player_date_stats_agg)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:17:31.39981Z","iopub.execute_input":"2021-06-10T09:17:31.400139Z","iopub.status.idle":"2021-06-10T09:17:46.811057Z","shell.execute_reply.started":"2021-06-10T09:17:31.400106Z","shell.execute_reply":"2021-06-10T09:17:46.810172Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Notable In-Game Events","metadata":{}},{"cell_type":"markdown","source":"#### Add some event-based stats that look at context on each play","metadata":{}},{"cell_type":"code","source":"# Merge games w/ events to get scheduled length of game (helps w/ some calculations)\nevents_plus = pd.merge(\n  events,\n  games[['gamePk', 'scheduledInnings']].drop_duplicates(),\n  on = ['gamePk'],\n  how = 'left'\n  )\n\n# Get current score from batting & pitching team perspectives\nevents_plus['battingTeamScore'] = np.where(events_plus['halfInning'] == 'bottom',\n  events_plus['homeScore'], events_plus['awayScore'])\n\nevents_plus['pitchingTeamScore'] = np.where(events_plus['halfInning'] == 'bottom',\n  events_plus['awayScore'], events_plus['homeScore'])\n\nevents_plus['pitches100mph'] = np.where(\n  (events_plus['type'] == 'pitch') & (events_plus['startSpeed'] >= 100), \n  1, 0)\n\nevents_plus['HRDist450ft'] = np.where(\n  (events_plus['event'] == 'Home Run') & (events_plus['totalDistance'] >= 450), \n  1, 0)\n\n# Use game context/score logic to add fields for notable in-game events\nevents_plus['gameTyingRBI'] = np.where(\n  (events_plus['isPaOver'] == 1) & (events_plus['rbi'] > 0) &\n  # Start w/ batting team behind in score...\n  (events_plus['battingTeamScore'] < events_plus['pitchingTeamScore']) & \n  # ...and look at cases where adding RBI ties score\n  ((events_plus['battingTeamScore'] + events_plus['rbi']) == \n    events_plus['pitchingTeamScore']\n    ),\n  1, 0)\n\nevents_plus['goAheadRBI'] = np.where(\n  (events_plus['isPaOver'] == 1) & (events_plus['rbi'] > 0) &\n  # Start w/ batting team not ahead in score (can be tied)...\n  (events_plus['battingTeamScore'] <= events_plus['pitchingTeamScore']) &\n  # ... and look at cases where adding RBI puts batting team ahead\n  ((events_plus['battingTeamScore'] + events_plus['rbi']) >\n    events_plus['pitchingTeamScore']\n    ),\n  1, 0)\n\n# Add field to count walk-off (game-winning, game-ending) RBI\nevents_plus['walkoffRBI'] = np.where(\n  (events_plus['inning'] >= events_plus['scheduledInnings']) & \n  (events_plus['halfInning'] == 'bottom') &\n  (events_plus['goAheadRBI'] == 1),\n  1, 0)\n\nadded_events_fields = ['pitches100mph', 'HRDist450ft', 'gameTyingRBI',\n  'goAheadRBI', 'walkoffRBI']\n\n# Count overall frequency of added events\nevent_counts = events_plus[added_events_fields].sum()\n\ndisplay(event_counts)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:18:34.729662Z","iopub.execute_input":"2021-06-10T09:18:34.729978Z","iopub.status.idle":"2021-06-10T09:18:44.080359Z","shell.execute_reply.started":"2021-06-10T09:18:34.729948Z","shell.execute_reply":"2021-06-10T09:18:44.079175Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Aggregate player event-based stats to player-date level","metadata":{}},{"cell_type":"code","source":"pitcher_date_events_agg = (events_plus.\n  groupby(['dailyDataDate', 'pitcherId'], as_index = False).\n  agg(\n    pitches100mph = ('pitches100mph', 'sum'),\n    walkoffRBIAllowed = ('walkoffRBI', 'sum')  \n    )  \n  )\n\nhitter_date_events_agg = (events_plus.\n  groupby(['dailyDataDate', 'hitterId'], as_index = False)\n  [[field for field in added_events_fields if field != 'pitches100mph']].\n  sum()\n  )\n\nplayer_date_events_agg = (pd.merge(\n  pitcher_date_events_agg.rename(columns = {'pitcherId': 'playerId'}),\n  hitter_date_events_agg.rename(columns = {'hitterId': 'playerId'}),\n  on = ['dailyDataDate', 'playerId'],\n  how = 'outer'\n  ). \n  # NAs on events fields can be turned to 0 (no such stats in those categories)\n  fillna({field: 0 for field in added_events_fields + ['walkoffRBIAllowed']})\n  )\n\ndisplay(player_date_events_agg)","metadata":{"execution":{"iopub.status.busy":"2021-06-10T09:19:08.087099Z","iopub.execute_input":"2021-06-10T09:19:08.087609Z","iopub.status.idle":"2021-06-10T09:19:08.525182Z","shell.execute_reply.started":"2021-06-10T09:19:08.087578Z","shell.execute_reply":"2021-06-10T09:19:08.524398Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Merge date-level player game stats w/ date-level player stats from events","metadata":{}},{"cell_type":"code","source":"player_date_stats_events_agg = (pd.merge(\n  player_date_stats_agg,\n  player_date_events_agg,\n  on = ['dailyDataDate', 'playerId'],\n  how = 'left'\n  ). \n  # set event fields NAs to 0 (assumed since player has game stats but not these)\n  fillna({field: 0 for field in added_events_fields + ['walkoffRBIAllowed']})\n  )\n\ndisplay(player_date_stats_events_agg)","metadata":{"execution":{"iopub.status.busy":"2021-06-10T09:20:14.072725Z","iopub.execute_input":"2021-06-10T09:20:14.07307Z","iopub.status.idle":"2021-06-10T09:20:14.312849Z","shell.execute_reply.started":"2021-06-10T09:20:14.073037Z","shell.execute_reply":"2021-06-10T09:20:14.31188Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#### Delete some tables no longer needed past this point to clear up memory \n\ndel(events, events_plus, player_date_stats_agg, pitcher_date_events_agg,\n  hitter_date_events_agg, player_date_events_agg)\n\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2021-06-10T09:20:25.58333Z","iopub.execute_input":"2021-06-10T09:20:25.583682Z","iopub.status.idle":"2021-06-10T09:20:28.264766Z","shell.execute_reply.started":"2021-06-10T09:20:25.583651Z","shell.execute_reply":"2021-06-10T09:20:28.2634Z"},"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Transactions","metadata":{}},{"cell_type":"markdown","source":"#### Look at different transaction type values and frequency in data","metadata":{}},{"cell_type":"code","source":"transaction_type_values = (transactions.\n  groupby(['typeCode', 'typeDesc'], as_index = False).\n  agg(\n    numTransactions = ('date', 'count')\n    ).\n  sort_values(['numTransactions'], ascending = False,\n    ignore_index = True)\n  )\n\ndisplay(transaction_type_values)","metadata":{"execution":{"iopub.status.busy":"2021-06-10T09:20:50.378766Z","iopub.execute_input":"2021-06-10T09:20:50.37913Z","iopub.status.idle":"2021-06-10T09:20:50.452627Z","shell.execute_reply.started":"2021-06-10T09:20:50.379102Z","shell.execute_reply":"2021-06-10T09:20:50.451631Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Create data frame on player-date level, w/ 1 column per transaction type\n","metadata":{}},{"cell_type":"code","source":"# Pick certain transaction codes of interest from above list\ntransaction_types_of_interest = ['Assigned', 'Signed as Free Agent', \n  'Status Change', 'Optioned', 'Recalled', 'Signed', 'Selected',\n  'Trade', 'Designated for Assignment']\n\nplayer_date_transactions_wide = (transactions.\n  assign(\n    # Create field w/ initial lower case & w/o spaces for later field names\n    typeDescNoSpace = [(typeDesc[0].lower() + typeDesc[1:]) for typeDesc in\n      transactions['typeDesc'].str.replace(' ', '')],\n    # Add count ahead of pivot\n    count = 1\n    )\n  [\n  # Filter to transactions of desired types and rows for actual players\n    np.isin(transactions['typeDesc'], transaction_types_of_interest) &\n    pd.notna(transactions['playerId'])\n  ][['dailyDataDate', 'playerId', 'typeDescNoSpace', 'count']].\n  # Filter to unique transaction types across player-date\n  drop_duplicates().\n  # Pivot data to 1 row per player-date and 1 column per transaction type\n  pivot_table(\n    index = ['dailyDataDate', 'playerId'],\n    columns = 'typeDescNoSpace',\n    values = 'count',\n    # NA can be turned to 0 since it means player didn't have that transaction that day\n    fill_value = 0\n    ).\n  reset_index()\n  )\n\ndisplay(player_date_transactions_wide)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:21:43.160509Z","iopub.execute_input":"2021-06-10T09:21:43.160855Z","iopub.status.idle":"2021-06-10T09:21:43.428221Z","shell.execute_reply.started":"2021-06-10T09:21:43.160824Z","shell.execute_reply":"2021-06-10T09:21:43.427297Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Team Standings","metadata":{}},{"cell_type":"markdown","source":"#### Prepare team standings table for merge w/ player digital engagement data\n\nIf 1st output below has 0 rows, can proceed without worrying about duplicate team-date standings rows.","metadata":{}},{"cell_type":"code","source":"# Check for multiple entries for team standings on same date\nteam_dates_multiple_standings_entries = (standings.\n  groupby(['dailyDataDate', 'teamId'], as_index = False).\n  agg(\n    numTeamDateStandingsEntries = ('teamId', 'count')\n    ).\n  query(\"numTeamDateStandingsEntries > 1\")\n  )\n\n# If following returns 0 rows, can join to other daily data w/o worrying about duplicates \ndisplay(team_dates_multiple_standings_entries)\n\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\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  ]\n\nstandings_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\nstandings_selected_fields['winStreakTeam'] = np.where(\n  standings_selected_fields['streakCodeTeam'].str[0] == 'W',\n  standings_selected_fields['streakLengthTeam'],\n  np.nan\n  )\n\nstandings_selected_fields['lossStreakTeam'] = np.where(\n  standings_selected_fields['streakCodeTeam'].str[0] == 'L',\n  standings_selected_fields['streakLengthTeam'],\n  np.nan\n  )\n\n# Drop streak fields no longer necessary w/ derived values\nstandings_selected_fields.drop(\n  ['streakCodeTeam', 'streakLengthTeam'], \n  axis = 1, \n  inplace = True\n  )\n\ndisplay(standings_selected_fields)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:25:04.026399Z","iopub.execute_input":"2021-06-10T09:25:04.026758Z","iopub.status.idle":"2021-06-10T09:25:04.144235Z","shell.execute_reply.started":"2021-06-10T09:25:04.026723Z","shell.execute_reply":"2021-06-10T09:25:04.143482Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Awards","metadata":{}},{"cell_type":"markdown","source":"#### Look at various awards/honors received by players in daily data & before","metadata":{}},{"cell_type":"code","source":"player_awards_all = (pd.concat([\n  # Filter awards from daily df to only those involving tracked players\n  awards[np.isin(awards['playerId'], players['playerId'])],\n  # Add daily data date to pre-2018 awards_df\n  awards_pre2018.assign(\n    dailyDataDate = pd.to_datetime(awards_pre2018['awardDate'], \n      format = '%Y-%m-%d')\n    )], \n    ignore_index = True\n    ).\n  sort_values(['awardDate'], ascending = False, ignore_index = True)\n  )\n\ndisplay(player_awards_all)\n\nawards_summary = (player_awards_all.\n  groupby(['awardId', 'awardName'], as_index = False).\n  agg(\n    numWinnersInThisPlayerSet = ('playerId', 'count'),\n    mostRecAwardDate = ('awardDate', 'max'),\n    mostRecAwardSeason = ('awardSeason', 'max')\n    ). \n  sort_values(['mostRecAwardDate', 'awardId'],\n    ascending = [False, True], ignore_index = True)\n  )\n\ndisplay(awards_summary)","metadata":{"execution":{"iopub.status.busy":"2021-06-10T09:25:43.91516Z","iopub.execute_input":"2021-06-10T09:25:43.915499Z","iopub.status.idle":"2021-06-10T09:25:44.039651Z","shell.execute_reply.started":"2021-06-10T09:25:43.915469Z","shell.execute_reply":"2021-06-10T09:25:44.038902Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Limit down to certain award categories, get players' running tallies by date","metadata":{}},{"cell_type":"code","source":"selected_awards = pd.DataFrame(data = {\n  'awardId':  ['ALAS', 'NLAS', 'ALMVP', 'NLMVP', 'ALCY', 'NLCY'],\n  'awardCategory': ['AllStar', 'AllStar', 'MVP', 'MVP', 'CyYoung', 'CyYoung']\n  })\n\nplayer_selected_awards = pd.merge(\n  player_awards_all,\n  selected_awards,\n  on = 'awardId',\n  # Inner join to limit player awards to only selected ones\n  how = 'inner'\n  )\n\nselected_award_categories_in_data = (player_selected_awards['awardCategory'].\n  unique())\n\nplayer_selected_awards_by_date = (player_selected_awards.\n  # Add count for use when pivoting\n  assign(count = 1).\n  pivot_table(\n    index = ['dailyDataDate', 'playerId', 'playerName'],\n    columns = 'awardCategory',\n    values = 'count',\n    # NA can be turned to 0 since it means player didn't get that award that day\n    fill_value = 0\n    ).\n  reset_index()\n  )\n\n# Add cumulative 'to date' sums for each award category\nfor award_category in selected_award_categories_in_data:\n    player_selected_awards_by_date[('toDate' + award_category + 's')] = (\n      player_selected_awards_by_date.\n        groupby(['playerId', 'playerName'])[award_category].cumsum()\n      )\n\n# Prepare for time-based merging by dropping non-\"to date\" fields\nplayer_selected_awards_by_date.drop(selected_award_categories_in_data,\n  axis = 1, inplace = True)\n\ndisplay(player_selected_awards_by_date)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:26:31.316909Z","iopub.execute_input":"2021-06-10T09:26:31.317457Z","iopub.status.idle":"2021-06-10T09:26:31.365995Z","shell.execute_reply.started":"2021-06-10T09:26:31.317406Z","shell.execute_reply":"2021-06-10T09:26:31.365053Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Player and Team Twitter Followers by Date","metadata":{}},{"cell_type":"markdown","source":"#### Prepare player & team Twitter followers data for merge w/ player daily engagement data","metadata":{}},{"cell_type":"code","source":"# Extract only desired fields, rename some fields (for clarity later)\nplayer_twitter_followers_for_merge = (playerTwitterFollowers\n  [['dailyDataDate', 'date', 'playerId', 'numberOfFollowers']].\n  rename(columns = {\n    'date': 'playerTwitterDataDate',\n    'numberOfFollowers': 'playerTwitterFollowers'\n    })\n  )\n\n# Extract only desired fields, rename some fields (for clarity/joining later)\nteam_twitter_followers_for_merge = (teamTwitterFollowers\n  [['dailyDataDate', 'date', 'teamId', 'numberOfFollowers']].\n  rename(columns = {\n    'date': 'teamTwitterDataDate',\n    # Name is weird, but helps set up for merge w/ digital engagement data\n    'teamId': 'rosterTeamIdIntForMerge',\n    'numberOfFollowers': 'teamTwitterFollowers'\n    })\n  )\n\ndisplay(player_twitter_followers_for_merge)\n\ndisplay(team_twitter_followers_for_merge)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:28:04.570106Z","iopub.execute_input":"2021-06-10T09:28:04.570645Z","iopub.status.idle":"2021-06-10T09:28:05.037664Z","shell.execute_reply.started":"2021-06-10T09:28:04.570598Z","shell.execute_reply":"2021-06-10T09:28:05.036758Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Merge in Other Data with Player Daily Engagement","metadata":{}},{"cell_type":"markdown","source":"#### Merge in other data frames by date to add various (player/team/etc.) info to daily engagement","metadata":{}},{"cell_type":"code","source":"# Merge in daily player engagement with date info, then filter to in-season dates only\n# Since test and future eval period is all 'in season', we imagine that looking at\n# this filtered set of dates will help w/ identifying relevant trends more easily\nplayer_engagement_with_info = (pd.merge(\n  nextDayPlayerEngagement,\n  dates_with_season_part,\n  on = ['dailyDataDate'],\n  how = 'left'\n  ).\n  query(\"inSeason\").\n  reset_index(drop = True)\n  )\n\n# Take \"row mean\" across targets to add (helps with studying all 4 targets at once)\nplayer_engagement_with_info['target1To4Avg'] = np.mean(\n  player_engagement_with_info[['target1', 'target2', 'target3', 'target4']],\n  axis = 1)\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',\n    'birthStateProvince', 'birthCountry', 'primaryPositionName']],\n   on = ['playerId'],\n   how = 'left'\n   )\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 = {\n      'statusCode': 'rosterStatusCode',\n      'status': 'rosterStatus',\n      'teamId': 'rosterTeamId'\n      })\n    ),\n  on = ['dailyDataDate', 'playerId'],\n  how = 'left'\n  )\n\n# Add int version of rosterTeamId (w/ -1 for NA) to help w/ future merging\nplayer_engagement_with_info['rosterTeamIdIntForMerge'] = (np.where(\n  pd.isna(player_engagement_with_info['rosterTeamId']), -1,\n  player_engagement_with_info['rosterTeamId']).\n  astype('int64')\n  )\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 = {\n      'id': 'rosterTeamId',\n      'teamName': 'rosterTeamName'\n      })\n    ),\n  on = ['rosterTeamId'],\n  how = 'left'\n  )\n\n# Merge in some player game stats and events (previously aggregated) from that date\nplayer_engagement_with_info = pd.merge(\n  player_engagement_with_info,\n  player_date_stats_events_agg,\n  on = ['dailyDataDate', 'playerId'],\n  how = 'left'\n  )\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\n# Get list of transactions fields to be added (and fill in NAs for post-merge)\ntransactions_fields = (player_date_transactions_wide.\n  drop(['dailyDataDate', 'playerId'] , axis = 1).\n  columns.values.tolist())\n\n# Merge in player transactions of note (previously created) on that date \nplayer_engagement_with_info = (pd.merge(\n  player_engagement_with_info,\n  player_date_transactions_wide,\n  on = ['dailyDataDate', 'playerId'],\n  how = 'left'\n  ).\n  # NAs on transactions fields can be turned to 0 (no player transaction that day)\n  fillna({field: 0 for field in transactions_fields})\n  )\n\n# Merge in some pieces of team standings (previously created) from that date\nplayer_engagement_with_info = pd.merge(\n  player_engagement_with_info,\n  # Join standings based on rosterTeamId (not gameTeamId)\n  standings_selected_fields.rename(columns = {'teamId': 'rosterTeamId'}),\n  on = ['dailyDataDate', 'rosterTeamId'],\n  how = 'left'\n  )\n\n# Get list of awards fields to be added (and fill in NAs for post-merge)\nawards_fields = (player_selected_awards_by_date.\n  drop(['dailyDataDate', 'playerId', 'playerName'], axis = 1).\n  columns.values.tolist())\n\n# Merge in selected player awards received from latest award date before given date\nplayer_engagement_with_info = (pd.merge_asof(\n  player_engagement_with_info,\n  player_selected_awards_by_date.drop(['playerName'], axis = 1),\n  # \"merge\" on date by player, looking backward (only use award dates up to daily date)\n  on = ['dailyDataDate'],\n  by = ['playerId'],\n  direction = 'backward'\n  ).\n  # NAs on awards fields can be turned to 0 (player had no awards of that type to date)\n  fillna({field: 0 for field in awards_fields})\n  )\n\n# Merge in player's Twitter followers from latest tracked date before given date\nplayer_engagement_with_info = pd.merge_asof(\n  player_engagement_with_info,\n  player_twitter_followers_for_merge,\n  # \"merge\" on date by player, looking backward (only use Twitter dates up to daily date)\n  on = ['dailyDataDate'],\n  by = ['playerId'],\n  direction = 'backward'\n  )\n\n# Merge in team Twitter followers from latest date before given date\nplayer_engagement_with_info = pd.merge_asof(\n  player_engagement_with_info,\n  team_twitter_followers_for_merge,\n  # \"merge\" on date by team, looking backward (only use Twitter dates up to daily date)\n  on = ['dailyDataDate'],\n  # Use integer version of rosterTeamId since merge_asof seems to need int (not float)\n  by = ['rosterTeamIdIntForMerge'],\n  direction = 'backward'\n  )\n\n# Drop integer version of rosterTeamId since merging is done\nplayer_engagement_with_info.drop(['rosterTeamIdIntForMerge'], axis = 1)\n\ndisplay(player_engagement_with_info)","metadata":{"execution":{"iopub.status.busy":"2021-06-10T09:31:49.569365Z","iopub.execute_input":"2021-06-10T09:31:49.569691Z","iopub.status.idle":"2021-06-10T09:32:09.133686Z","shell.execute_reply.started":"2021-06-10T09:31:49.569663Z","shell.execute_reply":"2021-06-10T09:32:09.132707Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Look at listing of all fields in merged player digital engagement data","metadata":{}},{"cell_type":"code","source":"display(player_engagement_with_info.info(max_cols = 200))","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:32:24.827599Z","iopub.execute_input":"2021-06-10T09:32:24.827989Z","iopub.status.idle":"2021-06-10T09:32:25.85053Z","shell.execute_reply.started":"2021-06-10T09:32:24.827956Z","shell.execute_reply":"2021-06-10T09:32:25.849556Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Look at Relationships Between Various Factors and Target Variables","metadata":{}},{"cell_type":"code","source":"#### Functions to help with calculating & display grouped target summaries\n\ndef GetGroupedTargetValuesSummary(df_with_group_and_targets, \n  grouping_vars_list):\n    \n    # Group target values by specified variables\n    group_target_values_summary = (df_with_group_and_targets.\n      groupby(grouping_vars_list, as_index = False, dropna = False).\n      agg(\n        numPlayerDates = ('playerId', 'count'),\n        numPlayers = ('playerId', 'nunique'),\n        target1 = ('target1', np.mean),\n        target2 = ('target2', np.mean),\n        target3 = ('target3', np.mean),\n        target4 =  ('target4', np.mean),\n        target1To4Avg = ('target1To4Avg', np.mean)\n        ).\n      sort_values(['target1To4Avg'], ascending = False,\n        ignore_index = True)\n      )\n    return(group_target_values_summary)\n;\n\ndef CalcAndDisplayMultipleSetsOfGroupedTargetValues(\n    df_with_group_and_targets, grouping_names_and_vars_sets_df):\n\n    num_group_sets = grouping_names_and_vars_sets_df.shape[0]\n    \n    kaggle_data_tabs = widgets.Tab()\n\n    # Add Output widgets for each group as tabs' children\n    kaggle_data_tabs.children = list([widgets.Output() \n      for group_set in range(0, num_group_sets)])\n\n    # Loop over each group (name/vars combo), create tab, calc/display summary\n    for index, row in grouping_names_and_vars_sets_df.iterrows():\n    \n        this_group_name = row['groupName']\n        this_group_vars_list = row['groupVarsList']\n\n        # Group target values by specified variables\n        group_target_values_summary = GetGroupedTargetValuesSummary(\n          df_with_group_and_targets, this_group_vars_list\n          )\n\n        # Rename tab bar titles to string concatenating grouping vars\n        kaggle_data_tabs.set_title(index, this_group_name)\n\n        # Display corresponding table output for this tab name\n        with kaggle_data_tabs.children[index]:\n            print('Average Daily Digital Engagement by ' + this_group_name)\n            \n            print('Average Target Values Grouped By ' + \n              str(this_group_vars_list))\n\n            display(group_target_values_summary.round(decimals = 1))\n    \n    display(kaggle_data_tabs)\n;","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:33:04.879624Z","iopub.execute_input":"2021-06-10T09:33:04.879957Z","iopub.status.idle":"2021-06-10T09:33:04.896773Z","shell.execute_reply.started":"2021-06-10T09:33:04.879928Z","shell.execute_reply":"2021-06-10T09:33:04.895831Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Get target averages for different player-related factors (team/roster/transactions)","metadata":{}},{"cell_type":"code","source":"player_related_grouping_names_and_var_sets = pd.DataFrame(data = {\n  'groupName': [\n    'Player',\n    'Player Team (by Roster)',\n    'Player Roster Status',\n    'Player Selected Transactions This Day',\n    'Player Selected Awards to Date'\n    ],\n  'groupVarsList': pd.Series([\n    ['playerId', 'playerName'], \n    ['rosterTeamName'],\n    ['rosterStatusCode', 'rosterStatus'], \n    ['trade', 'signedasFreeAgent', 'recalled'],\n    ['toDateMVPs', 'toDateCyYoungs']\n    ])\n })    \n\nCalcAndDisplayMultipleSetsOfGroupedTargetValues(\n  player_engagement_with_info, \n  player_related_grouping_names_and_var_sets\n  )","metadata":{"execution":{"iopub.status.busy":"2021-06-10T09:33:41.727264Z","iopub.execute_input":"2021-06-10T09:33:41.72782Z","iopub.status.idle":"2021-06-10T09:33:43.834971Z","shell.execute_reply.started":"2021-06-10T09:33:41.727786Z","shell.execute_reply":"2021-06-10T09:33:43.834277Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Some notes from the results above:\n* Well-known stars like Mike Trout, Aaron Judge, Bryce Harper, and Mookie Betts stand out with digital engagement averages of 17-25, much higher than the overall average across all players.\n\n* Players on some traditionally popular teams - Yankees, Dodgers, Red Sox - have had higher digital engagement averages, with some of the recently less successful teams with smaller fan bases - Pirates, Royals, Marlins - having lower digital engagement averages.\n\n* Among roster status categories with significant sample size, 'active' players have the highest average digital engagement (as expected). Below them are players on the 10-day IL, players on the 60-day IL, and those reassigned to the minors (in that order).\n\n* On average, traded players have much higher digital engagement right after the trade then players who are signed as free agents (this is only in-season data, so free agency isn't as big), and both have higher averages than recalled players.\n\n* Players with previous MVPs and Cy Young Awards tend to have much higher average digital engagement. It's hard to read into the value of having won those awards multiple times since there are only a few players who fit that category, but these \"top-level\" stars have much higher engagement than their peers in general.","metadata":{}},{"cell_type":"markdown","source":"#### Get target averages for different player game info-related variables","metadata":{}},{"cell_type":"code","source":"player_game_related_grouping_names_and_var_sets = pd.DataFrame(data = {\n  'groupName': [\n    'Game Type',\n    'Player Team in Game',\n    'Player\\'s Game Opponent',\n    'Player\\'s Team Games, W-L',\n    ],\n  'groupVarsList': pd.Series([\n    ['gameType'],\n    ['gameTeamName'],\n    ['oppName'],\n    ['numGamesTeam', 'winsTeam', 'lossesTeam']\n    ])\n })    \n\nCalcAndDisplayMultipleSetsOfGroupedTargetValues(\n  player_engagement_with_info, \n  player_game_related_grouping_names_and_var_sets\n  )","metadata":{"execution":{"iopub.status.busy":"2021-06-10T09:35:19.876549Z","iopub.execute_input":"2021-06-10T09:35:19.877013Z","iopub.status.idle":"2021-06-10T09:35:21.392239Z","shell.execute_reply.started":"2021-06-10T09:35:19.876982Z","shell.execute_reply":"2021-06-10T09:35:21.391297Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Some notes from the results above:\n* Players in the All-Star Game (gameType = 'A') have had pretty high digital engagement on the day after that game. Outside of that, average player digital engagement (relative to peers) increases for each subsequent round of the postseason players are around for - the highest being the World Series (gameType = 'W') and the lowest being the regular season (gameType = 'R').\n\n* Outside of the All-Star teams, players playing in games (not just on the roster) for the Yankees, Dodgers, and Cubs have the highest digital engagement averages, while those playing for the Diamondbacks, Royals, and Marlins rate lowest on average (but above those players not playing in games). This table resembles that of the average digital engagement by players' roster team (above), but also has some key differences.\n\n* The average digital engagement by game opponent list looks a good bit different, and has a bunch of teams clustered by division (e.g. all AL East teams up top, all AL Central teams at the bottom). This suggests that this could just be a byproduct of popularity of the teams that play those opponents more, since MLB teams play division opponents much more often than others.\n\n* The effect isn't huge, but players whose teams won the day before tend to have higher digital engagement averages than those on losing teams.","metadata":{}},{"cell_type":"markdown","source":"#### Get target averages for different player game batting-related stats","metadata":{}},{"cell_type":"code","source":"player_batting_related_grouping_names_and_var_sets = pd.DataFrame(data = {\n  'groupName': [\n    'Player Games as Batter',\n    'Player Hits in Game',\n    'Player HR & Long HR in Game',\n    'Player Go-Ahead & Walkoff RBI in Game'\n    ],\n  'groupVarsList': pd.Series([\n    ['gamesPlayedBatting'],\n    ['hits'],\n    ['homeRuns', 'HRDist450ft'],\n    ['goAheadRBI', 'walkoffRBI']\n    ])\n  })    \n\nCalcAndDisplayMultipleSetsOfGroupedTargetValues(\n  player_engagement_with_info, \n  player_batting_related_grouping_names_and_var_sets\n  )","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:38:13.948796Z","iopub.execute_input":"2021-06-10T09:38:13.949226Z","iopub.status.idle":"2021-06-10T09:38:15.715568Z","shell.execute_reply.started":"2021-06-10T09:38:13.949192Z","shell.execute_reply":"2021-06-10T09:38:15.714566Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Some notes from the results above:\n* As expected, position players who actually play have higher digital engagement than those that don't play, with some additional bump for playing both games of a doubleheader (on average).\n\n* Average digital engagement is higher for players with each increasing number of hits on a date, up to 6 hits (keep in mind that doubleheaders mean this could be across 2 games).\n\n* Players who hit HR have higher average digital engagement, with multi-HR games even higher and the rare 3-HR game leading to very high averages. In a small sample, there seems to be some additional value to hitting long HR (defined here as those traveling 450+ ft).\n\n* Players have quite high digital engagement after recording a walkoff RBI, with some smaller (but still substantial) boost from having any type of go-ahead RBI in a game.","metadata":{}},{"cell_type":"markdown","source":"#### Get target averages for different player game pitching-related stats","metadata":{}},{"cell_type":"code","source":"player_pitching_related_grouping_names_and_var_sets = pd.DataFrame(data = {\n  'groupName': [\n    'Pitching Games Started & W-L',\n    'Pitcher Shutout in Game',\n    'Pitcher Complete Game & No-Hitter'\n    ],\n  'groupVarsList': pd.Series([\n    ['gamesStartedPitching', 'winsPitching', 'lossesPitching'],\n    ['shutoutsPitching'],\n    ['completeGamesPitching', 'noHitter'] \n    ])\n   })    \n\nCalcAndDisplayMultipleSetsOfGroupedTargetValues(\n  player_engagement_with_info, \n  player_pitching_related_grouping_names_and_var_sets\n  )","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T09:39:08.852655Z","iopub.execute_input":"2021-06-10T09:39:08.852975Z","iopub.status.idle":"2021-06-10T09:39:09.977425Z","shell.execute_reply.started":"2021-06-10T09:39:08.852949Z","shell.execute_reply":"2021-06-10T09:39:09.976515Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Some notes from the results above:\n* Pitchers who start tend to have higher engagement than other players, with more than double the digital engagement (on average) if they record a win (vs a loss or no-decision).\n\n* The few pitchers who have thrown shutouts in this span have averaged very high digital engagement numbers the next day.\n\n* The 8 individual no-hitters in this span have resulted in player digital engagement numbers close to the max - an average of ~85 across all targets (on a 0-100 scale)! Other complete games (still pretty rare) having a pretty high average as well, near 15.","metadata":{}},{"cell_type":"markdown","source":"#### Look at correlation coefficients of some continuous variables w/ target metrics","metadata":{}},{"cell_type":"code","source":"continuous_predictors = [\n  'pitchesThrown', 'inningsPitchedAsFrac', 'strikeOutsPitching',\n  'pitchingGameScore', 'pitches100mph',\n  'plateAppearances', 'hits', 'totalBases', \n  'winPctTeam', 'leagueRankTeam', 'winStreakTeam', 'lossStreakTeam',\n  'toDateAllStars', 'playerTwitterFollowers', 'teamTwitterFollowers'\n  ]\n\ntarget_vars = ['target1', 'target2', 'target3', 'target4', 'target1To4Avg']\n\ncontinuous_predictors_targets_correlations = (player_engagement_with_info[\n  continuous_predictors + target_vars].\n  corr().\n  loc[continuous_predictors, target_vars]\n  )\n\ndisplay(continuous_predictors_targets_correlations.round(decimals = 2))","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-10T10:38:20.205408Z","iopub.execute_input":"2021-06-10T10:38:20.205835Z","iopub.status.idle":"2021-06-10T10:38:21.64245Z","shell.execute_reply.started":"2021-06-10T10:38:20.205802Z","shell.execute_reply":"2021-06-10T10:38:21.641248Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Some notes from the results above:\n* Many of these stats/metrics, particularly the few pitching ones chosen here, have pretty low pairwise correlations with digital engagement. Keep in mind, this doesn't mean that these measurements have no predictive value. It could be that the relationship isn't linear or only exists at the extremes of a given metric (e.g. maybe having 10+ strikeouts as a pitcher means more toward digital engagement than any differences among single-digit numbers of strikeouts).\n\n* There is a weak-to-moderate positive correlation between some basic hitting statistics that can take on a range of values on a day (plate appearances, hits, total bases) and digital engagement.\n\n* There are some relatively weak correlations with team performance. Players on teams with higher win percentage or better league rank (lower is better) tend to have higher digital engagement, but the magnitude of the correlation is quite small.\n\n* Players with more previous All-Star appearances and Twitter followers, and those on teams with higher followings on Twitter, tend to have higher average digital engagement. These are some of the stronger correlations among the factors considered here.","metadata":{}},{"cell_type":"markdown","source":"#### The above was a very rudimentary exploratory analysis of how various single factors relate to digital engagement individually, just to provide a baseline of how these relationships might be explored. There is a lot to be analyzed further about which of these factors might matter more/less when used together to predict digital engagement, as well as the causal mechanisms by which these factors affect the targets.","metadata":{}}]}