Menggabungkan Beberapa Tabel

Menggabungkan Beberapa Tabel dengan dplyr di R

Sebelum jalankan perintah SQL pada R terlebih dahulu lakukan peng-install-an packages jika baru pertama kali penggunakan packages berikut:

Install Packages

#terlebih dahulu install terhadap packages berikut, untuk menjalankan program yang akan dibuat. Peng-install-an dilakukan tanpa menggunakan tagar (#)
#install.packages("tidyverse")  
#install.packages("RSQLite")    
#install.packages("DBI")    

#Selanjutnya panggil packages tersebut
library(tidyverse)

## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --

## v ggplot2 3.3.5     v purrr   0.3.4
## v tibble  3.1.3     v dplyr   1.0.7
## v tidyr   1.1.3     v stringr 1.4.0
## v readr   2.0.1     v forcats 0.5.1

## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag()    masks stats::lag()

library(RSQLite)
library(DBI)

Import Data

Setelah melakukan peng-install-an dan pemanggilan packages, selanjutnya import data yang diinginkan.

Northwind<-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/db/Northwind_large.sqlite")
class(Northwind)

## [1] "SQLiteConnection"
## attr(,"package")
## [1] "RSQLite"

Selanjutnya, gunakan fungsi dbListTables untuk melihat tables yang terdapat di dalam database.

dbListTables(Northwind)

##  [1] "Category"             "Customer"             "CustomerCustomerDemo"
##  [4] "CustomerDemographic"  "Employee"             "EmployeeTerritory"   
##  [7] "Order"                "OrderDetail"          "Product"             
## [10] "ProductDetails_V"     "Region"               "Shipper"             
## [13] "Supplier"             "Territory"

Inner Join

Inner join digunakan untuk menghasilkan semua baris pada table x yang memiliki kesamaan nilai dengan table y, dan semua kolom dari x dan y.

#cara 1
inner_join(band_members,band_instruments)

## Joining, by = "name"

## # A tibble: 2 x 3
##   name  band    plays 
##   <chr> <chr>   <chr> 
## 1 John  Beatles guitar
## 2 Paul  Beatles bass

#cara 2
band_members %>% 
  inner_join(band_instruments)

## Joining, by = "name"

## # A tibble: 2 x 3
##   name  band    plays 
##   <chr> <chr>   <chr> 
## 1 John  Beatles guitar
## 2 Paul  Beatles bass

Left Join

Left join digunakan untuk menghasilkan semua baris pada table x, dan semua kolom pada x dan y, dimana untuk baris pada x yang tidak memiliki kesamaan dengan y diisi dengan nilai NA pada kolom yang baru.

#cara 1
left_join(band_members,band_instruments)

## Joining, by = "name"

## # A tibble: 3 x 3
##   name  band    plays 
##   <chr> <chr>   <chr> 
## 1 Mick  Stones  <NA>  
## 2 John  Beatles guitar
## 3 Paul  Beatles bass

#cara 2
band_members %>% 
  left_join(band_instruments)

## Joining, by = "name"

## # A tibble: 3 x 3
##   name  band    plays 
##   <chr> <chr>   <chr> 
## 1 Mick  Stones  <NA>  
## 2 John  Beatles guitar
## 3 Paul  Beatles bass

Right Join

Right join digunakan untuk menghasilkan semua baris pada table y, dan semua kolom pada x dan y, dimana untuk baris pada y yang tidak memiliki kesamaan dengan x akan diisi dengan nilai NA pada kolom yang baru.

#cara 1
right_join(band_members,band_instruments)

## Joining, by = "name"

## # A tibble: 3 x 3
##   name  band    plays 
##   <chr> <chr>   <chr> 
## 1 John  Beatles guitar
## 2 Paul  Beatles bass  
## 3 Keith <NA>    guitar

#cara 2
band_members %>% 
  right_join(band_instruments)

## Joining, by = "name"

## # A tibble: 3 x 3
##   name  band    plays 
##   <chr> <chr>   <chr> 
## 1 John  Beatles guitar
## 2 Paul  Beatles bass  
## 3 Keith <NA>    guitar

Full Join

