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

En commençant par :

houses <- read.table(text="Semi            1
Single          2
Row             3
Single          2
Apartment       4
Apartment       4
Row             3",col.names=c("HouseType","HouseTypeNo"))

... vous pouvez utiliser

as.numeric(factor(houses$HouseType))

... pour donner un numéro unique à chaque type de maison. Vous pouvez voir le résultat ici :

> houses2 <- data.frame(houses,as.numeric(factor(houses$HouseType)))
> houses2
  HouseType HouseTypeNo as.numeric.factor.houses.HouseType..
1      Semi           1                                    3
2    Single           2                                    4
3       Row           3                                    2
4    Single           2                                    4
5 Apartment           4                                    1
6 Apartment           4                                    1
7       Row           3                                    2

... vous vous retrouvez donc avec des numéros différents sur les lignes (parce que les facteurs sont classés par ordre alphabétique) mais le même modèle.

(EDIT : le reste du texte de cette réponse est en fait redondant. Il m'est venu à l'esprit de vérifier et il s'avère que read.table() avait déjà fait de houses$HouseType un facteur lorsqu'il a été lu dans le cadre de données en premier lieu).

Toutefois, il serait peut-être préférable de convertir simplement HouseType en un facteur, ce qui vous donnerait les mêmes avantages que HouseTypeNo, mais serait plus facile à interpréter car les types de maisons sont nommés plutôt que numérotés, par exemple :

> houses3 <- houses
> houses3$HouseType <- factor(houses3$HouseType)
> houses3
  HouseType HouseTypeNo
1      Semi           1
2    Single           2
3       Row           3
4    Single           2
5 Apartment           4
6 Apartment           4
7       Row           3
> levels(houses3$HouseType)
[1] "Apartment" "Row"       "Semi"      "Single"

Answer 2

Vous pourriez utiliser mapvalues() du paquet plyr.

Données initiales :

dat <- data.frame(HouseType = c("Semi", "Single", "Row", "Single", "Apartment", "Apartment", "Row"))

> dat
  HouseType
1      Semi
2    Single
3       Row
4    Single
5 Apartment
6 Apartment
7       Row

Table de consultation / de recoupement :

lookup <- data.frame(type_text = c("Semi", "Single", "Row", "Apartment"), type_num = c(1, 2, 3, 4))
> lookup
  type_text type_num
1      Semi        1
2    Single        2
3       Row        3
4 Apartment        4

Créez la nouvelle variable :

dat$house_type_num <- plyr::mapvalues(dat$HouseType, from = lookup$type_text, to = lookup$type_num)

Ou, pour les remplacements simples, vous pouvez éviter de créer une longue table de consultation et effectuer cette opération directement en une seule étape :

dat$house_type_num <- plyr::mapvalues(dat$HouseType,
                                      from = c("Semi", "Single", "Row", "Apartment"),
                                      to = c(1, 2, 3, 4))

Résultat :

> dat
  HouseType house_type_num
1      Semi              1
2    Single              2
3       Row              3
4    Single              2
5 Apartment              4
6 Apartment              4
7       Row              3

Answer 3

Utilisation de merge est différente de la consultation dans Excel, car elle peut potentiellement dupliquer (multiplier) vos données si la contrainte de clé primaire n'est pas appliquée dans la table de consultation ou réduire le nombre d'enregistrements si vous n'utilisez pas de clé primaire. all.x = T .

Pour être sûr de ne pas avoir d'ennuis avec cela et de faire des recherches en toute sécurité, je vous propose deux stratégies.

La première consiste à vérifier un certain nombre de lignes dupliquées dans la clé de recherche :

safeLookup <- function(data, lookup, by, select = setdiff(colnames(lookup), by)) {
  # Merges data to lookup making sure that the number of rows does not change.
  stopifnot(sum(duplicated(lookup[, by])) == 0)
  res <- merge(data, lookup[, c(by, select)], by = by, all.x = T)
  return (res)
}

Cela vous obligera à dé-duper le jeu de données de lookup avant de l'utiliser :

baseSafe <- safeLookup(largetable, house.ids, by = "HouseType")
# Error: sum(duplicated(lookup[, by])) == 0 is not TRUE 

baseSafe<- safeLookup(largetable, unique(house.ids), by = "HouseType")
head(baseSafe)
# HouseType HouseTypeNo
# 1 Apartment           4
# 2 Apartment           4
# ...

La deuxième option consiste à reproduire le comportement d'Excel en prenant la première valeur correspondante dans l'ensemble de données de consultation :

firstLookup <- function(data, lookup, by, select = setdiff(colnames(lookup), by)) {
  # Merges data to lookup using first row per unique combination in by.
  unique.lookup <- lookup[!duplicated(lookup[, by]), ]
  res <- merge(data, unique.lookup[, c(by, select)], by = by, all.x = T)
  return (res)
}

baseFirst <- firstLookup(largetable, house.ids, by = "HouseType")

Ces fonctions sont légèrement différentes de lookup car ils ajoutent plusieurs colonnes.

Answer 4

Le site lookup peut être utilisé ici :

library(lookup)
# reference data
hous <- data.frame(HouseType=c("Semi","Single","Row","Single","Apartment","Apartment","Row"),
                   HouseTypeNo=c(1,2,3,2,4,4,3))
# new large data with HouseType but no HouseTypeNo
largetable <- data.frame(HouseType = sample(unique(hous$HouseType), 1000, replace = TRUE))

# vector approach
largetable$num1 <- lookup(largetable$HouseType, hous$HouseType, hous$HouseTypeNo)
# dataframe approach
largetable$num2 <- vlookup(largetable$HouseType, hous, "HouseType", "HouseTypeNo")

head(largetable)
#   HouseType num1 num2
# 1      Semi    1    1
# 2      Semi    1    1
# 3 Apartment    4    4
# 4      Semi    1    1
# 5    Single    2    2
# 6    Single    2    2