Data Manipulation and cleaning

Source 1:Corruption Index

corruption=read_csv("./data/corruption_index.csv")|>
  janitor::clean_names()|>
  mutate(value=(as.numeric(x2020_yr2020)+as.numeric(x2020_yr2020)+as.numeric(x2020_yr2020))/3)|>
  select(country_name,country_code,series_name,value)|>
  pivot_wider(
    names_from = "series_name",
    values_from = "value"
  )|>
  janitor::clean_names()

We believe that it would be useful to ascertain the officially published data on the corruption index for the last 20 years. This data is provided by The World bank and is public information. World data bank gives estimated scores for countries on the overall corruption indicator. Following this indicator, we also find five indicators Government Effectiveness, Political Stability and Absence of Violence, Regulatory Quality, Rule of Law, Voice and Accountability may be correlated with the Corruption Index. This dataset contains information from 1996 to 2022.

The World data bank dataset was downloaded and read in. The dataset contains data for all indicators from 1996 to 2022 to reflect how they change from year to year. In this dataset, we keep all the variables. To make the data more readable and easier to analyze later, we made the dataset wide and created a new year variable to record year.

Source 2: Country Code

isso_code=
  read_html("https://www.countrycode.org/")|>
  html_elements("td:nth-child(3)")|>
  html_text()

country_name_isso=
  read_html("https://www.countrycode.org/")|>
  html_elements("td:nth-child(1)")|>
  html_text()

country_code=data.frame(country_name_isso,str_split_fixed(isso_code, '/',2))|>
  rename(c(country_code_2=X1,country_code_3=X2))|>
  mutate(country_code_2=gsub('[ ]','',country_code_2),
         country_code_3=gsub('[ ]','',country_code_3))|>
  distinct(country_code_3, .keep_all = T)

In order to have a better integration of the subsequent data and to avoid unnecessary trouble due to the fact that the names of the countries recorded are different in different datasets. We decided to use the country code to refer to the country.

This dataset is derived from countrycode.org. The data set was not available to download but we were able to scrape it from the website. We read the country name and ISSO code columns and split the ISSO code into country_code_2 and country_code_3.

Sourse 3:GDP

average_GDP=read_csv("./data/GDP.csv")|>
  janitor::clean_names()|>
  filter(average_gdp!="#DIV/0!")|>
  mutate(GDP=as.numeric(average_gdp))|>
  select(country_name,country_code,GDP)|>
  mutate(GDP=GDP/10^9)

GDP_EDA=
  read_csv("./data/GDP.csv")|>
  janitor::clean_names()|>
  select(country_code,x1996:x2022)|>
  pivot_longer(
    x1996:x2022,
    names_to = "year",
    values_to = "GDP"
               )|>
  mutate(GDP=GDP/10^9,
         year=substr(year,2,5))

GDP_per_capita_EDA=
  read_csv("./data/GDP_per_capita.csv")|>
  janitor::clean_names()|>
  select(country_code,x1996:x2022)|>
  pivot_longer(
    x1996:x2022,
    names_to = "year",
    values_to = "GDP_per_capita"
               )|>
  mutate(year=substr(year,2,5))

We are also interested in whether there is a correlation between a country’s level of economic development and the corruption index. This dataset comes from The World Bank. This dataset records the GDP of each country from 1960 to 2022. we divide the GDP by \(10^9\) to make the data more readable. We pivoted this dataset to long format and created average_GDP for future analysis.

Source 4:Continent

Africa=
  read_html("https://www.countrycallingcodes.com/iso-country-codes/africa-codes.php") |>
  html_elements("tr+ tr td:nth-child(3) font") |>
  html_text()

Asia=
  read_html("https://www.countrycallingcodes.com/iso-country-codes/asia-codes.php") |>
  html_elements("tr+ tr td:nth-child(3) font") |>
  html_text()

Europe=
  read_html("https://www.countrycallingcodes.com/iso-country-codes/europe-codes.php") |>
  html_elements("tr+ tr td:nth-child(3) font") |>
  html_text()|>
  append("JEY")

North_America=
  read_html("https://www.countrycallingcodes.com/iso-country-codes/north-america-codes.php") |>
  html_elements("tr+ tr td:nth-child(3) font") |>
  html_text()

South_America=
  read_html("https://www.countrycallingcodes.com/iso-country-codes/south-america-codes.php") |>
  html_elements("tr+ tr td:nth-child(3) font") |>
  html_text()

Oceania=
  read_html("https://www.countrycallingcodes.com/iso-country-codes/australia-codes.php") |>
  html_elements("tr+ tr td:nth-child(3) font") |>
  html_text()

