先日、ふと読んだ窓の杜の記事で「XLOOKUP関数はもう常識！」などと書いてありましたが、恥ずかしながら今までXLOOKUP関数を使ったことがありませんでした。
これは一大事！ということで、小生のつたないわずかばかりのRの知識を活用して、“XLOOKUP風”関数を実装してみたいと思います。
XLOOKUP関数の使い方
の詳細は、上記リンクの説明に譲るとして、ここでは簡単に引数の内容だけ記載します。
どうせ世の中の大半の人はXLOOKUPくらい知ってるし、なんなら自分もry
その説明によれば、XLOOKUP関数は
XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード)
という引数を持つそうです。
説明するまでもないですが、ここで引数として書かれてる日本語は単に簡単な説明であって、引数名ではありません。でも便利なのでこの記事ではXLOOKUP関数に関して引数の説明をするときは上記の名称に依拠します。
ていうかExcelってそもそも引数名がないんですよね。どういう仕組み何でしょうか？
XLOOKUP関数を書いてみよう
とりあえず簡単に、検索範囲と戻り範囲のデータを格納したベクトルを引数として与えると検索値と完全一致する戻り値を返す“XLOOKUP風”関数を作ります。一文が長い。
一応場合分けして「見つからない場合」にも対応できるようにしましょう。
xlookup <- \(x, range, return, missing = NULL) {
if (any(x == range)) {
return[x == range]
} else {
missing
}
}
できました。
検索範囲を表すrangeの中に検索値xと完全一致する値があるかどうかを判定し、ある場合はそれと同じ要素番号を持つ戻り範囲returnを返します。
これがたぶんXLOOKUP関数がやらんとしていることと同じものだと思います。
こうやって書いてみると、実質的に1行で書けることしかしてないんだな、ということがわかります。本質的な部分はreturn[x == range]ですからね。
とはいえ、この関数は検索範囲と戻り範囲のベクトルが同じ長さを持っていないと上手く動作しませんし、長さが異なる場合でも何も対処されません。
さらにmissingにいくらでも好きなコードを渡せてしまうので、本当は文字列かどうかの確認もした方がいいかもですね。
ではreprexを使って実際に動かしてみます。
set.seed(1)
data <- tibble::tibble(
x = letters,
y = runif(26)
)
data
#> # A tibble: 26 × 2
#> x y
#> <chr> <dbl>
#> 1 a 0.266
#> 2 b 0.372
#> 3 c 0.573
#> 4 d 0.908
#> 5 e 0.202
#> 6 f 0.898
#> 7 g 0.945
#> 8 h 0.661
#> 9 i 0.629
#> 10 j 0.0618
#> # … with 16 more rows
xlookup("p", data$x, data$y)
#> [1] 0.4976992
できました。
とはいえ、ときには検索範囲と戻り範囲を逆にしたい場合もあります。
しかし上のように戻り範囲が連続値だったり長い文だったりすると完全一致させるのが難しいことがあるかもしれません。完全一致だけでなく部分一致にも対応できると何かと便利です。
というわけで「一致モード」を引数で指定できるようにしましょう。
本家XLOOKUP関数の場合はさらに「検索モード」とやらも指定できる仕様らしいですが、これはなんだか難しそうなので今回は無視します。
xlookup2 <- \(x, range, return, missing = NULL, exact = TRUE) {
if (exact) {
if (any(x == range)) {
return[x == range]
} else {
missing
}
} else {
if (any(grepl(x, range))) {
return[grep(x, range)]
} else {
missing
}
}
}
できました。
if文の使い方がちょっと気になりますがたぶん大丈夫です（たぶん）
新しい引数exactがFALSEの場合、==の代わりにgrepを使用して部分一致した値を参照して戻り範囲から適当な値を返します。一文が
Rのgrepは部分一致する値のベクトルの要素番号を返します。
また、greplは各要素が部分一致するかどうかを論理値で返します。
部分一致が許容されれば、以下のように何桁もある数値でもある程度絞り込めれば適切な戻り値を得られるはずです。
data[data$x == "p", ]$y
#> [1] 0.4976992
ではやってみましょう。
xlookup2(0.497, data$y, data$x, exact = FALSE)
#> [1] "p"
できました。
ちゃんと最初に検索値として使用した文字列pが得られました。
データフレームに対応したXLOOKUPを書いてみよう
Rは主にデータ解析で利用される言語であり、データを扱うために特化した型があります。
それはdata.frameあるいはtibbleなどと呼ばれ、Rでプログラミングをする場合、このようなデータ形式を扱えるととても嬉しいわけです。
ベクトルではなくデータフレームに対応したxlookup関数を作れないでしょうか。
具体的には、第1引数にデータフレームを与え、その列名を参照して検索範囲と戻り範囲を指定できないでしょうか。
library(tidyverse)
xlookup_df <- \(
data, x, range, return,
missing = NULL,
exact = TRUE
) {
range <- rlang::enquo(range)
return <- rlang::enquo(return)
if (exact) {
if (any(x == pull(data, !!range))) {
data |>
select(!!range, !!return) |>
filter(!!range == x) |>
pull(!!return)
} else {
missing
}
} else {
if (any(grepl(x, pull(data, !!range)))) {
data |>
select(!!range, !!return) |>
filter(str_detect(!!range, x)) |>
pull(!!return)
} else {
missing
}
}
}
できました。
第1引数にデータフレームを指定し、その後はこれまでの引数を1つずつずらして指定するような形になっています。
NSEを利用してあたかもこれまでのようにベクトルを指定するようにデータフレーム内の列名を指定できる（「裸の変数」が使える）ようにしてあるのも特徴です。
ではさっそく動かしてみましょう。
使用するデータはRのサンプルデータで自動車のスペックが記録されているmtcarsです。知ってます。
私は車のことは詳しくないのですが、適当な数値と文字列がいい塩梅に揃っているので、サンプルデータとしてよく使っています。
ここでは、たまたま1車種しか記録されていなかったVolvo 142Eという車のシリンダー数を、完全一致および部分一致で正しくルックアップできるか確認してみます。
mtcars <- tibble::as_tibble(mtcars, rownames = "name")
mtcars[mtcars$name == "Volvo 142E", ]
#> # A tibble: 1 × 12
#> name mpg cyl disp hp drat wt qsec vs am gear carb
#> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 Volvo 142E 21.4 4 121 109 4.11 2.78 18.6 1 1 4 2
上記のように、シリンダー数cylは4です。
xlookup_df(mtcars, "Volvo 142E", name, cyl)
#> [1] 4
xlookup_df(mtcars, "Volvo", name, cyl, exact = FALSE)
#> [1] 4
できました。
ちゃんと部分一致にも対応しています。
これで上司から「XLOOKUPも知らないのかよ～遅れてる～」などと言われても平気です！