Cleaning, Wrangling and Joining Data

DRS Training

💡 Learning Outcomes

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.

tibble [1,704 × 6] (S3: tbl_df/tbl/data.frame)
 $ country  : Factor w/ 142 levels "Afghanistan",..: 1 1 1 1 1 1 1 1 1 1 ...
 $ continent: Factor w/ 5 levels "Africa","Americas",..: 3 3 3 3 3 3 3 3 3 3 ...
 $ year     : int [1:1704] 1952 1957 1962 1967 1972 1977 1982 1987 1992 1997 ...
 $ lifeExp  : num [1:1704] 28.8 30.3 32 34 36.1 ...
 $ pop      : int [1:1704] 8425333 9240934 10267083 11537966 13079460 14880372 12881816 13867957 16317921 22227415 ...
 $ gdpPercap: num [1:1704] 779 821 853 836 740 ...

[1] 1952 2007

Data Manipulation with dplyr

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.

Do the Task
Debrief

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.

# A tibble: 6 × 1
  total_years
        <dbl>
1  242658016.
2  280296010.
3  328515855.
4  392521603.
5  472011552.
6  571971739.

Using Mutate to Classify GDP per Capita

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.

# A tibble: 10 × 4
   country      year gdpPercap GDP_Category
   <fct>       <int>     <dbl> <chr>       
 1 Afghanistan  1952      779. Low         
 2 Afghanistan  1957      821. Low         
 3 Afghanistan  1962      853. Low         
 4 Afghanistan  1967      836. Low         
 5 Afghanistan  1972      740. Low         
 6 Afghanistan  1977      786. Low         
 7 Afghanistan  1982      978. Low         
 8 Afghanistan  1987      852. Low         
 9 Afghanistan  1992      649. Low         
10 Afghanistan  1997      635. Low

Arranging Data

Sort data using arrange().

Sort in ascending order

# A tibble: 6 × 6
  country       continent  year lifeExp      pop gdpPercap
  <fct>         <fct>     <int>   <dbl>    <int>     <dbl>
1 Lesotho       Africa     1952    42.1   748747      299.
2 Guinea-Bissau Africa     1952    32.5   580653      300.
3 Eritrea       Africa     1952    35.9  1438760      329.
4 Myanmar       Asia       1952    36.3 20092996      331 
5 Burundi       Africa     1952    39.0  2445618      339.
6 Ethiopia      Africa     1952    34.1 20860941      362.

Sort in descending order

# A tibble: 6 × 6
  country       continent  year lifeExp       pop gdpPercap
  <fct>         <fct>     <int>   <dbl>     <int>     <dbl>
1 Kuwait        Asia       1952    55.6    160000   108382.
2 Switzerland   Europe     1952    69.6   4815000    14734.
3 United States Americas   1952    68.4 157553000    13990.
4 Canada        Americas   1952    68.8  14785584    11367.
5 New Zealand   Oceania    1952    69.4   1994794    10557.
6 Norway        Europe     1952    72.7   3327728    10095.

Summarizing Data

Compute summary statistics with summarise().

# A tibble: 1 × 4
  n_obs total_pop mean_life_exp range_life_exp
  <int>     <int>         <dbl>          <dbl>
1     5  22618787          75.7           2.44

Grouping Data

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.

# A tibble: 12 × 6
    year n_obs n_countries  total_pop mean_life_exp range_life_exp
   <int> <int>       <int>      <dbl>         <dbl>          <dbl>
 1  1952   142         142 2406957150          49.1           43.9
 2  1957   142         142 2664404580          51.5           43.1
 3  1962   142         142 2899782974          53.6           41.7
 4  1967   142         142 3217478384          55.7           40.1
 5  1972   142         142 3576977158          57.6           39.3
 6  1977   142         142 3930045807          59.6           44.9
 7  1982   142         142 4289436840          61.5           38.7
 8  1987   142         142 4691477418          63.2           38.8
 9  1992   142         142 5110710260          64.2           55.8
10  1997   142         142 5515204472          65.0           44.6
11  2002   142         142 5886977579          65.7           42.8
12  2007   142         142 6251013179          67.0           43.0

Task 2: Explain a Summary (5 min)

Goal: explain the records contributing to a grouped statistic. Input: the year-level summary above. Swap operator and checker.

Do the Task
Debrief

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.

Do the Task
Debrief

1 min: predict which IDs a left join will keep.
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.

Do the Task
Debrief

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.

