tl3-esp.qmd

```{r esp_ctry}
ctry <- "ESP"
ctry2 <- "Spain"
```

# Spain

```{r esp_pkgs, warning=FALSE}
library(oecdcountryprofiles)
library(dplyr)
library(tidyr)
library(ggplot2)
library(patchwork)
library(oecdplot)
library(knitr)
library(readxl)
library(janitor)
library(stringr)
library(readr)
library(tintin)
library(scales)
library(plotly)
library(glue)

load_oecd_fonts()

missing_reg <- c("ITG2F")
excl_countries <- c("LUX", "IRL")

clrs <- tintin_colours$the_black_island
clrs2 <- tintin_colours$the_blue_lotus
clrs3 <- tintin_colours$the_red_sea_sharks
clrs4 <- tintin_colours$the_calculus_affair
clrs5 <- tintin_colours$red_rackhams_treasure
```

<table border="1" style="width:75%;margin-left:auto;margin-right:auto;">
    <tr>
      <th colspan="2" style="background:#fff9e6; padding:10px;font-size:9.0pt;" valign="center">
        OECD Regional Outlook
      </th>
    </tr>
    <tr>
      <td>
        <img style="padding:10px;" src="RO2023_cover.jpg" alt="OECD Regional Outlook 2023" height="200"/>
      </td>
      <td>
        <p style="padding:10px;font-size:9.0pt;">The OECD <a href="https://www.oecd-ilibrary.org/urban-rural-and-regional-development/oecd-regional-outlook_2dafc8cf-en" target="_blank"><i style="color:#0d6efd">Regional Outlook</i></a> reviews recent trends, policy developments, and prospects across OECD regions, including the underlying causes driving regional inequalities in performance and well-being. The report offers evidence, guidance and policy recommendations on how to improve competitiveness and productivity, promote inclusive growth, accelerate the net-zero transition and raise well-being standards through effective regional development policy and multi-level governance.</p>
      </td>
    </tr>
</table>

## Overview

```{r esp_tbl}
read_html_table(ctry)
```

## Regional inequality trends

