R: Divide the string by numeric and return the average as a new column in the dataframe
I have a large data frame with columns that are a character string of numbers like "1, 2, 3, 4". I want to add a new column that is the average of these numbers. I have installed the following example:
set.seed(2015)
library(dplyr)
a<-c("1, 2, 3, 4", "2, 4, 6, 8", "3, 6, 9, 12")
df<-data.frame(a)
df$a <- as.character(df$a)
Now I can use strsplit to split the string and return the average for the given string, where [[1]] indicates the first string.
mean(as.numeric(strsplit((df$a), split=", ")[[1]]))
[1] 2.5
The problem is I am trying to do this on a dataframe and referencing the line number where I am getting the error.
> df2<- df %>%
+ mutate(index = row_number(),
+ avg = mean(as.numeric(strsplit((df$a), split=", ")
[[index]])))
Error in strsplit((df$a), split = ", ")[[1:3]] :
recursive indexing failed at level 2
Can anyone explain this error and why I can't index it using a variable? If I replace the index with a constant it works, it seems to me that I am not using the variable there.
Many thanks!
source to share
Try:
library(dplyr)
library(splitstackshape)
df %>%
mutate(index = row_number()) %>%
cSplit("a", direction = "long") %>%
group_by(index) %>%
summarise(mean = mean(a))
What gives:
#Source: local data table [3 x 2]
#
# index mean
#1 1 2.5
#2 2 5.0
#3 3 7.5
Or as suggested by @Ananda:
> rowMeans(cSplit(df, "a"), na.rm = T)
# [1] 2.5 5.0 7.5
If you wanted to store the result in a dataframe you could do:
df %>% mutate(mean = rowMeans(cSplit(., "a"), na.rm = T))
What gives:
# a mean
#1 1, 2, 3, 4 2.5
#2 2, 4, 6, 8 5.0
#3 3, 6, 9, 12 7.5
source to share
library(data.table)
cols <- paste0("a",1:4)
setDT(df)[, (cols) := tstrsplit(a, ",", fixed=TRUE, type.convert=TRUE)
][, .(Mean = rowMeans(.SD)), .SDcols = cols]
Mean
1: 2.5
2: 5.0
3: 7.5
As an alternative
rowMeans(setDT(tstrsplit(df$a, ",", fixed=TRUE, type.convert=TRUE)))
# [1] 2.5 5.0 7.5
source to share