AE 02: Wrangling college education metrics

Suggested answers

Important

These are suggested answers. This document should be used as reference only, it’s not designed to be an exhaustive key.

To demonstrate data wrangling we will use data from College Scorecard.¹ The subset we will analyze contains a small number of metrics for all four-year colleges and universities in the United States for the 2025-26 academic year. ²

library(tidyverse)

The data is stored in scorecard.csv. The variables are:

• unit_id - Unit ID for institution
• name - Name of the college
• state - State abbreviation
• type - Type of college (Public; Private, nonprofit; Private, for-profit)
• adm_rate - Undergraduate admissions rate (from 0-100%)
• sat_avg - Average SAT equivalent score of students admitted
• cost - The average annual total cost of attendance, including tuition and fees, books and supplies, and living expenses
• net_cost - The average annual net cost of attendance (annual cost of attendance minus the average grant/scholarship aid)
• avg_fac_sal - Average faculty salary (9 month)
• pct_pell - Percentage of undergraduates who receive a Pell Grant
• comp_rate - Rate of first-time, full-time students at four-year institutions who complete their degree within six years
• first_gen - Share of first-generation students
• debt - Median debt of students after leaving school
• locale - Locale of institution

scorecard <- read_csv("data/scorecard.csv")

The data frame has around 1700 observations (rows), 1698 observations to be exact, so we will not view the entire data frame. Instead we’ll use the commands below to help us explore the data.

glimpse(scorecard)

Rows: 1,698
Columns: 14
$ unit_id     <dbl> 100654, 100663, 100706, 100724, 100751, 100830, 100858, 10…
$ name        <chr> "Alabama A & M University", "University of Alabama at Birm…
$ state       <chr> "AL", "AL", "AL", "AL", "AL", "AL", "AL", "AL", "AL", "AL"…
$ type        <chr> "Public", "Public", "Public", "Public", "Public", "Public"…
$ adm_rate    <dbl> 0.5795, 0.8818, 0.6857, 0.9755, 0.7665, 0.9157, 0.4592, 0.…
$ sat_avg     <dbl> 938, 1258, 1319, 976, 1285, 1093, 1345, 1020, NA, 1142, 10…
$ cost        <dbl> 27153, 28145, 27392, 23586, 33382, 21353, 34919, 37626, 27…
$ net_cost    <dbl> 17621, 18749, 18796, 20435, 22420, 13224, 24323, 22085, 19…
$ avg_fac_sal <dbl> 78291, 113508, 95751, 73377, 102771, 80766, 110916, 63441,…
$ pct_pell    <dbl> 0.6298, 0.3402, 0.2464, 0.7134, 0.1840, 0.4235, 0.1253, 0.…
$ comp_rate   <dbl> 0.2403, 0.6423, 0.6429, 0.3034, 0.7337, 0.3260, 0.8195, 0.…
$ first_gen   <dbl> 0.3658281, 0.3412237, 0.3101322, 0.3434343, 0.2257127, 0.3…
$ debt        <dbl> 16600, 15832, 13905, 17500, 17986, 13119, 17750, 15000, 11…
$ locale      <chr> "City", "City", "City", "City", "City", "City", "City", "C…

names(scorecard)

 [1] "unit_id"     "name"        "state"       "type"        "adm_rate"   
 [6] "sat_avg"     "cost"        "net_cost"    "avg_fac_sal" "pct_pell"   
[11] "comp_rate"   "first_gen"   "debt"        "locale"     

head(scorecard)

# A tibble: 6 × 14
  unit_id name  state type  adm_rate sat_avg  cost net_cost avg_fac_sal pct_pell
    <dbl> <chr> <chr> <chr>    <dbl>   <dbl> <dbl>    <dbl>       <dbl>    <dbl>
