A. Mengakses Database

1. Akses Database Menggunakan Engine `SQL`

Salah satu cara mengakses database menggunakan engine SQL di R adalah dengan package rmarkdown. Dengan membuat suatu objek connection, kemudian menggunakan connection tersebut bersama engine SQL.

# Membuat suatu obyek db
db<-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/chinook.db")

# Class obyek db adalah SQLiteConnection
class(db)

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

Berikut beberapa contoh penggunaan engine SQL di R dengan database chinook.db:

Menampilkan semua kolom dengan filter tertentu

contoh:

Menampilkan semua kolom di tabel employees dengan tahun lahir kurang dari sama dengan 1970 dan tinggal di kota Calgary

SELECT
  *
FROM
  employees
WHERE
  substr(BirthDate,1,4) <= '1970'
  AND
  City = 'Calgary';

3 records
EmployeeId	LastName	FirstName	Title	ReportsTo	BirthDate	HireDate	Address	City	State	Country	PostalCode	Phone	Fax	Email
2	Edwards	Nancy	Sales Manager	1	1958-12-08 00:00:00	2002-05-01 00:00:00	825 8 Ave SW	Calgary	AB	Canada	T2P 2T3	+1 (403) 262-3443	+1 (403) 262-3322	nancy@chinookcorp.com
4	Park	Margaret	Sales Support Agent	2	1947-09-19 00:00:00	2003-05-03 00:00:00	683 10 Street SW	Calgary	AB	Canada	T2P 5G3	+1 (403) 263-4423	+1 (403) 263-4289	margaret@chinookcorp.com
5	Johnson	Steve	Sales Support Agent	2	1965-03-03 00:00:00	2003-10-17 00:00:00	7727B 41 Ave	Calgary	AB	Canada	T3B 1Y7	1 (780) 836-9987	1 (780) 836-9543	steve@chinookcorp.com

Memilih beberapa baris pertama dari kolom tertentu

contoh:

Menampilkan 4 baris awal data nama depan dan jabatan di tabel employees

SELECT
  FirstName, Title
FROM
  employees
LIMIT
  4;

4 records
FirstName	Title
Andrew	General Manager
Nancy	Sales Manager
Jane	Sales Support Agent
Margaret	Sales Support Agent

Mengurutkan data berdasarkan variabel tertentu

contoh:

Menampilkan semua kolom di tabel employees dengan tahun lahir kurang dari sama dengan 1970, tinggal di kota Calgary, dengan diurutkan dari yang tertua

SELECT
  *
FROM
  employees
WHERE
  substr(BirthDate,1,4) <= '1970'
  AND
  City = 'Calgary'
ORDER BY
  substr(BirthDate,1,4);

3 records
EmployeeId	LastName	FirstName	Title	ReportsTo	BirthDate	HireDate	Address	City	State	Country	PostalCode	Phone	Fax	Email
4	Park	Margaret	Sales Support Agent	2	1947-09-19 00:00:00	2003-05-03 00:00:00	683 10 Street SW	Calgary	AB	Canada	T2P 5G3	+1 (403) 263-4423	+1 (403) 263-4289	margaret@chinookcorp.com
2	Edwards	Nancy	Sales Manager	1	1958-12-08 00:00:00	2002-05-01 00:00:00	825 8 Ave SW	Calgary	AB	Canada	T2P 2T3	+1 (403) 262-3443	+1 (403) 262-3322	nancy@chinookcorp.com
5	Johnson	Steve	Sales Support Agent	2	1965-03-03 00:00:00	2003-10-17 00:00:00	7727B 41 Ave	Calgary	AB	Canada	T3B 1Y7	1 (780) 836-9987	1 (780) 836-9543	steve@chinookcorp.com

Menghitung jumlah baris

contoh:

Jumlah baris di tabel employees

SELECT
  COUNT(*)
FROM
  employees;

1 records
COUNT(*)
8

2. Akses Database Menggunakan `dplyr`

Terlebih dahulu install package DBI dan RSQLite:

install.packages(c("RSQLite", "DBI"), dependencies = TRUE)

