How do I make a variable that represents the row number, when rows are not sorted in order?

Question

Pic shows the row number order

I am trying to add a variable to my data set that represents the row number; however every code I've found adds them in order as the rows are currently (1,2,3,4,5), rather than in the order the View option shows (129, 98, 21, 09). I need the order shown in the View option, as I am trying to merge with a another data set, and need the correct ("original row number").

I cannot add row numbers before making changes to the data set as the function doesn't work when I add the ID number.

Alternatively, being able to sort the data by row number would also help, but I don't know how to do that either (clicking on the arrow above the row number does nothing).

A bit of context

I am classifying network nodes in R. I made a matrix from the networks nodes and edges (using nodes2vec), and have to merge this matrix with nodes labels data set (this data set contains one variable which shows if nodes are positive or negative). The picture above shows the created matrix, and the original row numbers from the network data set are no longer in the original order. I need to add a variable to the matrix, that I converted to a data frame using:

netdf1 <- as.data.frame(network.node2vec)

that represents the original row number

what I tried

netdf1 <- netdf1 %>% mutate(id = row_number())

This just adds the row number as the rows are currently ordered so 1,2,3,4...

WHAT WORKED IN THE END == CORRECT ANSWER

db$ID <- rownames(db)

You should put together a small reproducible example, but something like this might do it: ` x$ID <- row.names(x)` — user2554330, Feb 28 '21 at 20:47
as far as I can tell from your description, both of the answers below should work. Can you add `dput(head(netdf1,4))` **as text** to your question please? — Ben Bolker, Feb 28 '21 at 23:45
https://stackoverflow.com/questions/29511215/convert-row-names-into-first-column — Ronak Shah, Mar 01 '21 at 07:04

score 1 · Answer 1 · answered Feb 28 '21 at 20:47

If I do understand your question right you have some kind of dataframe with row names that are not continuus? And now you want to have these row names in an extra column as numeric values?

You can use the row.names()-function and can convert them to numeric if you like:

# just creating a DF that might show what you mean:     
testDF <- data.frame(x = 1:10, y = sample((1:1000), 10))    
testDF <- testDF[testDF$y < 500,]    
View(testDF)    
# one possible way to get the row names
testDF$rowNum <- as.numeric(row.names(testDF))

And try to type ?sort to the console if you like to learn something about sorting vectors.

Unfortunately this is not it (i think). I edited the question a bit, I hope its more clear. — Milica Vdovic, Feb 28 '21 at 20:59

score 1 · Answer 2 · answered Feb 28 '21 at 23:11

Let's say you have a data frame with row names that are out of order:

my_data <- data.frame(row.names = 5:1,
                      V1 = 1:5)

#> my_data
#  V1
#5  1
#4  2
#3  3
#2  4
#1  5

dplyr::row_number() will add row numbers based on the current sorting, not based on the row names. (A general practice in the tidyverse is to eschew keeping useful data in the row names and to instead incorporate any sorts of row ID info into a variable.)

So you could use @user2554330's advice and add my_data$ID <- row.names(my_data) or the tidyverse equivalent of my_data %>% tibble::rownames_to_column(var = "ID"), then sort by that column.

my_data %>% 
  tibble::rownames_to_column(var = "ID") %>%
  arrange(ID)

  ID V1
1  1  5
2  2  4
3  3  3
4  4  2
5  5  1

How do I make a variable that represents the row number, when rows are not sorted in order?

A bit of context

what I tried

WHAT WORKED IN THE END == CORRECT ANSWER

2 Answers2