Data Analysis Bootcamp: Data Tidying

Author

Yuhao Shen

#HW 4 R Data Tidying

Preparation

Loading necessary libraries

#install.packages("countrycode")
library(dplyr)


Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union

library(countrycode)
library(tidyverse) # because, always

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ forcats   1.0.0     ✔ readr     2.1.5
✔ ggplot2   3.4.4     ✔ stringr   1.5.1
✔ lubridate 1.9.3     ✔ tibble    3.2.1
✔ purrr     1.0.2     ✔ tidyr     1.3.1

── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(janitor) # for clean_names() - makes variable names snake_case


Attaching package: 'janitor'

The following objects are masked from 'package:stats':

    chisq.test, fisher.test

Importing Data set

imf_climate_dashboards_green_debt_url <- "https://opendata.arcgis.com/datasets/8e2772e0b65f4e33a80183ce9583d062_0.csv"

green_debt <- imf_climate_dashboards_green_debt_url |> 
  read_csv()

Rows: 355 Columns: 42
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (12): Country, ISO2, ISO3, Indicator, Unit, Source, CTS_Code, CTS_Name, ...
dbl (30): ObjectId, F1985, F1986, F1987, F1990, F1991, F1992, F1993, F1994, ...

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

green_debt

# A tibble: 355 × 42
   ObjectId Country         ISO2  ISO3  Indicator Unit  Source CTS_Code CTS_Name
      <dbl> <chr>           <chr> <chr> <chr>     <chr> <chr>  <chr>    <chr>   
 1        1 Argentina       AR    ARG   Green Bo… Bill… Refin… ECFFI    Green B…
 2        2 Australia       AU    AUS   Green Bo… Bill… Refin… ECFFI    Green B…
 3        3 Austria         AT    AUT   Green Bo… Bill… Refin… ECFFI    Green B…
 4        4 Austria         AT    AUT   Sovereig… Bill… Refin… ECFF     Green B…
 5        5 Bangladesh      BD    BGD   Green Bo… Bill… Refin… ECFFI    Green B…
 6        6 Belarus, Rep. … BY    BLR   Green Bo… Bill… Refin… ECFFI    Green B…
 7        7 Belarus, Rep. … BY    BLR   Sovereig… Bill… Refin… ECFF     Green B…
 8        8 Belgium         BE    BEL   Green Bo… Bill… Refin… ECFFI    Green B…
 9        9 Belgium         BE    BEL   Sovereig… Bill… Refin… ECFF     Green B…
10       10 Bermuda         BM    BMU   Green Bo… Bill… Refin… ECFFI    Green B…
# ℹ 345 more rows
# ℹ 33 more variables: CTS_Full_Descriptor <chr>, Type_of_Issuer <chr>,
#   Use_of_Proceed <chr>, Principal_Currency <chr>, F1985 <dbl>, F1986 <dbl>,
#   F1987 <dbl>, F1990 <dbl>, F1991 <dbl>, F1992 <dbl>, F1993 <dbl>,
#   F1994 <dbl>, F1999 <dbl>, F2000 <dbl>, F2002 <dbl>, F2003 <dbl>,
#   F2004 <dbl>, F2007 <dbl>, F2008 <dbl>, F2009 <dbl>, F2010 <dbl>,
#   F2011 <dbl>, F2012 <dbl>, F2013 <dbl>, F2014 <dbl>, F2015 <dbl>, …

Tidying Up

# we want to compare these two indicators
indicators_we_want <- c("Green Bond Issuances by Country", "Sovereign Green Bond Issuances")

green_debt_subset <- green_debt |> 
  # from the janitor package -- makes variables snake_case so they are easier to work with
  clean_names() |> 
  # filter for the vector of indicators we defined above
  filter(indicator %in% indicators_we_want) |> 
  # "f\\d{4}" is a regular expression (regex) that searches for all columns that are f + four digits.
  # Ask ChatGPT to explain this to you.
  select(country, iso3, indicator, matches("f\\d{4}")) 

green_debt_subset

