How to split column into two columns by extracting?

user_13874036 0
Viewed 88

I would like to split columns into two and extract and keep the numbers alone in one column.

df <- data.frame(V1 = c("[1] Strongly disagree", "[2] Somewhat disagree", "[3] Neither", "[4] Somewhat agree", "[5] Strongly agree"))

                  V1
 [1] Strongly disagree
 [2] Somewhat disagree
 [3] Neither
 [4] Somewhat agree
 [5] Strongly agree

I tried using the separate function from tidyr :

tidyr::separate(df, V1, into = c("Value", "Label"), sep = "] ")

Value   Label
[1      Strongly disagree           
[2      Somewhat disagree           
[3      Neither         
[4      Somewhat agree          
[5      Strongly agree

I might be able to remove the [ with another function, but I was wondering if I can fix this in one step and wonder if there is another function that does the job.

I am trying to get this in the end

        Label        Value
 Strongly disagree     1
 Somewhat disagree     2
 Neither               3
 Somewhat agree        4
 Strongly agree        5
r
4 Answers

If you are more into base R, here is the base R solution:

df <- data.frame(V1 = c("[1] Strongly disagree", "[2] Somewhat disagree", "[3] Neither", "[4] Somewhat agree", "[5] Strongly agree"))

df$value = as.numeric(regmatches(df$V1, regexpr(r"(\d)", df$V1)))

df$V1 = regmatches(df$V1, regexpr("(?<=] ).*", df$V1, perl=TRUE))
df
#>                  V1 value
#> 1 Strongly disagree     1
#> 2 Somewhat disagree     2
#> 3           Neither     3
#> 4    Somewhat agree     4
#> 5    Strongly agree     5

Created on 2020-09-05 by the reprex package (v0.3.0)

regmatches is a base R function, which returns the matched value from the vector, it takes as an input a vector and a regexpr object.

If the first case (value column) \d is used to extract the digit. In second case, (?<=] ).* is used to return anything that matches after ] ,

user_85479860

Try this approach:

library(tidyverse)
#Data
df <- data.frame(V1 = c("[1] Strongly disagree",
                        "[2] Somewhat disagree",
                        "[3] Neither", 
                        "[4] Somewhat agree",
                        "[5] Strongly agree"))
#Mutate
df %>% separate(V1,into = c('V1','V2'),sep = ']') %>%
  mutate(V1=gsub("[[:punct:]]",'',V1))

Output:

  V1                 V2
1  1  Strongly disagree
2  2  Somewhat disagree
3  3            Neither
4  4     Somewhat agree
5  5     Strongly agree

If you want further to have other names you can use rename():

#Mutate 2
df %>% separate(V1,into = c('V1','V2'),sep = ']') %>%
  mutate(V1=gsub("[[:punct:]]",'',V1)) %>%
  rename(Label=V2,Value=V1) %>% select(c(2,1))

Output:

               Label Value
1  Strongly disagree     1
2  Somewhat disagree     2
3            Neither     3
4     Somewhat agree     4
5     Strongly agree     5
user_20808480

Another way you can try str_extractto get the value and str_remove to get rid of square brackets in the label column.

library(dplyr)
library(stringr)
df %>% 
  transmute(value = str_extract(V1, "\\d+"),
         label = str_remove(V1, "\\[.*\\]"))
#    value              label
# 1      1  Strongly disagree
# 2      2  Somewhat disagree
# 3      3            Neither
# 4      4     Somewhat agree
# 5      5     Strongly agree
user_123363050

An option with extract

library(tidyr)
library(dplyr)
df %>% 
   extract(V1, into = c("Value", "Label"), "^\\[(\\d+)\\]\\s*(.*)")
#  Value             Label
#1     1 Strongly disagree
#2     2 Somewhat disagree
#3     3           Neither
#4     4    Somewhat agree
#5     5    Strongly agree
user_37322710
Related