solutions session 4

Exercises 1

  1. See if some of the other variables influence support for Sanders or Clinton

All you need to do to solve this is to replace the x aesthetical mapping with a different variable:

results_state_facts |>
  filter(
    party == "Democrat",
    candidate %in% c("Bernie Sanders", "Hillary Clinton")
  ) |>
  ggplot(aes(x = white, y = pct_votes, colour = candidate)) +
  geom_point() +
  geom_smooth(method = "lm", formula = y ~ x)
results_state_facts |>
  filter(
    party == "Democrat",
    candidate %in% c("Bernie Sanders", "Hillary Clinton")
  ) |>
  ggplot(aes(x = college, y = pct_votes, colour = candidate)) +
  geom_point() +
  geom_smooth(method = "lm", formula = y ~ x)
results_state_facts |>
  filter(
    party == "Democrat",
    candidate %in% c("Bernie Sanders", "Hillary Clinton")
  ) |>
  ggplot(aes(x = female, y = pct_votes, colour = candidate)) +
  geom_point() +
  geom_smooth(method = "lm", formula = y ~ x)
  1. Below I prepared code that loads data from the nycflights13 package which is about flights departing NYC in 2013:
library(nycflights13)
data(airports)
data(flights)
data(weather)
data(planes)
data(airlines)

The dataset is perfect for practising joining data, as the set is split into tables that relate to each other. You can see the realtion in the diagram below:

Imagine you wanted to draw (approximately) the route each plane flies from its origin to its destination. What variables would you need? What data frames would you need to combine?

  • flights contains an origin and a destination airport. Looking at airports with View(airports) we see a longitude and a latitude coordinate. By joining flights and airports, we could calculate the differences in longitude and latitude.
  1. Add the location of the origin and destination (i.e. the lat and lon) to flights.
  • for this, we need to join the tables two times. Once we join origin with faa, and once dest with faa
  • since we are only interested in the lat and lon variables, we could first select a subset from the airports table (this is optional)
airports_loc <- airports |>
  select(faa, lat, lon)
flights |>
  left_join(airports_loc, by = c("origin" = "faa")) |>
  left_join(
    airports_loc,
    by = c("dest" = "faa"),
    # using the suffix produces cleaner variable names
    suffix = c("_origin", "_dest")
  )
  1. Which airlines departed most often from the NYC airports (i.e. JFK, LGA or EWR). Use their real names not the abbreviation
flights |>
  filter(origin %in% c("JFK", "LGA", "EWR")) |>
  left_join(airlines, by = "carrier") |>
  count(name, sort = TRUE)
  1. How could you calculate the average age of the planes departing from NYC? (hint: you can calculate averages with mean(), you might want to use na.rm = TRUE)
  • first, we need to join flights with planes to see which planes depart from NYC and how often.
  • looking at View(flights) and View(planes), we see that a common key is tailnum
  • if we decide to calculate the mean age of the plane for every departure we would use left_join:
airplanes_deps <- flights |>
  filter(origin %in% c("JFK", "LGA", "EWR")) |>
  left_join(planes, by = "tailnum", suffix = c("", "_plane"))
  • alternatively, you might want to check which of the planes started from NYC and then calculate the mean age of these planes. In this case, you need to right_join, as you do not want duplicated planes
airplanes_deps <- flights |>
  filter(origin %in% c("JFK", "LGA", "EWR")) |>
  right_join(planes, by = "tailnum", suffix = c("", "_plane"))
  • now you can calculate the mean age:
mean(airplanes_deps$year_plane, na.rm = TRUE)
# or as tidy code
airplanes_deps |>
  summarise(year_plane = mean(year_plane, na.rm = TRUE))
  • you have to add na.rm = TRUE or the result will always be NA

Exercises 2

  1. create a new table in db called elections with the fields: election_id, country_name, election_date, party_id, vote_share, seats, and seats_total. Choose appropriate data types for the fields (maybe peak ahead to the next exercise).
library(DBI)
library(RSQLite)
db <- dbConnect(SQLite(), "data/db.sql")
dbExecute(
  db,
  "CREATE TABLE elections (
            election_id integer,
            country_name varchar,
            election_date date,
            party_id integer,
            vote_share real,
            seats integer,
            seats_total integer
          )"
)
dbListTables(db)
dbGetQuery(db, "SELECT * FROM elections")
  1. Using SQL syntax, add this data to the elections table:
election_id country_name election_date party_id vote_share seats seats_total
1030 Austria 1919-02-16 97 40.75 72 170 
dbExecute(
  db,
  "INSERT INTO elections
            VALUES (1030, 'Austria', '1919-02-16', 97, 40.75, 72, 170)"
)
dbGetQuery(db, "SELECT * FROM elections")
  1. Using SQL syntax, add this data to the elections table:
election_id country_name election_date vote_share party_id
872 Belgium 1908-05-24 22.6 2422 
dbExecute(
  db,
  "INSERT INTO elections (election_id, country_name, election_date, vote_share, party_id)
            VALUES (872, 'Belgium', '1908-05-24', 22.6, 2422)"
)
dbGetQuery(db, "SELECT * FROM elections")
  1. Querying data from elections, create an R object that has the election_id, party_id and vote_share fields for elections after 1910.
df <- dbGetQuery(db, "SELECT election_id, party_id, vote_share FROM elections")
df
  1. Querying data from elections, get election_id, party_id, vote_share fields and just the year of the election.
dbGetQuery(
  db,
  "SELECT election_id, party_id, vote_share, strftime('%Y', election_date) FROM elections"
)
  1. Add a new field vote_share_prop to the elections database which shows the vote share as proportion rather than a percentage.
dbExecute(db, "ALTER TABLE elections ADD vote_share_prop real")
dbExecute(
  db,
  "UPDATE elections
               SET vote_share_prop = vote_share / 100"
)
dbGetQuery(db, "SELECT * FROM elections")

Exercises 3

  1. Add the three data.frames from the 2016 primaries case study to the database (schedule, results_state, and facts)
  2. Right join results_state and facts
  3. Recreate the table results_state_time by querying and joining from db. Hint: multiple keys can be used by putting AND between them, e.g., ON df1.id = df2.id AND df1.id2 = df2.id2.