---
title: "CPIA+RGI"
output: html_document
date: "2025-06-26"
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
library(readr)
library(tidyverse)
library(readxl)
library(ggplot2)
library(dplyr)
```

# CPIA

```{r}
df <- read_csv("d8a9bd65-917a-4bf1-9500-5a67bbfe672a_Series - Metadata.csv")
cpia_24 = read_csv("2024.csv")
```

```{r}
highlighted_vars <- c(
  "CPIA business regulatory environment rating (1=low to 6=high)",
  "CPIA financial sector rating (1=low to 6=high)",
  "CPIA fiscal policy rating (1=low to 6=high)",
  "CPIA macroeconomic management rating (1=low to 6=high)",
  "CPIA policy and institutions for environmental sustainability rating (1=low to 6=high)",
  "CPIA property rights and rule-based governance rating (1=low to 6=high)",
  "CPIA quality of budgetary and financial management rating (1=low to 6=high)",
  "CPIA quality of public administration rating (1=low to 6=high)",
  "CPIA transparency, accountability, and corruption in the public sector rating (1=low to 6=high)",
  "IDA resource allocation index (1=low to 6=high)"
)

cpia_cols <- c(
  "CPIA business regulatory environment rating (1=low to 6=high)",
  "CPIA financial sector rating (1=low to 6=high)",
  "CPIA fiscal policy rating (1=low to 6=high)",
  "CPIA macroeconomic management rating (1=low to 6=high)",
  "CPIA policy and institutions for environmental sustainability rating (1=low to 6=high)",
  "CPIA property rights and rule-based governance rating (1=low to 6=high)",
  "CPIA quality of budgetary and financial management rating (1=low to 6=high)",
  "CPIA quality of public administration rating (1=low to 6=high)",
  "CPIA transparency, accountability, and corruption in the public sector rating (1=low to 6=high)"
)

```

```{r}
df_highlighted <- subset(df, `Series Name` %in% highlighted_vars)
cpia_highlighted <- subset(cpia_24, `Series Name` %in% highlighted_vars)
```

```{r}
tb1 = df_highlighted %>% 
  select(`Country Name`, `Country Code`, `Series Name`, `2023 [YR2023]`) %>%
  pivot_wider(
    names_from = `Series Name`,
    values_from = `2023 [YR2023]`
  )

View(tb1)

df1 = cpia_highlighted %>% 
  select(`Country Name`, `Country Code`, `Series Name`, `2024 [YR2024]`) %>%
  pivot_wider(
    names_from = `Series Name`,
    values_from = `2024 [YR2024]`
  )




```

```{r}
tb2 <- tb1 %>%
  mutate(across(all_of(highlighted_vars), as.numeric)) %>%
  rowwise() %>%
  mutate(CPIA_Average_9 = mean(c_across(all_of(cpia_cols)), na.rm = TRUE)) %>%
  ungroup() %>%
  filter(`Country Name`  %in% fmf$Country)

View(tb2)



df2 <- df1 %>%
  mutate(across(all_of(highlighted_vars), as.numeric)) %>%
  rowwise() %>%
  mutate(CPIA_Average_9 = mean(c_across(all_of(cpia_cols)), na.rm = TRUE)) %>%
  ungroup() %>%
  filter(`Country Name`  %in% fmf$Country)
```

## Visualization

```{r}
library(ggplot2)

ggplot(tb2, aes(x = CPIA_Average_9, y = `IDA resource allocation index (1=low to 6=high)`)) +
  geom_point() +
  geom_smooth(method = "lm", se = FALSE, color = "blue") +
  labs(x = "Average of 9 CPIA Indicators", y = "IDA Resource Allocation Index",
       title = "CPIA Average vs IDA Index")

```

```{r}
cor(tb2$CPIA_Average_9, tb2$`IDA resource allocation index (1=low to 6=high)`, use = "complete.obs")

