# Session 2: Importing and exporting data
# Source: https://pierrebeaucoral.github.io/r-for-beginners/
# Open your r-course project in RStudio, then run this file line by line.

Sys.setenv(LANGUAGE = "en")   # R messages in English
library(tidyverse)   # readr, dplyr, ggplot2, ...
library(here)        # project-relative paths
library(janitor)     # clean_names()
library(readxl)      # read Excel files
library(writexl)     # write Excel files
library(lubridate)   # dates
library(scales)      # label_dollar()
library(WDI)         # World Bank API (used in a non-evaluated chunk)

source(here("R", "theme_cours.R"))

# here("data", "raw", "SCIM.csv") returns the full path on your machine,
# e.g. "/home/you/r-course/data/raw/SCIM.csv" (not printed here)
file.exists(here("data", "raw", "SCIM.csv"))

scim <- read_csv(here("data", "raw", "SCIM.csv"))

head(scim)
glimpse(scim)

summary(scim$Value)

scim <- clean_names(scim)
names(scim)

scim <- scim |>
  select(subject, location, measure, frequency, time, unit_code, value)

scim |> count(subject)
scim |> count(measure, unit_code, frequency)

scim |>
  filter(str_detect(time, "Q")) |>
  distinct(time) |>
  mutate(date = yq(time))

path_xlsx <- here("data", "raw", "SCIM.xlsx")
excel_sheets(path_xlsx)

scim_xlsx <- read_excel(path_xlsx, sheet = "Sheet1")
dim(scim_xlsx)
scim_xlsx[1:5, 1:6]

dim(read_excel(path_xlsx, sheet = "Feuil2"))

read_excel(path_xlsx, sheet = "Sheet1", range = "A1:D6")

readLines(here("data", "raw", "WID_Data.csv"), n = 9)

wid_naive <- read_csv(here("data", "raw", "WID_Data.csv"))   # naive attempt
dim(wid_naive)
head(wid_naive, 3)

problems(wid_naive) |> select(-file)   # hide the long path

wid <- read_delim(
  here("data", "raw", "WID_Data.csv"),
  delim = ";",
  skip = 1,                                   # the comment line
  col_names = c("country_wid", "indicator", "percentile", "year", "gini"),
  show_col_types = FALSE
)
nrow(wid)
glimpse(wid)

print(wid_naive |> rename(comment_line_as_name = 1) |> head(4), width = 40)
print(wid |> select(country_wid, year, gini) |> head(4))

wid_gini <- read_csv(here("data", "wid_gini.csv"), show_col_types = FALSE)
glimpse(wid_gini)

# Same numbers? (the cleaned file has an iso3c code in addition)
all.equal(wid$gini, wid_gini$gini)

ggplot(wid_gini, aes(x = year, y = gini, colour = iso3c)) +
  geom_line(linewidth = 1) +
  scale_colour_manual(values = c(CHN = pal_cours[["accent"]],
                                 IND = "#E69F00",
                                 USA = pal_cours[["principal"]])) +
  labs(
    title = str_wrap("Income inequality rose in China, India and the USA, 2000-2020", 60),
    x = NULL, y = "Gini index (0-1)", colour = NULL,
    caption = str_wrap("Source: World Inequality Database (WID), pre-tax national income.", 95)
  )

repair <- read_csv(here("data", "raw", "repair_me.csv"), show_col_types = FALSE)
glimpse(repair)

repair_base <- read.csv(here("data", "raw", "repair_me.csv"))
str(repair_base)

as.numeric(repair_base$gdp_pc_ppp)

parse_number(repair_base$gdp_pc_ppp)

parse_number("12 km")
parse_number("2.967,7", locale = locale(decimal_mark = ",", grouping_mark = "."))

repair_base$iso_num

repair_codes <- read_csv(here("data", "raw", "repair_me.csv"),
                         col_types = cols(iso_num = col_character()))
repair_codes$iso_num

unique(repair$country)        # read_csv(): spaces removed, case problem left
unique(repair_base$country)   # read.csv(): both problems

str_squish(c(" kenya", "KENYA ", "Burkina   Faso"))
unique(str_to_title(str_squish(repair_base$country)))

str_to_title(c("Côte d'Ivoire", "United States of America"))

sum(is.na(repair$life_exp))   # before
life_num <- as.numeric(repair$life_exp)
sum(is.na(life_num))          # after

repair |>
  mutate(life_num = as.numeric(life_exp)) |>
  filter(!is.na(life_exp), is.na(life_num)) |>
  select(country, year, life_exp, life_num)

repair_na <- read_csv(here("data", "raw", "repair_me.csv"),
                      na = c("", "NA", "n/a"), show_col_types = FALSE)
class(repair_na$life_exp)
sum(is.na(repair_na$life_exp))

interviews <- tibble(
  country   = c("Senegal", "senegal ", "Côte d'Ivoire", "Cote d'Ivoire", "IVORY COAST", " Ghana"),
  interview = c("15/03/2019", "02/11/2019", "30/06/2020", "31/02/2020", "14/07/2020", "03/12/2019")
)
interviews

interviews <- interviews |>
  mutate(
    country = str_to_title(str_squish(country)),
    country = case_when(
      country %in% c("Côte D'ivoire", "Cote D'ivoire", "Ivory Coast") ~ "Côte d'Ivoire",
      .default = country   # every other label stays as it is
    )
  )
