Module 2: Handling Data in R

knitr::opts_chunk$set(
 echo = TRUE,
 message = FALSE,
 warning = FALSE
)
load(".RData")

Module 2: Handling Data in R

Now that we have a basic understanding of R’s syntax and the RStudio application, we can start working with data!

This module will cover:

• Working with built-in datasets in R
• Understanding and handling missing data (NA values)
• Creating synthetic datasets for testing and examples
• Different ways to view and explore data in RStudio
• Extracting specific data from vectors and data frames
• Loading and saving data locally and online
• Common pitfalls and gotchas when working with R

By the end of this module, you’ll be comfortable loading data, inspecting it for problems, and extracting the parts you need for analysis. We’ll use both base R and tidyverse approaches, helping you understand both the traditional and modern ways of working with data in R.

Built-in Datasets

Many R packages include example datasets. Let’s look at two:

# View the first few rows of the starwars data set
library(dplyr)
head(starwars)

# Another dataset that includes the fuel efficiency of 32 cars.
mean(mtcars$mpg)

[1] 20.09062

These datasets are frequently used in help files, online discussions, or blog posts demonstrating R functions.

Notice the NA hair color in the table? That’s how R represents missing data.

Missing Data in R

R uses NA (Not Available) to represent missing values. These are different from zeros or empty strings. When you perform calculations with NA values, the result will be NA unless you explicitly tell R how to handle the missing data. This is safer than having R make assumptions about missing values!

# Vector with missing values
scores_with_na <- c(85, NA, 92, 78, NA, 88)

# Mean calculation with NA values
mean(scores_with_na)  # Returns NA

[1] NA

# Mean calculation excluding NA values
mean(scores_with_na, na.rm = TRUE)  # Returns actual mean

[1] 85.75

# Count missing values
sum(is.na(scores_with_na))  # Number of NA values

[1] 2

# Remove NA values
scores_complete <- na.omit(scores_with_na)
length(scores_complete)

[1] 4

Most R functions have an na.rm parameter to handle missing values. Setting na.rm = TRUE tells R to remove NA values before performing calculations. However, it’s usually better to clean the dataset of missing values first, and then run functions without the na.rm parameter.

# More examples with the starwars dataset
# How many missing heights?
sum(is.na(starwars$height))

[1] 6

# Better: How many are missing, and how many are not?
summary(is.na(starwars$height))

   Mode   FALSE    TRUE 
logical      81       6 

# WARNING: always use NA-specific functions like is.na() and not equals signs for NA
summary(starwars$height == NA) # Does not work - never use == with NA!

   Mode    NA's 
logical      87 

# Average height excluding NA values
mean(starwars$height, na.rm = TRUE)

[1] 174.6049

When working with real data, it’s important to understand why values are missing and make informed decisions about how to handle them.

Generating Synthetic Data

Example data is important for testing your analysis, getting help from the R community, or collaborating across organizations. While R comes with built-in example datasets, they aren’t usually relevant to education. Creating synthetic (fake) data is an easy and powerful way to test your code.

Let’s create a simulated dataset of student information:

# Number of students
n <- 50

# Create variables
student_id <- seq(1, n) # Simple sequence of numbers 1 to 50
grade <- sample(c(7,8,9,10,11,12), n, replace = TRUE)
        # ^ randomly sample from list of grades, with replacement
secondary <- grade > 8  # TRUE if grade 9 or higher

# Generate test scores
math_score <- round(rnorm(n, mean = 75, sd = 10)) # Normal distribution
english_score <- round(runif(n, min = 50, max = 100)) # Uniform distribution

# Combine into a data frame
synthetic_data <- data.frame(
  student_id,
  grade,
  secondary,
  math_score,
  english_score
)

# View the first few rows
head(synthetic_data)

Don’t worry about memorizing these functions! Keep a copy of this code and modify it when you need to generate different types of synthetic datasets.

Saving and Loading Data on your computer

R can save data in many ways, but you’ll mostly use two formats: R object files (.RData) and CSV text files (.csv).

R object files

If you need to save data for later use in R, and want to be absolutely certain that the data will be exactly the same when you load it back, use R object files:

# Save a single R object
save(synthetic_data, file = "synthetic_data.RData")

# Load the data back
load("synthetic_data.RData")