# A tibble: 107 × 32
   country iso3  indicator f1985 f1986 f1987 f1990 f1991 f1992 f1993 f1994 f1999
   <chr>   <chr> <chr>     <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1 Argent… ARG   Green Bo…    NA    NA    NA    NA    NA    NA    NA    NA    NA
 2 Austra… AUS   Green Bo…    NA    NA    NA    NA    NA    NA    NA    NA    NA
 3 Austria AUT   Green Bo…    NA    NA    NA    NA    NA    NA    NA    NA    NA
 4 Austria AUT   Sovereig…    NA    NA    NA    NA    NA    NA    NA    NA    NA
 5 Bangla… BGD   Green Bo…    NA    NA    NA    NA    NA    NA    NA    NA    NA
 6 Belaru… BLR   Green Bo…    NA    NA    NA    NA    NA    NA    NA    NA    NA
 7 Belaru… BLR   Sovereig…    NA    NA    NA    NA    NA    NA    NA    NA    NA
 8 Belgium BEL   Green Bo…    NA    NA    NA    NA    NA    NA    NA    NA    NA
 9 Belgium BEL   Sovereig…    NA    NA    NA    NA    NA    NA    NA    NA    NA
10 Bermuda BMU   Green Bo…    NA    NA    NA    NA    NA    NA    NA    NA    NA
# ℹ 97 more rows
# ℹ 20 more variables: f2000 <dbl>, f2002 <dbl>, f2003 <dbl>, f2004 <dbl>,
#   f2007 <dbl>, f2008 <dbl>, f2009 <dbl>, f2010 <dbl>, f2011 <dbl>,
#   f2012 <dbl>, f2013 <dbl>, f2014 <dbl>, f2015 <dbl>, f2016 <dbl>,
#   f2017 <dbl>, f2018 <dbl>, f2019 <dbl>, f2020 <dbl>, f2021 <dbl>,
#   f2022 <dbl>

Problem 1

Objectives: Use the countrycode package to add the region as a variable to green_debt_subset. Calculate the cumulative issuance of green bonds by region. Use ggplot2 to make a data visualization comparing regional cumulative issuance. Make your plot look professionally presentable, as we did above.

# Sorting out data
#?countrycode
green_bonds_tidy <- green_debt_subset |> 
  pivot_longer(
    # select all coluns with f + 4 numbers 
    cols = matches("f\\d{4}"),
    
    # change from default ("names")
    names_to = "year",
    # same with the values
    values_to = "issuance_bn_usd",
    
    # readr::parse_number is a handy function that changes the character string 
    # "f2222" into the number 2222. Very useful! 
    names_transform = readr::parse_number,
    
    # green bonds are new-ish, we can drop all those NA values in the 80s and 90s for now.
    values_drop_na = TRUE,
  ) |>
  mutate(region = countrycode(iso3, "iso3c", "region"))

green_bonds_tidy

# A tibble: 465 × 6
   country   iso3  indicator                        year issuance_bn_usd region 
   <chr>     <chr> <chr>                           <dbl>           <dbl> <chr>  
 1 Argentina ARG   Green Bond Issuances by Country  2017          0.974  Latin …
 2 Argentina ARG   Green Bond Issuances by Country  2020          0.0500 Latin …
 3 Argentina ARG   Green Bond Issuances by Country  2021          0.916  Latin …
 4 Argentina ARG   Green Bond Issuances by Country  2022          0.207  Latin …
 5 Australia AUS   Green Bond Issuances by Country  2014          0.526  East A…
 6 Australia AUS   Green Bond Issuances by Country  2015          0.413  East A…
 7 Australia AUS   Green Bond Issuances by Country  2016          0.531  East A…
 8 Australia AUS   Green Bond Issuances by Country  2017          2.53   East A…
 9 Australia AUS   Green Bond Issuances by Country  2018          2.22   East A…
10 Australia AUS   Green Bond Issuances by Country  2019          1.98   East A…
# ℹ 455 more rows

# Calculating total issuance by region
total_issuance_by_region <- green_bonds_tidy %>%
  group_by(region) %>%
  summarize(total_issuance = sum(issuance_bn_usd))

total_issuance_by_region

# A tibble: 7 × 2
  region                     total_issuance
  <chr>                               <dbl>
1 East Asia & Pacific                 586. 
2 Europe & Central Asia              1395. 
3 Latin America & Caribbean            99.8
4 Middle East & North Africa           10.3
5 North America                       239. 
6 South Asia                           15.0
7 Sub-Saharan Africa                   16.2

Plotting

ggplot(total_issuance_by_region) +
  aes(x = total_issuance, y = region, fill = region) +
  geom_col() +
  scale_fill_hue(direction = 1) +
  scale_x_continuous(trans = "log10") +
  labs(
    x = "Billions USD",
    y = "Region",
    title = "Total Issuance of Green Bonds by Region",
    subtitle = "X axis log10"
  ) +
  theme_minimal() +
  theme(plot.title = element_text(face = "bold"))

