การ Join ตารางกับตัวเอง
การ Join ข้อมูลด้วย dplyr
Chris Cardillo
Data Scientist
ตาราง themes
themes
# A tibble: 665 x 3
id name parent_id
<dbl> <chr> <dbl>
1 1 Technic NA
2 2 Arctic Technic 1
3 3 Competition 1
4 4 Expert Builder 1
5 5 Model 1
6 6 Airport 5
7 7 Construction 5
8 8 Farm 5
9 9 Fire 5
10 10 Harbor 5
# … with 655 more rows
ลำดับชั้นของธีม
ตารางความสัมพันธ์ child-parent
themes %>%
inner_join(themes, by = c("parent_id" = "id"))
# A tibble: 544 x 5
id name.x parent_id name.y parent_id.y
<dbl> <chr> <dbl> <chr> <dbl>
1 2 Arctic Technic 1 Technic NA
2 3 Competition 1 Technic NA
3 4 Expert Builder 1 Technic NA
4 5 Model 1 Technic NA
5 6 Airport 5 Model 1
6 7 Construction 5 Model 1
7 8 Farm 5 Model 1
8 9 Fire 5 Model 1
9 10 Harbor 5 Model 1
10 11 Off-Road 5 Model 1
# … with 534 more rows
การเพิ่ม suffix
themes %>%
inner_join(themes, by = c("parent_id" = "id"), suffix = c("_child", "_parent"))
# A tibble: 544 x 5
id name_child parent_id name_parent parent_id_parent
<dbl> <chr> <dbl> <chr> <dbl>
1 2 Arctic Technic 1 Technic NA
2 3 Competition 1 Technic NA
3 4 Expert Builder 1 Technic NA
4 5 Model 1 Technic NA
5 6 Airport 5 Model 1
6 7 Construction 5 Model 1
7 8 Farm 5 Model 1
8 9 Fire 5 Model 1
9 10 Harbor 5 Model 1
10 11 Off-Road 5 Model 1
# … with 534 more rows
ธีม Lord of the Rings: parent
themes %>%
inner_join(themes, by = c("parent_id" = "id"), suffix = c("_child", "_parent")) %>%
filter(name_child == "The Lord of the Rings")
# A tibble: 1 x 5
id name_child parent_id name_parent parent_id_parent
<dbl> <chr> <dbl> <chr> <dbl>
1 566 The Lord of the Rings 561 The Hobbit and Lord of the Rings NA
ธีม Lord of the Rings: children
themes %>%
inner_join(themes, by = c("parent_id" = "id"), suffix = c("_child", "_parent")) %>%
filter(name_parent == "The Lord of the Rings")
# A tibble: 3 x 5
id name_child parent_id name_parent parent_id_parent
<dbl> <chr> <dbl> <chr> <dbl>
1 567 The Fellowship of the Ring 566 The Lord of the Rings 561
2 568 The Two Towers 566 The Lord of the Rings 561
3 569 The Return of the King 566 The Lord of the Rings 561
ไตรภาค The Lord of the Rings
มาฝึกกันเถอะ!
การ Join ข้อมูลด้วย dplyr