R object files can contain multiple datasets - they can even save everything in your current session! The command save.image() saves everything in your environment to a file called .RData in your project directory. This file is typically re-loaded next time you start R.

Let’s experiment with the RStudio global environment.

CSV Files

CSV (Comma-Separated Values) files are the most compatible data format across different programs. They’re just text files where commas separate columns and new lines separate rows. They look like this:

student_id,grade,math_score
1,9,85
2,10,92
3,9,78
4,11,88

While most programs can read and write CSV files, there are some common issues to watch for:

• Text data (like names) often needs double-quotes around it, especially if it contains commas
• Different programs handle column names differently - some use quotes, some allow spaces
• Mac and Windows computers may save text files slightly differently
• Even when saving from R and loading back into R, data might change slightly depending on the options used

Because of these issues, always verify your data after reading a CSV file, especially when sharing between different programs.

# Save data to CSV
write.csv(synthetic_data, "synthetic_data.csv")

# To read data back, use RStudio's Import Dataset tool:
# File > Import Dataset > From Text (readr)

Reading Data from Remote or Online Sources

R can read data from many remote sources including databases, online files, and web pages.

We won’t cover database connections in this workshop since they require special permissions from your IT department. However, once set up, you will use the same commands we’re learning to work with the data.

Data files hosted on the web

R can read online files just like local files on your computer. Let’s load some example data from GitHub:

# Load survey data from URL
survey_data1 <- read.csv("https://github.com/grousell/MISAR/raw/refs/heads/master/Data/survey_data_1.csv")

survey_data2 <- read.csv("https://github.com/grousell/MISAR/raw/refs/heads/master/Data/survey_data_2.csv")

# Look at the first few rows
head(survey_data1)

NA

Notice we use the same read.csv() function as before!

Data files in R packages

Many R packages include example datasets:

# install.packages("devtools")
devtools::install_github("grousell/obt")
# library(obt) #optional
head(obt::ele_rc)

To explore datasets in installed packages: 1. Click “Global Environment” in the Environment pane 2. Select the package name 3. Click on any dataset to add it to your session 4. Explore it like any other dataset

The :: syntax

You may have noticed this new syntax: obt::ele_rc and devtools::install_github

This notation is useful in two ways:

1. Using a single function from a package without loading the whole package. For example, we only need install_github() from devtools once, so we don’t need to load the entire package.

2. Making it clear where data comes from. When you see obt::ele_rc, you know this data comes from the obt package, not your local environment. This helps avoid confusion when you have lots of data loaded.

Viewing Data

Let’s explore different ways to examine our synthetic_data:

# Get a quick summary of the data frame
summary(synthetic_data)

   student_id        grade       secondary         math_score    
 Min.   : 1.00   Min.   : 7.00   Mode :logical   Min.   : 57.00  
 1st Qu.:13.25   1st Qu.: 8.00   FALSE:14        1st Qu.: 69.00  
 Median :25.50   Median :10.00   TRUE :36        Median : 76.50  
 Mean   :25.50   Mean   : 9.88                   Mean   : 75.84  
 3rd Qu.:37.75   3rd Qu.:12.00                   3rd Qu.: 82.75  
 Max.   :50.00   Max.   :12.00                   Max.   :101.00  
 english_score  
 Min.   :52.00  
 1st Qu.:63.50  
 Median :75.00  
 Mean   :74.78  
 3rd Qu.:83.00  
 Max.   :99.00  

# Look at just the first few rows
head(synthetic_data)

# Or the last few rows
tail(synthetic_data)

# Get a glimpse of the structure
glimpse(synthetic_data)

Rows: 50
Columns: 5
$ student_id    <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,…
$ grade         <dbl> 12, 8, 7, 8, 10, 8, 11, 11, 12, 11, 12, 9, 12, 9, …
$ secondary     <lgl> TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, TRUE, TRUE…
$ math_score    <dbl> 85, 67, 60, 81, 66, 61, 64, 66, 76, 88, 76, 88, 88…
$ english_score <dbl> 97, 91, 83, 72, 65, 89, 70, 60, 77, 80, 78, 81, 62…

# Open the full data viewer
View(synthetic_data)  # Note: Capital V is required!

In the Environment pane, clicking the small “play” button next to synthetic_data provides the same information as glimpse(synthetic_data).

