Remove Dollar Signs and comma from string in R

user_6414727 0
Viewed 527

This seems to be a common problem in other languages from all the answers I've seen, but I'm having this problem in a R dataframe. I've got data that was imported with dollar signs and commas and I cannot seem to get them out. I've tried all of the str_sub type things that I can think of and cannot get them out. It seems like they just get ignored.

test.df <- structure(list(week_ending = structure(c(18747, 18747, 18747,18747, 18747, 18747, 18747, 18747, 18747, 18747), class = "Date"),store_num = c(7005, 7005, 7005, 7005, 7005, 7005, 7005, 7748,7748, NA), units = c("116", "1", "6", "2", "1", "1", "2","1", "46", "2,539"), cost = c("$699.36", "$14.29", "$34.02","$0.90", "$11.47", "$1.28", "$2.16", "$1.89", "$165.81","$16,250.83 "), dollars = c("$1,564.07 ", "$24.99", "$54.00","$9.98", "$24.99", "$2.99", "$7.98", "$4.99", "$360.11","$37,465.88 "), item_description = c("$1,564.07 ", "$24.99","$54.00", "$9.98", "$24.99", "$2.99", "$7.98", "$4.99", "$360.11","$37,465.88 ")), row.names = c(1L, 2L, 3L, 4L, 5L, 6L, 7L,702L, 703L, 704L), class = "data.frame")

r string
2 Answers

You can use gsub to substitute / remove certain strings. Here [$,] is used to specify to search for $ and , and substitute them with "".

gsub("[$,]", "", test.df$cost)
# [1] "699.36"    "14.29"     "34.02"     "0.90"      "11.47"     "1.28"     
# [7] "2.16"      "1.89"      "165.81"    "16250.83 "
user_104885040

using parse_number

   library(tidyverse) 
   test.df %>% 
      mutate(
        across(.cols = c("units", "cost", "dollars", "item_description"),
               .fns = parse_number)
        )
    
       week_ending store_num units     cost  dollars item_description
    1   2021-04-30      7005   116   699.36  1564.07          1564.07
    2   2021-04-30      7005     1    14.29    24.99            24.99
    3   2021-04-30      7005     6    34.02    54.00            54.00
    4   2021-04-30      7005     2     0.90     9.98             9.98
    5   2021-04-30      7005     1    11.47    24.99            24.99
    6   2021-04-30      7005     1     1.28     2.99             2.99
    7   2021-04-30      7005     2     2.16     7.98             7.98
    8   2021-04-30      7748     1     1.89     4.99             4.99
    9   2021-04-30      7748    46   165.81   360.11           360.11
    10  2021-04-30        NA  2539 16250.83 37465.88         37465.88
user_120254830
Related