Cleaning string and number columns in an R data.table-CodePudding

I try to clean a data.frame where I have columns with text and also numbers. I would like to exclude the numbers in the example column "name" and only take the first number (without string) for the column "number".

I am using data.table and created this frame:

df <- data.frame(x=c(1,2,3,4,5,6,7,8),
                 name=c('Tom', 'Maria 3', 'Ina.2', 'Anna13', 'Tim2a', 'Zoé', 'Mark_1', 'Bea: 2'), 
                 number=c('12, 13', '11/12', '3b', '12, 13', '134z', 'number 14', 'B3', '3-5'))

As described above, I would expect a cleaned table like this:

df_cleaned <- data.frame(x=c(1,2,3,4,5,6,7,8),
                         name=c('Tom', 'Maria', 'Ina', 'Anna', 'Tim', 'Zoé', 'Mark', 'Bea'),
                         number=c('12', '11', '3', '12', '134', '14', '3', '3'))

Thank you very much for your reply:)

CodePudding user response：

You can use readr::parse_number which does exactly that.

readr::parse_number(df$number)
#[1]  12  11   3  12 134  14   3   3

Or in base R -

as.numeric(sub('.*?(\\d ).*', '\\1', df$number))

To clean up the names, you can use the regex -

df$name <- sub('([ :_.]|\\d).*', '', df$name)
#[1] "Tom"   "Maria" "Ina"   "Anna"  "Tim"   "Zoé"   "Mark"  "Bea"

CodePudding user response：

Does this work:

library(dplyr)
library(stringr)
df %>% mutate(name = str_extract(name, '[A-Za-z] '), number = parse_number(number))
  x  name number
1 1   Tom     12
2 2 Maria     11
3 3   Ina      3
4 4  Anna     12
5 5   Tim    134
6 6    Zo     14
7 7  Mark      3
8 8   Bea      3