Flag first by-group in R data frame

Question

I have a data frame which looks like this:

I'd like to identify a way to flag the first occurrence of id -- similar to first. and last. in SAS. I've tried the !duplicated function, but I need to actually append the "flag" column to my data frame since I'm running it through a loop later on. I'd like to get something like this:

id  score   first_ind
1   15      1
1   18      0
1   16      0
2   10      1
2   9       0
3   8       1
3   47      0
3   21      0

score 17 · Accepted Answer · answered Oct 08 '14 at 20:29

17

> df$first_ind <- as.numeric(!duplicated(df$id))
> df
  id score first_ind
1  1    15         1
2  1    18         0
3  1    16         0
4  2    10         1
5  2     9         0
6  3     8         1
7  3    47         0
8  3    21         0

answered Oct 08 '14 at 20:29

Jilber Urbina

58,147
10
114
138

2

Also `(!duplicated(df$id))+0` is kinda neat. – Rich Scriven Oct 08 '14 at 20:32
3

@RichardScriven: that falls in my "too clever" category ... good for code golf, but otherwise a bad idea ... – Ben Bolker Oct 08 '14 at 20:48

Roman Luštrik · Answer 2 · 2014-10-09T10:23:58.217

6

You can find the edges using diff.

x <- read.table(text = "id  score
1   15
1   18
1   16
2   10
2   9
3   8
3   47
3   21", header = TRUE)

x$first_id <- c(1, diff(x$id))
x

  id score first_id
1  1    15        1
2  1    18        0
3  1    16        0
4  2    10        1
5  2     9        0
6  3     8        1
7  3    47        0
8  3    21        0

edited Oct 09 '14 at 10:23

answered Oct 08 '14 at 20:28

Roman Luštrik

69,533
24
154
197

This will only work as long as the id-entries are sorted in increasing order and numbered successively, right? Please correct me if I'm wrong. – talat Oct 09 '14 at 11:33

Ben Bolker · Answer 3 · 2014-10-09T12:08:52.303

3

Using plyr:

library("plyr")
ddply(x,"id",transform,first=as.numeric(seq(length(score))==1))

or if you prefer dplyr:

x %>% group_by(id) %>% 
    mutate(first=c(1,rep(0,n-1)))

(although if you're operating completely in the plyr/dplyr framework you probably wouldn't need this flag variable anyway ...)

edited Oct 09 '14 at 12:08

answered Oct 08 '14 at 20:32

Ben Bolker

211,554
25
370
453

2

In the `dplyr` version, do you mean `mutate` instead of `transform`? You could also write `df %>% group_by(id) %>% mutate(first = row_number() == 1)` (if logicals are acceptable) – talat Oct 09 '14 at 11:20

score 2 · Answer 4 · answered Oct 09 '14 at 11:14

Another base R option:

df$first_ind <- ave(df$id, df$id, FUN = seq_along) == 1
df
#  id score first_ind
#1  1    15      TRUE
#2  1    18     FALSE
#3  1    16     FALSE
#4  2    10      TRUE
#5  2     9     FALSE
#6  3     8      TRUE
#7  3    47     FALSE
#8  3    21     FALSE

This also works in case of unsorted ids. If you want 1/0 instead of T/F you can easily wrap it in as.integer(.).

Flag first by-group in R data frame

4 Answers4

Linked

Related