Data Wrangling — Part II: Sorting, Counting, and Deriving Variables

Before you begin, take a few minutes to check what you already know and how confident you feel. You will see the same questions again at the end of the module — this helps both you and us measure what you have learned. Click an option for every question and confidence rating, then click Next to continue.

In Part I you kept the rows and columns you wanted with filter() and select(), and joined your steps with the native pipe |>. Now you finish the toolkit. A trimmed table still needs to be read: you want the rows in a sensible order, a quick tally of how many patients fall in each group, and brand-new variables like a BMI category that were never in the raw file. This part gives you the verbs that do exactly that.

This part of the module covers three tools you will reach for in almost every analysis:

• arrange() and count() — sort the rows into order, and tally how many fall in each group.
• mutate() — derive a brand-new clinical variable from the columns you already have.
• if_else() and case_when() — write one yes/no rule, or several ranked rules, to label each patient.

By the end of this part you will be able to sort a dataset by any column in either direction, tally patients into group counts, and add a derived clinical variable such as an obesity flag or a BMI category — then chain all of these steps into one readable pipeline.

Try every snippet in the R Scratchpad on the right — the dataset patients.csv is already loaded and waiting for you.

Once you have the rows and columns you want, the two more verbs help you read the table are arrange() and count(). One orders the rows; the other summarises them into group counts.

arrange() sorts the rows by a column. arrange(age) orders patients from youngest to oldest — ascending order, the default. To sort the other way, wrap the column in desc(): arrange(desc(bmi)) puts the highest BMI first.

library(dplyr)
library(readr)
patients <- read_csv("patients.csv")
patients |>
    arrange(desc(bmi))

count() tallies the rows. count(sex) returns one row per sex with a column n giving how many patients have each value. It is the fastest way to answer "how many in each group?".

library(dplyr)
library(readr)
patients <- read_csv("patients.csv")
patients |>
    count(smoker)

You can build a tiny tibble by hand to see exactly how a count works. Here are six recorded smoking statuses; counting how many are "yes" is just summing a logical test.

Filtering, selecting, and sorting only ever show you data that is already there. mutate() is different: it adds a new column computed from existing ones, leaving every original column in place. This is how you derive the clinical variables your analysis actually needs.

mutate(bmi_rounded = round(bmi, 1)) adds a column called bmi_rounded. The name on the left is new; the expression on the right is computed for every row. You can derive several columns in one mutate() call, separated by commas.

library(dplyr)
library(readr)
patients <- read_csv("patients.csv")
patients |>
    mutate(bmi_rounded = round(bmi, 1))

For a single two-way rule, use if_else(). You give it a condition, the value to use when it is TRUE, and the value when it is FALSE. if_else(bmi >= 30, "obese", "not obese") labels each patient by one threshold.

library(dplyr)
library(readr)
patients <- read_csv("patients.csv")
patients |>
    mutate(obese = if_else(bmi >= 30, "obese", "not obese"))

When you need more than two categories, reach for case_when(). You write each rule as condition ~ value, and R checks them top to bottom, using the first one that is TRUE. This is how you build a BMI category from the standard cut-points.

case_when() stops at the first matching rule, so order the rules from lowest cut-point upward and they will not overlap. End with TRUE ~ "..." as a catch-all for anything left over.

library(dplyr)
library(readr)
patients <- read_csv("patients.csv")
patients |>
    mutate(bmi_cat = case_when(
    bmi < 18.5 ~ "underweight",
    bmi < 25 ~ "normal",
    bmi < 30 ~ "overweight",
    TRUE ~ "obese"
  ))

Here is the trap that catches everyone once. A dplyr verb returns a NEW data frame and never changes the original. Running patients |> mutate(...) prints a changed table, but patients itself is untouched. To keep the result, assign it back with <-, or keep piping into the next step.

Now feel the payoff of the pipe: chain three verbs into one readable pipeline. Read it aloud as "take patients, THEN keep ages over 50, THEN keep three columns, THEN sort by BMI".

library(dplyr)
library(readr)
patients <- read_csv("patients.csv")
patients |>
  filter(age > 50) |>
  select(patient_id, age, bmi) |>
  arrange(desc(bmi))

Now chain the verbs into one analysis. The worked example below writes each step as result <- verb(result, ...), which runs the verbs in order exactly as a |> pipeline would. It fades the support as you go: first you study a full solution, then you fill the gaps, then you solve a fresh one on your own.

library(dplyr)
library(readr)
patients <- read_csv("patients.csv")
result <- filter(patients, age > 50)
result <- mutate(result, obese = if_else(bmi >= 30, "obese", "not obese"))
result <- arrange(result, desc(bmi))
result

This Parsons problem gives you the right lines in the wrong order, plus a few lines that do not belong. Each line reassigns result, so the ordering is what matters: you must filter before you sort. Drag the correct lines into order and leave the wrong ones in the bank.

You have reached the end of the module. Try the same questions again — your answers here, paired with your pre-test answers, are how we measure what the module taught you. Answer every question and confidence rating, then click Submit and see results to view your score.

Here is how your post-test answers compare with your pre-test answers. The pre/post pairing is the most reliable way to see what this module actually taught you.