Chapter 9 Lab 8: Advanced Data Wrangling and Importing Ecological Datasets

Objectives:

  1. To read external data files (.txt, .csv, .xlsx) into R
  2. To identify and handle missing data
  3. To select specific variables and add new calculated columns
  4. To summarize data by category

Real ecological data rarely arrives ready to analyze. This lab covers how to bring outside files into R, clean them up, and reshape them into something usable.

9.1 Reading text-delimited files (.txt / .tsv)

Text delimited files contain plain, unformatted text, where columns are separated by a tab character (\t). To read them, use read.table():

# read.table(file = "data_table.txt", sep = "\t", header = T)

Question 1

  • Download the table from here and read it into R. Add the code.
  • What do the sep and header options mean? (Use ?read.table to check)
  • What happens if you remove the sep and header options?

9.2 Reading comma-separated files (.csv)

CSV files are the same idea, but columns are separated by commas instead of tabs. Use read.csv():

Question 2

  • Download the table from here and read it into R using read.csv(). Add the code and an explanation of the syntax.
  • Is this table any different from the one in Question 1?

9.3 Reading Excel files (.xlsx)

Excel files are extremely common in the biological sciences, especially from collaborators who aren’t programmers. Use the read_xlsx() function from the readxl package:

Question 3

  • Install the readxl package
  • Download the excel spreadsheet from here
  • Using read_xlsx(), read the sheets into two separate objects (check your spreadsheet’s tab names first)
  • Is this table any different from the ones in Questions 1 and 2?

9.4 Dealing with missing data

Ecological field data almost always has gaps — a sensor failed, a sample was lost, an observer skipped a field. R represents missing values as NA.

eco_data <- data.frame(
  site = c("A","B","C","D","E"),
  temp_c = c(18.2, NA, 21.4, 19.8, NA),
  ph = c(7.1, 6.9, NA, 7.3, 7.0)
)
eco_data
##   site temp_c  ph
## 1    A   18.2 7.1
## 2    B     NA 6.9
## 3    C   21.4  NA
## 4    D   19.8 7.3
## 5    E     NA 7.0

Check for missing values with is.na():

is.na(eco_data)
##       site temp_c    ph
## [1,] FALSE  FALSE FALSE
## [2,] FALSE   TRUE FALSE
## [3,] FALSE  FALSE  TRUE
## [4,] FALSE  FALSE FALSE
## [5,] FALSE   TRUE FALSE

Count how many missing values are in a column:

sum(is.na(eco_data$temp_c))
## [1] 2

You have a few options for handling missingness:

# Remove any row with at least one NA
na.omit(eco_data)
##   site temp_c  ph
## 1    A   18.2 7.1
## 4    D   19.8 7.3
# Calculate a mean while ignoring NAs
mean(eco_data$temp_c, na.rm = TRUE)
## [1] 19.8

Question 4

  • Using the eco_data example (or a similar data frame of your own with at least 8 rows and some NA values), calculate how many missing values exist in each numeric column
  • Calculate the mean of each numeric column while ignoring NAs
  • In your own words: what is the risk of simply deleting every row with a missing value (na.omit), versus keeping the row and ignoring the NA in your calculations (na.rm=TRUE)?

9.5 Selecting variables and adding calculated columns

Recall subset() from Lab 3 for filtering rows. The select argument lets you keep or drop specific columns:

# Keep only site and temp_c
subset(eco_data, select = c(site, temp_c))
##   site temp_c
## 1    A   18.2
## 2    B     NA
## 3    C   21.4
## 4    D   19.8
## 5    E     NA
# Drop the ph column
subset(eco_data, select = -c(ph))
##   site temp_c
## 1    A   18.2
## 2    B     NA
## 3    C   21.4
## 4    D   19.8
## 5    E     NA

To add a new calculated column, you can assign directly with $, or use mutate() from tidyverse (Lab 5):

eco_data$temp_f <- (eco_data$temp_c * 9/5) + 32
eco_data
##   site temp_c  ph temp_f
## 1    A   18.2 7.1  64.76
## 2    B     NA 6.9     NA
## 3    C   21.4  NA  70.52
## 4    D   19.8 7.3  67.64
## 5    E     NA 7.0     NA

Question 5

  • Remove the ph column from your own ecological data set and explain why you might want to do this in a real analysis
  • Add a new calculated column of your choice (a unit conversion, an index, a rounded value, etc.) using either $ or mutate(). Add the code and the resulting table.

9.6 Summarizing data by category

Combine what you learned in Lab 5 (group_by, summarize) with today’s cleaned-up data to produce site-level summaries:

library(tidyverse)
eco_data %>%
  group_by(site) %>%
  summarize(mean_temp = mean(temp_c, na.rm = TRUE))
## # A tibble: 5 × 2
##   site  mean_temp
##   <chr>     <dbl>
## 1 A          18.2
## 2 B         NaN  
## 3 C          21.4
## 4 D          19.8
## 5 E         NaN

Question 6

  • Using one of the data sets you imported in Questions 1-3, group by a categorical column and summarize a numeric column (mean, min, max, or count). Add the code and result.
  • What ecological or biological question could this summary help answer?