how to replace character in specific columns of data frame

user_13676462 0
Viewed 289

I have a data frame and I want to clean columns that include the price in character form. I want to remove $ sign and , separator and have those columns as numeric.

structure(list(Sold.Price = c("", "$177,500", "$180,000", "$180,000", 
"$189,000"), Title.to.Land = c("Freehold Strata", "Freehold Strata", 
"Freehold Strata", "Freehold Strata", "Freehold Strata"), Price = c("$174,900", 
"$177,500", "$180,000", "$180,000", "$189,000"), DOM = c(93L, 
34L, 39L, 56L, 2L), List.Date = c("10/4/2019", "12/12/2019", 
"12/9/2019", "11/12/2019", "1/9/2020"), MaintFee = c("$2,916.00", 
"$373.80", "$331.57", "$320.42", "$1,055.67")), row.names = c(NA, 
5L), class = "data.frame")

  Sold.Price   Title.to.Land    Price DOM  List.Date  MaintFee
1            Freehold Strata $174,900  93  10/4/2019 $2,916.00
2   $177,500 Freehold Strata $177,500  34 12/12/2019   $373.80
3   $180,000 Freehold Strata $180,000  39  12/9/2019   $331.57
4   $180,000 Freehold Strata $180,000  56 11/12/2019   $320.42
5   $189,000 Freehold Strata $189,000   2   1/9/2020 $1,055.67

I tried this method:

combined_csv$Sold.Price <- gsub("\\$", "", combined_csv$Sold.Price)
combined_csv$Sold.Price <- gsub("\\,", "", combined_csv$Sold.Price)
combined_csv$Sold.Price <- as.numeric(combined_csv$Sold.Price)

but this doesn't look smart. I want to do all these in one line for all price type columns (Sold.price, Maintfee, etc) . how can I do it?

dplyr r
4 Answers

I think you can use the following solution:

library(dplyr)

df %>%
  mutate(across(c(Sold.Price, Price, MaintFee), ~ as.numeric(gsub("[$,]", "", .x))))

  Sold.Price   Title.to.Land  Price DOM  List.Date MaintFee
1         NA Freehold Strata 174900  93  10/4/2019  2916.00
2     177500 Freehold Strata 177500  34 12/12/2019   373.80
3     180000 Freehold Strata 180000  39  12/9/2019   331.57
4     180000 Freehold Strata 180000  56 11/12/2019   320.42
5     189000 Freehold Strata 189000   2   1/9/2020  1055.67

Or in base R we could do:

as.data.frame(sapply(df, function(x) {
  if(any(grepl("\\$", x))) {
    as.numeric(gsub("[$,]", "", x))
  } else {
    x
  }
}))
user_143145200

We could use parse_number (This drops any non-numeric characters before or after the first number) from readr package in combination with across:

library(dplyr)
library(readr)
df %>%
  dplyr::mutate(across(c(Sold.Price, Price, MaintFee), parse_number))

 Sold.Price   Title.to.Land  Price DOM  List.Date MaintFee
1         NA Freehold Strata 174900  93  10/4/2019  2916.00
2     177500 Freehold Strata 177500  34 12/12/2019   373.80
3     180000 Freehold Strata 180000  39  12/9/2019   331.57
4     180000 Freehold Strata 180000  56 11/12/2019   320.42
5     189000 Freehold Strata 189000   2   1/9/2020  1055.67
user_133216470

an optional approach:

library(tidyverse)

df %>%
    dplyr::mutate(across(contains(c("Price","Fee")),~readr::parse_number(.x)))

  Sold.Price   Title.to.Land  Price DOM  List.Date MaintFee
1         NA Freehold Strata 174900  93  10/4/2019  2916.00
2     177500 Freehold Strata 177500  34 12/12/2019   373.80
3     180000 Freehold Strata 180000  39  12/9/2019   331.57
4     180000 Freehold Strata 180000  56 11/12/2019   320.42
5     189000 Freehold Strata 189000   2   1/9/2020  1055.67
user_135199610

An option with currency

library(dplyr)
library(formattable)
library(stringr)
df1 %>% 
    mutate(across(where(~ any(str_detect(., fixed("$")))), 
           ~ as.numeric(currency(.))))

-output

 Sold.Price   Title.to.Land  Price DOM  List.Date MaintFee
1         NA Freehold Strata 174900  93  10/4/2019  2916.00
2     177500 Freehold Strata 177500  34 12/12/2019   373.80
3     180000 Freehold Strata 180000  39  12/9/2019   331.57
4     180000 Freehold Strata 180000  56 11/12/2019   320.42
5     189000 Freehold Strata 189000   2   1/9/2020  1055.67
user_37322710
Related