---
title: "Tables and Figures"
author: "Francisco Lara-García"
output:
  html_document:
    df_print: paged
  word_document: default
  pdf_document: default
editor_options: 
  chunk_output_type: inline
---

```{r setup, include=FALSE}

library(knitr)
library(readxl)
library(dplyr)
library(tidyr)
library(tidyverse)
library(ggplot2)
library(kableExtra)
library(rlang)
library(RColorBrewer)
library(gridExtra)
library(grid)
library(naniar)

#clear workspace
rm(list= ls())

knitr::opts_chunk$set(message = FALSE, echo = FALSE, warning = FALSE)
options(scipen=999)
options(digits = 3)
options(knitr.kable.NA = "--")
options(knitr.table.format = "html")

```

```{r load data, include=FALSE}

##Tidy Data
df_litrev <- read_rds("Importable/2_FLG_litrev2.rds") %>% filter(Incorporation == 1) ##full dataset

```

```{r sites description, echo = FALSE}

##number of books

bks <- c("Book", "Book Chapter")

df_litrev %>% filter(Journal %in% bks) %>% group_by(ID) %>%
  summarize(n_cases = n()) %>% 
  nrow() %>% as.numeric() -> n_books

df_litrev %>% filter(Journal == "Book") %>%
  nrow() %>% as.numeric() -> n_book_obs

df_litrev %>% filter(Journal == "Book Chapter") %>%
  nrow() %>% as.numeric() -> n_bookchapter_obs

articles <- c("JEMS", "ERS", "IMR")

df_litrev %>% filter(Journal %in% articles) %>% group_by(ID) %>%
  summarize(n_cases = n()) %>% 
  nrow() %>% as.numeric() -> n_articles

df_litrev %>% filter(Journal %in% articles) %>%
  nrow() %>% as.numeric() -> n_articles_obs

df_litrev %>% filter(Scale1 != "Region") %>% summarize(n_distinct(Site1)) %>%
  as.numeric() -> distinct_sites

df_litrev %>% filter(Scale1 != "Region") %>% summarize(n_distinct(Site2)) %>% 
  as.numeric() -> distinct_sites1

totaltexts <- n_articles + n_books

```

```{r t1, echo = FALSE}

## TABLE STUDY SCALES

df_litrev %>% group_by(Scale1) %>% summarize(N = n()) %>%
  mutate(`%` = (N/sum(N)) * 100) %>% 
  arrange(desc(N)) -> t1

t1 %>% summarise_if(is.numeric, sum) -> t1_N
t1 <- full_join(t1, t1_N) %>% mutate_if(is.factor, as.character) 
t1[is.na(t1)] <- "Total"

```

```{r t2, echo = FALSE}

##TABLE STUDY FREQUENCY

unspecific <- c("American Cities", "British Counties", "American States", 
                "Swiss Canton", "Taiwanese Cities", "Cities in the Former Soviet Union", 
                "Greek Cities", "Estonian Cities", "German Cities")
df_litrev %>% 
  filter(Scale1 != "Region", 
         !grepl("Pseudonym", Site2), 
         !grepl("Municipalities", Site2)) %>%
  group_by(Site2, pop_2010) %>% 
  summarize(N = n()) %>% arrange(desc(N)) %>% 
  filter(!Site2 %in% unspecific) %>% 
  filter(N > 1) -> t2_settle

t2 <- t2_settle %>% ungroup() %>% top_n(n = 20)
t2 %>% select(-pop_2010) %>% mutate(rank = rank(-N, ties.method = "min")) %>% 
  select(rank, Site2, N) -> t2

t2_settle %>% nrow() %>% as.numeric () -> t2_settle_1
t2_settle %>% filter(N > 2) %>% nrow() %>% as.numeric () -> t2_settle_2
t2 %>% as.data.frame() -> t2_name

##Percentage of recurrent studies over 5 million

t2_settle %>% mutate(pop_5mill = ifelse(pop_2010 > 5000000, 1, 0),
                     pop_5th = ifelse(pop_2010 < 500000, 1, 0)) -> t2_settle_3

studiedonce <- t2_settle_1/distinct_sites1
studiedtwice <- t2_settle_2/distinct_sites1

```

