Subtracting values in one column based on two other columns in R

user_13481055 0
Viewed 370

I am trying to subtract the values in one column based on the variables in two other columns. It is made more complicated as not all 'IDs' are at all sites before and after. Here is an example of the data frame:

> time <- c("before", "after", "before", "after", "before", "after", "before")
> site <- c("red", "red","red", "red","green", "green", "blue")
> ID <- c('A','A','B','B','B','B','C')
> cover <- c('10','5','10','5','10','5','3')
> df <- data.frame(time, site, ID, cover)

    time  site ID cover
1 before   red  A    10
2  after   red  A     5
3 before   red  B    10
4  after   red  B     5
5 before green  B    10
6  after green  B     5
7 before  blue  C     3

You can see 'C' only appears at blue before. The outcome would look like this:

   site ID diff
1   red  A   -5
2   red  B   -5
3 green  B   -5
4  blue  C <NA>

I have tried this code found on another stack overflow post with a similar quesion but I cannot get it to work.

df_new <- df %>% group_by(time, site, ID) %>% 
summarise(diff = cover[time == c("after")] - cover[time == c("before")])

Any idea where I am going wrong?

Thank you!

dplyr dataframe r
1 Answers

We can do a group by diff with a condition to check with an if/else

library(dplyr)
df %>% 
    group_by(site, ID) %>% 
    summarise(diff = if(length(cover) == 1) NA 
         else diff(cover), .groups = 'drop')

-output

# A tibble: 4 x 3
  site  ID     diff
  <chr> <chr> <dbl>
1 blue  C        NA
2 green B        -5
3 red   A        -5
4 red   B        -5

OP's original code is not working because one of the grouping doesn't have both 'after' and 'before' in 'time'. We may need to specify an if/else condition for that

user_37322710
Related