Problem 2

Use the full green_debt dataset Use clean_names() to make the variable names snake_case Filter out observations where type_of_issuer is “Not Applicable” Use the tools taught in this chapter to provide a compelling data visualization and some repeatable factoids that provide actionable insights about green bond issuers.

green_debt_q2 <- green_debt %>%
  # from the janitor package -- makes variables snake_case so they are easier to work with
  clean_names() |> 
  # filter for the vector of indicators we defined above
  filter(type_of_issuer != "Not Applicable") |> 
  pivot_longer(
    cols = matches("f\\d{4}"),
    names_to = "year",
    values_to = "issuance_bn_usd",
    names_transform = readr::parse_number,
    values_drop_na = TRUE,
  ) |>
  mutate(region = countrycode(iso3, "iso3c", "region"))

Warning: There was 1 warning in `mutate()`.
ℹ In argument: `region = countrycode(iso3, "iso3c", "region")`.
Caused by warning:
! Some values were not matched unambiguously: WLD

green_debt_q2

# A tibble: 98 × 16
   object_id country iso2  iso3  indicator        unit  source cts_code cts_name
       <dbl> <chr>   <chr> <chr> <chr>            <chr> <chr>  <chr>    <chr>   
 1       347 World   <NA>  WLD   Green Bond Issu… Bill… Refin… ECFFI    Green B…
 2       347 World   <NA>  WLD   Green Bond Issu… Bill… Refin… ECFFI    Green B…
 3       347 World   <NA>  WLD   Green Bond Issu… Bill… Refin… ECFFI    Green B…
 4       347 World   <NA>  WLD   Green Bond Issu… Bill… Refin… ECFFI    Green B…
 5       347 World   <NA>  WLD   Green Bond Issu… Bill… Refin… ECFFI    Green B…
 6       347 World   <NA>  WLD   Green Bond Issu… Bill… Refin… ECFFI    Green B…
 7       347 World   <NA>  WLD   Green Bond Issu… Bill… Refin… ECFFI    Green B…
 8       347 World   <NA>  WLD   Green Bond Issu… Bill… Refin… ECFFI    Green B…
 9       347 World   <NA>  WLD   Green Bond Issu… Bill… Refin… ECFFI    Green B…
10       347 World   <NA>  WLD   Green Bond Issu… Bill… Refin… ECFFI    Green B…
# ℹ 88 more rows
# ℹ 7 more variables: cts_full_descriptor <chr>, type_of_issuer <chr>,
#   use_of_proceed <chr>, principal_currency <chr>, year <dbl>,
#   issuance_bn_usd <dbl>, region <chr>

green_debt_q2_tidy <- green_debt_q2 %>% 
  select(country, iso3, indicator, type_of_issuer, year, issuance_bn_usd) %>%
  group_by(type_of_issuer, year) %>%
  mutate(total_issuance = sum(issuance_bn_usd))

green_debt_q2_tidy

# A tibble: 98 × 7
# Groups:   type_of_issuer, year [98]
   country iso3  indicator   type_of_issuer  year issuance_bn_usd total_issuance
   <chr>   <chr> <chr>       <chr>          <dbl>           <dbl>          <dbl>
 1 World   WLD   Green Bond… Banks           1985          0.03           0.03  
 2 World   WLD   Green Bond… Banks           1986          0.3            0.3   
 3 World   WLD   Green Bond… Banks           1987          0.0751         0.0751
 4 World   WLD   Green Bond… Banks           1990          0              0     
 5 World   WLD   Green Bond… Banks           1999          0.0482         0.0482
 6 World   WLD   Green Bond… Banks           2000          0.0533         0.0533
 7 World   WLD   Green Bond… Banks           2002          0.148          0.148 
 8 World   WLD   Green Bond… Banks           2003          0.01           0.01  
 9 World   WLD   Green Bond… Banks           2004          0.109          0.109 
10 World   WLD   Green Bond… Banks           2013          0.757          0.757 
# ℹ 88 more rows

green_debt_q2_tidy %>%
  ggplot(aes(x = year, y = total_issuance, fill = type_of_issuer)) +
    geom_col() +
    scale_x_continuous(limits = c(2012, 2022)) +
    scale_y_continuous(labels = scales::label_dollar(suffix = " bn")) +
    labs(title = "Total Green Bond Issuance by Type of Issuer Over Years", x = "Year", y = "Total Issuance (in billions USD)") +
    theme_minimal() +
    theme(legend.position = "bottom")