Load package dplyr dan dbplyr(atau package tidyverse), kemudian load package RSQLite dan DBI.

library(tidyverse)

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

## v ggplot2 3.3.5     v purrr   0.3.4
## v tibble  3.1.5     v dplyr   1.0.7
## v tidyr   1.1.4     v stringr 1.4.0
## v readr   2.0.2     v forcats 0.5.1

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

library(RSQLite)

library(DBI)

Berikut syntax untuk koneksi terhadap database chinook.db pada R software:

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

class(Chinook)

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

Untuk melihat tabel yang terdapat di dalam database menggunakan fungsi dbListTables:

dbListTables(Chinook)

##  [1] "albums"          "artists"         "customers"       "employees"      
##  [5] "genres"          "invoice_items"   "invoices"        "media_types"    
##  [9] "playlist_track"  "playlists"       "sqlite_sequence" "sqlite_stat1"   
## [13] "tracks"

Untuk mengakses tabel pada database digunakan fungsi tbl(database, "table") dari package dplyr. Contoh:

#Melihat isi tabel employees dari database chinook.db
employees<-tbl(Chinook,"employees")
employees

## # Source:   table<employees> [?? x 15]
## # Database: sqlite 3.36.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>

class(employees)

## [1] "tbl_SQLiteConnection" "tbl_dbi"              "tbl_sql"             
## [4] "tbl_lazy"             "tbl"

Menampilkan nama belakang, nama depan pegawai dengan jabatan IT Manager atau IT Staff

p <- employees %>%
  select(LastName, FirstName, Title) %>%
  filter(Title=='IT Manager' | Title=='IT Staff')
p

## # Source:   lazy query [?? x 3]
## # Database: sqlite 3.36.0 [C:\sqlite\chinook.db]
##   LastName FirstName Title     
##   <chr>    <chr>     <chr>     
## 1 Mitchell Michael   IT Manager
## 2 King     Robert    IT Staff  
## 3 Callahan Laura     IT Staff

class(p)

## [1] "tbl_SQLiteConnection" "tbl_dbi"              "tbl_sql"             
## [4] "tbl_lazy"             "tbl"

dplyr dapat menerjemahkan pipeline dengan fungsi show_query():

dplyr::show_query(p)

## <SQL>
## SELECT *
## FROM (SELECT `LastName`, `FirstName`, `Title`
## FROM `employees`)
## WHERE (`Title` = 'IT Manager' OR `Title` = 'IT Staff')

B. Data Wrangling

Penggunaan Package `dplyr` pada Datasets`PlantGrowth`

Install package yang akan digunakan kemudian load package tersebut:

library(tidyverse)

Package tidyverse merupakan package di R yang berfungsi untuk melakukan pengolahan data.

Datasets

Data yang digunakan adalah data PlantGrowth dari package datasets

library(datasets)
data(PlantGrowth)
PlantGrowth<-tibble::as_tibble(PlantGrowth)
class(PlantGrowth)

## [1] "tbl_df"     "tbl"        "data.frame"

Menghitung rata-rata berat tanaman (weight):

mean(PlantGrowth$weight)

## [1] 5.073

atau

PlantGrowth$weight %>% mean()

## [1] 5.073

Untuk menghitung median, standar deviasi, nilai maksimal, nilai minimal, dll. sama seperti penggunaan syntax di atas, namun dengan menggunakan Command median(), sd(), min(), max(), dll.

Penggunaan Package `dplyr`

Package tidyverse memiliki beberapa package, salah satunya adalah package dplyr yang berfungsi untuk melakukan manipulasi/transformasi data. Berikut beberapa contoh penggunaan package dplyr dengan menggunakan datasets PlantGrowth:

1. Summarise

Fungsi summarise() digunakan untuk memperlihatkan informasi dasar/ringkasan dari suatu data seperti mean, median, standar deviasi, dll.

#Menghitung rata-rata berat tanaman(weight)
PlantGrowth %>% summarise(mean=mean(weight))

