【R語言】VLOOKUP一個常見案例的R語言實現
問題:
根據右側的查詢表,用VLOOKUP計算津貼列:
Excel 的 VLOOKUP 函式設計的比較反人類:晦澀、難懂、難記、難用,新的XLOOKUP有所改觀,但仍不如程式語言來的簡單、直接、清晰、易懂。
解決一個問題的終極思路,始終是:
把複雜的問題分解為若干簡單的問題,逐步解決
。
VLOOKUP 函式之所以這麼難記、難用,就是因為它不做這種分解,而總是妄圖一步到位。
對這個常見的 Excel VLOOKUP 問題,我用 R語言“分解+逐個解決”一下,你會發現非常清晰和一目瞭然。
總的來說,就是兩個資料表做“
(左)連線
”,但之前需要做一點準備工作:
(1) 用表1的工齡,計算一個新變數“工齡組別”
(2) 表2不是“整潔”表,需要變“整潔”
第1步,準備資料
把兩個表讀入進來,並檢視;順便存一下工齡級別標籤,備用
library
(
tidyverse
)
library
(
readxl
)
df
=
read_xlsx
(
“LookUP/WorkingAge。xlsx”
,
range
=
“A1:C19”
)
lookup
=
read_xlsx
(
“LookUP/WorkingAge。xlsx”
,
range
=
“I3:N8”
)
df
lookup
Labels
=
names
(
lookup
)
[
-
1
]
Labels
第2步,資料連線前的準備工作
即需要準備好,根據哪些列做匹配進而連線資料,本例顯然是根據“城市”和“工齡組別”列做匹配。
(1) 表1計算新變數“工齡組別”,並使用來自表2的工齡分組標籤
實際上這是
連續變數的離散化處理
,用 cut() 函式很容易實現,用到了之前準備的標籤。
df
=
df
%
>
%
mutate
(
工齡組別
=
cut
(
工齡
,
breaks
=
c
(
-
Inf
,
0
,
5
,
10
,
20
,
Inf
),
labels
=
Labels
))
df
(2) 表2 變成“整潔”表
整潔表的特點是:
每個樣本佔一行
每個變數佔一列
每個值佔一個單元格
表2 為什麼不整潔呢?因為除了第1列的列名,不是變數,而是“工齡組別”的值。所以,需要來個“寬變長”:
lookup
=
lookup
%
>
%
pivot_longer
(
-
城市
,
names_to
=
“工齡組別”
,
values_to
=
“津貼”
)
lookup
這就是它作為整潔表應該有的樣子。
第3步,做資料連線
這是以表1為基礎,根據“城市”和“工齡組別”匹配,把表2中的津貼,連線進來,即“
左連線
”:
另外,工齡組別列不想出現在結果表中,刪掉該列
df
%
>
%
left_join
(
lookup
,
by
=
c
(
“城市”
,
“工齡組別”
))
%
>
%
select
(
-
工齡組別
)
想寫出到Excel的話,也簡單:
writexl
::
write_xlsx
(
df
,
“結果表。xlsx”
)
總結:
透過問題分解,複雜的資料操作問題變成了若干步簡單的操作邏輯:
準備資料,做資料連接合並即可。
資料連線,需要準備根據哪列做匹配。
還有一個常識是:
所有資料表一般都需要變成整潔的資料表,再操作。
你只要熟悉基本的資料操作邏輯,可以說所有的 Excel VLOOKUP 操作都不在話下,因為 VLOOKUP 無非是 “查詢匹配+連線”而已。
版權宣告
原創作品,歡迎轉載,但請註明出處。