MATH2349 Data Wrangling

Practical Assessment 2

Setup

# Load packages 

library(readr)
library(kableExtra)
library(tidyr)
library(dplyr)
#install.packages("tidyverse")
library(tidyverse)
#install.packages("Hmisc")
library(Hmisc)
library(ggplot2)
#install.packages("DescTools")
library(DescTools)
library(magrittr)

Student Name, Student Number and Percentage of Contribution

Individual Details

Student name	Student Number	Percentage of contribution
Chamudi Abeysinghe	s4150303	100%

Executive Summary

• Identified two data sets which had multiple years as separate columns, violating tidy data principles in both data sets. Both data sets included a common variables, “Country Name”, and “Year” which was used for merging.

• Categorical variables such as “Country Name”, “Country Code”, “Indicator Code” and “Indicator Name” were converted to factor types to optimize memory usage and improve clarity in analysis.

• I transformed the wide format (years as columns) into a long format using gather(). This step created a single “Year” column and a corresponding “Value” column, making the data tidy and easier to work with.

• I used spread() to separate values in the “Indicator Name” column into distinct variables (e.g., population, electricity access). This allowed each indicator to have its own column.

• The “Year” variable was changed into an ordered factor to ensure correct sorting in visualizations.

• Merged Datasets with left_join(). Combined the two tidy data sets using a natural join on “Country Name” and “Year”. This retained all records from the population data set and added matching values from the development data set.

• Selected Relevant Variables. I narrowed down to four key variables: “Country Name”, “Year”, “Population”, and “Access to electricity, urban (% of urban population)”, focusing the data set on variables critical to the analysis.

• Scanned and Cleaned Data. Checked and removed missing values (NA), special values (Inf, NaN), and inconsistencies.

Data

The data sets were sourced from the World Bank Data repository. One of them, Urban Population.csv, contains information on people living in urban areas, based on definitions provided by each country’s national statistical office. The data is organized by country and covers the years from 1960 to 2023.

The second data set is ‘Urban Development.csv’ which lists urban development indicators by country for the same period of time.

In Urban Development (2023) mentions, data on urbanization, traffic and congestion, and air pollution are from the United Nations Population Division, World Health Organization, International Road Federation, World Resources Institute, and other sources.

Both sets contain ‘Country Name’ and ‘Country Code’ variables.

More variable descriptions will be provided later on this report.

After loading the data sets, I chose to focus only on the most recent six years (2018 to 2023) to keep things concise. This doesn’t affect the data wrangling methods used later, as they remain the same.

Source of Data

Urban population (2023) Worldbank.org, Retrieved May 25, 2025, https://data.worldbank.org/indicator/SP.URB.TOTL?end=2023&start=2000 Released under a Creative Commons Attribution BY-4.0 license.

This was sourced by World Bank staff estimates based on the United Nations Population Division’s World Urbanization Prospects: 2018 Revision.

Urban Development (2023) Worldbank.org, Retrieved May 25, 2025, https://data.worldbank.org/topic/urban-development. Released under a Creative Commons Attribution BY-4.0 license.

This was sourced by World Bank staff estimates based on the United Nations Population Division’s World Urbanization Prospects: 2018 Revision.

Data set I

In ‘Urban Population.csv’, there are 266 observations and 10 variables.

Variable Description for ‘Urban Population.csv’ as below:

Column Name	Description
Country Name	Name of the country.
Country Code	Country code (ex: “USA” - United States)
Indicator Name	The name of the indicator (ex: “Urban population”)
Indicator Code	Code used to represent the indicator (ex: “SP.URB.TOTL”)
1960 to 2024	Urban population data for each corresponding year

# Import the urban population data, skip the first 4 metadata rows while importing

urban_population <- read_csv("/Users/chamudi/Desktop/RMIT/1st Year 1st Semester/Data Wrangling/Assignments/Assignment 2/Dataset/1/Urban Population.csv", skip = 4) 

New names:Rows: 266 Columns: 70── Column specification ──────────────────────────────────────────────────────────────────────
Delimiter: ","
chr  (4): Country Name, Country Code, Indicator Name, Indicator Code
dbl (64): 1960, 1961, 1962, 1963, 1964, 1965, 1966, 1967, 1968, 1969, 1970, 1971, 1972, 19...
lgl  (2): 2024, ...70
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

#select columns 1 to 4 and 63 to 68 - reason is mentioned in 'Data' section.
urban_population <- urban_population %>%
  select(c(1:4, 63:68))

#display the first few rows
knitr::kable(head(urban_population))

Country Name	Country Code	Indicator Name	Indicator Code	2018	2019	2020	2021	2022	2023
Aruba	ABW	Urban population	SP.URB.TOTL	47278	47554	47449	47244	47272	47511
Africa Eastern and Southern	AFE	Urban population	SP.URB.TOTL	236107161	245939557	256139740	266650602	277426261	288380305
Afghanistan	AFG	Urban population	SP.URB.TOTL	9367638	9749465	10168092	10525708	10800465	11165011
Africa Western and Central	AFW	Urban population	SP.URB.TOTL	211219592	219276647	227465935	235827038	244365364	253228069
Angola	AGO	Urban population	SP.URB.TOTL	20504018	21425222	22353719	23295577	24260684	25242775
Albania	ALB	Urban population	SP.URB.TOTL	1728969	1747593	1762645	1770478	1772138	1773980

