Join current and previous dataframes in nested tibble using lag() and mutate() to produce a new list-column

user_14423746 0
Viewed 70

I am trying to determine the difference between the set of ids in subsequent pairs of dataframes. The dataframes are derived from an original dataframe split by a grouping variable representing the time period. The results should show the rows of the new ids that occur in the current time period compared to the previous one.

I can accomplish this with a list of dataframes:

library(tidyverse)
set.seed(999)

examp <- tibble(
  id = c(replicate(4, sample.int(20, 9))),
  year = rep(1:4, each = 9),
  val = runif(36)
)

examp %>% 
  split(.$year) %>%
  # note my default, I compare the first year to itself
  map2(lag(., default = .[1]), anti_join, by = "id")

$`1`
# A tibble: 0 x 3
# ... with 3 variables: id <int>, year <int>, val <dbl>

$`2`
# A tibble: 3 x 3
     id  year   val
  <int> <int> <dbl>
1     5     2 0.450
2    11     2 0.943
3     2     2 0.571

$`3`
# A tibble: 6 x 3
     id  year   val
  <int> <int> <dbl>
1    19     3 0.870
2    12     3 0.403
3     9     3 0.331
4    20     3 0.315
5    16     3 0.455
6    17     3 0.699

$`4`
# A tibble: 5 x 3
     id  year    val
  <int> <int>  <dbl>
1     4     4 0.190
2    11     4 0.0804
3     2     4 0.247
4     1     4 0.619
5    18     4 0.434

But I could not get the same to work using mutate in a nested dataframe:

examp %>%
  nest_by(year) %>%
  mutate(new = anti_join(data, lag(data), by = "id"))

# A tibble: 4 x 3
# Rowwise:  year
   year               data new$id   $val
  <int> <list<tibble[,2]>>  <int>  <dbl>
1     1            [9 x 2]      3 0.0601
2     2            [9 x 2]      1 0.495 
3     3            [9 x 2]     17 0.699 
4     4            [9 x 2]     18 0.434

Here I could not figure out how to specify the default and the output is unexpected. I expected "new" to be a list-column of dataframes corresponding with those above, which I could then unnest.

I am interested in learning more about working with nested dataframes and any help understanding how to get this to work would be much appreciated. Additionally, if there is another (simple) solution to this general problem, I would be happy to learn about it.

tibble dplyr r nested
1 Answers

It should be wrapped in a list

library(dplyr)
out <- examp %>% 
    nest_by(year) %>% 
    ungroup %>%
     mutate(newdat = lag(data, default = data[1])) %>% 
     rowwise %>%
     mutate(new = list(anti_join(data, newdat, by = 'id')))

-output

 out$new
[[1]]
# A tibble: 0 x 2
# … with 2 variables: id <int>, val <dbl>

[[2]]
# A tibble: 3 x 2
     id   val
  <int> <dbl>
1     5 0.450
2    11 0.943
3     2 0.571

[[3]]
# A tibble: 6 x 2
     id   val
  <int> <dbl>
1    19 0.870
2    12 0.403
3     9 0.331
4    20 0.315
5    16 0.455
6    17 0.699

[[4]]
# A tibble: 5 x 2
     id    val
  <int>  <dbl>
1     4 0.190 
2    11 0.0804
3     2 0.247 
4     1 0.619 
5    18 0.434
user_37322710
Related