```{r t3, echo = FALSE}

## TABLE PERCENT OF TEXTS THAT STUDY TOP SITES

df_litrev %>% 
  select(ID, Year, Author, Journal, Site2, N_Sites) %>% 
  group_by(Year, Author, ID, Journal, Site2, N_Sites) %>% 
  summarize(n()) %>% mutate(n = 1) %>% select(-'n()') %>%
  spread(Site2, n, fill=0) -> t3

names(t3) <- make.names(names(t3))

t2_settle %>% top_n(n = 20) -> top20_n

top20_n$Site2 %>% as.vector()%>% make.names() -> top20_list

###Top20 Names and expressions

top1 <- top20_list[1]
top2 <- top20_list[2]
top3 <- top20_list[3]
top4 <- top20_list[4]
top5 <- top20_list[5]
top6 <- top20_list[6]
top7 <- top20_list[7]
top8 <- top20_list[8]
top9 <- top20_list[9]
top10 <- top20_list[10]
top11 <- top20_list[11]
top12 <- top20_list[12]
top13 <- top20_list[13]
top14 <- top20_list[14]
top15 <- top20_list[15]
top16 <- top20_list[16]
top17 <- top20_list[17]
top18 <- top20_list[18]
top19 <- top20_list[19]
top20 <- top20_list[20]
top21 <- top20_list[21]
top22 <- top20_list[22]
top23 <- top20_list[23]
top24 <- top20_list[24]

e_top5 <- exprs(
  .data[[top1]] == 1 ~ 1,
  .data[[top2]] == 1 ~ 1,
  .data[[top3]] == 1 ~ 1,
  .data[[top4]] == 1 ~ 1,
  .data[[top5]] == 1 ~ 1,
  TRUE ~ 0
)

e_top10 <- exprs(
  .data[[top1]] == 1 ~ 1,
  .data[[top2]] == 1 ~ 1,
  .data[[top3]] == 1 ~ 1,
  .data[[top4]] == 1 ~ 1,
  .data[[top5]] == 1 ~ 1,
  .data[[top6]] == 1 ~ 1,
  .data[[top7]] == 1 ~ 1,
  .data[[top8]] == 1 ~ 1,
  .data[[top9]] == 1 ~ 1,
  .data[[top10]] == 1 ~ 1,
  TRUE ~ 0
)

e_top15 <- exprs(
  .data[[top1]] == 1 ~ 1,
  .data[[top2]] == 1 ~ 1,
  .data[[top3]] == 1 ~ 1,
  .data[[top4]] == 1 ~ 1,
  .data[[top5]] == 1 ~ 1,
  .data[[top6]] == 1 ~ 1,
  .data[[top7]] == 1 ~ 1,
  .data[[top8]] == 1 ~ 1,
  .data[[top9]] == 1 ~ 1,
  .data[[top10]] == 1 ~ 1,
  .data[[top11]] == 1 ~ 1,
  .data[[top12]] == 1 ~ 1,
  .data[[top13]] == 1 ~ 1,
  .data[[top14]] == 1 ~ 1,
  .data[[top15]] == 1 ~ 1,
  TRUE ~ 0
)

e_top20 <- exprs(
  .data[[top1]] == 1 ~ 1,
  .data[[top2]] == 1 ~ 1,
  .data[[top3]] == 1 ~ 1,
  .data[[top4]] == 1 ~ 1,
  .data[[top5]] == 1 ~ 1,
  .data[[top6]] == 1 ~ 1,
  .data[[top7]] == 1 ~ 1,
  .data[[top8]] == 1 ~ 1,
  .data[[top9]] == 1 ~ 1,
  .data[[top10]] == 1 ~ 1,
  .data[[top11]] == 1 ~ 1,
  .data[[top12]] == 1 ~ 1,
  .data[[top13]] == 1 ~ 1,
  .data[[top14]] == 1 ~ 1,
  .data[[top15]] == 1 ~ 1,
  .data[[top16]] == 1 ~ 1,
  .data[[top17]] == 1 ~ 1,
  .data[[top18]] == 1 ~ 1,
  .data[[top19]] == 1 ~ 1,
  .data[[top20]] == 1 ~ 1,
  .data[[top21]] == 1 ~ 1,
  .data[[top22]] == 1 ~ 1,
  .data[[top23]] == 1 ~ 1,
  .data[[top24]] == 1 ~ 1,
  TRUE ~ 0
)

##Case when
t3 %>% mutate(Top5 = case_when(!!!e_top5)) -> t3
t3 %>% mutate(Top10 = case_when(!!!e_top10)) -> t3
t3 %>% mutate(Top15 = case_when(!!!e_top15)) -> t3
t3 %>% mutate(Top20 = case_when(!!!e_top20)) -> t3

t3 %>% ungroup() %>% select(ID, Year, Author, Top5, Top10, Top15, Top20) %>% 
  distinct() -> t3.1

t3.1 %>% summarize (n = sum(Top5), pct = sum(Top5)/n()* 100) -> t3.2
t3.1 %>% summarize (n = sum(Top10), pct = sum(Top10)/n() * 100) -> t3.3
t3.1 %>% summarize (n = sum(Top15), pct = sum(Top15)/n() * 100) -> t3.4
t3.1 %>% summarize (n = sum(Top20), pct = sum(Top20)/n() * 100) -> t3.5

t3.6 <- bind_rows(t3.2, t3.3, t3.4, t3.5)
t3.6$top <- c("At least one of 5 most researched", 
              "At least one of 10 most researched", 
              "At least one of 15 most researched",
              "At least one of 20 most researched")

t3.6 %>% select(top, everything()) %>% as.data.frame() -> t3.6

```