NA
NA
NA

Data set II

In the ‘Urban Development.csv’, there are 4522 observations and 10 variables.

Variable Description for ‘Urban Development.csv’ as below:

Column Name	Description
Country Name	Name of the country (ex: “India”, “Brazil”).
Country Code	Country code (ex: “USA” for United States).
Indicator Name	The name of the development indicator, likely all “Urban population”.
Indicator Code	The official code for the indicator.
1960 to 2024	Annual values for the indicator from 1960 to 2024. Each year is a separate column.

# Import the urban development data, skip the first 4 metadata rows while importing

urban_development <- read_csv("/Users/chamudi/Desktop/RMIT/1st Year 1st Semester/Data Wrangling/Assignments/Assignment 2/Dataset/2/Urban Development.csv", skip = 4) 

New names:Rows: 4522 Columns: 70── Column specification ──────────────────────────────────────────────────────────────────────
Delimiter: ","
chr  (4): Country Name, Country Code, Indicator Name, Indicator Code
dbl (65): 1960, 1961, 1962, 1963, 1964, 1965, 1966, 1967, 1968, 1969, 1970, 1971, 1972, 19...
lgl  (1): ...70
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

#select columns 1 to 4 and 63 to 68 - reason is mentioned in 'Data' section.
urban_development <- urban_development %>%
  select(c(1:4, 63:68))

#display the first few rows
knitr::kable(head(urban_development))

Country Name	Country Code	Indicator Name	Indicator Code	2018	2019	2020	2021	2022	2023
Aruba	ABW	Urban population (% of total population)	SP.URB.TOTL.IN.ZS	4.341100e+01	4.354600e+01	43.6970000	43.8660000	4.40520e+01	4.425400e+01
Aruba	ABW	Urban population	SP.URB.TOTL	4.727800e+04	4.755400e+04	47449.0000000	47244.0000000	4.72720e+04	4.751100e+04
Aruba	ABW	Urban population growth (annual %)	SP.URB.GROW	4.302996e-01	5.820836e-01	-0.2210457	-0.4329788	5.92492e-02	5.043109e-01
Aruba	ABW	Mortality caused by road traffic injury (per 100,000 population)	SH.STA.TRAF.P5	NA	NA	NA	NA	NA	NA
Aruba	ABW	Population in urban agglomerations of more than 1 million (% of total population)	EN.URB.MCTY.TL.ZS	NA	NA	NA	NA	NA	NA
Aruba	ABW	Population in urban agglomerations of more than 1 million	EN.URB.MCTY	NA	NA	NA	NA	NA	NA

NA

Sub-setting: Now I am creating random subsets using my student number s4150303. Since set.seed() only accepts numeric values, I need to extract the numeric part of my student number: 4150303.

In the urban_population_subset, there are 212 observations and 10 variables.

In the urban_development_subset, there are 3617 observations and 10 variables.

# Set seed using numeric part of your student number
set.seed(4150303)

# Calculate desired number of rows (at least 80% as in the specification)
urban_population_rows <- floor(0.8 * nrow(urban_population))
urban_development_rows <- floor(0.8 * nrow(urban_development))

# Randomly select 80% of rows for each data set
urban_population_subset <- urban_population[sample(nrow(urban_population), urban_population_rows), ]
urban_development_subset <- urban_development[sample(nrow(urban_development), urban_development_rows), ]

# Display first few rows of the subsets
knitr::kable(head(urban_population_subset))

Country Name	Country Code	Indicator Name	Indicator Code	2018	2019	2020	2021	2022	2023
Tajikistan	TJK	Urban population	SP.URB.TOTL	2525452	2602538	2681645	2763425	2847764	2933456
Guam	GUM	Urban population	SP.URB.TOTL	154187	153489	153950	155568	157071	158457
Tonga	TON	Urban population	SP.URB.TOTL	24419	24417	24417	24372	24291	24225
Togo	TGO	Urban population	SP.URB.TOTL	3444076	3575477	3710640	3849488	3992304	4139500
Cambodia	KHM	Urban population	SP.URB.TOTL	3806285	3923374	4052917	4187222	4320041	4455286
Liechtenstein	LIE	Urban population	SP.URB.TOTL	5484	5542	5608	5670	5741	5825

knitr::kable(head(urban_development_subset))

Country Name	Country Code	Indicator Name	Indicator Code	2018	2019	2020	2021	2022	2023
Malawi	MWI	Population in largest city	EN.URB.LCTY	1029639	1074695	1121720	1170870	1222325	1276316
Georgia	GEO	Urban population living in areas where elevation is below 5 meters (% of total population)	EN.POP.EL5M.UR.ZS	NA	NA	NA	NA	NA	NA
Puerto Rico	PRI	Urban population living in areas where elevation is below 5 meters (% of total population)	EN.POP.EL5M.UR.ZS	NA	NA	NA	NA	NA	NA
Central Europe and the Baltics	CEB	Population in urban agglomerations of more than 1 million (% of total population)	EN.URB.MCTY.TL.ZS	NA	NA	NA	NA	NA	NA
Qatar	QAT	Population living in slums (% of urban population)	EN.POP.SLUM.UR.ZS	NA	NA	NA	NA	NA	NA
Curacao	CUW	Urban land area where elevation is below 5 meters (sq. km)	AG.LND.EL5M.UR.K2	NA	NA	NA	NA	NA	NA

