0

I would like to calculate the time since peak abundance for each of my sites annually, i.e where sites match and years match calculate the time since peak abundance for that specific site in that specific year. Below is a subset of my data, but i have manually entered the column ts_peak_abun so that it represents what I actually want:

    chr.site year chr.season       date ave_ajust_abun peak_abun           ts_peak_abun
1     Avalon 2014     Winter 2014-07-01      26.791667         0           dat$date[2] - dat$date[1]
2     Avalon 2014     Spring 2014-10-01      42.791667         1           dat$date[2] - dat$date[2]
3     Avalon 2015     Autumn 2015-04-01      20.307692         0           dat$date[6] - dat$date[3]
4     Avalon 2015     Spring 2015-10-01      42.256410         0           dat$date[6] - dat$date[4]
5     Avalon 2015     Winter 2015-07-01      29.794872         0           dat$date[6] - dat$date[5]
6     Avalon 2015     Summer 2015-01-01      52.000000         1           dat$date[6] - dat$date[6]
7     Avalon 2016     Autumn 2016-04-01      17.897436         0           dat$date[10] - dat$date[7]
8     Avalon 2016     Spring 2016-10-01      14.969697         0           dat$date[10] - dat$date[8]
9     Avalon 2016     Summer 2016-01-01      26.256410         0           dat$date[10] - dat$date[9]
10    Avalon 2016     Winter 2016-07-01      28.666667         1           dat$date[10] - dat$date[10]
11    Avalon 2017     Autumn 2017-04-01      25.487179         0           dat$date[14] - dat$date[11]
12    Avalon 2017     Summer 2017-01-01      15.743590         0           dat$date[14] - dat$date[12]
13    Avalon 2017     Winter 2017-07-01      36.923077         0           dat$date[14] - dat$date[13]
14    Avalon 2017     Spring 2017-10-01      37.714286         1           dat$date[14] - dat$date[14]
15    Avalon 2018     Summer 2018-01-01      32.952381         1           dat$date[15] - dat$date[15]
16 Blowering 2015     Spring 2015-10-01      10.000000         0           dat$date[18] - dat$date[16]
17 Blowering 2015     Winter 2015-07-01       9.000000         0           dat$date[18] - dat$date[17]
18 Blowering 2015     Autumn 2015-04-01      21.000000         1           dat$date[18] - dat$date[18]
19 Blowering 2016     Autumn 2016-04-01            NaN         0           dat$date[22] - dat$date[19]
20 Blowering 2016     Spring 2016-10-01            NaN         0           dat$date[22] - dat$date[20]
21 Blowering 2016     Winter 2016-07-01            NaN         0           dat$date[22] - dat$date[21]
22 Blowering 2016     Summer 2016-01-01       5.666667         1           dat$date[22] - dat$date[22]
23 Blowering 2017     Spring 2017-10-01       5.000000         0           dat$date[26] - dat$date[23]
24 Blowering 2017     Summer 2017-01-01       9.666667         0           dat$date[26] - dat$date[24]
25 Blowering 2017     Winter 2017-07-01            NaN         0           dat$date[26] - dat$date[25]
26 Blowering 2017     Autumn 2017-04-01      15.333333         1           dat$date[26] - dat$date[26]
27 Blowering 2018     Summer 2018-01-01            NaN         0           NA - dat$date[27]

Note that for Blowering 2018, row 27 in the above table, there is no peak abundance so ts_peak_abun should be NA.

I have tried the following, but neither give me what I want:

dat <- dat %>% arrange(site, year, peak_abun, season)
dat$ts_peak_abun <- ifelse(dat$peak_abun == 1, 0, ifelse(dat$site == lag(dat$site) & dat$year == lag(dat$year), dat$date - lag(dat$date), ifelse(dat$site == lag(dat$site, n = 2) & dat$year == lag(dat$year, n = 2), dat$date - lag(dat$date, n = 2), ifelse(dat$site == lag(dat$site, n = 3) & dat$year == lag(dat$year, n = 3), dat$date - lag(dat$date, n = 3), NA))))

and tried

dat <- dat %>% group_by(site, year) %>% do(dat$date[dat$peak_abun == 1] - dat$date)

There are other posts similar to this (here, here and here), but they have not been helpful to me so far.

