# Séance 2 : Importer et exporter des données
# Source : https://pierrebeaucoral.github.io/r-for-beginners/fr/
# Ouvrez votre projet r-course dans RStudio, puis exécutez ce fichier ligne par ligne.

Sys.setenv(LANGUAGE = "en")   # messages de R en anglais
library(tidyverse)   # readr, dplyr, ggplot2…
library(here)        # chemins relatifs au projet
library(janitor)     # clean_names()
library(readxl)      # lire des fichiers Excel
library(writexl)     # écrire des fichiers Excel
library(lubridate)   # dates
library(scales)      # label_dollar()
library(WDI)         # API de la Banque mondiale (utilisé dans un bloc non évalué)

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

# here("data", "raw", "SCIM.csv") renvoie le chemin complet sur votre machine,
# p. ex. "/home/you/r-course/data/raw/SCIM.csv" (non affiché ici)
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"))   # tentative naïve
dim(wid_naive)
head(wid_naive, 3)

problems(wid_naive) |> select(-file)   # masquer le long chemin

wid <- read_delim(
  here("data", "raw", "WID_Data.csv"),
  delim = ";",
  skip = 1,                                   # la ligne de commentaire
  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)

# Mêmes chiffres ? (le fichier nettoyé a en plus un code iso3c)
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("Les inégalités de revenu ont augmenté en Chine, en Inde et aux États-Unis, 2000-2020", 60),
    x = NULL, y = "Indice de Gini (0-1)", colour = NULL,
    caption = str_wrap("Source : World Inequality Database (WID), revenu national avant impôt.", 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() : espaces enlevées, reste la casse
unique(repair_base$country)   # read.csv() : les deux problèmes

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))   # avant
life_num <- as.numeric(repair$life_exp)
sum(is.na(life_num))          # après

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   # tous les autres libellés restent tels quels
    )
  )
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 : mêmes valeurs, types et noms

# WDIsearch("gdp per capita")      # nécessite Internet

# gdp_api <- WDI(
#   country   = c("US", "CN", "IN"),     # codes ISO-2
#   indicator = c(gdp = "NY.GDP.MKTP.CD"),  # nouveau_nom = code de l'indicateur
#   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("La plupart des indicateurs couvrent presque tous les pays ; pas l'indice de Gini", 60),
    x = NULL, y = "Part des pays avec une valeur", colour = NULL,
    caption = str_wrap("Source : Banque mondiale, 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 = "Le PIB de la Chine est passé de 12 % à 70 % de celui des États-Unis en 20 ans",
    x = NULL, y = "PIB (en $ courants, milliers de milliards)", colour = NULL,
    caption = "Source : Banque mondiale, 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 et tiret dans le nom

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
# )
# # puis sauvegardez-le une fois, pour ne plus jamais avoir à rappeler l'API :
# 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)                             # les espaces passent ; la virgule et "n/a", non
sum(is.na(as.numeric(x)))
parse_number(x)                           # 1200 est correct ; "n/a" donne encore un avertissement
parse_number(x, na = "n/a")               # "n/a" déclaré manquant : plus d'avertissement
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",   # graphie de la Banque mondiale
                        .default = country),
    date = my(month),
    year = year(date)
  )
visits |> filter(is.na(date))   # le mois 13 n'existe pas
visits |> count(country, year)