For any data frame, clicking the table icon (looks like a grid) in the Environment pane will open the same viewer as running the View(synthetic_data) command.

These tools help us quickly understand our data’s structure and contents.

Extracting Data

Working with Vectors

Remember that a vector is just a single list of one type of data (like numbers or text). Let’s look at the math_score vector:

The base R way:

# Extract the first value in math_score
synthetic_data$math_score[1]

[1] 85

# Extract the first three values of math_score
synthetic_data$math_score[1:3]

[1] 85 67 60

# Extract all math scores over 80
synthetic_data$math_score[synthetic_data$math_score > 80]

 [1]  85  81  88  88  88 101  83  83  87  84  83  86  82  89  90  82  81

The tidyverse way:

synthetic_data |>
  pull(math_score) |>
  head(3)

[1] 85 67 60

synthetic_data |>
  filter(math_score > 80) |>
  pull(math_score)

 [1]  85  81  88  88  88 101  83  83  87  84  83  86  82  89  90  82  81

Always remember to read the pipe symbol |> as “THEN.” In the first example, you can read it as:

1. Start with the synthetic_data dataframe, THEN
2. Pull out just the math_score vector, THEN
3. Extract only the first 3 entries (from the head or top of the vector)

Quiz: How would you read the second example in words?

Working with Data Frames

A data frame is a table where each column is a vector. Let’s look at ways to extract parts of our data frame:

The base R way:

# Extract one column by name
synthetic_data[, "math_score"]

 [1]  85  67  60  81  66  61  64  66  76  88  76  88  88 101  70  83  71
[18]  83  87  79  84  83  77  72  73  76  86  69  57  82  80  77  79  61
[35]  89  90  73  82  64  70  69  65  77  79  64  63  76  80  81  74

# Shortcut!
synthetic_data$math_score

 [1]  85  67  60  81  66  61  64  66  76  88  76  88  88 101  70  83  71
[18]  83  87  79  84  83  77  72  73  76  86  69  57  82  80  77  79  61
[35]  89  90  73  82  64  70  69  65  77  79  64  63  76  80  81  74

# Extract multiple columns by name
synthetic_data[, c("student_id", "math_score")]

# Extract specific rows (where math_score > 80)
synthetic_data[synthetic_data$math_score > 80, ]

This syntax can be confusing! Let’s look at the more readable tidyverse way:

# Extract specific columns
synthetic_data |>
  select(student_id, math_score)

# Extract specific rows
synthetic_data |>
  filter(math_score > 80)

# Extract both rows and columns
synthetic_data |>
  filter(math_score > 80) |>
  select(student_id, math_score)

Let’s read the first example:

1. Start with the synthetic_data dataframe, THEN
2. Select just the student_id and math_score columns

Quiz: How would you read the second and last examples with both filter and select?

Computing new columns

Let’s look at three common ways to compute new information from our existing data.

Creating calculated columns with mutate()

Sometimes we need to combine information from multiple columns. For example, we might want to calculate an average score for each student:

# Add a new column for average score
synthetic_data_with_average <- synthetic_data |>
  mutate(average_score = (math_score + english_score) / 2)

head(synthetic_data_with_average)

Let’s read this step by step:

1. We start with synthetic_data, THEN
2. Create a new column called average_score by:
   • Adding math_score and english_score together
   • Dividing by 2 to get the average
3. Save the entire result to a new data frame called synthetic_data_with_average
4. Print the first few rows to verify it worked

Grouping data for analysis

We often want to analyze data by categories. Let’s look at how scores vary across grade levels:

# Calculate average scores by grade
synthetic_data |>
  group_by(grade) |>
  summarise(
    avg_math = mean(math_score),
    avg_english = mean(english_score)
  )

Reading the pipe: Start with synthetic_data, THEN group the rows by grade level, THEN calculate summary statistics (average math and english scores) for each grade group.

Counting observations in groups

Sometimes we just need to know how many items fall into different categories:

# Count students in each grade
synthetic_data |>
  group_by(grade) |>
  summarise(n_students = n())

Reading the pipe: Start with synthetic_data, THEN group the rows by grade level, THEN count how many students are in each grade using the n() function.

Data Cleaning

When we load data from external sources, we often need to “clean” it before we can use it for analysis. Common data cleaning tasks include:

