##Combining Datasets from Global Carbon Atlas, World Bank's WDI, and SIPRI's Military Expenditures Beta

library(readr)
library(dplyr)
library(tidyr)
library(countrycode)

co2 <- read_csv("globalCarbonAtlas.csv")
co2 <- co2 %>% tidyr::gather("country", "co2_pc", 2:218)
Hmisc::label(co2$co2_pc) <- "Territorial emissions in tCO2 per person"
co2$countryName <- countrycode(co2$country, "country.name", "country.name")
co2$countryCode_wb <- countrycode(co2$countryName, "country.name", "wb")

##SIPRI - Military expenditure, local currency, current prices, by Calendar Year (beta)
sipri_lcu <- read_csv("sipri_lcu.csv", na = c("", "NA","..", ". .", "xxx"))
sipri_lcu <- sipri_lcu %>% gather(year, sipri_lcu, 2:57)
sipri_lcu$countryName <- countrycode(sipri_lcu$country, "country.name", "country.name")
sipri_lcu$countryCode_wb <- countrycode(sipri_lcu$countryName, "country.name", "wb")
sipri_lcu$year <- as.numeric(sipri_lcu$year)

wb <- read_csv("WDI.csv", na = c("","..", ". ."))

##delete extra columns from co2 and milex dataframes
co2 <- co2[,-which(names(co2)=="country")]
co2 <- co2[,-which(names(co2)=="countryName")]

sipri_lcu <- sipri_lcu[,-which(names(sipri_lcu)=="country")]
sipri_lcu <- sipri_lcu[,-which(names(sipri_lcu)=="countryName")]

##combine data sets
wb <- dplyr::left_join(wb, co2)
wb <- dplyr::left_join(wb, sipri_lcu)

wb <- wb[,-which(names(wb)=="yearCode")]
names(wb)[which(names(wb)=="countryName_wb")] <- "countryName"
names(wb)[which(names(wb)=="countryCode_wb")] <- "id"

write.csv(wb, file="wbFinal.csv")