```{r t4, echo = FALSE}

## TABLE POPULATION

df_litrev %>% filter(Scale1 != "Region") %>% mutate(pop_2010_1 = ifelse(pop_2010 >= 10000000, 10000000, pop_2010)) -> t4

t4 %>% mutate(pop_2010_2 = case_when(pop_2010 < 500000 ~ "500,000 or less",
                                     pop_2010 >= 500000 & pop_2010 < 1000000 ~ "500,000 to 1 million",
                                     pop_2010 >= 1000000 & pop_2010 < 1500000 ~ "1 million to 1.5 million",
                                     pop_2010 >= 1500000 & pop_2010 < 2000000 ~ "1.5 million to 2 million",
                                     pop_2010 >= 2000000 & pop_2010 < 2500000 ~ "2 million to 2.5 million",
                                     pop_2010 >= 2500000 & pop_2010 < 3000000 ~ "2.5 million to 3 million",
                                     pop_2010 >= 3000000 & pop_2010 < 3500000 ~ "3 million to 3.5 million",
                                     pop_2010 >= 3500000 & pop_2010 < 4000000 ~ "3.5 million to 4 million",
                                     pop_2010 >= 4000000 & pop_2010 < 4500000 ~ "4 million to 4.5 million",
                                     pop_2010 >= 4500000 & pop_2010 < 5000000 ~ "4.5 million to 5 million",
                                     pop_2010 > 5000000 ~ "5 million or more",
                                     TRUE ~ as.character(pop_2010)),
            pop_2010_2 = factor(pop_2010_2, levels=c("500,000 or less", "500,000 to 1 million",
                                                     "1 million to 1.5 million","1.5 million to 2 million",
                                                     "2 million to 2.5 million","2.5 million to 3 million",
                                                     "3 million to 3.5 million", "3.5 million to 4 million",
                                                     "4 million to 4.5 million", "4.5 million to 5 million",
                                                     "5 million or more"))) -> t4

#5 million or more  
t4 %>% filter(!is.na(pop_2010_2)) %>% group_by(pop_2010_2) %>% summarize(N = n()) %>%
  mutate(pct = N/sum(N)*100) -> t4.1
t4.1 %>% summarise_if(is.numeric, sum) -> t4_N
t4.1 <- full_join(t4.1, t4_N) %>% mutate_if(is.factor, as.character) 
t4.1[is.na(t4.1)] <- "Total"


```


