Image by Editor (Kanwal Mehreen) | Canva

Working with data means cleaning and preparing it for analysis. Sometimes, a column has multiple values in one cell, separated by commas, semicolons, or spaces, making analysis difficult because each value should be separate. In R, the separate_rows() function from the tidyr package can fix this, as it splits the values into new rows using a chosen delimiter.

In this article, we will discuss how to use separate_rows() for this purpose.

Load Required Packages

First, you must install and load the tidyr package if you haven’t already. You can do this by running the following commands:

# Install tidyr package if not already installed
install.packages("tidyr")

# Load tidyr package
library(tidyr)

Basic Syntax of separate_rows()

Here is the basic syntax for using separate_rows():

separate_rows(data, column, sep = ",")

• data: The data frame or tibble you want to modify
• column: The name of the column you want to separate
• sep: The delimiter that separates the values in each cell

Create a Sample Dataset

Let’s start with a dataset where the Hobbies column contains multiple values separated by commas. The following code creates this dataset using the tibble() function:

library(tibble)

# Sample dataset
survey_data <- tibble(
  Respondent = c(1, 2, 3),
  Hobbies = c("Reading,Traveling,Cooking", "Gardening,Painting", "Swimming,Traveling")
)

print(survey_data)

• The Respondent column identifies each participant
• The Hobbies column contains multiple hobbies in a single cell, separated by commas

This dataset is not yet tidy because each hobby needs to be treated as an individual entity.

Apply separate_rows()

To split the collapsed values in the Hobbies column into separate rows, use the separate_rows() function as shown below:

cleaned_data <- survey_data %>% 
  separate_rows(Hobbies, sep = ",")

print(cleaned_data)

In the original dataset, the Hobbies column has multiple values separated by commas. Using the separate_rows() function, the column is split by the comma delimiter, and each value is then placed into its own row.

Customizing the Delimiter

The sep parameter lets you specify the delimiter used to separate values. If your data uses a different delimiter, such as a semicolon (;), you can adjust the function. For example:

# Load necessary library
library(tidyverse)

# Sample dataset
survey_data <- tibble(
  Respondent = c(1, 2),
  Skills = c("R;Python;SQL", "Excel;Tableau")
)

# Apply separate_rows to split the Skills column by semicolons
cleaned_data <- survey_data %>%
  separate_rows(Skills, sep = ";")

# Print the cleaned data
print(cleaned_data)

In this example, the Skills column has values separated by semicolons (;). By setting sep = “;” in separate_rows(), the column is split at each semicolon, and each skill is then placed into a separate row.

Handling Missing Values

The separate_rows() function can also handle missing or empty values. Consider the following dataset with NAs and empty strings:

# Load necessary library
library(tidyverse)

# Sample dataset with NA and empty strings
survey_data_with_na <- tibble(
  Respondent = c(1, 2, 3),
  Hobbies = c("Reading,Traveling", NA, "Swimming,")
)

# Apply separate_rows to split the Hobbies column by commas
cleaned_data <- survey_data %>%
  separate_rows(Hobbies, sep = ",")

# Print the cleaned data
print(cleaned_data_with_na)

In this example, the second respondent’s Hobbies column contains an NA value. The third respondent’s hobbies have an empty string after the comma. The separate_rows() function handles these cases smoothly. It keeps the NA values unchanged and creates a blank row for the empty string.

Combining separate_rows() with Other tidyverse Functions

You can integrate separate_rows() into more complex workflows. For example, to count the frequency of each hobby:

hobby_counts <- survey_data %>
  separate_rows(Hobbies, sep = ",") %>%
  group_by(Hobbies) %>%
  summarize(Count = n(), .groups = "drop")

print(hobby_counts)

This code combines separate_rows() with other tidyverse functions to count the frequency of each hobby. It first uses separate_rows() to split the values in the Hobbies column into individual rows. Then, group_by() groups the data by hobby, and summarize() calculates the count of each unique hobby.

Conclusion

The separate_rows() function helps clean datasets with collapsed columns, as it splits values into separate rows. This makes data easier to analyze. separate_rows() follows tidy data principles, and the function lets you customize delimiters. It also handles missing values smoothly.

Is essence, using separate_rows() makes complex datasets more structured by transforming data into a format that’s easier to work with. Try it in your next data-cleaning project!