---
title: "Comparative Tables"
author: "Francisco Lara"
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(janitor)

#clear workspace
rm(list= ls())


knitr::opts_chunk$set(echo = TRUE)
options(scipen=999)
options(digits = 2)
options(knitr.kable.NA = "--")
options(knitr.table.format = "latex")

```

```{r cleaning, include=FALSE}

df <- read.csv("Importable/1_FLG_litrev.csv")

##Clean Data From census
df_uscensus2010 <- read_csv("Importable/1_USCensus_2010.csv")
df_acs2019 <- read_csv("Importable/1_USCensus_ACS5YR.csv")

##Data from World Urbanization Project

df_unpop<- read_excel("Importable/1_WUP2018-F12-Cities_Over_300K.xls")

##Add population and capital city data
df_pop<- read_excel("Importable/1_FLG_dfpop.xlsx",
                       col_types = c("text", "text", "numeric", "numeric", "numeric"))

#Other Missing population Data
df_miss <- read_rds("Importable/1_FLG_misspop.rds") %>% select(-Source, -Note)

```

RECODING OBSERVATIONS TO AGGREGATED CATEGORIES

```{r cleaning 1, include=FALSE}

## create Site 1 to recode some observations.

df$Site1<- NA
df$Site2<- NA
df$Scale1<- NA

## Change vectors to character. Will be easier to change the observation type. 

df$Site1 <-as.character(df$Site1)
df$Site2 <-as.character(df$Site2)
df %>% select (ID, Year, Author, Journal, Incorporation, 
               Scale, Scale1, Nation, N_Sites, Site, Site1, Site2,
               Notes, Coder) -> df

#For recoding below
df$Scale <- as.character(df$Scale)
df$Author <- as.character(df$Author)
df$N_Sites <- as.numeric(df$N_Sites)

```

RECODING SCALE OBSERVATIONS

Scales is comprised of 4 categories: Region, Settlement, Neighborhood and Organizations. Of course, defining the boundaries of these categories is not clear cut, but here the idea is that these are categories that are nested within each other and are understood as being geographically different sizes. Regions are meant to refer to large areas that encompass many human settlements and surrounding uninhabitated space. An example of this geographic entity is the American West or NUTS1 designation in Europe. Settlements follow the definition that Brenner and Schmid (2014, 2015) argue against: spatially-bounded local places with human populations. This might be criticized as a purposeless use of jargon, but I agree with Gans and Brenner et. al that rural/suburban/urban categories have no clear distinction and obfuscate more often than they clarify. Since this paper is about immigrant integration, and I am interested variation in local contexts, I wanted a term that would designate a local scale but that was universal enough to capture the varied types of places immigrants live. Settlement, following Gans suggestions, was a good fit - and was preferred over local because local has scalarly-flexible connotations. Neighborhoods are typically understood as referring to geographic areas within settlements, and this is the definition used here. This includes things like ethnic enclaves or Little Italy. Finally, organizations follow Small and McDermott's (2006) definition as an "establishment that has a physical location and offers services or sells goods basic to day-to-day living." These are the most granular scale that is coded because more granular scales would be at level of individuals. 

```{r recode scale, include=FALSE}

###RECODE SCALE

#LOCALITIES
#McGarrigle 2015 ~ This study compares neighborhoods and suburbs and refers to them as localities
df$Scale[df$Author =="McGarrigle" & df$Site == "2 Cities in Lisbon District"] <- "City"
df$Scale[df$Author =="McGarrigle" & df$Site == "1 Neighborhood in Sintra District"] <- "Neighborhood"

df$Scale[df$Author =="Hern?ndez-Carretero" & df$Site == "Catalonia, Spain"] <- "Region"

#Williamson also refers to Campsie as a neighborhood
df$Scale[df$Author =="Williamson" & df$Site == "Campsie, Sydney"] <- "Neighborhood"

##METROPOLITAN AREA ~ Mostly can be categorized as settlements. 
#Ellis 2014 is coded incorrectly. Should be State level data
df$Scale[df$Author =="Ellis et al." & df$ID == 134] <- "State"
df$Site[df$Author =="Ellis et al." & df$ID == 134] <- "Interstate ACS and CPS Data"

#Any site that contains greater "CITY" in the site name  is coded as metropolitan area 
df$Scale[df$Author =="Trovão" & df$Site == "Greater Libson Area"] <- "Metropolitan Area"
df$Scale[df$Author =="DuCros" & df$Site == "Greater Los Angeles Area"] <- "Metropolitan Area"
df$Scale[df$Author =="Phan et al." & df$Site == "Greater Toronto Area"] <- "Metropolitan Area"
df$Scale[df$Author =="Myrberg" & df$Site == "Greater Stockholm"] <- "Metropolitan Area"

##CITY;TRANSNATIONAL ~ This one study is difficult to code because it combines many data sources. Split into two different studies like Jimenez 2 with different scales. City;transnational is then settlement below

df$Scale[df$Author == "Verkuyten" & df$Site == "Germany"] <- "Nation-State"
df$Author[df$Author =="Verkuyten" & df$Site == "Germany"] <- "Verkuyten 2"

df$Scale[df$Author =="Verkuyten" & df$Site == "Netherlands"] <- "Nation-State"
df$Author[df$Author =="Verkuyten" & df$Site == "Netherlands"] <- "Verkuyten 2"

##COMMUNITY ~ This category disappears
#Kearns and Whitley 2015 - This refers to neighborhoods
df$Scale[df$Author == "Kearns and Whitley"] <- "Neighborhood"
#Moreh - This refers to a settlement within the Community of Madrid
df$Scale[df$Author == "Moreh"] <- "City"
#Patzelt - This refers to a community of people interspersed within the city of Ottawa
df$Scale[df$Author == "Patzelt"] <- "City"
#Holgate et al. - This refers to boroughs in London
df$Scale[df$Author == "Holgate et al."] <- "Borough"
#Wylyl - This refers to St. Paul, not a community
df$Scale[df$Author == "Wyly et al."] <- "City"

##DISTRICT ~ 

##Williams et al. is difficult to code because it combines two data sources. Split into two different studies like Jimenez 2 with different scale (Region and Nation-State)

df$Author[df$Author == "Williams et al." & df$Site == "Chitwan Valley, Nepal"] <- "Williams et al. 2"
df$Scale[df$Author =="Williams et al. 2"] <- "Region"
df$Scale[df$Author =="Williams et al."] <- "Nation-State"

##Bayram 2009 ~ Bayrami is referring to neighborhoods as districts. Need to recode.

df$Scale[df$Author == "Bayram et al."] <- "Neighborhood"

##Hussain and Bagguley compares neighborhoods and a town
df$Scale[df$Author =="Hussain and Bagguley" & df$Site == "2 Neighborhoods in Leeds"] <- "Neighborhood"
df$Scale[df$Author =="Hussain and Bagguley"  & df$Site == "Dewsbury"] <- "Town"

##It is a little unclear in this study what spatial scale they were operating in. 
df$Scale[df$Author =="Schuster and Majidi" & df$Site == "Kabul"] <- "Province" <- ##could be city or province. Made a call that province was better. 
df$Scale[df$Author =="Schuster and Majidi"  & df$Site == "Nangarhar"] <- "Province"
df$Scale[df$Author =="Schuster and Majidi"  & df$Site == "Herat"] <- "City"
df$Scale[df$Author =="Schuster and Majidi"  & df$Site == "Balkh"] <- "Province"

##Ley is referring to Metropolitan Vancouver
df$Scale[df$Author =="Ley"] <- "Metropolitan Area"

##Silm is referring to neighborhoods within a city
df$Scale[df$Author == "Silm et al."] <- "Neighborhood"

##Pereira is talking about municipalities when referring to districts
df$Scale[df$Author =="Pereira"] <- "Municipality" ##could be city or

