前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >TidyFriday Excel 用户的福音！在 R 中实现 Excel 的功能

TidyFriday Excel 用户的福音！在 R 中实现 Excel 的功能

作者头像
王诗翔呀
发布于 2020-07-06 17:55:51
2.4K0
发布于 2020-07-06 17:55:51
举报
文章被收录于专栏：优雅R优雅R

许多 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 的官网尽情探索吧！

本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2020-03-01，如有侵权请联系 cloudcommunity@tencent.com 删除
excel
lookup
return
tidyverse
vlookup

本文分享自 优雅R 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ，欢迎热爱写作的你一起参与！

excel
lookup
return
tidyverse
vlookup
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • 在 R 中实现透视表
  • 在 R 中实现 VLOOKUP
  • 在 R 中实现各种「IFS」函数
  • 更多的 Excel 函数
  • Tidyverse风格的新函数-Summarize By Time
  • 总结
相关产品与服务
腾讯云 BI
腾讯云 BI（Business Intelligence，BI）提供从数据源接入、数据建模到数据可视化分析全流程的BI能力，帮助经营者快速获取决策数据依据。系统采用敏捷自助式设计，使用者仅需通过简单拖拽即可完成原本复杂的报表开发过程，并支持报表的分享、推送等企业协作场景。
免费体验产品介绍产品文档
腾讯云BI新客户特惠专场，首月1.4折低至6.6元
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论