Suppose I have a df with 3 columns, group1, group2 & variable
set.seed(1)
group1 = c(rep(1,5),rep(2,5),rep(3,5),rep(4,5))
group2 = c("A","B","C","D","B","C","C","B","C","A","B","D")
variable = c(as.integer(rnorm(20,2)**3))
df=data.frame(group1, group2, variable)
I added the column 'min1' which states if the value of b within 'group1' is also present in group1(x-1). Vice Versa with plus1. Below the total data frame:
group1 group2 variable min1 plus1
1 1 A 3 0 0
2 1 B 11 0 1
3 1 C 2 0 1
4 2 D 47 0 1
5 2 B 13 1 1
6 2 C 2 1 1
7 3 C 16 1 0
8 3 B 21 1 1
9 3 C 18 1 0
10 4 A 5 0 0
11 4 B 44 1 0
12 4 D 14 0 0
Now I want to do calculations such as max() and sum() (but also some more exotic ones) on the variables but not just on all values within their own group1 & group2 combination, but including the values of the group before (or after it). The min1 example is shown below.
group1_min1 group2_min1 sum_min1 max_min1
1 2 B 24 13
2 2 C 4 2
3 3 C 36 18
4 3 B 34 21
5 4 B 65 44
Note that for group1_min1(3),group2_min1(C) three values are used: rows 6,7&9 (2,16&18).
I tried using group_by and summarize within dplyr, something like:
group_by(group1, group2) %>%
summarize_each(funs(sum, max))
EDIT:
I found a solution to add the sum to the original df:
sum_min1 = c()
j=0
for (j in 1:(length(df$group1))){
if (df[j,"min1"] == 0){sum_min1 = c(sum_min1,0)} else {
sum_min1 = c(sum_min1,(sum(df[which((df[,"group1"] == df[j,"group1"] | df[,"group1"] == (df[j,"group1"]-1)) & df[,"group2"]==(df[j,"group2"])),"variable"])))
}
}
df = cbind(df,sum_min1)
This delivers the output:
group1 group2 variable min1 plus1 sum_min1
1 1 A 3 0 0 0
2 1 B 11 0 1 0
3 1 C 2 0 1 0
4 2 D 47 0 0 0
5 2 B 13 1 1 24
6 2 C 2 1 1 4
7 3 C 16 1 0 36
8 3 B 21 1 1 34
9 3 C 18 1 0 36
10 4 A 5 0 0 0
11 4 B 44 1 0 65
12 4 D 14 0 0 0
However this seems to be a very crude way and may take long on big data sets, also in reality there are multiple variables and multiple functions. Also it might be a problem because I want to do some user-defined functions which include a for loop for all the values.
Is there a more elegant way to do this?
Sorry for anything I do wrong, I am new to R and StackOverflow and not a native speaker.
# Data
set.seed(1)
group1 = c(rep(1,3),rep(2,3),rep(3,3),rep(4,3))
group2 = c("A","B","C","D","B","C","C","B","C","A","B","D")
variable = c(as.integer(rnorm(12,2)**3))
df=data.frame(group1, group2, variable)
For the first part-
df$min1 <- sapply(seq(nrow(df)), function(x)
{
if(df[x, "group1"] == 1){0} else {
max(df[x, "group2"] %in% df[df$group1 == df[x,"group1"] - 1,"group2"])}
})
df$plus1 <- sapply(seq(nrow(df)), function(x)
{
if(df[x, "group1"] == max(df$group1){0} else {
max(df[x, "group2"] %in% df[df$group1 == df[x,"group1"] + 1,"group2"])}
})
Second part
df$sum_min1 <- sapply(seq(nrow(df)), function(x)
{
if(df[x, "group1"] == 1){0}else{
sum(df[df$group1 == df[x,"group1"] &
df$group2 == df[x,"group2"],"variable"],
df[df$group1 == df[x,"group1"] - 1 &
df$group2 == df[x,"group2"],"variable"])}
})