adding new column base on another column in dplyr dosn't work as I guess

user_7339624 0
Viewed 34

I'm new to R and I want to create a new column base on another column using dplyr. I read the solutions on the web but things do not go well in my problem.

I have a dataset of ranges (in string) like this:

df = data.frame(my_range = c("0-14", "15-24", "25-34"))

  my_range
1     0-14
2    15-24
3    25-34

I can calculate the mean of these strings with this code:

> mean(as.integer(unlist(strsplit("0-14", "-"))))
> 7

I want to create a new column named range_mean which contains the mean of every element in the my_range column. I thought that this might do the trick:

> library(dplyr)
> df %>%
+ mutate(range_mean = mean(as.integer(unlist(strsplit(my_range, "-")))))

But the result was not what I thought. I don't understand why it is like this:

  my_range range_mean
1     0-14   18.66667
2    15-24   18.66667
3    25-34   18.66667

I tried to research to find a solution but I failed. I'm here to ask how can I create a new column like this?

  my_range range_mean
1     0-14   7
2    15-24   19.5
3    25-34   29.5
dplyr r
1 Answers

An option is to separate the column into two numeric columns (remove = FALSE), then use map2 to loop over the corresponding columns, get the mean of the vector of elements

library(dplyr)
library(purrr)
library(tidyr)
df %>%
   separate(my_range, into = c('start', 'end'), convert = TRUE,
      remove = FALSE) %>%
   transmute(my_range, range_mean = map2_dbl(start, end, ~ mean(c(.x, .y))))

-output

# my_range range_mean
#1     0-14        7.0
#2    15-24       19.5
#3    25-34       29.5

The issue is that unlist is unlisting the whole column, so the mean on that column is the mean on all the values unless there is a grouping i.e. using the OP's code, we can just add rowwise

df %>% 
   rowwise %>%
   mutate(range_mean = mean(as.integer(unlist(strsplit(my_range, "-"))))) %>%
   ungroup

-output

# A tibble: 3 x 2
#  my_range range_mean
#  <chr>         <dbl>
#1 0-14            7  
#2 15-24          19.5
#3 25-34          29.5

or it can be as simplified with rowMeans as a vectorized option

rowMeans(read.table(text = df$my_range, sep="-", header = FALSE))
#[1]  7.0 19.5 29.5
user_37322710
Related