Do the Task
Debrief

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.

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.

  selected_rows observed_lifeExp missing_lifeExp mean_lifeExp_years
1             2                2               0            75.6375

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.

Do the Task
Debrief

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.

Selection Variants

Select a Range of Columns

# A tibble: 6 × 4
  country      year lifeExp      pop
  <fct>       <int>   <dbl>    <int>
1 Afghanistan  1952    28.8  8425333
2 Afghanistan  1957    30.3  9240934
3 Afghanistan  1962    32.0 10267083
4 Afghanistan  1967    34.0 11537966
5 Afghanistan  1972    36.1 13079460
6 Afghanistan  1977    38.4 14880372

Exclude columns

# A tibble: 6 × 5
  country     continent  year lifeExp gdpPercap
  <fct>       <fct>     <int>   <dbl>     <dbl>
1 Afghanistan Asia       1952    28.8      779.
2 Afghanistan Asia       1957    30.3      821.
3 Afghanistan Asia       1962    32.0      853.
4 Afghanistan Asia       1967    34.0      836.
5 Afghanistan Asia       1972    36.1      740.
6 Afghanistan Asia       1977    38.4      786.

Exclude multiple columns

# A tibble: 6 × 4
  continent  year lifeExp gdpPercap
  <fct>     <int>   <dbl>     <dbl>
1 Asia       1952    28.8      779.
2 Asia       1957    30.3      821.
3 Asia       1962    32.0      853.
4 Asia       1967    34.0      836.
5 Asia       1972    36.1      740.
6 Asia       1977    38.4      786.

Exclude a vector of columns

# A tibble: 6 × 4
  continent  year lifeExp gdpPercap
  <fct>     <int>   <dbl>     <dbl>
1 Asia       1952    28.8      779.
2 Asia       1957    30.3      821.
3 Asia       1962    32.0      853.
4 Asia       1967    34.0      836.
5 Asia       1972    36.1      740.
6 Asia       1977    38.4      786.

Exclude a Complement of columns

# A tibble: 6 × 4
  country      year lifeExp      pop
  <fct>       <int>   <dbl>    <int>
1 Afghanistan  1952    28.8  8425333
2 Afghanistan  1957    30.3  9240934
3 Afghanistan  1962    32.0 10267083
4 Afghanistan  1967    34.0 11537966
5 Afghanistan  1972    36.1 13079460
6 Afghanistan  1977    38.4 14880372

Rename columns with select

# A tibble: 6 × 1
  Life_Expectancy
            <dbl>
1            28.8
2            30.3
3            32.0
4            34.0
5            36.1
6            38.4

Rename columns with rename()

# 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

Exclude by labels

# A tibble: 6 × 4
   year lifeExp      pop gdpPercap
  <int>   <dbl>    <int>     <dbl>
1  1952    28.8  8425333      779.
2  1957    30.3  9240934      821.
3  1962    32.0 10267083      853.
4  1967    34.0 11537966      836.
5  1972    36.1 13079460      740.
6  1977    38.4 14880372      786.

Select columns beginning with ‘co’

# 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

Select columns ending with ‘p’

# A tibble: 6 × 3
  lifeExp      pop gdpPercap
    <dbl>    <int>     <dbl>
1    28.8  8425333      779.
2    30.3  9240934      821.
3    32.0 10267083      853.
4    34.0 11537966      836.
5    36.1 13079460      740.
6    38.4 14880372      786.

Select Columns Containing ‘pop’

# A tibble: 6 × 1
       pop
     <int>
1  8425333
2  9240934
3 10267083
4 11537966
5 13079460
6 14880372

String Manipulation with stringr

Perform string operations using stringr.

Split string with stringr

[[1]]
[1] "Hello"        "world,"       "welcome"      "to"           "R"           
[6] "programming!"

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 1: Select year, lifeExp, and country

# A tibble: 6 × 3
   year lifeExp country    
  <int>   <dbl> <fct>      
1  1952    28.8 Afghanistan
2  1957    30.3 Afghanistan
3  1962    32.0 Afghanistan
4  1967    34.0 Afghanistan
5  1972    36.1 Afghanistan
6  1977    38.4 Afghanistan

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

# A tibble: 1 × 3
  lifeExp_min lifeExp_max lifeExp_mean
        <dbl>       <dbl>        <dbl>
1        23.6        82.6         59.5

Exercise 11: Life expectancy by year