Thanks in advance.

Pat Taggart
  • 321
  • 1
  • 9

2 Answers2

0

Here is one approach - not sure this is what you are looking for. 

library(tidyverse)

dat$date <- as.Date(dat$date)

dat %>%
  mutate(peak_abun_date = if_else(peak_abun == 1, date, as.Date(NA))) %>%
  group_by(chr.site, year) %>%
  arrange(peak_abun) %>%
  fill(peak_abun_date, .direction = "up") %>%
  mutate(ts_peak_abun = peak_abun_date - date) %>%
  arrange(chr.site, year)

# A tibble: 27 x 8
# Groups:   chr.site, year [9]
   chr.site   year chr.season date       ave_ajust_abun peak_abun peak_abun_date ts_peak_abun
   <fct>     <int> <fct>      <date>              <dbl>     <int> <date>         <drtn>      
 1 Avalon     2014 Winter     2014-07-01          26.8          0 2014-10-01       92 days   
 2 Avalon     2014 Spring     2014-10-01          42.8          1 2014-10-01        0 days   
 3 Avalon     2015 Autumn     2015-04-01          20.3          0 2015-01-01      -90 days   
 4 Avalon     2015 Spring     2015-10-01          42.3          0 2015-01-01     -273 days   
 5 Avalon     2015 Winter     2015-07-01          29.8          0 2015-01-01     -181 days   
 6 Avalon     2015 Summer     2015-01-01          52            1 2015-01-01        0 days   
 7 Avalon     2016 Autumn     2016-04-01          17.9          0 2016-07-01       91 days   
 8 Avalon     2016 Spring     2016-10-01          15.0          0 2016-07-01      -92 days   
 9 Avalon     2016 Summer     2016-01-01          26.3          0 2016-07-01      182 days   
10 Avalon     2016 Winter     2016-07-01          28.7          1 2016-07-01        0 days   
11 Avalon     2017 Autumn     2017-04-01          25.5          0 2017-10-01      183 days   
12 Avalon     2017 Summer     2017-01-01          15.7          0 2017-10-01      273 days   
13 Avalon     2017 Winter     2017-07-01          36.9          0 2017-10-01       92 days   
14 Avalon     2017 Spring     2017-10-01          37.7          1 2017-10-01        0 days   
15 Avalon     2018 Summer     2018-01-01          33.0          1 2018-01-01        0 days   
16 Blowering  2015 Spring     2015-10-01          10            0 2015-04-01     -183 days   
17 Blowering  2015 Winter     2015-07-01           9            0 2015-04-01      -91 days   
18 Blowering  2015 Autumn     2015-04-01          21            1 2015-04-01        0 days   
19 Blowering  2016 Autumn     2016-04-01         NaN            0 2016-01-01      -91 days   
20 Blowering  2016 Spring     2016-10-01         NaN            0 2016-01-01     -274 days   
21 Blowering  2016 Winter     2016-07-01         NaN            0 2016-01-01     -182 days   
22 Blowering  2016 Summer     2016-01-01           5.67         1 2016-01-01        0 days   
23 Blowering  2017 Spring     2017-10-01           5            0 2017-04-01     -183 days   
24 Blowering  2017 Summer     2017-01-01           9.67         0 2017-04-01       90 days   
25 Blowering  2017 Winter     2017-07-01         NaN            0 2017-04-01      -91 days   
26 Blowering  2017 Autumn     2017-04-01          15.3          1 2017-04-01        0 days   
27 Blowering  2018 Summer     2018-01-01         NaN            0 NA               NA days

Data

