Week 3 HW

Week 3 - Data Transformation

Prep work

# install.packages("esquisse")
# install.packages("hrbrthemes")
# install.packages("viridis")
# install.packages("viridisLite")
# update.packages("viridisLite")
library(tidyverse)
# library(dplyr)
library(ggplot2)
library(esquisse)
library(hrbrthemes)
library(viridis)
library(forcats)
options(scipen = 999)

blackrock_etf_screener <- read_csv("https://raw.githubusercontent.com/t-emery/sais-susfin_data/main/datasets/ishares_etf_screener_as_of_2023-12-27.csv") 

# getting a sense of the variables
View(blackrock_etf_screener)
glimpse(blackrock_etf_screener)

Rows: 424
Columns: 18
$ ticker                                                   <chr> "IVV", "IEFA"…
$ name                                                     <chr> "iShares Core…
$ incept_date                                              <dttm> 2000-05-15, …
$ gross_expense_ratio_percent                              <dbl> 0.03, 0.07, 0…
$ net_expense_ratio_percent                                <dbl> 0.03, 0.07, 0…
$ net_assets_usd                                           <dbl> 399312542067,…
$ net_assets_as_of                                         <dttm> 2023-12-27, …
$ asset_class                                              <chr> "Equity", "Eq…
$ sub_asset_class                                          <chr> "Large Cap", …
$ region                                                   <chr> "North Americ…
$ market                                                   <chr> "Developed", …
$ location                                                 <chr> "United State…
$ investment_style                                         <chr> "Index", "Ind…
$ msci_esg_fund_rating_aaa_ccc                             <chr> "A", "AA", "A…
$ msci_esg_quality_score_0_10                              <dbl> 6.6845, 7.519…
$ msci_weighted_average_carbon_intensity_tons_co2e_m_sales <dbl> 106.80, 102.8…
$ msci_esg_percent_coverage                                <dbl> 99.05, 99.80,…
$ sustainable_classification                               <chr> NA, NA, NA, N…

colnames(blackrock_etf_screener)

 [1] "ticker"                                                  
 [2] "name"                                                    
 [3] "incept_date"                                             
 [4] "gross_expense_ratio_percent"                             
 [5] "net_expense_ratio_percent"                               
 [6] "net_assets_usd"                                          
 [7] "net_assets_as_of"                                        
 [8] "asset_class"                                             
 [9] "sub_asset_class"                                         
[10] "region"                                                  
[11] "market"                                                  
[12] "location"                                                
[13] "investment_style"                                        
[14] "msci_esg_fund_rating_aaa_ccc"                            
[15] "msci_esg_quality_score_0_10"                             
[16] "msci_weighted_average_carbon_intensity_tons_co2e_m_sales"
[17] "msci_esg_percent_coverage"                               
[18] "sustainable_classification"                              

# create ESG or not variable, change year, change to bn
blackrock_etf_screener_w_new_features <- blackrock_etf_screener |> 
  mutate(
    standard_or_esg = if_else(
      condition = is.na(sustainable_classification),
      "Standard",
      "ESG"
    ),
    inception_year = lubridate::year(incept_date),
    net_assets_bn_usd=net_assets_usd/10^9,
    .before = 1
  )

colnames(blackrock_etf_screener_w_new_features)

 [1] "standard_or_esg"                                         
 [2] "inception_year"                                          
 [3] "net_assets_bn_usd"                                       
 [4] "ticker"                                                  
 [5] "name"                                                    
 [6] "incept_date"                                             
 [7] "gross_expense_ratio_percent"                             
 [8] "net_expense_ratio_percent"                               
 [9] "net_assets_usd"                                          
[10] "net_assets_as_of"                                        
[11] "asset_class"                                             
[12] "sub_asset_class"                                         
[13] "region"                                                  
[14] "market"                                                  
[15] "location"                                                
[16] "investment_style"                                        
[17] "msci_esg_fund_rating_aaa_ccc"                            
[18] "msci_esg_quality_score_0_10"                             
[19] "msci_weighted_average_carbon_intensity_tons_co2e_m_sales"
[20] "msci_esg_percent_coverage"                               
[21] "sustainable_classification"                              

# 424

Problem 1

blackrock_etf_screener_w_new_features_pb1 <- blackrock_etf_screener_w_new_features |> 
  filter(standard_or_esg =="ESG") |>
  group_by(sustainable_classification) |> 
  mutate(
    pct_in_category = net_assets_bn_usd/sum(net_assets_bn_usd), .after = standard_or_esg
  ) |> 
  arrange(desc(pct_in_category)) |> 
  slice_max(pct_in_category, n=1) |> 
  ungroup()

View(blackrock_etf_screener_w_new_features_pb1)

Problem 2

blackrock_etf_screener_w_new_features_pb2 <- blackrock_etf_screener_w_new_features |>
  slice_min(net_assets_bn_usd, n = 10) |>
  mutate(name = fct_reorder(as.factor(name), net_assets_bn_usd)) |>
  ggplot(aes(x = name, y = net_assets_bn_usd)) +
  geom_col(fill = "green4", alpha=.7) +
  scale_y_continuous(labels = scales::label_dollar(scale = 10^3, suffix = " mn")) +
  labs(title = "Smallest 10 ETFs",
       # subtitle = "ESG comprise a small percentage of total assets.",
       x = "Fund Name",
       y = "Net Assets",
       caption = "Dandan\nSusFin 2024") 


blackrock_etf_screener_w_new_features_pb2

Problem 3

