{"cells":[{"metadata":{"_uuid":"051d70d956493feee0c6d64651c6a088724dca2a","_execution_state":"idle","trusted":true},"cell_type":"code","source":"library(tidyverse) # metapackage with lots of helpful functions\nlibrary(modelr)\nlibrary(MLmetrics)\n\n# This is the path to the folder that contains all the data files\nlist.files(path = \"../input/google-cloud-ncaa-march-madness-2020-division-1-mens-tournament\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"First, we're going to open the sample submission file and take a look at the format that we have to submit in. \n\nThere are two columns in the file: \n* The *ID* column contains information about each game in the format `year_team1id_team2id`. (Every team that has ever played in the NCAA gets a unique ID). Note that not all of these are real games: some are just theoretical match-ups of teams that made it to the competition but never actually played each other.\n* The *Pred* column contains the prediction for each game, which is the probability that team 1 beats team 2.\n\n(We're also going to add a column that indicates that this math-up is in the submission file.)"},{"metadata":{"trusted":true},"cell_type":"code","source":"subs_df <- read_csv(\"../input/google-cloud-ncaa-march-madness-2020-division-1-mens-tournament//MSampleSubmissionStage1_2020.csv\") \nhead(subs_df)\n\nsubs_df <- subs_df %>%\n  mutate(\n   is_submission_row = TRUE\n  )","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Next, we're going to open file that holds the result of *every* NCAA game ever played since 1985."},{"metadata":{"trusted":true},"cell_type":"code","source":"results_df <- read_csv(\"../input/google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1/MNCAATourneyCompactResults.csv\")\nhead(results_df)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This next bit of code creates an ID column (to match the one in the sample submission file), as well as an `outcome` column that records whether Team 1 or Team 2 won that match.\n\n(To understand what's going on here, recall from the submission file that Team 1 is the team with the lowest ID number.)"},{"metadata":{"trusted":true},"cell_type":"code","source":"results_df <- results_df %>%\n  mutate(\n    ID = if_else(\n      condition = WTeamID > LTeamID,\n      true = str_c(Season, LTeamID, WTeamID, sep = \"_\"),\n      false = str_c(Season, WTeamID, LTeamID, sep = \"_\")\n    )\n  )\n\nhead(results_df)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now we will:\n\n1. Join the two datasets.\n\n2. Split the ID column into separate columns for year, team 1's ID, and team 2's ID.\n\n3. Create a column `is_training` that indicates whether a match is in the submission file.\n\n4. Create an `outcome` column that indicates if Team1 won this match (note that this will be NA for any theoretical match-up rows from the submission example file).\n\n5. Finally we will convert `year`, `team1`, and `team2` from strings to integers\n\n(Later we will split this dataset into test and training subsets. We will train our model on the `is_training == TRUE` rows, and test our predictions on the `is_training == FALSE` rows.)"},{"metadata":{"trusted":true},"cell_type":"code","source":"joined_df <- results_df %>%\n  full_join(subs_df, by = c(\"ID\" = \"ID\")) %>%\n  separate(ID, into = c(\"year\", \"team1\", \"team2\"), sep = \"_\", remove = FALSE) %>%\n  mutate(\n    is_training = is.na(is_submission_row),\n    outcome = if_else(\n      condition = WTeamID > LTeamID,\n      true = 0,\n      false = 1\n    ),\n    year = as.integer(year),\n    team1 = as.integer(team1),\n    team2 = as.integer(team2)\n  )\n\nhead(joined_df)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Each year, every team gets a seed (which is a ranking). We will use these seeds to create a model that predicts which team will win any given match."},{"metadata":{"trusted":true},"cell_type":"code","source":"seeds <- read_csv(\"../input/google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1/MNCAATourneySeeds.csv\")\nhead(seeds)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We need to extract the actual seed number from the seed column:"},{"metadata":{"trusted":true},"cell_type":"code","source":"seeds_df <- seeds %>%\n  mutate(seed = as.integer(str_sub(Seed, 2, 3)))\nhead(seeds_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Now we will join the seeds dataframe with the matches dataframe twice, to get (1) the seed for team 1, and (2) the seed for team 2\n    \nWe also drop a bunch of columns that we no longer care about, and create a new column, the difference between the seeds of the two teams.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"full_dataset <- joined_df %>% \n  left_join(seeds_df %>% select(-Seed), by = c(\"year\" = \"Season\", \"team1\" = \"TeamID\")) %>%\n  rename(team1_seed = seed) %>%\n  left_join(seeds_df %>% select(-Seed), by = c(\"year\" = \"Season\", \"team2\" = \"TeamID\")) %>%\n  rename(team2_seed = seed) %>%\n  select(-Pred, -(Season:NumOT), -is_submission_row) %>%\n  mutate(seed_diff = team1_seed - team2_seed)\n\nhead(full_dataset)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Sanity check to make sure that joining worked properly\nfull_dataset %>%\n  group_by(is_training) %>%\n  summarize(\n    number = n(),\n    missing_seed = sum(is.na(team1_seed))\n  )","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Finally we split our dataset into training and testing sections:"},{"metadata":{"trusted":true},"cell_type":"code","source":"training_df <- full_dataset %>%\n  filter(is_training == TRUE)\n\ntest_df <- full_dataset %>%\n  filter(is_training == FALSE)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Then we:\n\n1. train a logistic regression model on the training data (using the difference in seedings between the two teams as our explanatory variable)\n\n2. use this model to create predictions for the test dataset\n\n3. calculate the Log Loss on just the games that were actually played"},{"metadata":{"trusted":true},"cell_type":"code","source":"seed_model <- glm(outcome ~ seed_diff, data = training_df, family = binomial)\n\nseed_submission <- test_df %>%\n  add_predictions(seed_model, type=\"response\") %>%\n  rename(Pred = pred)\n\nhead(seed_submission)\n\neval_set <- seed_submission %>%\n  filter(!is.na(outcome))\nLogLoss(eval_set$Pred, eval_set$outcome)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Creating a submission file:"},{"metadata":{"trusted":true},"cell_type":"code","source":"seed_submission %>%\n  select(ID, Pred) %>%\n  write_csv(\"submission.csv\")","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"R","language":"R","name":"ir"},"language_info":{"mimetype":"text/x-r-source","name":"R","pygments_lexer":"r","version":"3.4.2","file_extension":".r","codemirror_mode":"r"}},"nbformat":4,"nbformat_minor":4}