NCAA Basketball Analysis Part 1 - Downloading and Preparing the data for Analysis

Using R markdown SQL Chunks

All of this can be done using the DBI package, I just think the SQL code chunks are a super cool feature, so I like to use them whenever I get a chance. They are pretty straight forward. All you have to do is provide the connection object we defined above, and then write a SQL query.

First, I want to understand the difference between RegularSeasonCompactResults and RegularSeasonDetailedResults.

SELECT
      *
FROM
      RegularSeasonCompactResults
LIMIT 
      10

You can also save the the output of a query, and use it in an R code chunk.

SELECT
      *
FROM
      RegularSeasonDetailedResults
LIMIT 
      10

sample %>% 
      kable('html') %>% 
      kable_styling(
            bootstrap_options = c("striped", "hover")
            ) %>% 
      scroll_box(width = "100%")

Obviously the RegularSeasonCompactResults has WAY less information, so I am going to ditch that table, as well as a few others. Before doing that, I went ahead and joined the season and team details from two of the other tables.

SELECT
      wt.Team_Name AS winner,
      lt.Team_Name AS Loser,
      rs.Season,
      S.Dayzero,
      rs.Daynum,
      rs.Wscore,
      rs.Lscore,
      rs.Wloc,
      rs.Numot,
      rs.Wblk,
      rs.Wfga,
      rs.Wfga3,
      rs.Wfgm,
      rs.Wfgm3,
      rs.Wfta,
      rs.Wftm,
      rs.Wdr,
      rs.Wor,
      rs.Wast,
      rs.Wto,
      rs.Wpf,
      rs.Lblk,
      rs.Ldr,
      rs.Lor,
      rs.Lfga,
      rs.Lfga3,
      rs.Lfgm,
      rs.Lfgm3,
      rs.Lfta,
      rs.Lftm,
      rs.Last,
      rs.Lto,
      rs.Lpf
FROM
      RegularSeasonDetailedResults rs
      LEFT JOIN Seasons s ON s.Season = rs.Season
      LEFT JOIN Teams wt ON wt.Team_Id = rs.Wteam
      LEFT JOIN Teams lt ON lt.Team_Id = rs.Lteam

Now for post season…

SELECT
      wt.Team_Name AS winner,
      lt.Team_Name AS Loser,
      ps.Season,
      S.Dayzero,
      ps.Daynum,
      ps.Wscore,
      ps.Lscore,
      ps.Wloc,
      ps.Numot,
      ps.Wblk,
      ps.Wfga,
      ps.Wfga3,
      ps.Wfgm,
      ps.Wfgm3,
      ps.Wfta,
      ps.Wftm,
      ps.Wdr,
      ps.Wor,
      ps.Wast,
      ps.Wto,
      ps.Wpf,
      ps.Lblk,
      ps.Ldr,
      ps.Lor,
      ps.Lfga,
      ps.Lfga3,
      ps.Lfgm,
      ps.Lfgm3,
      ps.Lfta,
      ps.Lftm,
      ps.Last,
      ps.Lto,
      ps.Lpf
FROM
      TourneyDetailedResults ps
      LEFT JOIN Seasons s ON s.Season = ps.Season
      LEFT JOIN Teams wt ON wt.Team_Id = ps.Wteam
      LEFT JOIN Teams lt ON lt.Team_Id = ps.Lteam

Now that I have the data queried into the format that I want, I can put it together into a single data set.

# Labelling Source Location
regular_season$type = 'regular'
post_season$type = 'post'

# Binding data into single dataset
games <- bind_rows(regular_season, post_season)

I am a bit of a neat freak, so now I am going to write my new table and delete all of the redundent tables with a few functions from the DBI package.

dbWriteTable(
      conn = db_connection,
      name = 'games',
      value = games
)

dbRemoveTable(
      conn = db_connection,
      name = 'RegularSeasonCompactResults'
)
dbRemoveTable(
      conn = db_connection,
      name = 'RegularSeasonDetailedResults'
)
dbRemoveTable(
      conn = db_connection,
      name = 'TourneyDetailedResults'
)
dbRemoveTable(
      conn = db_connection,
      name = 'TourneyCompactResults'
)
dbRemoveTable(
      conn = db_connection,
      name = 'SampleSubmission'
)
dbDisconnect(db_connection)

Reminder: Remember to disconnect from the database. I forget pretty much every time…

Simple as that! We have a directory that holds the data that we downloaded, and cleaded up the database a little bit. Next step, some exploratory data analysis. Coming soon! Thanks for reading.