Suatu query merupakan perintah/permintaan/cara untuk mengambil suatu data atau informasi dari database, kemudian menampilkannya dalam bentuk tabel. Menggunakan query, kita hanya menampilkan data/informasi yang kita inginkan, bukan keseluruhan database.
Query dilakukan dengan menggunakan perangkat lunak untuk mengorganisasi database seperti, yang sering digunakan, MySQL. Namun, proses query dapat pula dilakukan menggunakan sotfware seperti R, python, SAS. Dalam R, package dplyr digunakan untuk melakukan query.
dplyr?Umumnya dalam melakukan query memang digunakan SQL. Namun, semakin berkembangnya teknologi, kebutuhan data tidak sekedar terhenti di proses query. Praktisnya, setelah query, kita dapat melakukan analisis terhadap data tersebut. Inilah yang tidak didukung oleh SQL, yang bahkan tidak dapat melakukan analisis sederhana seperti rata-rata untuk multivariabel.
Adapun perintah dalam dplyr (dibandingkan dengan perintah pada SQL) adalah sebagai berikut:
| Fungsi dalam dplyr | Fungsi dalam SQL | Keterangan |
|---|---|---|
select() |
SELECT | Menyeleksi kolom variabel |
filter() |
WHERE | Menyaring (filter) baris |
group_by() |
GROUP_BY | Mengelompokkan data |
summarise() |
tidak ada | Merangkum data |
arrange() |
ORDER_BY | Mengurutkan data |
mutate() |
COLUMN ALIAS | Membuat kolom baru |
join() |
JOIN | Menggabungkan data frame |
Untuk memulai, install package dplyr pada R atau Rstudio dengan perintah install.package(“dplyr”) atau dengan interface install yang telah tersedia. Lakukan pemuatan (loading) package dalam R:
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, unionSebagai contoh, gunakan data berikut mengenai jam tidur berbagai jenis mamalia. Data tersebut memiliki 83 baris dan 11 variabel. Unduhlah data tersebut, kemudian baca ke dalam R:
library(downloader)
urlku <- "https://raw.githubusercontent.com/genomicsclass/dagdata/master/inst/extdata/msleep_ggplot2.csv"
namafile <- "msleep_ggplot2.csv"
if (!file.exists(namafile)) download(urlku, namafile)
msleep <- read.csv("msleep_ggplot2.csv")
head(msleep)## name genus vore order conservation
## 1 Cheetah Acinonyx carni Carnivora lc
## 2 Owl monkey Aotus omni Primates <NA>
## 3 Mountain beaver Aplodontia herbi Rodentia nt
## 4 Greater short-tailed shrew Blarina omni Soricomorpha lc
## 5 Cow Bos herbi Artiodactyla domesticated
## 6 Three-toed sloth Bradypus herbi Pilosa <NA>
## sleep_total sleep_rem sleep_cycle awake brainwt bodywt
## 1 12.1 NA NA 11.9 NA 50.000
## 2 17.0 1.8 NA 7.0 0.01550 0.480
## 3 14.4 2.4 NA 9.6 NA 1.350
## 4 14.9 2.3 0.1333333 9.1 0.00029 0.019
## 5 4.0 0.7 0.6666667 20.0 0.42300 600.000
## 6 14.4 2.2 0.7666667 9.6 NA 3.850Anda dapat melakukan pengunduhan secara manual, kemudian menyimpan data tersebut dalam ekstensi csv. Selah itu, gunakan perintah read.csv(file = " "). Untuk memeriksa dimensi data:
dim(msleep)## [1] 83 11Data memiliki banyak baris 83, dengan 11 kolom. Untuk mengetahui nama (variabel) dari kesebelas kolom tersebut:
colnames(msleep)## [1] "name" "genus" "vore" "order"
## [5] "conservation" "sleep_total" "sleep_rem" "sleep_cycle"
## [9] "awake" "brainwt" "bodywt"Keterangan kolom (variabel):
| Nama Variabel | Keterangan |
|---|---|
| name | Nama mamalia |
| genus | Rank dalam taksonomi |
| vore | Jenis mamalia: carnivore, herbivore, atau omnivore |
| order | Rank dalam taksonomi |
| conservation | Status konservasi dari mamalia |
| sleep_total | Total waktu tidur (dalam jam) |
| sleep_rem | Lama waktu fase tidur Rapid-Eye-Movement (REM) (dalam jam) |
| sleep_cycle | Lama sikulus tidur (dalam jam) |
| awake | Total waktu terbangun (dalam jam) |
| brainwt | Berat otak (dalam kg) |
| bodywt | Berat tubuh (dalam kg) |
dplyrselect()Lakukan seleksi terhadap kolom dengan sintak: select(data, …), isikan … dengan nama variabel yang ingin dipilih.
mamalia.se <- dplyr::select(msleep, name, sleep_total, awake)
head(mamalia.se, n = 5)## name sleep_total awake
## 1 Cheetah 12.1 11.9
## 2 Owl monkey 17.0 7.0
## 3 Mountain beaver 14.4 9.6
## 4 Greater short-tailed shrew 14.9 9.1
## 5 Cow 4.0 20.0Untuk menyeleksi data msleep dari kolom “name” hingga kolom “order”, gunakan tanda titik dua(:), contoh:
mamalia.se <- dplyr::select(msleep, name:order)
head(mamalia.se, n = 5)## name genus vore order
## 1 Cheetah Acinonyx carni Carnivora
## 2 Owl monkey Aotus omni Primates
## 3 Mountain beaver Aplodontia herbi Rodentia
## 4 Greater short-tailed shrew Blarina omni Soricomorpha
## 5 Cow Bos herbi ArtiodactylaUntuk menyeleksi data msleep dengan kolom yang berawalan string “sl”, dapat dilakukan dengan starts_with(), contoh:
mamalia.se <- dplyr::select(msleep, starts_with("sl"))
head(mamalia.se, n = 5)## sleep_total sleep_rem sleep_cycle
## 1 12.1 NA NA
## 2 17.0 1.8 NA
## 3 14.4 2.4 NA
## 4 14.9 2.3 0.1333333
## 5 4.0 0.7 0.6666667Beberapa fungsi yang dapat digunakan dalam select():
1. ends_with() : menyeleksi kolom dengan akhiran suatu karakter string
2. contains() : menyeleksi kolom yang mengandung suatu karakter string
3. matches() : menyeleksi kolom yang cocok dengan ekspresi
4. one_of() : menyeleksi nama kolom dari sekelompok/grup nama
Untuk menyeleksi data msleep, kolom yang cocok dengan karakter “se”
mamalia.se <- dplyr::select(msleep, matches(".se."))
head(mamalia.se, n = 5)## conservation
## 1 lc
## 2 <NA>
## 3 nt
## 4 lc
## 5 domesticatedfilter()Lakukan seleksi mamalia yang tidur lebir dari sama dengan 16 jam:
mamalia.fi <- dplyr::filter(msleep, sleep_total >= 16)
print(mamalia.fi)## name genus vore order conservation
## 1 Owl monkey Aotus omni Primates <NA>
## 2 Long-nosed armadillo Dasypus carni Cingulata lc
## 3 North American Opossum Didelphis omni Didelphimorphia lc
## 4 Big brown bat Eptesicus insecti Chiroptera lc
## 5 Thick-tailed opposum Lutreolina carni Didelphimorphia lc
## 6 Little brown bat Myotis insecti Chiroptera <NA>
## 7 Giant armadillo Priodontes insecti Cingulata en
## 8 Arctic ground squirrel Spermophilus herbi Rodentia lc
## sleep_total sleep_rem sleep_cycle awake brainwt bodywt
## 1 17.0 1.8 NA 7.0 0.01550 0.480
## 2 17.4 3.1 0.3833333 6.6 0.01080 3.500
## 3 18.0 4.9 0.3333333 6.0 0.00630 1.700
## 4 19.7 3.9 0.1166667 4.3 0.00030 0.023
## 5 19.4 6.6 NA 4.6 NA 0.370
## 6 19.9 2.0 0.2000000 4.1 0.00025 0.010
## 7 18.1 6.1 NA 5.9 0.08100 60.000
## 8 16.6 NA NA 7.4 0.00570 0.920Lakukan seleksi mamalia yang tidur >= 16 jam dan yang memiliki berat badan >= 2 kg:
mamalia.fi <- dplyr::filter(msleep, sleep_total >= 16, bodywt >= 2)
print(mamalia.fi)## name genus vore order conservation
## 1 Long-nosed armadillo Dasypus carni Cingulata lc
## 2 Giant armadillo Priodontes insecti Cingulata en
## sleep_total sleep_rem sleep_cycle awake brainwt bodywt
## 1 17.4 3.1 0.3833333 6.6 0.0108 3.5
## 2 18.1 6.1 NA 5.9 0.0810 60.0Lakukan seleksi mamalia dengan order “Primates” dan “Rodentia”
mamalia.fi <- dplyr::filter(msleep, order %in% c("Primates", "Rodentia"))
print(mamalia.fi)## name genus vore order
## 1 Owl monkey Aotus omni Primates
## 2 Mountain beaver Aplodontia herbi Rodentia
## 3 Vesper mouse Calomys <NA> Rodentia
## 4 Guinea pig Cavis herbi Rodentia
## 5 Grivet Cercopithecus omni Primates
## 6 Chinchilla Chinchilla herbi Rodentia
## 7 African giant pouched rat Cricetomys omni Rodentia
## 8 Patas monkey Erythrocebus omni Primates
## 9 Western american chipmunk Eutamias herbi Rodentia
## 10 Galago Galago omni Primates
## 11 Human Homo omni Primates
## 12 Mongoose lemur Lemur herbi Primates
## 13 Macaque Macaca omni Primates
## 14 Mongolian gerbil Meriones herbi Rodentia
## 15 Golden hamster Mesocricetus herbi Rodentia
## 16 Vole Microtus herbi Rodentia
## 17 House mouse Mus herbi Rodentia
## 18 Round-tailed muskrat Neofiber herbi Rodentia
## 19 Slow loris Nyctibeus carni Primates
## 20 Degu Octodon herbi Rodentia
## 21 Northern grasshopper mouse Onychomys carni Rodentia
## 22 Chimpanzee Pan omni Primates
## 23 Baboon Papio omni Primates
## 24 Potto Perodicticus omni Primates
## 25 Deer mouse Peromyscus <NA> Rodentia
## 26 Laboratory rat Rattus herbi Rodentia
## 27 African striped mouse Rhabdomys omni Rodentia
## 28 Squirrel monkey Saimiri omni Primates
## 29 Cotton rat Sigmodon herbi Rodentia
## 30 Mole rat Spalax <NA> Rodentia
## 31 Arctic ground squirrel Spermophilus herbi Rodentia
## 32 Thirteen-lined ground squirrel Spermophilus herbi Rodentia
## 33 Golden-mantled ground squirrel Spermophilus herbi Rodentia
## 34 Eastern american chipmunk Tamias herbi Rodentia
## conservation sleep_total sleep_rem sleep_cycle awake brainwt bodywt
## 1 <NA> 17.0 1.8 NA 7.0 0.01550 0.480
## 2 nt 14.4 2.4 NA 9.6 NA 1.350
## 3 <NA> 7.0 NA NA 17.0 NA 0.045
## 4 domesticated 9.4 0.8 0.2166667 14.6 0.00550 0.728
## 5 lc 10.0 0.7 NA 14.0 NA 4.750
## 6 domesticated 12.5 1.5 0.1166667 11.5 0.00640 0.420
## 7 <NA> 8.3 2.0 NA 15.7 0.00660 1.000
## 8 lc 10.9 1.1 NA 13.1 0.11500 10.000
## 9 <NA> 14.9 NA NA 9.1 NA 0.071
## 10 <NA> 9.8 1.1 0.5500000 14.2 0.00500 0.200
## 11 <NA> 8.0 1.9 1.5000000 16.0 1.32000 62.000
## 12 vu 9.5 0.9 NA 14.5 NA 1.670
## 13 <NA> 10.1 1.2 0.7500000 13.9 0.17900 6.800
## 14 lc 14.2 1.9 NA 9.8 NA 0.053
## 15 en 14.3 3.1 0.2000000 9.7 0.00100 0.120
## 16 <NA> 12.8 NA NA 11.2 NA 0.035
## 17 nt 12.5 1.4 0.1833333 11.5 0.00040 0.022
## 18 nt 14.6 NA NA 9.4 NA 0.266
## 19 <NA> 11.0 NA NA 13.0 0.01250 1.400
## 20 lc 7.7 0.9 NA 16.3 NA 0.210
## 21 lc 14.5 NA NA 9.5 NA 0.028
## 22 <NA> 9.7 1.4 1.4166667 14.3 0.44000 52.200
## 23 <NA> 9.4 1.0 0.6666667 14.6 0.18000 25.235
## 24 lc 11.0 NA NA 13.0 NA 1.100
## 25 <NA> 11.5 NA NA 12.5 NA 0.021
## 26 lc 13.0 2.4 0.1833333 11.0 0.00190 0.320
## 27 <NA> 8.7 NA NA 15.3 NA 0.044
## 28 <NA> 9.6 1.4 NA 14.4 0.02000 0.743
## 29 <NA> 11.3 1.1 0.1500000 12.7 0.00118 0.148
## 30 <NA> 10.6 2.4 NA 13.4 0.00300 0.122
## 31 lc 16.6 NA NA 7.4 0.00570 0.920
## 32 lc 13.8 3.4 0.2166667 10.2 0.00400 0.101
## 33 lc 15.9 3.0 NA 8.1 NA 0.205
## 34 <NA> 15.8 NA NA 8.2 NA 0.112Dapat pula digunakan operasi logika: < (kurang dari), > (lebih dari), <= (kurang dari sama dengan), >= (lebih dari sama dengan), == (sama dengan), != (tidak sama dengan), %in% (dalam).
Pipe operator: %>% dalam dplyr diimport dari package magrittr. Pipe digunakan untuk menyalurkan output dari suatu fungsi ke input fungsi lain. Alih-alih menggunakan fungsi tersarang, digunakanlah pipe yang dibaca dari kiri ke kanan. Contoh:
head(select(msleep, name, sleep_total), n = 5)## name sleep_total
## 1 Cheetah 12.1
## 2 Owl monkey 17.0
## 3 Mountain beaver 14.4
## 4 Greater short-tailed shrew 14.9
## 5 Cow 4.0dengan menggunakan pipe, dapat dituliskan sebagai berikut:
msleep %>%
select(name, sleep_total) %>%
head(n = 5)## name sleep_total
## 1 Cheetah 12.1
## 2 Owl monkey 17.0
## 3 Mountain beaver 14.4
## 4 Greater short-tailed shrew 14.9
## 5 Cow 4.0arrage()Untuk mengurutkan suatu kolom, gunakan arrange(). Urutkan data msleep, berdasarkan variabel sleep_total:
msleep.ar <- arrange(msleep, sleep_total)
head(msleep.ar, n = 10)## name genus vore order conservation
## 1 Giraffe Giraffa herbi Artiodactyla cd
## 2 Pilot whale Globicephalus carni Cetacea cd
## 3 Horse Equus herbi Perissodactyla domesticated
## 4 Roe deer Capreolus herbi Artiodactyla lc
## 5 Donkey Equus herbi Perissodactyla domesticated
## 6 African elephant Loxodonta herbi Proboscidea vu
## 7 Caspian seal Phoca carni Carnivora vu
## 8 Sheep Ovis herbi Artiodactyla domesticated
## 9 Asian elephant Elephas herbi Proboscidea en
## 10 Cow Bos herbi Artiodactyla domesticated
## sleep_total sleep_rem sleep_cycle awake brainwt bodywt
## 1 1.9 0.4 NA 22.10 NA 899.995
## 2 2.7 0.1 NA 21.35 NA 800.000
## 3 2.9 0.6 1.0000000 21.10 0.6550 521.000
## 4 3.0 NA NA 21.00 0.0982 14.800
## 5 3.1 0.4 NA 20.90 0.4190 187.000
## 6 3.3 NA NA 20.70 5.7120 6654.000
## 7 3.5 0.4 NA 20.50 NA 86.000
## 8 3.8 0.6 NA 20.20 0.1750 55.500
## 9 3.9 NA NA 20.10 4.6030 2547.000
## 10 4.0 0.7 0.6666667 20.00 0.4230 600.000Gunakan pipe, pilih variabel: name, order, sleep_total, dan awake. Kemudian, lakukan pengurutan berdasarkan order dan sleep_total. Tambahkan filter untuk sleep_total >= 12
msleep %>%
select(name, order, sleep_total, awake) %>%
arrange(order, sleep_total) %>%
filter(sleep_total >= 12) %>%
head(10)## name order sleep_total awake
## 1 Tenrec Afrosoricida 15.6 8.4
## 2 Cheetah Carnivora 12.1 11.9
## 3 Domestic cat Carnivora 12.5 11.5
## 4 Arctic fox Carnivora 12.5 11.5
## 5 Lion Carnivora 13.5 10.5
## 6 Tiger Carnivora 15.8 8.2
## 7 Big brown bat Chiroptera 19.7 4.3
## 8 Little brown bat Chiroptera 19.9 4.1
## 9 Long-nosed armadillo Cingulata 17.4 6.6
## 10 Giant armadillo Cingulata 18.1 5.9Gunakan desc() untuk mengurutkan dari yang terbesar ke yang terkecil:
msleep %>%
select(name, order, sleep_total, awake) %>%
arrange(order, desc(sleep_total)) %>%
filter(sleep_total >= 12) %>%
head(10)## name order sleep_total awake
## 1 Tenrec Afrosoricida 15.6 8.4
## 2 Tiger Carnivora 15.8 8.2
## 3 Lion Carnivora 13.5 10.5
## 4 Domestic cat Carnivora 12.5 11.5
## 5 Arctic fox Carnivora 12.5 11.5
## 6 Cheetah Carnivora 12.1 11.9
## 7 Little brown bat Chiroptera 19.9 4.1
## 8 Big brown bat Chiroptera 19.7 4.3
## 9 Giant armadillo Cingulata 18.1 5.9
## 10 Long-nosed armadillo Cingulata 17.4 6.6mutate()Buat kolom rem_proportion yang merupakan rasio sleep_rem terhadap sleep_total:
msleep %>%
mutate(rem_proportion = sleep_rem/sleep_total) %>%
head(5)## name genus vore order conservation
## 1 Cheetah Acinonyx carni Carnivora lc
## 2 Owl monkey Aotus omni Primates <NA>
## 3 Mountain beaver Aplodontia herbi Rodentia nt
## 4 Greater short-tailed shrew Blarina omni Soricomorpha lc
## 5 Cow Bos herbi Artiodactyla domesticated
## sleep_total sleep_rem sleep_cycle awake brainwt bodywt rem_proportion
## 1 12.1 NA NA 11.9 NA 50.000 NA
## 2 17.0 1.8 NA 7.0 0.01550 0.480 0.1058824
## 3 14.4 2.4 NA 9.6 NA 1.350 0.1666667
## 4 14.9 2.3 0.1333333 9.1 0.00029 0.019 0.1543624
## 5 4.0 0.7 0.6666667 20.0 0.42300 600.000 0.1750000Apabila ingin menambahkan kolom lain, bodywt_gr:
## [1] "name" "genus" "vore" "order"
## [5] "conservation" "sleep_total" "sleep_rem" "sleep_cycle"
## [9] "awake" "brainwt" "bodywt" "rem_proportion"
## [13] "bodywt_gr"summarise()Untuk melakukan perhitungan statistika dalam suatu kolom, gunakan summarise(). Beberapa fungsi statistika sederhana yang dapat digunakan adalah mean(), median(), sd(), min(), max(), n() untuk mengetahui panjang dari vektor, n_distinct() untuk mengetahui jumlah nilai yang berbeda dalam suatu vektor, first() mengetahui nilai pertama suatu vektor, dan last() mengetahui nilai terakhir suatu vektor.
Tampilkan nilai rata-rata, median, minimum, dan maximum dari variabel sleep_total:
msleep %>%
summarise(rata2 = mean(sleep_total), median = median(sleep_total),
minimum = min(sleep_total), maximum = max(sleep_total))## rata2 median minimum maximum
## 1 10.43373 10.1 1.9 19.9Memilih beberapa variabel, misalnya variabel dari sleep_total hingga awake untuk dihitung rata-rata dan mediannya:
msleep %>%
summarise_at(vars(sleep_total:awake), funs(mean, median), na.rm = TRUE)## sleep_total_mean sleep_rem_mean sleep_cycle_mean awake_mean
## 1 10.43373 1.87541 0.4395833 13.56747
## sleep_total_median sleep_rem_median sleep_cycle_median awake_median
## 1 10.1 1.5 0.3333333 13.9Fungsi vars() digunakan untuk mendaftar semua variabel yang diinginkan, sedangkan funs() digunakan untuk mendaftar semua fungsi perhitungan statistika. Selain summarise_at dapat juga digunakan summarise_all, summarise_if.
Menghitung rangkuman statistik, apabila variabelnya numerik:
msleep %>%
summarise_if(is.numeric, funs(n_distinct,mean, median), na.rm = TRUE)## sleep_total_n_distinct sleep_rem_n_distinct sleep_cycle_n_distinct
## 1 65 32 22
## awake_n_distinct brainwt_n_distinct bodywt_n_distinct sleep_total_mean
## 1 65 53 82 10.43373
## sleep_rem_mean sleep_cycle_mean awake_mean brainwt_mean bodywt_mean
## 1 1.87541 0.4395833 13.56747 0.2815814 166.1363
## sleep_total_median sleep_rem_median sleep_cycle_median awake_median
## 1 10.1 1.5 0.3333333 13.9
## brainwt_median bodywt_median
## 1 0.0124 1.67group_by()Fungsi group_by() berhubungan dengan konsep “split-apply-combine”. Misalkan, kita menginginkan untuk memisah (split) data frame menggunakan suatu variabel (contoh order taksonomi), mengaplikasikan (apply) fungsi secara individual dalam data frame, dan mengkombinasi (combine) outputnya.
Pisahkan (split) data frame berdasarkan variabel order, kemudian tampilan ringkasan statistika seperti sebelumnya:
msleep %>%
group_by(order) %>%
summarise(rata2 = mean(sleep_total), median = median(sleep_total),
minimum = min(sleep_total), maximum = max(sleep_total))## # A tibble: 19 x 5
## order rata2 median minimum maximum
## <fctr> <dbl> <dbl> <dbl> <dbl>
## 1 Afrosoricida 15.6 15.6 15.6 15.6
## 2 Artiodactyla 4.52 3.90 1.90 9.10
## 3 Carnivora 10.1 10.2 3.50 15.8
## 4 Cetacea 4.50 5.20 2.70 5.60
## 5 Chiroptera 19.8 19.8 19.7 19.9
## 6 Cingulata 17.8 17.8 17.4 18.1
## 7 Didelphimorphia 18.7 18.7 18.0 19.4
## 8 Diprotodontia 12.4 12.4 11.1 13.7
## 9 Erinaceomorpha 10.2 10.2 10.1 10.3
## 10 Hyracoidea 5.67 5.40 5.30 6.30
## 11 Lagomorpha 8.40 8.40 8.40 8.40
## 12 Monotremata 8.60 8.60 8.60 8.60
## 13 Perissodactyla 3.47 3.10 2.90 4.40
## 14 Pilosa 14.4 14.4 14.4 14.4
## 15 Primates 10.5 9.90 8.00 17.0
## 16 Proboscidea 3.60 3.60 3.30 3.90
## 17 Rodentia 12.5 12.9 7.00 16.6
## 18 Scandentia 8.90 8.90 8.90 8.90
## 19 Soricomorpha 11.1 10.3 8.40 14.9Daftar pustaka
1. Rafael Irizarry and Michael Love, dplyr tutorial, http://genomicsclass.github.io/book/pages/dplyr_tutorial.html.
2. Deepanshu Bhalla, Data Manipulation with dplyr (50 Examples), https://www.listendata.com/2016/08/dplyr-tutorial.html.