Incremental IDs in a R data frame

Question

I have the following data frame:

> test = data.frame(A = sample(1:5, 10, replace = T)) %>% arrange(A)
> test

   A
1  1
2  1
3  1
4  2
5  2
6  2
7  2
8  4
9  4
10 5

I now want every row to have an ID that is only incremented when the value of A changes. This is what I have tried:

> test = test %>% mutate(id = as.numeric(rownames(test))) %>% group_by(A) %>% mutate(id = min(id))
> test

       A    id
   (int) (dbl)
1      1     1
2      1     1
3      1     1
4      2     4
5      2     4
6      2     4
7      2     4
8      4     8
9      4     8
10     5    10

However, I would like to get the following:

       A    id
   (int) (dbl)
1      1     1
2      1     1
3      1     1
4      2     2
5      2     2
6      2     2
7      2     2
8      4     3
9      4     3
10     5     4

Is your A column always increasing? Does "when the value of A changes" mean that for A = 3 3 4 3, you want 1 1 2 3, for example? — Frank, Feb 12 '16 at 19:02
Initially, my A column is unsorted, but I will sort it before assigning the IDs. So A = 3 3 4 3 should be 1 1 2 1 because I will sort it to be A = 3 3 3 4 — aseipel, Feb 12 '16 at 19:15
Ok, thanks for clarifying. Let us know if the linked question (in a banner at the top, if you refresh the page) doesn't work for you. — Frank, Feb 12 '16 at 19:16

score 6 · Accepted Answer · answered Feb 12 '16 at 18:59

6

library(dplyr)

test %>% mutate(id = dense_rank(A))

answered Feb 12 '16 at 18:59

davechilders

8,693
2
18
18

akrun · Answer 2 · 2016-02-12T19:13:06.630

5

One compact option would be using data.table. Convert the 'data.frame' to 'data.table' (setDT(test)), grouped by 'A', we assign (:=) .GRP as the new 'id' column. The .GRP will be a sequence of values for each unique value in 'A'.

library(data.table)
setDT(test)[, id:=.GRP, A]

In case the value of 'A' changes like 3, 3, 4, 3 and we want 1, 1, 2, 3 forthe 'id'

setDT(test)[, id:= rleid(A)]

Or we convert 'A' to factor class and then coerce it back to numeric/integer

library(dplyr)
test %>%
    mutate(id = as.integer(factor(A)))

Or we can match 'A' with the unique values in 'A'.

test %>%
     mutate(id = match(A, unique(A)))

Or from the dplyr version > 0.4.0, we can use group_indices (it is in the dupe link)

test %>%
      mutate(id=group_indices_(test, .dots= "A"))

edited Feb 12 '16 at 19:13

answered Feb 12 '16 at 18:58

akrun

874,273
37
540
662

1

Dplyr has `group_indices` to go with data.table's `.GRP`. I recall it being buggy, though. http://stackoverflow.com/q/23026145/1191259 – Frank Feb 12 '16 at 19:05
@Frank Looks like the `group_indices` is not working with `dplyr_0.4.3` – akrun Feb 12 '16 at 19:09
@Frank If I use `test %>% group_indices(A)` it gives a vector. – akrun Feb 12 '16 at 19:10
You should add some of these solutions to the linked question? – Pierre L Feb 12 '16 at 19:11
@Frank Never mind, it works – akrun Feb 12 '16 at 19:12
@PierreLafortune You are welcome to add there, thanks .. – akrun Feb 12 '16 at 19:14
How do I go about this if I need incremental ids within each group. For example, {A: group1, group2, group2, group1, group2, group3, group2}, and the corresponding values in the second column are {id: 1,1,2,2,3,1,4}? I can write nested for loops for this, but is there a better way? – Vipin Verma Apr 08 '19 at 21:54
@vipin8169 Please do ask as a new question – akrun Apr 09 '19 at 02:23
1

I did, and got my answer as well. Thanks – Vipin Verma Apr 09 '19 at 02:27

Incremental IDs in a R data frame

2 Answers2

Linked

Related