```{r esp_fig1}
iso3_g20 <- read_csv("data/ISO_3_G20.csv") %>%
  clean_names() %>%
  rename(iso3 = iso_3)

top_bottom_c <- read_excel("data/top_bottom_gdppc_w_agg.xlsx") %>%
  filter(TIME < 2021) %>%
  clean_names()

gdp_pop_00_20 <- read_csv("data/gdp_pop_00_20_imputed_agg_l.csv") %>%
  filter(TIME < 2021) %>%
  clean_names() %>%
  select(-x1) %>%
  left_join(iso3_g20, by = "iso3") %>%
  filter(!reg_id %in% missing_reg, !iso3 %in% excl_countries) %>%
  mutate(
    metro_large = ifelse(metro_large != "Metropolitan large", "Non-large", metro_large),
    metro_far = ifelse(metro_far != "Far from a FUA>250K", "Close", metro_far)
  ) %>%
  bind_rows(
    read_csv("data/gdp_pop_tl2_00_20_imputed_l.csv") %>%
      filter(TIME < 2021) %>%
      clean_names() %>%
      select(time, reg_id, iso3, gdp, pop, gdp_pc, regional_name_eng = reg_name)
  )

summary_theil_tl3 <- read_csv("data/theil_gdppc_country_00_20_wCHE-CHL.csv") %>%
  filter(TIME < 2021) %>%
  clean_names() %>%
  filter(level == "gdppc_tl3") %>%
  select(-c(x1, level))

summary_stats <- gdp_pop_00_20 %>%
  group_by(iso3, time) %>%
  summarise(mean_gdp_pc = mean(gdp_pc, na.rm = TRUE)) %>%
  ungroup() %>%
  left_join(top_bottom_c, by = c("iso3", "time")) %>%
  mutate(
    top_bottom = top_20_gdppc / bottom_20_gdppc,
    top_mean = top_20_gdppc / mean_gdp_pc,
    bottom_mean = bottom_20_gdppc / mean_gdp_pc
  )

summary_stats <- summary_stats %>%
  left_join(summary_theil_tl3, by = c("iso3", "time"))

min_y <- summary_theil_tl3 %>%
  filter(iso3 == ctry) %>%
  pull(time) %>%
  min()

if (ctry == "CHE") {
  min_y <- 2008
}

if (ctry == "HUN") {
  min_y <- 2001
}

if (ctry == "TUR") {
  min_y <- 2004
}

index_200x <- summary_stats %>%
  filter(time == min_y) %>%
  pivot_wider(
    id_cols = "iso3",
    names_from = "time",
    values_from = -c(time, iso3)
  )

colnames(index_200x) <- gsub("_200[0-9]", "_200x", colnames(index_200x))

summary_stats_index <- summary_stats %>%
  left_join(index_200x, by = "iso3") %>%
  mutate(
    index_gdppc = mean_gdp_pc / mean_gdp_pc_200x,
    index_bottom = bottom_mean / bottom_mean_200x,
    index_top = top_mean / top_mean_200x,
    index_theil = theil / theil_200x,
    index_top_mean = top_mean / top_mean_200x,
    index_bottom_mean = bottom_mean / bottom_mean_200x,
    index_top_bottom = top_bottom / top_bottom_200x
  ) %>%
  select(
    iso3,
    time,
    index_gdppc,
    index_bottom,
    index_top_mean,
    index_top_bottom,
    index_bottom_mean,
    index_top,
    index_theil
  )

summary_wide <- summary_stats_index %>%
  pivot_longer(cols = -c(time, iso3), names_to = "index") %>%
  mutate(
    labels_index = case_when(
      index == "index_bottom" ~ "Bottom 20%",
      index == "index_top" ~ "Top 20%",
      index == "index_bottom_mean" ~ "Bottom 20%/Mean",
      index == "index_top_mean" ~ "Top 20%/Mean",
      index == "index_theil" ~ "Theil index",
      index == "index_gdppc" ~ "Mean GDP per capita",
      index == "index_top_bottom" ~ "Top 20%/Bottom 20%"
    )
  ) %>%
  left_join(iso3_g20, by = "iso3")

lev_3 <- c(
  "Theil index",
  "Top 20%/Mean",
  "Bottom 20%/Mean",
  "Mean GDP per capita"
)

df_fig1 <- summary_wide %>%
  filter(iso3 == ctry, labels_index %in% lev_3, time >= min_y) %>%
  pivot_wider(
    names_from = index,
    values_from = value
  ) %>%
  mutate(
    index_label = factor(labels_index, levels = lev_3)
  ) 

fig1 <- df_fig1 %>%
  ggplot(aes(x = time)) +
  geom_line(aes(
    y = index_gdppc,
    colour = index_label
  ), linetype = "dashed", linewidth = 1.2) +
  geom_line(aes(
    y = index_top_mean,
    colour = index_label
  ), linetype = "dashed", linewidth = 1.2) +
  geom_line(aes(
    y = index_bottom_mean,
    colour = index_label
  ), linetype = "dashed", linewidth = 1.2) +
  geom_line(aes(
    y = index_theil,
    colour = index_label
  ), linewidth = 1.2) +
  geom_hline(yintercept = 1, color = "lightgrey", linetype = "dashed") +
  scale_colour_manual(values = clrs2[c(1, 3, 4, 2)]) + # evil hack to match TL2 colours
  scale_x_continuous(expand = c(0, 0), breaks = seq(from = min(df_fig1$time), to = max(df_fig1$time), by = 5)) +
  labs(
    title = "Figure 1: Trends in GDP per capita inequality indicators,\nTL3 OECD regions",
    x = "",
    y = "Statistic (2000=1)",
    linetype = "",
    colour = ""
  ) +
  # theme_oecd(base_size = 10) +
  # theme(plot.title = element_text(size = 13, hjust = 0, margin = margin(0, 0, 10, 0))) +
  theme_minimal()
```