##In Germany districts are about the size of counties
df$Scale[df$Author =="Meyer"] <- "Municipality" ##could be city or

##CITY/border ~ This study is talking about adjacent border towns, recoded to town.
df$Scale[df$Author =="Pfoser"] <- "Town"

##DIASPORA
##Eliassi conducted interviews in cities
df$Scale[df$Author =="Eliassi"] <- "City"

##CPS Data
##This is a state level study
df$Scale[df$Author =="Bozick et al."] <- "State"

##MOSQUE ~ This includes two national surveys
df$Scale[df$Author =="Bagby"  & df$Site == "Mosque Life Survey (MLS)"] <- "Nation-State"
df$Scale[df$Author =="Bagby"  & df$Site == "Mosque Study Project"] <- "Nation-State"

##REGION ~ This compares across a region and settlement. Change Sheffield to settlement
df$Scale[df$Author =="Scott and Cartledge"  & df$Site == "Sheffield"] <- "City"

df$Scale[df$Author =="Østergaard-Nielsen"  & df$Site == "Catalonia"] <- "Region"

df$Scale[df$Author =="Cabrera and Glavac"  & df$Site == "Southern Arizona"] <- "Region"

df$Scale[df$Author =="Bloch"  & df$Site == "the Northwest"] <- "Region"
df$Scale[df$Author =="Bloch"  & df$Site == "Midlands"] <- "Region"

df$Scale[df$Author =="Vives and Silva" & df$Site == "Galicia"] <- "Region"
df$Scale[df$Author =="Vives and Silva" & df$Site == "Andalusia"] <- "Region"

df$Scale[df$Author =="Alexander" & df$Site == "District of Dinajpur, Bangladesh"] <- "Region"
df$Scale[df$Author =="Alexander" & df$Site == "District of Satkhira, Bangladesh"] <- "Region"

df$Scale[df$Author =="Bugg" & df$Site == "Gujarat, India"] <- "Region"

##Organization ~ Akidwa is a national organization, so should not be included in the analysis. 
df$Scale[df$Author =="De Tona and Lentin"  & df$Site == "AkiDwa"] <- "National Organization"

##CITY/NATION-STATE
df$Scale[df$Author =="A El Moudden"  & df$Site == "Morocco"] <- "Nation-State"
df$Scale[df$Author =="A El Moudden"  & df$Site == "Hejaz, Saudi Arabia"] <- "Region" #Hejaz is actually a region

df$Scale[df$Author =="R Mandel"  & df$Site == "Turkey"] <- "Nation-State"
df$Scale[df$Author =="R Mandel"  & df$Site == "West Germany"] <- "Nation-State"
df$Scale[df$Author =="R Mandel"  & df$Site == "West Berlin"] <- "City"

#CITY/NEIGHBORHOOD
df$Scale[df$Author =="R Mandel"  & df$Site == "Kolhewadi, Ratnagiri, India"] <- "Village"
df$Scale[df$Author =="R Mandel"  & df$Site == "Kamrangirchar, Dhaka, Bangladesh"] <- "Neighborhood"
df$Scale[df$Author =="R Mandel"  & df$Site == "Shenzhen, China"] <- "City"
df$Scale[df$Author =="R Mandel"  & df$Site == "Kibera, Nairobi"] <- "Neighborhood"
df$Scale[df$Author =="R Mandel"  & df$Site == "Santa Marta, Rio de Janeiro, Brazil"] <- "Neighborhood"
df$Scale[df$Author =="R Mandel"  & df$Site == "Los Angeles"] <- "City"
df$Scale[df$Author =="R Mandel"  & df$Site == "Herdon, Virginia"] <- "City"
df$Scale[df$Author =="R Mandel"  & df$Site == "Wheaton, Maryland"] <- "City"
df$Scale[df$Author =="R Mandel"  & df$Site == "Tatary, Poland"] <- "City"
df$Scale[df$Author =="R Mandel"  & df$Site == "Shuilin, Sichuan, China"] <- "Village"
df$Scale[df$Author =="R Mandel"  & df$Site == "Dorli, Maharashtra, India"] <- "Village"
df$Scale[df$Author =="R Mandel"  & df$Site == "Biswanath, Sylhet, Bangladesh"] <- "Village"
df$Scale[df$Author =="R Mandel"  & df$Site == "Paris"] <- "City"
df$Scale[df$Author =="R Mandel"  & df$Site == "London"] <- "City"
df$Scale[df$Author =="R Mandel"  & df$Site == "Toronto"] <- "City"
df$Scale[df$Author =="R Mandel"  & df$Site == "Chicago"] <- "City"
df$Scale[df$Author =="R Mandel"  & df$Site == "Istanbul"] <- "City"
df$Scale[df$Author =="R Mandel"  & df$Site == "Emamzadeh 'Isa, Tehran"] <- "Neighborhood"
df$Scale[df$Author =="R Mandel"  & df$Site == "Petare, Caracas"] <- "Neighborhood"
df$Scale[df$Author =="R Mandel"  & df$Site == "Mulund, Mumbai"] <- "Neighborhood"
df$Scale[df$Author =="R Mandel"  & df$Site == "Les Pyramides, Evry, France"] <- "City"
df$Scale[df$Author =="R Mandel"  & df$Site == "Kreuzberg, Berlin"] <- "Neighborhood"
df$Scale[df$Author =="R Mandel"  & df$Site == "Parla, Spain"] <- "City"
df$Scale[df$Author =="R Mandel"  & df$Site == "Jardim Angela, São Paulo, Brazil"] <- "City"
df$Scale[df$Author =="R Mandel"  & df$Site == "Mumbai"] <- "City"
df$Scale[df$Author =="R Mandel"  & df$Site == "Slotervaart, Amsterdam"] <- "Neighborhood"
df$Scale[df$Author =="R Mandel"  & df$Site == "Karail, Dhaka, Bangladesh"] <- "Neighborhood"
df$Scale[df$Author =="R Mandel"  & df$Site == "Thorncliffer Park, Toronto"] <- "Neighborhood"

df$Scale[df$Author =="MF Orellana"  & df$Site == "Pseudonym (Madison), Los Angeles"] <- "Neighborhood"
df$Scale[df$Author =="MF Orellana"  & df$Site == "Pseudonym (Regan), Chicago"] <- "Neighborhood"
df$Scale[df$Author =="MF Orellana"  & df$Site == "Pseudonym (Engleville)"] <- "Suburb"

df$Scale[df$Author =="NN Sorenson"  & df$Site == "Rural Villages in the Cibao Region"] <- "Village"
df$Scale[df$Author =="NN Sorenson"  & df$Site == "Santiago, Dominican Republic"] <- "City"
df$Scale[df$Author =="NN Sorenson"  & df$Site == "Santo Domingo, Dominican Republic"] <- "City"
df$Scale[df$Author =="NN Sorenson"  & df$Site == "Puerto Plata, Dominican Republic"] <- "City"
df$Scale[df$Author =="NN Sorenson"  & df$Site == "La Romana, Dominican Republic"] <- "City"
df$Scale[df$Author =="NN Sorenson"  & df$Site == "Border areas close to Haiti"] <- "Region"
df$Scale[df$Author =="NN Sorenson"  & df$Site == "4 Neighborhoods in New York City"] <- "Neighborhood"
df$Scale[df$Author =="NN Sorenson"  & df$Site == "Yonkers"] <- "City"
df$Scale[df$Author =="NN Sorenson"  & df$Site == "Madrid"] <- "City"

##CITY/REGION

df$Scale[df$Author =="D Griffith"  & df$Site == "Marshalltown, Iowa"] <- "Town"
df$Scale[df$Author =="D Griffith"  & df$Site == "Southwest Minnesota"] <- "Region"
df$Scale[df$Author =="D Griffith"  & df$Site == "Adel, Georgia"] <- "Town"
df$Scale[df$Author =="D Griffith"  & df$Site == "Southeastern North Carolina"] <- "Region"