NA

Understand I

To get an understand about the subset data set, I first checked the data types.

The data set contains 212 observations and 10 variables.

All columns are stored as character data, while the columns for the years 2018 to 2023 are numeric. I decided to convert the character variables into factors because, in later sections involving statistical modeling, it’s important to treat these as categorical groups rather than plain text. This also helps improve both efficiency and performance during analysis.

# Check initial structure
str(urban_population_subset)

tibble [212 × 10] (S3: tbl_df/tbl/data.frame)
 $ Country Name  : chr [1:212] "Tajikistan" "Guam" "Tonga" "Togo" ...
 $ Country Code  : chr [1:212] "TJK" "GUM" "TON" "TGO" ...
 $ Indicator Name: chr [1:212] "Urban population" "Urban population" "Urban population" "Urban population" ...
 $ Indicator Code: chr [1:212] "SP.URB.TOTL" "SP.URB.TOTL" "SP.URB.TOTL" "SP.URB.TOTL" ...
 $ 2018          : num [1:212] 2525452 154187 24419 3444076 3806285 ...
 $ 2019          : num [1:212] 2602538 153489 24417 3575477 3923374 ...
 $ 2020          : num [1:212] 2681645 153950 24417 3710640 4052917 ...
 $ 2021          : num [1:212] 2763425 155568 24372 3849488 4187222 ...
 $ 2022          : num [1:212] 2847764 157071 24291 3992304 4320041 ...
 $ 2023          : num [1:212] 2933456 158457 24225 4139500 4455286 ...

# Convert categorical variables to factors
urban_population_subset <- urban_population_subset %>%
  mutate(
    `Country Name` = as.factor(`Country Name`),
    `Country Code` = as.factor(`Country Code`),
    `Indicator Name` = as.factor(`Indicator Name`),
    `Indicator Code` = as.factor(`Indicator Code`)
  )

# View structure after conversion
glimpse(urban_population_subset)

Rows: 212
Columns: 10
$ `Country Name`   <fct> "Tajikistan", "Guam", "Tonga", "Togo", "Cambodia", "Liechtenstein",…
$ `Country Code`   <fct> TJK, GUM, TON, TGO, KHM, LIE, TUR, JOR, PSS, PYF, RWA, DEU, MRT, AB…
$ `Indicator Name` <fct> Urban population, Urban population, Urban population, Urban populat…
$ `Indicator Code` <fct> SP.URB.TOTL, SP.URB.TOTL, SP.URB.TOTL, SP.URB.TOTL, SP.URB.TOTL, SP…
$ `2018`           <dbl> 2525452, 154187, 24419, 3444076, 3806285, 5484, 61171815, 9518501, …
$ `2019`           <dbl> 2602538, 153489, 24417, 3575477, 3923374, 5542, 62454830, 9733085, …
$ `2020`           <dbl> 2681645, 153950, 24417, 3710640, 4052917, 5608, 63459911, 9932774, …
$ `2021`           <dbl> 2763425, 155568, 24372, 3849488, 4187222, 5670, 64430760, 10139659,…
$ `2022`           <dbl> 2847764, 157071, 24291, 3992304, 4320041, 5741, 65453229, 10336289,…
$ `2023`           <dbl> 2933456, 158457, 24225, 4139500, 4455286, 5825, 66096052, 10526364,…

# View summary statistics
summary(urban_population_subset)

                      Country Name  Country Code          Indicator Name     Indicator Code
 Afghanistan                :  1   ABW    :  1   Urban population:212    SP.URB.TOTL:212   
 Africa Eastern and Southern:  1   AFE    :  1                                             
 Africa Western and Central :  1   AFG    :  1                                             
 Albania                    :  1   AFW    :  1                                             
 Algeria                    :  1   AGO    :  1                                             
 American Samoa             :  1   ALB    :  1                                             
 (Other)                    :206   (Other):206                                             
      2018                2019                2020                2021          
 Min.   :5.484e+03   Min.   :5.542e+03   Min.   :5.608e+03   Min.   :5.670e+03  
 1st Qu.:8.644e+05   1st Qu.:8.737e+05   1st Qu.:8.827e+05   1st Qu.:8.915e+05  
 Median :5.559e+06   Median :5.715e+06   Median :5.799e+06   Median :5.924e+06  
 Mean   :1.714e+08   Mean   :1.751e+08   Mean   :1.788e+08   Mean   :1.822e+08  
 3rd Qu.:3.857e+07   3rd Qu.:3.937e+07   3rd Qu.:4.016e+07   3rd Qu.:4.090e+07  
 Max.   :4.239e+09   Max.   :4.317e+09   Max.   :4.396e+09   Max.   :4.465e+09  
 NA's   :2           NA's   :2           NA's   :2           NA's   :2          
      2022                2023          
 Min.   :5.741e+03   Min.   :5.825e+03  
 1st Qu.:9.032e+05   1st Qu.:9.184e+05  
 Median :6.055e+06   Median :6.186e+06  
 Mean   :1.857e+08   Mean   :1.894e+08  
 3rd Qu.:4.126e+07   3rd Qu.:4.150e+07  
 Max.   :4.538e+09   Max.   :4.614e+09  
 NA's   :2           NA's   :2          