```{r esp_fig1_summary}
theil_chg <- summary_wide %>%
  filter(
    index == "index_theil",
    iso3 == ctry
  ) %>%
  filter(!is.na(value)) %>%
  filter(time %in% range(time)) %>%
  arrange(time) %>%
  mutate(diff = value - lag(value)) %>%
  filter(time == max(time)) %>%
  pull(diff)

theil_chg_txt <- ifelse(theil_chg > 0, "an increase", "a decline")

theil_max <- summary_wide %>%
  filter(
    index == "index_theil",
    iso3 == ctry
  ) %>%
  filter(!is.na(value)) %>%
  filter(value == max(value)) %>%
  pull(time) %>%
  as.integer()

polarization_pct <- summary_wide %>%
  filter(
    index == "index_top_mean",
    iso3 == ctry
  ) %>%
  filter(!is.na(value)) %>%
  filter(time %in% range(time)) %>%
  arrange(time) %>%
  mutate(diff = lead(value) - value) %>%
  filter(time == min(time)) %>%
  pull(diff) %>%
  round(3)

polarization_txt <- ifelse(polarization_pct > 0, "higher", "lower")

polarization_txt2 <- ifelse(polarization_txt == "higher", "increased", "decreased")

polarization_yrs <- summary_wide %>%
  filter(
    index == "index_top_mean",
    iso3 == ctry
  ) %>%
  filter(time %in% range(time)) %>%
  pull(time) %>%
  as.integer()

polarization_2_pct <- summary_wide %>%
  filter(
    index == "index_bottom_mean",
    iso3 == ctry
  ) %>%
  filter(time %in% range(time)) %>%
  arrange(time) %>%
  mutate(diff = lead(value) - value) %>%
  filter(time == min(time)) %>%
  pull(diff) %>%
  round(3)

polarization_2_txt <- ifelse(polarization_2_pct > 0, "higher", "lower")
polarization_2_txt2 <- ifelse(polarization_2_txt == "higher", "convergence", "divergence")

# needs the: USA, UK, Czech Rep, Slovak Rep, Netherlands

ctry3 <- if (any(ctry %in% c("USA", "GBR", "CZE", "SVK", "NLD"))) {
  paste("The", ctry2)
} else {
  ctry2
}

ctry4 <- if (any(ctry %in% c("USA", "GBR", "CZE", "SVK", "NLD"))) {
  paste("the", ctry2)
} else {
  ctry2
}

paragraph_theil <- if (any(ctry %in% c("GBR"))){
  glue("{ ctry3 } experienced { theil_chg_txt } in the Theil index of GDP per capita over 2000-2020. Inequality reached its maximum in { theil_max }. The figures are normalized, with values in the year {as.integer(min_y)} set to 1.

The Top 20%/Mean ratio was { paste(abs(polarization_pct), polarization_txt) } in { max(polarization_yrs) } compared to { min(polarization_yrs) }, indicating { polarization_txt2 } polarisation. The Bottom 20%/Mean ratio did not change in the same period.")
} else{
glue("{ ctry3 } experienced { theil_chg_txt } in the Theil index of GDP per capita over 2000-2020. Inequality reached its maximum in { theil_max }. The figures are normalized, with values in the year {as.integer(min_y)} set to 1.

The Top 20%/Mean ratio was { paste(abs(polarization_pct), polarization_txt) } in { max(polarization_yrs) } compared to { min(polarization_yrs) }, indicating { polarization_txt2 } polarisation. The Bottom 20%/Mean ratio was { paste(abs(polarization_2_pct), polarization_2_txt) } in the same period, indicating bottom { polarization_2_txt2 }.")}
```

`r paragraph_theil`

```{r esp_fig1_1}
# no interactivity
# fig1

# interactivity
ggplotly(fig1) %>%
  config(displayModeBar = F)
```

<div class="oecdnote">
**Note**: Top/bottom calculated as population equivalent (top/bottom regions with at least 20% of the population). The interpretation of top/bottom 20% GDP per capita is that 20% of the population in the country holds 20% of the value. Top 20%/Mean calculated as mean GDP per capita in top 20% regions over mean TL3 GDP per capita in a given year. Bottom 20%/Mean calculated as mean TL3 GDP per capita in bottom 20% regions over mean TL3 GDP per capita in a given year. To improve data consistency, input series are aggregated when TL3 regions are part of the same FUA. To improve time series, TL3 missing values have been estimated based on the evolution at higher geographic level.<br>
**Source**: OECD Regional Database (2022).
</div>

<br>
<br>

