Removing and adding observations specific to an id variable within a dataframe of multiple ids in R

vheim 0
Viewed 29

I have a dataframe containing location data of different animals. Each animal has a unique id and each observation has a time stamp and some further metrics of the location observation. See a subset of the data below. The subset contains the first two observations of each id.

> sub
       id lc      lon     lat     a    b   c                date
1     111  3 -79.2975 25.6996   414   51  77 2019-04-01 22:08:50
2     111  3 -79.2975 25.6996   414   51  77 2019-04-01 22:08:50
3     222  3 -79.2970 25.7001   229   78  72 2019-01-07 20:36:27
4     222  3 -79.2970 25.7001   229   78  72 2019-01-07 20:36:27
5     333  B -80.8211 24.8441 11625 6980  37 2018-12-17 20:45:05
6     333  3 -80.8137 24.8263   155  100  69 2018-12-17 21:00:43
7     444  3 -80.4535 25.0848   501   33 104 2019-10-20 19:44:16
8     444  1 -80.8086 24.8364  6356  126  87 2020-01-18 20:32:28
9     555  3 -77.7211 24.4887   665   45  68 2020-07-12 21:09:17
10    555  3 -77.7163 24.4897   285  129 130 2020-07-12 21:10:35
11    666  2 -77.7221 24.4902  1129   75  66 2020-07-12 21:09:02
12    666  2 -77.7097 24.4905   314  248 164 2020-07-12 21:11:37
13    777  3 -77.7133 24.4820   406   58 110 2020-06-20 11:18:18
14    777  3 -77.7218 24.4844   170   93 107 2020-06-20 11:51:06
15    888  3 -79.2975 25.6996   550   34  79 2017-11-25 19:10:45
16    888  3 -79.2975 25.6996   550   34  79 2017-11-25 19:10:45

However, I need to do some data housekeeping, i.e. I need to include the day/time and location each animal was released. And after that I need to filter out observations for each animal that occurred pre-release of the corresponding animal.

I have a an additional dataframe that contains the necessary release metadata:

> stack
      id          release      lat       lon
1    888 2017-11-27 14:53 25.69201 -79.31534
2    333 2019-01-31 16:09 25.68896 -79.31326
3    222 2019-02-02 15:55 25.70051 -79.31393
4    111 2019-04-02 10:43 25.68534 -79.31341
5    444 2020-03-13 15:04 24.42892 -77.69518
6    666 2020-10-27 09:40 24.58290 -77.69561
7    555 2020-01-21 14:38 24.43333 -77.69637
8    777 2020-06-25 08:54 24.42712 -77.76427

So my question is: how can I add the release information (time and lat/lon) to the dataframe fore each id (while the columns a, b, and c can be NA). And how can I then filter out the observations that occured before each animal's release time? I have been looking into possibilites using dplyr but was not yet able to resolve my issue.

data-manipulation r
1 Answers

You've not provided an easy way of obtaining your data (dput()) is by far the best and you have issues with your date time values (release uses Y-M-D H:M whereas date uses Y:M:D H:M:S) so for clarity I've included code to obtain the data frames I use at the end of this post.

First, the solution:

library(tidyverse)
library(lubridate)

sub %>% 
  left_join(stack, by="id") %>% 
  mutate(
    release=ymd_hms(paste0(release, ":00")),
    date=ymd_hms(date)
  ) %>% 
  filter(date >= release)
   id lc    lon.x   lat.x   a   b   c                date             release    lat.y     lon.y
1 555  3 -77.7211 24.4887 665  45  68 2020-07-12 21:09:17 2020-01-21 14:38:00 24.43333 -77.69637
2 555  3 -77.7163 24.4897 285 129 130 2020-07-12 21:10:35 2020-01-21 14:38:00 24.43333 -77.69637

As I indicated in comments.

To obtain the data

sub <-  read.table(textConnection("id lc      lon     lat     a    b   c                date
1     111  3 -79.2975 25.6996   414   51  77 '2019-04-01 22:08:50'
2     111  3 -79.2975 25.6996   414   51  77 '2019-04-01 22:08:50'
3     222  3 -79.2970 25.7001   229   78  72 '2019-01-07 20:36:27'
4     222  3 -79.2970 25.7001   229   78  72 '2019-01-07 20:36:27'
5     333  B -80.8211 24.8441 11625 6980  37 '2018-12-17 20:45:05'
6     333  3 -80.8137 24.8263   155  100  69 '2018-12-17 21:00:43'
7     444  3 -80.4535 25.0848   501   33 104 '2019-10-20 19:44:16'
8     444  1 -80.8086 24.8364  6356  126  87 '2020-01-18 20:32:28'
9     555  3 -77.7211 24.4887   665   45  68 '2020-07-12 21:09:17'
10    555  3 -77.7163 24.4897   285  129 130 '2020-07-12 21:10:35'
11    666  2 -77.7221 24.4902  1129   75  66 '2020-07-12 21:09:02'
12    666  2 -77.7097 24.4905   314  248 164 '2020-07-12 21:11:37'
13    777  3 -77.7133 24.4820   406   58 110 '2020-06-20 11:18:18'
14    777  3 -77.7218 24.4844   170   93 107 '2020-06-20 11:51:06'
15    888  3 -79.2975 25.6996   550   34  79 '2017-11-25 19:10:45'
16    888  3 -79.2975 25.6996   550   34  79 '2017-11-25 19:10:45'"), header=TRUE)

stack <- read.table(textConnection("id          release      lat       lon
1    888 '2017-11-27 14:53' 25.69201 -79.31534
2    333 '2019-01-31 16:09' 25.68896 -79.31326
3    222 '2019-02-02 15:55' 25.70051 -79.31393
4    111 '2019-04-02 10:43' 25.68534 -79.31341
5    444 '2020-03-13 15:04' 24.42892 -77.69518
6    666 '2020-10-27 09:40' 24.58290 -77.69561
7    555 '2020-01-21 14:38' 24.43333 -77.69637
8    777 '2020-06-25 08:54' 24.42712 -77.76427"), header=TRUE)
user53nU0
Related