str(urban_population_subset)

tibble [212 × 10] (S3: tbl_df/tbl/data.frame)
 $ Country Name  : Factor w/ 212 levels "Afghanistan",..: 186 71 191 190 30 109 194 93 145 62 ...
 $ Country Code  : Factor w/ 212 levels "ABW","AFE","AFG",..: 184 71 189 182 97 108 193 93 155 156 ...
 $ Indicator Name: Factor w/ 1 level "Urban population": 1 1 1 1 1 1 1 1 1 1 ...
 $ Indicator Code: Factor w/ 1 level "SP.URB.TOTL": 1 1 1 1 1 1 1 1 1 1 ...
 $ 2018          : num [1:212] 2525452 154187 24419 3444076 3806285 ...
 $ 2019          : num [1:212] 2602538 153489 24417 3575477 3923374 ...
 $ 2020          : num [1:212] 2681645 153950 24417 3710640 4052917 ...
 $ 2021          : num [1:212] 2763425 155568 24372 3849488 4187222 ...
 $ 2022          : num [1:212] 2847764 157071 24291 3992304 4320041 ...
 $ 2023          : num [1:212] 2933456 158457 24225 4139500 4455286 ...

Understand the Urban Development subset dataset.

I followed the same thing for this dataset as well.

# View structure of the data set
str(urban_development_subset)

tibble [3,617 × 10] (S3: tbl_df/tbl/data.frame)
 $ Country Name  : chr [1:3617] "Malawi" "Georgia" "Puerto Rico" "Central Europe and the Baltics" ...
 $ Country Code  : chr [1:3617] "MWI" "GEO" "PRI" "CEB" ...
 $ Indicator Name: chr [1:3617] "Population in largest city" "Urban population living in areas where elevation is below 5 meters (% of total population)" "Urban population living in areas where elevation is below 5 meters (% of total population)" "Population in urban agglomerations of more than 1 million (% of total population)" ...
 $ Indicator Code: chr [1:3617] "EN.URB.LCTY" "EN.POP.EL5M.UR.ZS" "EN.POP.EL5M.UR.ZS" "EN.URB.MCTY.TL.ZS" ...
 $ 2018          : num [1:3617] 1029639 NA NA NA NA ...
 $ 2019          : num [1:3617] 1074695 NA NA NA NA ...
 $ 2020          : num [1:3617] 1121720 NA NA NA NA ...
 $ 2021          : num [1:3617] 1170870 NA NA NA NA ...
 $ 2022          : num [1:3617] 1222325 NA NA NA NA ...
 $ 2023          : num [1:3617] 1276316 NA NA NA NA ...

# Convert categorical variables to factor type
urban_development_subset <- urban_development_subset %>%
  mutate(
    `Country Name` = as.factor(`Country Name`),
    `Country Code` = as.factor(`Country Code`),
    `Indicator Name` = as.factor(`Indicator Name`),
    `Indicator Code` = as.factor(`Indicator Code`)
  )

# Re-check structure after type conversion
glimpse(urban_development_subset)

Rows: 3,617
Columns: 10
$ `Country Name`   <fct> "Malawi", "Georgia", "Puerto Rico", "Central Europe and the Baltics…
$ `Country Code`   <fct> MWI, GEO, PRI, CEB, QAT, CUW, BEN, VEN, BWA, ESP, CEB, BEN, CHE, KH…
$ `Indicator Name` <fct> "Population in largest city", "Urban population living in areas whe…
$ `Indicator Code` <fct> EN.URB.LCTY, EN.POP.EL5M.UR.ZS, EN.POP.EL5M.UR.ZS, EN.URB.MCTY.TL.Z…
$ `2018`           <dbl> 1.029639e+06, NA, NA, NA, NA, NA, 4.731200e+01, 3.820000e+01, 6.944…
$ `2019`           <dbl> 1.074695e+06, NA, NA, NA, NA, NA, 4.786100e+01, 3.900000e+01, 7.017…
$ `2020`           <dbl> 1.121720e+06, NA, NA, NA, NA, NA, 4.841500e+01, NA, 7.087700e+01, N…
$ `2021`           <dbl> 1.170870e+06, NA, NA, NA, NA, NA, 4.897200e+01, NA, 7.156000e+01, N…
$ `2022`           <dbl> 1.222325e+06, NA, NA, NA, NA, NA, 4.953400e+01, NA, 7.222400e+01, N…
$ `2023`           <dbl> 1.276316e+06, NA, NA, NA, NA, NA, 5.010000e+01, NA, 7.286700e+01, N…