```{r esp_fig2}
# Load file with top/bottom 20% data (provided by Eric using weights)
top_bottom_c_oecd <- read_excel("data/top_bottom_gdppc_w_agg.xlsx") %>%
  clean_names()

# Load GDP_PC data for 2000-2020 aggregated by metros sharing the same FUA (missings imputed by Eric)
gdp_pop_00_20 <- read_csv("data/gdp_pop_00_20_imputed_agg_l.csv") %>%
  clean_names() %>%
  select(-x1) %>%
  filter(!reg_id %in% missing_reg, !iso3 %in% excl_countries) %>%
  mutate(
    metro_large = ifelse(metro_large != "Metropolitan large", "Non-large", metro_large),
    metro_far = ifelse(metro_far != "Far from a FUA>250K", "Close", metro_far)
  ) %>%
  drop_na(iso3)

# Process data ------------------------------------------------------------

gdppc_metro <- gdp_pop_00_20 %>%
  group_by(metro_lev1, time, iso3) %>%
  summarise(mean_gdppc = mean(gdp_pc), .groups = "drop") %>%
  ungroup() %>%
  filter(time %in% c(2000, 2020)) %>%
  pivot_wider(id_cols = c(time, iso3), names_from = metro_lev1, values_from = mean_gdppc) %>%
  mutate(
    gap = Metropolitan / `Non-metropolitan`,
    type = "Metropolitan/Non-metropolitan"
  ) %>%
  select(time, iso3, gap, type)

gdppc_large <- gdp_pop_00_20 %>%
  group_by(metro_large, time, iso3) %>%
  summarise(mean_gdppc = mean(gdp_pc, na.rm = T), .groups = "drop") %>%
  ungroup() %>%
  filter(time %in% c(2000, 2020)) %>%
  pivot_wider(id_cols = c(time, iso3), names_from = metro_large, values_from = mean_gdppc) %>%
  clean_names() %>%
  mutate(
    gap = metropolitan_large / non_large,
    type = "Large metropolitan/Non-large"
  ) %>%
  select(time, iso3, gap, type) %>%
  drop_na(gap)

gdppc_far <- gdp_pop_00_20 %>%
  group_by(metro_far, time, iso3) %>%
  summarise(mean_gdppc = mean(gdp_pc, na.rm = T), .groups = "drop") %>%
  ungroup() %>%
  filter(time %in% c(2000, 2020)) %>%
  pivot_wider(id_cols = c(time, iso3), names_from = metro_far, values_from = mean_gdppc) %>%
  clean_names() %>%
  mutate(
    gap = close / far_from_a_fua_250k,
    type = "Near/Far from a FUA >250K"
  ) %>%
  select(time, iso3, gap, type) %>%
  drop_na(gap)

gaps <- bind_rows(gdppc_metro, gdppc_far, gdppc_large)

mean_gaps <- gaps %>%
  filter(time == 2020) %>%
  group_by(time, type) %>%
  summarise(gap = mean(gap, na.rm = TRUE)) %>%
  ungroup() %>%
  mutate(time = "OECD mean gap, 2020") %>%
  select(time, type, gap)

country <- gaps %>%
  filter(iso3 == ctry) %>%
  select(-iso3)

country <- bind_rows(
  country %>%
    mutate(time = as.character(time)),
  mean_gaps
) %>%
  mutate(time = factor(time))

lev_type <- c("Large metropolitan/Non-large", "Metropolitan/Non-metropolitan", "Near/Far from a FUA >250K")

fig2 <- country %>%
  mutate(
    type = factor(type, levels = lev_type)
  ) %>%
  rename(
    series = type
  ) %>%
  ggplot() +
  geom_col(
    # aes(x = time, y = gap, fill = type),
    aes(x = series, y = gap, fill = time),
    linewidth = 1.5,
    position = position_dodge2(preserve = "single")
  ) +
  labs(
    x = "", y = "Gap",
    title = "Figure 2: GDP per capita gap by type of region\ncompared to the OECD average"
  ) +
  # theme_oecd(base_size = 10) +
  # theme(plot.title = element_text(size = 13, hjust = 0, margin = margin(0, 0, 10, 0))) +
  theme_minimal() +
  theme(
    # put x-ticks with 30 degree angle
    axis.text.x = element_text(angle = 30, hjust = 1)
  ) +
  scale_fill_manual(values = clrs, name = "")
```

```{r esp_fig2_2}
gdp_gap <- country %>%
  filter(
    time == 2020,
    type == "Large metropolitan/Non-large"
  ) %>%
  pull(gap) %>%
  round(3)

gdp_2_gap <- country %>%
  filter(
    time == "OECD mean gap, 2020",
    type == "Large metropolitan/Non-large"
  ) %>%
  pull(gap) %>%
  round(3)

gdp_gap_lag <- country %>%
  filter(
    time == 2000,
    type == "Large metropolitan/Non-large"
  ) %>%
  pull(gap) %>%
  round(3)

gdp_gap_txt <- ifelse(gdp_gap - gdp_gap_lag > 0, "increased", "decreased")

gdp_gap_pct <- abs(round(gdp_gap - gdp_gap_lag, 3))

gdp_3_gap <- country %>%
  filter(
    time == 2020,
    type == "Metropolitan/Non-metropolitan"
  ) %>%
  pull(gap) %>%
  round(3)

gdp_3_gap_lag <- country %>%
  filter(
    time == 2000,
    type == "Metropolitan/Non-metropolitan"
  ) %>%
  pull(gap) %>%
  round(3)

gdp_4_gap <- country %>%
  filter(
    time == "OECD mean gap, 2020",
    type == "Metropolitan/Non-metropolitan"
  ) %>%
  pull(gap) %>%
  round(3)

gdp_2_gap_txt <- ifelse(gdp_3_gap - gdp_3_gap_lag > 0, "increased", "decreased")

gdp_2_gap_pct <- abs(round(gdp_3_gap - gdp_3_gap_lag, 3))

gdp_5_gap <- country %>%
  filter(
    time == 2020,
    type == "Near/Far from a FUA >250K"
  ) %>%
  pull(gap) %>%
  round(3)

gdp_5_gap_lag <- country %>%
  filter(
    time == 2000,
    type == "Near/Far from a FUA >250K"
  ) %>%
  pull(gap) %>%
  round(3)

gdp_3_gap_txt <- ifelse(gdp_5_gap - gdp_5_gap_lag > 0, "increased", "decreased")

gdp_3_gap_pct <- abs(round(gdp_5_gap - gdp_5_gap_lag, 3))
```

