The teaching assistant

Session 6 practical exercises

You are reaching the end of this course. During the last five sessions you built a new vocabulary: importing, filtering, mutating, grouping, summarising, tabulating, plotting. It’s the vocabulary of data manipulation with the tidyverse in R. But of course, we could cover just a tiny fraction of what you can, and will need to do.

What will happen when you arrive at a problem we did not prepare you for? Today you will practice such situation with the assistance of AI tools, that replaced the old habits of looking for solutions in forums, tutorials or online communities.

At this morning’s meeting, Kassandra put your regional case counts on the screen. R1 with a thousand cases over the four years, R6 with six hundred and fifty, R9 with barely a hundred and seventy. Somebody from the vaccination unit asked the obvious question, the one that everybody in the room had been thinking:

“R1 has six times more cases than R9. But R1 also has fifteen times more people. So which region actually has the bigger problem?”

Silence. Because counts alone cannot answer that. You need denominators.

Kassandra forwards you an email from the statistics unit with two attachments and a one-line message: “Rates per 100,000, by region and year. And by sex if you can manage it. Tomorrow morning.”

You have never calculated a rate in R. And you just received two population dataframes that are stored in the data/raw folder, that now you need to incorporate somehow. Nobody taught you how to do so. And there is nobody left to ask — this is the last session.

Well. Almost nobody.

Part 1 · Ask to be taught, not to be rescued

You are about to ask an AI for help. How you ask determines what you learn.

The natural instinct is to describe the outcome you want and let it produce the code: “calculate incidence rates by region and year from these two files.” You will get working code in ten seconds. You will also have learned nothing, and tomorrow, when the code breaks or the numbers look odd, you will be exactly where you started.

Ask to be taught instead. It costs you two minutes and you keep it forever.

Never paste the data

Your line list contains dates, ages, sex, region and clinical outcome for real people. Never paste case-level records into a chat interface, even anonymised, even “just a few rows”.

Describe your data instead: variable names, types and the shape of the table. Use functions like names() and copy the output. Take time to explain your data, i.e., variable class when neccessary. Write a fake row. Everything should be done without a single real record leaving your computer.

This is not a formality. In surveillance work it is a data protection obligation, and it applies to every tool that sends your text to somebody else’s server. The only exception would be if your training site has their own AI Tool and confidentiality configurations.

Action — Open the AI tool of your choice and ask it to teach you how to bring information from a second table into an existing one in the tidyverse. Include in your prompt:

  1. Who you are — an epidemiologist, a beginner in R, working in tidyverse with %>%
  2. Which action you want to take — calculate rates by bringing population information from another dataframe, making sure every number is correctly pasted to its year and region.
  3. What you have — a table of case counts with region_id and year, and a separate table of populations with the same two identifiers
  4. What you want — an explanation of the workflow for the task detailing the steps, functions needed, and what you need to know to use them adequately. You can include potential error sources to be aware of.
  5. How to show you — with a made-up example of three or four rows, small enough to check by hand

Action — Read the answer. Then verify it on the toy example: take the three fake rows, work out by hand what the result should be, and compare it with what the AI says the result is.

That last step is the whole point. A four-row example is small enough that you can be certain whether you understood. If the explanation does not survive four rows, it will not survive two thousand six hundred.

What you should be able to say

You now have a name for the thing: left_join() or some other *_join() function. Before you use it on your real data, check that you actually understood how to use them:

Action — Can you answer these questions? If you cannot, go back and ask the AI to explain that specific point again.

  1. What is a key, and why does a join need one?
  2. What happens to a row in your counts table whose region and year are not found in the population table?
  3. What happens to a row in the population table that has no matching cases?
  4. Why is it called left? What would inner_join() have done differently?
  1. The key is the set of variables used to decide which row matches which. Both tables must contain them, and the values must be written identically. Here the key is two variables together — region_id and year — because neither alone identifies a row.
  2. It is kept, and the columns coming from the population table are filled with NA. No error, no warning.
  3. It is dropped. left_join() keeps every row of the left table and only the matching rows of the right one.
  4. “Left” is the table you start from — the one being piped in. It is preserved whole. inner_join() would keep only the rows that matched in both tables, silently discarding cases whose region had no population figure.

Point 2 is the one that will cost you if you forget it. A join that fails does not complain: it hands you a column full of NA, and every rate calculated from it becomes NA too.

Your counts table has a region that does not appear in the population file. What does left_join() do with it?

Understanding the different joins available