# View summary statistics
summary(urban_development_subset)

       Country Name   Country Code 
 Cuba        :  17   CUB    :  17  
 Gabon       :  17   GAB    :  17  
 Kiribati    :  17   KIR    :  17  
 Sierra Leone:  17   SLE    :  17  
 Bulgaria    :  16   ARE    :  16  
 Canada      :  16   BGR    :  16  
 (Other)     :3517   (Other):3517  
                                                                                    Indicator Name
 Population density (people per sq. km of land area)                                       : 223  
 Urban land area where elevation is below 5 meters (sq. km)                                : 220  
 Access to electricity, urban (% of urban population)                                      : 218  
 Mortality caused by road traffic injury (per 100,000 population)                          : 218  
 Population in the largest city (% of urban population)                                    : 218  
 Urban population living in areas where elevation is below 5 meters (% of total population): 218  
 (Other)                                                                                   :2302  
           Indicator Code      2018                 2019                 2020           
 EN.POP.DNST      : 223   Min.   :        -4   Min.   :        -3   Min.   :        -3  
 AG.LND.EL5M.UR.K2: 220   1st Qu.:        17   1st Qu.:        17   1st Qu.:        20  
 EG.ELC.ACCS.UR.ZS: 218   Median :        46   Median :        46   Median :        55  
 EN.POP.EL5M.UR.ZS: 218   Mean   :  18496456   Mean   :  19784247   Mean   :  21371607  
 EN.URB.LCTY.UR.ZS: 218   3rd Qu.:       100   3rd Qu.:       139   3rd Qu.:       264  
 SH.STA.TRAF.P5   : 218   Max.   :4238877472   Max.   :4317201037   Max.   :4395641624  
 (Other)          :2302   NA's   :1596         NA's   :1688         NA's   :1795        
      2021                 2022                 2023           
 Min.   :       -11   Min.   :        -7   Min.   :        -8  
 1st Qu.:        23   1st Qu.:        23   1st Qu.:        21  
 Median :        78   Median :        79   Median :        79  
 Mean   :  25669620   Mean   :  26263438   Mean   :  31214010  
 3rd Qu.:    173656   3rd Qu.:    205704   3rd Qu.:   1113224  
 Max.   :4464635019   Max.   :4538431007   Max.   :4614425176  
 NA's   :2072         NA's   :2079         NA's   :2299        

str(urban_development_subset)

tibble [3,617 × 10] (S3: tbl_df/tbl/data.frame)
 $ Country Name  : Factor w/ 266 levels "Afghanistan",..: 150 88 200 43 201 56 24 259 29 224 ...
 $ Country Code  : Factor w/ 266 levels "ABW","AFE","AFG",..: 169 83 193 37 201 52 19 255 34 71 ...
 $ Indicator Name: Factor w/ 17 levels "Access to electricity, urban (% of urban population)",..: 6 17 17 9 10 13 15 2 15 12 ...
 $ Indicator Code: Factor w/ 17 levels "AG.LND.EL5M.UR.K2",..: 10 8 8 13 9 1 17 14 17 2 ...
 $ 2018          : num [1:3617] 1029639 NA NA NA NA ...
 $ 2019          : num [1:3617] 1074695 NA NA NA NA ...
 $ 2020          : num [1:3617] 1121720 NA NA NA NA ...
 $ 2021          : num [1:3617] 1170870 NA NA NA NA ...
 $ 2022          : num [1:3617] 1222325 NA NA NA NA ...
 $ 2023          : num [1:3617] 1276316 NA NA NA NA ...

Tidy & Manipulate Data I

The original data sets aren’t in a tidy format, as mainly the ‘Year’ column doesn’t represent a single variable which goes against the principles of tidy data.

Repeating ‘Urban population’ cell data in ‘indicator Name’ doesn’t mean the data set untidy. Therefore, I am only dealing with urban population only it’s okay and safe to drop the column to make the data set more concise.

Additionally, in the urban development the ‘Indicator Name’ column contains multiple types of information, which should be separated into distinct variables.

To fix this, I’ll start by using gather(). This will turn the separate year columns into a single ‘Year’ column, with the corresponding values in another column as ‘Population’

#I combined all the year columns into one using the gather() function. I also removed unnecessary columns like "Country Code" and "Indicator Code" to keep the data cleaner and more focused.

population_gather <- urban_population_subset %>% gather(5:10, key = "Year", value = "Population") %>%  select(c(1, 3, 5, 6)) 

#Repeating 'Urban population' cell data in "indicator Name" doesn't mean the data set untidy. Therefore, I am only dealing with urban population only it's okay and safe to drop the column to make the data set more concise.

population_new_gather <- population_gather %>%
  select(-`Indicator Name`)

head(population_new_gather)

NA

Looking at the modified urban population data set, we can now see that it contains only three remaining variables.

Next, the “Indicator Name” column in the urban development.csv will be separated into individual variables using the spread() function.

#Same as before
development_gather <- urban_development_subset %>% gather(5:10, key = "Year", value = "Development") %>%  select(c(1, 3, 5, 6)) 

#Split the "Indicator Name" into multiple columns using spread()
development_spread <- spread(development_gather, key = "Indicator Name", value = "Development")

head(development_spread)

NA

Understand II

Converting the “Year” variable to an ordered factor is important especially in the context of data analysis and visualization.

#The “Year” variable should be a ordered factor variable.

population_new_gather$Year <- population_new_gather$Year %>% factor(levels = c("2018", "2019", "2020", "2021", "2022", "2023"), ordered = TRUE)

levels(population_new_gather$Year)

[1] "2018" "2019" "2020" "2021" "2022" "2023"

str(population_new_gather)

tibble [1,272 × 3] (S3: tbl_df/tbl/data.frame)
 $ Country Name: Factor w/ 212 levels "Afghanistan",..: 186 71 191 190 30 109 194 93 145 62 ...
 $ Year        : Ord.factor w/ 6 levels "2018"<"2019"<..: 1 1 1 1 1 1 1 1 1 1 ...
 $ Population  : num [1:1272] 2525452 154187 24419 3444076 3806285 ...