```{r esp_next paragraph}
next_paragraph <- if (any(ctry %in% c("LTU", "EST", "FIN", "LVA", "NZL", "NOR", "SVK", "SVN", "CHE"))) {
  glue("There is no data for the gap in GDP per capita between large metropolitan and non-large metropolitan regions for 2000 and 2020.")
} else {
  glue("In 2020, the gap in GDP per capita between large metropolitan and non-large metropolitan regions was { gdp_gap }. For reference, the same value for OECD was { gdp_2_gap }. This gap { gdp_gap_txt }  by { gdp_gap_pct } percentage points between 2000 and 2020.")
}

last_paragraph <- if (any(ctry %in% c("KOR", "NLD"))) {
  glue("There is no data for the gap in GDP per capita between regions near and far a Functional Urban Area (FUA) of more than 250 thousand inhabitants for 2000 and 2020.")
} else {
  glue("In turn, the gap in GDP per capita between regions near and far a Functional Urban Area (FUA) of more than 250 thousand inhabitants was { gdp_5_gap } in 2020 and { gdp_3_gap_txt } by { gdp_3_gap_pct}  percentage points since 2000.")
}
```

`r next_paragraph`

Meanwhile, in 2020, the gap in GDP per capita between metropolitan and non-metropolitan regions was `r gdp_3_gap`. For reference, the same value for OECD was `r gdp_4_gap`. This gap `r paste(gdp_2_gap_txt, "by", gdp_2_gap_pct)` percentage points since 2000.

`r last_paragraph`

```{r esp_fig2_3}
# no interactivity
# fig2

# interactivity
ggplotly(fig2) %>%
  config(displayModeBar = F)
```

<div class="oecdnote">
**Note**: Far from a FUA>250K includes regions near/with a small FUA and remote regions. OECD mean gap based on 1 586 TL3 regions in 27 countries with available data (no TL3 data for Australia, Canada, Chile, Colombia, Costa Rica, Iceland, Ireland, Israel, Mexico, Luxembourg and Switzerland).<br>
**Source**: OECD Regional Database (2022).
</div>

<br>
<br>

```{r esp_fig3}
# read ----
dp1 <- read_excel("data/countryprofile_fig3_alt.xlsx", sheet = ctry) %>%
  select(time, pw_lp, pw_hp) %>% 
  clean_names()

# tidy ----

# colnames(dp1) <- str_replace(colnames(dp1), tolower(ctry), "country")

colnames(dp1) <- c("time", "pw_lp_country", "pw_hp_country")

dp11 <- dp1 %>%
  select(time, matches("country")) %>%
  pivot_longer(-time) %>%
  rename(value_country = value)

dp11 <- dp11 %>%
  mutate(
    name = case_when(
      name == "pw_lp_country" ~ "Lower half",
      name == "pw_hp_country" ~ "Upper half"
    )
  )

# plot ----

yrs <- sort(unique(dp11$time))
yrs <- seq(min(yrs), max(yrs), 2)

hpgrew <- dp11 %>%
  filter(
    name == "Upper half",
    time %in% c(min(yrs), max(yrs))
  ) %>%
  summarise(grew = 100 * (value_country - lag(value_country)) / lag(value_country)) %>%
  drop_na() %>%
  pull() %>%
  round(1)

lpgrew <- dp11 %>%
  filter(
    name == "Lower half",
    time %in% c(min(yrs), max(yrs))
  ) %>%
  summarise(grew = 100 * (value_country - lag(value_country)) / lag(value_country)) %>%
  drop_na() %>%
  pull() %>%
  round(1)

hpdiff <- hpgrew - lpgrew

hpmore <- ifelse(hpdiff > 0, "more", "less")

fig3_title <- if (any(ctry %in% c("USA", "TUR", "NOR", "CHE"))) {
  "Figure 3: Evolution of labour productivity,\nTL2 regions"
} else {
  "Figure 3: Evolution of labour productivity,\nTL3 regions"
}

fig3_1 <- dp11 %>%
  rename(
    value = value_country,
    series = name
  ) %>%
  ggplot() +
  geom_line(aes(x = time, y = value, color = series), linewidth = 1.2) +
  # theme_oecd(base_size = 10) +
  # theme(plot.title = element_text(size = 13, hjust = 0, margin = margin(0, 0, 10, 0))) +
  theme_minimal() +
  # scale_colour_manual(values = clrs3[1:2]) +
  scale_colour_manual(values = c("#508551", "#177dc7")) +
  labs(
    x = "", y = "Labour productivity (2015 USD PPP)", colour = "",
    title = fig3_title
  ) +
  scale_x_continuous(labels = as.character(yrs), breaks = yrs) +
  scale_y_continuous(labels = scales::number_format())
```