df$Scale[df$Author =="S Holmes"  & df$Site == "Skagit Valley, Washington"] <- "Region"
df$Scale[df$Author =="S Holmes"  & df$Site == "Madera, California"] <- "City"
df$Scale[df$Author =="S Holmes"  & df$Site == "San Miguel, Mexico"] <- "Village"
df$Scale[df$Author =="S Holmes"  & df$Site == "San Miguel, Mexico"] <- "Village"

##CITY/STATES

df$Scale[df$Author =="D Bacon"  & df$Site == "Emeryville, California"] <- "City"
df$Scale[df$Author =="D Bacon"  & df$Site == "Tarheel, North Carolina"] <- "City"
df$Scale[df$Author =="D Bacon"  & df$Site == "Oaxaca"] <- "State"
df$Scale[df$Author =="D Bacon"  & df$Site == "Cananea, Sonora"] <- "City"
df$Scale[df$Author =="D Bacon"  & df$Site == "Los Angeles"] <- "City"
df$Scale[df$Author =="D Bacon"  & df$Site == "Mississippi"] <- "State"
df$Scale[df$Author =="D Bacon"  & df$Site == "New Orleans"] <- "City"
df$Scale[df$Author =="D Bacon"  & df$Site == "Redwood City, California"] <- "City"

##ORGANIZATION/ NATIONAL ORGANIZATION
df$Scale[df$Author =="K Kempadoo"  & df$Site == "Hong Kong"] <- "Organization"
df$Scale[df$Author =="K Kempadoo"  & df$Site == "Calcutta"] <- "Organization"
df$Scale[df$Author =="K Kempadoo"  & df$Site == "National Organization (Thailand)"] <- "National Organization"

#CATALONIA CATEGORIZED AS SETTLEMENT, SHOULD BE REGION here

df$Scale[df$Author =="Østergaard-Nielsen" & df$Site == "Catalonia, Spain"] <- "Region"

#SOUTH TYROL CATEGORIZED AS INSTITUION, SHOULD THESE ARE REGION INSTITUTIONS. CODED AS REGIONS

df$Scale[df$Author =="Wisthaler" & df$Site == "South Tyrol"] <- "Region"

##Create Lookup table
lut <- c(
  ##Organization
  "Organization" = "Organization", "Institution" = "Organization", "Workplace" = "Organization",
  "Governmental Office" = "Organization",  "School" = "Organization", "Newspaper" = "Organization",
  "Faith Spaces" = "Organization",  "Business" = "Organization",  "Association" = "Organization",
  "Places of Worship" = "Organization", "Italian Local Authority" = "Organization", "Informal Support Group" = "Organization",
  "Church" = "Organization", "Radio Station" = "Organization", "Public Health Departments" = "Organization",
  "Mosque" = "Organization",  "Embassy" = "Organization", "Football Club" = "Organization", "League" = "Organization",
  "Ritual" = "Organization",  "Cricket Club" = "Organization", "Dance Club" = "Organization", "Social Club" = "Organization", "Organizations" = "Organization", "Association" = "Organization",
  #Neighborhood
  "Neighborhood" = "Neighborhood", "Housing Estates" = "Neighborhood", "Ethnic Enclave" = "Neighborhood",
  "Borough" = "Neighborhood",  "Street" = "Neighborhood", "City district" = "Neighborhood",
  "Area" = "Neighborhood", #These two studies are talking about inner-city London
  "Statistical Area Level 2 (SA2)" = "Neighborhood", # Very similar to census tracts so coded as neighborhood
  "Neighborhood (SAMS)" = "Neighborhood",  "Neighborhood/Barrios" = "Neighborhood", "Census tracts" = "Neighborhood",
  ##Settlement
  "City" = "Settlement", "Locality" = "Settlement", "Metropolitan Area" = "Settlement", "Town" = "Settlement",
  "City;transnational" = "Settlement",  "Municipality" = "Settlement",  "MSAs" = "Settlement",  "Village" = "Settlement",
  "County" = "Settlement", "Township" = "Settlement",  "Suburb" = "Settlement",
  "NUTS3" = "Settlement", #NUTS3 are about the size of counties, so coded as settlements.
  "Regional Town" = "Settlement", #This is a small town
  "City-region" = "Settlement", #This is a hard call since this Brussels is both a city and a region. For consistency this is coded as a Settlement.
  "Urban Region" = "Settlement", #Settlement level in Estonia
  "Island" = "Settlement", #Nevis is so small that it is hard to think of it as a region.
  "5 Percent PUMS" = "Settlement", #This is a study of metro areas, so coded as settlement
  "Border Route" = "Settlement", ## This is two settlements on the U.S Mexico border
  "City-state" = "Settlement", #This is a hard call since Luxembourg and Singapore are cities and nations. For consistency this coded as a city.
  "CUMS Spatial Unit" = "Settlement", #Best approximation here is settlement. This is their terminology as well
  ##Region
  "Region" = "Region",  "Administrative Region" = "Region",  "State" = "Region",  "C-PUMA" = "Region",
  "Province" = "Region", "Canton" = "Region", "Valley" = "Region", "Prefecture" = "Region",
  "Border" = "Region", "NUTS2" = "Region",
  "Australian Electoral Divisions" = "Region", #There is huge spatial variation here but region seems like the most appropriate scale
  "Euro-African Borderlands" = "Region",
  ##Multi-level & Nation-State
  "Nation-State" = "Nation-State", "All NUTS Regions" = "Multi-level") #This study is explicitly multi-level

## Adding the Scale1 Column
df$Scale1 <- lut[df$Scale]

##remove those studies conducted at the national level and the single study that is multi-level
df %>% filter(Scale1 != "Nation-State", Scale1 != "Multi-level", Scale1 != "National Organization") -> df1

```

RECODING SITE OBSERVATIONS

What I am trying to determine here is whether there are certain places that are getting studied over and over again. This will require two levels of coding. The key to the first round (Site1) is that sites that have been coded as being in the same scale in the above section should share the same name. As an example, since counties and cities and metropolitan areas have been coded as settlements above, these places should share a name. An example would be Los Angeles. In this first round of coding studies in LA County, LA City and LA Metro will be converted into Los Angeles. In contrast, Southern California which is coded as a region will not be recoded as Los Angeles. Coding in such a manner will aggregate places that are roughly analogous while being consistent across scales. 

A second round of coding (Site2) will convert all subnational units into the settlement container to see if our understanding of neighborhoods is situated in particular cities. This within city variation, which controls for urban-level features, is useful to the degree that variation emerges at features at a smaller scale. However, if we only ever study neighborhoods in certain cities, the all our assumptions are neighborhoods might be assuming features of cities that are rare (like good public transportation). Thus, we would be looking at rare types of neighborhoods in the world. My instinct is that coding like this will further  demonstrate the truncated studies problem. 

All in all, this type of coding should allow me to produce three tables. The first will be a straight study frequency table that shows which sites are most commonly studied. A second table will filter at only the settlement scale and show which settlements are most commonly studied at the "urban/rural-level". Finally, a third table will aggregate subnational studies into the settlement scale to see if including these studies makes some cities an even larger focus of academic attention. 

```{r recode Site1, include=FALSE}

df1$Site1 <- df1$Site

##Organizations
##For this scale, if organization is not identified collapsed into a generic organization in that city category.

df1$Site1[df1$Site1 == "Four Minority-Owned Businesses in London"] <- "Organizations in London"
df1$Site1[df1$Site1 == "London" & df1$Scale1 == "Organization"] <- "Organizations in London"
df1$Site1[df1$Site1 == "Secondary State School in South London"] <- "Organizations in London"
df1$Site1[df1$Site1 == "Six London Based Nigerian Organizations"] <- "Organizations in London"
df1$Site1[df1$Site1 == "Three churches in London"] <- "Organizations in London"