```

# RGI

```{r}
rgi <- read_csv("nrgi_data.csv")
```

```{r}
rgi_scaled = rgi %>%
  select(name, sector, region, `Composite/component`, Score2017, Score2021) %>%
  mutate(
         scaled_2017 = case_when(
           Score2017 >= 90 ~ 6,
           Score2017 >= 70 ~ 5,
           Score2017 >= 50 ~ 4,
           Score2017 >= 30 ~ 3,
           Score2017 >= 10 ~ 2,
           Score2017 < 10 ~ 1
         ),
         scaled_2017 = replace_na(scaled_2017, 0),
         scaled_2021 = case_when(
           Score2021 >= 90 ~ 6,
           Score2021 >= 70 ~ 5,
           Score2021 >= 50 ~ 4,
           Score2021 >= 30 ~ 3,
           Score2021 >= 10 ~ 2,
           Score2021 < 10 ~ 1),
         scaled_2021 = replace_na(scaled_2021, 0))
```

## Filter super region country

```{r}
fmf <- read_excel("Countries of the Super Region.xlsx", skip = 2)


colnames(fmf) <- c("No", "Country", "Official_Name")


fmf <- fmf %>% select(Country, Official_Name)


head(fmf)
```

## Join data, Correlation test

```{r}

merged_df <- dplyr::inner_join(rgi_scaled, tb2, by = c("name" = "Country Name")) %>%
  filter(name %in% fmf$Country)
  

cor_test_result <- cor.test(merged_df$scaled_2021, merged_df$CPIA_Average_9, method = "pearson")


print(cor_test_result)

```

```{r}
plot(merged_df$scaled_2021, merged_df$CPIA_Average_9,
     xlab = "RGI Scaled Score (2021)", ylab = "CPIA Average Score",
     main = "Correlation between RGI and CPIA")
abline(lm(CPIA_Average_9 ~ scaled_2021, data = merged_df), col = "blue")

```

```{r}
cor_test_result_2 <- cor.test(merged_df$Score2017, merged_df$`IDA resource allocation index (1=low to 6=high)`, method = "pearson")

print(cor_test_result_2)

```

```{r}
plot(merged_df$Score2017, merged_df$`IDA resource allocation index (1=low to 6=high)`,
     xlab = "RGI Scaled Score (2017)", ylab = "ida Score",
     main = "Correlation between RGI and CPIA")
abline(lm(CPIA_Average_9 ~ scaled_2021, data = merged_df), col = "blue")
```

```{r}
mi <- read_xlsx("SuperRegion_Mineral_Shares.xlsx")
```

```{r}

rgi_scaled = rgi_scaled %>% filter(name %in% fmf$Country)

tb3 = tb2 %>%
  select(`Country Name` ,`IDA resource allocation index (1=low to 6=high)`) %>%
  rename(
    `IDA resource allocation index 2023` = `IDA resource allocation index (1=low to 6=high)`
  ) %>%
  dplyr::left_join(df2, by = c( "Country Name" = "Country Name")) %>%
  # rename(
  #   `IDA resource allocation index 2024` = `IDA resource allocation index (1=low to 6=high)`
  #  ) %>%
   select(`Country Name` ,`IDA resource allocation index 2023`, `IDA resource allocation index 2024`) %>%
    dplyr::left_join(rgi_scaled, by = c( "Country Name" = "name")) %>%
   select(`Country Name`, `IDA resource allocation index 2023`, `IDA resource allocation index 2024`, Score2017 , Score2021) %>%
  mutate(Increment = (Score2021-Score2017)/Score2017) %>%
  full_join(mi, by = c( "Country Name" = "reporterDesc"))




View(tb3)

#write.csv(fmf, "fmf.csv", row.names = FALSE)

tb3$non_na_count <- rowSums(!is.na(tb3 %>% select(-`Country Name`)))

# 找出非 NA 值最多的行
tb3[which.max(tb3$non_na_count), ]
tb4 = tb3 %>%
  select(`Country Name`, non_na_count) %>%
  arrange(desc(non_na_count)) 