```{r settlement fbpop, echo = FALSE}


lamisna <- c(":", " ", "c", "0~", ".", "-")
df_ons2020 <- read_excel("Importable/2_onsmigration2020.xlsx") %>% replace_with_na_all(condition = ~.x %in% lamisna)

fbuk <- df_ons2020 %>%  select(areacode, areaname, residentpop2020, fbpop2020) %>%
  mutate(residentpop2020 = as.numeric(residentpop2020) * 1000,
         fbpop2020 = as.numeric(fbpop2020) * 1000,
         code = str_sub(areacode, 1, 3))

#BINS


twofivekbins <- c(0, 250000, 500000, 750000, 1000000,
               1250000, 1500000, 1750000, 2000000, 
               2250000, 2500000, 2750000, 3000000,
               3250000, 3500000, 3750000, 4000000,
               4250000, 4500000, 4750000, 5000000, 
               50000000)

n_bins <- length(twofivekbins) - 1


#Study Proportion

studyprop <- t4 %>% filter(!is.na(pop_2010)) %>% mutate(pop_2010_3 = cut(pop_2010, breaks = twofivekbins, labels = FALSE))

studyprop <- studyprop %>% group_by(pop_2010_3) %>% summarize(count = n()) %>%
  complete(pop_2010_3 = full_seq(1:n_bins, 1), fill = list(count = 0)) %>%
   mutate(prop = count/sum(count))


#US counties
df_acs2019 <- read_rds("Importable/2_acs2019_birthplace.rds")

fbus <- df_acs2019 %>% mutate(total_2 = cut(total, breaks = twofivekbins, labels = FALSE), 
                          fbnatprop = fbtotal/sum(fbtotal))

fbus1 <- fbus %>% group_by(total_2) %>% summarize(fbprop = sum(fbnatprop)) %>%
  complete(total_2 = full_seq(1:n_bins, 1), fill = list(fbprop = 0))

#English counties, metropolitan counties and inner and outer london
fbuk1 <- fbuk %>% filter(code %in% c("E10", "E11", "E13")) %>% filter(!is.na(fbpop2020))
fbuk1 <- fbuk1 %>% mutate(total_2 = cut(residentpop2020, breaks = twofivekbins, labels = FALSE), 
                          fbnatprop = fbpop2020/sum(fbpop2020))

fbuk2 <- fbuk1 %>% group_by(total_2) %>% summarize(fbprop = sum(fbnatprop)) %>%
  complete(total_2 = full_seq(1:n_bins, 1), fill = list(fbprop = 0))

#German administrative districts

df_germanfso <- read_excel("Importable/2_germanfso2020.xlsx") %>% replace_with_na_all(condition = ~.x %in% lamisna)

fbgerman <- df_germanfso %>% filter(!is.na(fbpop2020)) %>% mutate(fbpop2020 = as.numeric(fbpop2020),
                                    residentpop2020 = as.numeric(residentpop2020),
                                    total_2 = cut(residentpop2020, breaks = twofivekbins, labels = FALSE), 
                                    fbnatprop = fbpop2020/sum(fbpop2020))

fbgerman1 <- fbgerman %>% group_by(total_2) %>% summarize(fbprop = sum(fbnatprop)) %>%
  complete(total_2 = full_seq(1:n_bins, 1), fill = list(fbprop = 0))



```


```{r t5, echo = FALSE}

df_litrev %>% group_by(ID, Author, Year) %>%
  summarize(large = max(large), 
            second_large = max(second_large), 
            third_large = max(third_large),
            largest3 = max(largest3)) -> t5

t5 %>% group_by(large) %>% 
  summarize(n = n()) %>% 
  mutate(pct = n/sum(n) * 100) %>% arrange(desc(large)) -> t5.1

t5.1$cap <- c("City", "Other")

t5 %>% group_by(second_large) %>% 
  summarize(n = n()) %>% 
  mutate(pct = n/sum(n) * 100) %>% arrange(desc(second_large)) -> t5.2

t5.2$cap <- c("City", "Other")

t5 %>% group_by(third_large) %>% 
  summarize(n = n()) %>% 
  mutate(pct = n/sum(n) * 100) %>% arrange(desc(third_large)) -> t5.3

t5.3$cap <- c("City", "Other")

t5 %>% group_by(largest3) %>% 
  summarize(n = n()) %>% 
  mutate(pct = n/sum(n) * 100) %>% arrange(desc(largest3)) -> t5.4

t5.4$cap <- c("City", "Other")


dplyr::bind_cols(t5.1, t5.2, t5.3, t5.4, .id =NULL) -> t5.5

t5.5 %>% select(cap...4, n...2, pct...3,
                n...6, pct...7,
                n...10, pct...11,
                n...14, pct...15) -> t5.6

##Total

t5.6 %>% summarise_if(is.numeric, sum) -> t5_N
t5.6 <- full_join(t5.6, t5_N) %>% mutate_if(is.factor, as.character) 
t5.6[is.na(t5.6)] <- "Total"

```