#The “Year” variable should be a ordered factor variable.

development_spread$Year <- development_spread$Year %>% factor(levels = c("2018", "2019", "2020", "2021", "2022", "2023"), ordered = TRUE)

levels(development_spread$Year)

[1] "2018" "2019" "2020" "2021" "2022" "2023"

str(development_spread)

tibble [1,596 × 19] (S3: tbl_df/tbl/data.frame)
 $ Country Name                                                                                : Factor w/ 266 levels "Afghanistan",..: 1 1 1 1 1 1 2 2 2 2 ...
 $ Year                                                                                        : Ord.factor w/ 6 levels "2018"<"2019"<..: 1 2 3 4 5 6 1 2 3 4 ...
 $ Access to electricity, urban (% of urban population)                                        : num [1:1596] 98.8 99.5 99.5 99.5 95.9 ...
 $ Mortality caused by road traffic injury (per 100,000 population)                            : num [1:1596] 14.2 15.9 NA NA NA ...
 $ PM2.5 air pollution, mean annual exposure (micrograms per cubic meter)                      : num [1:1596] 67.2 58.3 46.1 NA NA ...
 $ PM2.5 air pollution, population exposed to levels exceeding WHO guideline value (% of total): num [1:1596] NA NA NA NA NA NA NA NA NA NA ...
 $ Population density (people per sq. km of land area)                                         : num [1:1596] 56.3 58 59.9 61.3 62.2 ...
 $ Population in largest city                                                                  : num [1:1596] NA NA NA NA NA NA NA NA NA NA ...
 $ Population in the largest city (% of urban population)                                      : num [1:1596] 42.8 42.2 41.5 41.2 41.3 ...
 $ Population in urban agglomerations of more than 1 million                                   : num [1:1596] 4011770 4114030 4221532 4335770 4457882 ...
 $ Population in urban agglomerations of more than 1 million (% of total population)           : num [1:1596] NA NA NA NA NA NA NA NA NA NA ...
 $ Population living in slums (% of urban population)                                          : num [1:1596] 73.3 NA 73.3 NA NA ...
 $ Urban land area (sq. km)                                                                    : num [1:1596] NA NA NA NA NA NA NA NA NA NA ...
 $ Urban land area where elevation is below 5 meters (% of total land area)                    : num [1:1596] NA NA NA NA NA NA NA NA NA NA ...
 $ Urban land area where elevation is below 5 meters (sq. km)                                  : num [1:1596] NA NA NA NA NA NA NA NA NA NA ...
 $ Urban population                                                                            : num [1:1596] 9367638 9749465 10168092 10525708 10800465 ...
 $ Urban population (% of total population)                                                    : num [1:1596] 25.5 25.8 26 26.3 26.6 ...
 $ Urban population growth (annual %)                                                          : num [1:1596] 3.88 4 4.2 3.46 2.58 ...
 $ Urban population living in areas where elevation is below 5 meters (% of total population)  : num [1:1596] NA NA NA NA NA NA NA NA NA NA ...

Tidy & Manipulate Data I Cont.

Now as both data sets are in a tidy format, it will be merged using a left join based on the common variables “Country Name” and “Year”. A left join ensures that all observations from the population data set are retained, while matching values from the development data set are added wherever available.

#Merge using natural join
population_development <- population_new_gather %>%
  left_join(development_spread, by = c("Country Name", "Year"))

head(population_development)

#choosing the columns 
population_development2 <- population_development %>% select(c(1, 2, 3, 4))

str(population_development2)

tibble [1,272 × 4] (S3: tbl_df/tbl/data.frame)
 $ Country Name                                        : Factor w/ 266 levels "Afghanistan",..: 186 71 191 190 30 109 194 93 145 62 ...
 $ Year                                                : Ord.factor w/ 6 levels "2018"<"2019"<..: 1 1 1 1 1 1 1 1 1 1 ...
 $ Population                                          : num [1:1272] 2525452 154187 24419 3444076 3806285 ...
 $ Access to electricity, urban (% of urban population): num [1:1272] NA 100 99.8 83.1 NA ...

Variable Description: In the data frame there are only 4 remaining variables it has 1272 observations:

Column Name	Description
Country Name	There are 266 countries.
Year.	An ordered factor variable to show the order in which the data was collected, from the oldest year to the most recent
Population	Total population of a country in any given year
Access to electricity, urban (% of urban population)	The percentage of population with access to electricity in any given year.

Tidy & Manipulate Data II

In this section, I’ll calculate the total number of people with access to electricity in each country for the year 2020.

Calculation is as below.

# Create a new column for total number with access to electricity in 2020
electricity_2020 <- population_development2 %>%
  filter(Year == 2020) %>% select(1,3,4) %>%
  mutate(
    Population_with_electricity = Population * `Access to electricity, urban (% of urban population)` / 100
  )

# View the result
head(electricity_2020)

str(electricity_2020)

