5 Wrangling VBD Data in R

5.1 Overview

Data retrieved through the VBD Hub, using tools such as the Hub Search or the ohvbd package, can be raw and may include missing values, inconsistent formats, or unclear variable names.

Data wrangling is the process of cleaning, transforming, and organising this data into a format that is suitable for analysis.

In this session, we will introduce key data wrangling principles, and explore commonly used techniques in VBD research, including merging datasets and standardising species names.

5.1.1 Learning Objectives

By the end of this workshop, you should be able to:

Clean and transform VBD Hub datasets ready for analysis.
Handle common data issues, such as inconsistent formats, duplicates, and missing values.
Reshape and merge datasets using standardised structures and identifiers.
Standardise species names using recognised taxonomic conventions.

5.2 Shifting Perspectives: Problems to Decisions

Real-world VBD data is rarely clean, especially if it has been collected for different purposes or recorded using different reporting standards. You may encounter:

Missing values
Inconsistent data types
Poorly named columns
Duplicate records
Data stored in inconvenient formats

Rather than treating these as isolated problems, it can be helpful to recognise common patterns which affect downstream analyses. For instance:

Inconsistent naming affects merging datasets
Mixed data types affect calculations
Wide format affects analysis and visualisations

When we think about data inconsistency patterns in this way, we shift our focus from problems to decisions. Each issue you identify in your dataset requires a decision:

Should missing values be removed or retained?
Should columns be renamed or merged?
Should data be formatted from wide to long format?

When we stop asking “what is wrong with this dataset?” and start thinking “what do I need this dataset to do?”, we can make informed decisions and prioritise the most appropriate data wrangling approaches to your data and research question.

We cannot cover every data wrangling principle within a single training session. Today, we will focus on two commonly used data wrangling techniques applied to VBD datasets:

Merging datasets
Standardising species names

5.3 Merging Datasets

In many research workflows, we use more than one dataset. It is rare for a single dataset to contain all the information needed to answer a research question.

For instance, you might have:

One dataset containing species abundance data
Another dataset containing environmental variables.

If you want to analyse how environmental factors influence species abundance, you will need to combine these into a single dataset.

We call this merging or joining datasets.

For a merge to work effectively, both datasets must share at least one common column name. This shared column is known as a key. In VBD datasets, a common key might be a species name, a location, or a date.

Let’s imagine we have two datasets which both contain a column called species.

We can merge these two datasets using the left_join() function from the dplyr package:

library(dplyr)

merged_data <- left_join(dataset_a, dataset_b, by = "species")

In this code:

dataset_a is the main dataset we want to keep.
dataset_b contains data we want to add.
by = "species" tells R to match rows using the species column.

A left join keeps all the rows from dataset_a and adds matching information from dataset_b where the species value is the same.

5.3.1 Different Merge Types

There are different types of merges or joins, each of which acts slightly differently:

A left join, left_join(), keeps all the rows from the first dataset and adds matching values from the second. This is usually the safest option, as it avoids losing data.
An inner join, inner_join(), only keeps the rows that appear in both datasets. This is useful when you only want complete matches, but it risks accidental data loss.
A full join, full_join(), keeps all the rows from both datasets. Missing values are filled with NA where matches do not exist. This can be helpful in exploratory work, but may require further data cleaning.

Choosing which merge to use depends on your research question and how you want to format rows that don’t align across datasets. If in doubt, we recommend using a left join.

5.3.2 Merging with Multiple Keys

Sometimes, a single column is not enough to uniquely identify matches. For instance, in VBD research the same species might appear in multiple locations. In this case, we can merge using multiple keys.