```{r t6, echo = FALSE}

##Percent of Texts that study that study at least one capital

df_litrev %>% group_by(ID, Author, Year) %>%
  summarize(ncapital = max(National_Capital), 
            pcapital = max(Provincial_Capital), 
            bcapital = max(Both_Capital)) %>% 
  mutate(bcapital1 = case_when(bcapital == 1 ~ 1,
                              bcapital == 0 ~ 0,
                              is.na(bcapital) ~ 0))  -> t6

## summarize by percents

t6 %>% group_by(ncapital) %>% 
  summarize(n = n()) %>% 
  mutate(pct = n/sum(n) * 100) %>% arrange(desc(ncapital)) -> t6.1

t6.1$cap <- c("Capital", "Other")

t6 %>% group_by(pcapital) %>% 
  summarize(n = n()) %>% 
  mutate(pct = n/sum(n) * 100) %>% arrange(desc(pcapital)) -> t6.2

t6.2$cap <- c("Capital", "Other")

t6 %>% group_by(bcapital1) %>% 
  summarize(n = n()) %>% 
  mutate(pct = n/sum(n) * 100) %>% arrange(desc(bcapital1))-> t6.3

t6.3$cap <- c("Capital", "Other")

dplyr::bind_cols(t6.1, t6.2, t6.3) -> t6.4
t6.4 %>% select(cap...4, n...2, pct...3,
                cap...8, n...6, pct...7,
                cap...12, n...10, pct...11) -> t6.4

##Total

t6.4 %>% summarise_if(is.numeric, sum) -> t6_N
t6.4 <- full_join(t6.4, t6_N) %>% mutate_if(is.factor, as.character) 
t6.4[is.na(t6.4)] <- "Total"

```

```{r table 1 scale, echo = FALSE}

t1 %>% kable(caption = "Table 1. Study Scale", digits = 2, align = "c", booktabs = T, 
      col.names = c("Scale", "N", "%")) %>%
  kable_styling( full_width = T)

```

```{r table 2 study frequency, results='asis'}

t2 %>% knitr::kable(caption = "Table 2. Ranked Order of 20 Most Studied Settlements",
                    digits = 2, align = "c", booktabs = T, 
                    col.names = c("", "Site", "N")) %>% row_spec(row = 0, bold = T) %>% 
  kable_styling( full_width = T)

```
```{r table 20 most, results='asis'}

t3.6 %>% knitr::kable(caption = "Table 3. Percentage of Texts that Include The Most Studied Cities", digits = 4, align = "c", booktabs = T,
                      col.names = c("", "N", "%")) %>% row_spec(row = 0, bold = T) %>% 
  kable_styling(full_width = T)

```