Full join digunakan untuk menghasilkan semua baris dan kolom dari x dan y. Jika ada nilai yang tidak sama (match) antara x dan y, maka akan bernilai NA.

#cara 1
full_join(band_members,band_instruments)

## Joining, by = "name"

## # A tibble: 4 x 3
##   name  band    plays 
##   <chr> <chr>   <chr> 
## 1 Mick  Stones  <NA>  
## 2 John  Beatles guitar
## 3 Paul  Beatles bass  
## 4 Keith <NA>    guitar

#cara 2
band_members %>% 
  full_join(band_instruments)

## Joining, by = "name"

## # A tibble: 4 x 3
##   name  band    plays 
##   <chr> <chr>   <chr> 
## 1 Mick  Stones  <NA>  
## 2 John  Beatles guitar
## 3 Paul  Beatles bass  
## 4 Keith <NA>    guitar

Semi Join

Semi join digunakan untuk menghasilkan semua baris pada table x yang memiliki kesamaan nilai dengan table y, dan semua kolom dari x. Ini mirip dengan inner_join(), bedanya inner_join() mengembalikan semua kolom dari x dan y. Untuk melakukan semi join terdapat dua cara yaitu

#cara 1
semi_join(band_members,band_instruments)

## Joining, by = "name"

## # A tibble: 2 x 2
##   name  band   
##   <chr> <chr>  
## 1 John  Beatles
## 2 Paul  Beatles

#cara 2
band_members %>% 
  semi_join(band_instruments)

## Joining, by = "name"

## # A tibble: 2 x 2
##   name  band   
##   <chr> <chr>  
## 1 John  Beatles
## 2 Paul  Beatles

Anti Join

Anti join digunakan untuk menghasilkan semua baris dari x yang TIDAK memiliki kesamaan dengan y, dan semua kolom yang berasal dari x.

#cara 1
anti_join(band_members,band_instruments)

## Joining, by = "name"

## # A tibble: 1 x 2
##   name  band  
##   <chr> <chr> 
## 1 Mick  Stones

#cara 2
band_members %>% 
  anti_join(band_instruments)

## Joining, by = "name"

## # A tibble: 1 x 2
##   name  band  
##   <chr> <chr> 
## 1 Mick  Stones

Menggabungkan Beberapa Tabel dengan SQL

Package rmarkdown memfasilitasi penggunaan SQL engine, untuk mengeksekusi syntax SQL dan menampilkan hasilnya.Untuk menggunakannya, kita harus membuat connection terlebih dahulu yang digunakan secara bersamaan dengan engine sql.Berikut ini adalah proses membentuk suatu object db yang merupakan class SQLiteConnection

db <- DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/db/chinook.db")
class(db)

## [1] "SQLiteConnection"
## attr(,"package")
## [1] "RSQLite"

Object db ini digunakan pada opsi connection, kemudian pada opsi engine digunakan “sql”. Sehingga di dalamnya kita bisa menulis syntax SQL.

Displaying records 1 - 10
TrackId	Name	AlbumId	MediaTypeId	GenreId	Composer	Milliseconds	Bytes	UnitPrice
1	For Those About To Rock (We Salute You)	1	1	1	Angus Young, Malcolm Young, Brian Johnson	343719	11170334	0.99
2	Balls to the Wall	2	2	1	NA	342562	5510424	0.99
3	Fast As a Shark	3	2	1	F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman	230619	3990994	0.99
4	Restless and Wild	3	2	1	F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman	252051	4331779	0.99
5	Princess of the Dawn	3	2	1	Deaffy & R.A. Smith-Diesel	375418	6290521	0.99
6	Put The Finger On You	1	1	1	Angus Young, Malcolm Young, Brian Johnson	205662	6713451	0.99
7	Let’s Get It Up	1	1	1	Angus Young, Malcolm Young, Brian Johnson	233926	7636561	0.99
8	Inject The Venom	1	1	1	Angus Young, Malcolm Young, Brian Johnson	210834	6852860	0.99
9	Snowballed	1	1	1	Angus Young, Malcolm Young, Brian Johnson	203102	6599424	0.99
10	Evil Walks	1	1	1	Angus Young, Malcolm Young, Brian Johnson	263497	8611245	0.99