merged_data <- left_join(dataset_a, dataset_b, by = c("species", "location")

Here, the vector c("species", "location") tells R to match rows where both the species and location are the same.

Setting multiple key columns ensures more accurate matching, especially when working with ecological or epidemiological data, where the species might appear in multiple regions and should be accounted for with this in mind.

5.3.3 Frequent Merging Mistakes

Merging is usually straightforward, but it can sometimes produce unexpected results.

One common cause of unexpected merge results is inconsistent formatting between datasets. For instance, if dataset_a formats species names as "Ixodes ricinus" and dataset_b formats species names as "ixodes_ricinus", R will not identify these species as a match for merging.

This can result in missing values or incomplete merges.

It is useful to remember that a successful merge does not always mean a correct merge. Even if your code runs without errors, the output might not be what you were aiming for.

This is why it is important to check the merge results:

Has the number of rows changed significantly?
Are there unexpected missing values?
Do the matches look correct?

A simple check is to compare the number of rows before and after merging using nrow():

nrow(dataset_a)
nrow(merged_data)

This will check the number of rows in the original dataset and the new, merged dataset. If the number of rows increases significantly, this may include duplicate matches. If it decreases, you may have lost data.

5.4 Standardising Species Names

Species names are one of the most common causes of inconsistency when merging datasets.

Even small formatting differences can prevent datasets from merging correctly or lead to inaccuracies in later analyses.

Mismatched species names are usually caused by:

Differences in capitalisation - "Ixodes ricinus" or "IXODES RICINUS"
Using spaces or underscores - "ixodes ricinus" or "ixodes_ricinus"
Extra text, such as spp. - "Ixodes ricinus spp."
Duplicate rows for the same species.

Although these all represent the same species, R will recognise each name variation as a different value.

5.4.1 Setting All Text to Lowercase

A good starting point when standardising species names is setting all text to lowercase:

clean_data <- clean_data |>
 mutate(species = tolower(species))

Here, mutate() modifies the column, and tolower() converts all text in the specified column to lowercase, in this case the species column.

This ensures consistency in capitalisation across the dataset.

5.4.2 Removing Unwanted Characters

We can remove any unwanted characters from the species names in our datasets, such as underscores:

clean_data <- clean_data |>
 mutate(species = gsub("_", " ", species))

We use the mutate() function again, this time with gsub() to replace the characters in a string. In this case, we are replacing any underscores with a space in the species column, such as converting "ixodes_ricinus" to "ixodes ricinus".

5.4.3 Removing Additional Text

We can use the same approach to remove additional text, such as “spp.”:

clean_data <- clean_data |>
 mutate(species = gsub(" spp\\.", "", species))

Note that in this code syntax there is:

A space before spp to ensure we remove the extra text without leaving a trailing space in the species name.
A double backslash between spp and the point. In R, the point is considered a special character with specific meaning. We use the double backslash so R knows to treat the point as a full stop, rather than a special character.

5.4.4 Removing Extra Spaces

We can remove any extra spaces by using trimws() within the mutate() function:

clean_data <- clean_data |>
 mutate(species = trimws(species))

trimws() removes leading and trailing spaces from the species names. These variations can be particularly tricky to spot by simply viewing the dataset.

If species names are not standardised across our datasets:

Merges between datasets could fail.
Duplicate species entries might be created.
Analyses might produce inaccurate results.

Example: Merging Mosquito Datasets

Let’s imagine we have used ohvbd to retrieve a dataset on mosquito abundance, mosquito_abundance_data, and another dataset on mosquito habitats, mosquito_habitat_data, in order to analyse how habitat type affects patterns of abundance in mosquitos:

mosquito_abundance_data
#>              species location abundance
#> 1      Aedes aegypti    Site1        10
#> 2      Culex pipiens    Site1         5
#> 3      aedes_aegypti    Site2        12
#> 4 Culex pipiens spp.    Site2         7

mosquito_habitat_data
#>         species location habitat
#> 1 aedes aegypti    Site1   urban
#> 2 culex pipiens    Site1 wetland
#> 3 aedes aegypti    Site2   urban
#> 4 culex pipiens    Site2 wetland

We can try to merge these datasets in their raw format using left_join():

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

merged_mosquito_data <- left_join(
  mosquito_abundance_data,
  mosquito_habitat_data,
  by = c("species", "location")
)

Now, let’s check the results of our merge using head() to view the first few rows, and nrow() to see the number of rows:

head(merged_mosquito_data)
#>              species location abundance habitat
#> 1      Aedes aegypti    Site1        10    <NA>
#> 2      Culex pipiens    Site1         5    <NA>
#> 3      aedes_aegypti    Site2        12    <NA>
#> 4 Culex pipiens spp.    Site2         7    <NA>
nrow(merged_mosquito_data)
#> [1] 4

We can see that the merge has run, but something is wrong - the expected habitat data is missing. This suggests the merge did not match rows correctly.

Let’s take a step back and look at the original datasets:

head(mosquito_abundance_data)
#>              species location abundance
#> 1      Aedes aegypti    Site1        10
#> 2      Culex pipiens    Site1         5
#> 3      aedes_aegypti    Site2        12
#> 4 Culex pipiens spp.    Site2         7
head(mosquito_habitat_data)
#>         species location habitat
#> 1 aedes aegypti    Site1   urban
#> 2 culex pipiens    Site1 wetland
#> 3 aedes aegypti    Site2   urban
#> 4 culex pipiens    Site2 wetland

We can see some inconsistencies in our species names:

Differences in capitalisation
Underscores instead of spaces
Additional text

Although the locations match, both the location and species need to match exactly for the merge to work.

Now that we have identified the issues, let’s clean the species names in both datasets:

clean_mosquito_abundance_data <- mosquito_abundance_data |>
  mutate(species = tolower(species)) |>
  mutate(species = gsub("_", " ", species)) |>
  mutate(species = gsub(" spp\\.", "", species))

clean_mosquito_habitat_data <- mosquito_habitat_data |>
  mutate(species = tolower(species)) |>
  mutate(species = gsub("_", " ", species)) |>
  mutate(species = gsub(" spp\\.", "", species))

In this code, we have used the mutate() function with tolower() to convert to lowercase and gsub() to replace underscores and remove extra text.

Now that our species names are consistent, we can try merging again:

merged_mosquito_data <- left_join(
  clean_mosquito_abundance_data,
  clean_mosquito_habitat_data,
  by = c("species", "location")
)

head(merged_mosquito_data)
#>         species location abundance habitat
#> 1 aedes aegypti    Site1        10   urban
#> 2 culex pipiens    Site1         5 wetland
#> 3 aedes aegypti    Site2        12   urban
#> 4 culex pipiens    Site2         7 wetland
nrow(merged_mosquito_data)
#> [1] 4

We can see that the merge has now worked as expected - the rows match correctly, and the habitat data has been added as expected. The dataset is now in a usable format for further wrangling and analysis.

Frequent mistake: People often only clean one dataset before merging, but data for the key column needs to be consistent across both datasets for the merge to be successful.

5.4.5 Complex Cases

So far, we have covered how to approach simple inconsistencies.

However, real-world VBD datasets often contain more complex inconsistencies. These might involve:

Abbreviated genus names - "I. ricinus"
Additional descriptors - "Ixodes ricinus aff." or "Ixodes ricinus cf."
Mixed formatting within the same column.

In these cases, you may require more steps to clean the data. For instance, if we had a dataset where the species column contained these variations:

"Ixodes ricinus"
"Ixodes_ricinus"
"Ixodes ricinus spp."
"IXODES RICINUS"
"I. ricinus"

If we apply our earlier name cleaning techniques, we might standardise most of these, but "I. ricinus" would remain as is, and R would treat this as a separate value.

To identify these issues, we can inspect all the unique species names in the dataset using unique():

unique(clean_data$species)

This allows you to see all the distinct values in the species column, helping you to identify inconsistencies.

We can manually correct specific cases that cannot be handled using our earlier techniques:

clean_data <- clean_data |>
  mutate(species = ifelse(species == "i. ricinus", "ixodes ricinus", species))

Here:

ifelse() check each value in the species column.
If a value matches "i.cicinus", it is replaced with "ixodes ricinus",
If not, the value stays the same.

As species columns are typically used as a key when merging in VBD research, ensuring species names are consistent across our datasets can allow us to merge multiple datasets more confidently and reliably.

Tip: Automating our workflow feels much easier, but it is important to balance this with manual reviewing to ensure you don’t miss specific cases like these.

To support the balance between automation and accuracy, we can follow a three step approach:

Apply broad cleaning techniques (lowercase, remove underscores and extra text).
Inspect the results using unique().
Manually correct any remaining inconsistencies.

5.4.6 Resolving Species Names Using GBIF

When using large or complex datasets, you might choose to match your species names to a recognised taxonomic database.

The rgbif package can be used to help standardise species names using the Global Biodiversity Information Facility (GBIF).

We can use the name_backbone() function to try to match your species name to a standardised species name in the GBIF backbone taxonomy:

library(rgbif)

name_backbone(name = "ixodes ricinus")

This approach can help to identify spelling or formatting errors and synonymous species names.

5.5 Collaborative Task

Collaborative Task: Frequent Data Wrangling Mistakes Let’s have a go at applying what we have learnt so far by working together in groups. Each group will be given a short example of data wrangling code, along with a small dataset. The code contains errors or issues, and your job is to identify and fix them together.

As a group, you will:

Identify what the code is trying to do.
Run it and discuss any errors or unexpected results.
Edit the code so that it runs and produces the results you would expect.

You might find it helpful to share out roles, for example, one person might run the code, one might take notes, and one might guide the discussion.

The aim of this activity is not to produce perfect code, but to apply what you have learnt so far and think critically about data wrangling principles. There may be more than one fix, and explaining why you chose your approach is part of the task.

There are 4 sets of flawed code you can work through. We recommend each group tries at least 2 sets of code.

Code 1

data_a
#>             species abundance
#> 1     aedes aegypti        10
#> 2     culex pipiens        25
#> 3 anopheles gambiae         5
data_b
#>             Species trait
#> 1     aedes aegypti urban
#> 2     culex pipiens rural
#> 3 anopheles gambiae rural

# Merge datasets
merged_data <- left_join(data_a, data_b, by = "Species")

Code 2

data_c
#>              species abundance
#> 1      Aedes aegypti        10
#> 2      aedes_aegypti        12
#> 3 Aedes aegypti spp.         8

# Clean species name
clean_data <- data_c |>
  mutate(species = tolower(Species)) |>
  mutate(species = gsub("spp.", "", species))

Code 3

data_a
#>             species abundance
#> 1     aedes aegypti        10
#> 2     culex pipiens        25
#> 3 anopheles gambiae         5
data_d
#>         species trait
#> 1 aedes aegypti urban
#> 2 culex pipiens rural

# Merge datasets
merged_data <- inner_join(data_a, data_d, by = "species")

Code 4

data_e
#>         species location abundance
#> 1 aedes aegypti    site1        10
#> 2 aedes aegypti    site2        15
#> 3 culex pipiens    site1        20
data_f
#>         species location temperature
#> 1 aedes aegypti    site1          25
#> 2 culex pipiens    site1          22

# Merge datasets
merged_data <- left_join(data_e, data_f, by = "species", "location")

Hopefully, this task has reinforced the data wrangling principles we have discussed so far, and developed our understanding of how reformatting our data effectively contributes to a reproducible workflow and smoother downstream analyses.

5.6 Planning a Wrangling Workflow

We have discussed how to reframe data wrangling as a process, rather than a rigid set of steps.

When you open a new dataset, it can be tempting to start making changes immediately. To relieve this temptation, we can be prepared with a guide to a practical workflow that leaves room for flexibility to account for your specific dataset and research questions.

Inspect the data - understand the structure, variables, and data types.
Identify any issues - look for inconsistencies, missing values, and formatting problems.
Prioritise tasks - decide which issues are most important for your analysis.
Apply cleaning steps - after you understand the data, use appropriate data wrangling approaches.
Check results - ensure the changes you have made have worked in the way you expected.

Without a clear workflow, it is easy to lose track of the changes you have made, and introduce new errors, especially if you don’t check your results.

Approaching data wrangling in this way helps to ensure your work is reproducible, efficient, and aligned with your research aims.

Tip: To keep track of your workflow, add comments to your code explaining what changes you made and why:

# Convert data to all lowercase to ensure consistency across datasets.

5.7 Key Takeaways

Data wrangling is a process of preparing data for a specific research question.
VBD datasets often require cleaning and transformation before analysis.
Common issues include missing values, inconsistent formats, and duplicates.
Merging datasets requires consistent keys and careful validation.
Standardising species names is essential for accurate integration.
Validation ensures your dataset is accurate, consistent, and ready for analysis.

4 Understanding Data Curation and Standards

6 Visualising VBD Data in R