Finding new observations per grouping in R

user_4451288 0
Viewed 89

I have a dataframe:

id = c('a', 'b', 'a', 'b', 'c', 'a', 'b', 'c', 'd')
period = c(1, 1, 2, 2, 2, 3, 3, 3, 3)
a <- data.frame(period, id); a


  period  id
1      1  a
2      1  b
3      2  a
4      2  b
5      2  c
6      3  a
7      3  b
8      3  c
9      3  d

Now, I want find new observations per period. So I do,

a_group <- a %>% group_by(period) %>% count(id_count = n())
a_news <- a_group %>% ungroup() %>% 
  mutate(new_vals = id_count - lag(id_count))
a_news

       period id_count n new_vals
   <dbl>    <int> <int>    <int>
1      1        2     2       NA
2      2        3     3        1
3      3        4     4        1

This works fine, since for every period there is one new unique observation being added. Consider, when the new id per period is not unique:

id1 = c('a', 'b', 'a', 'b', 'a', 'a', 'b', 'c', 'a')
period1 = c(1, 1, 2, 2, 2, 3, 3, 3, 3)
b <- data.frame(period1, id1); b  

   period1 id1
1       1   a
2       1   b
3       2   a
4       2   b
5       2   a
6       3   a
7       3   b
8       3   c
9       3   a
b_group <- b %>% group_by(period1) %>% count(id1_count = n())
b_news <- b_group %>% ungroup() %>% 
  mutate(new_vals = id1_count - lag(id1_count))

I get the same result as a_news

   period1 id1_count     n new_vals        
1       1         2     2       NA
2       2         3     3        1
3       3         4     4        1

While in fact the new observation a is not new at all from period 2 onwards. How can I find out the count of new observations per period (after period 1)? Since, in period 1 of course everything is new.

Expected output for dataframe b:

period1 new_vals        
1      2
2      0
3      1

Where in period1 == 2, there is no new observation, in period1 == 3 there is one new observation (c)

dplyr r
4 Answers

You can try Reduce with unique:

tt <- split(a$id, a$period) #Split id per period
#Find unique id's until period
n <- lengths(Reduce(function(i,j) unique(c(i,j)), tt, accumulate = TRUE))
setNames(diff(c(0,n)), names(tt))
#1 2 3 
#2 1 1 

tt <- split(b$id1, b$period1)
n <- lengths(Reduce(function(i,j) unique(c(i,j)), tt, accumulate = TRUE))
setNames(diff(c(0,n)), names(tt))
#1 2 3 
#2 0 1
user_104885040

Here is a base R option using aggregate + Vectorize + setdiff

within(
  u <- aggregate(. ~ period1, b, c),
  newVar <- c(NA, lengths(Vectorize(setdiff)(id1[-1], id1[-nrow(u)])))
)

which gives

  period1        id1 newVar
1       1       a, b     NA
2       2    a, b, a      0
3       3 a, b, c, a      1
user_121587570

One dplyr and purrr solution could be:

b %>%
 mutate(new_vals = pmax(row_number() == 1,
                        map_lgl(seq_along(id1), 
                                ~ !id1[.x] %in% unique(id1[1:(.x - 1)])))) %>%
 group_by(period1) %>%
 summarise(count = n(),
           id_count = n_distinct(id1),
           new_vals = sum(new_vals))

  period1 count id_count new_vals
    <dbl> <int>    <int>    <int>
1       1     2        2        2
2       2     3        2        0
3       3     4        3        1
user_59645570

You can do the trick by simply using the unique function:

    a_group <- unique(a) %>% count(period)
    a_group <- cbind(a_group,new_vals = diff(c(0,a_group$n)))

      period n new_vals
1      1     2    2
2      2     2    0
3      3     3    1
user_137417080
Related