Splitting or extracting numbers from column in R dataframe

Question

My dataframe is one column and goes like:

    df<- data.frame(col = c(
    "cat-dog-cat999-dog dog-mouse 23", 
    "dog2-cat45-mouse-cat 7",
    "dog 4",
    "dog6-dog-cat3-cat 6")
    )

I want to end with a column with only the last digits in each row, so the output would have the column

(23,
7,
4,
6 )

I think there are two options: either split at the last number preceded with a space or split at the last number in each raw.

Any ideas?

Your ideas are not bad. Have you tried to execute them? Any problems during the process? It'd be great if you also show us what have you tried and what are the problems when executing your ideas, we can learn together if you do it this way :) — benson23, Feb 23 '22 at 11:31
@zx8754 I edited the question now.. So you can see that there are many spaces the column.. — Kamal, Feb 23 '22 at 11:41
Try: `sapply(strsplit(x, " ", fixed = TRUE), tail, n = 1)` Split on space, get last item. — zx8754, Feb 23 '22 at 11:45
@benson23 There are many functions and packages (gsub, stringr, readr, etc), but I can't find a function that allows a multiple delimiter (a space followed by a digit). — Kamal, Feb 23 '22 at 11:45

score 0 · Answer 1 · answered Feb 23 '22 at 11:44

One way to do it is using sub. let's assume your data frame is like this:

df<- data.frame(A=c("cat-dog-cat999-dog dog-mouse 23", 
"dog2-cat45-mouse-cat 7",
"dog 4",
"dog6-dog-cat3-cat 6"))

Then you can use:

df %>%  mutate(new_column = sub(".* ", "", A))

score 0 · Accepted Answer · answered Feb 23 '22 at 11:45

This can be done using the str_extract() function from stringr and mutate from dplyr - just loading tidyverse as this will load both stringr and dplyr.

library(tidyverse)
df<- data.frame(col = c("cat-dog-cat999-dog dog-mouse 23",
   "dog2-cat45-mouse-cat 7",
   "dog 4",
   "dog6-dog-cat3-cat 6")
)

df %>% 
  mutate(col = str_extract(col, "[0-9]*$") %>% 
           as.numeric())

Splitting or extracting numbers from column in R dataframe

2 Answers2