Antarctica=
  read_html("https://www.countrycallingcodes.com/iso-country-codes/antarctica-codes.php") |>
  html_elements("tr+ tr td:nth-child(3) font") |>
  html_text()

The geographical location of a country may also have a bearing on a country’s corruption index. Therefore, we decided to find the continent on which each country is located and explore whether it has an impact on the corruption index. This dataset comes from countrycallingcodes.com, where we read the country codes of the countries contained in each continent. The data set was not available to download but we were able to scrape it from the website. In future analyses, we will use this data to match the continent of the country.

Source 5: Development

developed=
  read_csv("./data/developed-countries-2023.csv")|>
  pull(cca3)
least_develpoed=
  read_csv("./data/least-developed-countries-2023.csv")|>
  pull(cca3)

Countries in the world can be roughly divided into developed countries, developing countries and least developed countries. A country’s corruption index may be related to its state of development. This dataset comes from World Population Review. This dataset records the state of development of countries in 2023.

Source 6: Coordinate

coordinate_html=
  read_html("https://developers.google.com/public-data/docs/canonical/countries_csv?hl=en")

country_name=
  coordinate_html |>
  html_elements("td:nth-child(4)") |>
  html_text()

country_code2=
  coordinate_html |>
  html_elements("td:nth-child(1)") |>
  html_text() 

latitude=
  coordinate_html |>
  html_elements("td:nth-child(2)") |>
  html_text()

longitude=
  coordinate_html |>
  html_elements("td:nth-child(3)") |>
  html_text()

country_coordinate=
  data.frame(country_name,country_code2,latitude,longitude)|>
  left_join(country_code,join_by(country_code2==country_code_2))|>
  select(country_name,country_code=country_code_3,latitude,longitude)

We need the coordinates of the countries for the future EDA session. This dataset comes from Google for Developers. Since this dataset records two-digit country codes and the other datasets use three-digit country codes, we use the left_join function to match their country codes and keep only the three-digit country code.

Source 7: Population

average_population=read_csv("./data/population.csv")|>
  janitor::clean_names()|>
  filter(average_population!="#DIV/0!")|>
  mutate(population=as.numeric(average_population))|>
  select(country_name,country_code,population)|>
  mutate(population=population/(10^7))

population=read_csv("./data/population.csv")|>
  janitor::clean_names()|>
  select(country_name,country_code,x1996:x2022)|>
  pivot_longer(
    x1996:x2022,
    names_to = "year",
    values_to = "population")|>
  mutate(year=substr(year,2,5),
         population=population/(10^7))

We are also interested in whether there is a correlation between a country’s population and the corruption index. This dataset comes from The World Bank. This dataset records the population of each country from 1960 to 2022. We pivoted this dataset to long format and created average_population for future analysis.

Source 8: CPI

country_code_cpi = 
  country_code |> 
    select(-country_code_2) |>
    rename("Country / Territory" = country_name_isso,
           "Country code" = country_code_3)

numberlist = c("A1:M200", "N1:P200", "Q1:S200", "T1:V200", "W1:Y200", "Z1:AB200", "AC1:AE200", "AF1:AH200", "AI1:AK200", "AL1:AN200", "AO1:AQ200", "AR1:AT200", "AU1:AW200", "AX1:AZ200", "BA1:BC200")

read_cpi = 
  function(address, scope){
    tidy_data = 
  read_excel(address, sheet = 1, scope)
    
    tidy_data
}

for (i in 1:length(numberlist)){

  tidy_data = 
    read_cpi("./data/Country CPI 1998-2022.xlsx",
            numberlist[[i]]) 
  
  country_code_cpi = 
    country_code_cpi |> 
    left_join(tidy_data, by = "Country / Territory")
}

country_code_cpi = 
    country_code_cpi |>
    select("Country / Territory", "Country code.x", contains(c("20", "19"))) |> 
  janitor::clean_names() |> 
  rename("country_code" = country_code_x)

names(country_code_cpi) = 
  gsub("cpi_score_", "", names(country_code_cpi))

country_code_cpi$"2005" = as.numeric(country_code_cpi$"2005")

cols_to_check =  as.character(c(1998 : 2022))

country_code_cpi =
  country_code_cpi |> 
  select(sort(names(country_code_cpi))) |> 
  relocate(country_territory, country_code)|> 
  mutate_at(vars(c("1998":"2011")), ~ . * 10) |> 
  filter(!if_all(cols_to_check, is.na))