df1$Site1[df1$Site1 == "Los Angeles" & df1$Scale1 == "Organization"] <- "Organizations in Los Angeles"
df1$Site1[df1$Site1 == "2 Organizations in Los Angeles" & df1$Scale1 == "Organization"] <- "Organizations in Los Angeles"
df1$Site1[df1$Site1 == "2 Workplaces in Los Angeles" & df1$Scale1 == "Organization"] <- "Organizations in Los Angeles"

df1$Site1[df1$Site1 == "State and migrant organizations in Madrid" & df1$Scale1 == "Organization"] <- "Organizations in Madrid"
df1$Site1[df1$Site1 == "Madrid" & df1$Scale1 == "Organization"] <- "Organizations in Madrid"

df1$Site1[df1$Site1 == "Milan" & df1$Scale1 == "Organization"] <- "Organizations in Milan"
df1$Site1[df1$Site1 == "School in Milan" & df1$Scale1 == "Organization"] <- "Organizations in Milan"

df1$Site1[df1$Site1 == "New York City" & df1$Scale1 == "Organization"] <- "Organizations in New York City"
df1$Site1[df1$Site1 == "New York" & df1$Scale1 == "Organization"] <- "Organizations in New York City"

df1$Site1[df1$Site1 == "2 urban spaces in Lyon" & df1$Scale1 == "Organization"] <- "Organizations in Lyon"

df1$Site1[df1$Site1 == "47 Local Authorities in Lombardy" & df1$Scale1 == "Organization"] <- "Organizations in Lombardy"

df1$Site1[df1$Site1 == "64 Rural Development Associations in 16 provinces" & df1$Scale1 == "Organization"] <- "Organizations in Morocco"

df1$Site1[df1$Site1 == "Immigrant Association" & df1$Nation == "Norway"] <- "Organizations in Counties in Norway"

##Neighborhoods
##For this scale, if neighborhood is not identified collapsed into a generic neighborhood in that city category. An example of this is 3 neighborhoods in Berlin is turned into Berlin neighborhoods. 

df1$Site1[df1$Site1 == "3 Neighborhoods in Berlin"] <- "Berlin Neighborhoods"
df1$Site1[df1$Site1 == "Berlin" & df1$Scale1 == "Neighborhood"] <- "Berlin Neighborhoods"

df1$Site1[df1$Site1 == "Census tracts in Toronto"] <- "Toronto Neighborhoods"
df1$Site1[df1$Site1 == "Toronto" & df1$Scale1 == "Neighborhood"] <- "Toronto Neighborhoods"

df1$Site1[df1$Site1 == "Census data" & df1$Scale1 == "Neighborhood"] <- "US Neighborhoods"

df1$Site1[df1$Site1 == "3 Housing Estates in Norwich, England" & df1$Scale1 == "Neighborhood"] <- "Norwich Neighborhoods"
df1$Site1[df1$Site1 == "37 districts in Duisburg" & df1$Scale1 == "Neighborhood"] <- "Duisburg Neighborhoods"

df1$Site1[df1$Site1 == "2 Neighborhoods in London"] <- "London Neighborhoods"
df1$Site1[df1$Site1 == "2 Boroughs in London"] <- "London Neighborhoods"
df1$Site1[df1$Site1 == "2 Boroughs in London"] <- "3 Communities in London"

df1$Site1[df1$Site1 == "4 Neighborhoods in Los Angeles"] <- "Los Angeles Neighborhoods"
df1$Site1[df1$Site1 == "3 Neighborhoods in Los Angeles"] <- "Los Angeles Neighborhoods"

##Settlement

df1$Site1[df1$Site1 == "5 Counties in Los Angeles metropolitan area" & df1$Scale1 == "Settlement"] <- "Los Angeles"
df1$Site1[df1$Site1 == "Anaheim, Los Angeles" & df1$Scale1 == "Settlement"] <- "Los Angeles"
df1$Site1[df1$Site1 == "Greater Los Angeles Area" & df1$Scale1 == "Settlement"] <- "Los Angeles"
df1$Site1[df1$Site1 == "Inglewood, Los Angeles" & df1$Scale1 == "Settlement"] <- "Los Angeles"
df1$Site1[df1$Site1 == "Los Angeles County" & df1$Scale1 == "Settlement"] <- "Los Angeles"
df1$Site1[df1$Site1 == "6 Cities in Los Angeles County" & df1$Scale1 == "Settlement"] <- "Los Angeles"

df1$Site1[df1$Site1 == "Greater Toronto Area" & df1$Scale1 == "Settlement"] <- "Toronto"
df1$Site1[df1$Site1 == "Toronto Metropolitan Area" & df1$Scale1 == "Settlement"] <- "Toronto"

df1$Site1[df1$Site1 == "District of Lisbon" & df1$Scale1 == "Settlement"] <- "Lisbon"
df1$Site1[df1$Site1 == "Lisbon Metropolitan Area" & df1$Scale1 == "Settlement"] <- "Lisbon"
df1$Site1[df1$Site1 == "Lisbon, Portugal" & df1$Scale1 == "Settlement"] <- "Lisbon"
df1$Site1[df1$Site1 == "Greater Libson Area" & df1$Scale1 == "Settlement"] <- "Lisbon"
df1$Site1[df1$Site1 == "Greater Libson Area" & df1$Scale1 == "Settlement"] <- "Lisbon"

df1$Site1[df1$Site1 == "Boston, Massachusetts and the seven counties of the Boston-Worcester-Manchester" & df1$Scale1 == "Settlement"] <- "Boston"
df1$Site1[df1$Site1 == "Boston/Providence" & df1$Scale1 == "Settlement"] <- "Boston"

df1$Site1[df1$Site1 == "Madrid (data not included in article)" & df1$Scale1 == "Settlement"] <- "Madrid"

df1$Site1[df1$Site1 == "New York" & df1$Scale1 == "Settlement"] <- "New York City"
df1$Site1[df1$Site1 == "New York /New Jersey" & df1$Scale1 == "Settlement"] <- "New York City"

df1$Site1[df1$Site1 == "Vienna, Austria" & df1$Scale1 == "Settlement"] <- "Vienna"

df1$Site1[df1$Site1 == "Greater Barcelona Metropolitain Area" & df1$Scale1 == "Settlement"] <- "Barcelona"
df1$Site1[df1$Site1 == "3 municipalities in Greater Barcelona" & df1$Scale1 == "Settlement"] <- "Barcelona"

df1$Site1[df1$Site1 == "Paris metropolitan area" & df1$Scale1 == "Settlement"] <- "Paris"

df1$Site1[df1$Site1 == "Atlanta" & df1$Scale1 == "Settlement"] <- "Atlanta, GA"

df1$Site1[df1$Site1 == "Brussles" & df1$Scale1 == "Settlement"] <- "Brussels"

df1$Site1[df1$Site1 == "Charlotte" & df1$Scale1 == "Settlement"] <- "Charlotte, NC"

df1$Site1[df1$Site1 == "Cracow and its surroundings" & df1$Scale1 == "Settlement"] <- "Krakow"
df1$Site1[df1$Site1 == "Cracow" & df1$Scale1 == "Settlement"] <- "Krakow"

df1$Site1[df1$Site1 == "Fort Lauderdale Metropolitan Area" & df1$Scale1 == "Settlement"] <- "Fort Lauderdale"

df1$Site1[df1$Site1 == "German Speaking Community in Ottowa" & df1$Scale1 == "Settlement"] <- "Ottawa"

df1$Site1[df1$Site1 == "Greater Stockholm" & df1$Scale1 == "Settlement"] <- "Stockholm"

