13  HK Graduates Employment

The new challenge: split, reshape, and recombine mixed data

The table mixes two levels of detail. Full-time employment is divided into occupation rows, while every other employment status is already a single total. We must separate these structures, aggregate them to the same level, recombine them, and only then construct a denominator.

13.1 Learning objectives

By the end of this case, you should be able to:

• recognize a table that mixes different levels of detail;
• split rows with different structures;
• recombine detailed occupation rows into comparable status totals;
• reshape the status totals into columns;
• build a denominator from mutually exclusive categories; and
• calculate a weighted rate.

13.2 Why the familiar workflow is not enough

The Norway holdings have a relatively simple row structure. The Hong Kong graduate employment data are more complicated. A single group_by() can produce a result, but first separating the two row structures makes the aggregation rule visible and easier to check.

Continue in the same project

Open djr.Rproj and create 11-employment.Rmd. Download employment.csv and save it in data/. The familiar folder structure lets you concentrate on the more complicated row structure.

13.3 Set up the case

library(tidyverse)

employment <- read_csv("data/employment.csv") |>
  rename(
    academic_year = `Academic Year`,
    university = University,
    level = `Level of study`,
    status = `Employment Situation`,
    occupation = Occupation,
    headcount = `Number of Graduates (Headcount)`
  )

glimpse(employment)

Rows: 5,669
Columns: 6
$ academic_year <chr> "2009/10", "2009/10", "2009/10", "2009/10", "2009/10", "…
$ university    <chr> "City University of Hong Kong", "City University of Hong…
$ level         <chr> "Research postgraduate", "Research postgraduate", "Resea…
$ status        <chr> "FT employment", "FT employment", "FT employment", "FT e…
$ occupation    <chr> "Authors, Journalists and Related Writers", "Business Pr…
$ headcount     <dbl> 1, 2, 2, 1, 3, 1, 3, 1, 15, 2, 18, 8, 5, 1, 1, 97, 1, 6,…

13.4 Understand the row structure

Count the combinations of status and occupation:

employment |>
  count(status, occupation) |>
  arrange(status, desc(n))

For full-time employment, the occupation column contains many detailed categories. For other statuses, it contains “Not in Full-time Employment.”

One raw row therefore represents one headcount cell for an academic year, university, study level, employment status, and—only for full-time employment—occupation.

flowchart TD
  A["Year × university × level"] --> B["Full-time employment"]
  A --> C["Other employment status"]
  B --> D["Several occupation rows"]
  C --> E["One row"]

Counting raw rows would count categories, not graduates.

13.5 The strategy

We will solve the mixed structure in six steps:

1. split full-time and other-status rows;
2. aggregate the detailed occupation rows;
3. recombine comparable status totals;
4. reshape statuses into columns;
5. construct a denominator; and
6. add headcounts before calculating a combined rate.

Each step changes what one row represents. State that unit after every major transformation.

13.6 Strategy 1: split the two row structures

Use filter() to create one table for detailed full-time records and another for the already aggregated statuses:

full_time <- employment |>
  filter(status == "FT employment")

other_statuses <- employment |>
  filter(status != "FT employment")

The split is based on data structure, not on which outcome is more important. It prevents the occupation rows from being treated as if they were already comparable with unemployment or further-study totals.

13.7 Strategy 2: aggregate the detailed rows

Within the full-time table, add occupation headcounts for each cohort:

full_time_totals <- full_time |>
  group_by(academic_year, university, level, status) |>
  summarise(headcount = sum(headcount))

full_time_totals

One row now represents the full-time total for one year-university-level cohort.

The other-status table already has one row for each status in each cohort. Keep only the columns needed for the next step:

other_status_totals <- other_statuses |>
  select(academic_year, university, level, status, headcount)

13.8 Strategy 3: recombine comparable totals

The two tables now have the same columns and the same level of detail. bind_rows() stacks them:

status_totals <- bind_rows(
  full_time_totals,
  other_status_totals
)

status_totals |>
  count(status)

One row now represents one employment status within one cohort. Recombining before the full-time rows were aggregated would recreate the original problem.

13.9 Strategy 4: reshape statuses into columns

pivot_wider() gives each status its own column:

cohorts <- status_totals |>
  pivot_wider(
    names_from = status,
    values_from = headcount
  ) |>
  rename(
    full_time = `FT employment`,
    further_studies = `Further studies`,
    others = Others,
    underemployed = Underemployed,
    unemployed = Unemployed
  )

cohorts

One row now represents one year-university-level group.

13.10 Strategy 5: build the denominator

The employment statuses are mutually exclusive. Add them to obtain the total reported graduate outcomes, then calculate rates:

cohorts <- cohorts |>
  mutate(
    total = full_time + further_studies + others + underemployed + unemployed,
    full_time_rate = full_time / total,
    unemployment_rate = unemployed / total
  )

cohorts

The numerator and denominator now refer to the same cohort. If any status is missing, investigate it before treating the value as zero.

13.11 Strategy 6: calculate a weighted university rate

To combine study levels, add the headcounts first and calculate the rate from those totals:

university_rates <- cohorts |>
  group_by(academic_year, university) |>
  summarise(
    full_time = sum(full_time),
    unemployed = sum(unemployed),
    total = sum(total)
  ) |>
  mutate(
    full_time_rate = full_time / total,
    unemployment_rate = unemployed / total
  )

university_rates

Do not simply average the study-level rates. That would give a small cohort the same influence as a large cohort. Adding the headcounts first weights the result by the number of graduates.

A descriptive comparison is not a league table

Universities differ in subjects, study levels, student populations, and career paths. The rates describe reported outcomes. They do not prove that attending one university caused a better or worse outcome.

13.12 Optional extension: examine one occupation

Suppose the question concerns authors, journalists, and related writers. First stay within full-time employment, then create an indicator column before grouping. if_else() assigns one value when a condition is true and another when it is false:

journalism_share <- employment |>
  filter(status == "FT employment") |>
  mutate(
    journalism_headcount = if_else(
      occupation == "Authors, Journalists and Related Writers",
      headcount,
      0
    )
  ) |>
  group_by(academic_year, university, level) |>
  summarise(
    journalists = sum(journalism_headcount),
    all_full_time = sum(headcount)
  ) |>
  mutate(share_of_full_time = journalists / all_full_time)

journalism_share

The denominator is all full-time employed graduates in the same cohort, because that is the comparison being made.

13.13 Practice

Choose unemployment, underemployment, or further study. Create a university- year rate by:

1. describing the raw row structure;
2. splitting the detailed and already aggregated rows;
3. aggregating full-time occupation rows and recombining the tables;
4. reshaping the statuses;
5. adding headcounts before calculating the rate; and
6. writing one sentence explaining why the result is not a causal university ranking.

13.14 Takeaways

Function or idea	What it does in this case
filter()	Splits rows with different structures
group_by() + summarise()	Combine detailed occupation rows into status totals
bind_rows()	Recombines tables after their row structures match
pivot_wider()	Put mutually exclusive statuses into columns
mutate()	Build totals and rates with explicit denominators
Weighted rate	Adds headcounts before calculating the combined rate

Want more control?

The official dplyr reference and tidyr reference cover further grouping and reshaping options for more complicated official statistics.