Tugas 3 - Sains Data
Esis Ramadhan
9/19/2021
import library
library(leaflet)
library(sf)## Linking to GEOS 3.9.1, 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)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionlibrary(rgdal)## Loading required package: sp## Please note that rgdal will be retired by the end of 2023,
## plan transition to sf/stars/terra functions using GDAL and PROJ
## at your earliest convenience.
##
## rgdal: version: 1.5-27, (SVN revision 1148)
## 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: D:/Program Files/R/R-4.1.1/library/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: D:/Program Files/R/R-4.1.1/library/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 sp or rgdal.
## Overwritten PROJ_LIB was D:/Program Files/R/R-4.1.1/library/rgdal/projlibrary(RODBC)
library(RSQLite)
library(DBI)
library(mdsr)
library(sp)
library(rgdal)
library(ggplot2)
library(tidyverse)## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --## v tibble 3.1.3 v purrr 0.3.4
## 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()Start Learning. Happy Coding Fellas!!!
#dbGetQuery()
con <- dbConnect(SQLite(),"D:/Esis/esis/file kerja/Kuliah/S2 IPB/STA581 Sains Data-20210815T135607Z-001/Materi 2 - STA581/Northwind_large_copy.sqlite")
class(con)## [1] "SQLiteConnection"
## attr(,"package")
## [1] "RSQLite"dbListTables(con)## [1] "Category" "Customer" "CustomerCustomerDemo"
## [4] "CustomerDemographic" "Employee" "EmployeeTerritory"
## [7] "Order" "OrderDetail" "Product"
## [10] "ProductDetails_V" "Region" "Shipper"
## [13] "Supplier" "Territory"product<-dplyr::tbl(con,"Product")
class(product)## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"product## # Source: table<Product> [?? x 10]
## # Database: sqlite 3.36.0 [D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains
## # Data-20210815T135607Z-001\Materi 2 - STA581\Northwind_large_copy.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># filtering cara 1
q<-product %>%
filter(UnitPrice>20) %>%
group_by(SupplierId) %>%
summarize(stock=min(UnitsInStock)) %>%
arrange(desc(stock))
q## Warning: Missing values are always removed in SQL.
## Use `MIN(x, na.rm = TRUE)` to silence this warning
## This warning is displayed only once per session.## # Source: lazy query [?? x 2]
## # Database: sqlite 3.36.0 [D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains
## # Data-20210815T135607Z-001\Materi 2 - STA581\Northwind_large_copy.sqlite]
## # Ordered by: desc(stock)
## SupplierId stock
## <int> <int>
## 1 25 115
## 2 9 104
## 3 8 40
## 4 6 35
## 5 4 29
## 6 15 26
## 7 5 22
## 8 26 21
## 9 28 19
## 10 29 17
## # ... with more rowsshow_query(q)## <SQL>
## SELECT `SupplierId`, MIN(`UnitsInStock`) AS `stock`
## FROM `Product`
## WHERE (`UnitPrice` > 20.0)
## GROUP BY `SupplierId`
## ORDER BY `stock` DESC# filtering cara 2
tbl(con, sql("SELECT productname, categoryid, unitprice FROM Product ORDER BY productname LIMIT 10"))## # Source: SQL [?? x 3]
## # Database: sqlite 3.36.0 [D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains
## # Data-20210815T135607Z-001\Materi 2 - STA581\Northwind_large_copy.sqlite]
## productname categoryid unitprice
## <chr> <int> <dbl>
## 1 Alice Mutton 6 39
## 2 Aniseed Syrup 2 10
## 3 Boston Crab Meat 8 18.4
## 4 Camembert Pierrot 4 34
## 5 Carnarvon Tigers 8 62.5
## 6 Chai 1 18
## 7 Chang 1 19
## 8 Chartreuse verte 1 18
## 9 Chef Anton's Cajun Seasoning 2 22
## 10 Chef Anton's Gumbo Mix 2 21.4tbl(con,"Customer") %>% select(CompanyName) %>% head(n=10)## # Source: lazy query [?? x 1]
## # Database: sqlite 3.36.0 [D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains
## # Data-20210815T135607Z-001\Materi 2 - STA581\Northwind_large_copy.sqlite]
## CompanyName
## <chr>
## 1 Alfreds Futterkiste
## 2 Ana Trujillo Emparedados y helados
## 3 Antonio Moreno TaquerÃa
## 4 Around the Horn
## 5 Berglunds snabbköp
## 6 Blauer See Delikatessen
## 7 Blondesddsl père et fils
## 8 Bólido Comidas preparadas
## 9 Bon app
## 10 Bottom-Dollar MarketsTugas Praktikum 3.a
# data northwind
Product <- dplyr::tbl(con,'Product')
Supplier <- dplyr::tbl(con,'Supplier') %>% select(Id,CompanyName)
head(Product)## # Source: lazy query [?? x 10]
## # Database: sqlite 3.36.0 [D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains
## # Data-20210815T135607Z-001\Materi 2 - STA581\Northwind_large_copy.sqlite]
## Id ProductName SupplierId CategoryId QuantityPerUnit UnitPrice UnitsInStock
## <int> <chr> <int> <int> <chr> <dbl> <int>
## 1 1 Chai 1 1 10 boxes x 20 ~ 18 39
## 2 2 Chang 1 1 24 - 12 oz bot~ 19 17
## 3 3 Aniseed Sy~ 1 2 12 - 550 ml bo~ 10 13
## 4 4 Chef Anton~ 2 2 48 - 6 oz jars 22 53
## 5 5 Chef Anton~ 2 2 36 boxes 21.4 0
## 6 6 Grandma's ~ 3 2 12 - 8 oz jars 25 120
## # ... with 3 more variables: UnitsOnOrder <int>, ReorderLevel <int>,
## # Discontinued <int>head(Supplier)## # Source: lazy query [?? x 2]
## # Database: sqlite 3.36.0 [D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains
## # Data-20210815T135607Z-001\Materi 2 - STA581\Northwind_large_copy.sqlite]
## Id CompanyName
## <int> <chr>
## 1 1 Exotic Liquids
## 2 2 New Orleans Cajun Delights
## 3 3 Grandma Kelly's Homestead
## 4 4 Tokyo Traders
## 5 5 Cooperativa de Quesos 'Las Cabras'
## 6 6 Mayumi's# inner join
inner_join(Product,Supplier, by = c("SupplierId" = "Id"))## # Source: lazy query [?? x 11]
## # Database: sqlite 3.36.0 [D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains
## # Data-20210815T135607Z-001\Materi 2 - STA581\Northwind_large_copy.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 5 more variables: UnitsInStock <int>,
## # UnitsOnOrder <int>, ReorderLevel <int>, Discontinued <int>,
## # CompanyName <chr># left join
left_join(Product,Supplier, by = c("SupplierId" = "Id"))## # Source: lazy query [?? x 11]
## # Database: sqlite 3.36.0 [D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains
## # Data-20210815T135607Z-001\Materi 2 - STA581\Northwind_large_copy.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 5 more variables: UnitsInStock <int>,
## # UnitsOnOrder <int>, ReorderLevel <int>, Discontinued <int>,
## # CompanyName <chr># right join
right_join(Product,Supplier, by = c("SupplierId" = "Id"))## # Source: lazy query [?? x 11]
## # Database: sqlite 3.36.0 [D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains
## # Data-20210815T135607Z-001\Materi 2 - STA581\Northwind_large_copy.sqlite]
## Id ProductName SupplierId CategoryId QuantityPerUnit UnitPrice
## <int> <chr> <int> <int> <chr> <dbl>
## 1 3 Aniseed Syrup 1 2 12 - 550 ml bot~ 10
## 2 1 Chai 1 1 10 boxes x 20 b~ 18
## 3 2 Chang 1 1 24 - 12 oz bott~ 19
## 4 4 Chef Anton's Cajun Se~ 2 2 48 - 6 oz jars 22
## 5 5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.4
## 6 65 Louisiana Fiery Hot P~ 2 2 32 - 8 oz bottl~ 21.0
## 7 66 Louisiana Hot Spiced ~ 2 2 24 - 8 oz jars 17
## 8 6 Grandma's Boysenberry~ 3 2 12 - 8 oz jars 25
## 9 8 Northwoods Cranberry ~ 3 2 12 - 12 oz jars 40
## 10 7 Uncle Bob's Organic D~ 3 7 12 - 1 lb pkgs. 30
## # ... with more rows, and 5 more variables: UnitsInStock <int>,
## # UnitsOnOrder <int>, ReorderLevel <int>, Discontinued <int>,
## # CompanyName <chr># full join
full_join(Product,Supplier, by = c("SupplierId" = "Id"))## # Source: lazy query [?? x 11]
## # Database: sqlite 3.36.0 [D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains
## # Data-20210815T135607Z-001\Materi 2 - STA581\Northwind_large_copy.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 5 more variables: UnitsInStock <int>,
## # UnitsOnOrder <int>, ReorderLevel <int>, Discontinued <int>,
## # CompanyName <chr># semi join
semi_join(Product,Supplier, by = c("SupplierId" = "Id"))## # Source: lazy query [?? x 10]
## # Database: sqlite 3.36.0 [D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains
## # Data-20210815T135607Z-001\Materi 2 - STA581\Northwind_large_copy.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># anti join
anti_join(Product,Supplier, by = c("SupplierId" = "Id"))## # Source: lazy query [?? x 10]
## # Database: sqlite 3.36.0 [D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains
## # Data-20210815T135607Z-001\Materi 2 - STA581\Northwind_large_copy.sqlite]
## # ... with 10 variables: Id <int>, ProductName <chr>, SupplierId <int>,
## # CategoryId <int>, QuantityPerUnit <chr>, UnitPrice <dbl>,
## # UnitsInStock <int>, UnitsOnOrder <int>, ReorderLevel <int>,
## # Discontinued <int>Tugas Praktikum 3.b
#set warna
mycol <- c("green", "yellow", "red", "red4")
#kabupaten spatial
Admin2Kabupaten<-"D:/Esis/esis/file kerja/Kuliah/S2 IPB/STA581 Sains Data-20210815T135607Z-001/Admin2Kabupaten/idn_admbnda_adm2_bps_20200401.shp"
Admin2<-st_read(Admin2Kabupaten)## Reading layer `idn_admbnda_adm2_bps_20200401' from data source
## `D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains Data-20210815T135607Z-001\Admin2Kabupaten\idn_admbnda_adm2_bps_20200401.shp'
## using driver `ESRI Shapefile'
## Simple feature collection with 522 features and 14 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(Admin2)
#Admin2
#kabupaten dataframe
kabupaten<-"D:/Esis/esis/file kerja/Kuliah/S2 IPB/STA581 Sains Data-20210815T135607Z-001/Admin2Kabupaten/idn_admbnda_adm2_bps_20200401.dbf"
tangerangindonesia = as.data.frame(st_read(kabupaten))## Reading layer `idn_admbnda_adm2_bps_20200401' from data source
## `D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains Data-20210815T135607Z-001\Admin2Kabupaten\idn_admbnda_adm2_bps_20200401.dbf'
## using driver `ESRI Shapefile'
## Simple feature collection with 522 features and 14 fields
## Geometry type: MULTIPOLYGON
## Dimension: XY
## Bounding box: xmin: 95.01079 ymin: -11.00762 xmax: 141.0194 ymax: 6.07693
## Geodetic CRS: WGS 84tangerangindonesia <- tangerangindonesia %>% select(Shape_Leng,Shape_Area,ADM2_EN,ADM2_PCODE,ADM1_EN)
tangerangindonesia$pendapatan <- ifelse(tangerangindonesia$ADM2_EN=='Kota Tangerang',250,
ifelse(tangerangindonesia$ADM2_EN=='Kota Tangerang Selatan',500,
ifelse(tangerangindonesia$ADM2_EN=='Tangerang',100,NA)))
tangerangindonesia <- tangerangindonesia[tangerangindonesia$ADM1_EN %in% c('Jawa Barat','Banten'),]
merged_tangerangindonesia <- geo_join(spatial_data=Admin2,data_frame=tangerangindonesia, by_sp="ADM2_PCODE",by_df="ADM2_PCODE", how = "inner")## Warning: We recommend using the dplyr::*_join() family of functions instead.pDATA<-ggplot()+
geom_sf(data=merged_tangerangindonesia,aes(fill=pendapatan))+
scale_fill_gradientn(colours=mycol)+
labs(title="Average Revenue of Tangerang - Banten")
pDATA
#kecamatan spatial
AdminKecamatan<-"D:/Esis/esis/file kerja/Kuliah/S2 IPB/STA581 Sains Data-20210815T135607Z-001/Admin3Kecamatan/idn_admbnda_adm3_bps_20200401.shp"
Admin3<-st_read(AdminKecamatan)## Reading layer `idn_admbnda_adm3_bps_20200401' from data source
## `D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains Data-20210815T135607Z-001\Admin3Kecamatan\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)
#Admin3
#kecamatan dataframe
kecamatan<-"D:/Esis/esis/file kerja/Kuliah/S2 IPB/STA581 Sains Data-20210815T135607Z-001/Admin3Kecamatan/idn_admbnda_adm3_bps_20200401.dbf"
tangerangselatan = as.data.frame(st_read(kabupaten))## Reading layer `idn_admbnda_adm2_bps_20200401' from data source
## `D:\Esis\esis\file kerja\Kuliah\S2 IPB\STA581 Sains Data-20210815T135607Z-001\Admin2Kabupaten\idn_admbnda_adm2_bps_20200401.dbf'
## using driver `ESRI Shapefile'
## Simple feature collection with 522 features and 14 fields
## Geometry type: MULTIPOLYGON
## Dimension: XY
## Bounding box: xmin: 95.01079 ymin: -11.00762 xmax: 141.0194 ymax: 6.07693
## Geodetic CRS: WGS 84tangerangselatan <- tangerangselatan %>% select(Shape_Leng,Shape_Area,ADM2_EN,ADM2_PCODE,ADM1_EN)
tangerangselatan <- tangerangselatan[tangerangselatan$ADM1_EN %in% c('Banten'),]
tangerangselatan$pendapatan <- ifelse(tangerangselatan$ADM2_EN=='Kota Tangerang',250,
ifelse(tangerangselatan$ADM2_EN=='Kota Tangerang Selatan',500,
ifelse(tangerangselatan$ADM2_EN=='Tangerang',100,NA)))
merged_tangerangselatan <- geo_join(spatial_data=Admin3,data_frame=tangerangselatan, by_sp="ADM2_PCODE",by_df="ADM2_PCODE", how = "inner")## Warning: We recommend using the dplyr::*_join() family of functions instead.pDATA<-ggplot()+
geom_sf(data=merged_tangerangselatan,aes(fill=pendapatan))+
scale_fill_gradientn(colours=mycol)+
labs(title="Average Revenue of Tangerang")
pDATA