```

```{r}
tb5 = tb3 %>% 
  select(`Country Name`, `IDA resource allocation index 2024`, percentage_Lithium, percentage_Cobalt, percentage_Graphite, percentage_Nickel)


write.csv(tb5, "Super Region Country minerals.csv", row.names = FALSE)
```

```{r}
cpia_22 = read_csv("2022.csv")
```

```{r}
cpia_22_high <- subset(cpia_22, `Series Name` %in% highlighted_vars)
df_22 = cpia_22_high %>% 
  select(`Country Name`, `Country Code`, `Series Name`, `2022 [YR2022]`) %>%
  pivot_wider(
    names_from = `Series Name`,
    values_from = `2022 [YR2022]`
  )


df2_22 <- df_22 %>%
  mutate(across(all_of(highlighted_vars), as.numeric)) %>%
  rowwise() %>%
  mutate(CPIA_Average_9 = mean(c_across(all_of(cpia_cols)), na.rm = TRUE)) %>%
  ungroup() %>%
  filter(`Country Name`  %in% fmf$Country) %>%
  select(`Country Name`,`IDA resource allocation index (1=low to 6=high)` ) %>%
  rename(
    `IDA resource allocation index 2022` = `IDA resource allocation index (1=low to 6=high)`
  ) %>%
   dplyr::left_join(df2, by = c( "Country Name" = "Country Name"))
  



df2 = df2 %>%
  select(`Country Name` , `IDA resource allocation index (1=low to 6=high)`) %>%
  rename(
    `IDA resource allocation index 2024` = `IDA resource allocation index (1=low to 6=high)`
  ) 
```

## GRAPHITE (NATURAL)

```{r}
library(tibble)

df_graphite <- tribble(
  ~Country,               ~Graphite_Production_2022, ~Graphite_Production_2023, ~Graphite_Reserves,
  "United States",       NA,                    NA,                    NA,
  "Austria",             500,                   500,                   NA,
  "Brazil",              72000,                 73000,                 74000000,
  "Canada",              13000,                 3500,                  5700000,
  "China",               1210000,               1230000,               78000000,
  "Germany",             170,                   150,                   NA,
  "India",               11000,                 11500,                 8600000,
  "Korea, North",        8100,                  8100,                  2000000,
  "Korea, Republic of",  23800,                 27000,                 1800000,
  "Madagascar",          130000,                100000,                24000000,
  "Mexico",              2000,                  2000,                  3100000,
  "Mozambique",          166000,                96000,                 25000000,
  "Norway",              10380,                 7200,                  600000,
  "Russia",              16000,                 16000,                 14000000,
  "Sri Lanka",           2600,                  2200,                  1500000,
  "Tanzania",            6120,                  6000,                  18000000,
  "Turkey",              2800,                  2000,                  6900000,
  "Ukraine",             1000,                  2000,                  NA,
  "Vietnam",             500,                   500,                   NA,
  "World total",         1680000,               1600000,               280000000
)

```

## NICKEL

```{r}


df_nickel <- tribble(
  ~Country,         ~Nickel_Production_2022, ~Nickel_Production_2023, ~Nickel_Reserves,
  "United States",        17500,                 17000,                340000,
  "Australia",           155000,                160000,              24000000,
  "Brazil",              88500,                 89000,               16000000,
  "Canada",              143000,                180000,              2200000,
  "China",               114000,                110000,              4200000,
  "Indonesia",           1580000,              1800000,             55000000,
  "New Caledonia",       200000,                230000,              7100000,
  "Philippines",         345000,                400000,              4800000,
  "Russia",              222000,                200000,              8300000,
  "Other countries",     404000,                380000,              9100000,
  "World total",        3270000,              3600000,            130000000
)

```

## COBALT

```{r}

