CODEWITHSUNDEEP
rdifference
Mary Smirnova
Mary Smirnova

Reputation: 166

Calculate difference based on two columns in R

I have a little bit of a tricky question. Here is my data: 

> structure(list(seconds = c(689, 689.25, 689.5, 689.75, 690, 690.25, 690.5, 690.75, 691, 691.25, 691.5, 691.75, 692, 692.25, 692.5 ), threat = c(NA, NA, NA, NA, NA, NA, 1L, 1L, 0L, 0L, 1L, NA,  NA, 1L, 1L), bins = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L,  3L, 3L, 3L, 3L, 3L)), .Names = c ("seconds", "threat", "bins"), class = "data.frame", row.names = c(NA, -15L))

   seconds threat bins
1   689.00     NA    1
2   689.25     NA    1
3   689.50     NA    1
4   689.75     NA    1
5   690.00     NA    1
6   690.25     NA    2
7   690.50      1    2
8   690.75      1    2
9   691.00      0    2
10  691.25      0    2
11  691.50      1    3
12  691.75     NA    3
13  692.00     NA    3
14  692.25      1    3
15  692.50      1    3

Within each bin, I am trying to calculate the amount of time they are in each type of "threat" in the threat column. So I would need to calculate the difference score every time something different happens in threat and within each bin. So here is an example of something I am hoping to achieve: 

  bin threat seconds
   1     NA    1.25
   1      1    0.00
   1      0    0.00
   2     NA    0.25
   2      1    0.50
   2      0    0.50
   3     NA    0.50
   3      1    0.75
   3      0    0.00

Upvotes: 2

Views: 163

Answers (1)

Julius Vainora
Julius Vainora

Reputation: 48191

Here's a tidyverse solution:

df %>% arrange(seconds) %>% 
  mutate(duration = lead(seconds) - seconds) %>% 
  complete(bins, threat, fill = list(duration = 0)) %>%
  group_by(bins, threat) %>% 
  summarize(seconds = sum(duration, na.rm = TRUE))
# A tibble: 9 x 3
# Groups:   bins [?]
#    bins threat seconds
#   <int>  <int>   <dbl>
# 1     1      0    0   
# 2     1      1    0   
# 3     1     NA    1.25
# 4     2      0    0.5 
# 5     2      1    0.5 
# 6     2     NA    0.25
# 7     3      0    0   
# 8     3      1    0.5 
# 9     3     NA    0.5

You may erase complete(bins, threat, fill = list(duration = 0)) if adding rows where seconds is 0 is not necessary.

So, first we arrange the data to be safe. Then due to the interactions between threat we define a new variable duration. Next we add new rows with duration == 0 for those (bins, threat) cases that are not yet present. Lastly we group by bins and threat and sum up the durations.

Upvotes: 4

Related Questions