Database Querying, Data Wrangling, & Working with Spatial Data
Rahma Anisa1
Gerry Alfa Dito2
September 15, 2020
Database Queries
Beberapa package yang digunakan untuk bekerja dengan database menggunakan R software adalah:
DBIberfungsi memisahkan konektivitas ke DBMS menjadi “front-end” dan “back-end”. Aplikasi hanya menggunakan API front-end yang terbuka. Fasilitas back-end yang berkomunikasi dengan DBMS tertentu (SQLite, MySQL, PostgreSQL, MonetDB, dll.) disediakan oleh driver (package lain) yang dipanggil secara otomatis.dplyrdandbplyrmemiliki SQL back-end yang digeneralisasi untuk bisa bekerja dengan database.odbcmenyediakan antarmuka yang sesuai dengan DBI untuk driver Open Database Connectivity (ODBC). Hal ini memungkinkan koneksi yang efisien dan mudah diatur ke database apa pun dengan driver ODBC yang tersedia, termasuk SQL Server, Oracle, MySQL, PostgreSQL, SQLite, dan lainnya.
Connecting to a Database
Kali ini contoh pemanggilan database akan menggunakan contoh data Northwind yang digunakan pada pertemuan sebelumnya. Untuk dapat melakukannya, package RSQLite perlu di-install terlebih dulu.
install.packages("RSQLite")Selanjutnya kita panggil package yang akan digunakan. Kita dapat memanggil package dplry dan dbplyr, atau bisa dengan hanya memanggil package tidyverse yang mencakup keduanya sekaligus. Selain itu, jangan lupa untuk memanggil package RSQLite.
library(tidyverse)
library(RSQLite)Secara umum, koneksi terhadap database pada R software adalah dengan menggunakan syntax berikut:
DBI::dbConnect(RSQLite::SQLite(), path = ":dbname:")
Fungsi dapat pula dijalankankan tanpa memanggil package dengan perintah library() atau require(), jika syntax yang ditulis menyertakan nama package tempat fungsi tersebut berasal.
Northwind<-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/Northwind_large.sqlite")
class(Northwind)## [1] "SQLiteConnection"
## attr(,"package")
## [1] "RSQLite"Perhatikan bahwa syntax di atas tidak membaca data ke dalam R seperti halnya fungsi read_csv() melainkan berupa instruksi kepada R untuk mengkoneksikan database SQLite yang terdapat pada file Northwind_large.sqlite. Selanjutnya, fungsi dbListTables dapat digunakan untuk melihat tables yang terdapat di 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"Untuk mengakses tabel pada database dapat digunakan fungsi tbl(database, "table") seperti pada contoh berikut ini.
(product<-tbl(Northwind,"Product"))class(product)## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"Perhatikan bahwa product merupakan object dengan class tbl yang dapat diperlakukan mirip seperti data.frame.
From dplyr to SQL
Seandainya kita ingin mengetahui minimum stok untuk produk dengan harga di atas $20 untuk setiap supplier. Untuk memperoleh informasi tersebut kita dapat menuliskan syntax berikut ini.
q<-product %>%
filter(UnitPrice>20) %>%
group_by(SupplierId) %>%
summarize(id=SupplierId, stock=min(UnitsInStock)) %>%
arrange(stock)
q Pada beberapa kasus, data yang digunakan bisa saja berukuran sangat besar sehingga lebih efisien jika disimpan dalam bentuk database. Syntax di atas merupakan contoh cara kita memproses data dalam bentuk database dengan cara menggunakan package dplyr untuk menghubungkan syntax pada R dengan data pada SQLite. dplyr mampu menejermahkan pipeline dengan fungsi show_query() serperti contoh di bawah ini.
show_query(q)## <SQL>
## SELECT `SupplierId`, `SupplierId` AS `id`, MIN(`UnitsInStock`) AS `stock`
## FROM `Product`
## WHERE (`UnitPrice` > 20.0)
## GROUP BY `SupplierId`
## ORDER BY `stock`Sebaliknya, kita dapat menuliskan fungsi sql seperti pada contoh di bawah ini.
tbl(Northwind, sql("SELECT productname, categoryid, unitprice FROM Product limit 10"))Contoh lainnya, misalkan kita ingin melihat 4 perusahaan pertama dari table Customer.
tbl(Northwind,"Customer") %>%
select(CompanyName) %>%
head(n=4)Data Wrangling
Grammar for Data Wrangling
Menurut Baumer et al. (2017), terdapat lima verbs yang didefinisikan oleh Hadley Wickham, salah satu penyusun package dplyr, untuk digunakan saat bekerja pada suatu data frame, yaitu:
select()filter()mutate()arrange()summarize()
Kelima fungsi di atas sudah pernah kita bahas sebelumnya pada topik “Pengenalan Package dplyr”. Saat itu kita membahas contoh kasus data baseball, atau yang disebut juga sebagai sabermetrics. Untuk memudahkan, pada subbab berikutnya kita akan melanjutkan ilustrasi menggunakan data tersebut.
Revisiting Sabermetrics Case
Jika Anda masih ingat, pada praktikum 1 kita telah mendiskusikan bagaimana performa tim New York Mets saat bermain di New York League selama pemain Ben bergabung dengan tim Mets.
library(Lahman)
Teams %>%
select(yearID, teamID, W, L) %>%
filter(teamID == "NYN" & yearID %in% 2004:2012)Pada praktikum 1 kita telah mendiskusikan pada saat itu ternyata tim Mets mengalami lebih banyak kekalahan daripada kemenangan. Untuk menganalisa lebih lanjut apakah ini memang terjadi secara kebetulan atau tidak, perlu ditelusuri nilai harapan dari tingkat kemenangan tim tersebut. Untuk dapat menentukan nilai harapan tersebut, kita dapat memanfaatkan data banyaknya runs yang berhasil mereka menangkan (number of runs that the team scores (RS)) dan banyaknya runs yang mereka lakukan dalam pertandingan(number of runs that the team allows (RA)).
metsBen <- Teams %>% select(yearID, teamID, W, L, R, RA) %>%
filter(teamID == "NYN" & yearID %in% 2004:2012)
metsBenUntuk memudahkan penamaan variable, maka kita ganti nama variable R menjadi RS.
metsBen <- metsBen %>% rename(RS = R) # new name = old name
metsBenPersentase kemenangan dapat dihitung secara empirik berdasarkan data historis.
metsBen <- metsBen %>% mutate(WPct = W / (W + L))
metsBenSelanjutnya anggaplah model yang digunakan untuk menentukan nilai harapan bagi tingkat kemenangan yang umum digunakan pada sabermetrics adalah dengan menggunakan rumus berikut:
\[\hat{WPct} = \frac{1} {1+(\frac{RA}{RS})^2}\]
metsBen <- metsBen %>% mutate(WPct_hat = 1 / (1 +(RA/RS)^2))
metsBenNilai harapan untuk kemenangan the Mets dapat dihitung dengan mengalikan \(\hat{WPct}\) dengan banyaknya games.
metsBen <- metsBen %>% mutate(W_hat = WPct_hat * (W + L))
metsBenBerdasarkan informasi di atas, kita dapat menelusuri kapan the Mets mengalami kemenangan yang lebih besar dari nilai harapannya.
filter(metsBen, W >= W_hat)Ternyata, the Mets bermain lebih baik dari dugaannya pada musim di tahun 2006, 2007, dan 2012. Dan hal sebaliknya terjadi pada musim di tahun-tahun yang lain selama Ben bermain bersama the Mets. Untuk mengetahui musim terbaik bagi the Mets, kita dapat mengurutkan data tersebut seperti yang dapat dilihat di bawah ini.
arrange(metsBen, desc(WPct))Untuk melihat ringkasan data untuk menilai performa tim selama tahun 2004-2012, kita dapat menggunakan fungsi summarize() berikut.
metsBen %>%
summarize(num_years = n(), total_W = sum(W), total_L = sum(L),
total_WPct = sum(W) / sum(W + L), sum_resid = sum(W - W_hat))Seperti dijelaskan oleh Baumer et al. (2017), “Dalam sembilan tahun ini, Mets memiliki rekor gabungan 728 kemenangan dan 730 kekalahan, dengan persentase kemenangan keseluruhan 0,499. Hanya satu kemenangan ekstra akan membuat mereka tepat 0,500! (Jika kami dapat memilih game untuk bermain kembali, kami pasti akan memilih game terakhir musim 2007. Playoff di sana pasti akan menghasilkan kemenangan.) Namun, kami juga telah mempelajari bahwa performa tim relatif buruk berdasarkan model yang dibuat, yaitu dengan total selisih 10,6 pertandingan selama sembilan musim tersebut”, (p. 77).
Analisa lebih lanjut untuk studi kasus ini dapat dipelajari pada Baumer et al. (2017).
Combining Multiple Tables
inner_join()menghasilkan semua baris pada tablexyang memiliki kesamaan nilai dengan tabley, dan semua kolom darixdany.left_join()menghasilkan semua baris pada tablex, dan semua kolom padaxdany, dimana untuk baris padaxyang tidak memiliki kesamaan denganydiisi dengan nilaiNApada kolom yang baru.right_join()menghasilkan semua baris pada tabley, dan semua kolom padaxdany, dimana untuk baris padayyang tidak memiliki kesamaan denganxakan diisi dengan nilaiNApada kolom yang beru.full_join()menghasilkan semua baris dan kolom darixdany. Jika ada nilai yang tidak sama (match) antaraxdany, maka akan bernilaiNA.
Jenis penggabungan lain selengkapnya dapat dipelajari di sini.
Contoh Menggabungkan Tabel pada Data Northwind
Misalnya kita ingin menggabungkan data pada table Order dan Customer berdasarkan peubah CustomerId pada table Order, atau sama dengan Id pada table Customer.
(Order<-tbl(Northwind,"Order"))(Customer<-tbl(Northwind,"Customer"))InnerJoin<-Order %>%
inner_join(Customer, by=c("CustomerId"="Id"))
InnerJoinSeandainya akan diaplikasikan fungsi left_join() pada kedua tabel di atas, maka dapat dilakukan dengan menuliskan syntax berikut ini.
OrderDetail<-tbl(Northwind, "OrderDetail")
LeftJoin<-OrderDetail %>%
left_join(product, by=c("ProductId"="Id"))
LeftJoinUntuk memperlihatkan bahwa fungsi left_join()mengacu pada tabel pertama, dalam hal ini adalah tabel OrderDetail, perhatikan banyaknya baris pada masing-masing tabel sebagai berikut.
tally(product)tally(OrderDetail)tally(LeftJoin)Perhatikan bahwa banyaknya baris pada hasil penggabungan tabel adalah sama dengan tabel acuannya, yaitu OrderDetail.
Working with Spatial Data
Spatial Data Structure
Tipe data spasial yang paling umum digunakan adalah shapefile, adapun tipe lain yang juga cukup populer adalah KML (Keyhole Markup Language). Data spasial tidak hanya berisi baris dan kolom, namun objek geometrik, seperti titik, garis, ataupun poligon. Data shapefile sebenarnya terdiri dari beberapa file dengan beberapa extension, di antaranya adalah .shp, .shx, dan .dbf. Beberapa package yang umum digunakan untuk bekerja dengan data spasial adalah sp dan rgdal.
Reading Spatial Data
Sebagai ilustrasi, akan digunakan data yang tersedia pada laman http://rtwilson.com/downloads/SnowGIS_SHP.zip. Pastikan Anda mengekstrak folder data tersebut pada direktori yang Anda inginkan. Selanjutnya, package rgdal akan digunakan untuk membaca data SnowGIS tersebut.
library(rgdal)Untuk melihat file apa saja yang ada di dalam folder shapefile tersebut, kita dapat menggunakan fungsi list.files() dan tuliskan direktori Anda masing-masing, ini dikenal sebagai dsn.
dsn<-paste("D:/Dept.STK/Courses/Sains Data/Praktikum/P3/SnowGIS_SHP")
list.files(dsn)## [1] "Cholera_Deaths.dbf" "Cholera_Deaths.prj"
## [3] "Cholera_Deaths.sbn" "Cholera_Deaths.sbx"
## [5] "Cholera_Deaths.shp" "Cholera_Deaths.shx"
## [7] "OSMap.tfw" "OSMap.tif"
## [9] "OSMap_Grayscale.tfw" "OSMap_Grayscale.tif"
## [11] "OSMap_Grayscale.tif.aux.xml" "OSMap_Grayscale.tif.ovr"
## [13] "Pumps.dbf" "Pumps.prj"
## [15] "Pumps.sbx" "Pumps.shp"
## [17] "Pumps.shx" "README.txt"
## [19] "SnowMap.tfw" "SnowMap.tif"
## [21] "SnowMap.tif.aux.xml" "SnowMap.tif.ovr"Terlihat pada output di atas bahwa folder tersebut memuat beberapa shapefile, di antaranya terdapat 6 file dengan nama Cholera_Deathsdan 5 file bernama Pumps. Kedua set data tersebut dikenal sebagai layer.
ogrListLayers(dsn)## [1] "Cholera_Deaths" "Pumps"
## attr(,"driver")
## [1] "ESRI Shapefile"
## attr(,"nlayers")
## [1] 2Kita dapat menggunakan fungsi ogrInfo() untuk mengetahui informasi mengenai layer tersebut.
ogrInfo(dsn, layer = "Cholera_Deaths")## Source: "D:\Dept.STK\Courses\Sains Data\Praktikum\P3\SnowGIS_SHP", layer: "Cholera_Deaths"
## Driver: ESRI Shapefile; number of rows: 250
## Feature type: wkbPoint with 2 dimensions
## Extent: (529160.3 180857.9) - (529655.9 181306.2)
## CRS: +proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +units=m +no_defs
## LDID: 87
## Number of fields: 2
## name type length typeName
## 1 Id 0 6 Integer
## 2 Count 0 4 IntegerImporting Spatial Data
Fungsi readOGR() dapat digunakan untuk membaca data shapefile.
CholeraDeaths <- readOGR(dsn, layer = "Cholera_Deaths")## OGR data source with driver: ESRI Shapefile
## Source: "D:\Dept.STK\Courses\Sains Data\Praktikum\P3\SnowGIS_SHP", layer: "Cholera_Deaths"
## with 250 features
## It has 2 fieldssummary(CholeraDeaths)## Object of class SpatialPointsDataFrame
## Coordinates:
## min max
## coords.x1 529160.3 529655.9
## coords.x2 180857.9 181306.2
## Is projected: TRUE
## proj4string :
## [+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000
## +y_0=-100000 +ellps=airy +units=m +no_defs]
## Number of points: 250
## Data attributes:
## Id Count
## Min. :0 Min. : 1.000
## 1st Qu.:0 1st Qu.: 1.000
## Median :0 Median : 1.000
## Mean :0 Mean : 1.956
## 3rd Qu.:0 3rd Qu.: 2.000
## Max. :0 Max. :15.000Selanjutnya kita dapat memeriksa class dari data CholeraDeaths tersebut.
class(CholeraDeaths)## [1] "SpatialPointsDataFrame"
## attr(,"package")
## [1] "sp"Data tersebut merupakan SpatialPointsDataFrame yang termasuk S4 class, maka untuk mengakses data slot perlu digunakan notasi @.
str(CholeraDeaths@data)## 'data.frame': 250 obs. of 2 variables:
## $ Id : int 0 0 0 0 0 0 0 0 0 0 ...
## $ Count: int 3 2 1 1 4 2 2 2 3 2 ...Making Map
Fungsi plot() dapat digunakan untuk membuat grafik paling sederhana dari data CholeraDeaths.
par(mfrow=c(1,2))
plot(CholeraDeaths)
plot(CholeraDeaths, pch=20, col="steelblue")
Perhatikan bahwa plot di atas hanya menunjukkan sebaran titik spasial, tanpa memberikan informasi yang jelas tentang lokasi data tersebut. Jika kita memiliki peta dalam bentuk data polygon, kita dapat mengimpor data tersebut dengan cara yang sama (seandainya datanya berupa shapefile), kemudian kita plot peta baru kemudian plot data titik seperti di atas.
Alternatif lainnya jika kita tidak ingin menggunakan peta polygon dari shapefile, kita dapat menggunakan beberapa package yang tersedia di R software, seperti ggmap, OpenStreetMap, leaflet, atau yang lain. Namun perhatikan bahwa untuk bisa menggunakan package OpenStreetMap, Anda harus memastikan bahwa jika Anda menggunakan R 64-bit maka Java yang terinstall di PC Anda juga harus sesuai, yaitu 64-bit.
Berikut ini akan ditunjukkan salah satu cara menampilkan peta dengan memanfaatkan package leaflet.
library(leaflet)
map <- leaflet() %>% setView(lng = -0.13659, lat =51.51328 , zoom = 12)
map %>% addTiles() head(coordinates(CholeraDeaths))## coords.x1 coords.x2
## [1,] 529308.7 181031.4
## [2,] 529312.2 181025.2
## [3,] 529314.4 181020.3
## [4,] 529317.4 181014.3
## [5,] 529320.7 181007.9
## [6,] 529336.7 181006.0Seperti terlihat di atas, koordinat pada data CholeraDeaths diukur pada skala yang berbeda dengan peta yang diambil dari package leaflet. Terdapat beberapa macam coordinate reference system (CRS), beberapa di antaranya yang cukup populer adalah suatu set EPSG (European Petroleum Survey Group) berikut:
EPSG:4326 juga dikenal sebagai WGS84, ukuran standard yang digunakan pada sistem GPS dan Google Earth.
EPSG:3857 digunakan pada Google Maps, Open Street Maps, dsb.
EPSG:27700 juga dikenal sebagai OSGB 1936, atau British National Grid: United Kingdom Ordnance Survey.
cholera_latlong <- CholeraDeaths %>%
spTransform(CRS("+init=epsg:4326"))
leaflet(data = CholeraDeaths) %>%
addTiles() %>%
addMarkers(cholera_latlong@coords[,1], cholera_latlong@coords[,2])Dapat dilihat di atas, bahwa setelah koordinatnya disamakan, kita dapat menampilkan data CholeraDeaths pada peta yang diperoleh dari Open Street Map melalui package leaflet.
References
Agafonkin, V. (n.d.). Leaflet for R - Markers. rstudio.github.io. Retrieved from https://rstudio.github.io/leaflet/markers.html
Baumer, B.S., Kaplan, D.T., Horton, N.J. 2017. Modern Data Science with R. CRC Press.
Contributors Data Carpenters. (2020, August 12). SQL databases and R. Data Carpentry. Retrieved from https://datacarpentry.org/R-ecology-lesson/05-r-and-databases.html#Introduction
RStudio. (n.d.). Databases using dplyr. Databases using R. Retrieved from https://db.rstudio.com/dplyr
UQ SLC Digital Team. (2020, April 16). Creating maps using R. Language Technology and Data Analysis Laboratory (LADAL). Retrieved from https://slcladal.github.io/maps.html
Wickham, H., & Ruiz, E. (n.d.). Introduction to dbplyr. dbplyr. Retrieved from https://dbplyr.tidyverse.org/