The join() family in the Tidyverse is wide. There is no guarantee that your AI recommended left_join() for this task, you may have been suggested to use inner_join(), for example. There is no single right or wrong answer to the task, as usual.

Action — Ask AI to explain, in simple terms, the difference between existing joins and how they apply to your current problem

Part 2 · Total rates

Now it’s time to use your newly acquired knowledge and code

Action — Import Population_total.xlsx from the data/raw folder and assign it to pop_total. Open it in the viewer and look at it before doing anything else.

Action — Compare the column names of pop_total with those of your counts table. What do you notice?

Action — Which functions you already used can solve the problem for you?

The statistics unit writes Year, Region_ID and Population. You write year and region_id. R is case-sensitive, which you learned in Session 1 — as far as it is concerned, Region_ID and region_id are two unrelated words.

The join

Action — Create your count table imd_counts by region and year.

Action — Before joining, note how many rows your counts table has:

Action — Join the population data onto your counts table, defining the key accordingly. Assign the result to imd_rates.

  • How do you define multiple keys at once?

Through the actions, we make you choose changing the names of the population dataframe to match the count table. This is one possible course of action, but there is an alternative way you should also know about: to tell the join that two differently-named columns hold the same information:

left_join(pop_total, 
          by = c("region_id" = "Region_ID", 
                 "year" = "Year"))

Both are correct. The AI will suggest one or the other depending on how you phrased your question, and you should recognise both when you see them. We used clean_names() because it is one line, it applies to the whole file at once, and it matches the names we used on the first dataframe when cleaning. This won’t always be your case, and you will need to manually rename().

Trust, then verify

You now have a table containing numbers that came from somewhere else. Two checks, both of which take five seconds, and both of which you should run after every join you ever write.

Action — Count the rows of imd_rates. Is it still 20?

Action — Filter for rows where population is missing. How many are there?

They may seem like silly questions to ask, something you can see in a glimpse by opening the data viewer. Now imagine the same issue with hundreds of rows…

Action — Ask AI about the common error when using join() functions and how to detect them for quality checks

It grew. If your table has more rows than it started with, the key did not identify rows uniquely in the second table — one count row found several population rows and was duplicated. Your cases are now being counted twice, and no error was raised.

It has holes. If population is NA anywhere, those keys did not match. A typo, a different spelling, a number stored as text. Every rate on those rows will be NA.

Row count and missing check. Every time. This is the same reflex you built in Session 2 after every filter(), applied to a step where the consequences are worse.

The rate

Action — Add a rate column: cases divided by population, multiplied by 100,000, rounded to one decimal.

Action — Which region has the highest rate in every single year?

Action — Which region has the most cases in every single year?

R9 is last by counts and first by rates. R6 is second by counts and last by rates. The two extremes swap places, and they do it in all four years — this is not a fluke of one bad year. Yet still, we cannot appreciate the full extent of the information that rates is showing us, because of the long format of the data.

Ideally, we would like to see a table with the evolution of every rate for all years. That has a name you already know: wide format.

The report table

Kassandra wants a nice table she can share with her colleagues, in wide format to see the evolution of these rates and counts together.

Action — Turn the rate dataframe imd_rates into a reporting table in wide format

This is another one to ask the AI about, and the same rule applies: ask what the function is called and what its arguments mean, not for the finished line. The verb you are looking for turns long data wide, and it needs to know which column supplies the new column names and which supplies the values.

A caveat that belongs in your report

R9 has around 265,000 inhabitants and a few dozen cases a year. When the numerator is that small, the rate moves violently for reasons that may have nothing to do with disease transmission: the difference between R9’s lowest and highest year is thirty-five cases.

The highest rate in your table is also the least precise one. This does not make it wrong or unimportant — it makes it a number that needs a confidence interval before anyone acts on it, something you will learn very soon.

Reporting a rate without acknowledging its stability is a different kind of error from calculating it incorrectly, and harder for a reader to catch.

Part 3 · The same verb, a harder table

First half done. Now the second attachment: the same populations, split by sex.

Action — Import Population_by_sex.xlsx as pop_sex, and look at it. Something is different, and it is not the sex column.

Action — Run clean_names() on it, and compare the result with the original.

There is no year column at all, so there is nothing to match your year against. The join you just learned cannot even be attempted.

Where the x came from

The years are not stored in a variable. They are column headings — x1999, x2000, x2001, x2002. clean_names() added the “x”. A column name in R cannot begin with a digit, so janitor prefixes a letter to make the name usable. Your file said 1999; R needs x1999.

