Select and derive columns while retaining observation identity.
Explain grouped summaries, missing-value choices and unmatched join records.
Reshape a table and verify a round trip.
Export a checked table and reuse one analysis across later outputs.
❓ Questions
Which transformations change the meaning or number of records?
What evidence supports a missing-value or join decision?
How can the notebook and dashboard use the same checked calculation?
Structure & Agenda
Block
Teaching
Activity
Output to keep
Inspect and select
10 min
5 min
A checked subset
Derive and summarise
10 min
5 min
An explicit summary
Join and account for records
10 min
5 min
A join audit
Break
—
10 min break
—
Reshape and verify
10 min
5 min
A checked round trip
File exchange
10 min
5 min
A checked export
Share the analysis
10 min
5 min
Reusable selection and checks
Total
60 min
30 min
100 min including the break
This is lesson 5 of eight. Each five-minute activity includes a room response and debrief. Save work before the ten-minute break; use a practice copy and keep original inputs unchanged.
Starting Point and Preparation
Use the shared research project in a practice copy for lessons 5–8. Open that folder as the RStudio project. Complete lessons 1–4 and prepare the packages in Setup. The original Gapminder examples and calculations below are retained.
Inspect the Data before Selecting
Begin with a Research Question
A collaborator wants to compare historical life expectancy in two countries. Before producing a chart, identify the observation level, recorded years and units. These data concern countries over time, not individual participants or current conditions. The Gapminder package documentation describes its source and coverage.
Room check: ask pairs to name the identifiers needed to distinguish a country observed in two different years.
Load the Lesson Packages
Installation is covered in Setup. Load the packages for this R session:
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr 1.2.1 ✔ readr 2.2.0
✔ forcats 1.0.1 ✔ stringr 1.6.0
✔ ggplot2 4.0.3 ✔ tibble 3.3.1
✔ lubridate 1.9.5 ✔ tidyr 1.3.2
✔ purrr 1.2.2
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag() masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
Attaching package: 'gridExtra'
The following object is masked from 'package:dplyr':
combine
Exploring the Gapminder Dataset
Let’s take a look at the first few rows of the gapminder dataset.
# A tibble: 6 × 6
country continent year lifeExp pop gdpPercap
<fct> <fct> <int> <dbl> <int> <dbl>
1 Afghanistan Asia 1952 28.8 8425333 779.
2 Afghanistan Asia 1957 30.3 9240934 821.
3 Afghanistan Asia 1962 32.0 10267083 853.
4 Afghanistan Asia 1967 34.0 11537966 836.
5 Afghanistan Asia 1972 36.1 13079460 740.
6 Afghanistan Asia 1977 38.4 14880372 786.
The dplyr package provides functions for data manipulation, such as select(), filter(), mutate(), arrange(), summarise(), and group_by().
As in lesson 4, %>% passes the preceding table into the next function. For example, gapminder %>% select(country, year) means select(gapminder, country, year). Read a sequence of pipes from left to right and inspect the result after each transformation.
Selecting Columns
Select specific columns using select().
Select Individual Columns
# A tibble: 6 × 3
country continent pop
<fct> <fct> <int>
1 Afghanistan Asia 8425333
2 Afghanistan Asia 9240934
3 Afghanistan Asia 10267083
4 Afghanistan Asia 11537966
5 Afghanistan Asia 13079460
6 Afghanistan Asia 14880372
Filtering Rows
Use filter() to subset rows based on conditions.
Filter for Belgium
# A tibble: 8 × 6
country continent year lifeExp pop gdpPercap
<fct> <fct> <int> <dbl> <int> <dbl>
1 Belgium Europe 1952 68 8730405 8343.
2 Belgium Europe 1957 69.2 8989111 9715.
3 Belgium Europe 1962 70.2 9218400 10991.
4 Belgium Europe 1967 70.9 9556500 13149.
5 Belgium Europe 1972 71.4 9709100 16672.
6 Belgium Europe 1977 72.8 9821800 19118.
7 Belgium Europe 1982 73.9 9856303 20980.
8 Belgium Europe 1987 75.4 9870200 22526.
Filter with multiple conditions
# A tibble: 4 × 6
country continent year lifeExp pop gdpPercap
<fct> <fct> <int> <dbl> <int> <dbl>
1 Oman Asia 1982 62.7 1301048 12955.
2 Oman Asia 1987 67.7 1593882 18115.
3 Oman Asia 1992 71.2 1915208 18617.
4 Oman Asia 1997 72.5 2283635 19702.
Filter using %in% operator
# A tibble: 6 × 6
country continent year lifeExp pop gdpPercap
<fct> <fct> <int> <dbl> <int> <dbl>
1 Denmark Europe 1952 70.8 4334000 9692.
2 Denmark Europe 1957 71.8 4487831 11100.
3 Denmark Europe 1962 72.4 4646899 13583.
4 Denmark Europe 1967 73.0 4838800 15937.
5 Denmark Europe 1972 73.5 4991596 18866.
6 Denmark Europe 1977 74.7 5088419 20423.
Combining Select and Filter
Combine select() and filter() to extract specific data.
# A tibble: 6 × 3
country year gdpPercap
<fct> <int> <dbl>
1 United Kingdom 1952 9980.
2 United Kingdom 1957 11283.
3 United Kingdom 1962 12477.
4 United Kingdom 1967 14143.
5 United Kingdom 1972 15895.
6 United Kingdom 1977 17429.
Task 1: Check a Country–Year Selection (5 min)
Goal: retain a specific comparison without losing row identity. Input: the packaged gapminder table. Work in pairs.
1 min: check that 1977, Ireland and Brazil are present.
2 min: filter to those two country–year observations and select country, year and lifeExp.
1 min: compare the selected values with the original rows and record the units.
1 min: two pairs tell the room which identifiers and check they retained.
Keep: the checked subset and selection code. If access fails: annotate the instructor’s displayed table.
The output should have one row per selected country in the chosen year. Keep the country and year rather than two unlabelled values. An empty result requires a spelling or coverage check; do not silently substitute a nearby year.
Derive and Summarise with Meaning
Mutating Data
Use mutate() to create new columns and transmute() to keep only new columns.
Using Mutate to add new columns
# A tibble: 6 × 8
country continent year lifeExp pop gdpPercap pop_million countName
<fct> <fct> <int> <dbl> <int> <dbl> <dbl> <chr>
1 Afghanistan Asia 1952 28.8 8425333 779. 8.43 Afg
2 Afghanistan Asia 1957 30.3 9240934 821. 9.24 Afg
3 Afghanistan Asia 1962 32.0 10267083 853. 10.3 Afg
4 Afghanistan Asia 1967 34.0 11537966 836. 11.5 Afg
5 Afghanistan Asia 1972 36.1 13079460 740. 13.1 Afg
6 Afghanistan Asia 1977 38.4 14880372 786. 14.9 Afg
Using Mutate to Keep Only New Columns
The multiplication below demonstrates a derived column. lifeExp * pop is not itself evidence of observed person-years or a standard population-health measure; interpreting such a quantity needs a justified analytical definition.
The threshold of 10,000 below is an existing teaching example, not an official income classification. State its units and rationale before adapting it to a research analysis.
Group data with group_by() for per-group summaries. The example calculates an unweighted mean across country observations for each year; it is not a population-weighted global life expectancy. Record the aggregation level and weighting choice.
1 min: choose one recorded year and identify its observations.
2 min: inspect the summary’s row count, number of countries and mean life expectancy.
1 min: compare one statistic with a direct calculation on that year’s source rows.
1 min: two pairs explain to the room whether the mean represents countries or individual people.
Keep: the summary and a sentence naming its aggregation and weighting. If access fails: inspect the instructor’s summary and identify the records it combines.
Grouping changes the observation level from country–year to year. The mean gives each country observation equal weight; interpreting it as the experience of an average individual would require a different, justified approach.
Joining Data
Perform joins using inner_join(), left_join(), right_join() and full_join(). The employee and department tables below are fictional teaching examples. Check uniqueness and unmatched IDs before choosing the records to retain. See the official join documentation.
Example data for joining
Inspect the Join Keys
id name age
1 1 Alice 25
2 4 David 28
id department
1 5 IT
2 6 Sales
Unmatched IDs are part of the result to explain, not automatically errors to discard. Repeated keys could multiply rows; investigate their meaning before joining.
Inner join
id name age department
1 2 Bob 30 HR
2 3 Charlie 22 Finance
left join
id name age department
1 1 Alice 25 <NA>
2 2 Bob 30 HR
3 3 Charlie 22 Finance
4 4 David 28 <NA>
right join
id name age department
1 2 Bob 30 HR
2 3 Charlie 22 Finance
3 5 <NA> NA IT
4 6 <NA> NA Sales
full join
id name age department
1 1 Alice 25 <NA>
2 2 Bob 30 HR
3 3 Charlie 22 Finance
4 4 David 28 <NA>
5 5 <NA> NA IT
6 6 <NA> NA Sales
Missing-Value Decisions with tidyr
The fictional table below demonstrates different missing-value operations using tidyr. Inspect the affected rows before choosing a policy. These operations change the data and answer different questions; they are not automatic cleaning steps.
Sample data with missing values
Remove rows with any NA
Remove rows with NA in specific column
Replace NA Values
The replacements of age with 0 and score with 50 are deliberately arbitrary syntax examples. They do not establish a valid imputation method; do not use them in research without an explicit, justified policy. Compare which values change and retain the original df.
Task 3: Choose a Join and Account for Records (5 min)
Goal: make the retention decision visible. Input: the fictional employee and department tables. Work in pairs.
2 min: compare the left and inner joins and inspect unmatched IDs in both directions.
1 min: record which rows each approach retains and why a missing department appears.
1 min: vote on a join that retains every employee; two pairs explain their choice.
Keep: the chosen join and record-accounting note. If access fails: join the displayed ID lists on paper.
The left join retains employee IDs 1–4; IDs 1 and 4 have no matching department. The inner join keeps only IDs 2 and 3. A full join also retains department-only IDs 5 and 6. Choose according to the question and report unmatched records.
Break (10 min)
Save the join audit before continuing.
Reshape without Losing Observations
Long and Wide Describe Different Arrangements
Keep the country-year key while presenting one measurement in different layouts. The two years are recorded in the data; no missing year is invented.
The integer conversion applies to year labels generated by the reshape, not to research measurements. A reshape can expose missing combinations; investigate the source before filling them. Different measurements and units should not be pooled into one unnamed numeric column.
Task 4: Check a Reshape Round Trip (5 min)
Goal: preserve four country-year measurements Input: the long and wide examples Work in pairs: one operates and one checks; swap roles between tasks.
1 min: Predict the rows and columns in each arrangement.
2 min: Reshape to wide and back to long.
1 min: Compare keys, units and values after arranging by the same key.
1 min: Two pairs report what a failed comparison would require them to inspect.
Keep: both arrangements and their comparison. If access fails: annotate the instructor’s displayed input and output with the same decisions and checks.
Changing arrangement is not permission to aggregate, drop duplicates or impute missing combinations. Compare identity and measurements, not appearance alone.
Read and Write Derived Files
Use readr to read and write delimited files, and openxlsx for Excel files. res is the Scandinavian-country subset created earlier. These are derived outputs; keep them separate from the original data. Rerunning an export replaces an existing file at the same path, so inspect the destination in a practice project first.
Write to a file
Write to Excel
Read from Excel
Check the Export against Its Input
A CSV or delimited-text round trip does not retain every property of an R object, including factor levels. Check values and IDs as well as types. The Excel example writes row names explicitly; decide whether an extra row-label column suits a real handover.
Task 5: Inspect a File Round Trip (5 min)
Goal: verify that a derived table can be located and read. Input:res and the delimited export above. Work in pairs and swap roles.
1 min: inspect the output destination and distinguish the source from the derived file.
2 min: write the delimited file and read it back.
1 min: compare row count, country–year IDs, types and one measurement.
1 min: two pairs give the room a check beyond file existence.
Keep: the file, code and read-back check. If access fails: annotate the instructor’s displayed input and read-back table.
A saved file is useful only when its location and meaning are clear. Record identity and a value comparison check the export; file existence alone cannot. Inspect type differences instead of silently treating them as identical.
Share One Checked Analysis
Keep the Calculation Separate from Its Presentation
The shared project contains analysis.R, a notebook, an app and known-value/failure checks. Read the helper rather than treating a downloaded file as an authority. Run the following commands from the extracted project folder.
The summary states selected, observed and missing counts. Its mean is unweighted across the selected observed country values; it is not a population-weighted estimate. Both later outputs call the same helper, while their inputs and presentation remain explicit.
Task 6: Check the Shared Analysis (5 min)
Goal: connect a result to its input and reusable function Input: the project and analysis.R Work in pairs: one operates and one checks; swap roles between tasks.
1 min: Locate the source CSV and helper functions.
2 min: Select the two countries for 2007 and inspect the summary.
1 min: Compare one selected value with the original table; identify the missing-value policy.
1 min: Two pairs explain which code should be shared by a report and a dashboard.
Keep: the checked selection and documented helper contract. If access fails: annotate the instructor’s displayed input and output with the same decisions and checks.
Reuse helps keep a calculation consistent, but each interface still needs input validation, evidence checks and a statement of limitations.
Optional Reference Bank
The established selection, string and worked exercise examples below remain available for follow-up. Plotting variants now live in lesson 6. They are outside the core 100-minute plan.
The core route above is the 100-minute session. The following retained variations support follow-up learning and instructor extensions; select them according to the task. They do not add sessions to the course.
# A tibble: 6 × 6
country continent year Life_Expectancy pop gdpPercap
<fct> <fct> <int> <dbl> <int> <dbl>
1 Afghanistan Asia 1952 28.8 8425333 779.
2 Afghanistan Asia 1957 30.3 9240934 821.
3 Afghanistan Asia 1962 32.0 10267083 853.
4 Afghanistan Asia 1967 34.0 11537966 836.
5 Afghanistan Asia 1972 36.1 13079460 740.
6 Afghanistan Asia 1977 38.4 14880372 786.
Optional Reference: Additional Selection Methods
Select columns using indices, ranges, or specific patterns.
Select by index
# A tibble: 6 × 2
continent year
<fct> <int>
1 Asia 1952
2 Asia 1957
3 Asia 1962
4 Asia 1967
5 Asia 1972
6 Asia 1977
Select by index range
# A tibble: 6 × 2
continent year
<fct> <int>
1 Asia 1952
2 Asia 1957
3 Asia 1962
4 Asia 1967
5 Asia 1972
6 Asia 1977
Exclude by labels
# A tibble: 6 × 2
country continent
<fct> <fct>
1 Afghanistan Asia
2 Afghanistan Asia
3 Afghanistan Asia
4 Afghanistan Asia
5 Afghanistan Asia
6 Afghanistan Asia
# A tibble: 6 × 2
country continent
<fct> <fct>
1 Afghanistan Asia
2 Afghanistan Asia
3 Afghanistan Asia
4 Afghanistan Asia
5 Afghanistan Asia
6 Afghanistan Asia
Here we split the sentence up into a list of words
Access specific word with stringr
[1] "welcome"
We can print a specific word from the list.
Extract substring with stringr
[1] "world"
we can extract a substring
Replace substring with stringr
[1] "Hi world, welcome to R programming!"
and we can replace a substring directly
Optional Exercise Bank and Worked Answers
Choose exercises that consolidate the topic you need. Predict a result and save your attempt before inspecting the worked code. Exercises 1–5 practise selection; 7–11 practise sorting and summaries; 12–14 revisit joins, files and paths. Exercise 6 deliberately illustrates an unsupported request and should be discussed as a data-coverage check.
The worked answers below preserve the existing examples and analytical thresholds.
Exercise 2: Select all columns except year, lifeExp, and country
# A tibble: 6 × 3
continent pop gdpPercap
<fct> <int> <dbl>
1 Asia 8425333 779.
2 Asia 9240934 821.
3 Asia 10267083 853.
4 Asia 11537966 836.
5 Asia 13079460 740.
6 Asia 14880372 786.
Exercise 3: Filter for year 2002
# A tibble: 6 × 6
country continent year lifeExp pop gdpPercap
<fct> <fct> <int> <dbl> <int> <dbl>
1 Afghanistan Asia 2002 42.1 25268405 727.
2 Albania Europe 2002 75.7 3508512 4604.
3 Algeria Africa 2002 71.0 31287142 5288.
4 Angola Africa 2002 41.0 10866106 2773.
5 Argentina Americas 2002 74.3 38331121 8798.
6 Australia Oceania 2002 80.4 19546792 30688.
Exercise 4: Store and display data for Asian countries
# A tibble: 6 × 6
country continent year lifeExp pop gdpPercap
<fct> <fct> <int> <dbl> <int> <dbl>
1 Afghanistan Asia 1952 28.8 8425333 779.
2 Afghanistan Asia 1957 30.3 9240934 821.
3 Afghanistan Asia 1962 32.0 10267083 853.
4 Afghanistan Asia 1967 34.0 11537966 836.
5 Afghanistan Asia 1972 36.1 13079460 740.
6 Afghanistan Asia 1977 38.4 14880372 786.
Exercise 5: Compare Ireland and Brazil in 1977
# A tibble: 1 × 6
country continent year lifeExp pop gdpPercap
<fct> <fct> <int> <dbl> <int> <dbl>
1 Ireland Europe 1977 72.0 3271900 11151.
# A tibble: 1 × 6
country continent year lifeExp pop gdpPercap
<fct> <fct> <int> <dbl> <int> <dbl>
1 Brazil Americas 1977 61.5 114313951 6660.
Exercise 6: Diagnose a Request the Dataset Cannot Answer
The inherited request asks for African countries in 2012 with pop <= 3 and lifeExp >= 70. The packaged data end in 2007, and pop records people, so 3 is not three million. The code below preserves the original conditions and returns no observations. Explain both issues to the requester; do not silently change the year, population unit or threshold. A revised analysis requires an agreed question or another verified data source.
# A tibble: 0 × 2
# ℹ 2 variables: country <fct>, continent <fct>
Exercise 7: Lowest life expectancy
# A tibble: 1 × 6
country continent year lifeExp pop gdpPercap
<fct> <fct> <int> <dbl> <int> <dbl>
1 Rwanda Africa 1992 23.6 7290203 737.
Exercise 8: Highest life expectancy
# A tibble: 1 × 6
country continent year lifeExp pop gdpPercap
<fct> <fct> <int> <dbl> <int> <dbl>
1 Japan Asia 2007 82.6 127467972 31656.
Exercise 9: Shortest life expectancy in the Americas in 1962
# A tibble: 1 × 7
country continent year lifeExp pop gdpPercap lifeExpMonths
<fct> <fct> <int> <dbl> <int> <dbl> <dbl>
1 Bolivia Americas 1962 43.4 3593918 2181. 521.
Exercise 10: Summary statistics for life expectancy