Warning: Removed 27 rows containing missing values (`position_stack()`).

Warning: Removed 11 rows containing missing values (`geom_col()`).

From the chart what we find interesting is that non-financial organizations are issuing the largest shares of green bonds, indicating a possibly significant engagement from the private corporate sector in raising capital for “green” projects. This could suggest a strong need for corporate to raise capital for sustainability matters.

Problem 3

Repeat the process from problem 2 for use_of_proceed and for principal_currency What are green bond proceeds used for? What do we know about the currency of issuance? Is that changing over time?

green_debt_q3.1 <- green_debt %>%
  clean_names() |> 
  filter(use_of_proceed != "Not Applicable") |> 
  pivot_longer(
    cols = matches("f\\d{4}"),
    names_to = "year",
    values_to = "issuance_bn_usd",
    names_transform = readr::parse_number,
    values_drop_na = TRUE,
  ) |>
  mutate(region = countrycode(iso3, "iso3c", "region"))

Warning: There was 1 warning in `mutate()`.
ℹ In argument: `region = countrycode(iso3, "iso3c", "region")`.
Caused by warning:
! Some values were not matched unambiguously: WLD

green_debt_q3.1

# A tibble: 138 × 16
   object_id country iso2  iso3  indicator        unit  source cts_code cts_name
       <dbl> <chr>   <chr> <chr> <chr>            <chr> <chr>  <chr>    <chr>   
 1       209 World   <NA>  WLD   Cumulative Gree… Share Refin… ECFF     Green B…
 2       210 World   <NA>  WLD   Cumulative Gree… Bill… Refin… ECFF     Green B…
 3       211 World   <NA>  WLD   Cumulative Gree… Share Refin… ECFF     Green B…
 4       212 World   <NA>  WLD   Cumulative Gree… Bill… Refin… ECFF     Green B…
 5       213 World   <NA>  WLD   Cumulative Gree… Share Refin… ECFF     Green B…
 6       214 World   <NA>  WLD   Cumulative Gree… Bill… Refin… ECFF     Green B…
 7       215 World   <NA>  WLD   Cumulative Gree… Share Refin… ECFF     Green B…
 8       216 World   <NA>  WLD   Cumulative Gree… Bill… Refin… ECFF     Green B…
 9       217 World   <NA>  WLD   Cumulative Gree… Share Refin… ECFF     Green B…
10       218 World   <NA>  WLD   Cumulative Gree… Bill… Refin… ECFF     Green B…
# ℹ 128 more rows
# ℹ 7 more variables: cts_full_descriptor <chr>, type_of_issuer <chr>,
#   use_of_proceed <chr>, principal_currency <chr>, year <dbl>,
#   issuance_bn_usd <dbl>, region <chr>

green_debt_q3.1_tidy <- green_debt_q3.1 %>% 
  select(country, iso3, indicator, use_of_proceed, year, issuance_bn_usd) %>%
  group_by(use_of_proceed, year) %>%
  mutate(total_issuance = sum(issuance_bn_usd)) %>%
  arrange(desc(total_issuance)) %>%
  ungroup() %>%
  top_n(10, total_issuance)

green_debt_q3.1_tidy

# A tibble: 10 × 7
   country iso3  indicator   use_of_proceed  year issuance_bn_usd total_issuance
   <chr>   <chr> <chr>       <chr>          <dbl>           <dbl>          <dbl>
 1 World   WLD   Cumulative… Clean Transpo…  2022          0.365            834.
 2 World   WLD   Cumulative… Clean Transpo…  2022        833.               834.
 3 World   WLD   Cumulative… Energy Effici…  2022          0.210            480.
 4 World   WLD   Cumulative… Energy Effici…  2022        480.               480.
 5 World   WLD   Cumulative… Climate Chang…  2022          0.121            277.
 6 World   WLD   Cumulative… Climate Chang…  2022        277.               277.
 7 World   WLD   Cumulative… Eligible Gree…  2022          0.0893           204.
 8 World   WLD   Cumulative… Eligible Gree…  2022        204.               204.
 9 World   WLD   Cumulative… Green Constru…  2022          0.0557           127.
10 World   WLD   Cumulative… Green Constru…  2022        127.               127.

