Tugas Akhir Mandarel
Engine SQL
Database yang digunakan adalah database northwindSource . Untuk mengakses dan mengolah database ini melalui R diperlukan package tidyverse, DBI , danRSQLite.
Melakukan Instalasi Package
install.packages(c("RSQLite", "DBI"), dependencies = TRUE)
install.packages("tidyverse")Memanggil Library
Selanjutnya package tidyverse,RSQLite, dan DBI dipanggil .
library(tidyverse)
library(RSQLite)
library(DBI)Mengoneksikan Database
Koneksi terhadap database menggunakan drive DBMS SQLite dapat digunakan pada R dengan menggunakan sintaks berikut: DBI::dbConnect(RSQLite::SQLite(), path = ":dbname:")
northwind<-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/northwind_large.sqlite")Menggunakan Query SQL pada Database
Setelah melakukan dbConnect(), baru kita dapat melakukan perintah SQL pada RStudio.
SELECT
*
FROM
PRODUCT
WHERE
SupplierId ="1";| Id | ProductName | SupplierId | CategoryId | QuantityPerUnit | UnitPrice | UnitsInStock | UnitsOnOrder | ReorderLevel | Discontinued |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Chai | 1 | 1 | 10 boxes x 20 bags | 18 | 39 | 0 | 10 | 0 |
| 2 | Chang | 1 | 1 | 24 - 12 oz bottles | 19 | 17 | 40 | 25 | 0 |
| 3 | Aniseed Syrup | 1 | 2 | 12 - 550 ml bottles | 10 | 13 | 70 | 25 | 0 |
Memutus Koneksi Database
Jika sudah selesai mengolah database tersebut dan ingin mengolah database lain gunakan dbDisconnect() .Jika ingin menyambungkan kembali cukup menggunakan kembali dbConnect().
dbDisconnect(northwind)Menggunakan dplyr
northwind<-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/northwind_large.sqlite")Melihat Daftar Tabel dalam Database
RSQLite::dbListTables(northwind)## [1] "Category" "Customer" "CustomerCustomerDemo"
## [4] "CustomerDemographic" "Employee" "EmployeeTerritory"
## [7] "Order" "OrderDetail" "Product"
## [10] "ProductDetails_V" "Region" "Shipper"
## [13] "Supplier" "Territory"Memilih Tabel dalam Database
product <- dplyr::tbl(northwind,"Product")
product## # Source: table<Product> [?? x 10]
## # Database: sqlite 3.36.0 [C:\sqlite\northwind_large.sqlite]
## Id ProductName SupplierId CategoryId QuantityPerUnit UnitPrice
## <int> <chr> <int> <int> <chr> <dbl>
## 1 1 Chai 1 1 10 boxes x 20 ba~ 18
## 2 2 Chang 1 1 24 - 12 oz bottl~ 19
## 3 3 Aniseed Syrup 1 2 12 - 550 ml bott~ 10
## 4 4 Chef Anton's Cajun S~ 2 2 48 - 6 oz jars 22
## 5 5 Chef Anton's Gumbo M~ 2 2 36 boxes 21.4
## 6 6 Grandma's Boysenberr~ 3 2 12 - 8 oz jars 25
## 7 7 Uncle Bob's Organic ~ 3 7 12 - 1 lb pkgs. 30
## 8 8 Northwoods Cranberry~ 3 2 12 - 12 oz jars 40
## 9 9 Mishi Kobe Niku 4 6 18 - 500 g pkgs. 97
## 10 10 Ikura 4 8 12 - 200 ml jars 31
## # ... with more rows, and 4 more variables: UnitsInStock <int>,
## # UnitsOnOrder <int>, ReorderLevel <int>, Discontinued <int>Melihat Kelas dari Tabel
class(product)## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"Melakukan Wrangling pada Tabel
Sintaks berikut akan menyeleksi tabel dengan membuang kolom Discontinued dan ReorderLevel. Selanjutnya tabel difilter dan hanya menampilkan data dengan UnitPrice > 20. Ditambahkan peubah baru Price yang merupakan hasil kali dari UnitsOnOrder dan UnitPrice.
query <- product %>% select(-Discontinued,-ReorderLevel) %>% filter(UnitPrice>20)%>% mutate(Price=UnitsOnOrder*UnitPrice)
query ## # Source: lazy query [?? x 9]
## # Database: sqlite 3.36.0 [C:\sqlite\northwind_large.sqlite]
## Id ProductName SupplierId CategoryId QuantityPerUnit UnitPrice
## <int> <chr> <int> <int> <chr> <dbl>
## 1 4 Chef Anton's Cajun Sea~ 2 2 48 - 6 oz jars 22
## 2 5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.4
## 3 6 Grandma's Boysenberry ~ 3 2 12 - 8 oz jars 25
## 4 7 Uncle Bob's Organic Dr~ 3 7 12 - 1 lb pkgs. 30
## 5 8 Northwoods Cranberry S~ 3 2 12 - 12 oz jars 40
## 6 9 Mishi Kobe Niku 4 6 18 - 500 g pkg~ 97
## 7 10 Ikura 4 8 12 - 200 ml ja~ 31
## 8 11 Queso Cabrales 5 4 1 kg pkg. 21
## 9 12 Queso Manchego La Past~ 5 4 10 - 500 g pkg~ 38
## 10 14 Tofu 6 7 40 - 100 g pkg~ 23.2
## # ... with more rows, and 3 more variables: UnitsInStock <int>,
## # UnitsOnOrder <int>, Price <dbl>Melihat Query SQL untuk Menampilkan Tabel yang Telah Diubah
dplyr::show_query(query)## <SQL>
## SELECT `Id`, `ProductName`, `SupplierId`, `CategoryId`, `QuantityPerUnit`, `UnitPrice`, `UnitsInStock`, `UnitsOnOrder`, `UnitsOnOrder` * `UnitPrice` AS `Price`
## FROM (SELECT `Id`, `ProductName`, `SupplierId`, `CategoryId`, `QuantityPerUnit`, `UnitPrice`, `UnitsInStock`, `UnitsOnOrder`
## FROM `Product`)
## WHERE (`UnitPrice` > 20.0)Data Wrangling
Dataset yang digunakan adalah trees yang tersedia pada package default R datasets.
Memanggil Library Dataset
library(datasets)
trees## Girth Height Volume
## 1 8.3 70 10.3
## 2 8.6 65 10.3
## 3 8.8 63 10.2
## 4 10.5 72 16.4
## 5 10.7 81 18.8
## 6 10.8 83 19.7
## 7 11.0 66 15.6
## 8 11.0 75 18.2
## 9 11.1 80 22.6
## 10 11.2 75 19.9
## 11 11.3 79 24.2
## 12 11.4 76 21.0
## 13 11.4 76 21.4
## 14 11.7 69 21.3
## 15 12.0 75 19.1
## 16 12.9 74 22.2
## 17 12.9 85 33.8
## 18 13.3 86 27.4
## 19 13.7 71 25.7
## 20 13.8 64 24.9
## 21 14.0 78 34.5
## 22 14.2 80 31.7
## 23 14.5 74 36.3
## 24 16.0 72 38.3
## 25 16.3 77 42.6
## 26 17.3 81 55.4
## 27 17.5 82 55.7
## 28 17.9 80 58.3
## 29 18.0 80 51.5
## 30 18.0 80 51.0
## 31 20.6 87 77.0Dataset trees berisi data pengukuran diameter, tinggi, dan volume kayu 31 pohon sakura hitam yang ditebang. Untuk mengetahui informasi terkait dataset dapat menggunakan sintaks ?sebelum nama dataset .
Melihat Informasi Spesifik Dataset
?trees
View(trees)Dataset tersebut akan dilakukan perintah arrange , mutate ,filter,select, dan summarise.
Menyeleksi Data
trees %>% select(Height,Volume)## Height Volume
## 1 70 10.3
## 2 65 10.3
## 3 63 10.2
## 4 72 16.4
## 5 81 18.8
## 6 83 19.7
## 7 66 15.6
## 8 75 18.2
## 9 80 22.6
## 10 75 19.9
## 11 79 24.2
## 12 76 21.0
## 13 76 21.4
## 14 69 21.3
## 15 75 19.1
## 16 74 22.2
## 17 85 33.8
## 18 86 27.4
## 19 71 25.7
## 20 64 24.9
## 21 78 34.5
## 22 80 31.7
## 23 74 36.3
## 24 72 38.3
## 25 77 42.6
## 26 81 55.4
## 27 82 55.7
## 28 80 58.3
## 29 80 51.5
## 30 80 51.0
## 31 87 77.0trees## Girth Height Volume
## 1 8.3 70 10.3
## 2 8.6 65 10.3
## 3 8.8 63 10.2
## 4 10.5 72 16.4
## 5 10.7 81 18.8
## 6 10.8 83 19.7
## 7 11.0 66 15.6
## 8 11.0 75 18.2
## 9 11.1 80 22.6
## 10 11.2 75 19.9
## 11 11.3 79 24.2
## 12 11.4 76 21.0
## 13 11.4 76 21.4
## 14 11.7 69 21.3
## 15 12.0 75 19.1
## 16 12.9 74 22.2
## 17 12.9 85 33.8
## 18 13.3 86 27.4
## 19 13.7 71 25.7
## 20 13.8 64 24.9
## 21 14.0 78 34.5
## 22 14.2 80 31.7
## 23 14.5 74 36.3
## 24 16.0 72 38.3
## 25 16.3 77 42.6
## 26 17.3 81 55.4
## 27 17.5 82 55.7
## 28 17.9 80 58.3
## 29 18.0 80 51.5
## 30 18.0 80 51.0
## 31 20.6 87 77.0Mengurutkan Data
Sintaks dibawah akan mengurutkan data secara descending (besar ke kecil)
trees %>% arrange(desc(Height))## Girth Height Volume
## 1 20.6 87 77.0
## 2 13.3 86 27.4
## 3 12.9 85 33.8
## 4 10.8 83 19.7
## 5 17.5 82 55.7
## 6 10.7 81 18.8
## 7 17.3 81 55.4
## 8 11.1 80 22.6
## 9 14.2 80 31.7
## 10 17.9 80 58.3
## 11 18.0 80 51.5
## 12 18.0 80 51.0
## 13 11.3 79 24.2
## 14 14.0 78 34.5
## 15 16.3 77 42.6
## 16 11.4 76 21.0
## 17 11.4 76 21.4
## 18 11.0 75 18.2
## 19 11.2 75 19.9
## 20 12.0 75 19.1
## 21 12.9 74 22.2
## 22 14.5 74 36.3
## 23 10.5 72 16.4
## 24 16.0 72 38.3
## 25 13.7 71 25.7
## 26 8.3 70 10.3
## 27 11.7 69 21.3
## 28 11.0 66 15.6
## 29 8.6 65 10.3
## 30 13.8 64 24.9
## 31 8.8 63 10.2trees## Girth Height Volume
## 1 8.3 70 10.3
## 2 8.6 65 10.3
## 3 8.8 63 10.2
## 4 10.5 72 16.4
## 5 10.7 81 18.8
## 6 10.8 83 19.7
## 7 11.0 66 15.6
## 8 11.0 75 18.2
## 9 11.1 80 22.6
## 10 11.2 75 19.9
## 11 11.3 79 24.2
## 12 11.4 76 21.0
## 13 11.4 76 21.4
## 14 11.7 69 21.3
## 15 12.0 75 19.1
## 16 12.9 74 22.2
## 17 12.9 85 33.8
## 18 13.3 86 27.4
## 19 13.7 71 25.7
## 20 13.8 64 24.9
## 21 14.0 78 34.5
## 22 14.2 80 31.7
## 23 14.5 74 36.3
## 24 16.0 72 38.3
## 25 16.3 77 42.6
## 26 17.3 81 55.4
## 27 17.5 82 55.7
## 28 17.9 80 58.3
## 29 18.0 80 51.5
## 30 18.0 80 51.0
## 31 20.6 87 77.0Melakukan Filter
Sintaks di bawah akan menampilkan data dengan kriteria tertentu dalam kasus ini data yang ditampilkan hanya data yang memiliki value Height lebih dari 20
trees %>% filter(Height>20)## Girth Height Volume
## 1 8.3 70 10.3
## 2 8.6 65 10.3
## 3 8.8 63 10.2
## 4 10.5 72 16.4
## 5 10.7 81 18.8
## 6 10.8 83 19.7
## 7 11.0 66 15.6
## 8 11.0 75 18.2
## 9 11.1 80 22.6
## 10 11.2 75 19.9
## 11 11.3 79 24.2
## 12 11.4 76 21.0
## 13 11.4 76 21.4
## 14 11.7 69 21.3
## 15 12.0 75 19.1
## 16 12.9 74 22.2
## 17 12.9 85 33.8
## 18 13.3 86 27.4
## 19 13.7 71 25.7
## 20 13.8 64 24.9
## 21 14.0 78 34.5
## 22 14.2 80 31.7
## 23 14.5 74 36.3
## 24 16.0 72 38.3
## 25 16.3 77 42.6
## 26 17.3 81 55.4
## 27 17.5 82 55.7
## 28 17.9 80 58.3
## 29 18.0 80 51.5
## 30 18.0 80 51.0
## 31 20.6 87 77.0trees## Girth Height Volume
## 1 8.3 70 10.3
## 2 8.6 65 10.3
## 3 8.8 63 10.2
## 4 10.5 72 16.4
## 5 10.7 81 18.8
## 6 10.8 83 19.7
## 7 11.0 66 15.6
## 8 11.0 75 18.2
## 9 11.1 80 22.6
## 10 11.2 75 19.9
## 11 11.3 79 24.2
## 12 11.4 76 21.0
## 13 11.4 76 21.4
## 14 11.7 69 21.3
## 15 12.0 75 19.1
## 16 12.9 74 22.2
## 17 12.9 85 33.8
## 18 13.3 86 27.4
## 19 13.7 71 25.7
## 20 13.8 64 24.9
## 21 14.0 78 34.5
## 22 14.2 80 31.7
## 23 14.5 74 36.3
## 24 16.0 72 38.3
## 25 16.3 77 42.6
## 26 17.3 81 55.4
## 27 17.5 82 55.7
## 28 17.9 80 58.3
## 29 18.0 80 51.5
## 30 18.0 80 51.0
## 31 20.6 87 77.0Menambah Variabel Baru dengan fungsi mutate()
Sintaks di bawah akan membuat Variabel baru circum yaitu lingkar dari pohon sakura berdasarkan variabel Volume dan Height.
trees %>% mutate(circum=sqrt(Volume/Height/pi)*2*pi)## Girth Height Volume circum
## 1 8.3 70 10.3 1.359798
## 2 8.6 65 10.3 1.411129
## 3 8.8 63 10.2 1.426378
## 4 10.5 72 16.4 1.691845
## 5 10.7 81 18.8 1.707817
## 6 10.8 83 19.7 1.727026
## 7 11.0 66 15.6 1.723436
## 8 11.0 75 18.2 1.746264
## 9 11.1 80 22.6 1.884144
## 10 11.2 75 19.9 1.826000
## 11 11.3 79 24.2 1.962000
## 12 11.4 76 21.0 1.863407
## 13 11.4 76 21.4 1.881070
## 14 11.7 69 21.3 1.969564
## 15 12.0 75 19.1 1.788920
## 16 12.9 74 22.2 1.941626
## 17 12.9 85 33.8 2.235393
## 18 13.3 86 27.4 2.000926
## 19 13.7 71 25.7 2.132762
## 20 13.8 64 24.9 2.211132
## 21 14.0 78 34.5 2.357584
## 22 14.2 80 31.7 2.231462
## 23 14.5 74 36.3 2.482804
## 24 16.0 72 38.3 2.585461
## 25 16.3 77 42.6 2.636722
## 26 17.3 81 55.4 2.931685
## 27 17.5 82 55.7 2.921633
## 28 17.9 80 58.3 3.026176
## 29 18.0 80 51.5 2.844222
## 30 18.0 80 51.0 2.830382
## 31 20.6 87 77.0 3.334960Menggunakan fungsi summarise()
Sintaks di bawah akan menampilkan rata-rata dari peubah Height
trees %>% summarise(mean=mean(Height))## mean
## 1 76Melakukan kelima perintah bersamaan
trees %>% select(Height,Volume)%>% arrange(desc(Height))%>% filter(Height>20)%>% mutate(circum=sqrt(Volume/Height/pi)*2*pi)%>% summarise(mean=mean(Height))## mean
## 1 76