Akses Database dan Data Wrangling

A. Contoh Akses Database “Northwind”

Database Northwind dapat diunduh di https://github.com/jpwhite3/northwind-SQLite3 dengan nama file database Northwind_large.sqlite.

1. Engine SQL

Proses koneksi terhadap database pada R menggunakan syntax sebagai berikut.

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

Untuk melihat tabel yang terdapat pada database digunakan syntax sebagai berikut.

RSQLite::dbListTables(db)

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

Selanjutnya, untuk mengakses database Northwind_large.sqlite menggunakan syntax SQL sebagai berikut.

SELECT
  *
FROM
  Customer
WHERE
  Region = 'Western Europe';

Displaying records 1 - 10
Id	CompanyName	ContactName	ContactTitle	Address	City	Region	PostalCode	Country	Phone	Fax
ALFKI	Alfreds Futterkiste	Maria Anders	Sales Representative	Obere Str. 57	Berlin	Western Europe	12209	Germany	030-0074321	030-0076545
BLAUS	Blauer See Delikatessen	Hanna Moos	Sales Representative	Forsterstr. 57	Mannheim	Western Europe	68306	Germany	0621-08460	0621-08924
BLONP	Blondesddsl père et fils	Frédérique Citeaux	Marketing Manager	24, place Kléber	Strasbourg	Western Europe	67000	France	88.60.15.31	88.60.15.32
BONAP	Bon app	Laurence Lebihan	Owner	12, rue des Bouchers	Marseille	Western Europe	13008	France	91.24.45.40	91.24.45.41
CHOPS	Chop-suey Chinese	Yang Wang	Owner	Hauptstr. 29	Bern	Western Europe	3012	Switzerland	0452-076545	NA
DRACD	Drachenblut Delikatessen	Sven Ottlieb	Order Administrator	Walserweg 21	Aachen	Western Europe	52066	Germany	0241-039123	0241-059428
DUMON	Du monde entier	Janine Labrune	Owner	67, rue des Cinquante Otages	Nantes	Western Europe	44000	France	40.67.88.88	40.67.89.89
ERNSH	Ernst Handel	Roland Mendel	Sales Manager	Kirchgasse 6	Graz	Western Europe	8010	Austria	7675-3425	7675-3426
FOLIG	Folies gourmandes	Martine Rancé	Assistant Sales Agent	184, chaussée de Tournai	Lille	Western Europe	59000	France	20.16.10.16	20.16.10.17
FRANK	Frankenversand	Peter Franken	Marketing Manager	Berliner Platz 43	München	Western Europe	80805	Germany	089-0877310	089-0877451

Syntax di atas menampilkan tabel Customer yang peubah Region nya ‘Western Europe’.

2. Menggunakan `dplyr`

Proses koneksi terhadap database pada R menggunakan syntax sebagai berikut.

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

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

Untuk melihat tabel yang terdapat pada database digunakan syntax sebagai berikut.

RSQLite::dbListTables(Northwind)

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

Selanjutnya, untuk mengakses tabel pada database digunakan syntax sebagai berikut.

product<-dplyr::tbl(Northwind,"Product")
class(product)

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

Berikut ini adalah isi dari obyek product.

product

## # Source:   table<Product> [?? x 10]
## # Database: sqlite 3.36.0 [C:\sqlite\db\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>

Berikut ini adalah contoh penggunaan dplyr.

library(dplyr)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

result<-product %>% 
  select(Id, ProductName, UnitPrice, UnitsInStock) %>%
  filter(UnitPrice>30 & UnitsInStock!=0)
result

## # Source:   lazy query [?? x 4]
## # Database: sqlite 3.36.0 [C:\sqlite\db\Northwind_large.sqlite]
##       Id ProductName                UnitPrice UnitsInStock
##    <int> <chr>                          <dbl>        <int>
##  1     8 Northwoods Cranberry Sauce      40              6
##  2     9 Mishi Kobe Niku                 97             29
##  3    10 Ikura                           31             31
##  4    12 Queso Manchego La Pastora       38             86
##  5    18 Carnarvon Tigers                62.5           42
##  6    20 Sir Rodney's Marmalade          81             40
##  7    26 Gumbär Gummibärchen             31.2           15
##  8    27 Schoggi Schokolade              43.9           49
##  9    28 Rössle Sauerkraut               45.6           26
## 10    32 Mascarpone Fabioli              32              9
## # ... with more rows

Syntax di atas menampilkan tabel product yang berisi peubah Id, ProductName, UnitPrice, dan Units In Stock dengan UnitPrice lebih dari 30 dan Units In Stock tidak sama dengan nol.

class(result)

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

dplyr::show_query(result)

## <SQL>
## SELECT *
## FROM (SELECT `Id`, `ProductName`, `UnitPrice`, `UnitsInStock`
## FROM `Product`)
## WHERE (`UnitPrice` > 30.0 AND `UnitsInStock` != 0.0)

