chart for Matt and Derek

cpi charts.R

library(tidyverse)
library(ggrepel)
library(lubridate)

dat = read_csv('~/Downloads/Untitled spreadsheet - Sheet1.csv')

cpi = read_csv('~/Downloads/PRICES_CPI_17102023172052616.csv')

unique(cpi$Measure)
unique(cpi$Subject)

cpi = cpi %>%
  filter(Country %in% dat$country,
         Subject == 'CPI: All items non-food non-energy', #'CPI: 01-12 - All items',
         Measure == 'Index') %>%
  select(Country,Time,Value) 
  
cpi = cpi %>%
  mutate(quarter = gsub("Q1","Jan",Time),
         quarter = gsub("Q2","Apr",quarter),
         quarter = gsub("Q3","Jul",quarter),
         quarter = gsub("Q4","Oct",quarter),
         date = mdy(sprintf("%s-01-%s", 
                            substr(quarter,1,3),
                            substr(quarter,5,8))
           ),
         year = year(date)
         ) 

cpi$date  

index = cpi %>%
  group_by(Country) %>%
  filter(month(date) == month(max(date, na.rm=T)),
         (year == max(year,na.rm=T)) | (year == (max(year,na.rm=T) - 2))
  ) %>%
  ungroup()

index = index %>%
  group_by(Country) %>%
  mutate(max_year = max(year,na.rm=T)) %>%
  ungroup() %>%
  filter(max_year == 2023)

index = index %>%
  group_by(Country) %>%
  mutate(chg = Value - lag(Value)) %>% 
  ungroup() %>%
  select(country = Country,
         index_chg = chg) %>%
  filter(!is.na(index_chg)) 

dat = dat %>%
  left_join(index)

dat = dat %>% mutate_at(2:6, as.numeric)

ggplot(dat, aes(x = index_chg, y = Net)) +
  geom_text_repel(aes(label = country)) +
  geom_smooth(data = dat %>% filter(country != 'Mexico'),
              method = 'lm', se = F,
              aes(col = 'Trend excluding Mexico')) +
  labs(x = 'Change in CPI level excl. food and housing, Fall 2021-2023',
       y = 'Net leader approval',
       col = '') +
  theme_minimal() +
  theme(panel.grid.minor = element_blank(),
        legend.position = 'top')
  
  
  

Untitled spreadsheet - Sheet1.csv

CPI data can be downloaded from https://stats.oecd.org/Index.aspx?DataSetCode=CPL