Как сделать vlookup и заполнить (например, в Excel) в R?
У меня есть dataset около 105000 строк и 30 столбцов. У меня есть категориальная переменная, которую я хотел бы присвоить ей. В Excel я бы, вероятно, что-то сделал с VLOOKUP
и заполнил.
Как я буду делать то же самое в R
?
По сути, у меня есть переменная HouseType
, и мне нужно рассчитать HouseTypeNo
. Вот несколько примеров данных:
- Применить формулу VLOOKUP к нескольким листам
- Excel - сопоставление значений в столбцах на разных вкладках, а затем вставка в другой столбец, если есть совпадение
- Как я могу VLOOKUP в нескольких документах Excel?
- Vlookup с использованием 2 столбцов для ссылки на другой
- Как заставить VLOOKUP вернуть совпадение * last *?
HouseType HouseTypeNo Semi 1 Single 2 Row 3 Single 2 Apartment 4 Apartment 4 Row 3
- Быстрая замена # N / A на 0, когда vlookup
- Vba Excel делает vlookup из закрытого файла
- «Не удалось получить свойство VLookup classа WorksheetFunction»
- Excel. Как vlookup вернуть несколько значений?
- Как оптимизировать vlookup для высокого количества поиска? (альтернативы VLOOKUP)
- Excel беспорядок с моей формулой при копировании, как я могу остановить, я перестаю менять одну часть, но не другую?
- Функция MATCH в r
- Расширенная формула Vlookup Excel
Если я правильно понимаю ваш вопрос, вот четыре способа сделать эквивалент VLOOKUP
Excel и заполнить его с помощью 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)")
Также потребуются соответствующие изменения в других решениях.
Это то, что вы хотели сделать? Дайте мне знать, какой метод вам нравится, и я добавлю комментарий.
Я думаю, вы также можете использовать match()
:
largetable$HouseTypeNo <- with(lookup, HouseTypeNo[match(largetable$HouseType, HouseType)])
Это все еще работает, если я буду lookup
порядок lookup
.
Мне также нравится использовать qdapTools::lookup
или сокращенный двоичный оператор %l%
. Он работает идентично Excel vlookup, но он принимает аргументы имени, противоположные номерам столбцов
## 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
Решение №2 из ответа Бена не воспроизводится в других более общих примерах. Это приводит к правильному поиску в примере, потому что уникальный 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
Я попытался отредактировать ответ Бенса, но он отклоняется по причинам, которые я не могу понять.
Начиная с:
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"
Плакат не спрашивал о поиске значений, если exact=FALSE
, но я добавляю это как ответ для своих собственных ссылок и, возможно, других.
Если вы ищете категориальные значения, используйте другие ответы.
Excel vlookup
также позволяет сопоставлять соответствие приблизительно для числовых значений с 4-м аргументом (1) match=TRUE
. Я думаю, что match=TRUE
как поиск значений на термометре. Значение по умолчанию – FALSE, которое идеально подходит для категориальных значений.
Если вы хотите приблизиться (выполнить поиск), R имеет функцию findInterval
, которая (как следует из названия) найдет интервал / bin, который содержит ваше непрерывное числовое значение.
Однако предположим, что вы хотите findInterval
для нескольких значений. Вы можете написать цикл или использовать функцию apply. Тем не менее, я счел более эффективным использовать подход, основанный на DIY.
Предположим, что у вас есть grid значений, индексированных по 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 # xy 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 также могли быть найдены с помощью цикла using findInterval
, например, вот один пример для второй строки
findInterval(df$x[2], grid$x) # 4 findInterval(df$y[2], grid$y) # 2
Что соответствует ix
и iy
в df[2]
Сноска: (1) Четвертый аргумент vlookup ранее назывался «match», но после того, как они представили ленту, он был переименован в «[range_lookup]».
Вы можете использовать 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
Использование merge
отличается от поиска в Excel, поскольку у него есть возможность дублировать (умножать) ваши данные, если ограничение первичного ключа не применяется в таблице поиска или уменьшает количество записей, если вы не используете all.x = T
Чтобы убедиться, что вы не попадаете в проблему с этим и безопасным образом, я предлагаю две страtagsи.
Во-первых, чтобы проверить количество повторяющихся строк в ключе поиска:
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) }
Это заставит вас обменивать dataset поиска перед его использованием:
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
поскольку они добавляют несколько столбцов.