country_code_cpi |> 
  write.table(
    file = "./data/cleaned_data/cpi_data_year.csv",
    sep = ",", 
    row.names = FALSE)

We also recorded the CPI dataset for further EDA session. This dataset comes from Transparency International. The dataset contains the CPI of each countries from 1998 to 2022. In this dataset, we make data arranged by wide format.

Merge Datasets

corruption_data=left_join(corruption,country_code,join_by(country_code==country_code_3))|>
  left_join(average_GDP,join_by(country_code==country_code))|>
  left_join(country_coordinate,join_by(country_code))|>
  left_join(average_population,by=join_by(country_code))|>
  mutate(development=case_when(
    country_code %in% developed ~ "Developed",
    country_code %in% least_develpoed ~ "Least Developed",
    (!country_code %in% developed) & (!country_code %in% least_develpoed) ~ "Developing"
  ))|>
  mutate(continent=case_when(
    country_code %in% Asia ~ "Asia",
    country_code %in% Africa ~ "Africa",
    country_code %in% Europe ~ "Europe",
    country_code %in% North_America ~ "North America",
    country_code %in% South_America ~ "South America",
    country_code %in% Oceania ~ "Oceania",
    country_code %in% Antarctica ~ "Antarctica"
  ))|>
  select(country_name=country_name.x,country_code,
         con_cor=control_of_corruption_estimate,
         gov_eff=government_effectiveness_estimate,
         pol_vio=political_stability_and_absence_of_violence_terrorism_estimate,
         regu_qual=regulatory_quality_estimate,
         rul_law=rule_of_law_estimate,
         voi_acc=voice_and_accountability_estimate,
         GDP,population,latitude,longitude,development,continent)

write.table(corruption_data,"./data/cleaned_data/overall_dataset.csv",row.names=FALSE,col.names=TRUE,sep=",")
  

corruption_EDA_pre=read_csv("./data/corruption_index.csv")|>
  janitor::clean_names()|>
  pivot_longer(
    cols = x1996_yr1996:x2022_yr2022,
    names_to = "year",
    values_to = "value"
  )|>
  mutate(year=substr(year,2,5))|>
  select(-series_code)|>
  pivot_wider(
    names_from = "series_name",
    values_from = "value"
  )


corruption_EDA=
  corruption_EDA_pre|>
  left_join(GDP_EDA,by = join_by(country_code, year))|>
  left_join(GDP_per_capita_EDA,by = join_by(country_code, year))|>
  janitor::clean_names()|>
  select(country_name,country_code,year,
         con_cor=control_of_corruption_estimate,
         gov_eff=government_effectiveness_estimate,
         pol_vio=political_stability_and_absence_of_violence_terrorism_estimate,
         regu_qual=regulatory_quality_estimate,
         rul_law=rule_of_law_estimate,
         voi_acc=voice_and_accountability_estimate,
         gdp,gdp_per_capita)|>
  mutate(continent=case_when(
    country_code %in% Asia ~ "Asia",
    country_code %in% Africa ~ "Africa",
    country_code %in% Europe ~ "Europe",
    country_code %in% North_America ~ "North America",
    country_code %in% South_America ~ "South America",
    country_code %in% Oceania ~ "Oceania",
    country_code %in% Antarctica ~ "Antarctica"
  ))|>
  mutate(development=case_when(
    country_code %in% developed ~ "Developed",
    country_code %in% least_develpoed ~ "Least Developed",
    (!country_code %in% developed) & (!country_code %in% least_develpoed) ~ "Developing"
  ))|>
    left_join(country_coordinate,join_by(country_code))|>
  left_join(population,join_by(country_code,year))|>
  select(country_name=country_name.x,everything(),-country_name.y,-country_name)|>
  mutate(con_cor=as.numeric(con_cor),
         gov_eff=as.numeric(gov_eff),
         pol_vio=as.numeric(pol_vio),
         regu_qual=as.numeric(regu_qual),
         rul_law=as.numeric(rul_law),
         voi_acc=as.numeric(voi_acc),
         year=as.numeric(year)
         )
  
write.table(corruption_EDA,"./data/corruption_EDA.csv",row.names=FALSE,col.names=TRUE,sep=",")

Since we cleaned the datasets we got, we need to merge these datasets for our future analysis. Since country_code is the common variable in every datasets, we use left_join function to merge these datasets together. For those dataset is not suitable for left_join, we use case_when function to merge them into the cleaned dataset. We also need to make sure every variable in the apporite type. The cleaned dataset include the following variables:

• country_name: Country names
• country_code: Country codes
• year: year
• con_cor: Control of corruption
  
• gov_eff: Government effectiveness
  
