CODEWITHSUNDEEP
rdata.table
Michael
Michael

Reputation: 353

R: Swap Data Table Rows based on column values

I have a data table with two columns: "id" and "prevId". I want to find all instances where one row has a "prevId" value equivalent to the "id" value of a row that comes after it. I then want to swap the order of those rows in the data table.

So far, I have tried messing around with the logic below:

data[, index:=.I]
data[, priorMatch:=index[1L], by = cumsum(prevId == id)]
apply(records, 1, FUN = function(x) {
if (!is.na(priorMatch)) {
  records[x[["index"]],] <- records[x[["index"]] - 1,]
  records[x[["index"]] - 1,] <- x
}
})

However, cumsum() does not actually take me the earliest prior row with a "prevId" equivalent to the present row's "id".

An example of desired results:

id prevId
ef     cd
cd     ab

Would be transformed to:

id prevId
cd     ab
ef     cd

Any assistance is appreciated. Thanks!

Upvotes: 1

Views: 778

Answers (2)

Vivek Kalyanarangan
Vivek Kalyanarangan

Reputation: 9081

Use - 

df <- data.frame(id=c("ef", "cd"), "prevId"=c("cd", "ab"))
rows <- which(as.character(df[,"id"]) == as.character(shift(df[,"prevId"])))
for(i in 1:length(rows)){
  index <- rows[i]
  swap_index <- index - 1

  temp <- df[index, ]
  df[index, ] <- df[swap_index, ]
  df[swap_index, ] <- temp
}

Output

  id prevId
1 cd     ab
2 ef     cd

Upvotes: 0

Wimpel
Wimpel

Reputation: 27732

This will do the trick (at least for the sample data provided)

dt <- fread("id prevId
             ef     cd
             cd     ab")

#get rownumbers
dt[, row := .I]
#create a column with 'new' rownumbers to order on, using shift
dt[ prevId == shift(id, 1, type = "lead") , row := row + 1]
dt[ id == shift(prevId, 1, type = "lag") , row := row - 1]
#reorder
setkey(dt, row)
#drop the row-column
dt[, row := NULL][]
#    id prevId
# 1: cd     ab
# 2: ef     cd

Upvotes: 1

Related Questions