Get mean of specifics variable depending on another variable

Question

I have a dataframe where I have different month, I want to do a mean of each row between the month of begining (in the begin variable) and the end month (in the end variable)

nom <- letters[1:5]
pseudo <- paste(nom, 21:25, sep = "")
janvier <- c(0, 1, 1, 1, 0)
fevrier <- c(1, 1, 1, 1, 1)
mars <- c(0, 0, 0, 1, 1)
avril <- c(1, 1, 1, 0, 1)
mai <- c(1, 0, 1, 1, 1)
juin <- c(1, 1, 0, 1, 0)

df <- data.frame(nom =nom, pseudo = pseudo, janvier = janvier,
                 fevrier = fevrier, mars = mars, avril = avril,
                 mai = mai, juin = juin)

dfm <- as.matrix(df[, -c(1, 2)])
my_matrix <- matrix(nrow = 10, ncol = 6)

my_matrix <- matrix("no info", nrow = 5, ncol = 2)
colnames(my_matrix) <- c("begin", "end")


for(i in 1:dim(dfm)[1]){
  for(j in 1:(dim(dfm)[2]-2)){
    if(dfm[i, j] + dfm[i, j+1] + dfm[i, j+2] == 3){
      my_matrix[i, 1] <- colnames(dfm)[j]
      my_matrix[i, 2] <- colnames(dfm)[j+2]
      break
    } 
  }
}


output <- cbind(df, my_matrix)
output %>%
  filter(begin != "no info") -> output

I tried do do it with a vectorized method, something like :

output$mean <- rowMeans(output[, output$begin:output$end])

I also tried this but not seems to recognize my begin variable :

for(i in seq_len(nrow(output))){
  for(j in seq_len(ncol(output))){
    output$mean[i, j] <- rowMeans(as.character(begin[i, j]):as.character(end[i, j]))
  }
}

I dont want to use a loop if possible just with dplyr package, thanks for help

EDIT : I dont want to group_by, my question is a little bit complicated because I have to do means of row between the variables stored in begin and end variables

summarise_at() could maybe do the job ? – Mostafa90 Jul 05 '17 at 22:35 — Mostafa90, Jul 05 '17 at 22:35
up please, seems to be a little tricky – Mostafa90 Jul 06 '17 at 00:23 — Mostafa90, Jul 06 '17 at 00:23

score 2 · Accepted Answer · answered Jul 06 '17 at 01:07

If I'm understanding correctly, you'll want to switch everything to long format. Then you can join, filter, group, and average:

## long format
library(reshape2)
df_long = melt(dfm)
names(df_long) = c("id", "month", "value")
id_key = as.data.frame(my_matrix)
id_key$id = 1:nrow(id_key)

## turn the months into a factor with the correct order
month_levs = c("janvier", "fevrier", "mars", "avril", "mai", "juin")

id_key = mutate(id_key, begin = factor(begin, levels = month_levs),
                end = factor(end, levels = month_levs))
df_long = mutate(df_long, month = factor(month, levels = month_levs))

## calculate results
results = df_long %>% left_join(id_key) %>%
    group_by(id) %>%
    filter(between(as.numeric(month), as.numeric(begin), as.numeric(end))) %>%
    summarize(mean = mean(value))
results
## A tibble: 3 x 2
#      id  mean
#   <int> <dbl>
# 1     1     1
# 2     4     1
# 3     5     1

Thanks a lot, it's doing the job ! – Mostafa90 Jul 06 '17 at 05:01 — Mostafa90, Jul 06 '17 at 05:01

Get mean of specifics variable depending on another variable

1 Answers1