{"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","_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-07-01T23:42:45.672370Z","iopub.execute_input":"2021-07-01T23:42:45.673074Z","iopub.status.idle":"2021-07-01T23:42:45.868403Z","shell.execute_reply.started":"2021-07-01T23:42:45.673033Z","shell.execute_reply":"2021-07-01T23:42:45.867425Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-07-01T23:42:45.870246Z","iopub.execute_input":"2021-07-01T23:42:45.870872Z","iopub.status.idle":"2021-07-01T23:42:46.927309Z","shell.execute_reply.started":"2021-07-01T23:42:45.870828Z","shell.execute_reply":"2021-07-01T23:42:46.926320Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-07-01T23:42:46.929252Z","iopub.execute_input":"2021-07-01T23:42:46.929588Z","iopub.status.idle":"2021-07-01T23:43:49.966510Z","shell.execute_reply.started":"2021-07-01T23:42:46.929555Z","shell.execute_reply":"2021-07-01T23:43:49.965597Z"},"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-07-01T23:43:49.967921Z","iopub.execute_input":"2021-07-01T23:43:49.968392Z","iopub.status.idle":"2021-07-01T23:48:56.772746Z","shell.execute_reply.started":"2021-07-01T23:43:49.968361Z","shell.execute_reply":"2021-07-01T23:48:56.771526Z"},"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":{"iopub.status.busy":"2021-07-01T23:48:56.774240Z","iopub.execute_input":"2021-07-01T23:48:56.774588Z","iopub.status.idle":"2021-07-01T23:48:56.932259Z","shell.execute_reply.started":"2021-07-01T23:48:56.774554Z","shell.execute_reply":"2021-07-01T23:48:56.931174Z"},"trusted":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-07-01T23:48:56.934120Z","iopub.execute_input":"2021-07-01T23:48:56.934579Z","iopub.status.idle":"2021-07-01T23:49:00.193309Z","shell.execute_reply.started":"2021-07-01T23:48:56.934533Z","shell.execute_reply":"2021-07-01T23:49:00.192123Z"},"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-07-01T23:49:00.195041Z","iopub.execute_input":"2021-07-01T23:49:00.195550Z","iopub.status.idle":"2021-07-01T23:49:01.455270Z","shell.execute_reply.started":"2021-07-01T23:49:00.195505Z","shell.execute_reply":"2021-07-01T23:49:01.454480Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-07-01T23:49:01.457507Z","iopub.execute_input":"2021-07-01T23:49:01.457901Z","iopub.status.idle":"2021-07-01T23:49:01.657959Z","shell.execute_reply.started":"2021-07-01T23:49:01.457871Z","shell.execute_reply":"2021-07-01T23:49:01.656824Z"},"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":{"iopub.status.busy":"2021-07-01T23:49:01.659778Z","iopub.execute_input":"2021-07-01T23:49:01.660072Z","iopub.status.idle":"2021-07-01T23:49:01.835019Z","shell.execute_reply.started":"2021-07-01T23:49:01.660044Z","shell.execute_reply":"2021-07-01T23:49:01.833940Z"},"trusted":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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-07-01T23:49:01.836765Z","iopub.execute_input":"2021-07-01T23:49:01.837195Z","iopub.status.idle":"2021-07-01T23:49:01.906092Z","shell.execute_reply.started":"2021-07-01T23:49:01.837152Z","shell.execute_reply":"2021-07-01T23:49:01.905116Z"},"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-07-01T23:49:01.907618Z","iopub.execute_input":"2021-07-01T23:49:01.908042Z","iopub.status.idle":"2021-07-01T23:49:02.458875Z","shell.execute_reply.started":"2021-07-01T23:49:01.907996Z","shell.execute_reply":"2021-07-01T23:49:02.457800Z"},"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-07-01T23:49:02.460709Z","iopub.execute_input":"2021-07-01T23:49:02.461162Z","iopub.status.idle":"2021-07-01T23:49:03.012308Z","shell.execute_reply.started":"2021-07-01T23:49:02.461114Z","shell.execute_reply":"2021-07-01T23:49:03.011229Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-07-01T23:49:03.013658Z","iopub.execute_input":"2021-07-01T23:49:03.013945Z","iopub.status.idle":"2021-07-01T23:49:03.190997Z","shell.execute_reply.started":"2021-07-01T23:49:03.013918Z","shell.execute_reply":"2021-07-01T23:49:03.189906Z"},"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-07-01T23:49:03.192473Z","iopub.execute_input":"2021-07-01T23:49:03.192772Z","iopub.status.idle":"2021-07-01T23:49:03.314407Z","shell.execute_reply.started":"2021-07-01T23:49:03.192744Z","shell.execute_reply":"2021-07-01T23:49:03.313251Z"},"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-07-01T23:49:03.315862Z","iopub.execute_input":"2021-07-01T23:49:03.316158Z","iopub.status.idle":"2021-07-01T23:49:03.859116Z","shell.execute_reply.started":"2021-07-01T23:49:03.316130Z","shell.execute_reply":"2021-07-01T23:49:03.858414Z"},"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-07-01T23:49:03.860225Z","iopub.execute_input":"2021-07-01T23:49:03.860732Z","iopub.status.idle":"2021-07-01T23:49:22.153597Z","shell.execute_reply.started":"2021-07-01T23:49:03.860690Z","shell.execute_reply":"2021-07-01T23:49:22.152527Z"},"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-07-01T23:49:22.155137Z","iopub.execute_input":"2021-07-01T23:49:22.155768Z","iopub.status.idle":"2021-07-01T23:49:30.848632Z","shell.execute_reply.started":"2021-07-01T23:49:22.155720Z","shell.execute_reply":"2021-07-01T23:49:30.847550Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-07-01T23:49:30.849891Z","iopub.execute_input":"2021-07-01T23:49:30.850175Z","iopub.status.idle":"2021-07-01T23:49:31.295137Z","shell.execute_reply.started":"2021-07-01T23:49:30.850147Z","shell.execute_reply":"2021-07-01T23:49:31.294053Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-07-01T23:49:31.296415Z","iopub.execute_input":"2021-07-01T23:49:31.296687Z","iopub.status.idle":"2021-07-01T23:49:31.588098Z","shell.execute_reply.started":"2021-07-01T23:49:31.296660Z","shell.execute_reply":"2021-07-01T23:49:31.587053Z"},"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":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2021-07-01T23:49:31.589284Z","iopub.execute_input":"2021-07-01T23:49:31.589583Z","iopub.status.idle":"2021-07-01T23:49:34.996543Z","shell.execute_reply.started":"2021-07-01T23:49:31.589553Z","shell.execute_reply":"2021-07-01T23:49:34.995339Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-07-01T23:49:34.997984Z","iopub.execute_input":"2021-07-01T23:49:34.998308Z","iopub.status.idle":"2021-07-01T23:49:35.052317Z","shell.execute_reply.started":"2021-07-01T23:49:34.998265Z","shell.execute_reply":"2021-07-01T23:49:35.051196Z"},"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-07-01T23:49:35.053684Z","iopub.execute_input":"2021-07-01T23:49:35.053976Z","iopub.status.idle":"2021-07-01T23:49:35.368558Z","shell.execute_reply.started":"2021-07-01T23:49:35.053949Z","shell.execute_reply":"2021-07-01T23:49:35.367425Z"},"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-07-01T23:49:35.374261Z","iopub.execute_input":"2021-07-01T23:49:35.374620Z","iopub.status.idle":"2021-07-01T23:49:35.535905Z","shell.execute_reply.started":"2021-07-01T23:49:35.374582Z","shell.execute_reply":"2021-07-01T23:49:35.534650Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-07-01T23:49:35.537911Z","iopub.execute_input":"2021-07-01T23:49:35.538216Z","iopub.status.idle":"2021-07-01T23:49:35.684688Z","shell.execute_reply.started":"2021-07-01T23:49:35.538187Z","shell.execute_reply":"2021-07-01T23:49:35.683542Z"},"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-07-01T23:49:35.686002Z","iopub.execute_input":"2021-07-01T23:49:35.686356Z","iopub.status.idle":"2021-07-01T23:49:35.746212Z","shell.execute_reply.started":"2021-07-01T23:49:35.686323Z","shell.execute_reply":"2021-07-01T23:49:35.745279Z"},"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-07-01T23:49:35.747570Z","iopub.execute_input":"2021-07-01T23:49:35.747873Z","iopub.status.idle":"2021-07-01T23:49:35.780358Z","shell.execute_reply.started":"2021-07-01T23:49:35.747842Z","shell.execute_reply":"2021-07-01T23:49:35.779208Z"},"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":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-07-01T23:49:35.782131Z","iopub.execute_input":"2021-07-01T23:49:35.782600Z","iopub.status.idle":"2021-07-01T23:49:58.745078Z","shell.execute_reply.started":"2021-07-01T23:49:35.782554Z","shell.execute_reply":"2021-07-01T23:49:58.743819Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"player_engagement_with_info.to_csv('choncho.csv',index=False)","metadata":{"execution":{"iopub.status.busy":"2021-07-02T00:28:50.188679Z","iopub.execute_input":"2021-07-02T00:28:50.189048Z","iopub.status.idle":"2021-07-02T00:30:34.602947Z","shell.execute_reply.started":"2021-07-02T00:28:50.189017Z","shell.execute_reply":"2021-07-02T00:30:34.602084Z"},"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-07-02T00:28:22.567083Z","iopub.execute_input":"2021-07-02T00:28:22.567535Z","iopub.status.idle":"2021-07-02T00:28:22.571886Z","shell.execute_reply.started":"2021-07-02T00:28:22.567501Z","shell.execute_reply":"2021-07-02T00:28:22.570750Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}