B. Data Wrangling

library(tidyverse)

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

## v ggplot2 3.3.5     v purrr   0.3.4
## v tibble  3.1.3     v stringr 1.4.0
## v tidyr   1.1.4     v forcats 0.5.1
## v readr   2.0.2

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

Berikut ini adalah cara mengakses data attenu dari package datasets.

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

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

1. Fungsi `summarise()`

Untuk menghitung rata-rata dist pada setiap event, digunakan syntax sebagai berikut.

attenu %>% group_by(event) %>% summarise(mean=mean(dist), .groups='drop')

## # A tibble: 23 x 2
##    event  mean
##    <dbl> <dbl>
##  1     1  12  
##  2     2 189. 
##  3     3   8  
##  4     4  51.8
##  5     5 153. 
##  6     6  62  
##  7     7  62  
##  8     8  20.8
##  9     9  46.8
## 10    10  31  
## # ... with 13 more rows

2. Fungsi `arrange()`

Untuk mengurutkan peubah mag dari yang terkecil lalu mengurutkan peubah accel dari yang terbesar, digunakan syntax sebagai berikut.

attenu %>% arrange(mag, desc(accel))

## # A tibble: 182 x 5
##    event   mag station  dist accel
##    <dbl> <dbl> <fct>   <dbl> <dbl>
##  1    20     5 952       9.7 0.286
##  2    20     5 5055      7.5 0.264
##  3    20     5 942       8.8 0.263
##  4    20     5 955      10.5 0.237
##  5    20     5 5028      8.9 0.23 
##  6    20     5 5058     14.6 0.192
##  7    20     5 958       9.7 0.157
##  8    20     5 5115     17.6 0.154
##  9    20     5 5165      9.4 0.147
## 10    20     5 5057     14.9 0.147
## # ... with 172 more rows

3. Fungsi `filter()`

Untuk memilih sebagian data berdasarkan peubah mag yang bernilai 7.4 dan peubah dist yang bernilai antara 100 dan 300, digunakan syntax sebagai berikut.

attenu %>% filter(mag==7.4 & dist %in% 100:300)

## # A tibble: 6 x 5
##   event   mag station  dist accel
##   <dbl> <dbl> <fct>   <dbl> <dbl>
## 1     2   7.4 1083      148 0.014
## 2     2   7.4 135       107 0.062
## 3     2   7.4 475       109 0.054
## 4     2   7.4 113       156 0.014
## 5     2   7.4 1008      224 0.018
## 6     2   7.4 1028      293 0.01

4. Fungsi `mutate()`

Untuk menambahkan peubah baru pada data bernama speed yang merupakan pembagian antara dist dan time dan apabila time bernilai 4, digunakan syntax sebagai berikut.

attenu %>% mutate(speed=dist/4)

## # A tibble: 182 x 6
##    event   mag station  dist accel speed
##    <dbl> <dbl> <fct>   <dbl> <dbl> <dbl>
##  1     1   7   117        12 0.359   3  
##  2     2   7.4 1083      148 0.014  37  
##  3     2   7.4 1095       42 0.196  10.5
##  4     2   7.4 283        85 0.135  21.2
##  5     2   7.4 135       107 0.062  26.8
##  6     2   7.4 475       109 0.054  27.2
##  7     2   7.4 113       156 0.014  39  
##  8     2   7.4 1008      224 0.018  56  
##  9     2   7.4 1028      293 0.01   73.2
## 10     2   7.4 2001      359 0.004  89.8
## # ... with 172 more rows

5. Fungsi `select()`

Untuk memilih subset data berdasarkan peubah tertentu, digunakan syntax sebagai berikut.

attenu %>% select(mag, dist, accel)

## # A tibble: 182 x 3
##      mag  dist accel
##    <dbl> <dbl> <dbl>
##  1   7      12 0.359
##  2   7.4   148 0.014
##  3   7.4    42 0.196
##  4   7.4    85 0.135
##  5   7.4   107 0.062
##  6   7.4   109 0.054
##  7   7.4   156 0.014
##  8   7.4   224 0.018
##  9   7.4   293 0.01 
## 10   7.4   359 0.004
## # ... with 172 more rows

6. Gabungan Fungsi `summarise()`, `arrange()`, `filter()`, `mutate()`, dan `select()`

Attenu <- attenu %>% select(mag, dist, accel) %>%
          filter(mag>6.6 & dist %in% 100:300) %>%
          arrange(mag, desc(accel)) %>%   
          mutate(speed=dist/4) %>%
          group_by(mag) %>% summarise(mean=mean(dist), .groups='drop')
Attenu

## # A tibble: 2 x 2
##     mag  mean
##   <dbl> <dbl>
## 1   7.4  173.
## 2   7.7  222.

Sekian terima kasih.

Akses Database dan Data Wrangling