```{r esp_fig3_text}
fig3_text <- read_excel("data/fig3_text_FINAL.xlsx", sheet = "text") %>%
  clean_names()

fig3_text <- fig3_text %>%
  filter(iso3 == ctry) %>%
  pull(text_final)
```

`r fig3_text`

```{r esp_fig3_2}
# no interactivity
# fig3_1

# interactivity
ggplotly(fig3_1) %>%
  config(displayModeBar = F)
```

<div class="oecdnote">
**Note**: A region is in the “upper half” if labour productivity was above the country median in the first year with available data and “lower half” if productivity was below the country median.  Labour productivity in each group is equal to the sum of Gross Value Added, expressed in USD at constant prices and PPP (base year 2015) within the group, divided by the sum of total employment in regions within the group. Regions are small (TL3) regions, except for Australia, Canada, Chile, Colombia, Ireland, Mexico, Norway, Switzerland, Türkiye and the United States where they are large (TL2) regions due to data availability. <br>
**Source**: OECD Regional Database (2022).
</div>

<br>
<br>

```{r esp_fig4}
# read ----

dp2 <- read_excel("data/countryprofile_fig4_alt.xlsx", sheet = ctry) %>%
  clean_names()

# tidy ----

dp21 <- dp2 %>%
  select(time, starts_with("share_")) %>%
  pivot_longer(-time) %>%
  rename(value_country = value)

dp21 <- dp21 %>%
  mutate(
    name = case_when(
      name == "share_tgoods_lp" ~ paste(ctry, "TG LP"),
      name == "share_tgoods_hp" ~ paste(ctry, "TG HP"),
      name == "share_tserv_lp" ~ paste(ctry, "TS LP"),
      name == "share_tserv_hp" ~ paste(ctry, "TS HP")
    )
  )

# plot ----

yrs <- sort(unique(dp21$time))
yrs <- seq(min(yrs), max(yrs), 1)

tghpgrew <- dp21 %>%
  filter(
    name == paste(ctry, "TG HP"),
    time %in% c(min(yrs), max(yrs))
  ) %>%
  summarise(grew = value_country - lag(value_country)) %>%
  drop_na() %>%
  pull() %>%
  round(1)

tglpgrew <- dp21 %>%
  filter(
    name == paste(ctry, "TG LP"),
    time %in% c(min(yrs), max(yrs))
  ) %>%
  summarise(grew = value_country - lag(value_country)) %>%
  drop_na() %>%
  pull() %>%
  round(1)

tghpdiff <- tghpgrew - tglpgrew
tghpmore <- ifelse(tghpdiff > 0, "grew", "declined")

tglpdiff <- tglpgrew - tglpgrew
tglpmore <- ifelse(tglpdiff > 0, "grew", "declined")

tshpgrew <- dp21 %>%
  filter(
    name == paste(ctry, "TS HP"),
    time %in% c(min(yrs), max(yrs))
  ) %>%
  summarise(grew = value_country - lag(value_country)) %>%
  drop_na() %>%
  pull() %>%
  round(1)

tslpgrew <- dp21 %>%
  filter(
    name == paste(ctry, "TS LP"),
    time %in% c(min(yrs), max(yrs))
  ) %>%
  summarise(grew = value_country - lag(value_country)) %>%
  drop_na() %>%
  pull() %>%
  round(1)

tshpdiff <- tshpgrew - tslpgrew
tshpmore <- ifelse(tshpdiff > 0, "grew", "declined")

tslpdiff <- tslpgrew - tslpgrew
tslpmore <- ifelse(tslpdiff > 0, "grew", "declined")

dp21 <- dp21 %>%
  mutate(category = gsub(paste0("^OECD |^", ctry, " "), "", name))

dp21 <- dp21 %>%
  mutate(
    country = str_replace_all(name, " .*", ""),
    category1 = str_sub(category, 1, 2),
    category1 = str_replace_all(category1, "TG", "Tradable goods"),
    category1 = str_replace_all(category1, "TS", "Tradable services"),
    category2 = paste(str_sub(category, 4, 5), time),
    category2 = str_replace_all(category2, "LP", "Lower half"),
    category2 = str_replace_all(category2, "HP", "Upper half")
  )

dp21_2 <- dp21 %>%
  select(country, time, category1, category2, value_country) %>%
  mutate(
    category2_2 = category2,
    category2 = str_replace_all(category2, as.character(min(yrs)), "minyr"),
    category2 = str_replace_all(category2, as.character(max(yrs)), "maxyr")
  ) %>%
  pivot_wider(names_from = category2, values_from = value_country) %>%
  clean_names()

# for plot_ly -----
dp21_2 <- dp21 %>%
  select(country, time, category1, category2, value_country) %>%
  mutate(type = ifelse(str_detect(category2, "Lower"), "Lower half", "Upper half"))

dp21_min_TG <- dp21 %>%
  select(country, time, category1, category2, value_country) %>%
  mutate(type = ifelse(str_detect(category2, "Lower"), "Lower half", "Upper half")) %>%
  filter(time == min(dp21_2$time) & category1 == "Tradable goods")

dp21_min_TS <- dp21 %>%
  select(country, time, category1, category2, value_country) %>%
  mutate(type = ifelse(str_detect(category2, "Lower"), "Lower half", "Upper half")) %>%
  filter(time == min(dp21_2$time) & category1 == "Tradable services")

dp21_max_TG <- dp21 %>%
  select(country, time, category1, category2, value_country) %>%
  mutate(type = ifelse(str_detect(category2, "Lower"), "Lower half", "Upper half")) %>%
  filter(time == max(dp21_2$time) & category1 == "Tradable goods")

dp21_max_TS <- dp21 %>%
  select(country, time, category1, category2, value_country) %>%
  mutate(type = ifelse(str_detect(category2, "Lower"), "Lower half", "Upper half")) %>%
  filter(time == max(dp21_2$time) & category1 == "Tradable services")

data_plotly <- tibble(
  "x" = c("Lower half", "Upper half"),
  "y" = dp21_min_TG$value_country,
  "y2" = dp21_min_TS$value_country,
  "y3" = dp21_max_TG$value_country,
  "y4" = dp21_max_TS$value_country,
  "name1" = dp21_min_TG$category2,
  "name2" = dp21_max_TG$category2
)

x <- c("Lower half", "Upper half")

y <- dp21_min_TG$value_country
y2 <- dp21_min_TS$value_country

y3 <- dp21_max_TG$value_country
y4 <- dp21_max_TS$value_country

name1 <- dp21_min_TG$category2
name2 <- dp21_max_TG$category2

p1 <- plot_ly() %>% add_trace(
  x = ~x, y = ~y3, color = ~x,
  type = "bar",
  name = ~name2,
  marker = list(
    color = c("#c8f075","#6bc5f2")
  )
)

p1 <- p1 %>% add_markers(
  x = ~x, y = ~y, color = ~x,
  name = ~name1,
  mode = "markers",
  marker = list(
    color = clrs4[2:1],
    size = 12,
    symbol = "diamond-dot"
  )
)

p1 <- p1 %>% layout(
  title = "Tradable goods",
  xaxis = list(title = "", visible = FALSE),
  yaxis = list(title = "Employment share (%)")
)

p2 <- plot_ly() %>% add_trace(
  x = ~x, y = ~y4, color = ~x,
  type = "bar",
  marker = list(color = c("#c8f075","#6bc5f2")),
  showlegend = FALSE
)

p2 <- p2 %>% add_markers(
  x = ~x, y = ~y2, color = ~x,
  mode = "markers",
  marker = list(
    color = clrs4[2:1],
    size = 12,
    symbol = "diamond-dot"
  ),
  showlegend = FALSE
)

p2 <- p2 %>% layout(
  title = "Tradable services",
  xaxis = list(title = "", visible = FALSE),
  yaxis = list(title = "Employment share (%)")
)

fig4 <- subplot(p1, p2, nrows = 1, margin = 0.05, shareX = TRUE, shareY = TRUE)

fig4 <- fig4 %>%
  layout(
    title = list(text = "Figure 4: Share of workers in most productive (tradable) sectors,\nTL3 regions", x = 0),
    margin = list(
      l = 50, r = 50,
      b = 50, t = 120,
      pad = 4
    ),
    annotations = list(
      list(
        x = 0.25,
        y = 1,
        font = list(size = 14),
        text = "Industry",
        xref = "paper",
        yref = "paper",
        xanchor = "center",
        yanchor = "bottom",
        showarrow = FALSE
      ),
      list(
        x = 0.75,
        y = 1,
        font = list(size = 14),
        text = "Tradable services",
        xref = "paper",
        yref = "paper",
        xanchor = "center",
        yanchor = "bottom",
        showarrow = FALSE
      )
    )
  )
```

