Tugas Praktikum 3 STA581 Sains Data

Tugas Praktikum 3.A

Tugas ini akan mempraktikkan penggunaan inner_join(), left_join(), right_join(), full_join(), semi_join(), dan anti_join() dengan menggunakan database chinook.

Membuat koneksi dengan database chinook :

library(tidyverse)
library(RSQLite)
library(DBI)

chinook<-DBI::dbConnect(RSQLite::SQLite(), "D:/Kuliah Pasca/2021/STA581-Sains Data/Pertemuan 2/chinook.db")

class(chinook)

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

RSQLite::dbListTables(chinook)

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

`inner_join()`

Penggunaan inner_join() untuk menampilkan artists dan albumnya :

tbl(chinook,"artists") %>%
  inner_join(tbl(chinook,"albums"),by = 'ArtistId') %>%
  select(Name,Title) %>%
  arrange(Name,Title)

`left join()`

Penggunaan left_join() untuk menampilkan nama Customer, InvoiceID, InvoiceDate dan Total invoicenya :

tbl(chinook,"customers") %>%
  left_join(tbl(chinook,"invoices"),by = 'CustomerId') %>%
  select(FirstName,LastName,InvoiceId,InvoiceDate,Total)

`right_join()`

Penggunaan right_join() untuk menampilkan nama judul album dengan lagunya (track) :

tbl(chinook,"tracks") %>%
  right_join(tbl(chinook,"albums"),by = 'AlbumId') %>%
  select(Name,Title)

`full_join()`

Penggunaan full_join() untuk menampilkan gabungan semua invoice dengan invoice items:

tbl(chinook,"invoices") %>%
  full_join(tbl(chinook,"invoice_items"),by = 'InvoiceId')

`semi_join()`

Penggunaan semi_join() untuk menampilkan artist yang bergenre Metal :

metal_tracks <- tbl(chinook,"tracks") %>%
  inner_join(tbl(chinook,"genres"),by = 'GenreId') %>%
  inner_join(tbl(chinook,"albums"),by = 'AlbumId') %>%
  filter(Name.y=="Metal")

tbl(chinook,"artists") %>%
  semi_join(metal_tracks,by = 'ArtistId')

`anti_join()`

Penggunaan anti_join() untuk menampilkan daftar lagu yang tidak ada pada invoice items :

tbl(chinook,"tracks") %>%
  anti_join(tbl(chinook,"invoice_items"),by = 'TrackId')

Tugas Praktikum 3.B

Membuat peta dari daerah masing-masing (Kecamatan, Kabupaten). Pada tugas ini akan ditampilkan data Usaha Mikro Kecil dan Menengah (UMKM) Kuliner di Kota Bogor berdasarkan kecamatannya untuk tahun 2019.

Impor data spasial kecamatan dari data Indonesia - Subnational Administrative Boundaries :

library(sf)
library(ggplot2)
library(tigris)
library(dplyr)

Admin3Kecamatan<-"D:/Kuliah Pasca/2021/STA581-Sains Data/Praktikum/Indonesia - Subnational Administrative Boundaries/idn_admbnda_adm3_bps_20200401.shp"
glimpse(Admin3Kecamatan)

##  chr "D:/Kuliah Pasca/2021/STA581-Sains Data/Praktikum/Indonesia - Subnational Administrative Boundaries/idn_admbnda_"| __truncated__

Admin3<-st_read(Admin3Kecamatan)

## Reading layer `idn_admbnda_adm3_bps_20200401' from data source 
##   `D:\Kuliah Pasca\2021\STA581-Sains Data\Praktikum\Indonesia - Subnational Administrative Boundaries\idn_admbnda_adm3_bps_20200401.shp' 
##   using driver `ESRI Shapefile'
## Simple feature collection with 7069 features and 16 fields
## Geometry type: MULTIPOLYGON
## Dimension:     XY
## Bounding box:  xmin: 95.01079 ymin: -11.00762 xmax: 141.0194 ymax: 6.07693
## Geodetic CRS:  WGS 84

glimpse(Admin3)

## Rows: 7,069
## Columns: 17
## $ Shape_Leng <dbl> 0.2798656, 0.7514001, 0.6900061, 0.6483629, 0.2437073, 1.35~
## $ Shape_Area <dbl> 0.003107633, 0.016925540, 0.024636382, 0.010761277, 0.00116~
## $ ADM3_EN    <chr> "2 X 11 Enam Lingkung", "2 X 11 Kayu Tanam", "Abab", "Abang~
## $ ADM3_PCODE <chr> "ID1306050", "ID1306052", "ID1612030", "ID5107050", "ID7471~
## $ ADM3_REF   <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,~
## $ ADM3ALT1EN <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,~
## $ ADM3ALT2EN <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,~
## $ ADM2_EN    <chr> "Padang Pariaman", "Padang Pariaman", "Penukal Abab Lematan~
## $ ADM2_PCODE <chr> "ID1306", "ID1306", "ID1612", "ID5107", "ID7471", "ID9432",~
## $ ADM1_EN    <chr> "Sumatera Barat", "Sumatera Barat", "Sumatera Selatan", "Ba~
## $ ADM1_PCODE <chr> "ID13", "ID13", "ID16", "ID51", "ID74", "ID94", "ID94", "ID~
## $ ADM0_EN    <chr> "Indonesia", "Indonesia", "Indonesia", "Indonesia", "Indone~
## $ ADM0_PCODE <chr> "ID", "ID", "ID", "ID", "ID", "ID", "ID", "ID", "ID", "ID",~
## $ date       <date> 2019-12-20, 2019-12-20, 2019-12-20, 2019-12-20, 2019-12-20~
## $ validOn    <date> 2020-04-01, 2020-04-01, 2020-04-01, 2020-04-01, 2020-04-01~
## $ validTo    <date> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA~
## $ geometry   <MULTIPOLYGON [°]> MULTIPOLYGON (((100.2811 -0..., MULTIPOLYGON (~

Impor data UMKM yang sudah diberikan kode kecamatan :

KotaBogorUMK<-read.csv("D:/Kuliah Pasca/2021/STA581-Sains Data/Praktikum/pertemuan 3/BogorDataUKM.csv",
                         header=TRUE, sep=";")

Gabungkan data spasial dengan data UMKM :

merged_KotaBogorUMK <- geo_join(spatial_data=Admin3, 
                                  data_frame=KotaBogorUMK, by_sp="ADM3_PCODE", 
                                  by_df="ADM3_PCODE", how = "inner")

Menampilkan plot peta Chloropleth :

col_grad <- RColorBrewer::brewer.pal(5,"Spectral")
pDATA<-ggplot()+
  geom_sf(data=merged_KotaBogorUMK,aes(fill=UMKM_Kuliner))+
  scale_fill_gradientn(colours=col_grad)+
  labs(title="Jumlah UMKM Kuliner Kota Bogor Tahun 2019")
pDATA