• Fixing inconsistent data entry (e.g., “Grade 9” vs “9” vs “Nine”)
• Handling missing values (empty cells or special codes like -99 need to be converted to NA)
• Removing duplicate entries
• Correcting data types (e.g., numbers stored as text)
• Standardizing column names and values
• Identifying and handling outliers
• Dealing with extra spaces or special characters

Data cleaning is often the most time-consuming part of data analysis, but it’s crucial for getting accurate results. Let’s look at some common data cleaning tasks using our survey data:

Handling correct data types

Above, we imported survey_data1 and survey_data2. Open those dataframes using the Environment pane and take a look at the types. Everything was imported as characters! The reason is that the numerical responses include some text, and R, to avoid damaging the data by trying to convert to numbers, just imported everything as text.

# Take a look at one variable
survey_data2$session_knowledge |>
    unique() # Show only the unique values

[1] "4"                 "3"                 "2"                
[4] "5 - Significantly" "1 - Not at All"    NA                 

We can see the values are numeric, from a Likert-type scale. We usually consider this type of data to be ordinal, rather than continous, and in R, that is a special variable type called an ordered factor.

survey_data2 <- survey_data2 |>
  mutate(
    session_knowledge = factor(
      session_knowledge,
      levels = c("1 - Not at All", "2", "3", "4", "5 - Significantly"),
      ordered = TRUE
    )
  )

# Check the result
survey_data2$session_knowledge |>
  levels()

[1] "1 - Not at All"    "2"                 "3"                
[4] "4"                 "5 - Significantly"

Now, we have kept the combination of text and numbers, but the data is now ordered. This will be useful later if we try to plot the data. For example, a bar plot would automatically put the 5 levels in the correct order, and the helpful text would be part of the legend.

If we really wanted this data to be numeric (perhaps the scale truly represents a continuous variable), we can extract just the numbers and convert it to a numeric variable:

# Extract just the first character and convert to numeric
survey_data2 <- survey_data2 |>
  mutate(
    session_knowledge_num = substr(session_knowledge, 1, 1) |>
      as.numeric()
  )

# Now we can perform computations as a numerical vector
mean(survey_data2$session_knowledge_num, na.rm = TRUE)

[1] 3.644628

Note that converting ordinal data to numeric should be done carefully and with purpose. While it can enable additional statistical operations, it assumes the distances between levels are equal and meaningful. We have kept the origional ordered factor and created a seperate numeric version. Even if the numeric version is inappropriate to the categorical Likert-type response, it may still be useful for finding outliers. We could create numerical versions of all the items, and add the columns to identify extremes.

Correcting inconsistent data entry and missing values

Let’s create a small dataset with some common data problems:

# Create messy data
messy_data <- data.frame(
  student_id = 1:5,
  grade = c("Grade 9", "9", "grade 9", "Grade 9", "-99"),
  score = c(85, 92, 78, -99, 88)
)

# View the messy data
messy_data

# Step 1: Fix inconsistent grade entries that start with "Grade"
clean_data <- messy_data |>
  mutate(
    grade = str_replace(grade, "Grade 9", "9")
  )

# Step 2: Fix grades that start with lowercase "grade"
clean_data <- clean_data |>
  mutate(
    grade = str_replace(grade, "grade 9", "9")
  )

# Step 3: Replace -99 with NA in both grade and score
clean_data <- clean_data |>
  mutate(
    grade = if_else(grade == "-99", NA_character_, grade),
    score = if_else(score == -99, NA_real_, score)
  )

# View the cleaned data
clean_data

In this example, we used:

• str_replace() to handle text replacements in the grade column
• if_else() to replace specific values with NA in the score column
• NA_character_ and NA_real_ to specify the type of NA value

Removing duplicates

Let’s create a small dataset with some duplicate entries:

# Create data with duplicates
duplicate_data <- tibble(
  ResponseID = c(101, 101, 102, 103, 103),
  date = c("2023-01-15", "2023-01-15", "2023-02-01", "2023-01-01", "2023-02-01"),
  score = c(85, 85, 92, 78, 82)
)

# View the data
duplicate_data

# Remove exact duplicates (Entire entry is a duplicate)
unique_data <- duplicate_data |>
  distinct()

# View result after removing exact duplicates
unique_data

