### clear environment
rm(list = ls())

###packages preparation
install.packages("devtools")
devtools::install_github("hrbrmstr/nominatim")
install.packages("ggmap")
library(ggmap)
install.packages("ggplot2")
library(httr)
library(jsonlite)
library(stringr)
library(dplyr)
library(googlesheets4)
library(ISOcodes)
library(nominatim)

### google map and streat map API preparation
### PLEASE COMMENT OUT key_MapQuest CREDENTIALS TO PROTECT PRAVACY
Key_Google_API = "********" 
key_MapQuest = "********"   
geocode_url_base = "https://maps.googleapis.com/maps/api/geocode/json?"
osm_geocode_url_base = "http://open.mapquestapi.com/nominatim/v1/search.php?"



#############################################China part data processing######################################
###raw data read in and preparation
install.packages("readxl")
library(readxl)
my_data <- read_excel(path = "/Users/xl/*Template.xlsx", sheet = "match CDB & global plant (2)")
my_data <- read_excel(path = "/Users/xl/*Template.xlsx", sheet = "match CDB & global plant (2)", range = cell_rows(2:nrow(my_data)))
str(my_data)

(my_data$`financing country`=="China")
(China_data <- my_data[my_data$`financing country`=="China",])
head(my_data)
dim(China_data)

(China_data$web_list<- with(China_data, paste0(country_long, " ",web_name))) #combining "Country Name + Project Name"
(b <- data.frame( China_data$web_list))

### searching for China part geolocation data
df_total = data.frame()
for (i in b){
  # vector output
  model <- data.frame(geocode(location = i))
  # add vector to a dataframe
  df <- data.frame(model)
  df_total <- rbind(df_total,df)
}

dim(df_total)




########################################Japan part data processing########################################
###raw data read in and preparation
install.packages("xlsx")
library(xlsx)
write.xlsx(df_total, file = "/Users/xl/r_geo_data.xlsx", sheetName = "web_gps", append = TRUE)
my_data <- read_excel(path = "/Users/xl/*Template.xlsx", sheet = "match CDB & global plant (2)", range = cell_rows(2:nrow(my_data)))
str(my_data)
dim(my_data)

(my_data$`financing country`=="Japan")
(Japan_data <- my_data[my_data$`financing country`=="Japan",])
head(my_data)
dim(Japan_data)

(Japan_data$list<- with(Japan_data, paste0(country_long, " ",bilateral_power_name))) #combining "Country Name + Project Name"
(c <- data.frame( Japan_data$list))

### searching forJapan part geolocation data
df_total_J = data.frame()
for (i in c){
  # vector output
  model <- data.frame(geocode(location = i))
  # add vector to a dataframe
  df <- data.frame(model)
  df_total_J <- rbind(df_total_J,df)
}

dim(df_total_J)

### exporting geolocation data results to excel
library(xlsx)
write.xlsx(df_total_J, file = "/Users/xl/r_Japan_geo_data.xlsx", sheetName = "Japan_gps", append = TRUE)




#############################################US part data processing########################################
###raw data read in and preparation
my_data <- read_excel(path = "/Users/xl/*Template.xlsx", sheet = "match CDB & global plant (2)")
my_data <- read_excel(path = "/Users/xl/*Template.xlsx", sheet = "match CDB & global plant (2)", range = cell_rows(2:nrow(my_data)))

(us_data <- my_data[my_data$`financing country`=="The U.S.",])
head(my_data)
dim(my_data)
dim(us_data)

(us_data$list<- with(us_data, paste0(country_long, " ",bilateral_power_name))) #combining "Country Name + Project Name"
(s<- data.frame( us_data$list))

### searching forJapan part geolocation data
df_total_s = data.frame()
for (i in s){
  # vector output
  model1 <- data.frame(geocode(location = i))
  # add vector to a dataframe
  df1 <- data.frame(model1)
  df_total_s <- rbind(df_total_s,df1)
}

dim(df_total_s)

### exporting geolocation data results to excel
library(xlsx)
write.xlsx(df_total_s, file = "/Users/xl/r_US_geo_data.xlsx", sheetName = "US_gps", append = TRUE)