Pada bagian ini, kita akan menggabungkan tabel dari database chinook.db. Tabel yang akan digabung yaitu tabel artist dan tabel albumn.

SQLite Inner Join

Syntax berikut menampilkan kolom Title dari tabel albums dan name dari tabel artists. Dan menggabungkan kolom ArtisId yang ada pada tabel artist dan albums.

SELECT 
  Title,
  Name
FROM
  albums
INNER JOIN 
  artists 
ON artists.ArtistId = albums.ArtistId;

Displaying records 1 - 10
Title	Name
For Those About To Rock We Salute You	AC/DC
Balls to the Wall	Accept
Restless and Wild	Accept
Let There Be Rock	AC/DC
Big Ones	Aerosmith
Jagged Little Pill	Alanis Morissette
Facelift	Alice In Chains
Warner 25 Anos	Antônio Carlos Jobim
Plays Metallica By Four Cellos	Apocalyptica
Audioslave	Audioslave

Karena nama kolom ArtistID sama antara tabel artists dan albums maka Anda bisa menggunakan USING

SELECT
  Title,
  Name
FROM
  albums
INNER JOIN
  artists
USING(ArtistId);

Displaying records 1 - 10
Title	Name
For Those About To Rock We Salute You	AC/DC
Balls to the Wall	Accept
Restless and Wild	Accept
Let There Be Rock	AC/DC
Big Ones	Aerosmith
Jagged Little Pill	Alanis Morissette
Facelift	Alice In Chains
Warner 25 Anos	Antônio Carlos Jobim
Plays Metallica By Four Cellos	Apocalyptica
Audioslave	Audioslave

Contoh Inner Join untuk 3 tabel

SELECT
    trackid,
    tracks.name AS track,
    albums.title AS album,
    artists.name AS artist
FROM
    tracks
    INNER JOIN albums ON albums.albumid = tracks.albumid
    INNER JOIN artists ON artists.artistid = albums.artistid;

Displaying records 1 - 10
TrackId	track	album	artist
1	For Those About To Rock (We Salute You)	For Those About To Rock We Salute You	AC/DC
6	Put The Finger On You	For Those About To Rock We Salute You	AC/DC
7	Let’s Get It Up	For Those About To Rock We Salute You	AC/DC
8	Inject The Venom	For Those About To Rock We Salute You	AC/DC
9	Snowballed	For Those About To Rock We Salute You	AC/DC
10	Evil Walks	For Those About To Rock We Salute You	AC/DC
11	C.O.D.	For Those About To Rock We Salute You	AC/DC
12	Breaking The Rules	For Those About To Rock We Salute You	AC/DC
13	Night Of The Long Knives	For Those About To Rock We Salute You	AC/DC
14	Spellbound	For Those About To Rock We Salute You	AC/DC

SQLite Left Join

Syntax berikut menampilkan kolom nama dan title dengan menampilkan semua data pada kolom ArtistId yang ada pada tabel artists dan data pada kolom ArtistId yang sama dengan tabel artist yang terdapat pada tabel albums.

SELECT Name,Title
FROM artists LEFT JOIN albums
USING (ArtistId)
ORDER BY Name;

Displaying records 1 - 10
Name	Title
A Cor Do Som	NA
AC/DC	For Those About To Rock We Salute You
AC/DC	Let There Be Rock
Aaron Copland & London Symphony Orchestra	A Copland Celebration, Vol. I
Aaron Goldberg	Worlds
Academy of St. Martin in the Fields & Sir Neville Marriner	The World of Classical Favourites
Academy of St. Martin in the Fields Chamber Ensemble & Sir Neville Marriner	Sir Neville Marriner: A Celebration
Academy of St. Martin in the Fields, John Birch, Sir Neville Marriner & Sylvia McNair	Fauré: Requiem, Ravel: Pavane & Others
Academy of St. Martin in the Fields, Sir Neville Marriner & Thurston Dart	Bach: Orchestral Suites Nos. 1 - 4
Academy of St. Martin in the Fields, Sir Neville Marriner & William Bennett	NA

Demikianlah cara untuk menggabungkan tabel pada R dengan menggunakan package dplyr dan SQLite jika menggunakan rmarkdown.