count(interviews, country)

ymd("2019-03-15")
dmy("15/03/2019")
my("03/2019")
yq("2019-Q1")

interviews <- interviews |>
  mutate(date = dmy(interview), year = year(date))
interviews

interviews |> filter(is.na(date))

dir.create(here("output"), showWarnings = FALSE)

write_csv(wid_gini, here("output", "wid_gini_copy.csv"))
write_delim(wid_gini, here("output", "wid_gini_tab.txt"), delim = "\t")

write_xlsx(wid_gini, here("output", "wid_gini.xlsx"))

write_xlsx(
  list(wid_clean = wid_gini, scim_sample = head(scim, 100)),
  here("output", "two_sheets.xlsx")
)
excel_sheets(here("output", "two_sheets.xlsx"))

saveRDS(wid_gini, here("output", "wid_gini.rds"))
wid_back <- readRDS(here("output", "wid_gini.rds"))
all.equal(wid_gini, wid_back)   # TRUE: same values, types and names

# WDIsearch("gdp per capita")      # needs Internet

# gdp_api <- WDI(
#   country   = c("US", "CN", "IN"),     # ISO-2 codes
#   indicator = c(gdp = "NY.GDP.MKTP.CD"),  # new_name = indicator code
#   start = 2000, end = 2020
# )

wdi3 <- read_csv(here("data", "wdi_3countries.csv"), show_col_types = FALSE)
glimpse(wdi3)

wdi_panel <- read_csv(here("data", "wdi_panel.csv"), show_col_types = FALSE)
glimpse(wdi_panel)

wdi_panel |>
  summarise(across(c(gdp, gdp_pc_ppp, population, life_exp, gini, elec_access),
                   \(x) mean(!is.na(x))),
            .by = year) |>
  pivot_longer(-year, names_to = "indicator", values_to = "share_observed") |>
  ggplot(aes(x = year, y = share_observed, colour = indicator)) +
  geom_line(linewidth = 1) +
  scale_y_continuous(labels = label_percent(), limits = c(0, 1)) +
  labs(
    title = str_wrap("Most indicators cover nearly all countries; the Gini index does not", 60),
    x = NULL, y = "Share of countries with a value", colour = NULL,
    caption = str_wrap("Source: World Bank, World Development Indicators (WDI), 2000-2023.", 95)
  )

ggplot(wdi3, aes(x = year, y = gdp, colour = country)) +
  geom_line(linewidth = 1) +
  scale_y_continuous(labels = label_dollar(scale = 1e-12, suffix = "T")) +
  scale_colour_manual(values = c(China = pal_cours[["accent"]],
                                 India = "#E69F00",
                                 `United States` = pal_cours[["principal"]])) +
  labs(
    title = "China's GDP rose from 12% to 70% of the US level in 20 years",
    x = NULL, y = "GDP (current US$, trillions)", colour = NULL,
    caption = "Source: World Bank, World Development Indicators (WDI), 2000-2020."
  )

ex1 <- read_csv(here("data", "raw", "SCIM.csv"), show_col_types = FALSE) |>
  clean_names() |>
  select(location, time, subject, value)

head(ex1)
n_distinct(ex1$location)

ex2 <- read_excel(here("data", "raw", "SCIM.xlsx"), sheet = "Sheet1", range = "A1:C11")
ex2
ex2 |> slice_max(`Août-2022`, n = 1)   # backticks: accent and dash in the name

write_csv(ex2, here("output", "ex2.csv"))
write_xlsx(list(ex2 = ex2, ex1_head = head(ex1, 20)),
           here("output", "ex2_sheets.xlsx"))

file.exists(here("output", c("ex2.csv", "ex2_sheets.xlsx")))

# pop_api <- WDI(
#   country   = c("JP", "DE", "BR"),
#   indicator = c(population = "SP.POP.TOTL"),
#   start = 2000, end = 2020
# )
# # then save it once, so that you never have to call the API again:
# dir.create(here("output"), showWarnings = FALSE)
# write_csv(pop_api, here("output", "pop_3countries.csv"))

wdi_panel |>
  filter(country %in% c("Japan", "Germany", "Brazil"), year <= 2020) |>
  summarise(mean_pop_millions = mean(population, na.rm = TRUE) / 1e6,
            .by = country)

x <- c(" 12", "1,200", "n/a", "7 ")

as.numeric(x)                             # spaces are fine; the comma and "n/a" are not
sum(is.na(as.numeric(x)))
parse_number(x)                           # 1200 is right; "n/a" still gives a warning
parse_number(x, na = "n/a")               # "n/a" declared missing: no warning
sum(is.na(parse_number(x, na = "n/a")))

visits <- tibble(
  country = c("Gambia, The", "the  gambia", "THE GAMBIA ", "Senegal", "senegal"),
  month   = c("03/2018", "11/2018", "13/2019", "06/2019", "09/2019")
)

visits <- visits |>
  mutate(
    country = str_to_title(str_squish(country)),
    country = case_when(country == "Gambia, The" ~ "The Gambia",   # World Bank spelling
                        .default = country),
    date = my(month),
    year = year(date)
  )
visits |> filter(is.na(date))   # month 13 does not exist
visits |> count(country, year)