1  100654 Alab… AL    Publ…    0.580     938 27153    17621       78291    0.630
2  100663 Univ… AL    Publ…    0.882    1258 28145    18749      113508    0.340
3  100706 Univ… AL    Publ…    0.686    1319 27392    18796       95751    0.246
4  100724 Alab… AL    Publ…    0.976     976 23586    20435       73377    0.713
5  100751 The … AL    Publ…    0.766    1285 33382    22420      102771    0.184
6  100830 Aubu… AL    Publ…    0.916    1093 21353    13224       80766    0.424
# ℹ 4 more variables: comp_rate <dbl>, first_gen <dbl>, debt <dbl>,
#   locale <chr>

The head() function returns “A tibble: 6 x 14” and then the first six rows of the scorecard data.

Data wrangling with dplyr

{dplyr} is the primary package in the {tidyverse} for data wrangling.

NoteHelpful data wrangling resources

• {dplyr} reference page
• Data transformation cheatsheet

Quick summary of key {dplyr} functions³

Rows:

• filter():chooses rows based on column values.
• slice(): chooses rows based on location.
• arrange(): changes the order of the rows
• slice_sample(): take a random subset of the rows
• slice_min()/slice_max(): select rows with minimum/maximum values of a variable.

Columns:

• select(): changes whether or not a column is included.
• rename(): changes the name of columns.
• mutate(): changes the values of columns and creates new columns.

Groups of rows:

• summarize(): collapses a group into a single row.
• count(): count unique values of one or more variables.
• group_by(): perform calculations separately for each value of a variable

Operators

In order to make comparisons, we will use logical operators. These should be familiar from other programming languages. See below for a reference table for how to use these operators in R.

operator	definition
<	is less than?
<=	is less than or equal to?
>	is greater than?
>=	is greater than or equal to?
==	is exactly equal to?
!=	is not equal to?
x & y	is x AND y?
x | y	is x OR y?
is.na(x)	is x NA?
!is.na(x)	is x not NA?
x %in% y	is x in y?
!(x %in% y)	is x not in y?
!x	is not x?

The final operator only makes sense if x is logical (TRUE / FALSE).

The pipe

Before working with data wrangling functions, let’s formally introduce the pipe. The pipe, |>, is an operator (a tool) for passing information from one process to another. We will use |> mainly in data pipelines to pass the output of the previous line of code as the first input of the next line of code.

When reading code “in English”, say “and then” whenever you see a pipe.

• Your turn (3 minutes): Run the following chunk and observe its output. Then, come up with a different way of obtaining the same output.

scorecard |>
  select(name, type) |>
  head()

# A tibble: 6 × 2
  name                                type  
  <chr>                               <chr> 
1 Alabama A & M University            Public
2 University of Alabama at Birmingham Public
3 University of Alabama in Huntsville Public
4 Alabama State University            Public
5 The University of Alabama           Public
6 Auburn University at Montgomery     Public

Exercises

Demo: Filter the data frame to keep only schools with a greater than 40% share of first-generation students.

filter(.data = scorecard, first_gen > .40)

# A tibble: 351 × 14
   unit_id name          state type  adm_rate sat_avg  cost net_cost avg_fac_sal
     <dbl> <chr>         <chr> <chr>    <dbl>   <dbl> <dbl>    <dbl>       <dbl>
 1  101189 Faulkner Uni… AL    Priv…    0.731    1020 37626    22085       63441
 2  101365 Herzing Univ… AL    Priv…    0.933      NA 27750    19651       64503
 3  101587 University o… AL    Publ…    0.426    1015 22758    12684       72009
 4  102270 Stillman Col… AL    Priv…    0.622      NA 26107    15258       55476
 5  104717 Grand Canyon… AZ    Priv…    0.789    1077 32702    22472       68067
 6  106467 Arkansas Tec… AR    Publ…    0.959    1049 22808    12970       63423
 7  107983 Southern Ark… AR    Publ…    0.753    1047 24951    14027       67221
 8  108092 University o… AR    Publ…    0.805    1050 19048    10574       65979
 9  110361 California B… CA    Priv…    0.849      NA 54038    26285       98469
