Tugas Akhir Mandarel
A. Contoh Akses Database
Database yang akan diakses adalah database chinook Untuk mengakses database tersebut diperlukan package DBI dan RSQLite
install.packages(c("RSQLite", "DBI"), dependencies = TRUE)Panggil package tidyverse, DBI, RSQLite untuk mengakses dan mengolah database menggunakan fungsi library()
library(tidyverse)
library(RSQLite)
library(DBI)1. Engine SQL
Koneksi terhadap database untuk menghubungkan SQL dengan aplikasi R diperlukan suatu objek connection menggunakan syntax:
db<-DBI::dbConnect(RSQLite::SQLite(), "C:/Sqlite/chinook.db")Setelah menghubungkan SQL dengan aplikasi R melaluidbConnect(), perintah SQL dapat dilakukan
SELECT
*
FROM
customers
WHERE
Country="USA"
LIMIT
10
;| CustomerId | FirstName | LastName | Company | Address | City | State | Country | PostalCode | Phone | Fax | SupportRepId | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 16 | Frank | Harris | Google Inc. | 1600 Amphitheatre Parkway | Mountain View | CA | USA | 94043-1351 | +1 (650) 253-0000 | +1 (650) 253-0000 | fharris@google.com | 4 |
| 17 | Jack | Smith | Microsoft Corporation | 1 Microsoft Way | Redmond | WA | USA | 98052-8300 | +1 (425) 882-8080 | +1 (425) 882-8081 | jacksmith@microsoft.com | 5 |
| 18 | Michelle | Brooks | NA | 627 Broadway | New York | NY | USA | 10012-2612 | +1 (212) 221-3546 | +1 (212) 221-4679 | michelleb@aol.com | 3 |
| 19 | Tim | Goyer | Apple Inc. | 1 Infinite Loop | Cupertino | CA | USA | 95014 | +1 (408) 996-1010 | +1 (408) 996-1011 | tgoyer@apple.com | 3 |
| 20 | Dan | Miller | NA | 541 Del Medio Avenue | Mountain View | CA | USA | 94040-111 | +1 (650) 644-3358 | NA | dmiller@comcast.com | 4 |
| 21 | Kathy | Chase | NA | 801 W 4th Street | Reno | NV | USA | 89503 | +1 (775) 223-7665 | NA | kachase@hotmail.com | 5 |
| 22 | Heather | Leacock | NA | 120 S Orange Ave | Orlando | FL | USA | 32801 | +1 (407) 999-7788 | NA | hleacock@gmail.com | 4 |
| 23 | John | Gordon | NA | 69 Salem Street | Boston | MA | USA | 2113 | +1 (617) 522-1333 | NA | johngordon22@yahoo.com | 4 |
| 24 | Frank | Ralston | NA | 162 E Superior Street | Chicago | IL | USA | 60611 | +1 (312) 332-3232 | NA | fralston@gmail.com | 3 |
| 25 | Victor | Stevens | NA | 319 N. Frances Street | Madison | WI | USA | 53703 | +1 (608) 257-0597 | NA | vstevens@yahoo.com | 5 |
Untuk memutuskan koneksi dengan DBMS digunakan:
dbDisconnect(db)2. Menggunakan DPLYR
Membuat variabel untuk memanggil suatu database
dbchinook<-DBI::dbConnect(RSQLite::SQLite(), "C:/Sqlite/chinook.db")Untuk mengetahui kelas digunakan:
class(dbchinook)## [1] "SQLiteConnection"
## attr(,"package")
## [1] "RSQLite"Melihat tabel yang ada pada chinook
RSQLite::dbListTables(dbchinook)## [1] "albums" "artists" "customers" "employees"
## [5] "genres" "invoice_items" "invoices" "media_types"
## [9] "playlist_track" "playlists" "sqlite_sequence" "sqlite_stat1"
## [13] "tracks"Memanggil spesifik tabel
dplyr::tbl(dbchinook, "employees")## # Source: table<employees> [?? x 15]
## # Database: sqlite 3.37.0 [C:\Sqlite\chinook.db]
## EmployeeId LastName FirstName Title ReportsTo BirthDate HireDate Address City
## <int> <chr> <chr> <chr> <int> <chr> <chr> <chr> <chr>
## 1 1 Adams Andrew Gene~ NA 1962-02-~ 2002-08~ 11120 ~ Edmo~
## 2 2 Edwards Nancy Sale~ 1 1958-12-~ 2002-05~ 825 8 ~ Calg~
## 3 3 Peacock Jane Sale~ 2 1973-08-~ 2002-04~ 1111 6~ Calg~
## 4 4 Park Margaret Sale~ 2 1947-09-~ 2003-05~ 683 10~ Calg~
## 5 5 Johnson Steve Sale~ 2 1965-03-~ 2003-10~ 7727B ~ Calg~
## 6 6 Mitchell Michael IT M~ 1 1973-07-~ 2003-10~ 5827 B~ Calg~
## 7 7 King Robert IT S~ 6 1970-05-~ 2004-01~ 590 Co~ Leth~
## 8 8 Callahan Laura IT S~ 6 1968-01-~ 2004-03~ 923 7 ~ Leth~
## # ... with 6 more variables: State <chr>, Country <chr>, PostalCode <chr>,
## # Phone <chr>, Fax <chr>, Email <chr>Memanggil spesifik tabel sebagai variabel baru pada R
employees<-dplyr::tbl(dbchinook, "employees")
employees## # Source: table<employees> [?? x 15]
## # Database: sqlite 3.37.0 [C:\Sqlite\chinook.db]
## EmployeeId LastName FirstName Title ReportsTo BirthDate HireDate Address City
## <int> <chr> <chr> <chr> <int> <chr> <chr> <chr> <chr>
## 1 1 Adams Andrew Gene~ NA 1962-02-~ 2002-08~ 11120 ~ Edmo~
## 2 2 Edwards Nancy Sale~ 1 1958-12-~ 2002-05~ 825 8 ~ Calg~
## 3 3 Peacock Jane Sale~ 2 1973-08-~ 2002-04~ 1111 6~ Calg~
## 4 4 Park Margaret Sale~ 2 1947-09-~ 2003-05~ 683 10~ Calg~
## 5 5 Johnson Steve Sale~ 2 1965-03-~ 2003-10~ 7727B ~ Calg~
## 6 6 Mitchell Michael IT M~ 1 1973-07-~ 2003-10~ 5827 B~ Calg~
## 7 7 King Robert IT S~ 6 1970-05-~ 2004-01~ 590 Co~ Leth~
## 8 8 Callahan Laura IT S~ 6 1968-01-~ 2004-03~ 923 7 ~ Leth~
## # ... with 6 more variables: State <chr>, Country <chr>, PostalCode <chr>,
## # Phone <chr>, Fax <chr>, Email <chr>Untuk mengetahui kelas:
class(employees)## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"Contoh menggunakan syntax dplyr %>% dan dimasukkan dalam suatu variabel
E <- employees %>%
select(EmployeeId, LastName, FirstName, HireDate, City) %>%
filter(City=="Calgary") %>%
arrange(desc(EmployeeId))
E## # Source: lazy query [?? x 5]
## # Database: sqlite 3.37.0 [C:\Sqlite\chinook.db]
## # Ordered by: desc(EmployeeId)
## EmployeeId LastName FirstName HireDate City
## <int> <chr> <chr> <chr> <chr>
## 1 6 Mitchell Michael 2003-10-17 00:00:00 Calgary
## 2 5 Johnson Steve 2003-10-17 00:00:00 Calgary
## 3 4 Park Margaret 2003-05-03 00:00:00 Calgary
## 4 3 Peacock Jane 2002-04-01 00:00:00 Calgary
## 5 2 Edwards Nancy 2002-05-01 00:00:00 CalgaryUntuk melihat syntax tersebut jika dijalankan dalam SQL
dplyr::show_query(E)## <SQL>
## SELECT *
## FROM (SELECT `EmployeeId`, `LastName`, `FirstName`, `HireDate`, `City`
## FROM `employees`)
## WHERE (`City` = 'Calgary')
## ORDER BY `EmployeeId` DESCB. Data Wrangling
Data yang digunakan adalah dataset cars yang ada pada package datasets di R Dataset cars berisi Kecepatan (mph) dan Jarak tempuh mobil (ft)
library(datasets)
cars1. Select
Memilih kolom tertentu dengan menggunakan perintah select
cars %>% select(speed, dist)## speed dist
## 1 4 2
## 2 4 10
## 3 7 4
## 4 7 22
## 5 8 16
## 6 9 10
## 7 10 18
## 8 10 26
## 9 10 34
## 10 11 17
## 11 11 28
## 12 12 14
## 13 12 20
## 14 12 24
## 15 12 28
## 16 13 26
## 17 13 34
## 18 13 34
## 19 13 46
## 20 14 26
## 21 14 36
## 22 14 60
## 23 14 80
## 24 15 20
## 25 15 26
## 26 15 54
## 27 16 32
## 28 16 40
## 29 17 32
## 30 17 40
## 31 17 50
## 32 18 42
## 33 18 56
## 34 18 76
## 35 18 84
## 36 19 36
## 37 19 46
## 38 19 68
## 39 20 32
## 40 20 48
## 41 20 52
## 42 20 56
## 43 20 64
## 44 22 66
## 45 23 54
## 46 24 70
## 47 24 92
## 48 24 93
## 49 24 120
## 50 25 852. Arrange
Mengurutkan tabel berdasarkan kolom tertentu secara ascending atau descending menggunakan perintah arrange
Saat ini kita menggunakan perintah desc untuk mengurutkan dari terbesar ke terkecil
cars %>% arrange(desc(dist))## speed dist
## 1 24 120
## 2 24 93
## 3 24 92
## 4 25 85
## 5 18 84
## 6 14 80
## 7 18 76
## 8 24 70
## 9 19 68
## 10 22 66
## 11 20 64
## 12 14 60
## 13 18 56
## 14 20 56
## 15 15 54
## 16 23 54
## 17 20 52
## 18 17 50
## 19 20 48
## 20 13 46
## 21 19 46
## 22 18 42
## 23 16 40
## 24 17 40
## 25 14 36
## 26 19 36
## 27 10 34
## 28 13 34
## 29 13 34
## 30 16 32
## 31 17 32
## 32 20 32
## 33 11 28
## 34 12 28
## 35 10 26
## 36 13 26
## 37 14 26
## 38 15 26
## 39 12 24
## 40 7 22
## 41 12 20
## 42 15 20
## 43 10 18
## 44 11 17
## 45 8 16
## 46 12 14
## 47 4 10
## 48 9 10
## 49 7 4
## 50 4 23. Filter
Memilih baris atau menyeleksi baris berdasarkan kriteria tertentu menggunakan perintah filter
cars %>% filter(speed>15)## speed dist
## 1 16 32
## 2 16 40
## 3 17 32
## 4 17 40
## 5 17 50
## 6 18 42
## 7 18 56
## 8 18 76
## 9 18 84
## 10 19 36
## 11 19 46
## 12 19 68
## 13 20 32
## 14 20 48
## 15 20 52
## 16 20 56
## 17 20 64
## 18 22 66
## 19 23 54
## 20 24 70
## 21 24 92
## 22 24 93
## 23 24 120
## 24 25 854. Mutate
Membuat kolom baru dari kolom yang sudah ada menggunakan perintah mutate
Saat ini kita akan membuat kolom baru bernama miles dengan mengubah jarak dist(ft) dibagi dengan 5280 agar dapat mengubah jarak (ft) kedalam bentuk miles.
cars %>% mutate(miles=dist/5280)## speed dist miles
## 1 4 2 0.0003787879
## 2 4 10 0.0018939394
## 3 7 4 0.0007575758
## 4 7 22 0.0041666667
## 5 8 16 0.0030303030
## 6 9 10 0.0018939394
## 7 10 18 0.0034090909
## 8 10 26 0.0049242424
## 9 10 34 0.0064393939
## 10 11 17 0.0032196970
## 11 11 28 0.0053030303
## 12 12 14 0.0026515152
## 13 12 20 0.0037878788
## 14 12 24 0.0045454545
## 15 12 28 0.0053030303
## 16 13 26 0.0049242424
## 17 13 34 0.0064393939
## 18 13 34 0.0064393939
## 19 13 46 0.0087121212
## 20 14 26 0.0049242424
## 21 14 36 0.0068181818
## 22 14 60 0.0113636364
## 23 14 80 0.0151515152
## 24 15 20 0.0037878788
## 25 15 26 0.0049242424
## 26 15 54 0.0102272727
## 27 16 32 0.0060606061
## 28 16 40 0.0075757576
## 29 17 32 0.0060606061
## 30 17 40 0.0075757576
## 31 17 50 0.0094696970
## 32 18 42 0.0079545455
## 33 18 56 0.0106060606
## 34 18 76 0.0143939394
## 35 18 84 0.0159090909
## 36 19 36 0.0068181818
## 37 19 46 0.0087121212
## 38 19 68 0.0128787879
## 39 20 32 0.0060606061
## 40 20 48 0.0090909091
## 41 20 52 0.0098484848
## 42 20 56 0.0106060606
## 43 20 64 0.0121212121
## 44 22 66 0.0125000000
## 45 23 54 0.0102272727
## 46 24 70 0.0132575758
## 47 24 92 0.0174242424
## 48 24 93 0.0176136364
## 49 24 120 0.0227272727
## 50 25 85 0.01609848485. Summarise
Meringkas beberapa baris data menjadi suatu nilai baru menggunakan perintah summarise
cars %>% summarise(mean_speed=mean(speed), mean_dist=mean(dist))## mean_speed mean_dist
## 1 15.4 42.98Melakukan kelima perintah sekaligus
Untuk mempersingkat program, dapat melakukan kelima perintah bersama-sama
cars %>% select(speed, dist) %>%
arrange(desc(dist)) %>%
filter(speed>15) %>%
mutate(miles=dist/5280) %>%
summarise(mean_speed=mean(speed), mean_dist=mean(dist))## mean_speed mean_dist
## 1 19.91667 59.75