r - R 能否执行与嵌套在 VLOOKUP 中的 HLOOKUP 等效的操作？

Question

我正在尝试(未成功)在 Excel 中使用 R Studio 执行与嵌套在 VLOOKUP 中的 HLOOKUP 等效的操作。

情况是这样的。

我有两张 table 。表 1 有历史股票价格，其中每一列代表一个股票代码名称，每一行代表一个特定的日期。表 1 包含每个股票代码在每个日期的收盘价。

假设表 1 如下所示:

 |----------------------------|
 |   Date   |MSFT | AMZN |EPD | 
 |----------------------------|
 | 6/1/2020 | 196 | 2600 | 19 |
 | 5/1/2020 | 186 | 2200 | 20 |
 | 4/1/2020 | 176 | 2000 | 15 |
 | 3/1/2020 | 166 | 1800 | 14 |
 | 2/1/2020 | 170 | 2200 | 18 |
 | 1/1/2020 | 180 | 2300 | 17 |
 |----------------------------|

表 2 包含股票代码列表，以及两个日期和每个日期的股票价格占位符。 Date1 总是比 Date2 早，Date1 和 Date2 分别对应表 1 中的一个日期。注意表 2 每一行的 Date1 和 Date2 是不同的。

我的目标是将适用的 PriceOnDate1 和 PriceOnDate2 提取到表 2 中，类似于 Excel 中的 VLOOKUP/HLOOKUP 函数。 (我不能继续使用 Excel，因为文件太大，Excel 无法处理)。然后我可以通过这样的公式计算每一行的返回:(Date2 - Date1)/Date1

假设我希望表 2 看起来像这样，但我无法提取 PriceOnDate1 和 PriceOnDate2 的定价数据:

|-----------------------------------------------------------|
| Ticker | Date1    | Date2    |PriceOnDate1 |PriceOnDate2  |
|-----------------------------------------------------------|
| MSFT   | 1/1/2020 | 4/1/2020 | _________   | ________     | 
| MSFT   | 2/1/2020 | 6/1/2020 | _________   | ________     |   
| AMZN   | 5/1/2020 | 6/1/2020 | _________   | ________     |
| EPD    | 1/1/2020 | 3/1/2020 | _________   | ________     |   
| EPD    | 1/1/2020 | 4/1/2020 | _________   | ________     |
|-----------------------------------------------------------|

我的问题是，是否有一种方法可以使用 R 将表 2 每一行中每个 Date1 和 Date2 的收盘价数据从表 1 提取到表 2 中。例如，在表 2 的第一行中，理想情况下R 代码将为 PriceOnDate1 拉入 180，为 PriceOnDate2 拉入 176。

我已尝试寻找答案，但我无法制定允许我在 R Studio 中执行此操作的解决方案。谁能帮我解决问题？我非常感谢你的时间。谢谢!

Answer 1

使用 R 之类的东西需要您以不同的方式思考数据。您的表 1 可能最容易使用旋转成长格式。然后，您只需加入 Ticker 和 Date 即可提取所需的值。

数据:

table_1 <- data.frame(Date = c("6/1/2020", "5/1/2020", "4/1/2020", "3/1/2020", 
                               "2/1/2020", "1/1/2020"),
                      MSFT = c(196, 186, 176, 166, 170, 180),
                      AMZN = c(2600, 2200, 2000, 1800, 2200, 2300),
                      EPD = c(19, 20, 15, 14, 18, 17))

# only created part of Table 2
table_2 <- data.frame(Ticker = c("MSFT", "AMZN"),
                      Date1 = c("1/1/2020", "5/1/2020"),
                      Date2 = c("4/1/2020", "6/1/2020"))

解决方案:

tidyverse 方法在这里非常简单。

library(dplyr)
library(tidyr)

首先，将表 1 变长。

table_1_long <- table_1 %>% 
  pivot_longer(-Date, names_to = "Ticker", values_to = "Price")

然后通过匹配日期和代码加入您想要的价格。

table_2 %>% 
  left_join(table_1_long, by = c(Date1 = "Date", "Ticker")) %>% 
  left_join(table_1_long, by = c(Date2 = "Date", "Ticker")) %>% 
  rename(PriceOnDate1 = Price.x,
         PriceOnDate2 = Price.y)

#   Ticker    Date1    Date2 PriceOnDate1 PriceOnDate2
# 1   MSFT 1/1/2020 4/1/2020          180          176
# 2   AMZN 5/1/2020 6/1/2020         2200         2600