df_cobalt <- tribble(
  ~Country,            ~Cobalt_Production_2022, ~Cobalt_Production_2023, ~Cobalt_Reserves,
  "United States",            500,                    500,              69000,
  "Australia",               5790,                   4600,            1700000,
  "Canada",                  3060,                   2100,             230000,
  "Congo (Kinshasa)",      144000,                 170000,            6000000,
  "Cuba",                   3700,                   3200,             500000,
  "Indonesia",              9600,                  17000,             500000,
  "Madagascar",             3500,                   4000,             100000,
  "New Caledonia",          2000,                   3000,                 NA,
  "Papua New Guinea",       2990,                   2900,              49000,
  "Philippines",            3900,                   3800,             260000,
  "Russia",                 9200,                   8800,             250000,
  "Turkey",                 2100,                   2800,              91000,
  "Other countries",        6600,                   6600,             780000,
  "World total",          197000,                 230000,           11000000
)

```

## LITHIUM

```{r}



df_lithium <- tribble(
  ~Country,         ~Lithium_Production_2023, ~Lithium_Production_2024, ~Lithium_Reserves,
  "United States",            NA,                    NA,              1800000,
  "Argentina",              8630,                 18000,              4000000,
  "Australia",             91700,                 88000,             57000000,
  "Brazil",                 5260,                 10000,               390000,
  "Canada",                 3240,                  4300,              1200000,
  "Chile",                 41400,                 49000,              9300000,
  "China",                 35700,                 41000,              3000000,
  "Namibia",                2700,                  2700,                14000,
  "Portugal",                380,                   380,                60000,
  "Zimbabwe",              14900,                 22000,               480000,
  "Other countries",          NA,                    NA,               2800000,
  "World total",          204000,                240000,             30000000
)

```

## MANGANESE

```{r}

df_manganese <- tribble(
  ~Country,                    ~Manganese_Production_2022, ~Manganese_Production_2023, ~Manganese_Reserves,
  "United States",                    NA,                    NA,                  NA,
  "Australia",                      3040,                  3000,             500000,
  "Brazil",                          624,                   620,              270000,
  "Burma",                           207,                   210,                   NA,
  "China",                           743,                   740,              280000,
  "Côte d’Ivoire",                   394,                   390,                   NA,
  "Gabon",                          4670,                  4600,               61000,
  "Georgia",                         166,                   160,                   NA,
  "Ghana",                           844,                   840,               13000,
  "India",                           721,                   720,               34000,
  "Kazakhstan, concentrate",         129,                   130,                5000,
  "Malaysia",                        247,                   250,                   NA,
  "Mexico",                          221,                   220,                5000,
  "South Africa",                   7300,                  7200,              600000,
  "Ukraine, concentrate",            323,                   320,              140000,
  "Vietnam",                         155,                   160,                   NA,
  "Other countries",                 325,                   330,               NA,  # "Small" mapped to NA
  "World total",                   19800,                20000,             1900000
)

```

## COPPER

```{r}

df_copper <- tribble(
  ~Country,             ~Copper_Production_2022, ~Copper_Production_2023, ~Copper_Refinery_Production_2022, ~Copper_Refinery_Production_2023, ~Copper_Reserves,
  "United States",             1230,                  1100,                     952,                        890,              50000,
  "Australia",                  819,                   810,                     401,                        450,             100000,
  "Canada",                     520,                   480,                     278,                        310,               7600,
  "Chile",                     5330,                  5000,                    2150,                       2000,             190000,
  "China",                     1940,                  1700,                   11100,                      12000,              41000,
  "Congo (Kinshasa)",          2350,                  2500,                    1770,                       1900,              80000,
  "Germany",                     NA,                    NA,                     609,                        610,                 NA,
  "Indonesia",                 941,                   840,                     310,                        200,              24000,
  "Japan",                       NA,                    NA,                    1550,                       1500,                 NA,
  "Kazakhstan",                593,                   600,                     494,                        440,              20000,
  "Korea, Republic of",          NA,                    NA,                     638,                        620,                 NA,
  "Mexico",                    754,                   750,                     486,                        480,              53000,
  "Peru",                      2450,                  2600,                     391,                        400,             120000,
  "Poland",                    393,                   400,                     586,                        590,              34000,
  "Russia",                    936,                   910,                    1010,                       1000,              80000,
  "Zambia",                    797,                   760,                     349,                        380,              21000,
  "Other countries",          2850,                  3100,                    2830,                       2900,             180000,
  "World total",             21900,                 22000,                   25900,                      27000,            1000000
)

