¿Cómo emular SQL “partición por” en R?

data.table dplyr

Question

¿Cómo puedo realizar funciones analíticas como las funciones ROW_NUMBER (), RANK () o DENSE_RANK () de Oracle (consulte http://www.orafaq.com/node/55 ) en un marco de datos R? El paquete CRAN "plyr" está muy cerca, pero sigue siendo diferente.

Estoy de acuerdo en que la funcionalidad de cada función puede lograrse de manera ad hoc. Pero mi principal preocupación es el rendimiento. Sería bueno evitar el uso de la unión o el acceso a la indexación, por el bien de la memoria y la velocidad.

Answer 1

Desde data.table v1.9.5+ , se ha implementado la función frank() (para rango rápido ). frank() es útil en escenarios interactivos, donde como frankv() permite programar fácilmente con.

Implementa todas las operaciones disponibles en base::rank . Además, las ventajas son:

• frank() opera en listas , marcos de datos y tablas de datos además de vectores atómicos .

• Podemos especificar, para cada columna, si el rango debe calcularse en orden creciente o decreciente.

• También implementa un tipo de rango dense además de otros tipos en base .

• Puede usar - en una columna de caracteres también para clasificar por orden decreciente.

Aquí hay una ilustración de todos los puntos anteriores utilizando la misma tabla de datos de DT de @BenBarnes ''(excelente).

datos:

require(data.table)
set.seed(10)
sample_n <- function(x, n) sample(x, n, replace=TRUE)
DT <- data.table(
        ID = seq_len(4*3),
        group = rep(1:4,each=3),
        value = rnorm(4*3),
        info = c(sample_n(letters[1:2], 8), sample_n(letters[3:4], 4)))

En columnas individuales:

• Calcular el rango dense :

  DT[, rank := frank(value, ties.method="dense"), by=group]

También puedes usar los otros métodos min , max , random , average y first .

• En orden decreciente:

  DT[, rank := frank(-value, ties.method="dense"), by=group]

• Utilizando frankv , similar a frank :

  # increasing order
  frankv(DT, "value", ties.method="dense")
  
  # decreasing order
  frankv(DT, "value", order=-1L, ties.method="dense")

En multiples columnas

Puede usar .SD , que significa subconjunto de datos y contiene los datos correspondientes a ese grupo. Consulte la Introducción a la viñeta HTML de data.table para más información sobre .SD .

• Clasificar por info, value columnas mientras se agrupan por group :

  DT[, rank := frank(.SD,  info, value, ties.method="dense"), by=group]

• Use - para especificar el orden decreciente:

  DT[, rank := frank(.SD,  info, -value, ties.method="dense"), by=group]

• También puede usar - directamente en columnas de caracteres

  DT[, rank := frank(.SD, -info, -value, ties.method="dense"), by=group]

Puede usar frankv manera similar y proporcionar las columnas al argumento cols y el orden según el cual las columnas deben clasificarse utilizando el argumento order .

Pequeño punto de referencia para comparar con base::rank :

set.seed(45L)
x = sample(1e4, 1e7, TRUE)
system.time(ans1 <- base::rank(x, ties.method="first"))
#    user  system elapsed 
#  22.200   0.255  22.536 
system.time(ans2 <- frank(x, ties.method="first"))
#    user  system elapsed 
#   0.745   0.014   0.762 
identical(ans1, ans2) # [1] TRUE

Answer 2

Me gusta data.table tanto como el siguiente tipo, pero no siempre es necesario. data.table siempre será más rápido, pero incluso para conjuntos de datos moderadamente grandes, si el número de grupos es bastante pequeño, plyr seguirá funcionando adecuadamente.

Lo que hizo BenBarnes usando data.table s se puede hacer de manera tan compacta (pero como señalé antes, probablemente más lento en muchos casos) usando plyr :

library(plyr)                
ddply(DT,.(group),transform,valRank = rank(-value))
ddply(DT,.(group),transform,valRank = rank(info,ties.method = "min"),
                            valRankDense = as.integer(factor(info)))

e incluso sin cargar un solo paquete adicional en absoluto:

do.call(rbind,by(DT,DT$group,transform,valRank = rank(-value)))
do.call(rbind,by(DT,DT$group,transform,valRank = rank(info,ties.method = "min"),
                                        valRankDense = as.integer(factor(info))))

aunque pierdas algunas de las sutilezas sintácticas en ese último caso.

Answer 3

No creo que haya un equivalente directo a las funciones analíticas de Oracle. Plyr probablemente podrá lograr algunas de las funciones analíticas, pero no todas directamente. Estoy seguro de que R puede replicar cada función por separado, pero no creo que haya un solo paquete que lo haga todo.

Si hay una operación específica que debes lograr en R, entonces haz un poco de googlear, y si te quedas vacío, haz una pregunta específica aquí en .

Answer 4

El paquete data.table , especialmente a partir de la versión 1.8.1, ofrece gran parte de la funcionalidad de la partición en términos de SQL. rank(x, ties.method = "min") en R es similar a Oracle RANK() , y hay una manera de usar los factores (descritos a continuación) para imitar la función DENSE_RANK() . Una forma de imitar ROW_NUMBER debería ser obvia al final.

Aquí hay un ejemplo: Cargue la última versión de data.table de R-Forge:

install.packages("data.table",
  repos= c("http://R-Forge.R-project.org", getOption("repos")))

library(data.table)

Crea algunos datos de ejemplo:

set.seed(10)

DT<-data.table(ID=seq_len(4*3),group=rep(1:4,each=3),value=rnorm(4*3),
  info=c(sample(c("a","b"),4*2,replace=TRUE),
  sample(c("c","d"),4,replace=TRUE)),key="ID")

> DT
    ID group       value info
 1:  1     1  0.01874617    a
 2:  2     1 -0.18425254    b
 3:  3     1 -1.37133055    b
 4:  4     2 -0.59916772    a
 5:  5     2  0.29454513    b
 6:  6     2  0.38979430    a
 7:  7     3 -1.20807618    b
 8:  8     3 -0.36367602    a
 9:  9     3 -1.62667268    c
10: 10     4 -0.25647839    d
11: 11     4  1.10177950    c
12: 12     4  0.75578151    d

Clasifique cada ID disminuyendo el value dentro del group (tenga en cuenta que - delante del value para indicar el orden decreciente):

> DT[,valRank:=rank(-value),by="group"]
    ID group       value info valRank
 1:  1     1  0.01874617    a       1
 2:  2     1 -0.18425254    b       2
 3:  3     1 -1.37133055    b       3
 4:  4     2 -0.59916772    a       3
 5:  5     2  0.29454513    b       2
 6:  6     2  0.38979430    a       1
 7:  7     3 -1.20807618    b       2
 8:  8     3 -0.36367602    a       1
 9:  9     3 -1.62667268    c       3
10: 10     4 -0.25647839    d       3
11: 11     4  1.10177950    c       1
12: 12     4  0.75578151    d       2

Para DENSE_RANK() con vínculos en el valor que se está clasificando, puede convertir el valor en un factor y luego devolver los valores enteros subyacentes. Por ejemplo, clasifique cada ID según la info dentro del group (compare infoRank con infoRankDense ):

DT[,infoRank:=rank(info,ties.method="min"),by="group"]
DT[,infoRankDense:=as.integer(factor(info)),by="group"]

R> DT
    ID group       value info valRank infoRank infoRankDense
 1:  1     1  0.01874617    a       1        1             1
 2:  2     1 -0.18425254    b       2        2             2
 3:  3     1 -1.37133055    b       3        2             2
 4:  4     2 -0.59916772    a       3        1             1
 5:  5     2  0.29454513    b       2        3             2
 6:  6     2  0.38979430    a       1        1             1
 7:  7     3 -1.20807618    b       2        2             2
 8:  8     3 -0.36367602    a       1        1             1
 9:  9     3 -1.62667268    c       3        3             3
10: 10     4 -0.25647839    d       3        2             2
11: 11     4  1.10177950    c       1        1             1
12: 12     4  0.75578151    d       2        2             2

ps Hola Matthew Dowle.

LEAD y LAG

Para imitar LEAD y LAG, comience con la respuesta que se proporciona here . Yo crearía una variable de rango basada en el orden de las ID dentro de los grupos. Esto no sería necesario con los datos falsos como se indicó anteriormente, pero si las ID no están en orden secuencial dentro de los grupos, esto dificultaría un poco la vida. Así que aquí hay algunos nuevos datos falsos con ID no secuenciales:

set.seed(10)

DT<-data.table(ID=sample(seq_len(4*3)),group=rep(1:4,each=3),value=rnorm(4*3),
  info=c(sample(c("a","b"),4*2,replace=TRUE),
  sample(c("c","d"),4,replace=TRUE)),key="ID")

DT[,idRank:=rank(ID),by="group"]
setkey(DT,group, idRank)

> DT
    ID group       value info idRank
 1:  4     1 -0.36367602    b      1
 2:  5     1 -1.62667268    b      2
 3:  7     1 -1.20807618    b      3
 4:  1     2  1.10177950    a      1
 5:  2     2  0.75578151    a      2
 6: 12     2 -0.25647839    b      3
 7:  3     3  0.74139013    c      1
 8:  6     3  0.98744470    b      2
 9:  9     3 -0.23823356    a      3
10:  8     4 -0.19515038    c      1
11: 10     4  0.08934727    c      2
12: 11     4 -0.95494386    c      3

Luego, para obtener los valores del registro 1 anterior, use las variables group e idRank y reste 1 del idRank y use el argumento multi = ''last'' . Para obtener el valor del registro de las dos entradas anteriores, reste 2 .

DT[,prev:=DT[J(group,idRank-1), value, mult=''last'']]
DT[,prev2:=DT[J(group,idRank-2), value, mult=''last'']]

    ID group       value info idRank        prev      prev2
 1:  4     1 -0.36367602    b      1          NA         NA
 2:  5     1 -1.62667268    b      2 -0.36367602         NA
 3:  7     1 -1.20807618    b      3 -1.62667268 -0.3636760
 4:  1     2  1.10177950    a      1          NA         NA
 5:  2     2  0.75578151    a      2  1.10177950         NA
 6: 12     2 -0.25647839    b      3  0.75578151  1.1017795
 7:  3     3  0.74139013    c      1          NA         NA
 8:  6     3  0.98744470    b      2  0.74139013         NA
 9:  9     3 -0.23823356    a      3  0.98744470  0.7413901
10:  8     4 -0.19515038    c      1          NA         NA
11: 10     4  0.08934727    c      2 -0.19515038         NA
12: 11     4 -0.95494386    c      3  0.08934727 -0.1951504

Para LEAD, agregue el desplazamiento apropiado a la variable idRank y cambie a multi = ''first'' :

DT[,nex:=DT[J(group,idRank+1), value, mult=''first'']]
DT[,nex2:=DT[J(group,idRank+2), value, mult=''first'']]

    ID group       value info idRank        prev      prev2         nex       nex2
 1:  4     1 -0.36367602    b      1          NA         NA -1.62667268 -1.2080762
 2:  5     1 -1.62667268    b      2 -0.36367602         NA -1.20807618         NA
 3:  7     1 -1.20807618    b      3 -1.62667268 -0.3636760          NA         NA
 4:  1     2  1.10177950    a      1          NA         NA  0.75578151 -0.2564784
 5:  2     2  0.75578151    a      2  1.10177950         NA -0.25647839         NA
 6: 12     2 -0.25647839    b      3  0.75578151  1.1017795          NA         NA
 7:  3     3  0.74139013    c      1          NA         NA  0.98744470 -0.2382336
 8:  6     3  0.98744470    b      2  0.74139013         NA -0.23823356         NA
 9:  9     3 -0.23823356    a      3  0.98744470  0.7413901          NA         NA
10:  8     4 -0.19515038    c      1          NA         NA  0.08934727 -0.9549439
11: 10     4  0.08934727    c      2 -0.19515038         NA -0.95494386         NA
12: 11     4 -0.95494386    c      3  0.08934727 -0.1951504          NA         NA