Load library
library(tidyr)
library(dplyr)
library(ggplot2)
library(sf)
library(skimr)
library(ggrepel)
library(stringr)
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
Linking to GEOS 3.9.0, GDAL 3.2.1, PROJ 7.2.1
Load query
End of page for syntax
#load
df <- read.csv("my_gmobility.csv")
#remove NAs
df[is.na(df)] <- 0
#select columns
df1 <- df %>% select(c("sub_region_1", "date", "retail_and_recreation_percent_change_from_baseline", "grocery_and_pharmacy_percent_change_from_baseline", "transit_stations_percent_change_from_baseline", "workplaces_percent_change_from_baseline"))
#rename columns
colnames(df1) <- c("state","date","retail","grocery","transit","work")
head(df1)
Compute median and log percentage change from baseline / month
#compute base dataframe
melaka_feb <- df1 %>% filter(str_detect(date, "2020-02")) %>% filter(state == "Malaysia")
state <- "Malaysia"
month <- "feb20"
median <- (median(melaka_feb$retail) + median(melaka_feb$grocery) + median(melaka_feb$transit) + median(melaka_feb$work))
log <- (log(abs(median(melaka_feb$retail) + median(melaka_feb$grocery) + median(melaka_feb$transit) + median(melaka_feb$work))))
googmob <- data.frame(state,month,median,log)
#compute for february 2020
states <- c("Malacca", "Kedah", "Federal Territory of Kuala Lumpur", "Perak", "Kelantan", "Terengganu", "Negeri Sembilan", "Pahang", "Perlis", "Selangor", "Penang", "Johor")
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2020-02")) %>% filter(state == i)
state <- i
month <- "feb20"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#march 2020
states <- c("Malaysia", "Malacca", "Kedah", "Federal Territory of Kuala Lumpur", "Perak", "Kelantan", "Terengganu", "Negeri Sembilan", "Pahang", "Perlis", "Selangor", "Penang", "Johor")
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2020-03")) %>% filter(state == i)
state <- i
month <- "mar20"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#april 2020
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2020-04")) %>% filter(state == i)
state <- i
month <- "apr20"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#may 2020
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2020-05")) %>% filter(state == i)
state <- i
month <- "may20"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#june 2020
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2020-06")) %>% filter(state == i)
state <- i
month <- "jun20"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#july 2020
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2020-07")) %>% filter(state == i)
state <- i
month <- "jul20"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#aug 2020
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2020-08")) %>% filter(state == i)
state <- i
month <- "aug20"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#sep 2020
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2020-09")) %>% filter(state == i)
state <- i
month <- "sep20"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#oct 2020
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2020-10")) %>% filter(state == i)
state <- i
month <- "oct20"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#nov 2020
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2020-11")) %>% filter(state == i)
state <- i
month <- "nov20"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#dec 2020
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2020-12")) %>% filter(state == i)
state <- i
month <- "dec20"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#jan 2021
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2021-01")) %>% filter(state == i)
state <- i
month <- "jan21"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#feb 2021
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2021-02")) %>% filter(state == i)
state <- i
month <- "feb21"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#march 2021
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2021-03")) %>% filter(state == i)
state <- i
month <- "mar21"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#april 2021
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2021-04")) %>% filter(state == i)
state <- i
month <- "apr21"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#may 2021
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2021-05")) %>% filter(state == i)
state <- i
month <- "may21"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#june 2021
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2021-06")) %>% filter(state == i)
state <- i
month <- "jun21"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
#july 2021
for (i in states){
i_feb <- df1 %>% filter(str_detect(date, "2021-07")) %>% filter(state == i)
state <- i
month <- "jul21"
median <- (median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))
log <- (log(abs(median(i_feb$retail) + median(i_feb$grocery) + median(i_feb$transit) + median(i_feb$work))))
i <- data.frame(state,month,median,log)
googmob <- rbind(googmob,i)
}
googmob
Plot median percentage change from baseline
mys_plot2 <- ggplot(data = googmob, aes(month, median, color = state, group = state)) +
geom_line() +
guides(col = FALSE) +
theme_set(theme_bw()) +
scale_y_continuous(name ="Percentage change in mobility") +
scale_x_discrete(name ="Time (month.year)",
limits=c("feb20","mar20","apr20","may20","jun20","jul20","aug20","sep20","oct20","nov20","dec20","jan21","feb21","mar21","apr21","may21","jun21","jul21"),
labels=c("feb20"="02.2020","mar20"="03.2020","apr20"="04.2020","may20"="05.2020","jun20"="06.2020","jul20"="07.2020","aug20"="08.2020","sep20"="09.2020","oct20"="10.2020","nov20"="11.2020","dec20"="12.2020","jan21"="01.2021","feb21"="02.2021","mar21"="03.2021","apr21"="04.2021","may21"="05.2021","jun21"="06.2021","jul21"="07.2021"))
ggsave("mys_gr2.png", mys_plot2, width = 15, height = 10)
mys_plot2
mys_plot2 <- ggplot(data = googmob, aes(month, log(abs(median)), color = state, group = state)) +
geom_line() +
guides(col = FALSE) +
theme_set(theme_bw()) +
scale_y_continuous(name ="Percentage change in mobility") +
scale_x_discrete(name ="Time (month.year)",
limits=c("feb20","mar20","apr20","may20","jun20","jul20","aug20","sep20","oct20","nov20","dec20","jan21","feb21","mar21","apr21","may21","jun21","jul21"),
labels=c("feb20"="02.2020","mar20"="03.2020","apr20"="04.2020","may20"="05.2020","jun20"="06.2020","jul20"="07.2020","aug20"="08.2020","sep20"="09.2020","oct20"="10.2020","nov20"="11.2020","dec20"="12.2020","jan21"="01.2021","feb21"="02.2021","mar21"="03.2021","apr21"="04.2021","may21"="05.2021","jun21"="06.2021","jul21"="07.2021"))
ggsave("mys_gr2.png", mys_plot2, width = 15, height = 10)
mys_plot2
mys_plot2 <- ggplot(data = googmob, aes(month, log, color = state, group = state)) +
geom_line() +
guides(col = FALSE) +
theme_set(theme_bw()) +
scale_y_continuous(name ="Log percentage change in mobility") +
scale_x_discrete(name ="Time (month.year)",
limits=c("feb20","mar20","apr20","may20","jun20","jul20","aug20","sep20","oct20","nov20","dec20","jan21","feb21","mar21","apr21","may21","jun21","jul21"),
labels=c("feb20"="02.2020","mar20"="03.2020","apr20"="04.2020","may20"="05.2020","jun20"="06.2020","jul20"="07.2020","aug20"="08.2020","sep20"="09.2020","oct20"="10.2020","nov20"="11.2020","dec20"="12.2020","jan21"="01.2021","feb21"="02.2021","mar21"="03.2021","apr21"="04.2021","may21"="05.2021","jun21"="06.2021","jul21"="07.2021"))
ggsave("mys_gr.png", mys_plot2, width = 15, height = 10)
mys_plot2
Save
write.csv(googmob, "googmob.csv")
Data source: Google Cloud Mobility
https://cloud.google.com/blog/products/data-analytics/publicly-available-covid-19-data-for-analyticsli=1
SELECT
*
FROM
`bigquery-public-data.covid19_google_mobility.mobility_report`
WHERE
country_region = "Malaysia"
AND date BETWEEN "2019-12-01" AND "2021-07-31"
ORDER BY
date
Execution error
ERROR: Error in parse(text = x, srcfile = src): <text>:2:3: unexpected '*'
1: SELECT
2: *
^