Как сделать vlookup и заполнить (например, в Excel) в R?

Ответ 1

Если я правильно понимаю ваш вопрос, вот четыре способа сделать эквивалент Excel VLOOKUP и заполнить с помощью 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

Вот четыре метода для заполнения HouseTypeNo в largetable, используя значения в таблице lookup:

Сначала merge в базе:

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

Второй метод с именованными векторами в базе:

# 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]))

В-третьих, с помощью пакета plyr:

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

В-четвертых, используя пакет sqldf

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

Если возможно, что некоторые типы домов в largetable не существуют в lookup, тогда будет использоваться левое соединение:

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

Также потребуются соответствующие изменения в других решениях.

Это то, что вы хотели сделать? Дайте мне знать, какой метод вам нравится, и я добавлю комментарий.

Ответ 2

Я думаю, вы также можете использовать match():

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

Это работает, если я скремлю порядок lookup.

Ответ 3

Мне также нравится использовать qdapTools::lookup или сокращенный двоичный оператор %l%. Он работает идентично Excel vlookup, но он принимает аргументы имени, противоположные номерам столбцов

## Replicate Ben 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 this simple:
library(qdapTools)
largetable[, 1] %l% hous

Ответ 4

Решение № 2 ответа @Ben не воспроизводится в других более общих примерах. Это приводит к правильному поиску в примере, потому что уникальный HouseType в houses появляется в порядке возрастания. Попробуйте следующее:

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)

Решение Bens # 2 дает

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

который при

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

когда правильный ответ равен 17 из таблицы поиска

Правильный способ сделать это

 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]))

Теперь поиск выполняется правильно

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

Я попытался отредактировать ответ Bens, но он отклоняется по причинам, которые я не могу понять.

Ответ 5

Начиная с:

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

... вы можете использовать

as.numeric(factor(houses$HouseType))

... дать уникальный номер для каждого типа дома. Вы можете увидеть результат здесь:

> 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

... так что вы заканчиваете с разными номерами в строках (потому что факторы упорядочены по алфавиту), но одна и та же картина.

(EDIT: оставшийся текст в этом ответе на самом деле лишний. Мне пришло в голову проверить, и оказалось, что read.table() уже сделал дома $HouseType фактором, когда он был сначала прочитан в dataframe).

Однако вам может быть лучше просто преобразовать HouseType в фактор, который даст вам все те же преимущества, что и HouseTypeNo, но будет легче интерпретировать, потому что типы домов называются скорее, чем пронумерованы, например:

> 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"  

Ответ 6

Плакат не спрашивал о поиске значений, если exact=FALSE, но я добавляю это как ответ для своей собственной ссылки и, возможно, других.

Если вы просматриваете категориальные значения, используйте другие ответы.

Excel vlookup также позволяет сопоставлять соответствие приблизительно для числовых значений с 4-м аргументом (1) match=TRUE. Я думаю о match=TRUE как о поиске значений на термометре. Значение по умолчанию - FALSE, которое идеально подходит для категориальных значений.

Если вы хотите совместить приблизительно (выполнить поиск), R имеет функцию с именем findInterval, которая (как следует из названия) найдет интервал /bin, который содержит ваше непрерывное числовое значение.

Однако скажем, что вы хотите findInterval для нескольких значений. Вы можете написать цикл или использовать функцию apply. Тем не менее, я нашел более эффективным подход к векторизации с использованием DIY.

Скажем, что у вас есть сетка значений, индексированных по x и 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)))

и у вас есть некоторые значения, которые вы хотите найти по x и 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")

Вот пример:

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

Вы можете найти интервалы x и y интервалов с этим типом формулы:

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

Вы можете сделать это еще на один шаг и выполнить (упрощенную) интерполяцию по значениям z в grid следующим образом:

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)

Что дает эти значения:

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

Обратите внимание, что ix и iy также могли быть найдены с помощью цикла с использованием findInterval, например. здесь один пример для второй строки

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

Что соответствует ix и iy в df[2]

Сноска: (1) Четвертый аргумент vlookup ранее назывался "match", но после введения ленты он был переименован в "[range_lookup]".

Ответ 7

Вы можете использовать mapvalues() из пакета plyr.

Исходные данные:

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

Таблица поиска/кроссворда:

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

Создать новую переменную:

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

Или для простых заметок вы можете пропустить создание длинной таблицы поиска и сделать это прямо за один шаг:

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

Результат:

> 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

Ответ 8

Использование merge отличается от поиска в Excel, поскольку оно может дублировать (умножать) ваши данные, если ограничение первичного ключа не применяется в таблице поиска или уменьшает количество записей, если вы не используете all.x = T.

Чтобы убедиться, что вы не попадаете в неприятности с этим и искать безопасно, я предлагаю две стратегии.

Во-первых, чтобы проверить количество повторяющихся строк в ключе поиска:

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)
}

Это заставит вас обнулить набор данных поиска перед его использованием:

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
# ...

Второй вариант - воспроизвести поведение Excel, взяв первое сопоставимое значение из набора данных поиска:

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")

Эти функции немного отличаются от lookup, поскольку они добавляют несколько столбцов.