• pol_vio: Political stability and absence of violence terrorism
  
• regu_qual: Regulatory quality
  
• rul_law: Rule of law
  
• voi_acc: Voice and accountability
  
• gdp:GDP of countries(\(10^9\))
• gdp_per_capita: GDP per capita of countries
• continent: Continent where the countries are located
• development: Status of development of countries
• latitude: latitude
• longitude: longitude
• population: Population of countries(\(10^7\))

For the dataset we used in regression section, we take the average value of the numeric variables from 2020-2022.

country_name	country_code	con_cor	gov_eff	pol_vio	regu_qual	rul_law	voi_acc	GDP	population	latitude	longitude	development	continent
Afghanistan	AFG	-1.4936998	-1.6095092	-2.7026317	-1.3891630	-1.8313743	-1.0776886	17.363294	4.0066821	33.93911	67.709953	Least Developed	Asia
Albania	ALB	-0.5729245	-0.1548803	0.0885765	0.2219672	-0.3762492	0.0864026	17.325132	0.2808383	41.153332	20.168331	Developing	Europe
Algeria	DZA	-0.6664602	-0.5734335	-0.8478143	-1.3553003	-0.8003030	-1.0879523	167.043000	4.4177620	28.033886	1.659626	Developing	Africa
American Samoa	ASM	1.2660053	0.6328993	1.0916016	0.5253105	1.1194303	0.9520435	0.712500	0.0045166	-14.270972	-170.132217	Developing	Oceania
Andorra	AND	1.2660053	1.7494141	1.5885718	1.3354213	1.6162784	1.0862534	3.189393	0.0078853	42.546245	1.601554	Developed	Europe
Angola	AGO	-0.9386725	-1.2583939	-0.6007260	-0.8568535	-0.9509994	-0.8160028	74.213601	3.4507082	-11.202692	17.873887	Least Developed	Africa
Anguilla	AIA	0.6324695	0.9120280	1.5037429	0.9303660	0.3741581	NA	NA	NA	18.220554	-63.068615	Developing	North America
Antigua and Barbuda	ATG	0.2385333	-0.2044867	0.9295049	0.4976937	0.4079735	0.7427933	1.578157	0.0093215	17.060816	-61.796428	Developing	North America
Argentina	ARG	-0.1637807	-0.2538182	-0.0720262	-0.4983443	-0.4873348	0.5852944	501.846000	4.5806780	-38.416097	-63.616672	Developed	South America
Armenia	ARM	-0.0034387	-0.3046165	-0.7716423	0.2387480	-0.1258523	0.0471597	15.335297	0.2792350	40.069099	45.038189	Developing	Asia
Aruba	ABW	1.1993917	1.0752245	1.3714974	0.9148085	1.2702624	1.2821430	2.868029	0.0106522	12.52111	-69.968338	Developing	North America
Australia	AUS	1.6329559	1.5737755	0.8616769	1.8139470	1.6142451	1.2980305	1518.350000	2.5774101	-25.274398	133.775136	Developed	Oceania
Austria	AUT	1.4777892	1.6062636	0.8875288	1.3952273	1.7701634	1.3944378	462.331000	0.8971730	47.516231	14.550072	Developed	Europe
Azerbaijan	AZE	-1.0770841	-0.1555638	-0.8522475	-0.3360401	-0.7307201	-1.5415164	58.746490	1.0135296	40.143105	47.576927	Developing	Asia
Bahamas, The	BHS	1.1062059	0.4056442	0.8302374	0.0013662	0.0232638	0.9154646	11.393200	0.0408120	25.03428	-77.39628	Developed	North America
Bahrain	BHR	-0.0935939	0.3907681	-0.6101093	0.6148575	0.4553994	-1.4533018	39.438677	0.1470989	25.930414	50.637772	Developed	Asia
Bangladesh	BGD	-1.0036772	-0.7797257	-0.9158078	-0.9062135	-0.5679290	-0.7695580	416.789000	16.9321191	23.684994	90.356331	Least Developed	Asia
Barbados	BRB	1.1940669	0.4440223	1.1205775	0.4677958	0.2966763	1.1254591	5.051171	0.0281176	13.193887	-59.543198	Developing	North America
Belarus	BLR	-0.1339642	-0.8295090	-0.8876082	-0.7998758	-1.0822419	-1.4534981	67.946320	0.9297079	53.709807	27.953389	Developed	Europe
Belgium	BEL	1.4459563	1.0787830	0.5210230	1.3436358	1.3280041	1.2784452	566.056000	1.1600334	50.503887	4.469936	Developed	Europe