# A tibble: 12 × 4
    year lifeExp_min lifeExp_max lifeExp_mean
   <int>       <dbl>       <dbl>        <dbl>
 1  1952        28.8        72.7         49.1
 2  1957        30.3        73.5         51.5
 3  1962        32.0        73.7         53.6
 4  1967        34.0        74.2         55.7
 5  1972        35.4        74.7         57.6
 6  1977        31.2        76.1         59.6
 7  1982        38.4        77.1         61.5
 8  1987        39.9        78.7         63.2
 9  1992        23.6        79.4         64.2
10  1997        36.1        80.7         65.0
11  2002        39.2        82           65.7
12  2007        39.6        82.6         67.0

Exercise 12: Explore join statements

Already demonstrated in the “Joining Data” section above.

Exercise 13: Read a file

Use the output/gapm3.xlsx file created in the file-writing example above:

    country continent year lifeExp     pop gdpPercap
1 1 Denmark    Europe 1952   70.78 4334000  9692.385
2 2 Denmark    Europe 1957   71.81 4487831 11099.659
3 3 Denmark    Europe 1962   72.35 4646899 13583.314
4 4 Denmark    Europe 1967   72.96 4838800 15937.211
5 5 Denmark    Europe 1972   73.47 4991596 18866.207
6 6 Denmark    Europe 1977   74.69 5088419 20422.901

Exercise 14: Get and split working directory path

[1] "episodes"

Further Information

📚 Key Points

Retain the question, source, units and decisions with the result.
Compare at least one output with an independent known value or source record.
A successful run is one check; interpretation and limitations still need review.

🔦 Hints

Save before restarting R and rerun from the beginning.
Keep original inputs separate from derived outputs.
Record unresolved questions instead of silently changing data to obtain an answer.

Module Summary

You can account for selection, summary, join and reshape decisions and keep an analysis reusable. Next, explore and communicate its results visually.

Additional Learning

tidyr pivoting: long/wide transformations and their assumptions.
Learner Additional Material: practice, the shared project and official references.
Reference: terms and checking reminders.

Course Overview · Previous · Next

Feedback and Register

Scan the QR code to give feedback on Digital Skills training.

Cleaning, Wrangling and Joining Data

💡 Learning Outcomes

❓ Questions

Structure & Agenda

Starting Point and Preparation

Inspect the Data before Selecting

Begin with a Research Question

Load the Lesson Packages

Exploring the Gapminder Dataset

Data Manipulation with dplyr

Selecting Columns

Select Individual Columns

Filtering Rows

Filter for Belgium

Filter with multiple conditions

Filter using %in% operator

Combining Select and Filter

Task 1: Check a Country–Year Selection (5 min)

Derive and Summarise with Meaning

Mutating Data

Using Mutate to add new columns

Using Mutate to Keep Only New Columns

Using Mutate to Classify GDP per Capita

Arranging Data

Sort in ascending order

Sort in descending order

Summarizing Data

Grouping Data

Task 2: Explain a Summary (5 min)

Joining Data

Example data for joining

Inspect the Join Keys

Inner join

left join

right join

full join

Missing-Value Decisions with tidyr

Sample data with missing values

Remove rows with any NA

Remove rows with NA in specific column

Replace NA Values

Task 3: Choose a Join and Account for Records (5 min)

Break (10 min)

Reshape without Losing Observations

Long and Wide Describe Different Arrangements

Task 4: Check a Reshape Round Trip (5 min)

Read and Write Derived Files

Write to a file

Write to Excel

Read from Excel

Check the Export against Its Input

Task 5: Inspect a File Round Trip (5 min)

Share One Checked Analysis

Keep the Calculation Separate from Its Presentation

Task 6: Check the Shared Analysis (5 min)

Optional Reference Bank

Selection Variants

Select a Range of Columns

Exclude columns

Exclude multiple columns

Exclude a vector of columns

Exclude a Complement of columns

Rename columns with select

Rename columns with rename()

Optional Reference: Additional Selection Methods

Select by index

Select by index range

Exclude by labels

Exclude by labels

Select columns beginning with ‘co’

Select columns ending with ‘p’

Select Columns Containing ‘pop’

String Manipulation with stringr

Split string with stringr

Access specific word with stringr

Extract substring with stringr

Replace substring with stringr

Optional Exercise Bank and Worked Answers

Exercise 1: Select year, lifeExp, and country

Exercise 2: Select all columns except year, lifeExp, and country