split character column into two to create two separate columns

user_12674384 0
Viewed 71

I need to split a character column into two separate columns.

As an example: 

df1 <- data.frame(city_code=c("New York(NY)","London(LO)","Roma(RO)"))

I need an output like: 

df2 <- data.frame(city=c("New York","London","Roma"), code=c("NY","LO","RO"))
r split
4 Answers

In base R:

df2 <- data.frame(
    city = sub("\\(.*\\)", "", df1$city_code),
    code = sub(".*\\((.*)\\)", "\\1", df1$city_code))

> df2 
      city code
1 New York   NY
2   London   LO
3     Roma   RO
user_104424230

You can use sub and strsplit to create a new table with two columns:

do.call(rbind, strsplit(sub(")$", "", df1$city_code), "\\("))
colnames(df2) <- c("city", "code")
df2
#     city       code
#[1,] "New York" "NY"
#[2,] "London"   "LO"
#[3,] "Roma"     "RO"

Or using regexpr with regmatches.

df2 <- data.frame(city = regmatches(df1$city_code, regexpr(".*(?=\\()", df1$city_code, perl=TRUE))
 , code = regmatches(df1$city_code, regexpr("(?<=\\().*(?=\\))", df1$city_code, perl=TRUE)))
#      city code
#1 New York   NY
#2   London   LO
#3     Roma   RO

Or using sub as @Daniel-O and @Jaap have already done.

df2 <- data.frame(city = sub("\\(.*", "", df1$city_code)
 , code = sub(".*\\((.+)\\)", "\\1", df1$city_code))
df2
#      city code
#1 New York   NY
#2   London   LO
#3     Roma   RO

Or in case the string ends all the time with (??) using substr.

n <- nchar(df1$city_code)
df2 <- data.frame(city = substr(df1$city_code, 1, n-4)
 , code = substr(df1$city_code, n-2, n-1))
df2
#      city code
#1 New York   NY
#2   London   LO
#3     Roma   RO
user_104885040

A tidyverse response:

df2 <- df1 %>%
  separate(city_code, into = c('city', 'code'), sep = "\\(") %>%
  mutate(code = gsub(')', '', code))
user_107518830

Try to do it this way. \\( and \\) used to separate

library(tidyverse)
df1 %>% 
  separate(city_code, c("city", "code", NA), sep = "\\(|\\)")
user_120254830
Related