PM10: Data Retrieval and Processing

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, union
library(rgdal)
## Loading required package: sp
## rgdal: version: 1.4-8, (SVN revision 845)
##  Geospatial Data Abstraction Library extensions to R successfully loaded
##  Loaded GDAL runtime: GDAL 2.4.2, released 2019/06/28
##  Path to GDAL shared files: /Library/Frameworks/R.framework/Versions/3.6/Resources/library/rgdal/gdal
##  GDAL binary built with GEOS: FALSE 
##  Loaded PROJ.4 runtime: Rel. 5.2.0, September 15th, 2018, [PJ_VERSION: 520]
##  Path to PROJ.4 shared files: /Library/Frameworks/R.framework/Versions/3.6/Resources/library/rgdal/proj
##  Linking to sp version: 1.3-2
library(countrycode)
library(ggmap)
## Loading required package: ggplot2
## Google's Terms of Service: https://cloud.google.com/maps-platform/terms/.
## Please cite ggmap if you use it! See citation("ggmap") for details.
library(readxl)
library(leaflet)
library(DT)
library(taucharts)
library(sp)
library(magrittr)
## 
## Attaching package: 'magrittr'
## The following object is masked from 'package:ggmap':
## 
##     inset
library(forcats)

cities<-read_excel("OAP_database.xlsx", sheet = 4)
## New names:
## * `` -> ...2
## * `` -> ...3
## * `` -> ...4
## * `` -> ...5
## * `` -> ...6
## * ...
cities<-cities[-c(1:2),c(3,2,4)]
names(cities)=c("City","Country","PMLevel")
str(cities)
## tibble [1,099 × 3] (S3: tbl_df/tbl/data.frame)
##  $ City   : chr [1:1099] "Alger" "Buenos Aires" "Adelaide" "Brisbane" ...
##  $ Country: chr [1:1099] "Algeria" "Argentina" "Australia" "Australia" ...
##  $ PMLevel: chr [1:1099] "42" "38" "13.5" "18.166666666666668" ...

Worst 100 cities with high level of PM2.5

cities$PMLevel<-round(as.numeric(cities$PMLevel),2)

cities %>% arrange(-PMLevel)%>%top_n(100)%>%datatable()
## Selecting by PMLevel

Which countries contribute the highest number of cities in the top 100 on PM10?

tmp<-cities%>%arrange(-PMLevel)%>%top_n(100)%>%
  group_by(Country)%>%summarise(number_of_cities=length(Country))%>%arrange(-number_of_cities)
## Selecting by PMLevel

Bar Chart of countries with top 100 cities

tmp$Country<-fct_inorder(tmp$Country)
tauchart(tmp)%>%tau_bar("number_of_cities","Country",horizontal = "TRUE")%>% tau_legend()%>% tau_tooltip()
## Neither color nor size aesthetics have been mapped. Legend plugin will be active but not displayed.

# Geographic Map of 100 cities with highest level of PM2.5

cities100<-cities%>%arrange(-PMLevel)%>%top_n(100)
## Selecting by PMLevel
cities100$CityCountry=paste(cities100$City,cities100$Country,sep= ", ")

