title |
knit |
author |
date |
site |
documentclass |
bibliography |
biblio-style |
link-citations |
description |
R语言学习笔记 |
bookdown::render_book |
Yufei Zhong |
`r Sys.Date()` |
bookdown::bookdown_site |
book |
|
apalike |
true |
宇飞的R语言学习笔记,主要集中在数据清洗相关包的介绍,从数据导入(本地数据 数据库)到数据清洗,到固定报表输出的自动化流程。 |
```{r include=FALSE, cache=FALSE}
set.seed(1014)
options(digits = 3)
knitr::opts_chunk$set(
comment = "#>",
collapse = TRUE,
# cache = TRUE,
out.width = "70%",
fig.align = 'center',
fig.width = 6,
fig.asp = 0.618, # 1 / phi
fig.show = "hold"
)
options(dplyr.print_min = 6, dplyr.print_max = 6)
```
# 欢迎 {#welcome .unnumbered}
本文主要是我作为商业数据分析师的R语言学习笔记,主要是数据清洗相关包的介绍。
使用R语言自动完成如下报表:
```{r result= 'flextable-tbl-daily-report',echo=FALSE,warning=FALSE,message=FALSE}
library(flextable)
library(tidyverse)
library(formattable)
dt % mutate(
across(c(当前销额,同比销额,当前销量,同比销量),formattable::comma,digits=0),
across(c(`当前%`,`同比%`,`金额增长%`,`销量同比%`,`当前折扣`,`同比折扣`),formattable::percent,digits = 1)
) %>% dplyr::select(1:12)
ft %
merge_v(j = ~一级部门) %>%
bg(bg = "#e05297", part = "header") %>%
color(color = "white", part = "header") %>%
add_footer_lines(paste0('数据更新时间:',Sys.Date())) %>%
#valign(valign = "top") %>%
theme_box() %>%
align(j = 1:12, align = "center", part = 'body') %>%
autofit()
ft
```
## 内容概要{#abstract .unnumbered}
主要内容是为了将数据报表,数据报告,数据可视化等需求利用R语言自动化。
- 数据导入导出
- 数据操作 dplyr
- 数据整洁 tidyr
- 字符处理
- 日期时间处理
- 因子处理
- 数据处理利器 data.table
- 数据库使用
- 循环结构
- 循环迭代之purrr包介绍
- 自定义函数功能
## 授权说明 {#sec:licenses .unnumbered}
::: {.rmdwarn data-latex="{警告}"}
本书采用 [知识共享署名-非商业性使用-禁止演绎4.0国际许可协议](https://creativecommons.org/licenses/by-nc-nd/4.0/) 许可,请君自重。
项目中代码使用 [MIT协议](https://github.com/zyf19940501/Rbook) 开源。
:::
## 运行信息{#session .unnumbered}
```{r}
xfun::session_info(packages = c(
"knitr", "rmarkdown", "bookdown","collapse",
"data.table", "DT", "reactable","flextable",
"patchwork", "plotly", "shiny","formattable",
"ggplot2", "dplyr", "tidyverse","DBI","ROracle","dbplyr"
), dependencies = FALSE)
```
## 关于本人{#author .unnumbered}
一名热爱R语言的商业数据分析师。`R`极大拓展了我数据处理能力,让我很轻松方便处理数据,有更多精力时间聚焦在具体问题上。
因个人能力有限,本书难免出现错误,如发现错误,欢迎联系本人更正。
Email: <598253220@qq.com>
微信公众号: 宇飞的世界
语雀: https://www.yuque.com/zyufei
```{r include=FALSE, cache=FALSE}
set.seed(1014)
options(digits = 3)
knitr::opts_chunk$set(
comment = "#>",
collapse = TRUE,
# cache = TRUE,
out.width = "70%",
fig.align = 'center',
fig.width = 6,
fig.asp = 0.618, # 1 / phi
fig.show = "hold"
)
options(dplyr.print_min = 6, dplyr.print_max = 6)
```
```{r setup3, include=FALSE}
knitr::opts_chunk$set(echo = TRUE,eval = FALSE)
```
# 数据导入导出 {#data:read-write-description}
作为一名普通的数据分析师,我日常接触最多的数据是业务系统中的销售订单表、商品库存表、会员信息表,门店信息表,商品信息表等之类的业务表,但最初接触R时,看到的演示代码以及数据集大部分都是R包中内置的数据集,没有很明确操作数据的意义,没有代入感。在刚开始学习使用R做数据处理后,我就想使用自己的数据集来操作数据,用R去实现Excel的透视表或sql功能。这时就首先需要将原始数据导入[^loaddata]R中。
现实生活中数据来源复杂,商业环境中数据源同样具有多样性,如SAP,不同的数据库、OA系统、EXCEL手工文件等;我们想要统一数据做分析,就需要将不同的数据源整合导入R中。
我们从读取方式简单区分为本地文件数据、数据库数据,本章主要说明常用的Excel文件和csv[^csv]、txt等文本文件的读写方式。关于数据库的数据的读取,可以参照后续database章节。
相信大家随便使用搜索引擎搜索诸如"将Excel导入R"的关键词都能得到一些行之有效的方法,但是不够系统全面。本章主要简述R中数据导入导出的相关R包,如`readxl`,`writexl`,`openxlsx`,`readr`, `vroom`等主要处理csv或Excel的R包。
[^loaddata]:由于R是将数据加载至内存中,故数据集大小超过内存大小将导入失败。
[^csv]:csv即Comma-Separated Values,逗号分隔值,分隔符也可是不是逗号。
csv文件是一种以纯文本形式存储的表格数据,可以通过记事本打开。与Excel不同的是,CSV是一种文本格式,也不受Excel最大行数(1048576)限制。
csv文件也被称作平面文件,结构简单,平面文件比结构文件占用更少的空间;平面文件在数据仓库项目中广泛用于导入数据。
>当有其它数据格式需求的时候,那时候的你肯定已经会自行查找相关R包使用了。
在本章开始前,假定已经有一些R相关基础。如使用Rstudio查看导入的数据,R的数据结构等有一定认识。本章节主要分为:
- excel读写
- csv等平面文件读写
- 文件路径
## readxl {#data:readxl}
readxl软件包使R获取Excel数据变得方便简洁。与现有的软件包(例如:xlsx)相比,readxl没有外部依赖性,xlsx等包依赖java环境。readxl包容易在所有的操作系统安装使用。
readxl[项目地址](https://readxl.tidyverse.org/),本节大部分代码来源项目官网介绍,可自行查阅官网。
### 安装
从CRAN安装最新发行版本的最简单方法是安装整个tidyverse。
```{r eval=FALSE}
install.packages("tidyverse")
```
> 由于readxl不是tidyverse核心加载包,使用时仅需加载library(readxl)
或者是从CRAN仅安装readxl;
```{r eval = FALSE}
install.packages("readxl")
```
从github安装开发版:
```{r eval = FALSE}
# install.packages("devtools")
devtools::install_github("tidyverse/readxl")
```
### 用法
1.读取
readxl包中包含了几个示例文件,我们在接下来的案例中使用。
- 查看readxl包中自带xlsx文件
```{r}
library(readxl)
readxl_example()
readxl_example("clippy.xls")
```
`read_excel()`可读取xls和xlsx文件。
```{r}
xlsx_example %
group_by(name) %>%
group_walk(~ write.csv(.x,file = file.path('data/read-write',paste0(.y$name,'.csv'))))
list.files(path = 'data/read-write/')
```
> 暂时不用理解批量读取和输出的代码具体含义,可以先记住用法。
## writexl {#data:writexl}
截止到2021年5月17日,writexl包功能比较简单,仅有输出Excel功能。快速、不依赖java和Excle是它绝对的优势,并且输出文件相比`openxlsx`包较小。
[项目地址](https://docs.ropensci.org/writexl/)
### 用法
安装
```{r eval = FALSE}
install.packages("writexl")
```
参数
```{r eval=FALSE}
write_xlsx(
x,
path = tempfile(fileext = ".xlsx"),
col_names = TRUE,
format_headers = TRUE,
use_zip64 = FALSE
)
```
输出Excel
```{r eval=FALSE}
library(writexl)
writexl::write_xlsx(iris,path = 'iris.xlsx')
write_xlsx(list(mysheet1 = iris,mysheet2 = iris),path = 'iris.xlsx')
```
效率比较
```{r}
library(microbenchmark)
library(nycflights13)
microbenchmark(
writexl = writexl::write_xlsx(flights, tempfile()),
openxlsx = openxlsx::write.xlsx(flights, tempfile()),
times = 2
)
```
文件大小比较
```{r}
writexl::write_xlsx(flights, tmp1
个人感觉主要优势:
- 不依赖java环境
- 读写速度可接受
- 可设置条件格式,与Excel中『开始』选项卡的条件格式功能接近
- 可批量插入ggplot2图
- 可插入公式
- 可渲染大部分Excel格式,并且效率相比部分python包高效
- 可添加页眉页脚以及其他格式,方便直接打印
- 功能稳定可用并且在积极开发中
版本信息查看
```{r}
packageVersion("openxlsx")
```
本人公众号:宇飞的世界中有更加详细的阐述:
### 安装
稳定版
```{r eval=FALSE}
# 稳定版
install.packages("openxlsx", dependencies = TRUE, repos = "https://mirrors.tuna.tsinghua.edu.cn/CRAN/")
```
开发版
```{r eval = FALSE}
install.packages(c("Rcpp", "devtools"), dependencies = TRUE)
library(devtools)
install_github("ycphs/openxlsx")
```
### 基础功能
本文仅呈现基础功能部分,即读写EXCEL文件。其它功能,请查阅项目官方地址或微信公众号文章[R包-openxlsx-学习笔记](https://mp.weixin.qq.com/s/ZD0dJb0y8fsWGI1dCPh2mQ)
#### 读取Excel
read.xlsx()是读取函数,主要参数如下:
```{r eval=FALSE}
library(openxlsx)
read.xlsx(
xlsxFile,
sheet = 1,
startRow = 1,
colNames = TRUE,
rowNames = FALSE,
detectDates = FALSE,
skipEmptyRows = TRUE,
skipEmptyCols = TRUE,
rows = NULL,
cols = NULL,
check.names = FALSE,
sep.names = ".",
namedRegion = NULL,
na.strings = "NA",
fillMergedCells = FALSE
)
```
以上参数中需要注意 :
detecDates参数,当你的Excel表格中带日期列时需要将参数设置为TRUE,不然将会把日期识别为数字读入。
fillMergedCells参数,当你读取的表格中存在合并单元格,将用值填充其他全部单元格,如下所示:
![](https://gitee.com/zhongyufei/photo-bed/raw/pic/img/merge-cell-xlsx.png)
```{r eval=FALSE}
read.xlsx('./test.xlsx',detectDates = TRUE,fillMergedCells = TRUE)
```
读取后如下所示:
![openxlsx-merge-xlsx](https://gitee.com/zhongyufei/photo-bed/raw/pic/img/R-read-merge-xlsx.png)
readWorkbook()也可以读取Excel表格数据,参数与read.xlsx基本一致。
```{r eval=FALSE}
xlsxFile
个人主页:
## readr {#data:readr}
readr提供了一种快速友好的方式读取矩形数据[^矩形数据](如:csv,tsv,fwf),且当读取大型数据集时默认有进度条显示。
[^矩形数据]:矩形数据英文中表示为 rectangular data,矩形数据每一列都是变量(特征),而每一行都是案例或记录,关系数据库中的单表就是矩形数据的一种。
如果对readr包不熟悉,可以直接阅读包作者大神Hadley Wickham的书R for data science 中[data import chapter](https://r4ds.had.co.nz/data-import.html)章节。
### 安装
直接安装tidyverse获取或单独安装readr。
```{r eval= FALSE}
# The easiest way to get readr is to install the whole tidyverse:
install.packages("tidyverse")
# Alternatively, install just readr:
install.packages("readr")
# Or the the development version from GitHub:
# install.packages("devtools")
devtools::install_github("tidyverse/readr")
```
### 用法
readr包是tidyverse系列的核心包,可以加载tidyverse使用。
```{r}
library(tidyverse)
```
#### 主要函数
readr支持七种`read_`功能的文件格式。
— `read_csv()`:逗号分隔符文件
- `read_tsv()`:制表符分割文件
- `read_delim()`:规定分隔符文件
- `read_fwf()`:固定宽度文件
- `read_table()`:表格文件,列间用空格隔开
- `read_log()`:Web日志文件
在大多数情况下,我们常使用`read_csv()`,提供文件路径,将得到数据表。示例如下:
```{r}
mtcars 运行中输入\\192.168.1.247即可打开共享盘。
```{r eval=FALSE}
the_shared_disk r"()"用法是R-4.0-之后的特性。在win系统下表示路径特别有用
- mac 路径
macOS系统: `/User/vega_mac/Desktop/r`,路径中是一个正斜杠。
![mac-path](picture/read-write/mac-path.png)
```{r eval=FALSE}
readr::read_csv('/User/vega_mac/Desktop/r/Rbook/data/flights.csv')
```
### 默认路径
`getwd()`是查看当前工作目录的函数,在进行文件读写时的默认路径,也就是当没有明确指定路径时,读取导出的默认路径是`getwd()`。想要改变工作目录,通过设定`setwd()`即可。
```{r}
getwd()
```
```{r eval=FALSE}
# not run
setwd('C:/Users/zhongyf/Desktop/Rbook/data')
getwd()
```
## 拓展 {#data:expand}
1. feather项目地址
2. qs提供接口,用于快速将R对象保存到磁盘以及从磁盘读取。该包的目标是替换R中的`saveRDS`和`readRDS`。项目地址
3. arrow是feather的接替项目,地址
4. 其它统计学软件数据如spss,stata,SAs等可用`foreign`包读取
```{r include=FALSE, cache=FALSE}
set.seed(1014)
options(digits = 3)
knitr::opts_chunk$set(
comment = "#>",
collapse = TRUE,
# cache = TRUE,
out.width = "70%",
fig.align = 'center',
fig.width = 6,
fig.asp = 0.618, # 1 / phi
fig.show = "hold"
)
options(dplyr.print_min = 6, dplyr.print_max = 6)
```
```{r setup2, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```
# dplyr
`dplyr`包是`tidyverse`系列中的核心包之一,dplyr是**A Grammar of Data Manipulation **,即dplyr是数据处理的语法。
与`sql`相比,用R实现相同功能的好处:
- 代码量极大减少
- 当逻辑复杂时,`R`可以按照顺序一步步实现,无需嵌套,实现过程简单
- 该包就是从数据库相关操作中抽象而来,迁移成本低
- 配合`dbplyr`包使用,大部分情况下可以扔掉`sql`语法,从而实现不同数据库间语法并不完全一致时,代码可重复使用
本章节利用`R`语言完成与`Excel透视表`或`sql`语句的功能,将从行条件筛选、排序、分组聚合、表关联等方面记录`R`的实现方式。
> 本章节会照搬dplyr包中的部分案例
## 前言
数据操作在数据库中往往被增、改、删、查四字描述,加上表连接查询基本涵盖了大部分的数据库数据操作。在tidyverse系列中的dplyr包通过一组动词来解决相似数据操作动作。
在本章介绍部分,我们将拿R与Excel中的数据操作做类比,以达到Excel中透视表等功能。
### 安装
dplyr包可以直接安装。
```{r eval=FALSE}
## 最简单是的方式就是安装tidyverse
install.packages('tidyverse')
## 或者仅仅安装 tidyr:
install.packages('dplyr')
## 或者从github 安装开发版本
## install.packages("devtools")
devtools::install_github("tidyverse/dplyr")
```
### 介绍
`dplyr`包提供一组动词来解决最常见的数据处理问题:
- `mutate()` 添加新变量,现有变量的函数
- `select()` 筛选列,根据现有变量名称选择变量
- `filter()` 筛选行,根据条件筛选
- `summarise()` 按照一定条件汇总聚合
- `arrange()` 行排序
以上动词都可以和`group_by()`结合,使我们可以按组执行以上任何操作。除了以上单个表操作的动词,dplyr中还有操作两表(表关联)的动词,可以通过`vignette("two-table")`查看学习。
**Excel类比**
类比Excel数据操作功能,`filter`实现筛选,`mutate`实现列计算,`summarise`配合`group_by`实现数据透视表,`arrange`实现排序功能。
另外配合`dplyr::left_join()`等表连接功能,实现Excel中的`vlookup`,`xlookup`等函数效果。
dplyr包中的动词可实现Excel中的大部分数据操作功能。
如下所示:筛选订单表中的1-5月订单数据,按照城市汇总,求每个城市的销售额和门店数(去重)。
```{r eval=FALSE}
data %>%
filter(between(月,1,5)) %>%
group_by(城市) %>%
summarise(金额 = sum(金额),门店数 = n_distinct(门店编码))
```
**Cheat Sheet**
手册搬运于dplyr[官方介绍](https://dplyr.tidyverse.org/)
![dplyr-sheet](./picture/dplyr/data-transformation.pdf){width=100% height=400}
Rstudio其它手册:
## 基础用法
基础用法部分,我们将从行筛选,重命名、列位置调整、新增计算列、排序、分组聚合几个方面阐述`dplyr`动词功能。
### 加载包
```{r}
#library(dplyr)
# 禁掉提示
library(dplyr,warn.conflicts = FALSE)
```
### filter
`filter`动词顾名思义即筛选功能,按照一定条件筛选data.frame;与Excel中的筛选功能和`SQL`中`where`条件一致。
filter条件筛选中可以分为单条件筛选和多条件筛选;多条件中间用`,`分隔。
- 单条件
条件为` species == "Droid" `时,如下所示:
```{r}
starwars %>%
filter(species == "Droid")
```
```{sql eval = FALSE}
select * from starwars where species = "Droid" -- 注意=与==的区别
```
- 多条件
多条件筛选时,用英文逗号隔开多个条件。用“and”连接多个条件与用逗号隔开效果相同,“and”在R中用&表示。
```{r}
starwars %>%
filter(species == "Droid",skin_color == "gold")
# same above
# starwars %>%
# filter(species == "Droid" & skin_color == "gold")
```
```{sql eval =FALSE}
select * from starwars where species = "Droid" and skin_color = "gold"
```
- 多情况筛选
类似`SQL`中 `in` 的用法,或Excel中筛选条件时"或"条件
```{r}
starwars %>%
filter(species %in% c("Droid",'Clawdite'))
```
```{sql eval = FALSE}
select * from starwars where species in ("Droid","Clawdite") --sql查询
```
- 逻辑关系筛选
条件运算分为逻辑运算、关系运算。
关系运算符 >、=、<=分别代表大于、小于、等于、不等于、大于等于、小于等于。
逻辑运算符 &、|、!。 `|`为 或, `&` 为并、且条件,`!`为非。
```{r}
library(nycflights13)
filter(flights, !(arr_delay > 120 | dep_delay > 120))
filter(flights, arr_delay <= 120, dep_delay <= 120)
# same above
filter(flights, arr_delay <= 120 & dep_delay <= 120)
# %in% 的反面
starwars %>%
filter(!species %in% c("Droid",'Clawdite'))
```
> !的运算级别相比 %in% 更高
### select
当完整数据集列数较多时,我们某次分析可能并不需要那么多列,通过动词`select()`筛选列
- 基础用法
通过指定列名称筛选,并指定列之间顺序
```{r}
starwars %>%
select(name,height,mass,hair_color,skin_color,eye_color)
```
- 列索引
通过列名或数字向量索引,但是不建议用数字索引,避免原始数据列顺序变化后导致报错。
```{r}
starwars %>%
select(name : eye_color)
#same above
starwars %>%
select(1:6)
# starwars %>%
# select(c(1,2,4,5,7))
```
### rename
列重命名使用`rename()`函数,新名称写前面,如下所示:
```{r rename}
starwars %>% rename(home_world = homeworld)
# 多列同换
starwars %>% rename(home_world = homeworld,skincolor = skin_color)
```
```{sql eval = FALSE}
select * ,homeworld as home_word from starwars
select * ,homeworld home_word from starwars
```
> as 可以省略,但中间有一个以上空格。与R的差异是新增home_word列,原始列继续存在,R中是替换列名。
### relocate
更改列顺序,与使用`select()`动词指定列顺序功能相似。
参数
```{r eval=FALSE}
relocate(.data, ..., .before = NULL, .after = NULL)
```
```{r}
# sex:homeworld列在height列前面
starwars %>% relocate(sex:homeworld, .before = height)
```
### mutate
动词`mutate`
- 新增计算列
```{r}
starwars %>%
mutate(bmi = mass / ((height / 100) ^ 2)) %>%
select(name:mass,bmi)
```
- 新增计算列基础上新增列
```{r}
starwars %>%
mutate(bmi = mass / ((height / 100) ^ 2),newbmi = bmi *2) %>%
select(name:mass,bmi,newbmi)
```
- 删除列
```{r}
starwars %>% mutate(height = NULL)
```
### arrange
- 单列排序,默认升序,通过`desc()`降序排列
```{r}
starwars %>%
arrange(desc(mass))
```
- 多列排序
```{r}
starwars %>%
arrange(height,desc(mass))
```
```{sql eval=FALSE}
select * from starwars order by height,mass desc
```
### summarise
`summarise`常与`group_by`结合使用。
```{r}
mtcars %>%
summarise(mean = mean(disp), n = n())
```
> n()是dplyr包中的计算当前组的大小,用在summarise()和mutate()中。通常可用来组计算。
### group_by
聚合前一般都需要分组,`group_by()`动词实现该功能,与`SQL`中`group by ···`类似。
```{r}
starwars %>%
group_by(species) %>%
summarise(
n = n(),
mass = mean(mass, na.rm = TRUE)
)
```
```{sql eval=FALSE}
SELECT species,
count(species) n,
AVG(mass) mass
FROM [spb].[dbo].[starwars]
GROUP BY species
```
## 表操作
1. 指像`sql`中的`left join`,`inner join`等表格之间的操作,或者是Excel中`Power Piovt`建模的建立关系,从而实现不同表格间的关联
2. 表格中的列操作,如列求和,均值等
3. 行操作指不同字段间的计算,如`Excle`的列与列之间计算,`Excle`中的函数对行列不敏感,没有明显区别,但是`R`中`tidyverse`里列计算简单,行间计算依赖`rowwise()`函数实现
### 基础
`left_join()`,`full_join`,`inner_join()`等动词关联两个表。详情请查看:`vignette("two-table")`
`left_join()`实现类似Excel中`VLOOKUP`函数功能或数据库中`left join`功能,将“右表”的字段依据“主键”关联到“左表”上。
- 基础用法
`left_join()`,`right_join()`,`full_join()`,`inner_join`(),第一个以左表为主,第二个右表为主,第三个全连接,第四个内连接(只返回两表中都有的记录),和数据库中连接方式一致。
默认会自动寻找两表中相同的字段名作为关联的条件
```{r}
library("nycflights13")
# Drop unimportant variables so it's easier to understand the join results.
flights2 % select(year:day, hour, origin, dest, tailnum, carrier)
flights2 %>%
left_join(airlines)
```
指定关联条件列,类似数据库中`on a.column = b.column `
```{r}
flights2 %>% left_join(planes, by = "tailnum")
```
- 不同名称列关联
`left_join(x,y,by = c("a" = "b", "c" = "d"))` 将会匹配 x$a to y$b 和 x$c to y$d 作为关联条件
```{r}
#出发机场和目的机场信息
flights2 %>% left_join(airports, by = c("dest" = "faa"))
#flights2 %>% left_join(airports, c("origin" = "faa"))
# 组合条件 多条件时用向量包裹即可c("dest" = "faa","cola" = "colb"))
```
- 筛选连接
`anti_join()` 删除所有左表中在右表中匹配到的行
`semi_join()`保留所有左表在右表中匹配到的行
```{r}
df1 % semi_join(df2)
df2 %>% anti_join(df1)
```
- 集合操作
1. `intersect(x,y)`返回x,y交集
2. `union(x,y)`返回x,y中唯一的值
3. `setdiff(x,y)`返回存在x中但是不存在y中的记录
```{r}
(df1 %
group_by(g1, g2) %>%
summarise(a = mean(a), b = mean(b), c = mean(c), d = mean(d))
```
通过`across()`函数可以更简洁地重写上面代码:
```{r eval=FALSE}
df %>%
group_by(g1, g2) %>%
summarise(across(a:d, mean))
```
### 基本操作
across() 有两个主要参数:
- 第一个参数,.cols选择要操作的列。它使用`tidyr`的方式选择(例如select()),因此您可以按位置,名称和类型选择变量。
- 第二个参数,.fns是要应用于每一列的一个函数或函数列表。这也可以是purrr样式的公式(或公式列表),例如~ .x / 2。
```{r}
starwars %>%
summarise(across(where(is.character), ~ length(unique(.x))))
# 列属性是字符的列求唯一值数
# starwars %>%
# summarise(length(unique(name)))
# starwars %>%
# summarise(length(unique(hair_color)))
starwars %>%
group_by(species) %>%
filter(n() > 1) %>%
summarise(across(c(sex, gender, homeworld), ~ length(unique(.x))))
starwars %>%
group_by(homeworld) %>%
filter(n() > 1) %>%
summarise(across(where(is.numeric), ~ mean(.x, na.rm = TRUE)))
```
`across()` 不会选择分组变量:
```{r}
df %
group_by(g) %>%
summarise(across(where(is.numeric), sum))
```
### 多种函数功能
通过在第二个参数提供函数或lambda函数的命名列表,可是使用多个函数转换每个变量:
```{r}
min_max % summarise(across(where(is.numeric), min_max))
```
通过`.names`参数控制名称:
NB:该参数的机制没有特别理解,需多练习体会。主要是运用到匿名函数时
以下是官方图册中的案例,但是报错:
```{r eval=FALSE}
starwars %>% summarise(across(where(is.numeric), min_max, .names = "{.fn}.{.col}"))
```
修改后正常运行:
```{r}
starwars %>% summarise(across(where(is.numeric), min_max, .names = "{fn}.{col}"))
```
区别主要是`.names`参数的使用方式问题,`.`加不加的问题。
```{r eval=FALSE}
starwars %>% summarise(across(where(is.numeric), min_max, .names = "{fn}——{col}"))
```
### 当前列
如果需要,可以通过调用访问内部的“当前”列的名称`cur_column()`。
该函数不是特别容易理解,需要多尝试使用加深认识。
```{r}
df % mutate(across(all_of(names(mult)), ~ .x * mult[[cur_column()]]))
```
## 行操作
在操纵数据框中,`dplyr`等工具让我们对列操作相对简单,但是对行操作则困难些。
### 构造数据集
```{r}
df % rowwise()
```
像`group_by()`,`rowwise()`并没有做任何事情,它的作用是改变其他动词的工作方式:
比较以下代码中不的不同
```{r}
df %>% mutate(m = mean(c(x, y, z)))
df %>% rowwise() %>% mutate(m = mean(c(x, y, z)))
```
`data.table`中的操作:
```{r eval=FALSE}
library(data.table)
dt %
rowwise() %>%
summarise(m = mean(c(x, y, z)))
df %>%
rowwise(name) %>%
summarise(m = mean(c(x, y, z)))
```
### 行汇总统计
`dplyr::summarise()`使得汇总一列中各行的值非常容易。当与之结合使用时`rowwise()`,还可以轻松汇总一行中各列的值:
```{r}
df % rowwise(id)
rf %>% mutate(total = sum(c(w, x, y, z)))
rf %>% summarise(total = sum(c(w, x, y, z)))
```
键入每个变量名称很繁琐,通过`c_across()`使更简单
```{r}
rf %>% mutate(total = sum(c_across(w:z)))
rf %>% mutate(total = sum(c_across(where(is.numeric))))
rf %>%
mutate(total = sum(c_across(w:z))) %>%
ungroup() %>%
mutate(across(w:z, ~ . / total))
```
## 分组操作
详情:
`group_by()`最重要的分组动词,需要一个数据框和一个或多个变量进行分组:
### 添加分组
```{r}
by_species % group_by(species)
by_sex_gender % group_by(sex, gender)
```
除了按照现有变量分组外,还可以按照函数处理后的变量分组,等效在`mutate()`之后执行`group_by`:
```{r}
bmi_breaks %
group_by(bmi_cat = cut(mass/(height/100)^2, breaks=bmi_breaks)) %>%
tally()
```
### 删除分组变量
要删除所有分组变量,使用`ungroup()`:
```{r}
by_species %>%
ungroup() %>%
tally()
```
### 动词
`summarise()` 计算每个组的汇总,表示从`group_keys`开始右侧添加汇总变量
```{r}
by_species %>%
summarise(
n = n(),
height = mean(height, na.rm = TRUE)
)
```
该`.groups=`参数控制输出的分组结构。删除右侧分组变量的历史行为对应于`.groups =` "drop_last"没有消息或.groups = NULL有消息(默认值)。
从1.0.0版开始,分组信息可以保留`(.groups = "keep")`或删除 `(.groups = 'drop)`
```{r}
a %
summarise(
n = n(),
height = mean(height, na.rm = TRUE),.groups='drop') %>%
group_vars()
b %
summarise(
n = n(),
height = mean(height, na.rm = TRUE),.groups='keep') %>%
group_vars()
object.size(a)
object.size(b)
```
在实际使用中,当数据较大时需要删掉分组信息。以上可以看到保留分组信息的比没保留的大了两倍多。
## 常用函数
### 条件判断
相比于`base::ifelse`,`if_else`更为严格,无论`TRUE`或`FALSE`输出类型一致,这样速度更快。与`data.table::fifelse()`功能相似。
```{r eval=FALSE}
if_else(condition, true, false, missing = NULL)
```
与`ifelse`不同的是,`if_else`保留类型
```{r}
x % count(a, b)
# same above
df %>% group_by(a, b) %>% summarise(n = n())
```
```{r}
starwars %>% count(species)
# same above 等价
starwars %>% group_by(species) %>% summarise(n = n())
```
- 非重复计数
`n_distinct()`与` length(unique(x))`等价,但是更快更简洁。当我们需要给门店或订单之类数据需要去重计算时采用该函数。
```{r}
x % group_by(cyl)
by_cyl
# It changes how it acts with the other dplyr verbs:
by_cyl %>% summarise(
disp = mean(disp),
hp = mean(hp)
)
# group_by中可以添加计算字段 即mutate操作
mtcars %>% group_by(vsam = vs + am) %>%
group_vars()
```
- group_map()
group_map,group_modify,group_walk等三个函数是purrr类具有迭代风格的函数。简单关系数据库的数据清洗一般不涉及,常用在建模等方面。
但是目前三个函数是实验性的,未来可能会发生变化。
```{r}
# return a list
# 返回列表
mtcars %>%
group_by(cyl) %>%
group_map(~ head(.x, 2L))
```
```{r}
iris %>%
group_by(Species) %>%
group_modify(~ {
.x %>%
purrr::map_dfc(fivenum) %>%
mutate(nms = c("min", "Q1", "median", "Q3", "max"))
})
```
```{r eval=FALSE}
# group_walk
dir.create(temp %
group_by(Species) %>%
group_walk(~ write.csv(.x, file = file.path(temp, paste0(.y$Species, ".csv"))))
list.files(temp, pattern = "csv$")
unlink(temp, recursive = TRUE)
```
- group_cols()
选择分组变量
```{r}
gdf % group_by(Species)
gdf %>% select(group_cols())
```
### 其它函数
- between
- cummean cumsum cumall cumany
累计系列函数
```{r}
x
本节概念性东西较多且复杂不易理解,先尝试会使用,概念再慢慢消化理解。
虽然复杂但是比较实用,尤其是当我们需要定义一些通用功能函数时
以下是对原文引用
两种情况:
- When you have the data-variable in a function argument (i.e. an env-variable that holds a promise2), you need to ** embrace ** the argument by surrounding it in doubled braces, like `filter(df, {{ var }})`.
The following function uses embracing to create a wrapper around `summarise()` that computes the minimum and maximum values of a variable, as well as the number of observations that were summarised:
```{r eval=FALSE}
var_summary %
summarise(n = n(), min = min({{ var }}), max = max({{ var }}))
}
mtcars %>%
group_by(cyl) %>%
var_summary(mpg)
```
- When you have an env-variable that is a character vector, you need to index into the .data pronoun with [[, like summarise(df, mean = mean(.data[[var]])).
The following example uses .data to count the number of unique values in each variable of mtcars:
```{r eval=FALSE}
for (var in names(mtcars)) {
mtcars %>% count(.data[[var]]) %>% print()
}
```
Note that .data is not a data frame; it’s a special construct, a pronoun, that allows you to access the current variables either directly, with `.data$x` or indirectly with ` .data[[var]]`. Don’t expect other functions to work with it.
### 案例
当我们不知道接下来会用哪个变量汇总时:
```{r}
my_summarise %
group_by({{ group_var }}) %>%
summarise(mean = mean(mass))
}
```
如果在多个位置使用:
```{r}
my_summarise2 % summarise(
mean = mean({{ expr }}),
sum = sum({{ expr }}),
n = n()
)
}
```
当多个表达式时:
```{r}
my_summarise3 %
summarise(mean = mean({{ mean_var }}), sd = mean({{ sd_var }}))
}
```
如果要输出变量名时:
```{r}
my_summarise4 % summarise(
"mean_{{expr}}" := mean({{ expr }}),
"sum_{{expr}}" := sum({{ expr }}),
"n_{{expr}}" := n()
)
}
my_summarise5 %
summarise(
"mean_{{mean_var}}" := mean({{ mean_var }}),
"sd_{{sd_var}}" := mean({{ sd_var }})
)
}
```
任意个表达式:
这种使用场景更多
```{r}
my_summarise %
group_by(...) %>%
summarise(mass = mean(mass, na.rm = TRUE), height = mean(height, na.rm = TRUE))
}
starwars %>% my_summarise(homeworld)
starwars %>% my_summarise(sex, gender)
```
```{r include=FALSE, cache=FALSE}
set.seed(1014)
options(digits = 3)
knitr::opts_chunk$set(
comment = "#>",
collapse = TRUE,
# cache = TRUE,
out.width = "70%",
fig.align = 'center',
fig.width = 6,
fig.asp = 0.618, # 1 / phi
fig.show = "hold"
)
options(dplyr.print_min = 6, dplyr.print_max = 6)
```
# tidyr
在实际工作中,我们数据分析工作者80%的时间可能贡献在数据准备和数据清晰上。另外发现新问题时,可能又要重复数据准备、数据清晰的过程。如果采用不能完全复现的方式做数据准备清洗的工作,那将是一场灾难。
数据工作者最常用的工具可能是Excel,但是Excel并不具备很强的数据清洗能力,即使Excel有POwer query 、Dax等两大利器。工作中,实际面临原始的数据是脏乱无须的,业务系统仅仅只是记录了历史过程数据。当我们需要分析某一现象时,需要按照自己的需求重新采集数据,清洗为“标准”的数据格式。
> 标准数据:达到工作需求的数据,可以直接用Excel,power bi ,tableau等BI工具直接使用的程度。
`R`中的tidyverse系列构建了一种一致的数据结构,当我们用tidyverse软件包提供的“数据整洁工具”整洁数据时,我们将花费更少的时间将数据从一种形式迁移到另外一种形式。从而,我们拥有更多的时间专注在具体的业务问题上。
## 安装
本章节,我们重点关注`tidyr`包,这个软件包提供了许多的功能函数整理混乱的数据。tidyr是tidyverse的核心成员包
```{r eval=FALSE}
## 最简单是的方式就是安装tidyverse
install.packages('tidyverse')
## 或者仅仅安装 tidyr:
install.packages('tidyr')
## 或者从github 安装开发版本
## install.packages("devtools")
devtools::install_github("tidyverse/tidyr")
# CTEST CODE
```
## 主要功能
整洁的数据表现为:
1. 每个变量是单独的一列
2. 每一个观察的值都在自己的行
3. 每一个值都是独立的单元格
大部分的数据集都是用行和列构成的`data.frame`。用Excel的单元格来表示,即每列代表不同意义的字段,每行是某个情形下的一系列字段;单元格则是独立的值,属于某个变量的观察值,这样构建的二维数据结构则是“整洁数据”。
```{r}
library(tidyr)
```
`tidyr`包中的函数可以分为5个主要大类
- `pivot_longer()` 和 `pivot_wider()` 宽转长以及长转宽
- `unnest_longer()` 和 `unnest_wider()`,`hoist()` 将列表嵌套转化为整洁数据
- `nest()` 数据嵌套
- `separate()`,`extract()`拆分列,提取新列
- `replace_na()` 缺失值处理
### 宽转长
详情查看`vignette("pivot")`,以下是照搬该图册中的内容
#### 基础
长数据与宽数据之间的转换,类似我们常用的EXcel中的透视表功能。接下来用`tidyr`包自带的插图案例记录相关函数用法
在Excel中有时候方便我们肉眼观察,可能一个数据集会有很多列,如下所示:
col1 | col2 | col3 |col4 |col5 |col6 |col7
---- | ---- | ----- |------ |-----|-----|----
v1 | v2 | v3 |v4 |v5 |v6 |v7
vb1 | vb2 | vb3 |vb4 |vb5 |vb6 |vb7
方便观察,但是不方便统计分析,这是我们需要把数据做处理,从"宽数据变成长数据"即宽转长。
```{r}
library(tidyr)
library(dplyr)
library(readr)
```
```{r}
relig_income %>%
pivot_longer(cols = !religion,names_to = 'income',values_to = "count")
```
* 第一个参数是数据集
* 第二个参数是那些列需要重塑,在该例中除了`religion`的其他全部列
* `names_to`这个参数是新增的列名
* `values_to`是新增的存储之前数据集中数据的列名
#### 列名带数字
```{r}
billboard %>%
pivot_longer(
cols = starts_with("wk"),
names_to = "week",
values_to = "rank",
values_drop_na = TRUE
)
```
`names_prefix` 调整内容前缀,配合`names_transform`参数使用
```{r}
billboard %>%
pivot_longer(
cols = starts_with("wk"),
names_to = "week",
names_prefix = "wk",
names_transform = list(week = as.integer),
values_to = "rank",
values_drop_na = TRUE,
)
```
经过以上转换`week`列属性变成了整数,当然达到以上效果有其他的途径,如下:
```{r eval=FALSE}
library(tidyverse,warn.conflicts = TRUE)
# method 1
billboard %>%
pivot_longer(
cols = starts_with("wk"),
names_to = "week",
names_transform = list(week = readr::parse_number),
values_to = "rank",
values_drop_na = TRUE,
)
# method 2
billboard %>%
pivot_longer(
cols = starts_with("wk"),
names_to = "week",
values_to = "rank",
values_drop_na = TRUE,
) %>%
mutate(week = str_remove(week, "wk") %>% as.integer())
```
#### 多变量列名
该案列设计比较复杂的正则表达式,`new_?(.*)_(.)(.*)`需要一定正则表达式基础。
`new_?`表示匹配`new`或`new_`,`(.*)`匹配任意0次或多次任意字符。
[正则表达式介绍](https://www.runoob.com/regexp/regexp-syntax.html)
```{r}
who %>% pivot_longer(
cols = new_sp_m014:newrel_f65,
names_to = c("diagnosis", "gender", "age"),
names_pattern = "new_?(.*)_(.)(.*)",
values_to = "count"
)
```
进一步处理列`gender`,`age` 。
```{r}
who %>% pivot_longer(
cols = new_sp_m014:newrel_f65,
names_to = c("diagnosis", "gender", "age"),
names_pattern = "new_?(.*)_(.)(.*)",
names_transform = list(
gender = ~ readr::parse_factor(.x, levels = c("f", "m")),
age = ~ readr::parse_factor(
.x,
levels = c("014", "1524", "2534", "3544", "4554", "5564", "65"),
ordered = TRUE
)
),
values_to = "count",
)
```
#### 一行多观测值
```{r}
family % mutate_at(vars(starts_with("dob")), parse_date)
family
```
```{r}
family %>%
pivot_longer(
!family,
names_to = c(".value", "child"),
names_sep = "_",
values_drop_na = TRUE
)
```
```{r}
anscombe %>%
pivot_longer(everything(),
names_to = c(".value", "set"),
names_pattern = "(.)(.)"
) %>%
arrange(set)
```
```{r}
pnl %
pivot_longer(
!c(x, a, b),
names_to = c(".value", "time"),
names_pattern = "(.)(.)"
)
```
#### 重复列名
```{r}
df % pivot_longer(!id, names_to = "name", values_to = "value")
```
### 长转宽
`pivot_wider()`功能与`pivot_longer()`相反。通过增加列数减少行数使数据集变得更宽,通常我们在汇总时候使用,达到类似Excel透视表结果。
#### 基础
```{r}
fish_encounters %>% pivot_wider(names_from = station, values_from = seen)
```
缺失值填充
```{r}
fish_encounters %>% pivot_wider(
names_from = station,
values_from = seen,
values_fill = 0
)
```
#### 聚合
```{r}
warpbreaks % as_tibble()
warpbreaks %>% count(wool, tension)
```
需要通过`values_fn`指定聚合方式
```{r}
warpbreaks %>% pivot_wider(names_from = wool, values_from = breaks,values_fn= list(breaks = sum))
```
#### 从多个变量生成新列名
```{r}
production %
filter((product == "A" & country == "AI") | product == "B") %>%
mutate(production = rnorm(nrow(.)))
production
```
```{r}
production %>% pivot_wider(
names_from = c(product, country),
values_from = production
)
```
通过`names_sep`和`names_prefix`参数控制新的列名,或通过`names_glue`
```{r}
production %>% pivot_wider(
names_from = c(product, country),
values_from = production,
names_sep = ".",
names_prefix = "prod."
)
```
```{r}
production %>% pivot_wider(
names_from = c(product, country),
values_from = production,
names_glue = "prod_{product}_{country}"
)
```
#### 多值变宽
```{r}
us_rent_income %>%
pivot_wider(names_from = variable, values_from = c(estimate, moe))
```
### 处理json,html的数据
实际工作中不是经常使用,需要使用的时候往往会用相关的包处理:`jsonlite`
可通过`vignette("rectangle")`自行学习
```{r}
library(tidyr)
library(dplyr)
library(repurrrsive)
```
```{r}
users % unnest_wider(user)
```
### 嵌套数据
```{r}
library(tidyr)
library(dplyr)
library(purrr)
```
#### 基础
嵌套数据即:数据框中嵌套数据框,如下所示:
```{r}
df1 % nest(data = c(x, y))
#sample above
#df2 %>% group_by(g) %>% nest()
```
nest的反面 unnest
```{r}
df1 %>% unnest(data)
```
### 嵌套数据和模型
```{r}
mtcars_nested %
group_by(cyl) %>%
nest()
mtcars_nested
```
```{r}
mtcars_nested %
mutate(model = map(data, function(df) lm(mpg ~ wt, data = df)))
mtcars_nested
```
```{r}
mtcars_nested %
mutate(model = map(model, predict))
mtcars_nested
```
### 拆分和合并
#### 拆分
有时我们需要将一列拆分为多列:
```{r}
library(tidyr)
df % separate(x, c("A", "B"))
```
拆分数多列或少列时用`NA`补齐:
```{r}
df % separate(x, c("a", "b"))
```
多余的部分舍弃,缺失填充在左边还是右边:
```{r}
# The same behaviour as previous, but drops the c without warnings:
df %>% separate(x, c("a", "b"), extra = "drop", fill = "right")
```
多余部分合并,缺失填充在左边
```{r}
df %>% separate(x, c("a", "b"), extra = "merge", fill = "left")
```
或者全部保留
```{r}
df %>% separate(x, c("a", "b", "c"))
```
指定分隔符
```{r}
df %>% separate(x, c("key", "value"), sep = ": ", extra = "merge")
```
使用正则表达式
```{r}
# Use regular expressions to separate on multiple characters:
df % separate(x, c("A","B"), sep = "([.?:])")
```
#### 新列提取
```{r}
df % extract(x, "A")
df %>% extract(x, c("A", "B"), "([[:alnum:]]+)-([[:alnum:]]+)")
# [:alnum:] 匹配字母和数字
```
以上本质是字符处理,[正则表达式](http://baiy.cn/utils/_regex_doc/index.htm)
#### 合并
```{r}
df % unite("z", x:y, remove = FALSE)
# expand_grid 类似笛卡尔积功能
```
移除缺失值
```{r}
df %>% unite("z", x:y, na.rm = TRUE, remove = FALSE)
```
合并后再拆分
```{r}
df %>%
unite("xy", x:y) %>%
separate(xy, c("x", "y"))
```
### 缺失值处理
`replace_na()`用特定值替换缺失值。
```{r}
df % replace_na(list(x = 0, y = "unknown"))
```
```{r}
df %>% dplyr::mutate(x = replace_na(x, 0))
```
```{r include=FALSE, cache=FALSE}
set.seed(1014)
options(digits = 3)
knitr::opts_chunk$set(
comment = "#>",
collapse = TRUE,
# cache = TRUE,
out.width = "70%",
fig.align = 'center',
fig.width = 6,
fig.asp = 0.618, # 1 / phi
fig.show = "hold"
)
options(dplyr.print_min = 6, dplyr.print_max = 6)
```
# 字符处理
实际数据分析工作中,经常需要处理字符串以便让数据整洁,符合分析需求。在我们常用的Excel或sql中也有处理字符串的经验,大部分时候截断、替换等基础实现就能满足我们的大部分字符处理需求。
Excel中自带的字符串函数[^text],如:`left`,`len`,`mid`,`find`,`Proper`,`rept`,`trim`,`upper`,`substitute`,`concatenate`,以及`Excle`2019新出的`concat`,`TEXTJOIN`等字符函数,`TEXTJOIN`函数我个人比较喜欢用。在学习R的字符处理时候可以自行尝试实现以上相对应功能。
但是Excel中字符处理功能存在一定局限性,没有直接可用的正则表达式[^Regularexpression]函数,在正则表达式本身就很困难的情况下,在VBA中实现较复杂的函数将会难上加难。
字符处理本人觉得本质上就是将字符定位后去实现不同的操作。所以觉得难点在于字符串中字符的定位,而实现这个功能就需要用到正则表达式,所以字符处理真正的难点在于正则表达式的编写,但是在我看来正则表达式想要掌握,难度过高,我们秉着随用随查的态度对待即可。
因为大部分的数据分析工作者并不会面临太多复杂的字符处理工作,对大部分常规商业数据分析工作者面对的数据而言,字符处理可能仅仅只是合并、剔除、删除空格、倒叙等基础操作。面对舆情监控,购物评价等纯文本情感分析工作,个人觉得对普通数据分析岗位有点超纲,所以本章节的字符处理仅仅是常规的字符处理。
[^text]:Excel中支持的[TEXT functions] (https://support.microsoft.com/zh-cn/office/%e6%96%87%e6%9c%ac%e5%87%bd%e6%95%b0%ef%bc%88%e5%8f%82%e8%80%83%ef%bc%89-cccd86ad-547d-4ea9-a065-7bb697c2a56e?ui=zh-CN&rs=zh-CN&ad=CN)
[^Regularexpression]:正则表达式(regular expression)描述了一种字符串匹配的模式(pattern),查看帮助`?regex`。
## base R
本部分简述base R中关于字符处理的常用函数。
### 单双引号
`R`语言中字符串输入时,可以使用单引号,也可以使用双引号,详情请看`?Quotes`。
- 单双引号用法和意义没有差别
- R中推荐使用双引号分隔符,打印、显示时都是用双引号
- 单引号字符串通常用在字符串内包含双引号时,如用R执行sql字符串代码时
- R-4.0之后引入的R字符[新特性],让单双引号基本没区别
R中的字符用单双引号都可创建,如下所示:
```{r}
x 错误: unexpected string constant in "paste(""",""
```
R语言中使用"\"把特定的字符转义为特殊字符,例如“\t”是制表符,“\n”是换行符,或者是“\r\n”(系统差异)。想要正确显示"'",需使用转义,如下所示:
```{r}
char
如果不熟悉R中的字符串,可以从[R for Data Science](https://r4ds.had.co.nz/strings.html)的字符串部分开始学习,
本小节的部分案例照搬[R for Data Science](https://r4ds.had.co.nz/strings.html)。
### 安装
```{r eval=FALSE}
# Install the released version from CRAN:
install.packages("stringr")
# Install the cutting edge development version from GitHub:
# install.packages("devtools")
devtools::install_github("tidyverse/stringr")
```
### 基本使用
stringr包中所有的函数都已`str_`开头,让字符做第一个参数。
- 字符串长度
```{r}
library(stringr)
char
strings % unlist()
# same above
#str_split('ab||cd','\\|\\|') %>% purrr::as_vector()
```
当待处理的字符串是字符串向量时,得到的列表长度与向量长度一致
```{r}
fruits % unlist() #str_split 拆解出来是列表 需要向量化
}
dt[,list(newcol=my_str_split(letters)),by=.(col)]
```
## base和stringr
以下表格数据对比,主要是base R 和 stringr中的相应字符处理功能函数对比。
表格数据来源[stringr and base differences](https://stringr.tidyverse.org/articles/from-base.html)。表格数据可用以下代码获取(注意网络):
```{r eval=FALSE}
library(tidyverse)
library(rvest)
dt %
html_table() %>% `[[`(1)
```
| base | stringr |
| ---------------------------------- | ---------------------------------------- |
| gregexpr(pattern, x) | str_locate_all(x, pattern) |
| grep(pattern, x, value = TRUE) | str_subset(x, pattern) |
| grep(pattern, x) | str_which(x, pattern) |
| grepl(pattern, x) | str_detect(x, pattern) |
| gsub(pattern, replacement, x) | str_replace_all(x, pattern, replacement) |
| nchar(x) | str_length(x) |
| order(x) | str_order(x) |
| regexec(pattern, x) + regmatches() | str_match(x, pattern) |
| regexpr(pattern, x) + regmatches() | str_extract(x, pattern) |
| regexpr(pattern, x) | str_locate(x, pattern) |
| sort(x) | str_sort(x) |
| strrep(x, n) | str_dup(x, n) |
| strsplit(x, pattern) | str_split(x, pattern) |
| strwrap(x) | str_wrap(x) |
| sub(pattern, replacement, x) | str_replace(x, pattern, replacement) |
| substr(x, start, end) | str_sub(x, start, end) |
| tolower(x) | str_to_lower(x) |
| tools::toTitleCase(x) | str_to_title(x) |
| toupper(x) | str_to_upper(x) |
| trimws(x) | str_trim(x) |
通过以上对比,方便我们从Base R 切换到stringr包的使用。
## 参考资料
1. tidyverse-stringr:
2. stringr vignettes:
3. R new feature:
4. R-4.0.0 NEW features:
```{r include=FALSE, cache=FALSE}
set.seed(1014)
options(digits = 3)
knitr::opts_chunk$set(
comment = "#>",
collapse = TRUE,
# cache = TRUE,
out.width = "70%",
fig.align = 'center',
fig.width = 6,
fig.asp = 0.618, # 1 / phi
fig.show = "hold"
)
options(dplyr.print_min = 6, dplyr.print_max = 6)
```
# 时间处理 {#datetime }
时间处理看起来是一件简单的事情,因为我们每天都在使用,但事实上是一件复杂的事情。闰年导致每年的天数并不一致,每天也并不是24小时。可以自行搜索“夏令时”,“为什么一天是24小时”或"Why do we have 24 hours in a day"等问题了解关于时间的概念。
但是我们做数据分析,可能仅需要简单的计算时间,并不是必须了解“时间”。我们大部分时候能处理同环比,间隔天数等常规问题即可。
由于能力有限以及处理的日期数据类型有限,本章节仅就常规商业数据分析中时间处理提供一种解决办法。
本章主要分为三大部分:
- Base R中时间处理函数
- lubridate包提供的日期时间处理方法
- 常规运用以及和Excel的时间系统对比
## base R {#datetime:base-R}
R中内置Date,POSIXct和POSIXlt三个关于日期和时间的类[^类]。
[^类]:类是面向对象编程的一个术语,一个对象通常有0个1个或多个类。在R中用`class()`函数查看所属对象的类。
### Date {#datetime:base-R-date}
如果我们的数据结构中只有日期,没有时间,我们仅需要使用Date类。
```{r}
class(Sys.Date())
```
1.创建日期
```{r}
date1 tm1
```
加减计算,默认单位秒
```{r}
tm1 + 300
tm2 - 300
```
```{r}
tm2 - tm1
```
### POSIXlt {#datetime:base-R-POSIXlt}
通过此类,我们可以很便捷提取时间中的特定成分。其中"ct"代表日历时间,"it"代表本地时间,该类对象是list(列表)。
创建时间
```{r}
t1 曾经我在使用阿里云的RDS数据库时没注意时区差异,导致我清洗出来的时间数据错误。
```{r}
ymd_hms(.POSIXct(1591709615),tz = 'Asia/Shanghai')
```
### 提取日期时间成分 {#lubridate:extracting-information}
```{r}
#获取年
year(now())
#获取月
month(now())
# 当前时间所在年份天数
yday(now())
# 当前时间所在月天数
mday(now())
# 周几
wday(now(),label = TRUE,week_start = 1)
# 所在时刻
hour(now())
# 所在时刻
minute(now())
# 所在时刻
second(now())
```
### 处理时区 {#lubridate:time-zones}
数据时区与本地R环境一致时,数据中的时区没必要处理,但是当数据是跨时区的或者不同生产系统的时区不一致,我们需要将数据时区处理一致。
1.时区查看
时区和所用系统设置相关
```{r}
Sys.timezone()
# windows 系统默认的时区 中国台北
# linux 上是"Asia/Shanghai"
# mac 上是"Asia/Shanghai"
```
这里还有一个奇怪的点,Windows系统下时区设置为`(UTC+08:00)北京,重庆,香港特别行政区,乌鲁木齐`,但是R返回的时区是`Asia/Taipei`。
```{r}
now()
```
`now()`输出结果中,CST是时区概念。
CST可以同时代表四个时间
- Central Standard Time (USA) UT-6:00
- Central Standard Time (Australia) UT+9:30
- China Standard Time UT+8:00
- Cuba Standard Time UT-4:00
2.时区调整
lubridate中用`with_tz()`,`force_tz()`处理时区问题
```{r}
time
```{r}
lubridate::now() # now函数调用系统默认时区
as_datetime(now()) #as_datetime默认是UTC
as_datetime(now(),tz = 'asia/shanghai')
```
### 时间间隔
`lubridate`中将时间间隔保存为`interveal`类对象。
```{r}
arrive % mutate( 类型 = case_when(between(bill_date,current_start_date,date) ~ "当前",
between(bill_date,last_start_date,last_end_date) ~ "同期",
TRUE ~ "其他")) %>%
filter(类型 != "其他") %>%
group_by(...) %>%
summarise(金额 = sum(money,na.rm = TRUE)) %>%
ungroup()
#%>% pivot_wider(names_from = '类型',values_from = '金额')
}
```
```{r}
y_to_y(dt,date = '20201001',n = 1,area,类型) %>%
tidyr::pivot_wider(id_cols = 'area',names_from = '类型',values_from = '金额') %>%
mutate(增长率 = 当前 / 同期)
y_to_y(dt,date = '20201001',n = 1,area,类型,category) %>%
tidyr::pivot_wider(id_cols = c('area','category'),names_from = '类型',values_from = '金额') %>%
mutate(增长率 = 当前 / 同期)
```
### 清洗不同类型日期格式
如将`c('2001/2/13 10:33','1/24/13 11:16')`转换为相同格式的日期格式;
通过一个简单自定义函数解决,本质是区分不同类型日期后采用不同函数去解析日期格式
```{r message=FALSE}
library(lubridate)
library(tidyverse)
date1 % `[[`(1) %>% `[[`(1)
if(str_length(n)==4){
res[i] % which.min() #本处不对,应该判断res大于0的部分中谁最小
kong y]
res % which.min()
kong y]
res % which.min()
kong
2. date and time
3. dax时间函数
4. Excel日期系统
*
* pdf 下载
```{r include=FALSE, cache=FALSE}
set.seed(1014)
options(digits = 3)
knitr::opts_chunk$set(
comment = "#>",
collapse = TRUE,
# cache = TRUE,
out.width = "70%",
fig.align = 'center',
fig.width = 6,
fig.asp = 0.618, # 1 / phi
fig.show = "hold"
)
options(dplyr.print_min = 6, dplyr.print_max = 6)
```
# forcats
我在实际工作中因子数据类型使用较少,forcats软件包用来处理因子,该软件包是tidyverse的一部分.
因子是用于对数据进行分类的R的一种数据类型. 它们可以存储字符串和整数.它们在具有有限数量的唯一值的列中很有用. 像“男性”,“女性”和True,False等。它们在统计建模的数据分析中很有用.
因子变量会占用更小空间,R4.0改变了字符默认为因子的方式.想了解更多请参考
```{r }
object.size(rep(letters,100000))
object.size(rep(forcats::as_factor(letters),100000))
```
## 创建因子
实际工作中,可能各个事业部或部门之间没有实际顺序,但是在数据处理过程中需要指定顺序可以用因子.
```{r}
library(forcats)
vec1
```{r include=FALSE, cache=FALSE}
set.seed(1014)
options(digits = 3)
knitr::opts_chunk$set(
comment = "#>",
collapse = TRUE,
# cache = TRUE,
out.width = "70%",
fig.align = 'center',
fig.width = 6,
fig.asp = 0.618, # 1 / phi
fig.show = "hold"
)
options(dplyr.print_min = 6, dplyr.print_max = 6)
```
# data.table
data.table包是我数据处理最常用的R包,是我目前觉得最好用的数据处理包,大部分我需要用到的功能集成在包里,不需要很多的依赖包。我简单接触过python,julia两种语言,并没有深入比较,所以我这个好用的印象仅仅是个人感受。
data.table包是我用了较长一段时间tidyverse系列后发现的“数据处理包”。已经忘记最初是什么吸引了我,我猜测可能是“大数据处理利器”之类的标签吸引了我,因为我喜欢“快”。但是和大部分人可能不同的是,初次接触时,语法的“怪异”并没有给我带来多少麻烦,因为我本来就没有编程基础以及很深的R语言基础。
所以我死记硬背data.table里一些常用用法,尤其喜欢拿Excle的一些用法参照,去实现Excle上面的部分操作,从读取、增、改、删除、筛选、计算列等常规操作入手。慢慢熟悉data.table语法之后,将会享受data.table带来的便利,其简洁的语法以及高效的计算速度(相比tidyverse系列)。
另外,Python中也有该包,目前正在积极开发中,期待ing,毕竟python也是很好用,在不同需求下选择不同的语言实现功能。
官方关于data.table的基础介绍请参阅:
https://cran.r-project.org/web/packages/data.table/vignettes/datatable-intro.html
data.table 优势:
- 速度快
- 内存效率高
- API生命周期管理好
- 语法简洁
## 基础介绍
本部分从data.table安装,内置的案例查看,到data.table的句式语法,实现基础行列筛选和聚合计算。
1.安装
安装详细信息请参考[the Installation wiki](https://github.com/Rdatatable/data.table/wiki/Installation),有关于不同系统安装首次以及相关说明。
```{r eval=FALSE}
install.packages("data.table")
# latest development version:
data.table::update.dev.pkg()
```
2.使用说明
通过以下代码查看内置的使用案例。
```{r eval=FALSE}
library(data.table)
example(data.table)
```
### 读取数据
在我实际工作中接触的数据大部分以数据库,csv,Excel等形式存在,并且CSV格式数据较少。但是data.table包读取数据的`fread`函数仅接受CSV格式。如果是Excel格式文件,需要通过如`readxl`,`openxlsx`等包读入后转换为`data.table`格式数据。
fread 函数可以直接读取CSV格式文件,无论是本地文件或者在线文件.
本文会照搬很多官方关于data.table的demo.
```{r}
library(data.table)
input 、 =、<=分别代表大于、小于、等于、不等于、大于等于、小于等于。常用的逻辑运算符 &、|、!等。
```{r eval=FALSE}
#单条件筛选
filghts[year == 2014] #筛选year==2014
#多条件筛选 用 & 链接
flights[ year == 2014 & month == 6]
# | 相当于中文条件或
flights[ month == 5 | month == 6]
# %in% 类似sql中in用法
flights[month %in% c(1,3,5,7,9)]
# %between% 类似sql中between and 用法
flights[month %between% c(1,7)]
```
#### j列操作
数据集较大、字段较多时,由于无效信息较多可以做适当精选,这时需要我们筛选列。与sql中的select用法一致,即保留想要的字段。
.()或list()是data.table中的比较特殊的实现列筛选的用法。常规数字索引,字符向量索引同样有效。
```{r}
#注意前面的. .()
flights[,.(year,month,day,dep_delay,carrier,origin)]
# flights[,list(year,month,day,dep_delay,carrier,origin)] same above
# not run
# flights[,1:3]
# not run
# flights[,c('year','month','day')]
```
setcolorder函数可以调整列的顺序,将常用的字段信息排在前面可以用过该函数实现。
```{r}
# not run
# setcolorder(x = flights,neworder = c( "month","day","dep_delay" ,"arr_delay","carrier" ))
# 按照指定列顺序排序 其余字段保持不变,不是建立副本,是直接修改了flights 数据的列顺序
```
- 常规计算
根据最开始的Excel透视表截图,我们想要获得如截图一样的结果该怎么实现呢?代码如下:
```{r,eval=FALSE}
flights[year==2014 & month==6,.(求和项distance=sum(distance),平均距离=mean(distance)),by=.(origin,dest)]
```
在i的位置做筛选,j的位置做计算,by指定分组字段。在j的位置可以做各种各样的计算,R中自带的函数,或者是自己定义的函数。
```{r}
myfun 6)] #by里面可以做计算
head(dt)
```
### 行列筛选总结
行筛选在 i 的位置上进行, 列筛选在 j 的位置上进行;data.table中j的位置比较灵活多变,但是i的位置大部分时候都是进行条件筛选。我们通过上述的行列筛选已经大概知道data.table中i,j的用法。也就是我们常规数据清洗过程中的数据筛选过程,筛选符合要求的数据记录。
```{r}
dt =15,.(year,month,day,dep_delay,carrier,origin)]
head(dt)
```
## 常规操作
### 行筛选
上文已经大致讲过行筛选,但是行筛选使用有一定的技巧,涉及到运算的快慢。主要是逻辑条件的设置,交集并集之间的差异。除了上文中的关系运算筛选,逻辑运算筛选除外,data.table中还有几个常用的筛选函数。
- 数字向量筛选
%in%用法与 sql 中 in 用法类似。
```{r eval=FALSE}
# 筛选 %in%
flights[ hour %in% seq(1,24,2) ]
```
- 字符向量筛选
%chin%用法与 %in% 类似,但仅仅针对字符。
```{r eval=FALSE}
# 字符筛选
flights[ origin %chin% c('JFK','LGA')]
# not run 同上 %chin% 对字符速度筛选速度更快
#flights[ origin %in% c('JFK','LGA')]
```
- between 筛选
该函数的新特性矢量化挺实用。
```{r}
#between 函数参数
#between(x, lower, upper, incbounds=TRUE, NAbounds=TRUE, check=FALSE)
X Note: DT[a > 4, b := c] is different from DT[a > 4][, b := c]
### 排序
当我们清洗数据时,我们需要将数据框排序,我们可以使用`setorder`或`setorderv`函数实现排序。函数是`data.table`包的函数,比base R 中的`order`函数要节省内存。
注意:按照函数文档说法:Note that queries like x[order(.)] are optimised internally to use data.table's fast order。即x[order(.)]这样的用法会被优化为data.table的排序方法。
```{r}
set.seed(45L)
DT = data.table(A=sample(3, 10, TRUE),
B=sample(letters[1:3], 10, TRUE), C=sample(10))
setorder(DT, A, -B) #将DT按照A、B排序 A 升序,-B降序
# 和上面同样的效果 但是函数变成 setorderv
setorderv(DT, c("A", "B"), c(1, -1))
```
## 常用函数
常用函数指我们常用功能的函数,如排名、排序、非重复计数、判断、表连接、长宽转换等功能。
### 特殊符号
.SD,.BY,.N,.I,.NGRP和.GRP,.SDcols等,只能用在 j 的位置,.N 可以用在 i 的位置。
如果想要记住用法需要自己多尝试练习,对于我来说.N使用较多。
```{r}
DT = data.table(x=rep(c("b","a","c"),each=3), v=c(1,1,1,2,2,1,1,2,2), y=c(1,3,6), a=1:9, b=9:1)
DT
X = data.table(x=c("c","b"), v=8:7, foo=c(4,2))
X
# 用在i的位置
DT[.N] #取DT最后一行,.N 计数函数
DT[,.N] #DT 共有多少行记录 返回一个整数
DT[, .N, by=x] #分组计数
DT[, .SD, .SDcols=x:y] # 选择x 到y 列
#DT[, .SD, .SDcols=c("x","y")] 与上面不一样
DT[, .SD[1]] #取第一行
DT[, .SD[1], by=x] #按x列分组后
DT[, c(.N, lapply(.SD, sum)), by=x] #按照x分组后 行数计数和每列求和
```
### 排序函数
`frank`和`frankv`函数参数如下:
```{r eval=FALSE}
frank(x, ..., na.last=TRUE, ties.method=c("average",
"first", "last", "random", "max", "min", "dense"))
frankv(x, cols=seq_along(x), order=1L, na.last=TRUE,
ties.method=c("average", "first", "random",
"max", "min", "dense"))
```
官方案例,如下所示:
```{r}
# on vectors
x = c(4, 1, 4, NA, 1, NA, 4)
# NAs are considered identical (unlike base R)
# default is average
frankv(x) # na.last=TRUE
frankv(x, na.last=FALSE)
# on data.table
DT = data.table(x, y=c(1, 1, 1, 0, NA, 0, 2))
frankv(DT, cols="x") # same as frankv(x) from before
frankv(DT, cols="x", na.last="keep")
frankv(DT, cols="x", ties.method="dense", na.last=NA)
frank(DT, x, ties.method="dense", na.last=NA) # equivalent of above using frank
```
* frankv在排序时,NA被认为是一样的,基础base R 中认为不一样.
```{r}
x 2L, x, x - 1L)
fifelse(x > 2L,fifelse(x >= 4L,x + 1L,x),x-1L)
```
- fcase
与sql中的case when,与dplyr中的`case_when()`函数用法相似。相比fifelse相比,嵌套更加方便。
```{r}
x = 1:10
fcase(
x < 5L, 1L,
x > 5L, 3L
)
# not run 两种函数实现方式
fifelse(x > 5,fifelse(x >8,2,1),0)
fcase(
x > 8,2,
x > 5,1,
default = 0
)
```
### 交集 差集 合并
相当于base R 中 union(),intersect(),setdiff() 和setequal() 功能.all参数控制如何处理重复的行,和SQL中不同的是,data.table将保留行顺序.
```{r eval=FALSE}
fintersect(x, y, all = FALSE)
fsetdiff(x, y, all = FALSE)
funion(x, y, all = FALSE)
fsetequal(x, y, all = TRUE)
x % setorderv(cols=c('area','grouping'),na.last = TRUE))
```
通过上述计算,发现计算结果与Excel透视表一样。
- cube
观察`cube()`计算结果与`rollup()`差异,发现`cube()`聚合层次更多。
```{r}
cube(DT,j = sum(value),by = c("area","store_type"),id = TRUE)
```
- groupingsets
根据需要指定指定聚合的层次。
```{r}
# 与本例中rollup 结果一致
groupingsets(DT,j = sum(value),by = c("area","store_type"),sets = list('area',c("area","store_type"), character()),id = TRUE)
# 与本例中cube 结果一致
groupingsets(DT,j = sum(value),by = c("area","store_type"),sets = list('area',c("area","store_type"),"store_type", character()),id = TRUE)
```
> groupingsets: sets参数,用list()包裹想要聚合的字段组合,最后character(),加上该部分相当于不区分层级全部聚合,用法类似sql中"()".
> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
[comment]: <> (This is a comment, it will not be included)
### rleid
该函数根据分组生成长度列。
即将0011001110111101类似这种分组成1 1 2 2 3 3 4 4 4 5 6 6 6 6 7 8。在特定时候是很便捷的一个函数。如在计算股票连续上涨或下跌天数时。
```{r}
rleid(c(0,0,1,1,0,0,1,1,1,0,1,1,1,1,0,1))
```
用法:
```{r eval=FALSE}
rleid(..., prefix=NULL)
rleidv(x, cols=seq_along(x), prefix=NULL)
```
```{r}
DT = data.table(grp=rep(c("A", "B", "C", "A", "B"), c(2,2,3,1,2)), value=1:10)
rleid(DT$grp) # get run-length ids
rleidv(DT, "grp") # same as above
rleid(DT$grp, prefix="grp") # prefix with 'grp'
```
### shift
向前或向后功能,通俗来说就是向前或向后移动位置。
示例如下:
```{r}
x = 1:5
# lag with n=1 and pad with NA (returns vector)
shift(x, n=1, fill=NA, type="lag")
```
其中参数n控制偏移量,n正负数和type的参数相对应。, n=-1 and type='lead' 与 n=1 and type='lag'效果相同。
在data.table上使用:
```{r}
DT = data.table(year=2010:2014, v1=runif(5), v2=1:5, v3=letters[1:5])
cols = c("v1","v2","v3")
anscols = paste("lead", cols, sep="_")
DT[, (anscols) := shift(.SD, 1, 0, "lead"), .SDcols=cols]
```
例如求某人连续消费时间间隔天数时:
```{r}
DT = data.table(dates =lubridate::ymd(c(20210105,20210115,20210124,20210218,20210424)))
DT[,newdate:=shift(dates)]
DT
```
通过构造新列newdate,然后将两列相减`dates-newdate`即可得到每次购物间隔天数。
### J
J 是`.()`,`list()`等的别名。`SJ`是排序连接,`CJ`是交叉连接。
用法:
```{r eval=FALSE}
# DT[J(...)] # J() only for use inside DT[...]
# DT[.(...)] # .() only for use inside DT[...]
# DT[list(...)] # same; .(), list() and J() are identical
SJ(...) # DT[SJ(...)]
CJ(..., sorted=TRUE, unique=FALSE) # DT[CJ(...)]
```
- CJ
我喜欢用`CJ()`函数创建笛卡尔积表。例如在商品运营中,时常需要将门店和商品形成笛卡尔积表,相比起`dplyr::full_join()` ,`data.table::merge.data.table(allow.cartesian = TRUE )`,`CJ`更加方便快捷。
```{r}
# CJ usage examples
CJ(c(5, NA, 1), c(1, 3, 2)) # sorted and keyed data.table
# do.call(CJ, list(c(5, NA, 1), c(1, 3, 2))) # same as above
# CJ(c(5, NA, 1), c(1, 3, 2), sorted=FALSE) # same order as input, unkeyed
```
- SJ
SJ : Sorted Join. The same value as J() but additionally setkey() is called on all columns in the order they were passed to SJ. For efficiency, to invoke a binary merge rather than a repeated binary full search for each row of i.
## 小技巧
### 用{}抑制中间过程输出
默认只返回未命名花括号中定义的最后一个对象。
```{r}
dt
```{r include=FALSE, cache=FALSE}
set.seed(1014)
options(digits = 3)
knitr::opts_chunk$set(
comment = "#>",
collapse = TRUE,
# cache = TRUE,
out.width = "70%",
fig.align = 'center',
fig.width = 6,
fig.asp = 0.618, # 1 / phi
fig.show = "hold"
)
options(dplyr.print_min = 6, dplyr.print_max = 6)
```
```{r setup1, include=FALSE}
knitr::opts_chunk$set(echo = TRUE,eval = FALSE)
```
# database
实际工作中,需要从数据库获取数据并清洗,R与数据库有多种交互方式,目前工作中打交道数据库主要是MSSQL,Oracle,mysql等,本文主要从以上数据库介绍记录“R与数据库的连接”。
R中与数据库交互的包主要有DBI,RODBC,RMySQL,ROracle,odbc等包。DBI库在查询或上传工作中效率比RODBC高,特别数据量较大时,上传效率差异巨大,具体[差异](https://github.com/r-dbi/odbc)请点击查看详情。
即使你暂时没有用数据库,也建议你未来用数据库存储数据,尤其是当有一定数据量时;在我最开始接触数据时,数据一般保存在Excel,那时候数据量大概在50万行左右,当公式较多,尤其时需要大批量vlookup时,Excel表格将会很卡顿。
## 安装数据库
如果暂时没有数据库使用经验,如果是使用Windows系统,直接去微软官网下载安装数据库即可。如果决定用R做数据分析相关工作,尤其时商业环境下,使用数据库有较强的必要性。安装数据库后,利用数据库做数据分析的练习测试也是不错的体验。另外也可以积累ETL相关经验。
仅简单介绍 MS SQL Server 安装
- Win环境下安装
MS[下载](https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads),选择开发版或精简版(Developer、Express)其中一个版本下载即可。
![数据库下载](./picture/chap2/ms install.png)
成功下载后,按照提示一步步确认即可安装成功。另外使用`SSMS`工具,微软配套的MS SQL SERVER数据库链接工具连接数据库。至于详细的数据库配置尤其是远程连接、账户等信息请自行查阅相关资料。
- Linux环境下安装
[官网安装指南](https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-setup?view=sql-server-ver15)
以下用于 SQL Server 2019 的命令指向 Ubuntu 20.04 存储库。 如果使用的是 Ubuntu 18.04 或 16.04,请将以下路径更改为 /ubuntu/18.04/ 或 /ubuntu/16.04/,而不是 /ubuntu/20.04/。
```{bash}
# 导入公共存储库的密钥
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
# 为 SQL Server 2019 注册 Microsoft SQL Server Ubuntu 存储库
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"
# sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"
# 安装 SQL Server
sudo apt-get update
sudo apt-get install -y mssql-server
# 验证服务是否运行
systemctl status mssql-server --no-pager
```
至于其他如安装sql server 命令行工具请[查阅官网安装](https://docs.microsoft.com/zh-cn/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-linux-ver15&preserve-view=true)。
接下来我们就R语言与数据库的交互包展开介绍。
## DBI
### 安装
```{r eval=FALSE}
install.packages('DBI')
```
### 连接数据库
- 连接MS SQL SERVER
通过以下代码即可连接到服务器172.16.88.2(即IP地址)的数据库,成功连接后即可与数据库交互。
```{r}
library(DBI)
con `驱动,以下代码可连接到阿里云的MySQL数据库。
```{r eval=FALSE}
library(RMySQL)
con %
mutate(
a = y * x,
b = a ^ 2,
) %>%
show_query()
```
```{r eval=FALSE}
library(dplyr)
#connect database
con % select(year:day, dep_delay, arr_delay)
flights_db %>% filter(dep_delay > 240)
flights_db %>%
group_by(dest) %>%
summarise(delay = mean(dep_time))
```
部分简单不复杂的sql语句可以用dplyr的语法代替.
```{r eval=FALSE}
tailnum_delay_db %
group_by(tailnum) %>%
summarise(
delay = mean(arr_delay,na.rm = T),
n = n()
) %>%
arrange(desc(delay)) %>%
filter(n > 100)
tailnum_delay_db
tailnum_delay_db %>% show_query()
tailnum_delay % collect() #把数据从数据库加载到R内存中
```
### 无法正确转化
在使用过程中发现无法识别`lubridate`包的函数,但是`dbplyr`对于不认识的函数都将保留。
利用这个特性,可以使用数据库中原生的相关函数:如下所示,在Oracle中`to_date`函数
以下的自定义函数可以实现按照想要`group_by`的字段汇总金额、数量、吊牌额、折扣率等,其中关于时间周期的筛选就利用了该特性。
- date
```{r eval=FALSE}
#个人写的争对目前公司数仓写的包中获取销售数据的一段代码
get_sales_data % #DW层
select(BILL_DATE1, SKU_NO, SHOP_NO, BILL_QTY, BILL_MONEY2, PRICE) %>%
filter(between(
BILL_DATE1, to_date(start_date, "yyyy-mm-dd"),
to_date(end_date, "yyyy-mm-dd")
)) %>%
mutate(年 = year(BILL_DATE1), 月 = month(BILL_DATE1)) %>%
inner_join(store_table) %>%
inner_join(sku_table) %>%
group_by(...) %>%
summarise(
金额 = sum(BILL_MONEY2, na.rm = TRUE),
数量 = sum(BILL_QTY, na.rm = TRUE),
吊牌金额 = sum(BILL_QTY * PRICE, na.rm = TRUE)) %>%
collect() %>%
mutate(折扣率:= 金额 / 吊牌金额) %>%
arrange(...)
# return(res)
}
```
- like
```{r eval=FALSE}
mf %>%
filter(x %LIKE% "%foo%") %>%
show_query()
```
- 特殊用法
特殊情况可以使用`sql()`函数
```{r eval=FALSE}
mf %>%
transmute(factorial = sql("x!")) %>%
show_query()
```
## 参考资料
`DBI`包资料
`dbplyr`包资料
rstudio关于数据库介绍
数据库连接字符串介绍
个人博客关于Roracle的安装介绍
```{r include=FALSE, cache=FALSE}
set.seed(1014)
options(digits = 3)
knitr::opts_chunk$set(
comment = "#>",
collapse = TRUE,
# cache = TRUE,
out.width = "70%",
fig.align = 'center',
fig.width = 6,
fig.asp = 0.618, # 1 / phi
fig.show = "hold"
)
options(dplyr.print_min = 6, dplyr.print_max = 6)
```
# Loop structure
实际场景中,当需要重复做某动作时,可运用循环结构。
## 简单示例
利用循环实现1到100连续相加求和
```{r}
total 100){
print(paste0('连续相加求和等于:',total))
break
}
}
```
- while
```{r}
i
```{r include=FALSE, cache=FALSE}
set.seed(1014)
options(digits = 3)
knitr::opts_chunk$set(
comment = "#>",
collapse = TRUE,
# cache = TRUE,
out.width = "70%",
fig.align = 'center',
fig.width = 6,
fig.asp = 0.618, # 1 / phi
fig.show = "hold"
)
options(dplyr.print_min = 6, dplyr.print_max = 6)
```
# Iteration
常常需要重复操作同样的功能函数,这时可以用迭代来实现。purrr包提供了一套完整的函数来处理循环迭代,可以有效减少重复性工作和代码。
## 简单用法
- map
用map循环迭代,map函数始终返回list对象。
```{r message=FALSE,warning=FALSE}
library(tidyverse)
# define function
addTen % accumulate(`+`)
accumulate(letters[1:5], paste, sep = ".")
```
## 安全函数
possibly() 和 safely(),当循环时候遇到错误报错导致整个程序停止,这不是我们想要的。
```{r eval=FALSE}
l % flatten()
x %>% flatten_int()
# You can use flatten in conjunction with map
x %>% map(1L) %>% flatten_int()
# But it's more efficient to use the typed map instead.
x %>% map_int(1L)
```
- imap
imap()系列函数官方描述:
imap_xxx(x, ...), an indexed map, is short hand for map2(x, names(x), ...) if x has names, or map2(x, seq_along(x), ...) if it does not. This is useful if you need to compute on both the value and the position of an element.
imap,当x有names(x)或者seq_along(x)属性,imap是map2的另一种表达方式。
使用公式快捷方式时,第一个参数是值(.x),第二个参数是位置/名称(.y)。
详情请查看:?imap
示例1:
```{r}
imap_chr(sample(10), ~ paste0(.y, ": ", .x))
```
sample(10),没有names(),只有长度信息。转化成map2表达如下:
```{r}
#same above
map2_chr(sample(10),1:10,~paste0(.y,": ",.x)) # 第二个list 为位置信息.
```
```{r include=FALSE, cache=FALSE}
set.seed(1014)
options(digits = 3)
knitr::opts_chunk$set(
comment = "#>",
collapse = TRUE,
# cache = TRUE,
out.width = "70%",
fig.align = 'center',
fig.width = 6,
fig.asp = 0.618, # 1 / phi
fig.show = "hold"
)
options(dplyr.print_min = 6, dplyr.print_max = 6)
```
# define function
函数功能使我们尽可能避免复制粘贴代码,而且需要更改的时候不需要大面积修改代码仅需要调整函数参数,使代码整体更加模块化.
假设有工作任务需要给商品SKU排名,在代码中需要重复以下代码5次,当区间需要修改的时候就是灾难.
原始代码示例如下:
```{r message=FALSE,warning=FALSE}
library(tidyverse)
num 200,"200以上",'其他')))))
# same above
# case_when(num <= 50 ~ '1-50',
# num <= 100 ~ '51-100',
# num <= 150 ~ '101-150',
# num <= 200 ~ '151-200',
# num > 100 ~ '200以上'
# )
# 个人倾向data.table
# data.table::fifelse()
# data.table::fcase() 是sql中case when的实现
```
函数化后代码示例如下:
当需要修改区间时候仅仅只需要调整参数,而不必大量修改代码,当在脚本中需要调用多次时,能简洁代码.
```{r eval=FALSE}
# 排名区间函数
#library(tidyverse)
cut_function % as.character()
}
res2 TRUE
```
[参考资料](https://r4ds.had.co.nz/functions.html)
## 简单示例
给函数取一个合适名字是很难的事情,徐尽可能从函数名称看出你实现的功能.
```{r}
add_ten [1] "a, b, c, d, e, f, g, h, i, j"
rule %
show_missings() %>%
mutate(mpg = ifelse(mpg < 20, NA, mpg)) %>%
show_missings()
```
## 环境
环境是复杂的,建议阅读原文.
The last component of a function is its environment. This is not something you need to understand deeply when you first start writing functions. However, it’s important to know a little bit about environments because they are crucial to how functions work. The environment of a function controls how R finds the value associated with a name. For example, take this function:
```{r}
f
## 拓展部分
在我之前工作中遇到需要分组计算时,我想要编写一个函数实现某些功能,但是分组的group_by()字段不一样时,导致代码没办法复用。
参考资料:
```{r eval=FALSE}
#library(tidyverse)
mean_mpg = function(data, group_col) {
data %>%
group_by(group_col) %>%
summarize(mean_mpg = mean(mpg))
}
mtcars %>% mean_mpg(cyl)
mtcars %>% mean_mpg(gear)
```
当编写如下函数时,代码将成功运行
```{r}
#自定义函数
my_summarise3 %
group_by({{ group_var }}) %>%
summarise(mean = mean({{ mean_var }}), sd = mean({{ sd_var }}))
}
res1 %
group_by(cyl) %>%
summarise(mean=mean(carb),sd=mean(gear))
identical(res1,res2)
#res1 和res2 结果完全一致
```
以上my_summarise3()函数可以按照需求任意指定聚合汇总字段。