extracting certain letters from a a unique code in each row in R, with tidyverse only

user_7425379 0
Viewed 69

I am trying to extract the last numbers from a unique code, in each row. I do not know how to pursue this task, with tidyverse only, in R

here is an example:

structure(list(`CCGCode` = c("E38000232", "E38000237", 
"E38000004", "E38000240", "E38000006", "E38000007"), Total = c(17, 
27, 27, 43, 30, 42)), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))

By spotting the CCGCode in the data frame above you'll see the code in each row. I want to create a new column with ccg that takes only the last three digits of that code. For example, we have E38000006 in CCGCode and want into the new column ccg only the 006 because does are the last digits. How to do this.

tidyverse r
4 Answers

Here's a couple ways to do this which produce slightly different end results, hopefully one of them is along the lines of what you're looking for.

Method 1

df %>% 
  separate(CCGCode, c("CCGCode", "Last_3"), sep = -3)

Method 2

str_sub(df$CCGCode, -3) %>%
  as.tibble() %>% 
  bind_cols(df) %>%
  select(CCGCode, "Last_3" = value, Total)
user_138350410

If you want the number at the end of the CCGCode you could use gsub from base. If you need something from tidyverse, perhaps write code with a %>% pipe symbol. Try this:

#Assigning your example to df
df <-structure(list(`CCGCode` = c("E38000232", "E38000237", 
"E38000004", "E38000240", "E38000006", "E38000007"), Total = c(17, 
27, 27, 43, 30, 42)), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))

#gsub removes beginning of CCG code and assigns to df$new_col
df$CCGCode %>% gsub("E38000", "",.) ->df$new_col
user_20709990

Try str_sub() in stringr

library(dplyr)
library(stringr)

df %>%
  mutate(code = str_sub(CCGCode, -3))

# # A tibble: 6 x 3
#   CCGCode   Total code 
#   <chr>     <dbl> <chr>
# 1 E38000232    17 232  
# 2 E38000237    27 237  
# 3 E38000004    27 004  
# 4 E38000240    43 240  
# 5 E38000006    30 006  
# 6 E38000007    42 007

or using word()(also in stringr)

df %>%
  mutate(code = word(CCGCode, -3, -1, sep = "(?<=.)(?=.)"))
user_100689850

You can try this approach using tidyverse

library(tidyverse)
df <- data.frame(CCGCode = c("E38000232", "E38000237", "E38000004", "E38000240", "E38000006", "E38000007"), 
                 Total = c(17, 27, 27, 43, 30, 42))

df2 <- df %>% 
  mutate(CCG = str_extract(CCGCode, regex("\\d{3}$")))
#     CCGCode Total CCG
# 1 E38000232    17 232
# 2 E38000237    27 237
# 3 E38000004    27 004
# 4 E38000240    43 240
# 5 E38000006    30 006
# 6 E38000007    42 007
user_123363050
Related