Tugas 3 Responsi Data Sains
Nila Lestari / G1501211001
9/9/2021
library(tidyverse)## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --## v ggplot2 3.3.5 v purrr 0.3.4
## v tibble 3.1.3 v dplyr 1.0.7
## v tidyr 1.1.3 v stringr 1.4.0
## v readr 2.0.1 v forcats 0.5.1## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()library(RSQLite)
library(DBI)- Menggabungkan Dua Buah Tabel dari Database Chinook
# Memanggil Database
Chinook<-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/db/Chinook/Chinook.db")#Menampilkan Tabel
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"# Mengakses Tabel yang diperlukan
albums<-dplyr::tbl(Chinook,"albums")
class(albums)## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"artists<-dplyr::tbl(Chinook,"artists")
class(artists)## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"media_types<-dplyr::tbl(Chinook,"media_types")
class(media_types)## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"genres<-dplyr::tbl(Chinook,"genres")
class(genres)## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"# Menampilkan Tabel albums
albums## # Source: table<albums> [?? x 3]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook\chinook.db]
## AlbumId Title ArtistId
## <int> <chr> <int>
## 1 1 For Those About To Rock We Salute You 1
## 2 2 Balls to the Wall 2
## 3 3 Restless and Wild 2
## 4 4 Let There Be Rock 1
## 5 5 Big Ones 3
## 6 6 Jagged Little Pill 4
## 7 7 Facelift 5
## 8 8 Warner 25 Anos 6
## 9 9 Plays Metallica By Four Cellos 7
## 10 10 Audioslave 8
## # ... with more rows# Menampilkan Tabel artists
artists## # Source: table<artists> [?? x 2]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook\chinook.db]
## ArtistId Name
## <int> <chr>
## 1 1 AC/DC
## 2 2 Accept
## 3 3 Aerosmith
## 4 4 Alanis Morissette
## 5 5 Alice In Chains
## 6 6 Antônio Carlos Jobim
## 7 7 Apocalyptica
## 8 8 Audioslave
## 9 9 BackBeat
## 10 10 Billy Cobham
## # ... with more rows# Menampilkan Tabel media_types
media_types## # Source: table<media_types> [?? x 2]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook\chinook.db]
## MediaTypeId Name
## <int> <chr>
## 1 1 MPEG audio file
## 2 2 Protected AAC audio file
## 3 3 Protected MPEG-4 video file
## 4 4 Purchased AAC audio file
## 5 5 AAC audio file# Menampilkan Tabel genres
genres## # Source: table<genres> [?? x 2]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook\chinook.db]
## GenreId Name
## <int> <chr>
## 1 1 Rock
## 2 2 Jazz
## 3 3 Metal
## 4 4 Alternative & Punk
## 5 5 Rock And Roll
## 6 6 Blues
## 7 7 Latin
## 8 8 Reggae
## 9 9 Pop
## 10 10 Soundtrack
## # ... with more rowsalbums %>% inner_join(artists)## Joining, by = "ArtistId"## # Source: lazy query [?? x 4]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook\chinook.db]
## AlbumId Title ArtistId Name
## <int> <chr> <int> <chr>
## 1 1 For Those About To Rock We Salute You 1 AC/DC
## 2 2 Balls to the Wall 2 Accept
## 3 3 Restless and Wild 2 Accept
## 4 4 Let There Be Rock 1 AC/DC
## 5 5 Big Ones 3 Aerosmith
## 6 6 Jagged Little Pill 4 Alanis Morissette
## 7 7 Facelift 5 Alice In Chains
## 8 8 Warner 25 Anos 6 Antônio Carlos Jobim
## 9 9 Plays Metallica By Four Cellos 7 Apocalyptica
## 10 10 Audioslave 8 Audioslave
## # ... with more rowsalbums %>% left_join(artists)## Joining, by = "ArtistId"## # Source: lazy query [?? x 4]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook\chinook.db]
## AlbumId Title ArtistId Name
## <int> <chr> <int> <chr>
## 1 1 For Those About To Rock We Salute You 1 AC/DC
## 2 2 Balls to the Wall 2 Accept
## 3 3 Restless and Wild 2 Accept
## 4 4 Let There Be Rock 1 AC/DC
## 5 5 Big Ones 3 Aerosmith
## 6 6 Jagged Little Pill 4 Alanis Morissette
## 7 7 Facelift 5 Alice In Chains
## 8 8 Warner 25 Anos 6 Antônio Carlos Jobim
## 9 9 Plays Metallica By Four Cellos 7 Apocalyptica
## 10 10 Audioslave 8 Audioslave
## # ... with more rowsright_join(albums, artists)## Joining, by = "ArtistId"## # Source: lazy query [?? x 4]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook\chinook.db]
## AlbumId Title ArtistId Name
## <int> <chr> <int> <chr>
## 1 1 For Those About To Rock We Salute You 1 AC/DC
## 2 4 Let There Be Rock 1 AC/DC
## 3 2 Balls to the Wall 2 Accept
## 4 3 Restless and Wild 2 Accept
## 5 5 Big Ones 3 Aerosmith
## 6 6 Jagged Little Pill 4 Alanis Morissette
## 7 7 Facelift 5 Alice In Chains
## 8 8 Warner 25 Anos 6 Antônio Carlos Jobim
## 9 34 Chill: Brazil (Disc 2) 6 Antônio Carlos Jobim
## 10 9 Plays Metallica By Four Cellos 7 Apocalyptica
## # ... with more rowsfull_join(albums, artists)## Joining, by = "ArtistId"## # Source: lazy query [?? x 4]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook\chinook.db]
## AlbumId Title ArtistId Name
## <int> <chr> <int> <chr>
## 1 NA <NA> 25 Milton Nascimento & Bebeto
## 2 NA <NA> 26 Azymuth
## 3 NA <NA> 28 João Gilberto
## 4 NA <NA> 29 Bebel Gilberto
## 5 NA <NA> 30 Jorge Vercilo
## 6 NA <NA> 31 Baby Consuelo
## 7 NA <NA> 32 Ney Matogrosso
## 8 NA <NA> 33 Luiz Melodia
## 9 NA <NA> 34 Nando Reis
## 10 NA <NA> 35 Pedro Luís & A Parede
## # ... with more rowssemi_join(albums, artists)## Joining, by = "ArtistId"## # Source: lazy query [?? x 3]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook\chinook.db]
## AlbumId Title ArtistId
## <int> <chr> <int>
## 1 1 For Those About To Rock We Salute You 1
## 2 2 Balls to the Wall 2
## 3 3 Restless and Wild 2
## 4 4 Let There Be Rock 1
## 5 5 Big Ones 3
## 6 6 Jagged Little Pill 4
## 7 7 Facelift 5
## 8 8 Warner 25 Anos 6
## 9 9 Plays Metallica By Four Cellos 7
## 10 10 Audioslave 8
## # ... with more rowsartists %>% anti_join(genres)## Joining, by = "Name"## # Source: lazy query [?? x 2]
## # Database: sqlite 3.36.0 [C:\sqlite\db\chinook\chinook.db]
## ArtistId Name
## <int> <chr>
## 1 1 AC/DC
## 2 2 Accept
## 3 3 Aerosmith
## 4 4 Alanis Morissette
## 5 5 Alice In Chains
## 6 6 Antônio Carlos Jobim
## 7 7 Apocalyptica
## 8 8 Audioslave
## 9 9 BackBeat
## 10 10 Billy Cobham
## # ... with more rows2. Membuat Peta
library(rgdal)## Loading required package: sp## rgdal: version: 1.5-23, (SVN revision 1121)
## Geospatial Data Abstraction Library extensions to R successfully loaded
## Loaded GDAL runtime: GDAL 3.2.1, released 2020/12/29
## Path to GDAL shared files: C:/Users/DELL/Documents/R/win-library/4.0/rgdal/gdal
## GDAL binary built with GEOS: TRUE
## Loaded PROJ runtime: Rel. 7.2.1, January 1st, 2021, [PJ_VERSION: 721]
## Path to PROJ shared files: C:/Users/DELL/Documents/R/win-library/4.0/rgdal/proj
## PROJ CDN enabled: FALSE
## Linking to sp version:1.4-5
## To mute warnings of possible GDAL/OSR exportToProj4() degradation,
## use options("rgdal_show_exportToProj4_warnings"="none") before loading rgdal.
## Overwritten PROJ_LIB was C:/Users/DELL/Documents/R/win-library/4.0/rgdal/projlibrary(sf)## Linking to GEOS 3.9.0, GDAL 3.2.1, PROJ 7.2.1library(ggplot2)
library(tigris)## To enable
## caching of data, set `options(tigris_use_cache = TRUE)` in your R script or .Rprofile.library(dplyr)#Load Data Spasial
Admin3Kecamatan<-"C:/sqlite/db/STA581/Tugas 3/idn_adm_bps_20200401_shp/idn_admbnda_adm3_bps_20200401.shp"
Admin3<-st_read(Admin3Kecamatan)## Reading layer `idn_admbnda_adm3_bps_20200401' from data source
## `C:\sqlite\db\STA581\Tugas 3\idn_adm_bps_20200401_shp\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 84Merge Data
#Menggabungkan Peta Bone dan Sulawesi
BoneSulselIndonesia <- read.csv("C:/sqlite/db/STA581/Tugas 3/BoneSulselIndonesia.csv", header= TRUE, sep=";")
merged_SulselIndonesia <- geo_join(spatial_data=Admin3,
data_frame=BoneSulselIndonesia, by_sp="ADM3_PCODE",
by_df="ADM3_PCODE", how = "inner")## Warning: We recommend using the dplyr::*_join() family of functions instead.mycol <- c("blue", "grey", "red", "green")pDATA<-ggplot()+
geom_sf(data=merged_SulselIndonesia,aes(fill=DATA))+
scale_fill_gradientn(colours=mycol)+
labs(title="Bone dan Sulawesi")
pDATA
#——————————————————–
# Menampilkan Peta Bone dan Sulawesi Selatan
BoneSulsel<-read.csv("C:/sqlite/db/STA581/Tugas 3/BoneSulsel.csv", header=TRUE, sep=";")
merged_BoneSulsel <- geo_join(spatial_data=Admin3,
data_frame=BoneSulsel, by_sp="ADM3_PCODE",
by_df="ADM3_PCODE", how = "inner")## Warning: We recommend using the dplyr::*_join() family of functions instead.pDATA<-ggplot()+
geom_sf(data=merged_BoneSulsel,aes(fill=DATA))+
scale_fill_gradientn(colours=mycol)+
labs(title="Bone dan Sulawesi Selatan")
pDATA