Skip to content

Instantly share code, notes, and snippets.

@elliottmorris

elliottmorris/Untitled spreadsheet - Sheet1.csv

Created October 17, 2023 15:51
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save elliottmorris/09555942340fccbd485cc19cee018a49 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save elliottmorris/09555942340fccbd485cc19cee018a49 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. G. Elliott Morris created this gist Oct 17, 2023.
    18 changes: 18 additions & 0 deletions Untitled spreadsheet - Sheet1.csv
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,18 @@
    country,inflation (YoY Sept 2023),Leader approval,Disapproval,Net
    Austria,7.37,23,73,-50
    Belgium,2.39,38,46,-8
    Brazil,5.19,51,45,6
    Canada,4,33,59,-26
    France,4.86,23,72,-49
    Germany,4.53,25,68,-43
    Ireland,6.41,39,52,-13
    Italy,5.44,44,51,-7
    Japan,3.2,23,63,-40
    Korea,3.73,21,72,-51
    Mexico,4.45,68,28,40
    Netherlands,0.21,25,69,-44
    Poland,10.1,32,60,-28
    Spain,3.52,41,54,-13
    Sweden,6.48,33,57,-24
    United Kingdom,6.3,29,61,-32
    United States,3.7,39,53,-14
    71 changes: 71 additions & 0 deletions cpi charts.R
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,71 @@
    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')