dat <- read.table(
  text = "
  chr.site year chr.season       date ave_ajust_abun peak_abun           
1     Avalon 2014     Winter 2014-07-01      26.791667         0           
2     Avalon 2014     Spring 2014-10-01      42.791667         1           
3     Avalon 2015     Autumn 2015-04-01      20.307692         0           
4     Avalon 2015     Spring 2015-10-01      42.256410         0           
5     Avalon 2015     Winter 2015-07-01      29.794872         0           
6     Avalon 2015     Summer 2015-01-01      52.000000         1           
7     Avalon 2016     Autumn 2016-04-01      17.897436         0           
8     Avalon 2016     Spring 2016-10-01      14.969697         0          
9     Avalon 2016     Summer 2016-01-01      26.256410         0          
10    Avalon 2016     Winter 2016-07-01      28.666667         1          
11    Avalon 2017     Autumn 2017-04-01      25.487179         0           
12    Avalon 2017     Summer 2017-01-01      15.743590         0          
13    Avalon 2017     Winter 2017-07-01      36.923077         0          
14    Avalon 2017     Spring 2017-10-01      37.714286         1          
15    Avalon 2018     Summer 2018-01-01      32.952381         1         
16 Blowering 2015     Spring 2015-10-01      10.000000         0          
17 Blowering 2015     Winter 2015-07-01       9.000000         0          
18 Blowering 2015     Autumn 2015-04-01      21.000000         1           
19 Blowering 2016     Autumn 2016-04-01            NaN         0          
20 Blowering 2016     Spring 2016-10-01            NaN         0         
21 Blowering 2016     Winter 2016-07-01            NaN         0          
22 Blowering 2016     Summer 2016-01-01       5.666667         1          
23 Blowering 2017     Spring 2017-10-01       5.000000         0           
24 Blowering 2017     Summer 2017-01-01       9.666667         0          
25 Blowering 2017     Winter 2017-07-01            NaN         0          
26 Blowering 2017     Autumn 2017-04-01      15.333333         1  
27 Blowering 2018     Summer 2018-01-01            NaN         0", 
  header = T
)
Ben
  • 28,684
  • 5
  • 23
  • 45
0

This would be another approach from selecting top of the ave_ajust_abun.

dta %>%
  mutate(index = 1:n()) %>%
  left_join(dta %>% group_by(chr.site, year) %>% top_n(1, ave_ajust_abun) %>% ungroup() 
            %>% rename(top_ave_ajust_abun = ave_ajust_abun) %>% select(chr.site, year, top_ave_ajust_abun),
            key = c("chr.site", "year")) %>%
  arrange(index) %>%
  mutate(ts_peak_abun = top_ave_ajust_abun - ave_ajust_abun)

I used left_join function and you might used is.na(x) function for several rows if you want to deal with NA.

Here is your dataset.

dput(dta) structure(list(chr.site = c("Avalon", "Avalon", "Avalon", "Avalon", "Avalon", "Avalon", "Avalon", "Avalon", "Avalon", "Avalon", "Avalon", "Avalon", "Avalon", "Avalon", "Avalon", "Blowering", "Blowering", "Blowering", "Blowering", "Blowering", "Blowering", "Blowering", "Blowering", "Blowering", "Blowering", "Blowering", "Blowering" ), year = c(2014, 2014, 2015, 2015, 2015, 2015, 2016, 2016, 2016, 2016, 2017, 2017, 2017, 2017, 2018, 2015, 2015, 2015, 2016, 2016, 2016, 2016, 2017, 2017, 2017, 2017, 2018), chr.season = c("Winter", "Spring", "Autumn", "Spring", "Winter", "Summer", "Autumn", "Spring", "Summer", "Winter", "Autumn", "Summer", "Winter", "Spring", "Summer", "Spring", "Winter", "Autumn", "Autumn", "Spring", "Winter", "Summer", "Spring", "Summer", "Winter", "Autumn", "Summer"), date = structure(c(1404172800, 1412121600, 1427846400, 1443657600, 1435708800, 1420070400, 1459468800, 1475280000, 1451606400, 1467331200, 1491004800, 1483228800, 1498867200, 1506816000, 1514764800, 1443657600, 1435708800, 1427846400, 1459468800, 1475280000, 1467331200, 1451606400, 1506816000, 1483228800, 1498867200, 1491004800, 1514764800), class = c("POSIXct", "POSIXt"), tzone = "UTC"), ave_ajust_abun = c(26.791667, 42.791667, 20.307692, 42.25641, 29.794872, 52, 17.897436, 14.969697, 26.25641, 28.666667, 25.487179, 15.74359, 36.923077, 37.714286, 32.952381, 10, 9, 21, NA, NA, NA, 5.666667, 5, 9.666667, NA, 15.333333, NA), peak_abun = c(0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0)), row.names = c(NA, -27L), class = c("tbl_df", "tbl", "data.frame"))

jhyeon
  • 456
  • 4
  • 14