前言
dplyr作为R中必学工具包之一，其对数据的行、列处理，抽样，分组，新增，排序，筛选等操作；加之其配合上管道函数与tibble类数据框，使dplyr在语法上简洁易懂，效率上也超越一般的data.frame。
数据准备
> pacman::p_load(data.table, tidyverse) #也可以直接library
> data <- fread("D://contest//transactions.csv")
> #将数据框转换为tibble形式数据框，为dplyr函数加快处理速度
> data <- tbl_df(data)
> #查看行列信息
> names(data)
[1] "authorized_flag" "card_id" "city_id" "category_1" "installments" "category_3"
[7] "merchant_category_id" "merchant_id" "month_lag" "purchase_amount" "purchase_date" "category_2"
[13] "state_id" "subsector_id"
> dim(data)
[1] 185129 14
#185129行，14列
> data
# A tibble: 185,129 x 14
authorized_flag card_id city_id category_1 installments category_3 merchant_category~ merchant_id month_lag purchase_amount purchase_date category_2 state_id subsector_id
<chr> <chr> <int> <chr> <int> <chr> <int> <chr> <int> <dbl> <chr> <int> <int> <int>
1 Y C_ID_cb34e~ 20 N 0 A 422 M_ID_f162748~ 1 -0.660 2018-03-07 10:5~ 3 19 27
2 Y C_ID_8a118~ 251 N 0 A 278 M_ID_00c57ea~ 2 -0.624 2017-07-21 12:1~ 3 8 37
3 Y C_ID_69ed7~ 117 N 0 A 367 M_ID_1da88fb~ 2 -0.714 2017-10-27 20:2~ 4 13 16
4 Y C_ID_ae6f8~ 69 N 1 B 383 M_ID_ebbdb42~ 2 -0.446 2018-01-26 08:4~ 1 9 2
5 Y C_ID_07b21~ 277 N 0 A 278 M_ID_7291e8a~ 1 -0.736 2018-03-10 21:4~ 4 13 37
6 Y C_ID_34521~ 291 N 0 A 422 M_ID_0649e6e~ 2 -0.702 2018-03-11 19:3~ 1 9 27
7 Y C_ID_5d09f~ 69 N 0 A 511 M_ID_b794b9d~ 2 -0.183 2018-04-21 13:1~ 1 9 7
8 Y C_ID_a12f5~ 331 N 0 A 273 M_ID_73d19e5~ 1 -0.720 2018-03-21 06:5~ 3 3 20
9 Y C_ID_88fb2~ 69 N 0 A 19 M_ID_a79f97e~ 1 -0.726 2018-03-01 16:5~ 1 9 36
10 Y C_ID_8ef5e~ 261 N 1 B 823 M_ID_2059928~ 2 -0.509 2018-03-04 03:5~ 1 9 25
# ... with 185,119 more rows
我们以一个kaggle上的信用卡消费数据集为例
百度网盘下载：
链接：https://pan.baidu.com/s/1Qv3nAJxfo7hxjdTOGoLOXA
提取码：f4ks
行操作 - 对于记录操作
1
筛选 - filter
#行筛选 - 且的关系
data %>%
filter(purchase_amount > 0, category_2 == 3)
#行筛选 - 或的关系
data %>%
filter(purchase_amount > 0 | category_2 == 3)
#对字符串精确筛选
data %>%
filter(card_id %in% c("C_ID_cb34e13a1e", "C_ID_8a1184a21e"))
#根据行号范围提取
data %>%
slice(101:500)
#选取purchase_amount值最大的50个记录
data %>%
top_n(50, purchase_amount)
2
排序 - arrange
#根据purchase_amount倒序排序后，按city_id升序排序
data %>%
arrange(desc(purchase_amount), city_id)
3
去重 - distinct
#返回去重后的card_id列
data %>%
distinct(card_id)
4
抽样 - sample
#随机抽样50%的样本 - 默认无放回
data %>%
sample_frac(0.5, replace = T)
#随机抽样500个样本 - 默认无放回
data %>%
sample_n(500)
列操作 - 对于变量操作
1
提取 - select
#提取card_id，city_id列
data %>%
select(card_id, city_id)
#提取列名以“c”为开头的列
data %>%
select(starts_with("c"))
#提取列名以“d”结尾的列
data %>%
select(ends_with("d"))
#提取列名包含“e“的列
data %>%
select(matches(".e."))
#提取数据类型为numeric的列
data %>%
select_if(is.numeric)
2
新增 - mutate
#新增列inst_pay,赋值为(purchase_amount / installments)
data %>%
mutate(inst_pay = purchase_amount / installments)
#新增列inst_pay,赋值为(purchase_amount / installments)后删除原有列
data %>%
transmute(inst_pay = purchase_amount / installments)
#新增列new，赋值为1~data行数
data %>%
add_column(new = 1:nrow(data))
3
重命名 - rename
#重命名installments为inst
data %>%
rename(inst = installments)
分组计算 - group_by
#根据card_id分组，计算每组下purchase_amount之和，赋值到sum_amount列
data %>%
group_by(card_id) %>%
summarise(sum_amount = sum(purchase_amount))
# A tibble: 116,845 x 2
card_id sum_amount
<chr> <dbl>
1 C_ID_0001238066 -1.16
2 C_ID_0001793786 2.56
3 C_ID_000183fdda -1.86
4 C_ID_0002c7c2c1 -0.732
5 C_ID_0003be3c83 -0.735
6 C_ID_0004587331 -0.740
7 C_ID_0004c2a5ab -0.643
8 C_ID_000599daf9 -0.643
9 C_ID_0005f16cc8 -0.690
10 C_ID_000616f4a8 -0.539
# ... with 116,835 more rows
#也可以根据多个变量分组，上述代码释义为：每个card_id在每个city_id下purchase_amount的合计
data %>%
group_by(card_id, city_id) %>%
summarise(sum_amount = sum(purchase_amount))
# A tibble: 144,684 x 3
# Groups: card_id [116,845]
card_id city_id sum_amount
<chr> <int> <dbl>
1 C_ID_0001238066 248 -0.597
2 C_ID_0001238066 314 -0.567
3 C_ID_0001793786 69 2.20
4 C_ID_0001793786 87 1.51
5 C_ID_0001793786 96 -0.555
6 C_ID_0001793786 204 -0.592
7 C_ID_000183fdda 25 -0.539
8 C_ID_000183fdda 161 -1.32
9 C_ID_0002c7c2c1 213 -0.732
10 C_ID_0003be3c83 279 -0.735
# ... with 144,674 more rows
数据框横向连接 - join
#数据准备
data %>%
select(card_id, city_id) %>%
.[1:500, ] -> data_x
#提取列card_id, city_id后，取前500行记录赋值到data_x
data %>%
select(card_id, purchase_amount) %>%
.[1:1000, ] -> data_y
#提取列card_id, purchase_amount后，取前1000行记录赋值到data_y
现在我们得到了两个行数不同的数据框，共同列为card_id，不同列为city_id与purchase_amount
#左联结left_join：根据card_id列，连接x，y，保留x全记录
test_left <- left_join(data_x, data_y, by = "card_id")
> test_left
# A tibble: 505 x 3
card_id city_id purchase_amount
<chr> <int> <dbl>
1 C_ID_cb34e13a1e 20 -0.660
2 C_ID_8a1184a21e 251 -0.624
3 C_ID_69ed7c9177 117 -0.714
4 C_ID_69ed7c9177 117 -0.702
5 C_ID_ae6f8dd403 69 -0.446
6 C_ID_07b2107875 277 -0.736
7 C_ID_34521fce1b 291 -0.702
8 C_ID_5d09feb8b0 69 -0.183
9 C_ID_a12f57ce14 331 -0.720
10 C_ID_88fb23a843 69 -0.726
# ... with 495 more rows
#内连接inner_join：根据card_id列，连接x，y，保留x与y同时拥有的记录
test_inner <- inner_join(data_x, data_y, by = "card_id")
> test_inner
# A tibble: 505 x 3
card_id city_id purchase_amount
<chr> <int> <dbl>
1 C_ID_cb34e13a1e 20 -0.660
2 C_ID_8a1184a21e 251 -0.624
3 C_ID_69ed7c9177 117 -0.714
4 C_ID_69ed7c9177 117 -0.702
5 C_ID_ae6f8dd403 69 -0.446
6 C_ID_07b2107875 277 -0.736
7 C_ID_34521fce1b 291 -0.702
8 C_ID_5d09feb8b0 69 -0.183
9 C_ID_a12f57ce14 331 -0.720
10 C_ID_88fb23a843 69 -0.726
# ... with 495 more rows
#反连接anti_join：根据card_id，连接x，y，保留左边不与右边匹配的记录
test_anti <- anti_join(data_y, data_x, by = "card_id")
> test_anti
# A tibble: 497 x 2
card_id purchase_amount
<chr> <dbl>
1 C_ID_91eb92b791 -0.735
2 C_ID_e2c55a4727 -0.655
3 C_ID_40b11c0239 -0.680
4 C_ID_8ec1940500 -0.552
5 C_ID_c2c6444bfd -0.664
6 C_ID_44857dc5b4 -0.684
7 C_ID_e768b799be -0.732
8 C_ID_01d264dd43 -0.589
9 C_ID_7910e91f72 -0.657
10 C_ID_79a80f833c -0.705
# ... with 487 more rows
#全连接full_join：根据card_id列，连接x，y，保留x，y中所有记录
test_full <- full_join(data_x, data_y, by = "card_id")
> test_full
# A tibble: 1,002 x 3
card_id city_id purchase_amount
<chr> <int> <dbl>
1 C_ID_cb34e13a1e 20 -0.660
2 C_ID_8a1184a21e 251 -0.624
3 C_ID_69ed7c9177 117 -0.714
4 C_ID_69ed7c9177 117 -0.702
5 C_ID_ae6f8dd403 69 -0.446
6 C_ID_07b2107875 277 -0.736
7 C_ID_34521fce1b 291 -0.702
8 C_ID_5d09feb8b0 69 -0.183
9 C_ID_a12f57ce14 331 -0.720
10 C_ID_88fb23a843 69 -0.726
数据框纵向拼接 - bind_rows
data %>%
sample_n(500) -> data_3 #随机抽样500行记录
data %>%
sample_n(500) -> data_4
test_bind <- bind_rows(data_3, data_4)
> test_bind
# A tibble: 1,000 x 14
authorized_flag card_id city_id category_1 installments category_3 merchant_category~ merchant_id month_lag purchase_amount purchase_date category_2 state_id subsector_id
<chr> <chr> <int> <chr> <int> <chr> <int> <chr> <int> <dbl> <chr> <int> <int> <int>
1 Y C_ID_d5b79~ 138 N 1 B 307 "M_ID_421de74~ 1 -0.597 2018-03-14 06:~ 1 15 19
2 Y C_ID_abe51~ 124 N 1 B 705 "M_ID_9fdd0d2~ 2 -0.687 2018-01-15 11:~ 2 24 33
3 Y C_ID_6b2ef~ 19 N 0 A 45 "M_ID_1799f5b~ 2 -0.446 2018-04-25 16:~ 1 9 18
4 Y C_ID_6f4df~ 19 N 1 B 367 "M_ID_f68f141~ 1 -0.597 2018-03-30 15:~ 1 9 16
5 Y C_ID_50c96~ 197 N 0 A 363 "M_ID_5aebc7e~ 2 -0.609 2018-04-05 08:~ 1 9 18
6 Y C_ID_d3de5~ 130 N 0 A 222 "" 1 -0.672 2018-03-10 10:~ 3 7 21
7 Y C_ID_451d0~ 143 N 1 B 278 "M_ID_41fb6e8~ 2 -0.726 2018-04-06 09:~ 5 5 37
8 Y C_ID_09730~ 117 N 1 B 398 "M_ID_2ff192b~ 2 -0.588 2017-09-02 22:~ 1 9 17
9 Y C_ID_20b85~ 200 N 0 A 307 "M_ID_2f3593c~ 1 -0.717 2018-03-19 11:~ 2 18 19
10 Y C_ID_53d5c~ 248 N 0 A 705 "M_ID_8bf1607~ 1 -0.732 2018-03-15 17:~ 1 15 33
# ... with 990 more rows
#注意：这里要保持两数据框的列名、数据类型一致
最后
相信写到这里大家已经基本理解了dplyr包的功能与用法，数据清洗作为特征工程中重要的一环，熟练掌握数据表内的增删查改功能才能为数据分析提供最基本的支撑。
·END·
R语言与数据分析
生产力干货
230
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