df1$Site1[df1$Site1 == "Hargeisa, Somaliland" & df1$Scale1 == "Settlement"] <- "Hargeisa, Somalia"

df1$Site1[df1$Site1 == "Lund" & df1$Scale1 == "Settlement"] <- "Lund, Sweden"

df1$Site1[df1$Site1 == "Manchester (or Surrounding Areas)" & df1$Scale1 == "Settlement"] <- "Manchester"

df1$Site1[df1$Site1 == "Houston Metropolitan Area" & df1$Scale1 == "Settlement"] <- "Houston"

df1$Site1[df1$Site1 == "Kampala" & df1$Scale1 == "Settlement"] <- "Kampala, Uganda"

df1$Site1[df1$Site1 == "Kampala" & df1$Scale1 == "Settlement"] <- "Kampala, Uganda"

df1$Site1[df1$Site1 == "Ealing, London" & df1$Scale1 == "Settlement"] <- "London"

df1$Site1[df1$Site1 == "Ealing, London" & df1$Scale1 == "Settlement"] <- "London"

##Coded this instead of Fort Lauderdal because the text most often refers to Miami. 
df1$Site1[df1$Site1 == "Miami/Fort Lauderdale" & df1$Scale1 == "Settlement"] <- "Miami"

df1$Site1[df1$Site1 == "Durham metropolitan area" & df1$Scale1 == "Settlement"] <- "Raleigh-Durham"

df1$Site1[df1$Site1 == "San Bernandino County" & df1$Scale1 == "Settlement"] <- "San Bernardino"
df1$Site1[df1$Site1 == "San Bernardino County, California" & df1$Scale1 == "Settlement"] <- "San Bernardino"

df1$Site1[df1$Site1 == "San Diego County" & df1$Scale1 == "Settlement"] <- "San Diego"
df1$Site1[df1$Site1 == "San Diego Metropolitan Area" & df1$Scale1 == "Settlement"] <- "San Diego"

df1$Site1[df1$Site1 == "Suburban Housing Areas 25-30 km North of Perth" & df1$Scale1 == "Settlement"] <- "Perth"

df1$Site1[df1$Site1 == "Twin Cities" & df1$Scale1 == "Settlement"] <- "St.Paul"

df1$Site1[df1$Site1 == "Uppsala" & df1$Scale1 == "Settlement"] <- "Uppsala, Sweden"

df1$Site1[df1$Site1 == "Utrecht" & df1$Scale1 == "Settlement"] <- "Utrecht, Netherlands"

df1$Site1[df1$Site1 == "Warsaw and its surroundings" & df1$Scale1 == "Settlement"] <- "Warsaw"

df1$Site1[df1$Site1 == "Washington DC" & df1$Scale1 == "Settlement"] <- "Washington, DC"


##These are a little odd to code because these categories represent many geographically unspecific settlement areas. To make these completely consistent, with the format that has been used, I would have to extract the specific settlement from every study. This is very difficult since these studies don't usually provide a list of settlement sites. Instead, these are converted to generic geography-scale categories which are meant to indicate that there is likely overlap in settlements in these categories. 

df1$Site1[df1$Site1 == "100 Largest Metro Areas" & df1$Scale1 == "Settlement"] <- "American Cities"
df1$Site1[df1$Site1 == "3 Cities in Alabama" & df1$Scale1 == "Settlement"] <- "American Cities"
df1$Site1[df1$Site1 == "297 Metropolitan areas sampled by the U.S. Census" & df1$Scale1 == "Settlement"] <- "American Cities"
df1$Site1[df1$Site1 == "66 Cities with Populations over 25,000" & df1$Scale1 == "Settlement"] <- "American Cities"
df1$Site1[df1$Site1 == "108 Metropolitan Areas" & df1$Scale1 == "Settlement"] <- "American Cities"
df1$Site1[df1$Site1 == "60 Medium sized metropolitan areas" & df1$Scale1 == "Settlement"] <- "American Cities"
df1$Site1[df1$Site1 == "Counties in 15 States" & df1$Scale1 == "Settlement"] <- "American Cities"
df1$Site1[df1$Site1 == "All MSA" & df1$Scale1 == "Settlement"] <- "American Cities"
df1$Site1[df1$Site1 == "ACS and CPS Data" & df1$Scale1 == "Settlement"] <- "American Cities"
df1$Site1[df1$Site1 == "CUMS" & df1$Scale1 == "Settlement"] <- "American Cities"
df1$Site1[df1$Site1 == "Other" & df1$Scale1 == "Settlement"] <- "American Cities"
df1$Site1[df1$Site1 == "PUMS" & df1$Scale1 == "Settlement"] <- "American Cities"

df1$Site1[df1$Site1 == "All Counties" & df1$Scale1 == "Settlement"] <- "British Counties"
df1$Site1[df1$Site1 == "Surrounding towns of the West Midlands" & df1$Scale1 == "Settlement"] <- "British Counties"

df1$Site1[df1$Site1 == "20 largest cities in germany" & df1$Scale1 == "Settlement"] <- "German Cities"
df1$Site1[df1$Site1 == "NUTS3 State level" & df1$Scale1 == "Settlement"] <- "German Cities"

df1$Site1[df1$Site1 == "15 Urban regions" & df1$Scale1 == "Settlement"] <- "Estonian Cities" #Call this populational

df1$Site1[df1$Site1 == "13 Dutch Cities where the most immigrants live" & df1$Scale1 == "Settlement"] <- "Dutch Municipalities"
df1$Site1[df1$Site1 == "38 Municipalities in the Netherlands" & df1$Scale1 == "Settlement"] <- "Dutch Municipalities"
df1$Site1[df1$Site1 == "50 Muncipalities" & df1$Scale1 == "Settlement"] <- "Dutch Municipalities"
df1$Site1[df1$Site1 == "Six rural towns" & df1$Scale1 == "Settlement"] <- "Dutch Municipalities"

df1$Site1[df1$Site1 == "19 Municipalities in Brussels" & df1$Scale1 == "Settlement"] <- "Belgian Municipalities"
df1$Site1[df1$Site1 == "129 Municipalities in Wallonia" & df1$Scale1 == "Settlement"] <- "Belgian Municipalities"
df1$Site1[df1$Site1 == "53 Municipalities in Flanders" & df1$Scale1 == "Settlement"] <- "Belgian Municipalities"

df1$Site1[df1$Site1 == "16 athenian municipalities in which Roma pop concentrated" & df1$Scale1 == "Settlement"] <- "Greek Cities"

df1$Site1[df1$Site1 == "872 Municipalities with most Romanians and British immigrants" & df1$Scale1 == "Settlement"] <- "Spanish Municipalities"
df1$Site1[df1$Site1 == "Small Municipality in Spain" & df1$Scale1 == "Settlement"] <- "Spanish Municipalities"

df1$Site1[df1$Site1 == "1161 Municipalities" & df1$Scale1 == "Settlement"] <- "Swiss Municipalities"
df1$Site1[df1$Site1 == "14 Municipialities in Switzerland" & df1$Scale1 == "Settlement"] <- "Swiss Municipalities"

df1$Site1[df1$Site1 == "2456 Mexican Municipalities" & df1$Scale1 == "Settlement"] <- "Mexican Municipalities"

df1$Site1[df1$Site1 == "28 Cities in Five Autonomous Republics of the Russian Federation, in Ukraine, and in Crimea" & df1$Scale1 == "Settlement"] <- "Cities in the Former Soviet Union"

df1$Site1[df1$Site1 == "44 Municipalities" & df1$Scale1 == "Settlement"] <- "Danish Municipalities"
df1$Site1[df1$Site1 == "Two Rural Municipalities" & df1$Scale1 == "Settlement"] <- "Danish Municipalities"

df1$Site1[df1$Site1 == "Two Rural communities in lower Silensia" & df1$Scale1 == "Settlement"] <- "Polish Municipalities"

