First, clean the use-of-proceeds bonds data from the International Capital Markets Society. I do this two ways. First, I create a tibble in which there are all the included variables and a column for years included, and then I create one with the individual years as columns and filled them with the “Amount Issued” variable.
Second, I’ll do the same thing with the IRENA data on renewable energy investment. This one is easy, since there are no years included as it covers 2023 exclusively. I have to create one tibble here with all the variables in the sheet.
Third and Fourth, I will have to clean the data on infrastructure investment (Public sector and Private sector). This will be my biggest task, and I will have to get a sense of what I am doing here while I do it since I ultimately need to merge these two separate data-sets into one to engage in the comparison I want to.
Finally, I will engage in data analysis for each of these categories. In the final product, I will likely do outside research to make sense of my results.
My Analysis So Far
Ultimately my goal is to see why green bonds are published more frequently than social bonds, and my data is meant to demonstrate that there is a greater interest in private sector investment in renewables than in non-energy infrastructure (as a stand-in for social causes). My data has been in-line with this thesis so far, but my method so far is very flawed.
The Pie Chart shows that Green Bonds are in the highest demand out of all Use of Proceeds Bonds, using data from an ICMA partnership with LuxSE
The first column graph compares public sector and private sector investment in renewable energy over time, using data from the International Renewable Energy Agency. I have the ability to use this data to get granular about exactly who invests in renewables, which I will do in the final deliverable.
My third and fourth charts are incomplete because I have yet to finish tidying this data. I mention what is incomplete about them in the “Issues I will fix” section of this draft.
Issues I Will Fix
The largest thorn in my side is the private vs public sector infrastructure data I am working with, since its becoming clear to me that the datasets are “apples and oranges” and I need to change either how I interpret it or my data frame itself (and I suspect that it is the latter). The public sector data covers all types of infrastructure, but the private sector data only covers non-energy infrastructure. What I need to do is get public non-energy infrastructure data.
Also, I need to find a way to merge my tibbles for public and private infrastructure, as they use different formats for “regions” which is very annoying.
Also, the public and private sector infrastructure data has different time frames. I may end up comparing snapshots of time where they both line up.
Outside of this, I also don’t really understand the “breaking things into different files” decision. I source my raw data into this document and then edit it. Im not sure what I am supposed to do here, though I will certainly figure it out before the final deadline.
library(tidyverse)
Warning: package 'tidyverse' was built under R version 4.3.2
Warning: package 'ggplot2' was built under R version 4.3.2
Warning: package 'dplyr' was built under R version 4.3.2
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr 1.1.3 ✔ readr 2.1.4
✔ forcats 1.0.0 ✔ stringr 1.5.0
✔ ggplot2 3.4.4 ✔ tibble 3.2.1
✔ lubridate 1.9.2 ✔ tidyr 1.3.0
✔ purrr 1.0.2
── 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(readxl)
Warning: package 'readxl' was built under R version 4.3.2
library(here)
Warning: package 'here' was built under R version 4.3.2
here() starts at C:/Users/adams/OneDrive/Documents
library(patchwork)
Warning: package 'patchwork' was built under R version 4.3.2
library(esquisse)
Warning: package 'esquisse' was built under R version 4.3.2
#Part 1: ICMA Data on Use of Funds Bondspath_to_sheet_1 <-here("C:/Users/adams/Downloads/Data Raw", "ICMA-Export.xlsx")read_ICMA_sheet <-partial(.f = read_excel,path = path_to_sheet_1,sheet ="ICMA_LGX_Data_Table",col_names =FALSE)sheet_header_1 <-read_ICMA_sheet(range ="B4:O4")
# A tibble: 2,941 × 14
`Issuer Name` `Issuer location` `NA` `Issuer sector` `Bond type`
<chr> <chr> <lgl> <chr> <chr>
1 2W ENERGIA SA Brazil NA Corporate - Uti… Green
2 360 ENERGY SOLAR SA Argentina NA Corporate - Ene… Green
3 A.P. MOLLER - MAERSK A/S Denmark NA Corporate - Ind… Green
4 A2A S.P.A. Italy NA Corporate - Uti… Green
5 A2A S.P.A. Italy NA Corporate - Uti… Sustainabi…
6 AAREAL BANK AG Germany NA Financials - Co… Green
7 AARGAUISCHE KANTONALBANK Switzerland NA Financials - Ba… Green
8 AAVAS FINANCIERS LTD India NA Financials - Co… Social
9 AB ELECTROLUX (PUBL) Sweden NA Corporate - Con… Green
10 AB IGNITIS GRUPE Lithuania NA Corporate - Uti… Green
# ℹ 2,931 more rows
# ℹ 9 more variables: `Bonds per type` <chr>, `Amount issued (USD bn.)` <dbl>,
# `Lastest External Review Form` <chr>, `Last Url Form` <chr>,
# `External Reviewer\r\n` <chr>, `Latest External Review Report` <chr>,
# `Last Url Report` <chr>, `Latest Issuer Information Template` <chr>,
# `Last Url Template` <chr>
# A tibble: 2,941 × 15
`Issuer Name` `Issuer location` `NA` `Issuer sector` `Bond type`
<chr> <chr> <lgl> <chr> <chr>
1 2W ENERGIA SA Brazil NA Corporate - Uti… Green
2 360 ENERGY SOLAR SA Argentina NA Corporate - Ene… Green
3 A.P. MOLLER - MAERSK A/S Denmark NA Corporate - Ind… Green
4 A2A S.P.A. Italy NA Corporate - Uti… Green
5 A2A S.P.A. Italy NA Corporate - Uti… Sustainabi…
6 AAREAL BANK AG Germany NA Financials - Co… Green
7 AARGAUISCHE KANTONALBANK Switzerland NA Financials - Ba… Green
8 AAVAS FINANCIERS LTD India NA Financials - Co… Social
9 AB ELECTROLUX (PUBL) Sweden NA Corporate - Con… Green
10 AB IGNITIS GRUPE Lithuania NA Corporate - Uti… Green
# ℹ 2,931 more rows
# ℹ 10 more variables: `Bonds per type` <chr>, `Amount issued (USD bn.)` <dbl>,
# `Lastest External Review Form` <chr>, `Last Url Form` <chr>,
# `External Reviewer\r\n` <chr>, `Latest External Review Report` <chr>,
# `Last Url Report` <chr>, `Latest Issuer Information Template` <chr>,
# `Last Url Template` <chr>, `Total Issuance (USD bn.)` <dbl>
# A tibble: 1 × 8
...1 ...2 ...3 ...4 ...5 ...6 ...7 ...8
<chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 Year Country Country Code Region Income Group Population Total infrast… GDP …
# A tibble: 1,426 × 8
Year Country `Country Code` Region `Income Group` Population
<dbl> <chr> <chr> <chr> <chr> <dbl>
1 2007 Angola AGO Africa Africa 20905360
2 2007 Argentina ARG Latin America Latin America 39684303
3 2007 Australia AUS Oceania Oceania 20827600
4 2007 Azerbaijan AZE Middle East Middle East 8581300
5 2007 Bangladesh BGD Asia Asia 142660381
6 2007 Benin BEN Africa Africa 8454790
7 2007 Brazil BRA Latin America Latin America 190130445
8 2007 Cambodia KHM Asia Asia 13679953
9 2007 Canada CAN North America North America 32889025
10 2007 Chile CHL Latin America Latin America 16530201
# ℹ 1,416 more rows
# ℹ 2 more variables:
# `Total infrastructure investment per capita, constant 2015 USD` <chr>,
# `GDP per capita, current USD` <chr>
# A tibble: 8 × 2
region Region_total
<chr> <dbl>
1 Africa 1.93e14
2 Asia 3.26e15
3 Eastern Europe 6.95e13
4 Latin America 1.63e14
5 Middle East 3.28e13
6 North America 1.90e14
7 Oceania 1.60e13
8 Western Europe 2.33e14
GIH_info_yearschange <- GIH_info_better %>%select(-`Population`, -`GDP per capita, current USD`) %>%pivot_wider(names_from = Year, values_from =`Total infrastructure investment per capita, constant 2015 USD`)GIH_info_yearschange
# A tibble: 56 × 18
Country `Country Code` Region `Income Group` `2007` `2008` `2009` `2010`
<chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 Angola AGO Africa Africa 202.4… 202.8… 202.7… 206.1…
2 Argentina ARG Latin A… Latin America 409.8… 346.7… 310.3… 386.1…
3 Australia AUS Oceania Oceania 1640.… 1724.… 1782.… 1809.…
4 Azerbaijan AZE Middle … Middle East 233.5… 389.9… 329.5… 361.8…
5 Bangladesh BGD Asia Asia 46.85… 42.05… 37.40… 39.08…
6 Benin BEN Africa Africa 40.47… 27.58… 29.67… 33.77…
7 Brazil BRA Latin A… Latin America 284.7… 327.4… 348.1… 227.2…
8 Cambodia KHM Asia Asia 27.95… 57.37… 26.58… 25.68…
9 Canada CAN North A… North America 907.3… 980.1… 1078.… 1259.…
10 Chile CHL Latin A… Latin America 344.4… 318.0… 396.6… 326.9…
# ℹ 46 more rows
# ℹ 10 more variables: `2011` <chr>, `2012` <chr>, `2013` <chr>, `2014` <chr>,
# `2015` <chr>, `2016` <chr>, `2017` <chr>, `2018` <chr>, `2019` <chr>,
# `2020` <chr>
View(GIH_info_yearschange)#Part 2: IRENA data for Renewable Energy Investorspath_to_sheet_2 <-here("C:/Users/adams/Downloads/Data Raw", "2023_IRENA-CPI_RE_Investment_Trends.xlsx")read_IRENA_sheet <-partial(.f = read_excel,path = path_to_sheet_2,sheet ="IRENA-CPI RE Investments",col_names =FALSE)sheet_header_2 <-read_IRENA_sheet(range ="A1:D1")
# A tibble: 419 × 6
region `primary sector` `investment year` Payment commitments …¹
<chr> <chr> <chr> <dbl>
1 East Asia and Paci… Information and… 2000 5653
2 East Asia and Paci… <NA> 2001 325
3 East Asia and Paci… <NA> 2002 1430.
4 East Asia and Paci… <NA> 2003 0
5 East Asia and Paci… <NA> 2007 546
6 East Asia and Paci… <NA> 2008 0
7 East Asia and Paci… <NA> 2009 0
8 East Asia and Paci… <NA> 2013 0
9 East Asia and Paci… <NA> 2016 0
10 East Asia and Paci… <NA> 2017 0
# ℹ 409 more rows
# ℹ abbreviated name: ¹`Payment commitments to the government`
# ℹ 2 more variables: `Investment commitments in physical assets` <dbl>,
# `Total investment commitments` <dbl>
WB_info_filled_2 <-fill(WB_info_filled_1, 2)View(WB_info_filled_2)WB_info_best <- WB_info_filled_2 %>%select(-c(`Payment commitments to the government`, `Investment commitments in physical assets`)) %>%arrange(region) %>%group_by(region, `investment year`, `primary sector`) %>%summarise(Region_total_2 =sum(as.numeric(`Total investment commitments`), na.rm =TRUE)) %>%distinct()
`summarise()` has grouped output by 'region', 'investment year'. You can
override using the `.groups` argument.
View(WB_info_best)WB_info_final_1 <- WB_info_best %>%filter(!(region %in%c("<strong>Grand Total </strong>", "<strong>Subtotal - East Asia and Pacific</strong>", "<strong>Subtotal - Europe and Central Asia</strong>", "<strong>Subtotal - Latin America and the Caribbean</strong>", "<strong>Subtotal - Middle East and North Africa</strong>","<strong>Subtotal - South Asia</strong>", "<strong>Subtotal - Sub-Saharan Africa</strong>" )))WB_info_final_1
# A tibble: 412 × 4
# Groups: region, investment year [156]
region `investment year` `primary sector` Region_total_2
<chr> <chr> <chr> <dbl>
1 East Asia and Pacific 1999 Transport 2183.
2 East Asia and Pacific 1999 Water and sewerage 270.
3 East Asia and Pacific 2000 Information and commu… 5653
4 East Asia and Pacific 2000 Transport 2645.
5 East Asia and Pacific 2000 Water and sewerage 4066
6 East Asia and Pacific 2001 Information and commu… 325
7 East Asia and Pacific 2001 Transport 2755.
8 East Asia and Pacific 2001 Water and sewerage 673.
9 East Asia and Pacific 2002 Information and commu… 1439.
10 East Asia and Pacific 2002 Transport 1844.
# ℹ 402 more rows
WB_info_final_2 <- WB_info_best %>%filter(region %in%c("<strong>Grand Total </strong>", "<strong>Subtotal - East Asia and Pacific</strong>", "<strong>Subtotal - Europe and Central Asia</strong>", "<strong>Subtotal - Latin America and the Caribbean</strong>", "<strong>Subtotal - Middle East and North Africa</strong>","<strong>Subtotal - South Asia</strong>", "<strong>Subtotal - Sub-Saharan Africa</strong>" ))WB_info_final_2
# A tibble: 7 × 4
# Groups: region, investment year [7]
region `investment year` `primary sector` Region_total_2
<chr> <chr> <chr> <dbl>
1 <strong>Grand Total </stro… <NA> <strong>Subtota… 882582.
2 <strong>Subtotal - East Asi… <NA> <strong>Subtota… 271470.
3 <strong>Subtotal - Europe a… <NA> <strong>Subtota… 121911.
4 <strong>Subtotal - Latin Am… <NA> <strong>Subtota… 278471.
5 <strong>Subtotal - Middle E… <NA> <strong>Subtota… 27980.
6 <strong>Subtotal - South As… <NA> <strong>Subtota… 144658.
7 <strong>Subtotal - Sub-Saha… <NA> <strong>Subtota… 38091.
# A tibble: 16 × 4
Category Product Year `Investment in USD billion`
<chr> <chr> <chr> <dbl>
1 Source Private 2013 174.
2 Source Private 2014 218.
3 Source Private 2015 276.
4 Source Private 2016 231.
5 Source Private 2017 232.
6 Source Private 2018 230.
7 Source Private 2019 225.
8 Source Private 2020 240.
9 Source Public 2013 64.3
10 Source Public 2014 70.5
11 Source Public 2015 63.5
12 Source Public 2016 32.5
13 Source Public 2017 120.
14 Source Public 2018 91.9
15 Source Public 2019 104.
16 Source Public 2020 108.
percentages <- IRENA_focus_1 %>%group_by(Year, Product) %>%summarise(percentage =sum(`Investment in USD billion`) /sum(IRENA_focus_1$`Investment in USD billion`) *100) %>%ungroup()
`summarise()` has grouped output by 'Year'. You can override using the
`.groups` argument.
ggplot(data = IRENA_focus_1, aes(x =`Year`, y =`Investment in USD billion`, fill =`Product`)) +geom_col() +geom_text(data = percentages, aes(label =paste0(round(percentage), "%"), y =12* percentage), color ="black", size =3, vjust =-1) +labs(title ="Renewable Energy Investment in USD billion by Year and Product",x ="Year",y ="Investment in USD billion",fill ="Type of Investor")
#Part 8: Comparing Private and Public Infrastructure Developmentt1 <-ggplot(data = GIH_info_best,aes(x = region, y = Region_total)) +geom_col() +labs(title ="Total Government Investment in Infrastructure, Total, Between 2007 and 2020", x ="Region", y ="Total Investment in USD")t1
t2 <-ggplot(data = WB_info_final_2,aes(x = region, y = Region_total_2)) +geom_col() +labs(title ="Total Private Investment in Infrastructure, Total, Between 1999 and 2023", x ="Region", y ="Total Investment in USD")t2
# Part 5: Frankenstein-ing the Infrastructure Tibbles Together (NOT COMPLETE, HAVING TROUBLE)#combo_infra <- merge(WB_info_final_2, GIH_info_best, by = "region")#combo_infra