```{r esp_fig4_text}
text_all <- dp2 %>%
  filter(time == 2020) %>%
  pull(text_all)
```

`r text_all`

```{r esp_fig4_2}
# put fig4 title in black

title_fig4 <- if (any(ctry %in% c("USA", "TUR", "NOR", "CHE", "IRL"))) {
  "Figure 4: Share of workers in most productive (tradable) sectors, \nTL2 regions"
} else {
  "Figure 4: Share of workers in most productive (tradable) sectors, \nTL3 regions"
}

# https://stackoverflow.com/questions/34610165/what-is-the-default-font-for-ggplot2
# https://plotly.com/r/reference/layout/
fig4 <- fig4 %>%
  layout(
    title = list(text = title_fig4, 
                 x = 0,
                 xanchor = "left",
                 xref = "paper",
                 font = list(color = "black", family = "Arial"))
  )

# remove legend background
fig4 <- fig4 %>%
  layout(
    legend = list(bgcolor = "rgba(0,0,0,0)")
  )
  
ggplotly(fig4) %>%
  config(displayModeBar = F)
```

<div class="oecdnote">
**Note**: A region is in the “upper half” if labour productivity was above the country median in the first year with available data and “lower half” if productivity was below the country median.  The share of workers in a given sector for a group of regions is defined as the sum of employment in that sector within the group divided by the sum of total employment within the group. Regions are small (TL3) regions, except for Australia, Canada, Chile, Ireland, Mexico, Norway, Switzerland, Türkiye and the United States where they are large (TL2) regions due to data availability. Industry includes the following tradable goods sectors: Mining and quarrying (B), Manufacturing (C), Electricity, gas, steam and air conditioning supply (D) and Water supply; sewerage; waste management and remediation activities (E) NACE macro sectors. Tradable services include Information and communication (J), Financial and insurance activities (K), Real estate activities (L), Professional, scientific and technical activities (M), Administrative and support service activities (N).  <br>
**Source**: OECD Regional Database (2022).
</div>