```{r figure 1 population, echo = FALSE}

plot1 <- ggplot(t4, aes(x=pop_2010_1)) +  theme_bw() + 
  geom_histogram(binwidth=100000) +
    scale_x_continuous(breaks = c(0, 2000000, 4000000, 6000000, 8000000, 10000000), 
                     labels = c("0", "2", "4", 
                                "6", "8", "10+")) + labs(x = "Population (in Millions)", y = "Reviewed Studies (Frequency)") +
  ggtitle("Figure 1. Settlements Studied by Population")

#### 

plot2 <- ggplot(data=studyprop, aes(x=pop_2010_3, y=prop), width = 0.9) +
  geom_bar(stat="identity") +  theme_bw() + ylim(0, .4)+ scale_x_continuous(breaks = c(1, 5, 9, 13, 17, 21), 
                     labels = c("0", "1", "2", "3", 
                                "4", "5+")) + labs(x = "Settlement Population (in Millions)", y = "Proportion Study-Observations") +
  ggtitle("Figure 2. Reviewed Studies Proportion")


plot3 <- ggplot(data=fbus1, aes(x=total_2, y=fbprop), width = 0.9) +
  geom_bar(stat="identity") +  theme_bw() + ylim(0, .4)+ scale_x_continuous(breaks = c(1, 5, 9, 13, 17, 21), 
                     labels = c("0", "1", "2", "3", 
                                "4", "5+")) + labs(x = "County Population (in Millions)", y = "Proportion Foreign-Born") +
  ggtitle("Figure 3. Foreign-Born Proportion in US Counties")


plot4 <- ggplot(data=fbuk2, aes(x=total_2, y=fbprop), width = 0.9) +
  geom_bar(stat="identity") +  theme_bw() + ylim(0, .4) + scale_x_continuous(breaks = c(1, 5, 9, 13, 17, 21), 
                     labels = c("0", "1", "2", "3", 
                                "4", "5+")) + labs(x = "County Population (in Millions)", y = "Proportion Foreign-Born") +
  ggtitle("Figure 4. Foreign Born in English Counties, Metropolitan Counties and London")


plot5 <- ggplot(data=fbgerman1, aes(x=total_2, y=fbprop), width = 0.9) +  geom_bar(stat="identity") +  theme_bw() + 
  ylim(0, .4) + scale_x_continuous(breaks = c(1, 5, 9, 13, 17, 21), 
                     labels = c("0", "1", "2", "3", 
                                "4", "5+")) + labs(x = "German District Population (in Millions)", y = "Proportion Foreign-Born") +
  ggtitle("Figure 5. Foreigners in German Administrative Districts")

plot1
plot2
plot3
plot4
plot5


plot1 <- plot1 + theme(text = element_text(size=60))
plot2 <- plot2 + theme(text = element_text(size=60))
plot3 <- plot3 + theme(text = element_text(size=60))
plot4 <- plot4 + theme(text = element_text(size=60))
plot5 <- plot5 + theme(text = element_text(size=60))

####

pathplots <- "Importable/2_Output/"
ggsave(file= "1_studyfreq.png", plot1, path = pathplots, height = 18, width = 36, units = "in") #
ggsave(file= "2_studyprop.png", plot2, path = pathplots, height = 18, width = 36) #
ggsave(file="3_uscountycomp.png", plot3, path = pathplots, height = 18, width = 36) #
ggsave(file="4_ukcountycomp.png", plot4, path = pathplots, height = 18, width = 36) #
ggsave(file="5_germancountycomp.png", plot5, path = pathplots, height = 18, width = 36) #

```

```{r table 4, results='asis'}

t4.1 %>% knitr::kable(caption = "Settlements Studied by Population (Percentage)", digits = 2, align = "c", booktabs = T,
                      col.names = c("Population", "N", "%")) %>% row_spec(row = 0, bold = T) %>% 
  kable_styling( full_width = T)

```

```{r table 5 One Large City, echo = FALSE}

t5.6 %>% kable(caption = "Texts that Study At Least One Large City", 
               digits = 2, align = "c", booktabs = T, 
      col.names = c("", "N", "%", 
                    "N", "%", 
                    "N", "%", 
                    "N", "%")) %>%
  kable_styling( full_width = T)  %>%
  add_header_above(c("1st Largest" = 3, "2nd Largest" = 2, "3rd Largest" = 2, "Top 3" = 2))
```

```{r table 6 Capital City, echo = FALSE}

t6.4 %>% kable(caption = "Texts that Study At Least One Capital City", 
               digits = 2, align = "c", booktabs = T, 
      col.names = c("", "N", "%", "", "N", "%", "", "N", "%")) %>%
  kable_styling( full_width = T)  %>%
  add_header_above(c("National" = 3, "Provincial" = 3, "Either National or Provincial" = 3))

```

```{r table 7 study frequency, results='asis'}

t2_settle %>% knitr::kable(caption = "Settlement Study Frequency (More Than Once)",
                    digits = 2, align = "c", booktabs = T, 
                    col.names = c("", "Site", "N")) %>% row_spec(row = 0, bold = T) %>% 
  kable_styling( full_width = T)

```

