63

I need to create a column with unique ID, basically add the row number as an own column. My current data frame looks like this:

   V1  V2
1  23  45
2  45  45
3  56  67

How to make it look like this:

V1  V2  V3
1  23  45
2  45  45
3  56  67

?

Many thanks

Jaap
  • 81,064
  • 34
  • 182
  • 193
user10745
  • 637
  • 1
  • 5
  • 8

10 Answers10

65

Two alternatives (using sgibb's example data):

tibble::rowid_to_column(d, "ID")

which gives:

  ID V1 V2
1  1 23 45
2  2 45 45
3  3 56 67

Or:

dplyr::mutate(d, ID = row_number())

which gives:

  V1 V2 ID
1 23 45  1
2 45 45  2
3 56 67  3

As you can see, the rowid_to_column-function adds the new column in front of the other ones while the mutate&row_number()-combo adds the new column after the others.

And another base R alternative:

d$ID <- seq_along(d[,1])
Jaap
  • 81,064
  • 34
  • 182
  • 193
  • 2
    Curiously, the `mutate` and `seq_along` solutions do not work for `data.table`. – James Hirschorn Aug 17 '18 at 22:04
  • 1
    @JamesHirschorn Besides the method as shown by @altabq (which is the preferred one for [tag:data.table]), you could do `seq_along(d[[1]])` when `d` is a `data.table`. – Jaap Feb 19 '20 at 13:12
31

You could use cbind:

d <- data.frame(V1=c(23, 45, 56), V2=c(45, 45, 67))

## enter id here, you could also use 1:nrow(d) instead of rownames
id <- rownames(d)
d <- cbind(id=id, d)

## set colnames to OP's wishes
colnames(d) <- paste0("V", 1:ncol(d))

EDIT: Here a comparison of @dacko suggestions. d$id <- seq_len(nrow(d) is slightly faster, but the order of the columns is different (id is the last column; reorder them seems to be slower than using cbind):

library("microbenchmark")

set.seed(1)
d <- data.frame(V1=rnorm(1e6), V2=rnorm(1e6))

cbindSeqLen <- function(x) {
  return(cbind(id=seq_len(nrow(x)), x))
}

dickoa <- function(x) {
  x$id <- seq_len(nrow(x))
  return(x)
}

dickoaReorder <- function(x) {
  x$id <- seq_len(nrow(x))
  nc <- ncol(x)
  x <- x[, c(nc, 1:(nc-1))]
  return(x)
}

microbenchmark(cbindSeqLen(d), dickoa(d), dickoaReorder(d), times=100)

# Unit: milliseconds
#             expr      min       lq   median       uq      max neval
#   cbindSeqLen(d) 23.00683 38.54196 40.24093 42.60020 47.73816   100
#        dickoa(d) 10.70718 36.12495 37.58526 40.22163 72.92796   100
# dickoaReorder(d) 19.25399 68.46162 72.45006 76.51468 88.99620   100
sgibb
  • 25,396
  • 3
  • 68
  • 74
  • 5
    Why not `d$id <- seq_len(nrow(d))` and then `colnames(d) <- paste0("V", 1:ncol(d))` – dickoa May 05 '13 at 13:30
  • 1
    @dickoa: I just have not thought of it. Please see my edit. Your solution is a bit faster but doesn't preserve the order of the columns (but this isn't important in most cases). – sgibb May 05 '13 at 15:34
31

Many presented their ideas, but I think this is the sortest and simplest code for this task:

data$ID <- 1:nrow(data)

One line. The one and only.

Eric Lino
  • 429
  • 4
  • 10
  • 2
    True, but if your data has 0 rows, then I guess you have no data at all. Therefore, why would you need to create an ID for it? – Eric Lino Aug 17 '18 at 22:51
  • 3
    In my case, it was inside a function call where the `dataframe` is passed as an argument and is not known in advance. Could have 10 rows one time, 0 the next. – James Hirschorn Aug 17 '18 at 22:55
  • 1
    This worked perfectly for me. Used arrange() first, and then applied 1:nrow() creating a new variable of sequential IDs. Thank you for this simple solution. – amsloa Jul 14 '19 at 11:52
  • `data <- cbind(data, 1:nrow(data))` and then followed by `names(data)[names(data)=="1:nrow(data)"] <- "ID"` would be the [Wikibooks](https://en.wikibooks.org/wiki/R_Programming) way of doing it. – PolII Aug 10 '21 at 12:34
25

You could also do this using dplyr:

DF <- mutate(DF, id = rownames(DF))
WhiskeyGolf
  • 359
  • 3
  • 2
11

data.table solution

Easier syntax and much faster

library(data.table)

dt <- data.table(V1=c(23, 45, 56), V2=c(45, 45, 67))

setnames(dt, c("V2", "V3")) # changing column names
dt[, V1 := .I] # Adding ID column
Jaap
  • 81,064
  • 34
  • 182
  • 193
altabq
  • 1,322
  • 1
  • 20
  • 33
6

Hope this will help. Shortest and best way to create ID column is:

dataframe$ID <- seq.int(nrow(dataframe))
Jaap
  • 81,064
  • 34
  • 182
  • 193
mehakVT
  • 167
  • 1
  • 8
5

If you're starting without named rows in your df, the tidy way is:

df %>% 
  mutate(id = row_number()) %>% 
  select(id, everything())
Andrew McCartney
  • 191
  • 2
  • 10
  • How is this different compared to [this answer](https://stackoverflow.com/a/50909550/2204410)? – Jaap Sep 25 '22 at 09:27
4

Here is a solution that keeps the dplyr piping format and places id in the first column, which may be preferred. 

d %>% 
  mutate(id = rownames(.)) %>% 
  select(id, everything())
Jope
  • 176
  • 7
  • 3
    Suggestion: `relocate(id)` instead of the select statement is more consicse. – hyman Mar 09 '22 at 08:02
  • How is this different compared to [this answer](https://stackoverflow.com/a/26537145/2204410)? – Jaap Sep 25 '22 at 09:29
  • There is now a `.before` in mutate to control where your new column appears (and not default to far right). So you can do within the mutate: `d %>% mutate(id = rownames(.), .before=everything()) ` or if you don't like the rownames use `id = row_number()` as per Andrew McCartney's solution. – micstr Feb 16 '23 at 08:16
0

The function rownames_to_column() moves rownames into a column; found in the tidyverse package (docs).

rownames_to_column(DF, "my_column_name")

Use column_to_rownames() for the reverse operation.

Tobi Obeck
  • 1,918
  • 1
  • 19
  • 31
0

If your database is not too large this will work

# Load sample data
Dt1 <- tibble(V1=c(23,45,56),V2=c(45,45,67))
# Create Separate Tibble with row numbers
Dt2 <- tibble(id=seq(1:nrow(Dt1)))
# Join together
Dt3 <- cbind(Dt2,Dt1)
Harold Henson
  • 31
  • 5