10  110486 California S… CA    Publ…    0.938      NA 19522     5652      104517
# ℹ 341 more rows
# ℹ 5 more variables: pct_pell <dbl>, comp_rate <dbl>, first_gen <dbl>,
#   debt <dbl>, locale <chr>

Your turn: Filter the data frame to keep only public schools with a net cost of attendance below $12,000.

filter(.data = scorecard, type == "Public", net_cost < 12000)

# A tibble: 140 × 14
   unit_id name          state type  adm_rate sat_avg  cost net_cost avg_fac_sal
     <dbl> <chr>         <chr> <chr>    <dbl>   <dbl> <dbl>    <dbl>       <dbl>
 1  108092 University o… AR    Publ…    0.805    1050 19048    10574       65979
 2  110486 California S… CA    Publ…    0.938      NA 19522     5652      104517
 3  110495 California S… CA    Publ…    0.981      NA 19739     6067      107334
 4  110510 California S… CA    Publ…    0.937      NA 17427     4564      107892
 5  110529 California S… CA    Publ…    0.752      NA 23955    11531      116487
 6  110547 California S… CA    Publ…    0.933      NA 23191     8615      106101
 7  110556 California S… CA    Publ…    0.953      NA 20095     7000      103860
 8  110565 California S… CA    Publ…    0.905      NA 18269     6555      110655
 9  110574 California S… CA    Publ…    0.974      NA 22850     9320      115245
10  110583 California S… CA    Publ…    0.463      NA 22679    10440      110484
# ℹ 130 more rows
# ℹ 5 more variables: pct_pell <dbl>, comp_rate <dbl>, first_gen <dbl>,
#   debt <dbl>, locale <chr>

filter(.data = scorecard, type == "Public" & net_cost < 12000)

# A tibble: 140 × 14
   unit_id name          state type  adm_rate sat_avg  cost net_cost avg_fac_sal
     <dbl> <chr>         <chr> <chr>    <dbl>   <dbl> <dbl>    <dbl>       <dbl>
 1  108092 University o… AR    Publ…    0.805    1050 19048    10574       65979
 2  110486 California S… CA    Publ…    0.938      NA 19522     5652      104517
 3  110495 California S… CA    Publ…    0.981      NA 19739     6067      107334
 4  110510 California S… CA    Publ…    0.937      NA 17427     4564      107892
 5  110529 California S… CA    Publ…    0.752      NA 23955    11531      116487
 6  110547 California S… CA    Publ…    0.933      NA 23191     8615      106101
 7  110556 California S… CA    Publ…    0.953      NA 20095     7000      103860
 8  110565 California S… CA    Publ…    0.905      NA 18269     6555      110655
 9  110574 California S… CA    Publ…    0.974      NA 22850     9320      115245
10  110583 California S… CA    Publ…    0.463      NA 22679    10440      110484
# ℹ 130 more rows
# ℹ 5 more variables: pct_pell <dbl>, comp_rate <dbl>, first_gen <dbl>,
#   debt <dbl>, locale <chr>

Your turn: How many public colleges and universities in each state have a net cost of attendance below $12,000?

# using group_by() and summarize()
scorecard |>
  filter(type == "Public", net_cost < 12000) |>
  group_by(state) |>
  summarize(n = n())

# A tibble: 38 × 2
   state     n
   <chr> <int>
 1 AR        1
 2 CA       15
 3 CO        2
 4 CT        1
 5 FL       11
 6 FM        1
 7 GA        6
 8 HI        2
 9 IL        2
10 IN        8
# ℹ 28 more rows

# using count()
scorecard |>
  filter(type == "Public", net_cost < 12000) |>
  count(state)

# A tibble: 38 × 2
   state     n
   <chr> <int>
 1 AR        1
 2 CA       15
 3 CO        2
 4 CT        1
 5 FL       11
 6 FM        1
 7 GA        6
 8 HI        2
 9 IL        2