## # A tibble: 1 x 1
##    mean
##   <dbl>
## 1  5.07

#Menghitung rata-rata, median, standar deviasi, nilai pertama,terakhir, maksimal dan minimal dari berat tanaman (weight)
PlantGrowth %>% summarise(mean=mean(weight), median=median(weight), sd=sd(weight), first=first(weight), last=last(weight), max=max(weight), min=min(weight))

## # A tibble: 1 x 7
##    mean median    sd first  last   max   min
##   <dbl>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1  5.07   5.15 0.701  4.17  5.26  6.31  3.59

#Menghitung rata-rata berat setiap group
PlantGrowth %>% group_by(group) %>% summarise(mean=mean(weight))

## # A tibble: 3 x 2
##   group  mean
##   <fct> <dbl>
## 1 ctrl   5.03
## 2 trt1   4.66
## 3 trt2   5.53

#Menghitung rata-rata, median, standar deviasi, nilai pertama, terakhir, maksimal dan minimal dari berat tanaman untuk tiap group
PlantGrowth %>% group_by(group) %>% summarise(mean=mean(weight), median=median(weight), sd=sd(weight), first=first(weight), last=last(weight), max=max(weight), min=min(weight))

## # A tibble: 3 x 8
##   group  mean median    sd first  last   max   min
##   <fct> <dbl>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 ctrl   5.03   5.15 0.583  4.17  5.14  6.11  4.17
## 2 trt1   4.66   4.55 0.794  4.81  4.69  6.03  3.59
## 3 trt2   5.53   5.44 0.443  6.31  5.26  6.31  4.92

2. Arrange

Fungsi arrange() digunakan untuk mengurutkan data berdasarkan variabel, dapat lebih dari satu variabel dan dapat ditentukan apakah dari besar ke kecil atau sebaliknya.

#Mengurutkan berdasarkan peubah weight dari nilai terkecil
PlantGrowth %>% arrange(weight)

## # A tibble: 30 x 2
##    weight group
##     <dbl> <fct>
##  1   3.59 trt1 
##  2   3.83 trt1 
##  3   4.17 ctrl 
##  4   4.17 trt1 
##  5   4.32 trt1 
##  6   4.41 trt1 
##  7   4.5  ctrl 
##  8   4.53 ctrl 
##  9   4.61 ctrl 
## 10   4.69 trt1 
## # ... with 20 more rows

#Mengurutkan berdasarkan peubah weight dari nilai terbesar
PlantGrowth %>% arrange(desc(weight))

## # A tibble: 30 x 2
##    weight group
##     <dbl> <fct>
##  1   6.31 trt2 
##  2   6.15 trt2 
##  3   6.11 ctrl 
##  4   6.03 trt1 
##  5   5.87 trt1 
##  6   5.8  trt2 
##  7   5.58 ctrl 
##  8   5.54 trt2 
##  9   5.5  trt2 
## 10   5.37 trt2 
## # ... with 20 more rows

3. Filter

Fungsi filter() digunakan untuk menyeleksi dan menampilkan data sesuai dengan ketentuan atau nilai tertentu.

#Menampilkan hanya data untuk group ctrl
PlantGrowth %>% filter(group=="ctrl")

## # A tibble: 10 x 2
##    weight group
##     <dbl> <fct>
##  1   4.17 ctrl 
##  2   5.58 ctrl 
##  3   5.18 ctrl 
##  4   6.11 ctrl 
##  5   4.5  ctrl 
##  6   4.61 ctrl 
##  7   5.17 ctrl 
##  8   4.53 ctrl 
##  9   5.33 ctrl 
## 10   5.14 ctrl

4. Mutate

Fungsi mutate() digunakan untuk menambahkan variabel baru, dapat berupa variabel numerik maupun kategorik.

#Menambahkan variabel nettWeight yang berisikan perkiraan berat bersih tanaman
PlantGrowth %>% mutate(nettWeight= weight-0.5)