This is a small thing, but notice what it means: clean_names() changed your data in a way you did not ask for and were not told about. Looking at your column names after every import is not paranoia — it is the only way you find out about changes like this before they become a bug.

Action — In a real situation, you would instantly switch back to the original data without clean_names(). But for now, stay with the x, because fixing it will be part of the challenge.

Long and wide formats

This is a shape problem, and shape problems are something you know already. But you haven’t faced yet a situation like this one.

Action — Ask the AI to explain how to turn a set of columns into two columns: one holding the old column names, one holding their values, in the tidyverse.

  • Same approach as before: ask what the function does and what each argument controls. You can add an extra line stating that you are familiar with pivot_longer() so the explanation can start from there

  • What about that annoying x in the year name? Is there any way to address it?

Action — Apply it to pop_sex: the four year columns become a year column and a population column. Assign the result to pop_sex_long.

Action — Check the result. How many rows should it have?

The error you were going to get anyway

Action — Now create your count table imd_counts_sex by region, year and sex

Action — Then, try to join pop_sex_long to create imd_rates_sex

Action — Read the error message we know you got. Are you able to tell the problem? If not, you can copy from the console and paste it in the AI chat

Action — Fix the problem and perform the join.

An error is the good outcome

This join failed loudly and stopped. That is the best thing that can happen to you, because you found out immediately and the fix took one line.

Compare it with what happens next.

The failure that does not fail

The join now runs. No error, no warning. Before you calculate anything from it, run the main check:

Action — Filter for rows where population is missing. Second failure.

There are rows with no population. The join ran perfectly and produced a table with one more row than expected and full of holes. In Part 2, you asked AI about the most common sources of error when joining, right? Do they apply here?

Action — Look at those empty rows. What do they have in common? How can you find the source of the problem?

Fix the obvious holes first

Action — Have you already identified the source of error? Fix it with a function you already know. Or ask AI…

Two tables, one variable, two vocabularies. Read the values character by character — the difference is a single letter, and it is at the end.

The statistics unit wrote Females. Your dataset says Female. To R these are two unrelated strings, so not one female row found a match, and every female rate in your table would have come out as NA.

Nothing went wrong technically. The code was correct. The data disagreed about a word.

Action — Join again and check for missing populations once more.

The hole that SHOULD be there

There are still rows with no population. Only one, but not zero.

Action — Look at them. What is the value of sex in those rows?

Action — How many rows does imd_counts_sex have? And pop_sex_long? Why are they different?

These are cases whose sex was never recorded. They are not a technical failure — the join worked exactly as it should. There is no population of people with unknown sex, so there is no denominator that could ever be attached to them.

This is not a bug to fix. It is a decision to make, and to write down.

An epidemiological decision, not a coding one

You cannot calculate a sex-specific rate for cases of unknown sex. The only defensible options are to exclude them from the sex-stratified analysis, or to report them separately as a known gap.

What you must not do is let them vanish quietly. The total number of cases in your sex-stratified table will now be smaller than the total in your Part 2 table, and a reader comparing the two will notice. If you do not explain the difference, they will assume you made a mistake.

You did the same thing in Session 5 when you built the trend line by sex. The reasoning has not changed — only now the consequence is visible in a denominator.

Action — Exclude cases with unknown sex from this analysis, and add a comment in your script.

Action — Calculate the sex-specific rates, rounded to one decimal.

Action — Turn the long dataframe to a wide-format table once more.

Exercise summary

You started this exercise unable to do something, and you finished it having done that thing twice. Nobody taught you left_join() or pivot_longer(). You asked for them, checked what you were told on an example small enough to verify by eye, and then applied it to data where the answer mattered.

That is the mode of use worth keeping. Not “write this for me” — “teach me this, small enough that I can check you”. The first gets you code you cannot maintain. The second gets you a tool you own.

But notice what the AI could not do for you. It did not know that Females and Female were different words in your two files. It did not know that cases of unknown sex have no denominator, or that a rate of 24 per 100,000 in a region of 265,000 people needs a confidence interval before anybody acts on it. It could not have told you that R9 was the region to look at, because it never saw your data.

Every one of those came from you. The join is the easy half. The next exercise reverses the arrangement: you will ask the AI to do something you already know how to check, and find out what happens when nobody checks.

Function Package What it does
left_join() dplyr Adds columns from a second table, matching rows by a key; keeps every row of the first table
by = c(...) dplyr States explicitly which variables form the key, and pairs up differently-named ones
pivot_longer() tidyr Turns a set of columns into two: one with the old names, one with the values

There is no single right answer here, because the code you end up with depends on what you asked for. What follows is one clean version, together with the prompts that would produce it.