10 IN        8
# ℹ 28 more rows

Your turn: Generate a data frame with the 10 most expensive colleges in 2025-26 based on net cost of attendance.

We could use a combination of arrange() and slice() to sort the data frame from most to least expensive, then keep the first 10 rows:

# using desc()
arrange(.data = scorecard, desc(net_cost)) |>
  slice(1:10)

# A tibble: 10 × 14
   unit_id name          state type  adm_rate sat_avg  cost net_cost avg_fac_sal
     <dbl> <chr>         <chr> <chr>    <dbl>   <dbl> <dbl>    <dbl>       <dbl>
 1  197221 Webb Institu… NY    Priv…    0.140    1496 81950    73043      123129
 2  193654 The New Scho… NY    Priv…    0.635      NA 88284    58741      120753
 3  121150 Pepperdine U… CA    Priv…    0.629    1384 93512    58098      128826
 4  197151 School of Vi… NY    Priv…    0.926    1320 78051    57914       39915
 5  136774 Ringling Col… FL    Priv…    0.696      NA 80276    57742       88821
 6  487861 Felbry Colle… OH    Priv…    0.854      NA 60136    57632       49950
 7  247649 Landmark Col… VT    Priv…    0.494      NA 83124    56954       66357
 8  110370 California C… CA    Priv…    0.911      NA 79776    53909       88065
 9  384254 Beacon Colle… FL    Priv…    0.435      NA 70775    53517       56106
10  194578 Pratt Instit… NY    Priv…    0.732    1290 80049    52659      113283
# ℹ 5 more variables: pct_pell <dbl>, comp_rate <dbl>, first_gen <dbl>,
#   debt <dbl>, locale <chr>

# using -
arrange(.data = scorecard, -net_cost) |>
  slice(1:10)

# A tibble: 10 × 14
   unit_id name          state type  adm_rate sat_avg  cost net_cost avg_fac_sal
     <dbl> <chr>         <chr> <chr>    <dbl>   <dbl> <dbl>    <dbl>       <dbl>
 1  197221 Webb Institu… NY    Priv…    0.140    1496 81950    73043      123129
 2  193654 The New Scho… NY    Priv…    0.635      NA 88284    58741      120753
 3  121150 Pepperdine U… CA    Priv…    0.629    1384 93512    58098      128826
 4  197151 School of Vi… NY    Priv…    0.926    1320 78051    57914       39915
 5  136774 Ringling Col… FL    Priv…    0.696      NA 80276    57742       88821
 6  487861 Felbry Colle… OH    Priv…    0.854      NA 60136    57632       49950
 7  247649 Landmark Col… VT    Priv…    0.494      NA 83124    56954       66357
 8  110370 California C… CA    Priv…    0.911      NA 79776    53909       88065
 9  384254 Beacon Colle… FL    Priv…    0.435      NA 70775    53517       56106
10  194578 Pratt Instit… NY    Priv…    0.732    1290 80049    52659      113283
# ℹ 5 more variables: pct_pell <dbl>, comp_rate <dbl>, first_gen <dbl>,
#   debt <dbl>, locale <chr>

We can also use the slice_max() function in {dplyr} to accomplish the same thing with a single function.

slice_max(.data = scorecard, order_by = net_cost, n = 10)

# A tibble: 10 × 14
   unit_id name          state type  adm_rate sat_avg  cost net_cost avg_fac_sal
     <dbl> <chr>         <chr> <chr>    <dbl>   <dbl> <dbl>    <dbl>       <dbl>
 1  197221 Webb Institu… NY    Priv…    0.140    1496 81950    73043      123129
 2  193654 The New Scho… NY    Priv…    0.635      NA 88284    58741      120753
 3  121150 Pepperdine U… CA    Priv…    0.629    1384 93512    58098      128826
 4  197151 School of Vi… NY    Priv…    0.926    1320 78051    57914       39915
 5  136774 Ringling Col… FL    Priv…    0.696      NA 80276    57742       88821
 6  487861 Felbry Colle… OH    Priv…    0.854      NA 60136    57632       49950
 7  247649 Landmark Col… VT    Priv…    0.494      NA 83124    56954       66357
 8  110370 California C… CA    Priv…    0.911      NA 79776    53909       88065
 9  384254 Beacon Colle… FL    Priv…    0.435      NA 70775    53517       56106
