Compare value in a column within groups in dplyr

Question

I would like to compare the values inside a grouped data.frame using dplyr, and create a dummy variable, or something similar, indicating which is bigger. Couldn't figure it out!

Here is some reproducible code:

table <- structure(list(species = structure(c(1L, 1L, 1L, 2L, 2L, 2L), .Label = c("Adelophryne adiastola", 
"Adelophryne gutturosa"), class = "factor"), scenario = structure(c(3L, 
1L, 2L, 3L, 1L, 2L), .Label = c("future1", "future2", "present"
), class = "factor"), amount = c(5L, 3L, 2L, 50L, 60L, 40L)), .Names = c("species", 
"scenario", "amount"), class = "data.frame", row.names = c(NA, 
-6L))
> table
                species scenario amount
1 Adelophryne adiastola  present      5
2 Adelophryne adiastola  future1      3
3 Adelophryne adiastola  future2      2
4 Adelophryne gutturosa  present     50
5 Adelophryne gutturosa  future1     60
6 Adelophryne gutturosa  future2     40

I would group the df by species. I want to create a new column, can be increase_amount, where the amount in every "future" is compared to the "present". I could get 1 when the value has increased and 0 when it has decreased.

I have been trying with a for loop that goes throw each of the species, but the df contains over 50,000 of them and it takes too long for the times I will have to re-do the operation...

Someone know a way? Thanks a lot!

Answer 1

You can do something like that:

table %>% 
  group_by(species) %>% 
  mutate(tmp = amount[scenario == "present"]) %>% 
  mutate(increase_amount = ifelse(amount > tmp, 1, 0))
# Source: local data frame [6 x 5]
# Groups: species [2]
# 
#                 species scenario amount   tmp increase_amount
#                  <fctr>   <fctr>   <int> <int>           <dbl>
# 1 Adelophryne adiastola  present      5     5               0
# 2 Adelophryne adiastola  future1      3     5               0
# 3 Adelophryne adiastola  future2      2     5               0
# 4 Adelophryne gutturosa  present     50    50               0
# 5 Adelophryne gutturosa  future1     60    50               1
# 6 Adelophryne gutturosa  future2     40    50               0