df1$Site1[df1$Site1 == "Others" & df1$Scale1 == "Settlement"] <- "Taiwanese Cities"

df1$Site1[df1$Site1 == "Araucani´a"] <- "Municipalities in Araucanía Region in Chile"

df1$Site1[df1$Site1 == "Nine Smaller Cities"] <- "Dutch Cities"

##Regions

df1$Site1[df1$Site1 == "22 Monolingual Cantons" & df1$Scale1 == "Region"] <- "Swiss Canton"
df1$Site1[df1$Site1 == "26 Cantons" & df1$Scale1 == "Region"] <- "Swiss Canton"

df1$Site1[df1$Site1 == "All States Except for Alaska, Maine, Montana, North Dakota, South Dakota, Vermont, West Virginia, and Wyoming" & df1$Scale1 == "Region"] <- "American States"
df1$Site1[df1$Site1 == "CPS Data for all 50 States and the District of Columbia" & df1$Scale1 == "Region"] <- "American States"
df1$Site1[df1$Site1 == "States" & df1$Scale1 == "Region"] <- "American States"
df1$Site1[df1$Site1 == "All States" & df1$Scale1 == "Region"] <- "American States"

df1$Site1[df1$Site1 == "all consolidated PUMAS in the US" & df1$Scale1 == "Region"] <- "C-PUMAS"
df1$Site1[df1$Site1 == "543 c-PUMAs" & df1$Scale1 == "Region"] <- "C-PUMAS"
df1$Site1[df1$Site1 == "PUMAs" & df1$Scale1 == "Region"] <- "C-PUMAS"

df1$Site1[df1$Site1 == "Lambordia, Italy" & df1$Scale1 == "Region"] <- "Lombardy, Italy"

df1$Site1[df1$Site1 == "Catalonia" & df1$Scale1 == "Region"] <- "Catalonia, Spain"

df1$Site1[df1$Site1 == "Nordrhein" & df1$Scale1 == "Region"] <- "North Rhine-Westphalia, Germany"

#Clarifying Regions frm Settlements
df1$Site1[df1$Site1 == "Barcelona" & df1$Scale1 == "Region"] <- "Barcelona Region"
df1$Site1[df1$Site1 == "Geneva" & df1$Scale1 == "Region"] <- "Geneva Region"
df1$Site1[df1$Site1 == "Amsterdam" & df1$Scale1 == "Region"] <- "Amsterdam Region"
df1$Site1[df1$Site1 == "Hamburg" & df1$Scale1 == "Region"] <- "Hamburg Region"
df1$Site1[df1$Site1 == "Kabul" & df1$Scale1 == "Region"] <- "Kabul Region"
df1$Site1[df1$Site1 == "Flanders" & df1$Scale1 == "Region"] <- "Flanders Region"
df1$Site1[df1$Site1 == "Madrid" & df1$Scale1 == "Region"] <- "Madrid Region"
df1$Site1[df1$Site1 == "Milan" & df1$Scale1 == "Region"] <- "Milan Region"
df1$Site1[df1$Site1 == "Novosibirsk" & df1$Scale1 == "Region"] <- "Novosibirsk Region"
df1$Site1[df1$Site1 == "Paris" & df1$Scale1 == "Region"] <- "Paris Region"
df1$Site1[df1$Site1 == "West-Berlin" & df1$Scale1 == "Region"] <- "West-Berlin Region"

##Fixing some spelling and syntax errors
df1$Site1[df1$Site1 == "Washington D.C. Metropolitan Area" & df1$Scale1 == "Settlement"] <- "Washington, DC"
df1$Site1[df1$Site1 == "Guangzhou, China" & df1$Scale1 == "Settlement"] <- "Guangzhou"
df1$Site1[df1$Site1 == "Marseilles" & df1$Scale1 == "Settlement"] <- "Marseille"
df1$Site1[df1$Site1 == "Mumbia" & df1$Scale1 == "Settlement"] <- "Mumbai"
df1$Site1[df1$Site1 == "Chatham County, NC"] <- "Chatham County, North Carolina"
df1$Site1[df1$Site1 == "Person County, NC"] <- "Person County, North Carolina"
df1$Site1[df1$Site1 == "Orange County"] <- "Orange County, California"
df1$Site1[df1$Site1 == "Riverside County"] <- "Riverside County, California"
df1$Site1[df1$Site1 == "San Bernardino County"] <- "San Bernardino County, California"
df1$Site1[df1$Site1 == "Ventura County"] <- "Ventura County, California"


```

RECODE SUBNATIONAL UNITS INTO SETTLEMENTS

```{r recode Site2, include=FALSE}

df1 %>% mutate(Site2 = case_when(grepl("Glasgow", Site1) ~ "Glasgow",
                                 grepl("Melbourne", Site1) ~ "Melbourne",
                                 grepl("Sydney", Site1) ~ "Sydney",
                                 grepl("Tallinn", Site1) ~ "Tallinn",
                                 grepl("Leeds", Site1) ~ "Leeds",
                                 grepl("Stockholm", Site1) ~ "Stockholm",
                                 grepl("Birmingham", Site1) ~ "Birmingham",
                                 grepl("London", Site1) & Scale1 == "Neighborhood" ~ "London",
                                 grepl("London", Site1) & Scale1 == "Organization" ~ "London",
                                 !grepl("Ontario", Site1) & grepl("London", Site1) & Scale1 == "Settlement" ~ "London",
                                 grepl("Montreal", Site1) ~ "Montreal",
                                 grepl("Vancouver", Site1) ~ "Vancouver",
                                 grepl("Los Angeles", Site1) ~ "Los Angeles",
                                 grepl("Gothenburg", Site1) ~ "Gothenburg",
                                 grepl("Johannesburg", Site1) ~ "Johannesburg",
                                 grepl("Toronto", Site1) ~ "Toronto",
                                 grepl("Malm", Site1) ~ "Malmö",
                                 grepl("Thessaloniki", Site1) ~ "Thessaloniki",
                                 grepl("Chicago", Site1) ~ "Chicago",
                                 grepl("Santiago", Site1) & Nation == "Chile" ~ "Santiago",
                                 grepl("Stuttgart", Site1) ~ "Stuttgart",
                                 grepl("Beijing", Site1) ~ "Beijing",
                                 grepl("Amsterdam", Site1) & Scale1 != "Region" ~ "Amsterdam",
                                 grepl("Lisbon", Site1) & Scale1 != "Region" ~ "Lisbon",
                                 grepl("Berlin", Site1) & Scale1 != "Region" ~ "Berlin",
                                 grepl("Antwerp", Site1) ~ "Antwerp",
                                 grepl("Hong Kong", Site1) ~ "Hong Kong",
                                 grepl("Singapore", Site1) ~ "Singapore",
                                 grepl("Edinburgh", Site1) ~ "Edinburgh",
                                 grepl("Dublin", Site1) ~ "Dublin, Ireland",
                                 grepl("Coventry", Site1) ~ "Coventry",
                                 grepl("Tokyo", Site1) ~ "Tokyo",
                                 grepl("Aarhus", Site1) ~ "Aarhus",
                                 grepl("Moscow", Site1) ~ "Moscow",
                                 grepl("Novosibirsk", Site1) ~ "Novosibirsk",
                                 grepl("Oslo", Site1) ~ "Oslo", 
                                 grepl("Shanghai", Site1) ~ "Shanghai",
                                 grepl("Rome", Site1) ~ "Rome",
                                 grepl("San Francisco", Site1) ~ "San Francisco",
                                 grepl("Portland, Maine", Site1) ~ "Portland, Maine",
                                 grepl("Copenhagen", Site1) ~ "Copenhagen",
                                 grepl("Lyon", Site1) ~ "Lyon",
                                 grepl("Helsinki", Site1) ~ "Helsinki",
                                 grepl("Vienna", Site1) ~ "Vienna",
                                 grepl("Washington, DC", Site1) ~ "Washington, DC",
                                 grepl("Zurich", Site1) ~ "Zurich", 
                                 grepl("Boston", Site1) ~ "Boston",
                                 grepl("Milan", Site1) & Scale1 != "Region" ~ "Milan",
                                 !grepl("Province", Site1) & grepl("Barcelona", Site1) & Scale1 != "Region" ~ "Barcelona",
                                 grepl("Madrid", Site1) & Scale1 != "Region" ~ "Madrid",
                                 grepl("New York City", Site1) ~ "New York City",
                                 grepl("Nairobi", Site1) ~ "Nairobi",
                                 grepl("Athens", Site1) & Scale1 != "Region" ~ "Athens",
                                 grepl("Istanbul", Site1) & Scale1 != "Region" ~ "Istanbul",
                                 grepl("Koder Kamza", Site1) & Scale1 != "Region" ~ "Tirana",
                                 grepl("Paris", Site1) & Scale1 != "Region" ~ "Paris",
                                 grepl("Detroit", Site1) ~ "Detroit",
                                 !grepl("Province", Site1) & grepl("Malaga", Site1) ~ "Malaga",
                                 grepl("Ibadan", Site1) ~ "Ibadan",
                                 grepl("Manila", Site1) ~ "Manila",
                                 grepl("Lima", Site1) & Nation == "Peru" ~ "Lima",
                                 grepl("Cape Town", Site1) ~ "Cape Town",
                                 grepl("Hamburg", Site1) ~ "Hamburg",
                                 grepl("Shenzhen", Site1) ~ "Shenzhen",
                                 grepl("Wigan", Site1) ~ "Wigan",
                                 grepl("Graz", Site1) ~ "Graz",
                                 grepl("Nurenberg", Site1) ~ "Nurenberg",
                                 grepl("Sintra District", Site1) ~ "Sintra District",
                                 grepl("Korhogo", Site1) ~ "Korhogo, Ivory Coast",
                                 grepl("Duisburg", Site1) ~ "Duisburg",
                                 TRUE ~ Site1)) -> df1

