How to find unsorted fragments of data frame

user_7383971 0
Viewed 142

Let's assume that I've got a data.frame that is supposed to be sorted with respect to selected columns and I want to make sure that it is indeed a case. I could try something like:

library(dplyr)
mpg2 <- mpg %>% 
  arrange(manufacturer, model, year)
identical(mpg, mpg2)
[1] FALSE

but if the identical returns FALSE this only lets me know that a dataset is in incorrect order.

  1. What if I would like to inspect only those rows that are in fact in incorrect order? How can I filter those out of the whole dataset? (I need to avoid looping here at best, as the dataset I work with is pretty large)
  2. If the remaining variables (not used for ordering) are different for the same value of manufacturer, model, year, how dplyr::arrange decides which observation comes first? Does it preserve the order from original dataset (mpg here)?
dplyr r sorting
2 Answers

As for the second question, I believe that dplyr::arrange is stable, it preserves the order of the rows when there are ties in the sorting columns.
This can be seen by comparing with the result from base::order. From the help page, section Details (my emphasis):

In the case of ties in the first vector, values in the second are
used to break the ties. If the values are still tied, values in the
later arguments are used to break the tie (see the first example).
The sort used is stable (except for method = "quick"), so any
unresolved ties will be left in their original ordering. 

mpg2 <- mpg %>% 
  arrange(manufacturer, model, year)

i <- with(mpg, order(manufacturer, model, year))
mpg3 <- mpg[i, ]

identical(as.data.frame(mpg2), as.data.frame(mpg3))
#[1] TRUE

The values are identical, except for their classes. So dplyr::arrange does preserve the order in the case of ties.

As for the first question, maybe the code below answers it. It just gets the rows for which the next order number is smaller than the previous one. This means that those rows have changed relative positions.

j <- which(diff(i) < 0)
mpg[i[j], ]
user_82454060

I don't think this is something I've needed before. Usually it's best practice to not rely on table ordering. Only times I would rely on it, the ordering would be contained within a function ie I wouldn't have function B depend on ordering that happens in function A.

I think this does what you ask for, using the data.table package. You set keys with this package, and they are ordered from left to right in terms of primary, secondary key etc. I'm not sure if concatenating the keys together is the best way, but its simple. 

# reproducible fake data
library(data.table)
set.seed(1)
dt <- data.table(a=rep(1:5, 2), b=letters[1:10], c=sample(1:3, 10, TRUE))

# scramble
dt <- dt[sample(1:.N)]

# make the ideal structure
keys <- c("a", "b")
dt_ideal <- copy(dt)
dt_ideal <- setkeyv(dt_ideal, keys)
key(dt_ideal)

# function to find keys not the same for each row. Pasting together
findBad <- function(dt, dt_ideal){
  not_ok <- which(dt_ideal[, do.call(paste, c(.SD, sep=">")), .SDcols=keys] != 
                    dt[, do.call(paste, c(.SD, sep=">")), .SDcols=keys])
  not_ok
}

# index of bad rows - all bad in this case
not_ok <- findBad(dt, dt_ideal)
dt[not_ok]

# better eg, swap 7 & 8
dt2 <- copy(dt_ideal)
dt2 <- dt2[c(1:6, 8, 7, 9:10)]
not_ok <- findBad(dt2, dt_ideal)
dt2[not_ok]
user_59909380
Related