## # A tibble: 30 x 3
##    weight group nettWeight
##     <dbl> <fct>      <dbl>
##  1   4.17 ctrl        3.67
##  2   5.58 ctrl        5.08
##  3   5.18 ctrl        4.68
##  4   6.11 ctrl        5.61
##  5   4.5  ctrl        4   
##  6   4.61 ctrl        4.11
##  7   5.17 ctrl        4.67
##  8   4.53 ctrl        4.03
##  9   5.33 ctrl        4.83
## 10   5.14 ctrl        4.64
## # ... with 20 more rows

5. Select

Fungsi select() digunakan untuk mengambil subset data berdasarkan variabel/peubah tertentu.

#Menampilkan data dengan variabel group dan weight
PlantGrowth %>% dplyr::select(group, weight)

## # A tibble: 30 x 2
##    group weight
##    <fct>  <dbl>
##  1 ctrl    4.17
##  2 ctrl    5.58
##  3 ctrl    5.18
##  4 ctrl    6.11
##  5 ctrl    4.5 
##  6 ctrl    4.61
##  7 ctrl    5.17
##  8 ctrl    4.53
##  9 ctrl    5.33
## 10 ctrl    5.14
## # ... with 20 more rows

#Menampilkan data tanpa variable group
PlantGrowth %>% dplyr::select(-group)

## # A tibble: 30 x 1
##    weight
##     <dbl>
##  1   4.17
##  2   5.58
##  3   5.18
##  4   6.11
##  5   4.5 
##  6   4.61
##  7   5.17
##  8   4.53
##  9   5.33
## 10   5.14
## # ... with 20 more rows

Penggunaan Beberapa Fungsi Di Atas Secara Bersama-sama

Contoh:

Menambahkan variabel nettWeight kemudian menampilkan data rata-rata weight dan nettWeight berdasarkan group, yang mempunyai nilai nettWeight kurang dari 5 dan diurutkan berdasarkan nilai nettWeight terkecil ke terbesar:

PlantGrowthNew <- PlantGrowth %>% 
  mutate(nettWeight= weight-0.5)%>%
  dplyr::select(group, weight, nettWeight) %>%
  group_by(group) %>%
  summarise(weight=mean(weight), nettWeight=mean(nettWeight))%>%
  arrange(nettWeight)%>% filter(nettWeight<5)

PlantGrowthNew

## # A tibble: 2 x 3
##   group weight nettWeight
##   <fct>  <dbl>      <dbl>
## 1 trt1    4.66       4.16
## 2 ctrl    5.03       4.53

Perintah Dalam Query

Fungsi dalam SQL	Fungsi dalam dplyr
SELECT	`select()`
GROUP_BY	`group_by()`
tidak ada	`summarise()`
WHERE	`filter()`
ORDER_BY	`arrange()`
COLUMN ALIAS	`mutate()`
JOIN	`join()`

Akses Database dan Data Wrangling di R

Ferista Wahyu Saputri

12/21/2021

A. Mengakses Database

1. Akses Database Menggunakan Engine `SQL`

2. Akses Database Menggunakan `dplyr`

B. Data Wrangling

Penggunaan Package `dplyr` pada Datasets`PlantGrowth`

Datasets

Penggunaan Package `dplyr`

1. Summarise

2. Arrange

3. Filter

4. Mutate

5. Select

Penggunaan Beberapa Fungsi Di Atas Secara Bersama-sama

Perintah Dalam Query

Akses Database dan Data Wrangling di R

Ferista Wahyu Saputri

12/21/2021

A. Mengakses Database

1. Akses Database Menggunakan Engine SQL

2. Akses Database Menggunakan dplyr

B. Data Wrangling

Penggunaan Package dplyr pada DatasetsPlantGrowth

Datasets

Penggunaan Package dplyr

1. Summarise

2. Arrange

3. Filter

4. Mutate

5. Select

Penggunaan Beberapa Fungsi Di Atas Secara Bersama-sama

Perintah Dalam Query

1. Akses Database Menggunakan Engine `SQL`

2. Akses Database Menggunakan `dplyr`

Penggunaan Package `dplyr` pada Datasets`PlantGrowth`

Penggunaan Package `dplyr`