# Keep only most recent entry for each ResponseID (e.g., someone participated twice)
latest_data <- duplicate_data |>
  group_by(ResponseID) |>
  slice_max(date) |>
  ungroup()

# View final result
latest_data

In this example, we first remove exact duplicates with distinct(), then use slice_max() to keep only the most recent entry for each ResponseID. This is a common task when dealing with multiple responses from the same participant.

Identifying Outliers

Let’s add some outliers to our synthetic data and then identify them:

# Add some outlier math scores
synthetic_data$math_score[1:3] <- c(15, 20, 125)  # Add unrealistic scores

# Calculate summary statistics
outlier_analysis <- synthetic_data |>
  summarise(
    q1 = quantile(math_score, 0.25),
    q3 = quantile(math_score, 0.75),
    iqr = q3 - q1,
    lower_bound = q1 - 1.5 * iqr,
    upper_bound = q3 + 1.5 * iqr
  )

# Find outliers
outliers <- synthetic_data |>
  filter(
    math_score < outlier_analysis$lower_bound |
    math_score > outlier_analysis$upper_bound
  )

# View the outliers
outliers |>
  select(student_id, math_score)

# Remove outliers from our data
synthetic_data_clean <- synthetic_data |>
  filter(
    math_score > outlier_analysis$lower_bound,
    math_score < outlier_analysis$upper_bound
  )

# Verify the outliers are gone
summary(synthetic_data_clean$math_score)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
  57.00   69.50   77.00   76.17   82.50  101.00 

This example uses the Interquartile Range (IQR) method to identify outliers. Values that fall more than 1.5 times the IQR below the first quartile or above the third quartile are considered outliers. When dealing with real data, you should investigate outliers carefully - they might represent data entry errors, or they might be valid but unusual cases.

Joining Data

Sometimes we need to combine two different datasets. For example, you might have: - Student demographic information in one spreadsheet - Test scores in another spreadsheet - Survey responses in yet another

In Excel, you might use VLOOKUP to match information between spreadsheets. In R and most Database systems, we use “joins” to combine datasets based on a shared identifier column, like student number. This is also how Microsoft Access and PowerBI combine tables as well.

Let’s look at our survey data:

# Look at the first few rows of each dataset
head(survey_data1)

head(survey_data2)

Both datasets have a RespondentID column that we can use to match rows between the datasets. However, not every respondent appears in both datasets:

# Which respondents are only in dataset 1?
survey_data1 |>
  filter(!RespondentID %in% survey_data2$RespondentID) |>
  pull(RespondentID) |>
  length()

[1] 13

# Which respondents are only in dataset 2?
survey_data2 |>
  filter(!RespondentID %in% survey_data1$RespondentID) |>
  pull(RespondentID) |>
  length()

[1] 0

There are four main ways to join datasets:

1. Inner join: Keep only rows that match in both datasets
2. Left join: Keep all rows from the first dataset, add matching data from the second
3. Right join: Keep all rows from the second dataset, add matching data from the first
4. Full join: Keep all rows from both datasets

# Inner join - only matching rows
inner_joined <- survey_data1 |>
  inner_join(survey_data2, by = "RespondentID")

# Left join - all rows from survey_data1
left_joined <- survey_data1 |>
  left_join(survey_data2, by = "RespondentID")

# Compare the number of rows
nrow(inner_joined)  # Fewer rows - only matching RespondentIDs

[1] 252

nrow(left_joined)   # Same number of rows as survey_data1

[1] 265

Usually, you’ll use left_join() to add information to your main dataset. Any respondents that don’t have matching data will get NA values in the columns from the second dataset. Take a look at left_joined to see what I mean.

End of Module 3

In this module, we’ve covered a lot of ground! You now understand how to:

• Work with built-in datasets and create synthetic data for testing
• Handle missing data with NA values
• Load data from files and online sources
• View and explore data in RStudio
• Extract specific parts of your data using both base R and tidyverse approaches
• Clean messy data and handle common data problems
• Join datasets together using different types of joins

Congratulations on making it through the most challenging part! Data cleaning and preparation typically takes up most of our time in real-world analysis, but having these skills means you can work with almost any dataset in R and RStudio.

In the next module, we’ll focus on something more rewarding - creating high-quality figures and reports using R Markdown. You’ll learn how to present your analysis professionally and create reproducible documents that combine text, code, and visualizations.