```

```{r}
tb5$`Country Name`[tb5$`Country Name` == "Congo (DRC)"] <- "Congo (Kinshasa)"
tb6 = tb5 %>% 
  left_join(df_graphite, by = c("Country Name" = "Country")) %>%
  left_join(df_cobalt, by = c("Country Name" = "Country")) %>%
  left_join(df_copper,by = c("Country Name" = "Country")) %>%
  left_join(df_lithium,by = c("Country Name" = "Country")) %>%
  left_join(df_manganese,by = c("Country Name" = "Country")) %>%
  left_join(df_nickel,by = c("Country Name" = "Country")) %>%
  arrange(`Country Name`)


write.csv(tb6, "Super Region Country minerals.csv", row.names = FALSE)
```

# Correlation test

```{r}
congo = read_csv("cor.csv")


corr = read_csv("cor_matrix.csv")
fmf = read_csv("Commodity_super_region_ranked.csv")
```

```{r}
#congo = corr %>%
 # filter(`Series Name` !=  "Human capital index (HCI), upper bound (scale 0-1)" )
corr$`Country Name`[corr$`Country Name` == "Congo, Dem. Rep."] <- "Democratic Republic of the Congo"
corr$`Country Name`[corr$`Country Name` == "Congo, Rep."] <- "Congo (Republic of the)"
corr$`Country Name`[corr$`Country Name` == "Yemen, Rep."] = "Yemen"
corr$`Country Name`[corr$`Country Name` == "Gambia, The"] = "Gambia"
#corr$`Country Name`[corr$`Country Name` == "Sao Tome and Principe"] = "Sao Tome & Principe"
corr$`Country Name`[corr$`Country Name` == "Egypt, Arab Rep."] = "Egypt"
#corr$`Country Name`[corr$`Country Name` == "	
#Central African Republic"] = "Central African Republic"
corr$`Country Name`[corr$`Country Name` == "Syrian Arab Republic"] = "Syria"
corr$`Country Name`[corr$`Country Name` == "Kyrgyz Republic"] = "Kyrgyzstan"
#corr$`Country Name`[corr$`Country Name` == "Morocco"] = "Morroco"
#corr$`Country Name`[corr$`Country Name` == "South Africa"] = "South Africa (RSA)"
```

```{r}
# 指定保留的 series 列表
series_keep <- c(
  "Average time to clear exports through customs (days)",

  "Liner shipping connectivity index (maximum value in 2004 = 100)",
  
  "Rail lines (total route-km)",
  "Container port traffic (TEU: 20 foot equivalent units)"
)

df_clean <- corr %>%
  select(`Country Name`,`Series Name`, `2020 [YR2020]`) %>%
  #filter(`2020 [YR2020]` != "..") %>%
  mutate(`2020 [YR2020]` = as.numeric(`2020 [YR2020]`)) %>%
  filter(`Country Name` %in% fmf$Country.Name)%>%
  filter(`Series Name` %in% series_keep)
  #filter(`Country Name` != "Afghanistan")

df_clean_2 <- corr %>%
  select(`Country Name`,`Series Name`, `2020 [YR2020]`) %>%
  mutate(`2020 [YR2020]` = as.numeric(`2020 [YR2020]`)) %>%
  filter(`Country Name` %in% fmf$Country)
#df_focus <- df_clean %>%
 # filter(`Series Name` %in% series_keep)


df_wide_focus <- df_clean %>%
  select(`Country Name`, `Series Name`, `2020 [YR2020]`) %>%
  pivot_wider(names_from = `Series Name`, values_from = `2020 [YR2020]`)


cor_focus <- cor(df_wide_focus %>% select(-`Country Name`), use = "pairwise.complete.obs")


View(round(cor_focus, 5))


```

```{r fig.height=10, fig.width=15, echo=FALSE, message=FALSE, warning=FALSE}
#library(ggplot2)
#library(reshape2)

