Homework assignment 1 at the course Applied Data Analysis in Business with R
Paul Friedrich
2024-04-05
library(readxl)
data <- read_excel("data_1714754986.xlsx")
head(data)## # A tibble: 6 × 9
## indicator Institutional covera…¹ Country__ESTANDAR Public spending by f…²
## <chr> <chr> <chr> <chr>
## 1 Public spendi… Central government Brazil Total expenditure
## 2 Public spendi… Central government Brazil Total expenditure
## 3 Public spendi… Central government Nicaragua Total expenditure
## 4 Public spendi… Central government Grenada Total expenditure
## 5 Public spendi… Central government Saint Vincent an… Total expenditure
## 6 Public spendi… Central government Grenada Total expenditure
## # ℹ abbreviated names: ¹`Institutional coverage`,
## # ²`Public spending by function`
## # ℹ 5 more variables: Years__ESTANDAR <chr>, value <dbl>, unit <chr>,
## # notes_ids <lgl>, source_id <dbl>data <- data[!(data$Country__ESTANDAR %in% c("Brazil", "Grenada", "Belize", "Saint Vincent and the Grenadines", "Bahamas", "Saint Kitts and Nevis", "Barbados", "Haiti", "Antigua and Barbuda", "Jamaica", "Trinidad and Tobago", "Saint Lucia", "Suriname", "Guyana", "Dominica")), ]
head(data)## # A tibble: 6 × 9
## indicator Institutional covera…¹ Country__ESTANDAR Public spending by f…²
## <chr> <chr> <chr> <chr>
## 1 Public spendi… Central government Nicaragua Total expenditure
## 2 Public spendi… Central government El Salvador Total expenditure
## 3 Public spendi… Central government El Salvador Total expenditure
## 4 Public spendi… Central government El Salvador Total expenditure
## 5 Public spendi… Central government El Salvador Total expenditure
## 6 Public spendi… Central government El Salvador Total expenditure
## # ℹ abbreviated names: ¹`Institutional coverage`,
## # ²`Public spending by function`
## # ℹ 5 more variables: Years__ESTANDAR <chr>, value <dbl>, unit <chr>,
## # notes_ids <lgl>, source_id <dbl>columns_to_remove <- c("indicator", "unit", "source_id", "notes_ids")
data <- data[, !names(data) %in% columns_to_remove]
head(data)## # A tibble: 6 × 5
## `Institutional coverage` Country__ESTANDAR `Public spending by function`
## <chr> <chr> <chr>
## 1 Central government Nicaragua Total expenditure
## 2 Central government El Salvador Total expenditure
## 3 Central government El Salvador Total expenditure
## 4 Central government El Salvador Total expenditure
## 5 Central government El Salvador Total expenditure
## 6 Central government El Salvador Total expenditure
## # ℹ 2 more variables: Years__ESTANDAR <chr>, value <dbl>colnames(data)[colnames(data) == "Country__ESTANDAR"] <- "Country"
colnames(data)[colnames(data) == "Years__ESTANDAR"] <- "Years"
head(data)## # A tibble: 6 × 5
## `Institutional coverage` Country `Public spending by function` Years value
## <chr> <chr> <chr> <chr> <dbl>
## 1 Central government Nicaragua Total expenditure 1990 19.4
## 2 Central government El Salvador Total expenditure 1990 9.54
## 3 Central government El Salvador Total expenditure 1991 11.8
## 4 Central government El Salvador Total expenditure 1992 12.0
## 5 Central government El Salvador Total expenditure 1993 12.9
## 6 Central government El Salvador Total expenditure 1994 15.7data <- data[data$`Public spending by function` != "Total expenditure", ]
head(data)## # A tibble: 6 × 5
## `Institutional coverage` Country `Public spending by function` Years value
## <chr> <chr> <chr> <chr> <dbl>
## 1 Central government Nicaragua General public services 1990 1.61
## 2 Central government El Salvador General public services 1990 1.90
## 3 Central government El Salvador General public services 1991 3.75
## 4 Central government El Salvador General public services 1992 3.76
## 5 Central government El Salvador General public services 1993 3.72
## 6 Central government El Salvador General public services 1994 3.96data$Years <- as.integer(data$Years)
head(data)## # A tibble: 6 × 5
## `Institutional coverage` Country `Public spending by function` Years value
## <chr> <chr> <chr> <int> <dbl>
## 1 Central government Nicaragua General public services 1990 1.61
## 2 Central government El Salvador General public services 1990 1.90
## 3 Central government El Salvador General public services 1991 3.75
## 4 Central government El Salvador General public services 1992 3.76
## 5 Central government El Salvador General public services 1993 3.72
## 6 Central government El Salvador General public services 1994 3.96data$`Institutional coverage` <- as.factor(data$`Institutional coverage`)
data$Country <- as.factor(data$Country)
data$`Public spending by function`<- as.factor(data$`Public spending by function`)
head(data)## # A tibble: 6 × 5
## `Institutional coverage` Country `Public spending by function` Years value
## <fct> <fct> <fct> <int> <dbl>
## 1 Central government Nicaragua General public services 1990 1.61
## 2 Central government El Salvador General public services 1990 1.90
## 3 Central government El Salvador General public services 1991 3.75
## 4 Central government El Salvador General public services 1992 3.76
## 5 Central government El Salvador General public services 1993 3.72
## 6 Central government El Salvador General public services 1994 3.96The data is from the Comisión Económica para América Latina y el Caribe (https://www.cepal.org/), Cepal for short. Units of observation are countries, South and Central American member states. I have removed the countries that do not have Castilian as their main language and the columns that do not contain information. There is a separate row for each specific budgetary outlay and year, so it would be more correct to say that budgets are the units of observation. There are 18 countries, 31 observed years and several spending functions, which leaves us with 23 973 rows. All variables are categorical with the exception of “value”, which is properly numeric (and what is measured) and “Years” which is in between as we only allow integer values, but the set of possible values is infinite. “Value” represents the value of outlays as percentage of GDP. The exact source is: https://statistics.cepal.org/portal/cepalstat/dashboard.html?indicator_id=4410&area_id=2313&lang=en
print(subset(data, `Institutional coverage` == "Central government" & `Country` == "Argentina" & `Public spending by function` == "Education"), n = 6)## # A tibble: 31 × 5
## `Institutional coverage` Country `Public spending by function` Years value
## <fct> <fct> <fct> <int> <dbl>
## 1 Central government Argentina Education 1990 0.974
## 2 Central government Argentina Education 1992 0.565
## 3 Central government Argentina Education 1991 0.962
## 4 Central government Argentina Education 1993 0.720
## 5 Central government Argentina Education 1994 0.825
## 6 Central government Argentina Education 1995 0.862
## # ℹ 25 more rowssummary_stats <- list()
countries <- unique(data$Country)
for (country in countries) {
country_data <- subset(data, Country == country)
min_value <- min(country_data$value, na.rm = TRUE)
mean_value <- mean(country_data$value, na.rm = TRUE)
max_value <- max(country_data$value, na.rm = TRUE)
summary_stats[[country]] <- c(min_value = min_value, mean_value = mean_value, max_value = max_value)
}
summary_stats_df <- as.data.frame(do.call(rbind, summary_stats))
summary_stats_df$Country <- rownames(summary_stats_df)
summary_stats_df <- summary_stats_df[, c("min_value", "mean_value", "max_value")]
print(summary_stats_df)## min_value mean_value max_value
## Nicaragua 0.000070 1.1584824 6.242859
## El Salvador 0.000116 1.0502498 8.855099
## Uruguay 0.000182 1.0331100 10.736911
## Bolivia (Plurinational State of) 0.000193 2.0323432 31.263323
## Cuba 0.056675 5.2580817 33.747638
## Panama 0.000123 0.9515186 7.867925
## Ecuador -5.684106 0.7701396 12.925349
## Argentina 0.007652 1.6883860 15.117129
## Venezuela (Bolivarian Republic of) 0.001963 1.9186881 13.231398
## Guatemala 0.000008 0.6338732 3.521593
## Peru 0.000351 1.1410396 6.750324
## Honduras 0.000046 1.1041415 8.521301
## Dominican Republic 0.000019 0.8208901 6.149250
## Costa Rica 0.000001 0.9800736 9.039073
## Paraguay 0.000021 0.7739492 6.753471
## Chile 0.000001 0.7575188 8.122326
## Mexico 0.000000 0.7676046 9.331209
## Colombia -0.111669 0.7767910 8.956186We see that public expenditure for education on the central government level differs across countries. The period of observation is 1990 to 2020. The differences could be explained as left-wing governments spending more on public education, or as reflecting that education spending takes a larger slice of GDP if a country is very poor (as in Bolivia), or that illiberal governments are lying (Cuba?). Guatemala from 1960 to 1996 suffered a civil war in which the government committed acts (the “Holocausto silencioso”) that eventually were qualified as genocide on the own population, reluctance on the part of the government to spend a lot of money on public education is therefor not surprising. Negative values represent accounting effects, namely transfers of a general character between different levels of government.
subset_data <- subset(data, `Institutional coverage` == "Central government" &
`Public spending by function` == "Education")
plot(subset_data$Years, subset_data$value,
type = "p", # "p" for points only
pch = 8, # point character
col = "darkblue", # color of points
xlab = "Years",
ylab = "Value",
main = "Percentage of GDP spent on education by central governments")
We find a tendency for public spending on education to rise over time.
The data only encompass public spending on the national level, but the
trend seems clear.
spending_freq <- table(data$`Public spending by function`)
spending_freq_sorted <- sort(spending_freq, decreasing = TRUE)
print(spending_freq_sorted)##
## Education
## 633
## Health
## 633
## Social protection
## 629
## Education n.e.c.
## 621
## Health n.e.c.
## 619
## Housing and community amenities
## 617
## General public services
## 611
## Public debt transactions
## 598
## Social protection n.e.c.
## 593
## Economic affairs
## 583
## Public order and safety
## 562
## Recreation, culture, and religion
## 537
## Executive and legislative organs, financial and fiscal affairs, external affairs
## 535
## Agriculture, forestry, fishing, and hunting
## 532
## Transport
## 525
## Fuel and energy
## 496
## Law courts
## 455
## Public order and safety n.e.c.
## 444
## Defense
## 430
## Environmental protection
## 423
## Housing and community amenities n.e.c.
## 422
## Mining, manufacturing, and construction
## 421
## General economic, commercial, and labor affairs
## 415
## Other industries
## 413
## Economic affairs n.e.c.
## 409
## Cultural services
## 401
## Communication
## 390
## Water supply
## 372
## Environmental protection n.e.c.
## 371
## Old age
## 368
## Housing development
## 362
## Military defense
## 358
## Tertiary education
## 358
## General public services n.e.c.
## 348
## Recreational and sporting services
## 337
## Education not definable by level
## 329
## Family and children
## 328
## Public health services
## 305
## Basic research
## 304
## Police services
## 303
## Prisons
## 299
## Social exclusion n.e.c.
## 292
## Secondary education
## 289
## General services
## 282
## Community development
## 281
## Pre-primary and primary education
## 276
## Hospital services
## 274
## Protection of biodiversity and landscape
## 249
## Recreation, culture, and religion n.e.c.
## 235
## Transfers of a general character between different levels of government
## 210
## Fire protection services
## 204
## Sickness and disability
## 200
## Defense n.e.c.
## 162
## Broadcasting and publishing services
## 161
## Pollution abatement
## 155
## Subsidiary services to education
## 148
## Outpatient services
## 147
## Medical products, appliances, and equipment
## 145
## R&D Economic affairs
## 140
## Waste water management
## 135
## R&D Health
## 110
## Waste management
## 105
## Statistical discrepancy
## 104
## Housing
## 88
## Unemployment
## 88
## Civil defense
## 87
## Postsecondary nontertiary education
## 81
## R&D Social protection
## 78
## R&D General public services
## 76
## R&D Education
## 75
## R&D Defense
## 62
## R&D Public order and safety
## 61
## Street lighting
## 60
## R&D Environmental protection
## 57
## Foreign economic aid
## 54
## Religious and other community services
## 42
## R&D Recreation, culture, and religion
## 37
## Foreign military aid
## 22
## R&D Housing and community amenities
## 8
## Survivors
## 4We find that both the number and the names of budget items vary. Some budget items are rather rare, but “education”, “health” and “social protection” are omnipresent.
Finally we shall look whether there is a strong correlation between public expense on education on the national level and defense spending.
merged_data <- merge(subset(data, `Public spending by function` == "Education"),
subset(data, `Public spending by function` == "Defense"),
by = c("Country", "Years"))
plot(merged_data$value.x, merged_data$value.y,
main = "Education expenditure vs. Defense expenditure",
xlab = "Education expenditure", ylab = "Defense expenditure",
col = "darkgreen", pch = 16)
correlation <- cor(merged_data$value.x, merged_data$value.y)
r_squared <- correlation^2
cat("Correlation coefficient (r):", correlation, "\n")## Correlation coefficient (r): 0.02413563cat("Coefficient of determination (r^2):", r_squared, "\n")## Coefficient of determination (r^2): 0.0005825287Correlation between the two is negligible.