I have a dataset that looks like the one I create with the following code:
cluster <- rep(c(1,1,1,2,2,1,3,3,2,3,3))
measure_t1 <- rep(c(4.3, 4.7, 4.5, 3.4, 3.3, 4.7, 2.2, 2.1, 3.6, 2.2, 2.2))
measure_t2 <- rep(c(4.0, 4.1, 4.2, 3.5, 3.5, 4.6, 2.1, 2.4, 3.7, 2.3, 2.2))
measure_t3 <- rep(c(4.3, 4.2, 4.9, 3.8, 3.5, 4.3, 2.2, 2.8, 3.8, 2.7, 2.4))
df <- data.frame(cluster=cluster, measure_t1=measure_t1, measure_t2=measure_t2,
measure_t3=measure_t3)
So I have measures of the same variable taken at three different sample occasions, in three different locations ("cluster"), with replicates within that location.
I basically want to add three columns to the dataset, where each new column contains the mean of each cluster for a given sample occasion. In other words, the new column "mean_t1" should contain the mean of measure_t_1 for cluster 1 every time cluster==1, the mean of measure_t1 for cluster 2 every time cluster==2, and the mean of measure_t1 for cluster 3 every time cluster==3. I want to do the same for measure_t2 and measure_t3.
I have no problems at doing it with the following code:
mean_t1 <- sapply(df$cluster, function(x) if(x==1) mean(df$measure_t1[df$cluster==1])
else if(x==2) mean(df$measure_t2[df$cluster==2])
else if(x==3) mean(df$measure_t2[df$cluster==3]))
and of course I can use the same code to create also mean_t2 and mean_t3, but my real dataset has many columns and this becomes time-consuming.
Therefore, I would like to do it iteratively, either in a loop or by making a function to be applied to the different columns, but I did not manage.
We can accomplish this with minimal repetition using dplyr and the general tidyverse suite. Here we group by each cluster and compute the mean of each non-group column. Then rename the columns, and join to the original data set.
library(tidyverse)
df.means <- df %>%
group_by(cluster) %>%
mutate_all(mean)
colnames(df.means) <- gsub('measure', 'mean', colnames(df.means))
df.final <- bind_cols(df, df.means)
cluster measure_t1 measure_t2 measure_t3 cluster1 mean_t1 mean_t2 mean_t3
1 1 4.3 4.0 4.3 1 4.550000 4.225000 4.425
2 1 4.7 4.1 4.2 1 4.550000 4.225000 4.425
3 1 4.5 4.2 4.9 1 4.550000 4.225000 4.425
4 2 3.4 3.5 3.8 2 3.433333 3.566667 3.700
5 2 3.3 3.5 3.5 2 3.433333 3.566667 3.700
6 1 4.7 4.6 4.3 1 4.550000 4.225000 4.425
7 3 2.2 2.1 2.2 3 2.175000 2.250000 2.525
8 3 2.1 2.4 2.8 3 2.175000 2.250000 2.525
9 2 3.6 3.7 3.8 2 3.433333 3.566667 3.700
10 3 2.2 2.3 2.7 3 2.175000 2.250000 2.525
11 3 2.2 2.2 2.4 3 2.175000 2.250000 2.525