explore data

Set up

library(tidyverse)

## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.5
## ✔ forcats   1.0.0     ✔ stringr   1.5.1
## ✔ ggplot2   3.5.2     ✔ tibble    3.2.1
## ✔ lubridate 1.9.4     ✔ tidyr     1.3.1
## ✔ purrr     1.0.4     
## ── 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)
library(lubridate) # to convert the Date column to date data type

moody_base     <- read_excel("../00_data/MOODYBASE.xlsx")
moody_moderate <- read_excel("../00_data/MOODYMODERATE.xlsx")
moody_severe   <- read_excel("../00_data/MOODYSEVERE.xlsx")

Notes:

• All three datasets have the same variables.
• All observations appear to be the same up to 2021-Q1.
• Forecast variables appear to be the last variables on the far right - CPIO through RealGDPgrowthO.
• Forecast values after 2021-Q2 and beyond on these variables.
• The last three rows are NAs. Remove them.

Clean

I assume Kelly wants to plot three scenarios of the forecast varlues. To demonstrate how this can be done, take CPI0 as an example.

moody_base_clean     <- moody_base %>% select(Date, CPIO) %>% drop_na() %>% rename(base = CPIO)
moody_moderate_clean <- moody_moderate %>% select(Date, CPIO) %>% drop_na() %>% rename(moderate = CPIO)
moody_severe_clean   <- moody_severe %>% select(Date, CPIO) %>% drop_na() %>% rename(severe = CPIO)

Join datasets

moody_all <- moody_base_clean %>%
    left_join(moody_moderate_clean) %>%
    left_join(moody_severe_clean)

## Joining with `by = join_by(Date)`
## Joining with `by = join_by(Date)`

moody_all

## # A tibble: 84 × 4
##    Date    base moderate severe
##    <chr>  <dbl>    <dbl>  <dbl>
##  1 2007Q2  4.61     4.61   4.61
##  2 2007Q3  2.56     2.56   2.56
##  3 2007Q4  5.00     5.00   5.00
##  4 2008Q1  4.40     4.40   4.40
##  5 2008Q2  5.31     5.31   5.31
##  6 2008Q3  6.31     6.31   6.31
##  7 2008Q4 -8.85    -8.85  -8.85
##  8 2009Q1 -2.72    -2.72  -2.72
##  9 2009Q2  2.14     2.14   2.14
## 10 2009Q3  3.49     3.49   3.49
## # ℹ 74 more rows

Transform data to long-form for visualization

moody_long <- moody_all %>%
    pivot_longer(-Date, names_to = "type", values_to = "forecast") %>%
    
    # Convert Date to date type
    mutate(Date = lubridate::yq(Date))

moody_long

## # A tibble: 252 × 3
##    Date       type     forecast
##    <date>     <chr>       <dbl>
##  1 2007-04-01 base         4.61
##  2 2007-04-01 moderate     4.61
##  3 2007-04-01 severe       4.61
##  4 2007-07-01 base         2.56
##  5 2007-07-01 moderate     2.56
##  6 2007-07-01 severe       2.56
##  7 2007-10-01 base         5.00
##  8 2007-10-01 moderate     5.00
##  9 2007-10-01 severe       5.00
## 10 2008-01-01 base         4.40
## # ℹ 242 more rows

Plot

moody_long %>%
    filter(Date > "2021-01-01") %>%
    ggplot(aes(Date, forecast, color = type)) +
    geom_line()

Import CPI data

cpi_tbl <- readr::read_table("../00_data/CPI GRAPH.txt")

## 
## ── Column specification ────────────────────────────────────────────────────────
## cols(
##   N = col_double(),
##   Date = col_character(),
##   CPI = col_double(),
##   CPI_BASE = col_double(),
##   CPI_MODERATE = col_double(),
##   CPI_SEVERE = col_double()
## )

## Warning: 1 parsing failure.
## row col  expected    actual                       file
##  84  -- 6 columns 1 columns '../00_data/CPI GRAPH.txt'

Notes:

• It’s a Word file. Save it in .txt file type to import it into R.
• The first column doesn’t have a name. Name it “N” to import the data correctly.
• Import the data using read_table and remove the first column, N.

Clean

cpi_clean <- cpi_tbl %>%
    select(-N) %>%
    mutate(Date = yq(Date))

cpi_clean

## # A tibble: 84 × 5
##    Date         CPI CPI_BASE CPI_MODERATE CPI_SEVERE
##    <date>     <dbl>    <dbl>        <dbl>      <dbl>
##  1 2007-04-01  4.61    3.48         3.48       3.48 
##  2 2007-07-01  2.56    2.07         2.07       2.07 
##  3 2007-10-01  5.00    5.17         5.17       5.17 
##  4 2008-01-01  4.40    4.83         4.83       4.83 
##  5 2008-04-01  5.31    4.84         4.84       4.84 
##  6 2008-07-01  6.31    4.67         4.67       4.67 
##  7 2008-10-01 -8.85   -8.06        -8.06      -8.06 
##  8 2009-01-01 -2.72   -1.94        -1.94      -1.94 
##  9 2009-04-01  2.14    0.755        0.755      0.755
## 10 2009-07-01  3.49    2.71         2.71       2.71 
## # ℹ 74 more rows

Transform data to long-form

cpi_long <- cpi_clean %>% 
    pivot_longer(-Date, names_to = "type", values_to = "forecast")

cpi_long

## # A tibble: 336 × 3
##    Date       type         forecast
##    <date>     <chr>           <dbl>
##  1 2007-04-01 CPI              4.61
##  2 2007-04-01 CPI_BASE         3.48
##  3 2007-04-01 CPI_MODERATE     3.48
##  4 2007-04-01 CPI_SEVERE       3.48
##  5 2007-07-01 CPI              2.56
##  6 2007-07-01 CPI_BASE         2.07
##  7 2007-07-01 CPI_MODERATE     2.07
##  8 2007-07-01 CPI_SEVERE       2.07
##  9 2007-10-01 CPI              5.00
## 10 2007-10-01 CPI_BASE         5.17
## # ℹ 326 more rows

Plot

cpi_long %>%
    ggplot(aes(Date, forecast, color = type)) +
    geom_line(alpha = 0.3)

## Warning: Removed 31 rows containing missing values or values outside the scale range
## (`geom_line()`).

cpi_long %>%
    filter(Date > "2021-01-01") %>%
    ggplot(aes(Date, forecast, color = type)) +
    geom_line(alpha = 0.3)

## Warning: Removed 27 rows containing missing values or values outside the scale range
## (`geom_line()`).