# reshape for ggplot
cor_melted <- melt(cor_focus, na.rm = TRUE)

ggplot(data = cor_melted, aes(x = Var1, y = Var2, fill = value)) +
  geom_tile(color = "white") +
  scale_fill_gradient2(low = "blue", high = "red", mid = "white", 
                       midpoint = 0, limit = c(-1,1), space = "Lab", 
                       name="Pearson\nCorrelation") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, vjust = 1, hjust = 1)) +
  theme(
    axis.text.y = element_text(face = "bold")
  )+
  geom_text(aes(label = round(value, 2)), size = 3) 

```

```{r fig.height=8, fig.width=15, echo=FALSE, message=FALSE, warning=FALSE}
library(ggplot2)
library(reshape2)

# melt correlation matrix
cor_melted <- melt(cor_focus, na.rm = TRUE)

# 添加标记字段：是否显著
cor_melted$label_color <- ifelse(abs(cor_melted$value) > 0.5, "red", "black")
cor_melted$label_fontface <- ifelse(abs(cor_melted$value) > 0.5, "bold", "plain")

# 绘图
ggplot(data = cor_melted, aes(x = Var1, y = Var2, fill = value)) +
  geom_tile(color = "white") +
  geom_text(aes(label = round(value, 4), color = label_color, fontface = label_fontface), size = 3) +
  scale_fill_gradient2(
    low = "green", high = "yellow", mid = "white",
    midpoint = 0, limit = c(-1,1), space = "Lab",
    name = "Pearson\nCorrelation"
  ) +
  scale_color_identity() +  # 使用原始颜色变量
  theme_minimal() +
  theme(
    axis.text.x = element_text(angle = 45, vjust = 1, hjust = 1),
    axis.text.y = element_text(face = "bold")
  )

```

## 5indicators for all FMF countries in 2020, sub GDP with newest 2024 or 2023

```{r}


library(dplyr)

# Step 1: 先把 ".." 替换成 NA，并转为 numeric
xx <- corr %>%
  mutate(
    `2024 [YR2024]` = na_if(`2024 [YR2024]`, ".."),
    `2023 [YR2023]` = na_if(`2023 [YR2023]`, ".."),
    `2024 [YR2024]` = as.numeric(`2024 [YR2024]`),
    `2023 [YR2023]` = as.numeric(`2023 [YR2023]`)
  )

# Step 2: 替换 GDP per capita 的 2020 列为 2024，若没有则用 2023
xx <- xx %>%
  mutate(
    `2020 [YR2020]` = ifelse(
      `Series Name` == "GDP per capita (current US$)",
      coalesce(`2024 [YR2024]`, `2023 [YR2023]`),
      `2020 [YR2020]`
    ),
    `2020 [YR2020]` = ifelse(
      `Series Name` == "GDP growth (annual %)",
      coalesce(`2024 [YR2024]`, `2023 [YR2023]`),
      `2020 [YR2020]`
    )
  )
corr = xx

df_wide_2 <- df_clean %>%
  pivot_wider(
    names_from = `Series Name`,
    values_from = `2020 [YR2020]`
  )


series_keep_2 <- c(
  "GDP growth (annual %)",
  "GDP per capita (current US$)"
)

df_clean_2 <- corr %>%
  select(`Country Name`,`Series Name`, `2020 [YR2020]`) %>%
  #filter(`2020 [YR2020]` != "..") %>%
  mutate(`2020 [YR2020]` = as.numeric(`2020 [YR2020]`)) %>%
  #filter(`Country Name` %in% fmf$Country.Name)%>%
  filter(`Series Name` %in% series_keep_2)


df_wide <- df_clean_2 %>%
  pivot_wider(
    names_from = `Series Name`,
    values_from = `2020 [YR2020]`
  )


result = df_wide %>% 
  left_join(df_wide_2, by = c("Country Name" = "Country Name"))


readr::write_csv(result, "all_countries_5_indicators.csv")

```