tibble [212 × 4] (S3: tbl_df/tbl/data.frame)
 $ Country Name                                        : Factor w/ 266 levels "Afghanistan",..: 186 71 191 190 30 109 194 93 145 62 ...
 $ Population                                          : num [1:212] 2681645 153950 24417 3710640 4052917 ...
 $ Access to electricity, urban (% of urban population): num [1:212] NA 100 100 94 NA ...
 $ Population_with_electricity                         : num [1:212] NA 153950 24417 3488002 NA ...

Scan I

In this section, I checked the data for any missing values, unusual entries, or inconsistencies.

#missing values
sum(is.na(electricity_2020))

[1] 84

colSums(is.na(electricity_2020))

                                        Country Name 
                                                   0 
                                          Population 
                                                   2 
Access to electricity, urban (% of urban population) 
                                                  41 
                         Population_with_electricity 
                                                  41 

#special values
sapply(electricity_2020, function(x) sum(is.infinite(x) | is.nan(x)))

                                        Country Name 
                                                   0 
                                          Population 
                                                   0 
Access to electricity, urban (% of urban population) 
                                                   0 
                         Population_with_electricity 
                                                   0 

There are no infinite or NaN values in the data set.

But there are a few missing values (NAs). Since all columns except Country Name are numeric, any missing entries in those columns are automatically treated as NA. From the data scan, we can see a total of 84 missing values across the three main variables.

#Countries which are missing population data
electricity_2020[which(is.na(electricity_2020$'Population')), "Country Name"]

#Countries which are missing 'Access to electricity' data
electricity_2020[which(is.na(electricity_2020$`Access to electricity, urban (% of urban population)`)), "Country Name"]

#Countries which are missing 'Population_with_electricity' data
electricity_2020[which(is.na(electricity_2020$Population_with_electricity)), "Country Name"]

An obvious error can be seen in the country name ‘Not classified’. So, it appears to be no data for this. Therefore, I decided to remove that row. It will not affect for my analysis.

For the remaining NA values which includes numerical values are replaced with the mean.

electricity_2020_clean <- electricity_2020 %>%
  filter(`Country Name` != "Not classified")

#Replace the missing value(s) of population with the mean
electricity_2020_clean$Population <- impute(electricity_2020_clean$Population, fun = mean) 

#Replace the missing value(s) of Access to electricity with the mean
electricity_2020_clean$`Access to electricity, urban (% of urban population)` <- impute(electricity_2020_clean$`Access to electricity, urban (% of urban population)`, fun = mean) 

#Replace the missing value(s) of population with electricity, with the mean
electricity_2020_clean$Population_with_electricity <- impute(electricity_2020_clean$Population_with_electricity, fun = mean) 

#Remaining missing values
colSums(is.na(electricity_2020_clean))

                                        Country Name 
                                                   0 
                                          Population 
                                                   0 
Access to electricity, urban (% of urban population) 
                                                   0 
                         Population_with_electricity 
                                                   0 

Now seems to be okay with the NA values.

Scan II

Before handling the outliers, it is important to understand the distribution of your variables. The best first step is to use Histogram to visually inspect whether the data is normally distributed or skewed.

# Histogram for Population
ggplot(electricity_2020_clean, aes(x = Population)) +
  geom_histogram(binwidth = 1000000, fill = "blue", color = "black") +
  labs(title = "Distribution of Urban Population", x = "Population", y = "Frequency")

# Histogram for Access to Electricity (%)
ggplot(electricity_2020_clean, aes(x = `Access to electricity, urban (% of urban population)`)) +
  geom_histogram(binwidth = 5, fill = "green", color = "black") +
  labs(title = "Distribution of Urban Electricity Access", x = "Percentage", y = "Frequency")

# Histogram for Computed Variable: Population with Electricity
ggplot(electricity_2020_clean, aes(x = Population_with_electricity)) +
  geom_histogram(binwidth = 1000000, fill = "purple", color = "black") +
  labs(title = "Distribution of People with Electricity", x = "Population with Access", y = "Frequency")

NA
NA

1. Distribution of Urban Population The histogram is highly right-skewed (positively skewed). Most countries have a relatively small urban population, concentrated near the left side of the graph. A few countries have very large urban populations, extending the distribution to the right with long, low-frequency bars.

2. Distribution of Urban Electricity Access The histogram is left-skewed (negatively skewed), with the tail extending toward lower percentages. A large number of countries have very high access rates (close to 100%). Very few countries have low access rates (below 50%), shown as smaller bars on the left.

3. Distribution of People with Electricity The histogram is highly right-skewed, with a long tail stretching toward higher values. The majority of countries have low to moderate values of urban population with electricity access. A small number of countries have extremely large values, driving the skew.

Since the observation clearly displays all the three types are heavily skewed.

To detect outliers in this task, the best way is Turkey’s method (IQR).

#Plots variables using box plots

electricity_2020_clean$Population %>% boxplot(ylab="Population")

electricity_2020_clean$`Access to electricity, urban (% of urban population)` %>% boxplot(ylab="Access to electricity (% of population)")

electricity_2020_clean$Population_with_electricity%>% boxplot(ylab="No of people with access to electricity")

From the box plots, each contains multiple outliers. Classical statistics are sensitive to extreme values.

I have chosen to apply capping/Winsorising. This method limits extreme values by replacing them with a specified percentile (ex: the 5th and 95th percentiles).

