{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# MLB Fan Engagement - Getting Started","metadata":{}},{"cell_type":"markdown","source":"In this notebook, I will import all of the original data from Kaggle's MLB fan engagement competition (https://www.kaggle.com/c/mlb-player-digital-engagement-forecasting/data) and clean it into a format that allows me to easily analyze, select features, and create models.\n\nThis notebook should serve as a guide for getting started on this competition by organizing these large amounts of data.  This notebook will also be well-documented so that I can explain the information that all of the data conveys.","metadata":{}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:39:04.009020Z","iopub.execute_input":"2021-06-20T01:39:04.009471Z","iopub.status.idle":"2021-06-20T01:39:04.923094Z","shell.execute_reply.started":"2021-06-20T01:39:04.009383Z","shell.execute_reply":"2021-06-20T01:39:04.922084Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Importing the Data","metadata":{}},{"cell_type":"markdown","source":"First, I will import the data and explain what we are working with.","metadata":{}},{"cell_type":"code","source":"#path varies depending if I am running on my own machine or Kaggle\n\nmy_path = \"/Users/Ethan/Desktop/Desktop - Ethan’s MacBook Air/Personal Projects/Baseball/fan-engagement/data\"\n\nkaggle_path = \"../input/mlb-player-digital-engagement-forecasting\"","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:39:04.925157Z","iopub.execute_input":"2021-06-20T01:39:04.925586Z","iopub.status.idle":"2021-06-20T01:39:04.930593Z","shell.execute_reply.started":"2021-06-20T01:39:04.925547Z","shell.execute_reply":"2021-06-20T01:39:04.929489Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def read_data(path, file):\n    \n    df = pd.read_csv(f\"{path}/{file}.csv\")\n    \n    num_rows = len(df)\n    num_cols = len(df.columns)\n    mem_usage = df.memory_usage(deep = True).sum()\n    \n    print(f\"{file}.csv: {num_rows} rows; {num_cols} columns; {mem_usage} bytes of memory.\")\n    return df","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:39:04.932379Z","iopub.execute_input":"2021-06-20T01:39:04.932923Z","iopub.status.idle":"2021-06-20T01:39:04.942376Z","shell.execute_reply.started":"2021-06-20T01:39:04.932878Z","shell.execute_reply":"2021-06-20T01:39:04.941531Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#change my_path to kaggle_path when running on Kaggle\n\ntrain = read_data(kaggle_path, \"train\")\nteams = read_data(kaggle_path, \"teams\")\nseasons = read_data(kaggle_path, \"seasons\")\nplayers = read_data(kaggle_path, \"players\")\nawards = read_data(kaggle_path, \"awards\")\nexample_test = read_data(kaggle_path, \"example_test\")\nexample_submission = read_data(kaggle_path, \"example_sample_submission\")","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:39:04.943925Z","iopub.execute_input":"2021-06-20T01:39:04.944449Z","iopub.status.idle":"2021-06-20T01:40:19.623384Z","shell.execute_reply.started":"2021-06-20T01:39:04.944410Z","shell.execute_reply":"2021-06-20T01:40:19.622468Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"train.csv is the main dataset of interest and takes up a large amount of memory because each cell corresponds to a multitude of information at a given date.  All of this information is stored in json format.  The rest of the dataframes are helpful for identifying teams and players from their IDs, recording when each season begins, etc.","metadata":{}},{"cell_type":"code","source":"train.head()","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:40:19.626745Z","iopub.execute_input":"2021-06-20T01:40:19.627017Z","iopub.status.idle":"2021-06-20T01:40:19.664726Z","shell.execute_reply.started":"2021-06-20T01:40:19.626992Z","shell.execute_reply":"2021-06-20T01:40:19.663434Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.info()","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:40:19.667717Z","iopub.execute_input":"2021-06-20T01:40:19.668023Z","iopub.status.idle":"2021-06-20T01:40:19.688393Z","shell.execute_reply.started":"2021-06-20T01:40:19.667996Z","shell.execute_reply":"2021-06-20T01:40:19.687428Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Each row in this dataset corresponds to every date spanning from January 1st, 2018 through April 30th, 2021, and missing values often correspond to off-season dates.  The MLB pre-season (Spring Training) usually starts in late February and the post-season usually ends in late October.  The regular season usually spans from the beginning of April through the end of September.  Transactions (trades) and awards are not daily occurances, and Twitter followers are recorded at the 1st of every month according to the competition.","metadata":{}},{"cell_type":"markdown","source":"## Unnesting the Training Data ","metadata":{}},{"cell_type":"markdown","source":"Now, for train.csv, we need to convert the json from each cell into dataframes.  Each of the 11 columns will correspond to a dataframe, as nextDayPlayerEngagement represents the target variables.  The following function will convert the json into a dataframe given a column from train.csv.","metadata":{}},{"cell_type":"code","source":"def json_to_df(df, column):\n    \n    num_rows = len(df)\n    \n    data_list = []\n    for row in range(num_rows):\n        \n        json_data = df.iloc[row][column]\n        if str(json_data) != \"nan\": #we don't want to append NA values in the dataframes\n            data = pd.read_json(json_data)\n            data_list.append(data)\n        \n    all_data = pd.concat(data_list, axis = 0)\n    \n    num_rows = len(all_data)\n    num_cols = len(all_data.columns)\n    mem_usage = all_data.memory_usage(deep = True).sum()\n    \n    print(f\"{column}: {num_rows} rows; {num_cols} cols; {mem_usage} bytes.\")\n    return all_data","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:40:19.689636Z","iopub.execute_input":"2021-06-20T01:40:19.689883Z","iopub.status.idle":"2021-06-20T01:40:19.698687Z","shell.execute_reply.started":"2021-06-20T01:40:19.689859Z","shell.execute_reply":"2021-06-20T01:40:19.697803Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#a list of the 11 columns, not including the date.  We have this info in each json cell anyway.\n\nnested_columns = train.columns[1:]","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:40:19.700105Z","iopub.execute_input":"2021-06-20T01:40:19.700635Z","iopub.status.idle":"2021-06-20T01:40:19.712303Z","shell.execute_reply.started":"2021-06-20T01:40:19.700606Z","shell.execute_reply":"2021-06-20T01:40:19.711385Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#we yield 11 dataframes from train.csv\n\nengage, games, rosters, player_boxes, team_boxes, transactions, standings, awards, events, player_twitter, team_twitter = [json_to_df(train, var) for var in nested_columns]","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:40:19.713733Z","iopub.execute_input":"2021-06-20T01:40:19.714037Z","iopub.status.idle":"2021-06-20T01:44:40.661404Z","shell.execute_reply.started":"2021-06-20T01:40:19.714011Z","shell.execute_reply":"2021-06-20T01:44:40.660181Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Events make up a massive amount of information because it includes many variables for each individual pitch.","metadata":{}},{"cell_type":"code","source":"engage.head()","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:44:40.662917Z","iopub.execute_input":"2021-06-20T01:44:40.663200Z","iopub.status.idle":"2021-06-20T01:44:40.677999Z","shell.execute_reply.started":"2021-06-20T01:44:40.663170Z","shell.execute_reply":"2021-06-20T01:44:40.676942Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Each of these dataframes contain keys of date and player id, including the engagement target variable dataframe.  We can later use these keys to join all dataframes together when analyzing and creating the models.","metadata":{}},{"cell_type":"markdown","source":"## Cleaning","metadata":{}},{"cell_type":"markdown","source":"Now, some cleaning is required.  We need to convert each date into a pandas datetime object and then make sure all keys are named identically so we can join the data as we please.","metadata":{}},{"cell_type":"code","source":"from datetime import datetime, timedelta","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:44:40.679907Z","iopub.execute_input":"2021-06-20T01:44:40.680438Z","iopub.status.idle":"2021-06-20T01:44:40.688165Z","shell.execute_reply.started":"2021-06-20T01:44:40.680385Z","shell.execute_reply":"2021-06-20T01:44:40.687455Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"engage[\"engagementMetricsDate\"] = pd.to_datetime(engage[\"engagementMetricsDate\"])\n\n#As the competiton notes, the engagement data corresponds to information from the day prior.  Therefore, when\n#joining this data to any other data, we need to join on the previous day.  \n\nengage[\"engagementMetricsDate\"] = engage[\"engagementMetricsDate\"] - timedelta(days = 1)\n\nengage = engage.rename(columns = {\"engagementMetricsDate\": \"date\"})","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:44:40.689604Z","iopub.execute_input":"2021-06-20T01:44:40.689913Z","iopub.status.idle":"2021-06-20T01:44:41.928228Z","shell.execute_reply.started":"2021-06-20T01:44:40.689886Z","shell.execute_reply":"2021-06-20T01:44:41.926801Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"games[\"gameDate\"] = pd.to_datetime(games[\"gameDate\"])\nrosters[\"gameDate\"] = pd.to_datetime(rosters[\"gameDate\"])\nplayer_boxes[\"gameDate\"] = pd.to_datetime(player_boxes[\"gameDate\"])\nteam_boxes[\"gameDate\"] = pd.to_datetime(team_boxes[\"gameDate\"])\ntransactions[\"date\"] = pd.to_datetime(transactions[\"date\"])\nstandings[\"gameDate\"] = pd.to_datetime(standings[\"gameDate\"])\nawards[\"awardDate\"] = pd.to_datetime(awards[\"awardDate\"])\nevents[\"gameDate\"] = pd.to_datetime(events[\"gameDate\"])\nplayer_twitter[\"date\"] = pd.to_datetime(player_twitter[\"date\"])\nteam_twitter[\"date\"] = pd.to_datetime(team_twitter[\"date\"])\n\ngames = games.rename(columns = {\"gameDate\": \"date\"})\nrosters = rosters.rename(columns = {\"gameDate\": \"date\"})\nplayer_boxes = player_boxes.rename(columns = {\"gameDate\": \"date\"})\nteam_boxes = team_boxes.rename(columns = {\"gameDate\": \"date\"})\nstandings = standings.rename(columns = {\"gameDate\": \"date\"})\nawards = awards.rename(columns = {\"awardDate\": \"date\"})\nevents = events.rename(columns = {\"gameDate\": \"date\"})","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:44:41.935330Z","iopub.execute_input":"2021-06-20T01:44:41.935667Z","iopub.status.idle":"2021-06-20T01:44:46.496800Z","shell.execute_reply.started":"2021-06-20T01:44:41.935638Z","shell.execute_reply":"2021-06-20T01:44:46.495923Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The new engagement dataframe now has dates that corresponds to targets for the next day.","metadata":{}},{"cell_type":"code","source":"engage.head()","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:44:46.497984Z","iopub.execute_input":"2021-06-20T01:44:46.498392Z","iopub.status.idle":"2021-06-20T01:44:46.510162Z","shell.execute_reply.started":"2021-06-20T01:44:46.498352Z","shell.execute_reply":"2021-06-20T01:44:46.509468Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Merging: Example","metadata":{}},{"cell_type":"markdown","source":"An example of a dataframe created from a json column:","metadata":{}},{"cell_type":"code","source":"player_twitter.head()","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:44:46.511123Z","iopub.execute_input":"2021-06-20T01:44:46.511441Z","iopub.status.idle":"2021-06-20T01:44:46.531500Z","shell.execute_reply.started":"2021-06-20T01:44:46.511414Z","shell.execute_reply":"2021-06-20T01:44:46.530478Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Notice how our keys of interest are date and player id.  All data in the player_twitter df corresponds to engagement data for the next day, so these dataframes are ready to be merged.","metadata":{}},{"cell_type":"code","source":"#It is important to left join since we don't want to lose any information regarding target variables.\n\nengage_twitter = pd.merge(engage, player_twitter, on = [\"date\", \"playerId\"], how = \"left\")","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:44:46.532952Z","iopub.execute_input":"2021-06-20T01:44:46.533433Z","iopub.status.idle":"2021-06-20T01:44:47.027705Z","shell.execute_reply.started":"2021-06-20T01:44:46.533395Z","shell.execute_reply":"2021-06-20T01:44:47.026631Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"engage_twitter.head()","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:44:47.029209Z","iopub.execute_input":"2021-06-20T01:44:47.029661Z","iopub.status.idle":"2021-06-20T01:44:47.047870Z","shell.execute_reply.started":"2021-06-20T01:44:47.029619Z","shell.execute_reply":"2021-06-20T01:44:47.047189Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"NA's represent dates that aren't the first of the month or players who don't have Twitter info.","metadata":{}},{"cell_type":"markdown","source":"## Conclusion","metadata":{}},{"cell_type":"markdown","source":"We can now save all of the converted data for my next notebook, which will involve merging much of the data, cleaning, and feature selecting.  Thanks for reading!","metadata":{}},{"cell_type":"code","source":"#engage.to_csv(\"../fan-engagement/data/engage.csv\", index = False)\n#games.to_csv(\"../fan-engagement/data/games.csv\", index = False)\n#rosters.to_csv(\"../fan-engagement/data/rosters.csv\", index = False)\n#player_boxes.to_csv(\"../fan-engagement/data/player_boxes.csv\", index = False)\n#team_boxes.to_csv(\"../fan-engagement/data/team_boxes.csv\", index = False)\n#transactions.to_csv(\"../fan-engagement/data/transactions.csv\", index = False)\n#standings.to_csv(\"../fan-engagement/data/standings.csv\", index = False)\n#awards.to_csv(\"../fan-engagement/data/awards.csv\", index = False)\n#events.to_csv(\"../fan-engagement/data/events.csv\", index = False)\n#player_twitter.to_csv(\"../fan-engagement/data/player_twitter.csv\", index = False)\n#team_twitter.to_csv(\"../fan-engagement/data/team_twitter.csv\", index = False)","metadata":{"execution":{"iopub.status.busy":"2021-06-20T01:44:47.048935Z","iopub.execute_input":"2021-06-20T01:44:47.049394Z","iopub.status.idle":"2021-06-20T01:44:47.060321Z","shell.execute_reply.started":"2021-06-20T01:44:47.049343Z","shell.execute_reply":"2021-06-20T01:44:47.059283Z"},"trusted":true},"execution_count":null,"outputs":[]}]}