Styjun

I have an experiment where I need to subtract values of two different treatments from the Control (baseline), but these subtractions must correspond to other columns, named block and year sampled.

Dummy data frame:

df <- data.frame("Treatment" = c("Control","Treat1", "Treat2"), 
 "Block" = rep(1:3, each=3), "Year" = rep(2011:2013, each=3),
 "Value" = c(6,12,4,3,9,5,6,3,1));df

 Treatment Block Year Value
1 Control 1 2011 6
2 Treat1 1 2011 12
3 Treat2 1 2011 4
4 Control 2 2012 3
5 Treat1 2 2012 9
6 Treat2 2 2012 5
7 Control 3 2013 6
8 Treat1 3 2013 3
9 Treat2 3 2013 1

Desired output:

 Treatment Block Year Value
1 Control-Treat1 1 2011 -6
2 Control-Treat2 1 2011 2
3 Control-Treat1 2 2012 -6
4 Control-Treat2 2 2012 -2
5 Control-Treat1 3 2013 3
6 Control-Treat2 3 2013 5

Any suggestion, preferably using dplyr?

I have found similar questions but none addressing this specific issue.

We can use dplyr, group_by Block and subtract Value where Treatment == "Control" from each Value and remove the "Control" rows.

library(dplyr)

df %>%
 group_by(Block) %>%
 mutate(Value = Value[which.max(Treatment == "Control")] - Value) %>%
 filter(Treatment != "Control")

# Treatment Block Year Value
# <fct> <int> <int> <dbl>
#1 Treat1 1 2011 -6
#2 Treat2 1 2011 2
#3 Treat1 2 2012 -6
#4 Treat2 2 2012 -2
#5 Treat1 3 2013 3
#6 Treat2 3 2013 5

Not sure, if the values in Treatment column in expected output (Control-Treat1, Control-Treat2) are shown only for demonstration purpose of the calculation or OP really wants that as output. In case if that is needed as output we can use

df %>%
 group_by(Block) %>%
 mutate(Value = Value[which.max(Treatment == "Control")] - Value, 
 Treatment = paste0("Control-", Treatment)) %>%
 filter(Treatment != "Control-Control")

# Treatment Block Year Value
# <chr> <int> <int> <dbl>
#1 Control-Treat1 1 2011 -6
#2 Control-Treat2 1 2011 2
#3 Control-Treat1 2 2012 -6
#4 Control-Treat2 2 2012 -2
#5 Control-Treat1 3 2013 3
#6 Control-Treat2 3 2013 5

A somehow different tidyverse possibility could be:

df %>%
 spread(Treatment, Value) %>%
 gather(var, val, -c(Block, Year, Control)) %>%
 mutate(Value = Control - val,
 Treatment = paste("Control", var, sep = " - ")) %>%
 select(Treatment, Block, Year, Value) %>%
 arrange(Block)

 Treatment Block Year Value
1 Control - Treat1 1 2011 -6
2 Control - Treat2 1 2011 2
3 Control - Treat1 2 2012 -6
4 Control - Treat2 2 2012 -2
5 Control - Treat1 3 2013 3
6 Control - Treat2 3 2013 5

This can be done with an SQL self join like this:

library(sqldf)
sqldf("select a.Treatment || '-' || b.Treatment as Treatment, 
 a.Block, 
 a.Year, 
 a.Value - b.Value as Value
 from df a 
 join df b on a.block = b.block and 
 a.Treatment = 'Control' and 
 b.Treatment != 'Control'")

giving:

 Treatment Block Year Value
1 Control-Treat1 1 2011 -6
2 Control-Treat2 1 2011 2
3 Control-Treat1 2 2012 -6
4 Control-Treat2 2 2012 -2
5 Control-Treat1 3 2013 3
6 Control-Treat2 3 2013 5

Another dplyr-tidyr approach: You can remove unwanted columns with select:

library(tidyr)
 library(dplyr)
 dummy_df %>% 
 spread(Treatment,Value) %>% 
 gather(key,value,Treat1:Treat2) %>%
 group_by(Block,Year,key) %>% 
 mutate(Val=Control-value)
 # A tibble: 6 x 6
# Groups: Block, Year, key [6]
 Block Year Control key value Val
 <int> <int> <dbl> <chr> <dbl> <dbl>
1 1 2011 6 Treat1 12 -6
2 2 2012 3 Treat1 9 -6
3 3 2013 6 Treat1 3 3
4 1 2011 6 Treat2 4 2
5 2 2012 3 Treat2 5 -2
6 3 2013 6 Treat2 1 5

Just the exact output:

dummy_df %>% 
 spread(Treatment,Value) %>% 
 gather(key,value,Treat1:Treat2) %>% 
 mutate(Treatment=paste0("Control-",key)) %>% 
 group_by(Block,Year,Treatment) %>% 
 mutate(Val=Control-value) %>% 
 select(Treatment,everything(),-value,-key)%>% 
 arrange(Year)

Result:

# A tibble: 6 x 5
# Groups: Block, Year, Treatment [6]
 Treatment Block Year Control Val
 <chr> <int> <int> <dbl> <dbl>
1 Control-Treat1 1 2011 6 -6
2 Control-Treat2 1 2011 6 2
3 Control-Treat1 2 2012 3 -6
4 Control-Treat2 2 2012 3 -2
5 Control-Treat1 3 2013 6 3
6 Control-Treat2 3 2013 6 5

Another tidyverse solution. We can use filter to separate "Control" and "Treatment" to different data frames, use left_join to combine them by Block and Year, and then process the data frame.

library(tidyverse)

df2 <- df %>%
 filter(!Treatment %in% "Control") %>%
 left_join(df %>% filter(Treatment %in% "Control"), 
 ., 
 by = c("Block", "Year")) %>%
 mutate(Value = Value.x - Value.y) %>%
 unite(Treatment, Treatment.x, Treatment.y, sep = "-") %>%
 select(names(df))
# Treatment Block Year Value
# 1 Control-Treat1 1 2011 -6
# 2 Control-Treat2 1 2011 2
# 3 Control-Treat1 2 2012 -6
# 4 Control-Treat2 2 2012 -2
# 5 Control-Treat1 3 2013 3
# 6 Control-Treat2 3 2013 5