##I used this to check if aggregatings are correct for Site2
df1 %>% 
  filter(Scale1 != "Region") %>% 
  group_by(Scale1, Site1, Site2) %>% 
  summarize(N = n()) %>% dplyr::arrange(Scale1, N, Site1) -> rctable


df1 %>% filter(Scale1 == "Settlement") -> df1_settle

```

ADDING CAPITAL CITY DATA

```{r cleaning population estimates 1, include=FALSE}

df_pop %>% select(-Nation) -> df_pop

df2 <- left_join(df1, df_pop, by = "Site2")

##create both capital variable

df2$National_Capital[is.na(df2$National_Capital)] <- 0
df2$Provincial_Capital[is.na(df2$Provincial_Capital)] <- 0
df2$City_Size[is.na(df2$City_Size)] <- 9

df2 %>% mutate(Both_Capital = case_when(National_Capital == 1 ~ 1, 
                                        Provincial_Capital == 1 ~ 1,
                                        National_Capital == 0 | Provincial_Capital == 0 ~ 0,
                                        is.na(National_Capital) ~ 0,
                                        is.na(Provincial_Capital) ~ 0)) -> df2

df2 %>% 
  dplyr::mutate(large = case_when(City_Size == 1 ~ 1, TRUE ~ 0),
                second_large = case_when(City_Size == 2 ~ 1, TRUE ~ 0),
                third_large = case_when(City_Size == 3 ~ 1, TRUE ~ 0)) %>% 
  dplyr::mutate(largest3 = case_when(City_Size == 1 ~ 1,
                                     City_Size == 2 ~ 1,
                                     City_Size == 3 ~ 1,
                                     City_Size == 9 ~ 0,
                                     is.na(City_Size) ~ 0)) -> df2
  

df2 %>% ##filter(Scale1 == "Settlement") %>%
  select(ID, Year, Author, Journal, Incorporation, 
         Scale, Scale1,  
         Nation, N_Sites, 
         Site, Site1, Site2, 
         City_Size, National_Capital, Provincial_Capital, Both_Capital, 
         large, second_large, third_large, largest3,
         Notes, Coder) -> df2

```

```{r census information, include=FALSE}

#US 2010 Census
colnames(df_uscensus2010) <- df_uscensus2010[1, ] # the first row will be the header
df_uscensus2010 <- df_uscensus2010[-1, ] %>% clean_names()
#note 1.27.2021. Column name is misleading. This is the total population, not total population of Latinos.
df_uscensus2010 <- df_uscensus2010 %>% mutate(Nation = "United States of America",
                           pop_2010 = as.numeric(number_hispanic_or_latino_total_population)) %>% 
  select(Nation, geography, pop_2010) %>% 
  rename(Site2 = geography)

#US 2019 ACS
colnames(df_acs2019) <- df_acs2019[1, ] # the first row will be the header
df_acs2019 <- df_acs2019[-1, ] %>% clean_names()
df_acs2019 <- df_acs2019 %>% rename(geography = geographic_area_name, 
                                    fbtotal = estimate_total_foreign_born, 
                                    nativetotal = estimate_total_native, 
                                    total = estimate_total) %>% select(geography, fbtotal, nativetotal, total) %>% mutate(across(contains("total"), as.numeric))

```

```{r cleaning population estimates 2, include=FALSE}

df_unpop %>% select(`Country or area`, `Urban Agglomeration`, `2010`) %>%
  rename("Nation" = `Country or area`,
         "Site2" = `Urban Agglomeration`, 
         "pop_2010" = `2010`) %>% 
  mutate(pop_2010 = pop_2010 * 1000) ->  df_unpop

