How do I remove a percentage sign and find the average of some values in a column using r?

user_13827082 0
Viewed 497

I have a column in a dataset called debt_to_income_ratio. The first six values in the column look like this:

1. 45
2. 43
3. 20%-<30%
4. 38
5. 49
6. 50%-60%

I have been trying to remove the percentage sign from the values that are percentages. I have also been trying to find the mean of the values that are like 20%-<30% or like 50%-60%. I would like them to read 25 and 55. I used this code to try to remove the percentage sign, but it didn't work:

mutate(debt_to_income_ratio = gsub('//%','',debt_to_income_ratio))

I also am not sure how to find the average of only certain values in a column. Thanks in advance for any help!

dplyr r
5 Answers

Here is one possibility using map from the purrr package and str_extract_all from the stringr package:

library(tidyverse)

mydata %>%
  mutate(debt_to_income_ratio = map_dbl(debt_to_income_ratio,
                                        ~mean(as.numeric(str_extract_all(.x, "\\d+")[[1]]))))

Here is the data I used:

mydata <- tibble(debt_to_income_ratio = c("45", "43", "20%<30%", "38", "49", "50%<60%"))
user_54368770

A tidyverse option with your data

  debt_to_income_ratio <- data.frame(mycol = c("45", "43", "20%-<30%", "38", "49", "50%-60%"))
  
  library(dplyr)
  library(stringr)
  
  debt_to_income_ratio <- debt_to_income_ratio %>% 
    mutate(
      newmycol = str_extract_all(mycol, "[0-9]+") %>%
        purrr::map( 
          ~ifelse(
            length(.x) == 1, 
            as.numeric(.x), 
            mean(unlist(as.numeric(.x)))
          )
        )
    )
  debt_to_income_ratio
#>      mycol newmycol
#> 1       45       45
#> 2       43       43
#> 3 20%-<30%       25
#> 4       38       38
#> 5       49       49
#> 6  50%-60%       55
user_91900340

We could also use rowMeans with extract

library(dplyr)
library(tidyr)
debt_to_income_ratio %>% 
  extract(mycol, into = c('col1', 'col2'), "^(\\d+)\\D*(\\d*).*", convert = TRUE) %>% 
   transmute(mycol = rowMeans(., na.rm = TRUE))
#  mycol
#1    45
#2    43
#3    25
#4    38
#5    49
#6    55

data

debt_to_income_ratio <- structure(list(mycol = c("45", "43", "20%-<30%", "38", "49", 
"50%-60%")), class = "data.frame", row.names = c(NA, -6L))
user_37322710

Here's a base R solution:

extracted <- regmatches(df$debt_to_income_ratio, gregexpr("\\d+", df$debt_to_income_ratio))
df$debt_to_income_ratio <- sapply(extracted, function(x) mean(as.numeric(x)))

  debt_to_income_ratio
1                   45
2                   43
3                   25
4                   38
5                   49
6                   55

Data:

df <- data.frame(debt_to_income_ratio = c(45,43,"20%-<30%",38,49,"50%-60%"))
user_104424230

Here's a base Rsolution

debt_to_income_ratio$mean <- unlist(lapply(lapply(strsplit(sub("(\\d+)%-<?(\\d+)%", "\\1 \\2", debt_to_income_ratio$mycol), " "), as.numeric), mean))

Or, somewhat shorter:

library(stringr)
debt_to_income_ratio$mean <- unlist(lapply(lapply(str_extract_all(debt_to_income_ratio$mycol, "\\d+"), as.numeric), mean))

Result:

debt_to_income_ratio
     mycol mean
1       45   45
2       43   43
3 20%-<30%   25
4       38   38
5       49   49
6  50%-60%   55
user_80399780
Related