Comment faire des vlookup et fill down (comme dans Excel) en R ?

Question

J'ai un ensemble de données d'environ 105 000 lignes et 30 colonnes. J'ai une variable catégorielle que je voudrais affecter à un nombre. Dans Excel, je ferais probablement quelque chose comme VLOOKUP et remplir.

Comment pourrais-je faire la même chose en R ?

Essentiellement, ce que j'ai est un HouseType et j'ai besoin de calculer la variable HouseTypeNo . Voici quelques exemples de données :

HouseType HouseTypeNo
Semi            1
Single          2
Row             3
Single          2
Apartment       4
Apartment       4
Row             3

Answer 1

Si je comprends bien votre question, voici quatre méthodes pour faire l'équivalent de la méthode Excel VLOOKUP et remplir le bas en utilisant R :

# load sample data from Q
hous <- read.table(header = TRUE, 
                   stringsAsFactors = FALSE, 
text="HouseType HouseTypeNo
Semi            1
Single          2
Row             3
Single          2
Apartment       4
Apartment       4
Row             3")

# create a toy large table with a 'HouseType' column 
# but no 'HouseTypeNo' column (yet)
largetable <- data.frame(HouseType = as.character(sample(unique(hous$HouseType), 1000, replace = TRUE)), stringsAsFactors = FALSE)

# create a lookup table to get the numbers to fill
# the large table
lookup <- unique(hous)
  HouseType HouseTypeNo
1      Semi           1
2    Single           2
3       Row           3
5 Apartment           4

Voici quatre méthodes pour remplir le HouseTypeNo dans le largetable en utilisant les valeurs de la lookup table :

D'abord avec merge en base :

# 1. using base 
base1 <- (merge(lookup, largetable, by = 'HouseType'))

Une deuxième méthode avec des vecteurs nommés en base :

# 2. using base and a named vector
housenames <- as.numeric(1:length(unique(hous$HouseType)))
names(housenames) <- unique(hous$HouseType)

base2 <- data.frame(HouseType = largetable$HouseType,
                    HouseTypeNo = (housenames[largetable$HouseType]))

Troisièmement, en utilisant le plyr paquet :

# 3. using the plyr package
library(plyr)
plyr1 <- join(largetable, lookup, by = "HouseType")

Quatrièmement, en utilisant le sqldf paquet

# 4. using the sqldf package
library(sqldf)
sqldf1 <- sqldf("SELECT largetable.HouseType, lookup.HouseTypeNo
FROM largetable
INNER JOIN lookup
ON largetable.HouseType = lookup.HouseType")

S'il est possible que certains types de maison en largetable n'existent pas dans lookup alors une jointure gauche serait utilisée :

sqldf("select * from largetable left join lookup using (HouseType)")

Des modifications correspondantes des autres solutions seraient également nécessaires.

C'est ce que tu voulais faire ? Faites-moi savoir quelle méthode vous préférez et j'ajouterai un commentaire.

Answer 2

Je pense que vous pouvez aussi utiliser match() :

largetable$HouseTypeNo <- with(lookup,
                     HouseTypeNo[match(largetable$HouseType,
                                       HouseType)])

Cela fonctionne toujours si je brouille l'ordre de lookup .

Answer 3

J'aime aussi utiliser qdapTools::lookup ou opérateur binaire abrégé %l% . Il fonctionne de la même manière qu'un vlookup d'Excel, mais il accepte les noms au lieu des numéros de colonne.

## Replicate Ben's data:
hous <- structure(list(HouseType = c("Semi", "Single", "Row", "Single", 
    "Apartment", "Apartment", "Row"), HouseTypeNo = c(1L, 2L, 3L, 
    2L, 4L, 4L, 3L)), .Names = c("HouseType", "HouseTypeNo"), 
    class = "data.frame", row.names = c(NA, -7L))

largetable <- data.frame(HouseType = as.character(sample(unique(hous$HouseType), 
    1000, replace = TRUE)), stringsAsFactors = FALSE)

## It's this simple:
library(qdapTools)
largetable[, 1] %l% hous

Answer 4

L'affiche n'a pas demandé de rechercher des valeurs si exact=FALSE mais je l'ajoute comme réponse pour ma propre référence et peut-être pour d'autres.

Si vous recherchez des valeurs catégorielles, utilisez les autres réponses.

Excel vlookup vous permet également d'établir une correspondance approximative pour les valeurs numériques avec le 4ème argument(1) match=TRUE . Je pense à match=TRUE comme la recherche de valeurs sur un thermomètre. La valeur par défaut est FALSE, ce qui est parfait pour les valeurs catégoriques.

Si vous voulez faire correspondre approximativement (effectuer une recherche), R dispose d'une fonction appelée findInterval qui (comme son nom l'indique) trouvera l'intervalle / la case qui contient votre valeur numérique continue.

Cependant, disons que vous voulez findInterval pour plusieurs valeurs. Vous pourriez écrire une boucle ou utiliser une fonction d'application. Cependant, j'ai trouvé plus efficace d'adopter une approche vectorielle bricolée.

Disons que vous disposez d'une grille de valeurs indexées par x et y :

grid <- list(x = c(-87.727, -87.723, -87.719, -87.715, -87.711), 
             y = c(41.836, 41.839, 41.843, 41.847, 41.851), 
             z = (matrix(data = c(-3.428, -3.722, -3.061, -2.554, -2.362, 
                                  -3.034, -3.925, -3.639, -3.357, -3.283, 
                                  -0.152, -1.688, -2.765, -3.084, -2.742, 
                                   1.973,  1.193, -0.354, -1.682, -1.803, 
                                   0.998,  2.863,  3.224,  1.541, -0.044), 
                         nrow = 5, ncol = 5)))

et vous avez des valeurs que vous voulez rechercher par x et y :

df <- data.frame(x = c(-87.723, -87.712, -87.726, -87.719, -87.722, -87.722), 
                 y = c(41.84, 41.842, 41.844, 41.849, 41.838, 41.842), 
                 id = c("a", "b", "c", "d", "e", "f")

Voici l'exemple visualisé :

contour(grid)
points(df$x, df$y, pch=df$id, col="blue", cex=1.2)

Vous pouvez trouver les intervalles x et les intervalles y avec ce type de formule :

xrng <- range(grid$x)
xbins <- length(grid$x) -1
yrng <- range(grid$y)
ybins <- length(grid$y) -1
df$ix <- trunc( (df$x - min(xrng)) / diff(xrng) * (xbins)) + 1
df$iy <- trunc( (df$y - min(yrng)) / diff(yrng) * (ybins)) + 1

Vous pouvez aller un peu plus loin et effectuer une interpolation (simpliste) sur les valeurs z en grid comme ça :

df$z <- with(df, (grid$z[cbind(ix, iy)] + 
                      grid$z[cbind(ix + 1, iy)] +
                      grid$z[cbind(ix, iy + 1)] + 
                      grid$z[cbind(ix + 1, iy + 1)]) / 4)

Ce qui vous donne ces valeurs :

contour(grid, xlim = range(c(grid$x, df$x)), ylim = range(c(grid$y, df$y)))
points(df$x, df$y, pch=df$id, col="blue", cex=1.2)
text(df$x + .001, df$y, lab=round(df$z, 2), col="blue", cex=1)

df
#         x      y id ix iy        z
# 1 -87.723 41.840  a  2  2 -3.00425
# 2 -87.712 41.842  b  4  2 -3.11650
# 3 -87.726 41.844  c  1  3  0.33150
# 4 -87.719 41.849  d  3  4  0.68225
# 6 -87.722 41.838  e  2  1 -3.58675
# 7 -87.722 41.842  f  2  2 -3.00425

Notez que ix, et iy auraient également pu être trouvés avec une boucle utilisant findInterval Voici un exemple pour la deuxième ligne.

findInterval(df$x[2], grid$x)
# 4
findInterval(df$y[2], grid$y)
# 2

Qui correspond ix y iy sur df[2]

Note de bas de page : (1) Le quatrième argument de vlookup était auparavant appelé "match", mais après l'introduction du ruban, il a été renommé "[range_lookup]".

Answer 5

Solution n° 2 de la réponse de @Ben n'est pas reproductible dans d'autres exemples plus génériques. Il se trouve qu'elle donne la bonne recherche dans l'exemple parce que la variable unique HouseType sur houses apparaissent par ordre croissant. Essayez ceci :

hous <- read.table(header = TRUE,   stringsAsFactors = FALSE,   text="HouseType HouseTypeNo
  Semi            1
  ECIIsHome       17
  Single          2
  Row             3
  Single          2
  Apartment       4
  Apartment       4
  Row             3")

largetable <- data.frame(HouseType = as.character(sample(unique(hous$HouseType), 1000, replace = TRUE)), stringsAsFactors = FALSE)
lookup <- unique(hous)

La solution #2 de Bens donne

housenames <- as.numeric(1:length(unique(hous$HouseType)))
names(housenames) <- unique(hous$HouseType)
base2 <- data.frame(HouseType = largetable$HouseType,
                    HouseTypeNo = (housenames[largetable$HouseType]))

qui, lorsque

unique(base2$HouseTypeNo[ base2$HouseType=="ECIIsHome" ])
[1] 2

lorsque la réponse correcte est 17 à partir de la table de consultation.

La manière correcte de le faire est

 hous <- read.table(header = TRUE,   stringsAsFactors = FALSE,   text="HouseType HouseTypeNo
      Semi            1
      ECIIsHome       17
      Single          2
      Row             3
      Single          2
      Apartment       4
      Apartment       4
      Row             3")

largetable <- data.frame(HouseType = as.character(sample(unique(hous$HouseType), 1000, replace = TRUE)), stringsAsFactors = FALSE)

housenames <- tapply(hous$HouseTypeNo, hous$HouseType, unique)
base2 <- data.frame(HouseType = largetable$HouseType,
  HouseTypeNo = (housenames[largetable$HouseType]))

Maintenant les recherches sont effectuées correctement

unique(base2$HouseTypeNo[ base2$HouseType=="ECIIsHome" ])
ECIIsHome 
       17

J'ai essayé d'éditer la réponse de Bens mais elle est rejetée pour des raisons que je ne comprends pas.