<br>
<br>

## Recent policy developments

```{r esp_txt}
read_html_text(ctry)
```

<table border="1" style="width:75%;margin-left:auto;margin-right:auto;">
  <tr>
    <th style="background:#d4edff; padding:10px;font-size:9.0pt;" valign="center">
      Territorial definitions
    </th>
  </tr>
  <tr>
    <td>
      <p style="padding-left:10px;padding-right:10px;padding-top:10px;font-size:9.0pt;">
        The data in this note reflect different sub-national geographic levels in OECD countries. In particular, <b>regions</b> are classified on two territorial levels reflecting the administrative organisation of countries: large regions (TL2) and small regions (TL3).
      </p>
      <p style="padding-left:10px;padding-right:10px;font-size:9.0pt;">
        Small regions are classified according to their access to metropolitan areas (Fadic et al. 2019). The typology classifies small (TL3) regions into metropolitan and non-metropolitan regions according to the following criteria:
      </p>
      <ul style="list-style-type:circle;font-size:9.0pt;padding-right:10px">
        <li>
          <b>Metropolitan regions</b>, if more than half of the population live in a FUA. Metropolitan regions are further classified into: <b>metropolitan large</b>, if more than half of the population live in a (large) FUA of at least 1.5 million inhabitants; and <b>metropolitan midsize</b>, if more than half of the population live in a (midsize) FUA of at 250 000 to 1.5 million inhabitants.
        </li>
        <li>
          <b>Non-metropolitan regions</b>, if less than half of the population live in a midsize/large FUA. These regions are further classified according to their level of access to FUAs of different sizes: <b>near a midsize/large FUA</b> if more than half of the population live within a 60-minute drive from a midsize/large FUA (of more than 250 000 inhabitants) or if the TL3 region contains more than 80% of the area of a midsize/large FUA; <b>near a small FUA</b> if the region does not have access to a midsize/large FUA and at least half of its population have access to a small FUA (i.e. between 50 000 and 250 000 inhabitants) within a 60-minute drive, or contains 80% of the area of a small FUA; and <b>remote</b>, otherwise.
        </li>
      </ul>
      </p>
      <p style="padding-left:10px;padding-right:10px;font-size:9.0pt;">
        Disclaimer: <a href="https://oecdcode.org/disclaimers/territories.html" target="_blank"><i style="color:#0d6efd">https://oecdcode.org/disclaimers/territories.html</i></a>
      </p>
    </td>
  </tr>
</table>