Lab 6: R and SQL

Overview

This lab is divided into two parts. In the first part you will practice using joins for data wrangling and analysis on the nycflights dataset. Some of these problems originate from Chapter 19 of your book. For the second part, you will download a dataset on the budgets of college sports programs and process it for storage in a relational database (I strongly recommend using duckdb which can be installed using install.packages("duckdb")- duckdb is highly performant, self-contained, and ideally suited both to learning SQL and performing data analysis). Then you will load this database and use dbplyr to perform an analysis. You will also practice using forcats to recode some of the variables as factors (which are supported by duckdb) and using separate_wider_delim to split columns of text data.

You will need to have installed and to use the following libraries:

library(tidyverse)
library(DBI)
library(duckdb)
library(nycflights13)

Problems

Part I: Airline Flight Delays

For the first part of this lab exercise, we will be using the nycflights library, which contains several different built in datasets including planes, which has information on each plane that appears in the data; flights, which has information on individual flights; airports, which has information on individual airports; and weather, which has information on the weather that the origin airports. In order to do this set of lab exercises, you will need to use different types of joins to combine variables in each data frame.

Problem 1

  1. Use the flights and planes tibbles (both part nycflights) to compute the mean departure delay of each aircraft that has more than 30 recorded flights in the dataset. Hint: Make note of the fact that the variable year appears in both flights and planes but means different things in each before performing any joins.

  2. Use anti-join to identify flights where tailnum does not have a match in plane. Determine the carriers for which this problem is the most common.

  3. Find the airplane model whose planes made the most flights in the dataset, and filter the dataset to contain only flights flown by airplanes of that model, adding a variable which corresponds to the year each those airplanes were built. Then compute the average departure delay for each year of origin and plot the data. Is there any evidence that plane age relates to departure delays?

Problem 2

  1. Compute the average delay by destination, then join on the airports data frame so you can show the spatial distribution of delays. Here’s an easy way to draw a map of the United States:
airports |>
  semi_join(flights, join_by(faa == dest)) |>
  ggplot(aes(x = lon, y = lat)) +
    borders("state") +
    geom_point() +
    coord_quickmap()

You might want to use the size or color of the points to display the average delay for each airport.

Part II: Creating and Accessing a Database

In this exercise we will begin with a flat file which contains data on college sports programs throughout the country. The source of the data is a government run database called Equity in Athletics Data Analysis, though we are working with just a small subset here. You can download this file by clicking here: sports_program_costs.csv. I have also included a data dictionary which gives a quick description of the dataset, which can be downloaded from here: sports_program_data_dictionary.qmd. This file contains information on two types of entities: sports teams and universities, however the information on both entities is combined into a single table, creating substantial redundancies. This exercise has several goals:

  1. Load this data into R, split the dataframe into two dataframes, one corresponding to colleges and another corresponding to sports teams, related to each other by common keys. Many databases are stored according to normalization rules, which are designed to limit redundancy and to make it easier to both work with the data and make changes to it. By splitting the data frame we will partially normalize it.
  2. Create a relational database using duckdb which contains these two tables.
  3. Read this database into R and use SQL queries to perform an analysis.

Problem 3:

  1. sports_program_data.csv contains variables which either describe properties of a sports team or a college. Split sports_programs_data into two data frames, one called colleges and another called teams. How can you tell which variables describe colleges and which describe teams? Use the data dictionary and observations of how the values vary as you move from college to college to help make the decision easier. Make sure there are primary keys for both the colleges and teams data frames (verify with count)- what are the primary keys in each case and are they simple keys (one variable) or compound keys (require multiple variables)? After the split, one of the data-sets you created should contain a foreign key- which one has it and what variables comprise it?

  2. The variable sector_name contains information about whether a college is public, private, non-profit, for-profit, a 2-year college, or a 4-year + college. Split this variable (using separate_wider_delim) into two variables, one of which describes whether the college is a Public, Private nonprofit, or private for-profit, and another which describes how many years the college programs run.

  3. Several variables are candidates to be recoded as factors, for example state_cd, zip_text, classification_name, sports, and the sector variables you just created for the previous part. Recode these variables as categorical variables. For the classification variable, use the classification_code to order the factors according to the numeric code.

Problem 4

  1. Using DBI, duckdb, and dbplyr, create a relational database with two tables, writing the sports data frame you created in problem 3 to one and the colleges data frame (also from problem 3) to the other. Write this database to disk. How does the size of the database file compare to the original csv? DuckDB uses a compression algorithm to store database files. Create a separate relational database with just a single table containing all the data in the original csv and compare the size of this database file to the normalized one you created (this is technically more of an apples to apples comparison of the effect of normalization).

  2. Use dbplyr to write a query to this database that calculates the top 10 colleges ranked by the average profit (defined as revenue - expenses) of their American football team over the years of data. Print the SQL query that results from your R pipeline using show_query() and then use collect() to show the results of this query.