How to Split Column Into Multiple Columns in R

In R, a common data manipulation task is splitting a column into multiple columns. This is often needed when a single column contains several pieces of information that need to be analyzed separately. Multiple methods can be used to achieve this task, depending on the structure of the data and the desired outcome. This article explores various methods to split columns in R, utilizing base R functions, string manipulation packages, and data wrangling packages.

Using strsplit( ) Function in Base R

Overview:

The strsplit() function in base R can be used to split string components of a column based on a specified delimiter.

Example:

Suppose there is a column that combines city and state, separated by a comma, and we need to split them into separate columns.

# Create a sample data frame
df <- data.frame(
  Location = c("New York, NY", "Los Angeles, CA", "Chicago, IL"),
  stringsAsFactors = FALSE
)

# Split Location into City and State
split_columns <- strsplit(df$Location, ", ")
df$City <- sapply(split_columns, `[`, 1)
df$State <- sapply(split_columns, `[`, 2)
print(df)

Output:

         Location        City State
1    New York, NY    New York    NY
2 Los Angeles, CA Los Angeles    CA
3     Chicago, IL     Chicago    IL

Using separate( ) Function from tidyr

Overview:

The separate() function from the tidyr package provides a straightforward approach to splitting one column into multiple columns.

Example:

# Load tidyr package
library(tidyr)

# Create a sample data frame
df <- data.frame(
  Location = c("New York, NY", "Los Angeles, CA", "Chicago, IL"),
  stringsAsFactors = FALSE
)

# Use separate() to split Location into City and State
df <- separate(df, Location, c("City", "State"), sep = ", ")
print(df)

Output:

         City State
1    New York    NY
2 Los Angeles    CA
3     Chicago    IL

Splitting Fixed Width Strings

Overview:

For columns where the string components have fixed widths, the substring() function in base R can be used to extract parts of the string.

Example:

If there is a date column in YYYYMMDD format, it can be split into Year, Month, and Day using substring().

# Create a sample data frame
df <- data.frame(
  Date = c("20220101", "20220202", "20230303"),
  stringsAsFactors = FALSE
)

# Split Date into Year, Month, and Day
df$Year <- substring(df$Date, 1, 4)
df$Month <- substring(df$Date, 5, 6)
df$Day <- substring(df$Date, 7, 8)
print(df)

Output:

      Date Year Month Day
1 20220101 2022    01  01
2 20220202 2022    02  02
3 20230303 2023    03  03

Using extract( ) Function from tidyr

Overview:

The extract() function in tidyr can be used to extract multiple parts of a string into separate columns using regular expressions.

Example:

Suppose we have a column containing dates in “YYYY-MM-DD” format and want to split it into separate Year, Month, and Day columns.

# Load tidyr package
library(tidyr)

# Create a sample data frame with character column
df <- data.frame(
  Date = c("2022-01-01", "2022-02-02", "2023-03-03"),
  stringsAsFactors = FALSE
)

# Use extract() to split Date into Year, Month, and Day
df <- extract(df, Date, into = c("Year", "Month", "Day"), regex = "([0-9]{4})-([0-9]{2})-([0-9]{2})", convert = TRUE)
print(df)

Output:

  Year Month Day
1 2022     1   1
2 2022     2   2
3 2023     3   3

Using cSplit( ) Function from splitstackshape

Overview:

The cSplit() function from the splitstackshape package is an efficient way to split one column into multiple columns, especially when dealing with multiple delimiters and large datasets.

Example:

# install and Load splitstackshape package
install.packages("splitstackshape")
library(splitstackshape)

# Create a sample data frame
df <- data.frame(
  Info = c("John|30|Male", "Jane|25|Female", "Mike|35|Male"),
  stringsAsFactors = FALSE
)

# Use cSplit() to split Info into Name, Age, and Gender
df <- cSplit(df, "Info", sep = "|")
print(df)

Output:

   Info_1 Info_2 Info_3
1:   John     30   Male
2:   Jane     25 Female
3:   Mike     35   Male

Splitting by Rows and Unnesting

Overview:

Sometimes, values in a column are separated by a delimiter indicating different rows. In this scenario, splitting and unnesting are required.

Example:

If a column has multiple values separated by a semicolon, indicating different rows:

# Load tidyr package
library(tidyr)

# Create a sample data frame
df <- data.frame(
  ID = 1:2,
  Values = c("a;b;c", "d;e;f"),
  stringsAsFactors = FALSE
)

# Use separate_rows() to split and unnest the Values column
df <- df %>% separate_rows(Values, sep = ";")
print(df)

Output:

# A tibble: 6 × 2
     ID Values
  <int> <chr> 
1     1 a     
2     1 b     
3     1 c     
4     2 d     
5     2 e     
6     2 f   

Conclusion

Splitting a column into multiple columns is a common yet crucial step in data preprocessing in R. Depending on the context and the type of data, R provides various methods to perform this task:

1. strsplit() in Base R:
   • Good for simple string splits based on a delimiter.
   • Requires additional steps to assign the split strings to new columns.
2. tidyr’s separate():
   • Offers a convenient and tidy approach to separate one column into multiple columns.
   • Handles missing values efficiently.
3. Substring with Fixed Width:
   • Suitable for strings with fixed widths.
   • Does not need a delimiter to perform the split.
4. tidyr’s extract():
   • Useful when regular expressions are needed to define the split.
   • Provides more control over the extraction process.
5. splitstackshape’s cSplit():
   • Efficient with large datasets and multiple delimiters.
   • Returns a data table object that can be converted to a data frame.
6. Row-wise Splitting and Unnesting:
   • Essential when the delimiter separates values that belong to different rows.
   • tidyr’s separate_rows() function can be used to achieve row-wise splitting and unnesting.

By using these techniques effectively, users can ensure that the data is appropriately structured and prepared for subsequent analysis stages, optimizing the overall analytical workflow in R.