许多 R 的新用户在金融、市场、商业分析等领域有丰富的行业经验，但是他们并没有太多的编程背景，所以日常工作中还是选择 Excel、PowerBI 这些传统的工具进行工作；tidyquant 的作者意识到了这些痛点，于是他在新版本中加入了好多 Excel 的特性，如果你是 Excel 的重度患者，又想体验 R 强大的数据处理和可视化功能，那么本文再合适不过了！
首先我们要通过 github 安装 tidyquant 的最新版，并加载需要的包；
代码语言：javascript复制devtools::install_github("business-science/tidyquant")
library(tidyverse)
library(tidyquant)
library(knitr)
在 R 中实现透视表
很多 Excel 的用户青睐它的数据透视表功能，现在 R 也可以通过 pivot_table()轻松实现；
本次实验我们将利用 tidyquant 一个内置的数据集 FANG，FANG 包含 Facebook,Amazon,Netflix,Google 的股价数据；
代码语言：javascript复制
FANG
## # A tibble: 4,032 x 8
## symbol date open high low close volume adjusted
## <chr> <date> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 FB 2013-01-02 27.4 28.2 27.4 28 69846400 28
## 2 FB 2013-01-03 27.9 28.5 27.6 27.8 63140600 27.8
## 3 FB 2013-01-04 28.0 28.9 27.8 28.8 72715400 28.8
## 4 FB 2013-01-07 28.7 29.8 28.6 29.4 83781800 29.4
## 5 FB 2013-01-08 29.5 29.6 28.9 29.1 45871300 29.1
## 6 FB 2013-01-09 29.7 30.6 29.5 30.6 104787700 30.6
## 7 FB 2013-01-10 30.6 31.5 30.3 31.3 95316400 31.3
## 8 FB 2013-01-11 31.3 32.0 31.1 31.7 89598000 31.7
## 9 FB 2013-01-14 32.1 32.2 30.6 31.0 98892800 31.0
## 10 FB 2013-01-15 30.6 31.7 29.9 30.1 173242600 30.1
## # … with 4,022 more rows
我们可以通过 pivot_table 对 adjusted 进行透视，首先我们查看下 adjust 列是否有缺失值；
代码语言：javascript复制FANG %>%
pivot_table(
.columns = symbol,
.values = ~ SUM(is.na(adjusted))
) %>%kable()
# | AMZN| FB| GOOG| NFLX|
# |----:|--:|----:|----:|
# | 0| 0| 0| 0|
好没有缺失值，我们想看一下这几个公司每年每个季度的收益率；
代码语言：javascript复制
FANG %>%
pivot_table(
.rows = c(symbol, ~ QUARTER(date)),
.columns = ~ YEAR(date),
.values = ~ (LAST(adjusted) - FIRST(adjusted)) / FIRST(adjusted)
) %>%
kable()
# |symbol | QUARTER(date)| 2013| 2014| 2015| 2016|
# |:------|-------------:|----------:|----------:|----------:|----------:|
# |AMZN | 1| 0.0356768| -0.1547856| 0.2060807| -0.0680544|
# |AMZN | 2| 0.0614656| -0.0530919| 0.1723923| 0.1956892|
# |AMZN | 3| 0.1082595| -0.0299348| 0.1703285| 0.1538281|
# |AMZN | 4| 0.2425300| -0.0223965| 0.2979913| -0.1038196|
# |FB | 1| -0.0864286| 0.1010785| 0.0480561| 0.1162199|
# |FB | 2| -0.0254603| 0.0745768| 0.0502020| -0.0153369|
# |FB | 3| 1.0245869| 0.1613283| 0.0344034| 0.1233033|
# |FB | 4| 0.0838954| 0.0192031| 0.1507422| -0.1065466|
# |GOOG | 1| 0.0980851| 0.0017401| 0.0441873| 0.0041923|
# |GOOG | 2| 0.0988279| 0.0143170| -0.0406450| -0.0770892|
# |GOOG | 3| -0.0134815| -0.0091132| 0.1659128| 0.1116688|
# |GOOG | 4| 0.2634837| -0.0736798| 0.2414403| -0.0009578|
# |NFLX | 1| 1.0571677| -0.0297393| 0.1941594| -0.0702983|
# |NFLX | 2| 0.1571014| 0.2081494| 0.5901917| -0.1345316|
# |NFLX | 3| 0.3786783| -0.0463327| 0.1027844| 0.0194477|
# |NFLX | 4| 0.1341568| -0.2214904| 0.0792602| 0.2062750|
代码啥意思呢，别急我们一层一层来看；
代码语言：javascript复制.rows = c(symbol, ~ QUARTER(date)) # 透视表的行，~ QUARTER(date)提取date对应的季度
QUARTER('2020-03-01')
[1] 1
.columns = ~ YEAR(date), # 透视表的列 ~ YEAR(date) 提取date的年份
YEAR('2020-03-01')
[1] 2020
.values = ~ (LAST(adjusted) - FIRST(adjusted)) / FIRST(adjusted) # 透视值 一个简单变换最后一个值减去第一个值再除以第一个值，得到收益率
当我们需要对变量值进行计算的时候，我们要传入 ～ 符号，不需要计算的就不用传了;如果我们只需要透视年收益率，tidyquant 为我们提供了现成的函数 PCT_CHANGE_FIRSTLAST，这时我们只需要调用，不用自己写计算过程了；
代码语言：javascript复制FANG %>%
pivot_table(
.rows = symbol,
.columns = ~ YEAR(date),
.values = ~ PCT_CHANGE_FIRSTLAST(adjusted)
) %>%
kable()
# |symbol | 2013| 2014| 2015| 2016|
# |:------|---------:|----------:|---------:|---------:|
# |AMZN | 0.5498426| -0.2201673| 1.1907495| 0.1772084|
# |FB | 0.9517858| 0.4260647| 0.3340983| 0.1255136|
# |GOOG | 0.5495473| -0.0532416| 0.4460024| 0.0404130|
# |NFLX | 3.0014129| -0.0584587| 1.2945491| 0.1258640|
在 R 中实现 VLOOKUP
Excel 中另一个强大的函数是 VLOOKUP，VLOOKUP 的主要功能如下：
我们构造一个数据来演示 VLOOKUP；
代码语言：javascript复制lookup_table <- tibble(
stock = c("FB", "AMZN", "NFLX", "GOOG"),
company = c("Facebook", "Amazon", "Netflix", "Google")
)
> lookup_table %>% kable()
|stock |company |
|:-----|:--------|
|FB |Facebook |
|AMZN |Amazon |
|NFLX |Netflix |
|GOOG |Google |首先查看 VLOOKUP 的用法
VLOOKUP(.lookup_values, .data, .lookup_column, .return_column)
参数名 | 含义 |
|---|---|
.lookup_values | 要查找的值 |
.data | 备查的数据框 |
.lookup_column | 要查找的列 |
.return_column | 要返回的列 |
比如我们想查找 AMZN 代表的公司，
代码语言：javascript复制VLOOKUP("AMZN", lookup_table, stock, company)
[1] "Amazon"
不过我们在 Excel 中使用 VLOOKUP 是想在一个表中添加列，这列的值要去另一个表中查找， 在 R 中怎么做呢？我们还记得前面的 mutate 吗，它可以构造新列，我们将它俩合起来用就可以了；
代码语言：javascript复制FANG %>%
mutate(company = VLOOKUP(symbol, lookup_table, stock, company))
# A tibble: 4,032 x 9
# symbol date open high low close volume adjusted company
# <chr> <date> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>
# 1 FB 2013-01-02 27.4 28.2 27.4 28 69846400 28 Facebook
# 2 FB 2013-01-03 27.9 28.5 27.6 27.8 63140600 27.8 Facebook
# 3 FB 2013-01-04 28.0 28.9 27.8 28.8 72715400 28.8 Facebook
# 4 FB 2013-01-07 28.7 29.8 28.6 29.4 83781800 29.4 Facebook
# 5 FB 2013-01-08 29.5 29.6 28.9 29.1 45871300 29.1 Facebook
# 6 FB 2013-01-09 29.7 30.6 29.5 30.6 104787700 30.6 Facebook
# 7 FB 2013-01-10 30.6 31.5 30.3 31.3 95316400 31.3 Facebook
# 8 FB 2013-01-11 31.3 32.0 31.1 31.7 89598000 31.7 Facebook
# 9 FB 2013-01-14 32.1 32.2 30.6 31.0 98892800 31.0 Facebook
#10 FB 2013-01-15 30.6 31.7 29.9 30.1 173242600 30.1 Facebook
# … with 4,022 more rows
我们给 FANG 添加了一个新列 company，列值为对应的公司名，是不是很方便？
在 R 中实现各种「IFS」函数
很多同学喜欢 Excel 是因为它的条件筛选功能，比如SUMIFS(), COUNTIFS(), AVERAGEIFS()等；
在 R 中如何实现呢？tidyquant 为我们提供了同名的函数，简直不要太贴心，来看俩小例子；
代码语言：javascript复制SUM_IFS(x = 1:10, x > 5) # 对vector中大于5的求和
[1] 40
> COUNT_IFS(x = letters, str_detect(x, "[a-c]")) # 检测到a-c这几个字母就进行计数
[1] 3
那么如何在 tidyverse 工作流中使用条件筛选呢？我们想查看各股票 2015 年交易量高于75%分位数的有多少天
代码语言：javascript复制FANG %>%
group_by(symbol) %>%
summarise(
high_volume_in_2015 = COUNT_IFS(volume,
year(date) == 2015,
volume > quantile(volume, 0.75))
) %>% glimpse()
Observations: 4
Variables: 2
$ symbol <chr> "AMZN", "FB", "GOOG", "NFLX"
$ high_volume_in_2015 <int> 62, 15, 19, 54
更多的 Excel 函数
除了上面提到的，tidyquant 还提供了许多时间处理、金融分析和统计的函数，
比如我们想计算到今年 7 月 1 日，有多少个工作日；
代码语言：javascript复制NET_WORKDAYS("2020-01-01", "2020-07-01",
holidays = HOLIDAY_SEQUENCE("2020-01-01", "2020-07-01",
calendar = "NYSE"))
# [1] 126
假期在哪里？
代码语言：javascript复制HOLIDAY_SEQUENCE("2020-01-01", "2020-07-01", calendar = "NYSE")
# [1] "2020-01-01" "2020-01-20" "2020-02-17" "2020-04-10" "2020-05-25"
当然这里不是国内的假期，你可以使用？HOLIDAY_SEQUENCE来查看支持查询的地点。
Tidyverse风格的新函数-Summarize By Time
既然是 tidyquant，当然少不了时间序列的计算，我们想看一下每个月第一天的 adjust 值；
代码语言：javascript复制FANG %>%
group_by(symbol) %>%
# 只取每个月第一天的值
summarise_by_time(
.date_var = date,
.by = "month",
adjusted = FIRST(adjusted)
)
# A tibble: 192 x 3
# Groups: symbol [4]
#symbol date adjusted
# <chr> <date> <dbl>
# 1 AMZN 2013-01-01 257.
# 2 AMZN 2013-02-01 265
# 3 AMZN 2013-03-01 266.
# 4 AMZN 2013-04-01 262.
# 5 AMZN 2013-05-01 248.
# 6 AMZN 2013-06-01 267.
# 7 AMZN 2013-07-01 282.
# 8 AMZN 2013-08-01 306.
# 9 AMZN 2013-09-01 289.
#10 AMZN 2013-10-01 321.
# … with 182 more rows
总结
当然 tidyquant 的功能不仅限于上面列举的，结合 tidyverse 它可以处理异常复杂和庞大的数据， 这是 Excel 所不具备的，你还可以结合 ggplot 和 shiny 做出很多 BI 类的应用，去 tidyquant 的官网尽情探索吧！