10  194578 Pratt Instit… NY    Priv…    0.732    1290 80049    52659      113283
# ℹ 5 more variables: pct_pell <dbl>, comp_rate <dbl>, first_gen <dbl>,
#   debt <dbl>, locale <chr>

Your turn: Generate a data frame with the average SAT score for each type of college.

Note that since the sat_avg column contains NAs (missing values), we need to explicitly exclude them from our mean calculation. Otherwise the resulting data frame contains NAs.

# incorrect - ignores NAs
scorecard |>
  group_by(type) |>
  summarize(mean_sat = mean(sat_avg))

# A tibble: 3 × 2
  type                mean_sat
  <chr>                  <dbl>
1 Private, for-profit       NA
2 Private, nonprofit        NA
3 Public                    NA

# exclude NAs using mean()
scorecard |>
  group_by(type) |>
  summarize(mean_sat = mean(sat_avg, na.rm = TRUE))

# A tibble: 3 × 2
  type                mean_sat
  <chr>                  <dbl>
1 Private, for-profit    1123.
2 Private, nonprofit     1218.
3 Public                 1140.

# exclude NAs using drop_na() to remove the rows prior to summarizing
scorecard |>
  drop_na(sat_avg) |>
  group_by(type) |>
  summarize(mean_sat = mean(sat_avg))

# A tibble: 3 × 2
  type                mean_sat
  <chr>                  <dbl>
1 Private, for-profit    1123.
2 Private, nonprofit     1218.
3 Public                 1140.

Your turn: Calculate for each school how many students it takes to pay the average faculty member’s salary and generate a data frame with the school’s name, net cost of attendance, average faculty salary, and the calculated value. How many Cornell and Ithaca College students does it take to pay their average faculty member’s salary?

Note

You should use the net cost of attendance measure, not the sticker price.

scorecard |>
  # mutate() to create a column with the ratio
  mutate(ratio = avg_fac_sal / net_cost) |>
  # select() to keep only the name and ratio columns
  select(name, net_cost, avg_fac_sal, ratio) |>
  # filter() to keep only Cornell and Ithaca College
  filter(name == "Cornell University" | name == "Ithaca College")

# A tibble: 2 × 4
  name               net_cost avg_fac_sal ratio
  <chr>                 <dbl>       <dbl> <dbl>
1 Cornell University    28690      161577  5.63
2 Ithaca College        33926       88596  2.61

NoteSession information

sessioninfo::session_info()

─ Session info ───────────────────────────────────────────────────────────────
 setting  value
 version  R version 4.6.1 (2026-06-24)
 os       macOS Tahoe 26.6.2
 system   aarch64, darwin23
 ui       X11
 language (EN)
 collate  en_US.UTF-8
 ctype    en_US.UTF-8
 tz       America/New_York
 date     2026-09-04
 pandoc   3.10 @ /Applications/Positron.app/Contents/Resources/app/quarto/bin/tools/aarch64/ (via rmarkdown)
 quarto   1.10.18 @ /Applications/quarto/bin/quarto