locs<-geocode(cities100$CityCountry)
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Ahwaz,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Ulaanbaatar,+Mongolia&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Sanandaj,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Ludhiana,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Quetta,+Pakistan&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Kermanshah,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Peshawar,+Pakistan&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Gaborone,+Botswana&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Yasouj,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Kanpur,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Lahore,+Pakistan&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Delhi,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Karachi,+Pakistan&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Islamabad,+Pakistan&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Lucknow,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Rawalpindi,+Pakistan&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Uromiyeh,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Qom,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Indore,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=khoramabad,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Agra,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Al+Ain,+United+Arab+Emirates&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Riyadh,+Saudi+Arabia&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Al-Hafouf,+Saudi+Arabia&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Lanzhou,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Kolkata,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Van,+Turkey&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Dakar,+Senegal&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Makkah,+Saudi+Arabia&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Xining,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Urumqi,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Faridabad,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Greater+Cairo,+Egypt&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## "Greater Cairo, Egypt" not uniquely geocoded, using "17 mohammed mazhar, zamalek, giza governorate, egypt"
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Mexicali,+Mexico&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Jabalpur,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Dhaka,+Bangladesh&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Mumbai,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Dhanbad,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Ilam,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Jeddah,+Saudi+Arabia&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Allahabad,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Bushehr,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Kerman,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Jinan,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Kuwait+City,+Kuwait&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Lagos,+Nigeria&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Beijing,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Dammam,+Saudi+Arabia&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Patna,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Sarajevo,+Bosnia+and+Herzegovina&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Abu+Dhabi,+United+Arab+Emirates&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Meerut,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Xi'an,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Jaipur,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Qazvin,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Medan,+Indonesia&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Chengdu,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Hefei,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Shenyang,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Yanbu,+Saudi+Arabia&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Kathmandu+Valley,+Nepal&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Taiyuan,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Varanasi,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Chongqing,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Wuhan,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Esfahan,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Shijiazhuang,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Konya,+Turkey&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Kars,+Turkey&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Hamedan,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Arak,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Harbin,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Tianjin,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Nanjing,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Denizli,+Turkey&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Zhengzhou,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Pune,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Hatay,+Turkey&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Nagpur,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Accra,+Ghana&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Hangzhou,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Tehran,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Yangon,+Myanmar&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Erzurum,+Turkey&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Bhopal,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Changsha,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Vijayawada,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Tecate,+Mexico&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Yinchuan,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Bangalore,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Rajkot,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Mandalay,+Myanmar&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Hyderabad,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Visakhapatnam,+India&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Mashhad,+Iran+(Islamic+Republic+of)&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Sfax,+Tunisia&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Tunis,+Tunisia&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Bizerte,+Tunisia&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Changchun,+China&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
## Source : https://maps.googleapis.com/maps/api/geocode/json?address=Balikesir,+Turkey&key=xxx-iMAjn1VWGCYdwDmIQ90jh7xgiXGwE
cities100$lat<-locs$lat
cities100$lon<-locs$lon

## Using ggplot 2

worldmap <- borders("world", fill="lightgray",colour="white")

worldmap <- ggplot()+worldmap

worldmap <- worldmap + geom_point(data = cities100,aes(x=lon,y=lat,size=PMLevel),alpha=.4,color="red")
worldmap + theme_void()

The Map - Interactive

cities100$popup<-paste("<table><tr><td>City:",cities100$City,"<br>Country:",cities100$Country, "<br>Annual Mean PM10 Level:", cities100$PMLevel,"</td></tr></table>")

leaflet(cities100)%>%addTiles()%>%
  #addProviderTiles("CartoDB.Position") %>%
  setView(0, 0, zoom = 2) %>% 
  addCircles(stroke = FALSE, fillOpacity = .5, color = "red",radius=~PMLevel*1000,popup=~popup)
## Assuming "lon" and "lat" are longitude and latitude, respectively
countries <- read_excel("OAP_database.xlsx",sheet = 5)
## New names:
## * `` -> ...2
## * `` -> ...3
## * `` -> ...4
## * `` -> ...5
## * `` -> ...6
## * ...
countries<-countries[-c(1:2),c(2:3)]

names(countries)<-c("Country","PMLevel") # 3 Remaining columns
str(countries)
## tibble [91 × 2] (S3: tbl_df/tbl/data.frame)
##  $ Country: chr [1:91] "Estonia" "Mauritius" "Australia" "New Zealand" ...
##  $ PMLevel: chr [1:91] "11.132999999999999" "11.65" "13.179790073011779" "15" ...
countries$PMLevel=round(as.numeric(countries$Country),2)
countries$iso3c<-as.factor(countrycode(countries$Country,"country.name","iso3c"))

head(countries)
## # A tibble: 6 x 3
##   Country     PMLevel iso3c
##   <chr>         <dbl> <fct>
## 1 Estonia          NA EST  
## 2 Mauritius        NA MUS  
## 3 Australia        NA AUS  
## 4 New Zealand      NA NZL  
## 5 Ireland          NA IRL  
## 6 Luxembourg       NA LUX
countries$iso3c=countrycode(countries$Country,"country.name","iso3c")
countries$iso3c<-as.factor(countries$iso3c)