df_unpop$Site2[df_unpop$Site2 =="Los Angeles-Long Beach-Santa Ana"] <- "Los Angeles"
df_unpop$Site2[df_unpop$Site2 =="New York-Newark"] <- "New York City"
df_unpop$Site2[df_unpop$Site2 =="s-Gravenhage (The Hague)"] <- "The Hague"
df_unpop$Site2[df_unpop$Site2 =="San Francisco-Oakland"] <- "San Francisco"
df_unpop$Site2[df_unpop$Site2 =="Wien (Vienna)"] <- "Vienna"
df_unpop$Site2[df_unpop$Site2 =="Lisboa (Lisbon)"] <- "Lisbon"
df_unpop$Site2[df_unpop$Site2 =="Milano (Milan)"] <- "Milan"
df_unpop$Site2[df_unpop$Site2 =="Roma (Rome)"] <- "Rome"
df_unpop$Site2[df_unpop$Site2 =="Utrecht"] <- "Utrecht, Netherlands"
df_unpop$Site2[df_unpop$Site2 =="Washington, D.C."] <- "Washington, DC"
df_unpop$Site2[df_unpop$Site2 =="Antwerpen"] <- "Antwerp"
df_unpop$Site2[df_unpop$Site2 =="København (Copenhagen)"] <- "Copenhagen"
df_unpop$Site2[df_unpop$Site2 =="Bruxelles-Brussel"] <- "Brussels"
df_unpop$Site2[df_unpop$Site2 =="Dublin"] <- "Dublin, Ireland"
df_unpop$Site2[df_unpop$Site2 =="Moskva (Moscow)"] <- "Moscow"
df_unpop$Site2[df_unpop$Site2 =="Phoenix-Mesa"] <- "Phoenix"
df_unpop$Site2[df_unpop$Site2 =="Torino (Turin)"] <- "Turin"
df_unpop$Site2[df_unpop$Site2 =="Warszawa (Warsaw)"] <- "Warsaw"
df_unpop$Site2[df_unpop$Site2 =="Zürich (Zurich)"] <- "Zurich"
df_unpop$Site2[df_unpop$Site2 =="Athínai (Athens)"] <- "Athens"
df_unpop$Site2[df_unpop$Site2 =="Frankfurt am Main"] <- "Frankfurt"
df_unpop$Site2[df_unpop$Site2 =="Kampala"] <- "Kampala, Uganda"
df_unpop$Site2[df_unpop$Site2 =="Kraków (Cracow)"] <- "Krakow"
df_unpop$Site2[df_unpop$Site2 =="Delhi"] <- "New Delhi"
df_unpop$Site2[df_unpop$Site2 =="Southampton/Portsmouth (South Hampshire)"] <- "Southampton"
df_unpop$Site2[df_unpop$Site2 =="Atlanta"] <- "Atlanta, GA"
df_unpop$Site2[df_unpop$Site2 =="Kolkata (Calcutta)"] <- "Calcutta"
df_unpop$Site2[df_unpop$Site2 =="Charlotte"] <- "Charlotte, NC"
df_unpop$Site2[df_unpop$Site2 =="Coventry-Bedworth"] <- "Coventry"
df_unpop$Site2[df_unpop$Site2 =="Gent"] <- "Ghent"
df_unpop$Site2[df_unpop$Site2 =="Göteborg"] <- "Gothenburg"
df_unpop$Site2[df_unpop$Site2 =="Guangzhou, Guangdong"] <- "Guangzhou"
df_unpop$Site2[df_unpop$Site2 =="Hargeysa"] <- "Hargeisa, Somalia"
df_unpop$Site2[df_unpop$Nation =="Canada" & df_unpop$Site2 =="London"] <- "London, Ontario"
df_unpop$Site2[df_unpop$Site2 =="Ciudad de México (Mexico City)"] <- "Mexico City"
df_unpop$Site2[df_unpop$Site2 =="Montréal"] <- "Montreal"
df_unpop$Site2[df_unpop$Site2 =="Mumbai (Bombay)"] <- "Mumbai"
df_unpop$Site2[df_unpop$Site2 =="Ottawa-Gatineau"] <- "Ottawa"
df_unpop$Site2[df_unpop$Site2 =="Raleigh"] <- "Raleigh-Durham"
df_unpop$Site2[df_unpop$Site2 =="Riverside-San Bernardino"] <- "San Bernardino"
df_unpop$Site2[df_unpop$Site2 =="Minneapolis-St. Paul"] <- "St.Paul"
df_unpop$Site2[df_unpop$Site2 =="Krung Thep (Bangkok)"] <- "Bangkok"
df_unpop$Site2[df_unpop$Site2 =="Bogotá"] <- "Bogota"
df_unpop$Site2[df_unpop$Site2 =="Bologna"] <- "Bologna, Italy"
df_unpop$Site2[df_unpop$Site2 =="Christchurch"] <- "Christchurch, New Zealand"
df_unpop$Site2[df_unpop$Site2 =="Cochabamba"] <- "Cochabamba, Bolivia"
df_unpop$Site2[df_unpop$Site2 =="Dallas-Fort Worth"] <- "Dallas"
df_unpop$Site2[df_unpop$Site2 =="Dubayy (Dubai)"] <- "Dubai"
df_unpop$Site2[df_unpop$Site2 =="Durban (Ethekwini)"] <- "Durban"
df_unpop$Site2[df_unpop$Site2 =="Irbil (Erbil)"] <- "Erbil, Iraq"
df_unpop$Site2[df_unpop$Site2 =="Firenze (Florence)"] <- "Florence"
df_unpop$Site2[df_unpop$Site2 =="Genève"] <- "Geneva"
df_unpop$Site2[df_unpop$Site2 =="Genova (Genoa)"] <- "Genoa"
df_unpop$Site2[df_unpop$Site2 =="Hangzhou"] <- "Hangzhou, China"
df_unpop$Site2[df_unpop$Nation =="India" & df_unpop$Site2 =="Hyderabad"] <- "Hyderabad, India"
df_unpop$Site2[df_unpop$Site2 =="Kuala Lumpur"] <- "Kuala Lumpur, Malaysia"
df_unpop$Site2[df_unpop$Site2 =="Kumasi"] <- "Kumasi, Ghana"
df_unpop$Site2[df_unpop$Site2 =="Kinki M.M.A. (Osaka)"] <- "Kyoto" ##Kyoto is part of this MMA.
df_unpop$Site2[df_unpop$Site2 =="Liège"] <- "Liège, Belgium"
df_unpop$Site2[df_unpop$Site2 =="Lusaka"] <- "Lusaka, Zambia"
df_unpop$Site2[df_unpop$Site2 =="Marseille-Aix-en-Provence"] <- "Marseille"
df_unpop$Site2[df_unpop$Site2 =="Matamoros"] <- "Matamoros, Mexico"
df_unpop$Site2[df_unpop$Site2 =="McAllen"] <- "McAllen, Texas"
df_unpop$Site2[df_unpop$Site2 =="Muqdisho (Mogadishu)"] <- "Mogadishu"
df_unpop$Site2[df_unpop$Site2 =="Monterrey"] <- "Monterrey, Mexico"
df_unpop$Site2[df_unpop$Site2 =="München (Munich)"] <- "Munich"
df_unpop$Site2[df_unpop$Site2 =="Napoli (Naples)"] <- "Naples"
df_unpop$Site2[df_unpop$Site2 == "Nashville-Davidson"] <- "Nashville, Tennessee"
df_unpop$Site2[df_unpop$Site2 =="Nuevo Laredo"] <- "Nuevo Laredo, Mexico"
df_unpop$Site2[df_unpop$Site2 =="Orlando"] <- "Orlando, Florida"
df_unpop$Site2[df_unpop$Site2 =="Reynosa"] <- "Reynosa Mexico"
df_unpop$Site2[df_unpop$Nation =="Dominican Republic" & df_unpop$Site2 =="Santiago"] <- "Santiago, Dominican Republic"
df_unpop$Site2[df_unpop$Site2 =="Taibei"] <- "Taipei"
df_unpop$Site2[df_unpop$Site2 =="Tampa-St. Petersburg"] <- "Tampa, Florida"
df_unpop$Site2[df_unpop$Site2 =="Tijuana"] <- "Tijuana Municipality"
df_unpop$Site2[df_unpop$Site2 =="Tiranë (Tirana)"] <- "Tirana"
df_unpop$Site2[df_unpop$Site2 =="Varanasi (Benares)"] <- "Varanasi, India"
df_unpop$Site2[df_unpop$Site2 =="Wellington"] <- "Wellington, New Zealand"
df_unpop$Site2[df_unpop$Nation =="United States of America" & df_unpop$Site2 =="Birmingham"] <- "Birmingham, Alabama"
df_unpop$Site2[df_unpop$Nation =="United Kingdom" & df_unpop$Site2 =="Birmingham (West Midlands)"] <- "Birmingham"
df_unpop$Site2[df_unpop$Nation =="Dominican Republic" & df_unpop$Site2 =="Santo Domingo"] <- "Santo Domingo, Dominican Republic"
df_unpop$Site2[df_unpop$Site2 =="Wroclaw"] <- "Wroclaw"
df_unpop$Site2[df_unpop$Site2 =="Port Elizabeth (Nelson Mandela Bay)"] <- "Port Elizabeth, South Africa"
df_unpop$Site2[df_unpop$Site2 =="Pune (Poona)"] <- "Pune, India"

##Joint Population Data
df_jointpop <- bind_rows(df_unpop, df_uscensus2010) %>% bind_rows(df_miss) %>% select(-Nation)

##Left Join
df2 %>% left_join(df_jointpop, by = c("Site2" = "Site2")) -> df3
write_rds(df3, "Importable/2_FLG_litrev2.rds")
write_rds(df_acs2019, "Importable/2_acs2019_birthplace.rds")

```