Prompts that teach rather than solve

I am an epidemiologist, a beginner in R, working in the tidyverse. I have a table of case counts with columns region_id and year, and a separate table of populations with the same two columns plus population. I want to calculate rates, making sure every population is matched to the right region and year. Explain the workflow to me: which steps are involved, which functions I need, what their main arguments control, and what can go wrong. Illustrate with a made-up example of four rows so I can check it by hand. Do not give me a full script yet.

Explain the difference between the various *_join() functions in dplyr in simple terms, and tell me which one fits my case: I want to keep every case count, even if a region turns out to have no population figure.

I have a population table where each year is a separate column (x1999, x2000…) and I need the years to be a single variable so I can join on them. I already know pivot_longer(), so start from there. Which argument removes the x prefix as the names become values?

My join ran without errors but the population column is NA for some rows. What are the usual causes, and how do I diagnose which one it is?

The code

# Load libraries
library(pacman)
p_load(rio, here, tidyverse, janitor)

# Import data
imd <- import(here("data", "clean", "IMD_Sample_Clean.rds"))


# --- Part 2: total rates -----------------------------------------------

# Numerator: one row per region and year
imd_counts <- imd %>% 
  count(region_id, year)

# Denominator
pop_total <- import(here("data", "raw", "Population_total.xlsx"))

names(pop_total)

pop_total <- pop_total %>% 
  clean_names()

names(pop_total)

# Join on two keys at once
imd_rates <- imd_counts %>% 
  left_join(
    pop_total,
    by = c("region_id", "year")
  )

# Check 1: did the number of rows change?
nrow(imd_counts)
nrow(imd_rates)

# Check 2: did every row find a population?
imd_rates %>% 
  filter(is.na(population))

# Rates per 100,000 inhabitants
imd_rates <- imd_rates %>% 
  mutate(
    rate = round(n / population * 100000, 1)
  )

# Report table: counts and rates, side by side, one column per year
rates_wide <- imd_rates %>% 
  select(region_id, year, n, rate) %>% 
  pivot_wider(
    names_from  = year,
    values_from = c(n, rate)
  )


# --- Part 3: rates by sex ----------------------------------------------

# Denominator, in the wrong shape
pop_sex <- import(here("data", "raw", "Population_by_sex.xlsx"))
names(pop_sex)

pop_sex <- pop_sex %>% 
  clean_names()
names(pop_sex)

# Reshape: the four year columns become one variable
pop_sex_long <- pop_sex %>% 
  pivot_longer(
    cols         = -c(region_id, sex),
    names_to     = "year",
    values_to    = "population",
    names_prefix = "x"
  )

nrow(pop_sex_long)

# Numerator by sex
imd_counts_sex <- imd %>% 
  count(region_id, year, sex)

# This join fails: year is text on one side and numeric on the other
imd_counts_sex %>% 
  left_join(
    pop_sex_long,
    by = c("region_id", "year", "sex")
  )

# Fix the type
pop_sex_long <- pop_sex_long %>% 
  mutate(
    year = as.numeric(year)
  )

# Join again: this time it runs
imd_rates_sex <- imd_counts_sex %>% 
  left_join(
    pop_sex_long,
    by = c("region_id", "year", "sex")
  )

# Check 1: numerator and denominator do not have the same number of rows
nrow(imd_counts_sex)
nrow(pop_sex_long)
nrow(imd_rates_sex)

# Check 2: rows that found no population
imd_rates_sex %>% 
  filter(is.na(population))

# Where is the mismatch? Compare the two vocabularies
unique(imd_counts_sex$sex)

unique(pop_sex_long$sex)


# Reconcile them: change the population table, never the line list
pop_sex_long <- pop_sex_long %>% 
  mutate(
    sex = replace_values(
      sex,
      "Females" ~ "Female"
    )
  )

# Join once more
imd_rates_sex <- imd_counts_sex %>% 
  left_join(
    pop_sex_long,
    by = c("region_id", "year", "sex")
  )

# Check again: one row still has no population
imd_rates_sex %>% 
  filter(is.na(population))

# Cases of unknown sex have no denominator: excluded and documented
imd_rates_sex <- imd_rates_sex %>% 
  filter(!is.na(sex)) %>% 
  mutate(
    rate = round(n / population * 100000, 1)
  )

# Report table by sex
rates_sex_wide <- imd_rates_sex %>% 
  select(region_id, sex, year, rate) %>% 
  pivot_wider(
    names_from  = year,
    values_from = rate
  )