R에서 vlookup을 수행하고 Excel에서와 같이 채우는 방법은 무엇입니까?
약 105000 행과 30 열에 대한 데이터 세트가 있습니다. 숫자에 할당하고 싶은 범주 형 변수가 있습니다. Excel에서는 아마도 뭔가를 VLOOKUP하고 채울 것입니다.
에서 똑같은 일을 R어떻게할까요?
본질적으로 내가 가진 것은 HouseType변수이며 HouseTypeNo. 다음은 몇 가지 샘플 데이터입니다.
HouseType HouseTypeNo
Semi 1
Single 2
Row 3
Single 2
Apartment 4
Apartment 4
Row 3
귀하의 질문을 올바르게 이해했다면 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'))
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]))
셋째, 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.
나는 또한 사용 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
@Ben의 답변 중 솔루션 # 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)
벤스 솔루션 # 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 답변을 편집하려고했지만 이해할 수없는 이유로 거부되었습니다.
로 시작:
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
... 따라서 행에 다른 숫자가 표시되지만 (요인이 알파벳순으로 정렬되어 있기 때문에) 동일한 패턴이됩니다.
(편집 :이 답변의 나머지 텍스트는 실제로 중복됩니다. 확인하기 위해 발생 read.table()했으며 처음에 데이터 프레임으로 읽었을 때 이미 house $ HouseType을 요소로 만든 것으로 나타났습니다 ).
그러나 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, 이는 이름에서 알 수 있듯이 연속 숫자 값을 포함하는 간격 / 빈을 찾습니다.
그러나 findInterval여러 값 을 원한다고 가정 해 보겠습니다 . 루프를 작성하거나 적용 기능을 사용할 수 있습니다. 그러나 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
한 단계 더 나아가 다음과 grid같이 z 값에 대해 (단순한) 보간을 수행 할 수 있습니다 .
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의 네 번째 인수는 이전에 "일치"라고 불렸지만 리본을 도입 한 후에는 "[range_lookup]"으로 이름이 변경되었습니다.
mapvalues()플라이어 패키지에서 사용할 수 있습니다 .
초기 데이터 :
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
Using merge is different from lookup in Excel as it has potential to duplicate (multiply) your data if primary key constraint is not enforced in lookup table or reduce the number of records if you are not using all.x = T.
To make sure you don't get into trouble with that and lookup safely, I suggest two strategies.
First one is to make a check on a number of duplicated rows in lookup key:
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)
}
This will force you to de-dupe lookup dataset before using it:
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
# ...
Second option is to reproduce Excel behaviour by taking the first matching value from the lookup dataset:
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")
These functions are slightly different from lookup as they add multiple columns.
참고URL : https://stackoverflow.com/questions/15303283/how-to-do-vlookup-and-fill-down-like-in-excel-in-r
'Program Club' 카테고리의 다른 글
| Linq의 Union Vs Concat (0) | 2020.10.18 |
|---|---|
| 사용자 지정 유효성 검사 angularjs 지시문을 테스트하려면 (0) | 2020.10.18 |
| 정적 라이브러리, 정적으로 연결된 동적 라이브러리 및 동적으로 연결된 동적 라이브러리의 .lib 파일에는 무엇이 있습니까? (0) | 2020.10.18 |
| 명령 줄에서 실행되지 않은 MySQL 쿼리를 어떻게 지우나요? (0) | 2020.10.18 |
| 여러 배열로 JSON 객체를 만들려면 어떻게해야합니까? (0) | 2020.10.18 |