green_debt_q3.1_tidy %>%
  ggplot(aes(x = use_of_proceed, y = total_issuance, fill = use_of_proceed)) +
    geom_col() +
    scale_y_continuous(labels = scales::label_dollar(suffix = " bn")) +
    labs(title = "Top 10 Green Bond Issuance by Use of Proceed Over Years", x = "Use of Proceed", y = "Total Issuance (in billions USD)") +
    theme_minimal() +
    theme(legend.position = "right") +
    theme(axis.text.x = element_text(angle = 45, hjust = 1))

green_debt_q3.2 <- green_debt %>%
  clean_names() |> 
  filter(principal_currency != "Not Applicable") |> 
  pivot_longer(
    cols = matches("f\\d{4}"),
    names_to = "year",
    values_to = "issuance_bn_usd",
    names_transform = readr::parse_number,
    values_drop_na = TRUE,
  ) |>
  mutate(region = countrycode(iso3, "iso3c", "region"))

Warning: There was 1 warning in `mutate()`.
ℹ In argument: `region = countrycode(iso3, "iso3c", "region")`.
Caused by warning:
! Some values were not matched unambiguously: WLD

green_debt_q3.2

# A tibble: 100 × 16
   object_id country iso2  iso3  indicator        unit  source cts_code cts_name
       <dbl> <chr>   <chr> <chr> <chr>            <chr> <chr>  <chr>    <chr>   
 1       109 World   <NA>  WLD   Cumulative Gree… Share Refin… ECFF     Green B…
 2       110 World   <NA>  WLD   Cumulative Gree… Bill… Refin… ECFF     Green B…
 3       111 World   <NA>  WLD   Cumulative Gree… Share Refin… ECFF     Green B…
 4       112 World   <NA>  WLD   Cumulative Gree… Bill… Refin… ECFF     Green B…
 5       113 World   <NA>  WLD   Cumulative Gree… Share Refin… ECFF     Green B…
 6       114 World   <NA>  WLD   Cumulative Gree… Bill… Refin… ECFF     Green B…
 7       115 World   <NA>  WLD   Cumulative Gree… Share Refin… ECFF     Green B…
 8       116 World   <NA>  WLD   Cumulative Gree… Bill… Refin… ECFF     Green B…
 9       117 World   <NA>  WLD   Cumulative Gree… Share Refin… ECFF     Green B…
10       118 World   <NA>  WLD   Cumulative Gree… Bill… Refin… ECFF     Green B…
# ℹ 90 more rows
# ℹ 7 more variables: cts_full_descriptor <chr>, type_of_issuer <chr>,
#   use_of_proceed <chr>, principal_currency <chr>, year <dbl>,
#   issuance_bn_usd <dbl>, region <chr>

green_debt_q3.2_tidy <- green_debt_q3.2 %>% 
  select(country, iso3, indicator, principal_currency , year, issuance_bn_usd) %>%
  group_by(principal_currency, year) %>%
  mutate(total_issuance = sum(issuance_bn_usd)) %>%
  ungroup() 

green_debt_q3.2_tidy

# A tibble: 100 × 7
   country iso3  indicator              principal_currency  year issuance_bn_usd
   <chr>   <chr> <chr>                  <chr>              <dbl>           <dbl>
 1 World   WLD   Cumulative Green Bond… Argentinian Peso    2022       0.0000105
 2 World   WLD   Cumulative Green Bond… Argentinian Peso    2022       0.0240   
 3 World   WLD   Cumulative Green Bond… Argentinian Unida…  2022       0        
 4 World   WLD   Cumulative Green Bond… Argentinian Unida…  2022       0        
 5 World   WLD   Cumulative Green Bond… Australian Dollar   2022       0.0122   
 6 World   WLD   Cumulative Green Bond… Australian Dollar   2022      27.8      
 7 World   WLD   Cumulative Green Bond… Bangladeshi Taka    2022       0.000170 
 8 World   WLD   Cumulative Green Bond… Bangladeshi Taka    2022       0.387    
 9 World   WLD   Cumulative Green Bond… Brazilian Real      2022       0.00214  
10 World   WLD   Cumulative Green Bond… Brazilian Real      2022       4.89     
# ℹ 90 more rows
# ℹ 1 more variable: total_issuance <dbl>

#esquisser(green_debt_q3.2_tidy)

green_debt_q3.2_tidy %>%
  ggplot(aes(x = total_issuance, y = principal_currency)) +
    geom_col() +
    scale_x_continuous(labels = scales::label_dollar(suffix = " bn")) +
    labs(title = "Green Bonds Issuance by currency in 2022", x = "Total Issuance", y = "Curreny") +
    theme_minimal() +
    theme(legend.position = "right")