─ Packages ───────────────────────────────────────────────────────────────────
 ! package      * version date (UTC) lib source
 P bit            4.6.0   2025-03-06 [?] RSPM
 P bit64          4.8.2   2026-05-19 [?] RSPM
 P cli            3.6.6   2026-04-09 [?] RSPM
 P crayon         1.5.3   2024-06-20 [?] RSPM
 P digest         0.6.39  2025-11-19 [?] RSPM
 P dplyr        * 1.2.1   2026-04-03 [?] RSPM
 P evaluate       1.0.5   2025-08-27 [?] RSPM
 P farver         2.1.2   2024-05-13 [?] RSPM
 P fastmap        1.2.0   2024-05-15 [?] RSPM
 P forcats      * 1.0.1   2025-09-25 [?] RSPM
 P generics       0.1.4   2025-05-09 [?] RSPM
 P ggplot2      * 4.0.3   2026-04-22 [?] RSPM
 P glue           1.8.1   2026-04-17 [?] RSPM
 P gtable         0.3.6   2024-10-25 [?] RSPM
 P here           1.0.2   2025-09-15 [?] RSPM
 P hms            1.1.4   2025-10-17 [?] RSPM
 P htmltools      0.5.9   2025-12-04 [?] RSPM
 P htmlwidgets    1.6.4   2023-12-06 [?] RSPM
 P jsonlite       2.0.0   2025-03-27 [?] RSPM
 P knitr          1.51    2025-12-20 [?] RSPM
 P lifecycle      1.0.5   2026-01-08 [?] RSPM
 P lubridate    * 1.9.5   2026-02-04 [?] RSPM
 P magrittr       2.0.5   2026-04-04 [?] RSPM
 P otel           0.2.0   2025-08-29 [?] RSPM
 P pillar         1.11.1  2025-09-17 [?] RSPM
 P pkgconfig      2.0.3   2019-09-22 [?] RSPM
 P purrr        * 1.2.2   2026-04-10 [?] RSPM
 P R6             2.6.1   2025-02-15 [?] RSPM
 P RColorBrewer   1.1-3   2022-04-03 [?] RSPM
 P readr        * 2.2.0   2026-02-19 [?] RSPM
   renv           1.2.2   2026-04-16 [1] RSPM (R 4.6.1)
 P rlang          1.3.0   2026-07-05 [?] RSPM
 P rmarkdown      2.31    2026-03-26 [?] RSPM
 P rprojroot      2.1.1   2025-08-26 [?] RSPM
 P S7             0.2.2   2026-04-22 [?] RSPM
 P scales         1.4.0   2025-04-24 [?] RSPM
 P sessioninfo    1.2.4   2026-06-04 [?] RSPM
 P stringi        1.8.9   2026-08-04 [?] RSPM
 P stringr      * 1.6.0   2025-11-04 [?] RSPM
 P tibble       * 3.3.1   2026-01-11 [?] RSPM
 P tidyr        * 1.3.2   2025-12-19 [?] RSPM
 P tidyselect     1.2.1   2024-03-11 [?] RSPM
 P tidyverse    * 2.0.0   2023-02-22 [?] RSPM
 P timechange     0.4.0   2026-01-29 [?] RSPM
 P tzdb           0.5.0   2025-03-15 [?] RSPM
 P utf8           1.2.6   2025-06-08 [?] RSPM
 P vctrs          0.7.3   2026-04-11 [?] RSPM
 P vroom          1.7.1   2026-03-31 [?] RSPM
 P withr          3.0.3   2026-06-19 [?] RSPM
 P xfun           0.60    2026-07-09 [?] RSPM
 P yaml           2.3.12  2025-12-10 [?] RSPM

 [1] /Users/bcs88/Projects/info-5001/course-site/renv/library/macos/R-4.6/aarch64-apple-darwin23
 [2] /Users/bcs88/Library/Caches/org.R-project.R/R/renv/sandbox/macos/R-4.6/aarch64-apple-darwin23/46003b10

 * ── Packages attached to the search path.
 P ── Loaded and on-disk path mismatch.

──────────────────────────────────────────────────────────────────────────────

Footnotes

1. College Scorecard is a product of the U.S. Department of Education and compiles detailed information about student completion, debt and repayment, earnings, and more for all degree-granting institutions across the country.

2. The full database contains thousands of variables from 1996-2026.

3. From {dplyr} vignette