blackrock_etf_screener_w_new_features_pb3 <- blackrock_etf_screener_w_new_features |>
  mutate(
    fund_percentile=percent_rank(msci_esg_quality_score_0_10 * -1), 
    .before = 1) |> 
  filter(fund_percentile <=0.2) |> 
  group_by(asset_class, sub_asset_class) |> 
  summarize(
    n(),
    sum(net_assets_bn_usd)
  )

`summarise()` has grouped output by 'asset_class'. You can override using the
`.groups` argument.

blackrock_etf_screener_w_new_features_pb3

# A tibble: 6 × 4
# Groups:   asset_class [3]
  asset_class  sub_asset_class `n()` `sum(net_assets_bn_usd)`
  <chr>        <chr>           <int>                    <dbl>
1 Equity       All Cap            26                 164.    
2 Equity       Large Cap           2                   1.38  
3 Equity       Large/Mid Cap      42                 190.    
4 Equity       Small Cap           1                   0.0456
5 Fixed Income Credit              3                   9.52  
6 Multi Asset  Multi Strategy      2                   0.0447

Problem 4

# drop entries w/o carbon intensity
blackrock_etf_screener_w_new_features_with_carbon <- blackrock_etf_screener_w_new_features |> filter(!is.na(msci_weighted_average_carbon_intensity_tons_co2e_m_sales))
blackrock_etf_screener_w_new_features_with_carbon

# A tibble: 354 × 21
   standard_or_esg inception_year net_assets_bn_usd ticker name                 
   <chr>                    <dbl>             <dbl> <chr>  <chr>                
 1 Standard                  2000             399.  IVV    iShares Core S&P 500…
 2 Standard                  2012             107.  IEFA   iShares Core MSCI EA…
 3 Standard                  2003             101.  AGG    iShares Core U.S. Ag…
 4 Standard                  2000              82.1 IWF    iShares Russell 1000…
 5 Standard                  2000              78.2 IJR    iShares Core S&P Sma…
 6 Standard                  2000              77.1 IJH    iShares Core S&P Mid…
 7 Standard                  2012              73.9 IEMG   iShares Core MSCI Em…
 8 Standard                  2000              67.7 IWM    iShares Russell 2000…
 9 Standard                  2000              55.4 IWD    iShares Russell 1000…
10 Standard                  2001              51.3 EFA    iShares MSCI EAFE ETF
# ℹ 344 more rows
# ℹ 16 more variables: incept_date <dttm>, gross_expense_ratio_percent <dbl>,
#   net_expense_ratio_percent <dbl>, net_assets_usd <dbl>,
#   net_assets_as_of <dttm>, asset_class <chr>, sub_asset_class <chr>,
#   region <chr>, market <chr>, location <chr>, investment_style <chr>,
#   msci_esg_fund_rating_aaa_ccc <chr>, msci_esg_quality_score_0_10 <dbl>,
#   msci_weighted_average_carbon_intensity_tons_co2e_m_sales <dbl>, …

View(blackrock_etf_screener_w_new_features_with_carbon)
#354! dropped 70 funds 

#find mean carbon intensity by groups
carbon_intensity_comp <- blackrock_etf_screener_w_new_features_with_carbon |> 
  group_by(standard_or_esg) |> 
  summarize(
    avg_carbon_intensity = mean(msci_weighted_average_carbon_intensity_tons_co2e_m_sales, na.rm = TRUE), 
    median_carbon_intensity = median(msci_weighted_average_carbon_intensity_tons_co2e_m_sales),
    avg_fund_size = mean(net_assets_bn_usd, na.rm = TRUE),
    hypothetical_carbon_foot_print= avg_carbon_intensity * avg_fund_size,
    n_funds =n()
  )

carbon_intensity_comp

# A tibble: 2 × 6
  standard_or_esg avg_carbon_intensity median_carbon_intensity avg_fund_size
  <chr>                          <dbl>                   <dbl>         <dbl>
1 ESG                             117.                    87.8          1.39
2 Standard                        207.                   150.           6.98
# ℹ 2 more variables: hypothetical_carbon_foot_print <dbl>, n_funds <int>

# Graph
ggplot(carbon_intensity_comp) +
  aes(
    x = avg_fund_size,
    y = avg_carbon_intensity,
    colour = standard_or_esg ,
    size = n_funds
  ) +
  geom_point(
    # shape = "circle",
    alpha=0.75
  ) +
  scale_size(range = c(20,100), name="# of funds") +
  scale_color_manual(
    values = c(ESG = "green4" , Standard = "gray30")
  ) +
  labs(
    x = "Average Fund Size (USD Billion)",
    y = "Average Carbon Intensity (mt CO2 e/ mn Sales)",
    title = "Carbon foot print of ETF funds",
    subtitle = "Standard iShares ETFs are 2X dirter, 5X bigger, and 10X as many ~ that's up to 100X the carbon foot print as ESG funds! \n(Green indicates ESG fund, gray indicates stardard fund. Size of circle indicates number of funds in category)",
    caption = "Dandan Gong\nSusFin 2024"
  ) +
  theme_minimal() +
  theme(
    legend.position = "none",
    plot.title = element_text(size = 16L, face = "bold"),
    plot.subtitle = element_text(size = 12L),
    plot.caption = element_text(size = 10L),
    axis.title.y = element_text(size = 12L),
    axis.title.x = element_text(size = 12L)
  ) +
  xlim(0, 10) +
  ylim(0, 250)

# Factoid: 10x times more funds, 5x bigger, 2x, 100 times impact