#stakoverflow: https://stackoverflow.com/questions/13339685/how-to-replace-outliers-with-the-5th-and-95th-percentile-values-in-r?utm_medium=organic&utm_sourcegoogle_rich_qa&utm_campaign=google_rich_qa

# Define a function to cap the values outside the limits

cap <- function(x){
    quantiles <- quantile( x, c(.05, 0.25, 0.75, .95 ) )
    x[ x < quantiles[2] - 1.5*IQR(x) ] <- quantiles[1]
    x[ x > quantiles[3] + 1.5*IQR(x) ] <- quantiles[4]
    x}

#capping function

population_capped <- electricity_2020_clean$Population %>% cap()
boxplot(population_capped, ylab="Population")

electricity_capped <- electricity_2020_clean$`Access to electricity, urban (% of urban population)` %>% cap()
boxplot(electricity_capped, ylab="Access to electricity (% of population)")

people_electricity_capped <- electricity_2020_clean$Population_with_electricity %>% cap()
boxplot(people_electricity_capped, ylab="No of people with access to electricity")

Transform

Histograms and box plots created during the “Scan II” section, I observed that the variable ‘No of people with access to electricity’ is heavily right-skewed, meaning most of the data is concentrated on the left, with a few extreme values on the right.

hist(electricity_2020_clean$Population, main = "Histogram of non-transformed data")

hist(electricity_2020_clean$`Access to electricity, urban (% of urban population)`, main = "Histogram of non-transformed data")

hist(electricity_2020_clean$Population_with_electricity, main = "Histogram of non-transformed data")

Apply the method of logarithmic transformation to changing the Population into a normal distribution.

Norm_Population<-log10(electricity_2020_clean$Population)
hist(Norm_Population)

The data now follows as a normal distribution much more closely than earlier.

Reflection on Challenges and Ease

• Challenges Faced

  1. Initially, identifying two datasets that were both untidy and had a common variable for joining was challenging. This step required careful consideration of the structure and content of potential datasets.
  2. I took guidance from my lecturer (Dr. Sona), who provided valuable feedback on appropriate dataset characteristics. This helped me make a more confident on my selection.
  3. While the concept of un-tidy data was explained in the course, applying it practically, especially transforming data using gather(), spread(), and joining techniques took some experimentation and review of the course materials.

• Ease and Strengths

  1. Once the datasets were selected and the initial transformation understood, the rest of the task became easier. The step by step structure taught in the course aligned well with what was required in the assignment.
  2. Creating histograms using ggplot2 was one of the easier tasks, and it helped me better understand the distribution of my variables.
  3. I found using R, particularly functions from the tidyverse like mutate(), select(), and left_join(), relatively straightforward thanks to consistent practice during lectorials and tutorails.

• Insights and Learning

  1. I learned that careful dataset selection and clear planning are crucial before beginning data transformation.
  2. This task strengthened my understanding of tidy data principles and enhanced my ability to apply them confidently using R.

References

1. Dr. Sona (2025) ‘Module 1’, [Module 1 Notes, MATH2349], RMIT University, Melbourne, accessed May 25 https://data-wrangling.stem.melbourne/module_01
2. Dr. Sona (2025) ‘Module 2’, [Module 1 Notes, MATH2349], RMIT University, Melbourne, accessed May 25 https://data-wrangling.stem.melbourne/module_02
3. Dr. Sona (2025) ‘Module 3’, [Module 1 Notes, MATH2349], RMIT University, Melbourne, accessed May 25 https://data-wrangling.stem.melbourne/module_03
4. Dr. Sona (2025) ‘Module 4’, [Module 1 Notes, MATH2349], RMIT University, Melbourne, accessed May 26 https://data-wrangling.stem.melbourne/module_04
5. Dr. Sona (2025) ‘Module 5’, [Module 1 Notes, MATH2349], RMIT University, Melbourne, accessed May 26 https://data-wrangling.stem.melbourne/module_05
6. Dr. Sona (2025) ‘Module 6’, [Module 1 Notes, MATH2349], RMIT University, Melbourne, accessed May 26 https://data-wrangling.stem.melbourne/module_06
7. Dr. Sona (2025) ‘Module 7’, [Module 1 Notes, MATH2349], RMIT University, Melbourne, accessed May 26 https://data-wrangling.stem.melbourne/module_07
8. Stack overflow (2025), Stack overflow website, accessed 26 May 2025 https://stackoverflow.com/questions/13339685/how-to-replace-outliers-with-the-5th-and-95th-percentile-values-in-r?utm_medium=organic&utm_sourcegoogle_rich_qa&utm_campaign=google_rich_qa
9. Urban population (2023) Worldbank.org, accessed May 25, 2025, https://data.worldbank.org/indicator/SP.URB.TOTL?end=2023&start=2000 Released under a Creative Commons Attribution BY-4.0 license.
10. Urban Development | Data (2023) Worldbank.org, accessed May 25, 2025, https://data.worldbank.org/topic/urban-development. Released under a Creative Commons Attribution BY-4.0 license.
11. R Core Team (2021). R: A language and environment for statistical computing. R Foundation for Statistical Computing, Vienna, Austria, accessed 27 May 2025 https://www.R-project.org/.

Presentation

[Data Wrangling Assignment 2 - s4150303]

Link: https://rmit-arc.instructuremedia.com/embed/40201e92-ab45-41b5-96d7-3b7e9dba84d8