CODEWITHSUNDEEP
r
user11916948
user11916948

Reputation: 954

Average the periods 1-3 and 4-6 for each treatment and id for each variable and get the data in a new data frame

Here I would like to average the periods 1-3 and 4-6 for each treatment and id for each variable and get the data in a new data frame. Does anyone know how I accomplish this?

set.seed(1)
id <- rep(1:2,each=6)
trt <- c("A","A","A","B", "B", "B","A","A","A","B", "B", "B")
period <- rep(1:6,2)
pointA <- sample(1:10,12, replace=TRUE)
pointB<- sample(1:10,12, replace=TRUE)
pointC<- sample(1:10,12, replace=TRUE)
df <- data.frame(id,trt,period,pointA, pointB,pointC)
head(df)

   id trt period pointA pointB pointC
1   1   A      1      3      7      3
2   1   A      2      4      4      4
3   1   A      3      6      8      1
4   1   B      4     10      5      4
5   1   B      5      3      8      9
6   1   B      6      9     10      4
7   2   A      1     10      4      5
8   2   A      2      7      8      6
9   2   A      3      7     10      5
10  2   B      4      1      3      2
11  2   B      5      3      7      9
12  2   B      6      2      2      7

I would like it to look like this:

  id trt Period pointA pointB pointC
1  1   A    123     13     19      8
2  1   B    456     21     23     17
3  2   A    456     24     22     16
4  2   B    123      6     12     18

Upvotes: 0

Views: 53

Answers (3)

jay.sf
jay.sf

Reputation: 72984

You could do this concise in base R using aggregate and ave.

pstClps <- function(x) paste(x, collapse="")  # pre-define FUN

aggregate(. ~ id + trt + period, transform(df, period=ave(period, id, trt, FUN=pstClps)), sum)
#   id trt period pointA pointB pointC
# 1  1   A    123     13     19      8
# 2  2   A    123     24     22     16
# 3  1   B    456     22     23     17
# 4  2   B    456      6     12     18

Data:

df <- structure(list(id = c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 
2L, 2L), trt = c("A", "A", "A", "B", "B", "B", "A", "A", "A", 
"B", "B", "B"), period = c(1L, 2L, 3L, 4L, 5L, 6L, 1L, 2L, 3L, 
4L, 5L, 6L), pointA = c(3L, 4L, 6L, 10L, 3L, 9L, 10L, 7L, 7L, 
1L, 3L, 2L), pointB = c(7L, 4L, 8L, 5L, 8L, 10L, 4L, 8L, 10L, 
3L, 7L, 2L), pointC = c(3L, 4L, 1L, 4L, 9L, 4L, 5L, 6L, 5L, 2L, 
9L, 7L)), row.names = c(NA, -12L), class = "data.frame")

Upvotes: 1

JDG
JDG

Reputation: 1364

Using data.table. I have attached two solutions, one for your example (sum) and one for your request (mean). Unsure why they differ in your question.

Code

library(data.table); setDT(df)

point_var = colnames(df) %like% 'point'

# (i) for the sum (as per your example):
dtsum = df[, lapply(.SD, sum), .SDcols = point_var, .(id, trt, pCat = ifelse(period > 3, 456, 123))] 


# (ii) for the mean (as per your request)
dtmean = df[, lapply(.SD, mean), .SDcols = point_var, .(id, trt, pCat = ifelse(period > 3, 456, 123))]

Output (i) Sum

> dtsum
   id trt pCat pointA pointB pointC
1:  1   A  123     13     19      8
2:  1   B  456     22     23     17
3:  2   A  123     24     22     16
4:  2   B  456      6     12     18

Output (ii) Mean

> dtmean
   id trt pCat   pointA   pointB   pointC
1:  1   A  123 4.333333 6.333333 2.666667
2:  1   B  456 7.333333 7.666667 5.666667
3:  2   A  123 8.000000 7.333333 5.333333
4:  2   B  456 2.000000 4.000000 6.000000

Upvotes: 1

MrFlick
MrFlick

Reputation: 206253

Using dplyr you can create a new variable with the proper group, and then use that as a group_by. For example

library(dplyr)
df %>% 
  mutate(period_class = case_when(
    period %in% c(1,2,3)~"123",
    period %in% c(4,5,6)~"456")
  ) %>% 
  select(-period) %>% 
  group_by(id, trt, period_class) %>% 
  summarize_all(mean) # though you seem to have used `sum` in your example

Upvotes: 1

Related Questions