Data-607-Project2-CodeBase.knit

Introduction

The objective of this project was to gain practical experience in transforming wide-format datasets into tidy datasets suitable for analysis and visualization. In many real-world datasets, related variables are stored across multiple columns, which can make the data more difficult to analyze. Following the tidy data principles described by Hadley Wickham, the datasets were organized so that each variable formed a column, each observation formed a row, and each type of observational unit was represented appropriately.

Using the tidyr and dplyr packages in R, this project demonstrated how wide-format datasets could be transformed into tidy formats through reproducible data transformation workflows.

Each dataset was first examined in its original format and then reshaped into a structure that was easier to analyze, summarize, and visualize.

Three independent datasets from Discussion 5A were used in this project:

Each dataset represented a different data transformation scenario. By restructuring these datasets into tidy formats, the project demonstrated how appropriate data organization supported meaningful analysis and visualization.

Dataset 1: COVID World Vaccination Progress

Data Source

Dataset: COVID World Vaccination Progress

Source: Kaggle

Link: COVID World Vaccination Progress

This dataset was used to examine COVID-19 vaccination progress across countries. It contained several vaccination-related measures, including total vaccinations, the number of people vaccinated, the number of people fully vaccinated, and daily vaccination rates.

The dataset provided an opportunity to demonstrate how multiple vaccination measures could be reorganized into a tidy format. After the transformation, the data was easier to compare across countries and analyze over time using R.

Data Loading and Initial Inspection

covid_url <- "https://raw.githubusercontent.com/BIKASHBHOWMIK15/Data-607/main/Project-2/COVID-19-World-Vaccination.csv"

Data Structure Before Tidying

The dataset contained several vaccination-related measures stored in separate columns. Although these columns provided useful information, similar types of measurements were spread across multiple variables rather than being represented by a single variable identifying the vaccination metric.

This structure was treated as a partially wide format because different vaccination measures were stored in separate columns.

covid_raw <- read_csv(covid_url)

dim(covid_raw)

[1] 86512    15

names(covid_raw)

 [1] "country"                             "iso_code"                           
 [3] "date"                                "total_vaccinations"                 
 [5] "people_vaccinated"                   "people_fully_vaccinated"            
 [7] "daily_vaccinations_raw"              "daily_vaccinations"                 
 [9] "total_vaccinations_per_hundred"      "people_vaccinated_per_hundred"      
[11] "people_fully_vaccinated_per_hundred" "daily_vaccinations_per_million"     
[13] "vaccines"                            "source_name"                        
[15] "source_website"

glimpse(covid_raw)

Rows: 86,512
Columns: 15
$ country                             <chr> "Afghanistan", "Afghanistan", "Afg…
$ iso_code                            <chr> "AFG", "AFG", "AFG", "AFG", "AFG",…
$ date                                <date> 2021-02-22, 2021-02-23, 2021-02-2…
$ total_vaccinations                  <dbl> 0, NA, NA, NA, NA, NA, 8200, NA, N…
$ people_vaccinated                   <dbl> 0, NA, NA, NA, NA, NA, 8200, NA, N…
$ people_fully_vaccinated             <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA…
$ daily_vaccinations_raw              <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA…
$ daily_vaccinations                  <dbl> NA, 1367, 1367, 1367, 1367, 1367, …
$ total_vaccinations_per_hundred      <dbl> 0.00, NA, NA, NA, NA, NA, 0.02, NA…
$ people_vaccinated_per_hundred       <dbl> 0.00, NA, NA, NA, NA, NA, 0.02, NA…
$ people_fully_vaccinated_per_hundred <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA…
$ daily_vaccinations_per_million      <dbl> NA, 34, 34, 34, 34, 34, 34, 40, 45…
$ vaccines                            <chr> "Johnson&Johnson, Oxford/AstraZene…
$ source_name                         <chr> "World Health Organization", "Worl…
$ source_website                      <chr> "https://covid19.who.int/", "https…

covid_raw %>% slice_head(n = 10)

# A tibble: 10 × 15
   country     iso_code date       total_vaccinations people_vaccinated
   <chr>       <chr>    <date>                  <dbl>             <dbl>
 1 Afghanistan AFG      2021-02-22                  0                 0
 2 Afghanistan AFG      2021-02-23                 NA                NA
 3 Afghanistan AFG      2021-02-24                 NA                NA
 4 Afghanistan AFG      2021-02-25                 NA                NA
 5 Afghanistan AFG      2021-02-26                 NA                NA
 6 Afghanistan AFG      2021-02-27                 NA                NA
 7 Afghanistan AFG      2021-02-28               8200              8200
 8 Afghanistan AFG      2021-03-01                 NA                NA
 9 Afghanistan AFG      2021-03-02                 NA                NA
10 Afghanistan AFG      2021-03-03                 NA                NA
# ℹ 10 more variables: people_fully_vaccinated <dbl>,
#   daily_vaccinations_raw <dbl>, daily_vaccinations <dbl>,
#   total_vaccinations_per_hundred <dbl>, people_vaccinated_per_hundred <dbl>,
#   people_fully_vaccinated_per_hundred <dbl>,
#   daily_vaccinations_per_million <dbl>, vaccines <chr>, source_name <chr>,
#   source_website <chr>

The original dataset contained several vaccination measures stored in separate columns, such as total_vaccinations, people_vaccinated, people_fully_vaccinated, and related rate variables. This made the dataset partially wide because similar measurements were spread across multiple columns instead of being represented as values in a single variable. To reshape this structure, the related measurement columns were transformed into two variables: metric and value.

Tidy Data Transformation

covid_tidy <- covid_raw %>%
  mutate(date = as.Date(date)) %>%
  pivot_longer(
    cols = c(
      total_vaccinations,
      people_vaccinated,
      people_fully_vaccinated,
      daily_vaccinations_raw,
      daily_vaccinations,
      total_vaccinations_per_hundred,
      people_vaccinated_per_hundred,
      people_fully_vaccinated_per_hundred,
      daily_vaccinations_per_million
    ),
    names_to = "metric",
    values_to = "value"
  ) %>%
  rename_with(tolower) %>%
  mutate(metric = str_replace_all(metric, "_", " ")) %>%
  filter(!is.na(value))

glimpse(covid_tidy)

Rows: 455,194
Columns: 8
$ country        <chr> "Afghanistan", "Afghanistan", "Afghanistan", "Afghanist…
$ iso_code       <chr> "AFG", "AFG", "AFG", "AFG", "AFG", "AFG", "AFG", "AFG",…
$ date           <date> 2021-02-22, 2021-02-22, 2021-02-22, 2021-02-22, 2021-0…
$ vaccines       <chr> "Johnson&Johnson, Oxford/AstraZeneca, Pfizer/BioNTech, …
$ source_name    <chr> "World Health Organization", "World Health Organization…
$ source_website <chr> "https://covid19.who.int/", "https://covid19.who.int/",…
$ metric         <chr> "total vaccinations", "people vaccinated", "total vacci…
$ value          <dbl> 0.00, 0.00, 0.00, 0.00, 1367.00, 34.00, 1367.00, 34.00,…

covid_tidy %>%
  slice_head(n = 10) %>%
  knitr::kable(
    caption = "First 10 Rows of the Tidied COVID-19 Vaccination Dataset",
    digits = 2,
    align = "c",
    format = "html"
  ) %>%
  kableExtra::kable_styling(
    bootstrap_options = c("striped", "hover", "condensed"),
    full_width = TRUE,
    position = "center"
  )

First 10 Rows of the Tidied COVID-19 Vaccination Dataset
country	iso_code	date	vaccines	source_name	source_website	metric	value
Afghanistan	AFG	2021-02-22	Johnson&Johnson, Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing	World Health Organization	https://covid19.who.int/	total vaccinations	0
Afghanistan	AFG	2021-02-22	Johnson&Johnson, Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing	World Health Organization	https://covid19.who.int/	people vaccinated	0
Afghanistan	AFG	2021-02-22	Johnson&Johnson, Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing	World Health Organization	https://covid19.who.int/	total vaccinations per hundred	0
Afghanistan	AFG	2021-02-22	Johnson&Johnson, Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing	World Health Organization	https://covid19.who.int/	people vaccinated per hundred	0
Afghanistan	AFG	2021-02-23	Johnson&Johnson, Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing	World Health Organization	https://covid19.who.int/	daily vaccinations	1367
Afghanistan	AFG	2021-02-23	Johnson&Johnson, Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing	World Health Organization	https://covid19.who.int/	daily vaccinations per million	34
Afghanistan	AFG	2021-02-24	Johnson&Johnson, Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing	World Health Organization	https://covid19.who.int/	daily vaccinations	1367
Afghanistan	AFG	2021-02-24	Johnson&Johnson, Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing	World Health Organization	https://covid19.who.int/	daily vaccinations per million	34
Afghanistan	AFG	2021-02-25	Johnson&Johnson, Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing	World Health Organization	https://covid19.who.int/	daily vaccinations	1367
Afghanistan	AFG	2021-02-25	Johnson&Johnson, Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing	World Health Organization	https://covid19.who.int/	daily vaccinations per million	34

Validation of the Tidy Dataset

After the transformation, the tidy dataset was validated to confirm that the expected variables were present and that the reshaping process retained the country, date, vaccination metric, and reported value information.

covid_summary <- covid_tidy %>%
  summarise(
    rows_after_tidying = n(),
    countries = n_distinct(country),
    metrics = n_distinct(metric),
    date_min = min(date, na.rm = TRUE),
    date_max = max(date, na.rm = TRUE)
  )

knitr::kable(
  covid_summary,
  caption = "Summary of COVID-19 Vaccination Data After Tidying",
  col.names = c(
    "Rows After Tidying",
    "Number of Countries",
    "Number of Metrics",
    "Earliest Date",
    "Latest Date"
  )
)

Summary of COVID-19 Vaccination Data After Tidying
Rows After Tidying	Number of Countries	Number of Metrics	Earliest Date	Latest Date
455194	223	9	2020-12-02	2022-03-29

The validation confirmed that the dataset was successfully transformed into a tidy structure. The resulting tidy dataset contained eight variables: country, iso_code, date, vaccines, source_name, source_website, metric, and value. The nine vaccination measures were converted from separate columns into the metric and value variables. This structure made the dataset easier to filter, group, summarize, compare, and visualize.

Missing Value Handling

Missing values are common in this dataset because countries reported different vaccination measures on different dates. Rather than imputing missing values, only rows where the reshaped value was missing were removed after the pivot_longer() transformation.

covid_missing <- covid_raw %>%
  mutate(date = as.Date(date)) %>%
  pivot_longer(
    cols = c(
      total_vaccinations,
      people_vaccinated,
      people_fully_vaccinated,
      daily_vaccinations_raw,
      daily_vaccinations,
      total_vaccinations_per_hundred,
      people_vaccinated_per_hundred,
      people_fully_vaccinated_per_hundred,
      daily_vaccinations_per_million
    ),
    names_to = "metric",
    values_to = "value"
  )

covid_missing %>%
  summarise(
    rows_before_removing_missing = n(),
    missing_values = sum(is.na(value)),
    rows_after_removing_missing = sum(!is.na(value))
  )

# A tibble: 1 × 3
  rows_before_removing_missing missing_values rows_after_removing_missing
                         <int>          <int>                       <int>
1                       778608         323414                      455194

The missing-value check identified 323,414 observations with missing values among the nine reshaped vaccination measures. These observations were excluded from the final tidy dataset using filter(!is.na(value)), leaving 455,194 non-missing observations. No missing values were imputed.

Analysis and Visualization

After the dataset was transformed into a tidy format, the analysis focused on examining vaccination progress across countries and over time. Summary statistics and visualizations were used to compare vaccination coverage and identify differences between countries.

The analysis included:

Comparing vaccination coverage across countries
Examining changes in vaccination progress over time
Identifying countries with relatively higher or lower vaccination coverage
Creating visualizations to highlight differences and trends in vaccination progress

covid_latest_full <- covid_tidy %>%
  filter(metric == "people fully vaccinated per hundred") %>%
  group_by(country) %>%
  slice_max(order_by = date, n = 1, with_ties = FALSE) %>%
  ungroup() %>%
  select(
    country,
    latest_date = date,
    fully_vaccinated_per_hundred = value
  ) %>%
  filter(!is.na(fully_vaccinated_per_hundred)) %>%
  arrange(desc(fully_vaccinated_per_hundred))

covid_latest_full %>%
  slice_head(n = 15) %>%
  knitr::kable(
    caption = "Top 15 Countries by Latest Fully Vaccinated per 100 People",
    col.names = c(
      "Country",
      "Latest Date",
      "Fully Vaccinated per 100 People"
    ),
    digits = 2,
    align = "ccc",
    format = "html"
  ) %>%
  kableExtra::kable_styling(
    bootstrap_options = c("striped", "hover", "condensed"),
    full_width = TRUE,
    position = "center"
  )

Top 15 Countries by Latest Fully Vaccinated per 100 People
Country	Latest Date	Fully Vaccinated per 100 People
Gibraltar	2022-03-24	122.37
Pitcairn	2021-09-07	100.00
United Arab Emirates	2022-03-29	96.12
Portugal	2022-03-10	92.60
Brunei	2022-03-18	91.71
Singapore	2022-03-28	91.04
Malta	2022-03-28	90.35
Chile	2022-03-22	90.29
Qatar	2022-03-24	88.50
Cayman Islands	2022-03-25	87.80
Niue	2022-03-13	87.79
Cuba	2022-03-27	87.57
South Korea	2022-03-29	86.70
China	2022-03-24	85.91
Spain	2022-03-23	85.82

The table above lists the countries with the highest latest reported values of people fully vaccinated per hundred. This provides a direct comparison of vaccination coverage across countries using the most recent available data.

Vaccination Coverage Countries

The following bar chart visualizes the top 10 countries by latest fully vaccinated rate.

covid_latest_full %>%
  slice_head(n = 10) %>%
  ggplot(aes(
    x = reorder(country, fully_vaccinated_per_hundred),
    y = fully_vaccinated_per_hundred
  )) +
  geom_col(fill = "#ADD8E6", width = 0.8) +
  geom_text(
    aes(label = round(fully_vaccinated_per_hundred, 1)),
    hjust = 1.2,
    color = "#000080",
    size = 3.5
  ) +
  coord_flip() +
  labs(
    title = "Top 10 Countries by Full Vaccination Coverage",
    x = "Country",
    y = "Fully vaccinated per 100 people"
  ) +
  scale_y_continuous(labels = scales::label_number()) +
  theme_minimal() +
  theme(
    plot.title = element_text(
      color = "#000080",
      face = "bold",
      size = 14
    ),
    axis.title = element_text(color = "#000080", face = "bold"),
    axis.text = element_text(size = 10),
    plot.margin = margin(10, 25, 10, 10)
  )

This plot shows that the latest reported vaccination coverage varied substantially across countries. Gibraltar had the highest reported value in the table at approximately 122 fully vaccinated people per 100, followed by several countries with values above 90 per 100. Values above 100 should be interpreted cautiously because the measure is based on reported vaccination data and may reflect the source dataset’s reporting conventions or differences between vaccination records and population estimates.

Vaccination Progress Over Time

To examine vaccination progress over time, I also compare a small set of countries using the tidy dataset.

selected_countries <- c("United States", "India", "United Kingdom", "Germany", "Brazil")

covid_time_series <- covid_tidy %>%
  filter(
    metric == "people fully vaccinated per hundred",
    country %in% selected_countries
  )

covid_time_series %>%
  ggplot(aes(x = date, y = value, color = country)) +
  geom_line(linewidth = 0.8) +
  labs(
    title = "Vaccination Progress Over Time",
    subtitle = "People fully vaccinated per 100 people",
    x = "Date",
    y = "People fully vaccinated per 100",
    color = "Country"
  ) +
  scale_y_continuous(labels = label_number())

Dataset 2 — Renewable Energy Capacity Time Series

This dataset was used to examine renewable energy generation across different countries and energy sources. It contained information related to several types of renewable energy, including solar, wind, hydro, and other renewable technologies. The dataset provided an opportunity to examine differences in renewable energy production across countries and over time.

Data Source

Dataset: Renewable Power Plants / Renewable Capacity Time Series
Source: Kaggle
Link: Renewable Power Plants / Renewable Capacity Time Series

Data Loading and Initial Inspection

renewable_url <- "https://raw.githubusercontent.com/BIKASHBHOWMIK15/Data-607/main/Project-2/Renewable-Energy-Capacity.csv"

Data Structure Before Tidying

The dataset contained information organized across multiple columns, with different renewable energy sources and country-related information represented separately. Time-related information was also included to describe changes in energy generation over time.

Because related information was distributed across multiple columns, the dataset required restructuring before it could be easily analyzed. The wide format made it more difficult to compare energy sources, countries, and time periods using standard R analysis and visualization functions.

renewable_raw <- read_csv(renewable_url)

dim(renewable_raw)

[1] 44166    40

names(renewable_raw)

 [1] "day"                           "CH_bioenergy_capacity"        
 [3] "CH_solar_capacity"             "CH_wind_onshore_capacity"     
 [5] "DE_bioenergy_capacity"         "DE_geothermal_capacity"       
 [7] "DE_solar_capacity"             "DE_wind_capacity"             
 [9] "DE_wind_offshore_capacity"     "DE_wind_onshore_capacity"     
[11] "DK_solar_capacity"             "DK_wind_capacity"             
[13] "DK_wind_offshore_capacity"     "DK_wind_onshore_capacity"     
[15] "FR_bioenergy_capacity"         "FR_geothermal_capacity"       
[17] "FR_hydro_capacity"             "FR_marine_capacity"           
[19] "FR_solar_capacity"             "FR_wind_onshore_capacity"     
[21] "GB-GBN_bioenergy_capacity"     "GB-GBN_hydro_capacity"        
[23] "GB-GBN_marine_capacity"        "GB-GBN_solar_capacity"        
[25] "GB-GBN_wind_capacity"          "GB-GBN_wind_offshore_capacity"
[27] "GB-GBN_wind_onshore_capacity"  "GB-NIR_bioenergy_capacity"    
[29] "GB-NIR_solar_capacity"         "GB-NIR_wind_onshore_capacity" 
[31] "GB-UKM_bioenergy_capacity"     "GB-UKM_hydro_capacity"        
[33] "GB-UKM_marine_capacity"        "GB-UKM_solar_capacity"        
[35] "GB-UKM_wind_capacity"          "GB-UKM_wind_offshore_capacity"
[37] "GB-UKM_wind_onshore_capacity"  "SE_wind_capacity"             
[39] "SE_wind_offshore_capacity"     "SE_wind_onshore_capacity"

glimpse(renewable_raw)

Rows: 44,166
Columns: 40
$ day                             <date> 1900-01-01, 1900-01-02, 1900-01-03, 1…
$ CH_bioenergy_capacity           <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ CH_solar_capacity               <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ CH_wind_onshore_capacity        <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ DE_bioenergy_capacity           <dbl> 0.00, 0.08, 0.08, 0.08, 0.08, 0.08, 0.…
$ DE_geothermal_capacity          <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ DE_solar_capacity               <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ DE_wind_capacity                <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ DE_wind_offshore_capacity       <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ DE_wind_onshore_capacity        <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ DK_solar_capacity               <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ DK_wind_capacity                <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ DK_wind_offshore_capacity       <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ DK_wind_onshore_capacity        <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ FR_bioenergy_capacity           <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ FR_geothermal_capacity          <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ FR_hydro_capacity               <dbl> 122.496, 122.496, 122.496, 122.496, 12…
$ FR_marine_capacity              <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ FR_solar_capacity               <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ FR_wind_onshore_capacity        <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-GBN_bioenergy_capacity`     <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-GBN_hydro_capacity`         <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-GBN_marine_capacity`        <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-GBN_solar_capacity`         <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-GBN_wind_capacity`          <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-GBN_wind_offshore_capacity` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-GBN_wind_onshore_capacity`  <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-NIR_bioenergy_capacity`     <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-NIR_solar_capacity`         <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-NIR_wind_onshore_capacity`  <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-UKM_bioenergy_capacity`     <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-UKM_hydro_capacity`         <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-UKM_marine_capacity`        <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-UKM_solar_capacity`         <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-UKM_wind_capacity`          <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-UKM_wind_offshore_capacity` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ `GB-UKM_wind_onshore_capacity`  <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ SE_wind_capacity                <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ SE_wind_offshore_capacity       <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…
$ SE_wind_onshore_capacity        <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,…

renewable_raw %>% slice_head(n = 10)

# A tibble: 10 × 40
   day        CH_bioenergy_capacity CH_solar_capacity CH_wind_onshore_capacity
   <date>                     <dbl>             <dbl>                    <dbl>
 1 1900-01-01                     0                 0                        0
 2 1900-01-02                     0                 0                        0
 3 1900-01-03                     0                 0                        0
 4 1900-01-04                     0                 0                        0
 5 1900-01-05                     0                 0                        0
 6 1900-01-06                     0                 0                        0
 7 1900-01-07                     0                 0                        0
 8 1900-01-08                     0                 0                        0
 9 1900-01-09                     0                 0                        0
10 1900-01-10                     0                 0                        0
# ℹ 36 more variables: DE_bioenergy_capacity <dbl>,
#   DE_geothermal_capacity <dbl>, DE_solar_capacity <dbl>,
#   DE_wind_capacity <dbl>, DE_wind_offshore_capacity <dbl>,
#   DE_wind_onshore_capacity <dbl>, DK_solar_capacity <dbl>,
#   DK_wind_capacity <dbl>, DK_wind_offshore_capacity <dbl>,
#   DK_wind_onshore_capacity <dbl>, FR_bioenergy_capacity <dbl>,
#   FR_geothermal_capacity <dbl>, FR_hydro_capacity <dbl>, …

The original dataset is wide because the renewable generation variables are stored as separate columns such as CH_solar_capacity, DE_wind_capacity, and FR_hydro_capacity. In this structure, both the country and the renewable energy type are embedded in the column names rather than represented as separate variables.

Tidy Data Transformation

The dataset was transformed from a wide format into a longer, tidy format. The transformation organized the data so that renewable energy type, country, time period, and generation value were represented as separate variables.

The transformation used pivot_longer() from the tidyr package to combine related columns into appropriate variables. The resulting tidy dataset made it easier to group and summarize the data by country, energy source, and time period.

The transformed dataset included variables such as:

country
energy_type
time
generation

This structure provided a consistent format for further analysis and visualization.

renewable_tidy <- renewable_raw %>%
  mutate(day = as.Date(day)) %>%
  pivot_longer(
    cols = -day,
    names_to = "country_energy",
    values_to = "capacity"
  ) %>%
  extract(
    country_energy,
    into = c("country_code", "energy_type"),
    regex = "^(.*?)_(.*)_capacity$",
    remove = TRUE
  ) %>%
  rename_with(tolower) %>%
  mutate(
    energy_type = str_replace_all(energy_type, "_", " "),
    country_code = str_to_upper(country_code)
  ) %>%
  filter(!is.na(capacity))

glimpse(renewable_tidy)

Rows: 1,722,474
Columns: 4
$ day          <date> 1900-01-01, 1900-01-01, 1900-01-01, 1900-01-01, 1900-01-…
$ country_code <chr> "CH", "CH", "CH", "DE", "DE", "DE", "DE", "DE", "DE", "DK…
$ energy_type  <chr> "bioenergy", "solar", "wind onshore", "bioenergy", "geoth…
$ capacity     <dbl> 0.000, 0.000, 0.000, 0.000, 0.000, 0.000, 0.000, 0.000, 0…

renewable_tidy %>% slice_head(n = 10)

# A tibble: 10 × 4
   day        country_code energy_type   capacity
   <date>     <chr>        <chr>            <dbl>
 1 1900-01-01 CH           bioenergy            0
 2 1900-01-01 CH           solar                0
 3 1900-01-01 CH           wind onshore         0
 4 1900-01-01 DE           bioenergy            0
 5 1900-01-01 DE           geothermal           0
 6 1900-01-01 DE           solar                0
 7 1900-01-01 DE           wind                 0
 8 1900-01-01 DE           wind offshore        0
 9 1900-01-01 DE           wind onshore         0
10 1900-01-01 DK           solar                0

Missing Value Handling

In this dataset, the main inconsistency was that country and energy type were encoded together in the original column names. This structure was normalized by extracting them into separate variables. Missing capacity values were removed after reshaping.

The source dataset began at 1900-01-01, and the date values were retained as provided in the original file to ensure that the transformation pipeline remained fully reproducible.

renewable_tidy %>%
  summarise(
    rows_after_tidying = n(),
    countries = n_distinct(country_code),
    energy_types = n_distinct(energy_type),
    start_date = min(day),
    end_date = max(day)
  )

# A tibble: 1 × 5
  rows_after_tidying countries energy_types start_date end_date  
               <int>     <int>        <int> <date>     <date>    
1            1722474         8            8 1900-01-01 2020-12-02

Analysis and Visualization

The original discussion suggested comparing renewable energy output across energy sources, across countries, and over time. To address this, I summarize total capacity by energy type, total capacity by country, and then aggregate daily totals by energy type to examine long-run time trends.

renewable_by_energy <- renewable_tidy %>%
  group_by(energy_type) %>%
  summarise(
    total_capacity = sum(capacity, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  arrange(desc(total_capacity))

renewable_by_energy

# A tibble: 8 × 2
  energy_type   total_capacity
  <chr>                  <dbl>
1 wind              327252661.
2 wind onshore      318761019.
3 solar             210255953.
4 hydro             129437107.
5 bioenergy          82058286.
6 wind offshore      54058820.
7 marine               222916.
8 geothermal           125056.

Total Installed Renewable Energy Capacity

The summary table above compares total renewable capacity across energy types.

renewable_by_energy %>%
  ggplot(aes(
    x = reorder(energy_type, total_capacity),
    y = total_capacity
  )) +
  geom_col(fill = "lightblue") +
  geom_text(
    aes(label = scales::comma(round(total_capacity, 0), accuracy = 1)),
    hjust = 1.05,
    size = 3,
    color = "black"
  ) +
  coord_flip() +
  labs(
    title = "Total Installed Renewable Energy Capacity by Source",
    x = "Renewable Energy Source",
    y = "Total Capacity"
  ) +
  scale_y_continuous(
    labels = scales::label_comma(accuracy = 1),
    expand = expansion(mult = c(0, 0.05))
  ) +
  theme_minimal()

This bar chart shows that wind related categories contribute the largest total capacity in the dataset. Solar and hydro also make large contributions, while geothermal and marine remain much smaller. This suggests that wind and solar are the dominant renewable technologies across the included countries.

Total Renewable Energy Capacity

Next, I compare total renewable capacity across countries.

# Summarize total renewable energy capacity by country
renewable_by_country <- renewable_tidy %>%
  group_by(country_code) %>%
  summarise(
    total_capacity = sum(capacity, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  arrange(desc(total_capacity))

# Display the summary table
knitr::kable(
  renewable_by_country,
  caption = "Total Renewable Energy Capacity by Country",
  digits = 0,
  format.args = list(big.mark = ",")
)

Total Renewable Energy Capacity by Country
country_code	total_capacity
DE	507,154,526
GB-UKM	214,517,654
GB-GBN	207,324,910
FR	82,867,640
DK	59,064,658
SE	45,247,120
GB-NIR	3,777,263
CH	2,218,045

# Plot total renewable energy capacity by country
renewable_by_country %>%
  ggplot(aes(
    x = reorder(country_code, total_capacity),
    y = total_capacity
  )) +
  geom_col(fill = "lightblue") +
  geom_text(
    aes(label = scales::comma(round(total_capacity, 0))),
    hjust = 1.05,
    size = 3,
    color = "black"
  ) +
  coord_flip() +
  labs(
    title = "Total Renewable Energy Capacity by Country",
    x = "Country Code",
    y = "Total Capacity"
  ) +
  scale_y_continuous(
    labels = scales::label_comma(accuracy = 1),
    expand = expansion(mult = c(0, 0.05))
  ) +
  theme_minimal()

This chart shows that Germany has the highest total renewable capacity in the dataset, followed by major United Kingdom regions. Countries or regions such as Switzerland and Northern Ireland contribute much smaller totals. These differences likely reflect varying levels of investment, infrastructure, and national renewable energy policy.

Finally, I examine how major renewable energy categories evolve over time.

renewable_over_time <- renewable_tidy %>%
  group_by(day, energy_type) %>%
  summarise(
    total_capacity = sum(capacity, na.rm = TRUE),
    .groups = "drop"
  )

renewable_over_time %>%
  ggplot(aes(x = day, y = total_capacity, color = energy_type)) +
  geom_line(linewidth = 0.7) +
  labs(
    title = "Renewable Capacity Over Time by Energy Type",
    x = "Date",
    y = "Total capacity",
    color = "Energy type"
  ) +
  scale_y_continuous(labels = label_comma())

This time-series plot shows how renewable capacity changes over time for each energy type. Wind and solar show strong upward growth patterns, which suggests expanded adoption of these technologies over time. Hydropower remains more stable, likely because major hydro infrastructure changes more slowly once built.

Interpretation and Key Findings

After tidying, the dataset made it possible to compare renewable capacity across countries and energy sources. The summary tables and plots showed which renewable technologies contributed the most total capacity, which countries had the largest totals, and how the overall capacity profile changed over time. The tidy structure made these comparisons much more direct than in the original wide format.

Dataset 3 — GDP by Country (1960–2022)

This dataset was used to examine GDP trends across countries from 1960 through 2022. It contained GDP values for multiple countries, with annual values recorded for different years. The dataset provided an opportunity to demonstrate how time-based data could be transformed from a wide format into a tidy structure.

Data Source

Dataset: World GDP by Country (1960–2022)
Source: Kaggle
Link: World GDP by Country (1960–2022)

Data Loading and Initial Inspection

gdp_url <- "https://raw.githubusercontent.com/BIKASHBHOWMIK15/Data-607/main/Project-2/World-GDP-Country.csv"

Data Structure Before Tidying

gdp_raw <- read_csv(gdp_url)

dim(gdp_raw)

[1] 266  65

names(gdp_raw)

 [1] "Country"      "Country Code" "1960"         "1961"         "1962"        
 [6] "1963"         "1964"         "1965"         "1966"         "1967"        
[11] "1968"         "1969"         "1970"         "1971"         "1972"        
[16] "1973"         "1974"         "1975"         "1976"         "1977"        
[21] "1978"         "1979"         "1980"         "1981"         "1982"        
[26] "1983"         "1984"         "1985"         "1986"         "1987"        
[31] "1988"         "1989"         "1990"         "1991"         "1992"        
[36] "1993"         "1994"         "1995"         "1996"         "1997"        
[41] "1998"         "1999"         "2000"         "2001"         "2002"        
[46] "2003"         "2004"         "2005"         "2006"         "2007"        
[51] "2008"         "2009"         "2010"         "2011"         "2012"        
[56] "2013"         "2014"         "2015"         "2016"         "2017"        
[61] "2018"         "2019"         "2020"         "2021"         "2022"

gdp_raw %>%
  slice_head(n = 15) %>%
  select(Country, `Country Code`, `1960`:`1963`) %>%
  knitr::kable(
    caption = "First 15 Rows of GDP Data (1960–1963)",
    digits = 0,
    format.args = list(big.mark = ",")
  )

First 15 Rows of GDP Data (1960–1963)
Country	Country Code	1960	1961	1962	1963
Aruba	ABW	NA	NA	NA	NA
Africa Eastern and Southern	AFE	21,125,015,452	21,616,228,139	23,506,279,900	28,048,360,188
Afghanistan	AFG	537,777,811	548,888,896	546,666,678	751,111,191
Africa Western and Central	AFW	10,447,637,853	11,173,212,080	11,990,534,018	12,727,688,165
Angola	AGO	NA	NA	NA	NA
Albania	ALB	NA	NA	NA	NA
Andorra	AND	NA	NA	NA	NA
Arab World	ARB	NA	NA	NA	NA
United Arab Emirates	ARE	NA	NA	NA	NA
Argentina	ARG	NA	NA	24,450,604,878	18,272,123,664
Armenia	ARM	NA	NA	NA	NA
American Samoa	ASM	NA	NA	NA	NA
Antigua and Barbuda	ATG	NA	NA	NA	NA
Australia	AUS	18,606,562,979	19,682,883,152	19,922,563,190	21,539,843,449
Austria	AUT	6,592,693,841	7,311,749,633	7,756,110,210	8,374,175,258

gdp_raw %>% slice_head(n = 10)

# A tibble: 10 × 65
   Country  `Country Code`   `1960`   `1961`   `1962`   `1963`   `1964`   `1965`
   <chr>    <chr>             <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>
 1 Aruba    ABW            NA       NA       NA       NA       NA       NA      
 2 Africa … AFE             2.11e10  2.16e10  2.35e10  2.80e10  2.59e10  2.95e10
 3 Afghani… AFG             5.38e 8  5.49e 8  5.47e 8  7.51e 8  8.00e 8  1.01e 9
 4 Africa … AFW             1.04e10  1.12e10  1.20e10  1.27e10  1.39e10  1.49e10
 5 Angola   AGO            NA       NA       NA       NA       NA       NA      
 6 Albania  ALB            NA       NA       NA       NA       NA       NA      
 7 Andorra  AND            NA       NA       NA       NA       NA       NA      
 8 Arab Wo… ARB            NA       NA       NA       NA       NA       NA      
 9 United … ARE            NA       NA       NA       NA       NA       NA      
10 Argenti… ARG            NA       NA        2.45e10  1.83e10  2.56e10  2.83e10
# ℹ 57 more variables: `1966` <dbl>, `1967` <dbl>, `1968` <dbl>, `1969` <dbl>,
#   `1970` <dbl>, `1971` <dbl>, `1972` <dbl>, `1973` <dbl>, `1974` <dbl>,
#   `1975` <dbl>, `1976` <dbl>, `1977` <dbl>, `1978` <dbl>, `1979` <dbl>,
#   `1980` <dbl>, `1981` <dbl>, `1982` <dbl>, `1983` <dbl>, `1984` <dbl>,
#   `1985` <dbl>, `1986` <dbl>, `1987` <dbl>, `1988` <dbl>, `1989` <dbl>,
#   `1990` <dbl>, `1991` <dbl>, `1992` <dbl>, `1993` <dbl>, `1994` <dbl>,
#   `1995` <dbl>, `1996` <dbl>, `1997` <dbl>, `1998` <dbl>, `1999` <dbl>, …

The GDP dataset is wide because each year is stored as a separate column. This means the variable year is embedded in the column names instead of being represented as its own column.

Tidy Data Transformation

The dataset was transformed from wide format into a tidy long format by combining the year columns into a single year variable and placing the corresponding GDP values into a gdp variable.

The transformation was performed using the pivot_longer() function from the tidyr package.

The resulting tidy dataset contained the following variables:

country
year
gdp

This structure made it easier to filter, group, summarize, and visualize GDP data across countries and years.

gdp_tidy <- gdp_raw %>%
  pivot_longer(
    cols = -c(`Country`, `Country Code`),
    names_to = "year",
    values_to = "gdp"
  ) %>%
  rename(
    country = `Country`,
    country_code = `Country Code`
  ) %>%
  rename_with(tolower) %>%
  mutate(
    year = as.integer(year)
  ) %>%
  filter(!is.na(gdp))

glimpse(gdp_tidy)

Rows: 13,365
Columns: 4
$ country      <chr> "Aruba", "Aruba", "Aruba", "Aruba", "Aruba", "Aruba", "Ar…
$ country_code <chr> "ABW", "ABW", "ABW", "ABW", "ABW", "ABW", "ABW", "ABW", "…
$ year         <int> 1986, 1987, 1988, 1989, 1990, 1991, 1992, 1993, 1994, 199…
$ gdp          <dbl> 405586592, 487709497, 596648045, 695530726, 764804469, 87…

gdp_tidy %>% slice_head(n = 10)

# A tibble: 10 × 4
   country country_code  year         gdp
   <chr>   <chr>        <int>       <dbl>
 1 Aruba   ABW           1986  405586592.
 2 Aruba   ABW           1987  487709497.
 3 Aruba   ABW           1988  596648045.
 4 Aruba   ABW           1989  695530726.
 5 Aruba   ABW           1990  764804469.
 6 Aruba   ABW           1991  872067039.
 7 Aruba   ABW           1992  958659218.
 8 Aruba   ABW           1993 1083240223 
 9 Aruba   ABW           1994 1245810056 
10 Aruba   ABW           1995 1320670391

Validation of the Tidy Dataset

The tidy GDP dataset was validated by checking its structure, column names, and data types. The number of rows and observations was reviewed to ensure that the data was reshaped correctly. Country names, year values, and GDP measurements were also checked for missing or invalid values. These checks helped confirm that the dataset was ready for further analysis and visualization.

Missing Value Handling

For this dataset, the main structural issue is that years are spread across columns. I normalize the dataset by reshaping those years into a single year variable. Missing GDP values are removed after reshaping.

gdp_tidy %>%
  summarise(
    rows_after_tidying = n(),
    countries = n_distinct(country),
    min_year = min(year),
    max_year = max(year)
  )

# A tibble: 1 × 4
  rows_after_tidying countries min_year max_year
               <int>     <int>    <int>    <int>
1              13365       262     1960     2022

Analysis and Visualization

The original discussion focused on comparing GDP growth across countries and visualizing economic trends over time. To explore this, I selected five major economies, created a time-series visualization of their GDP trends, compared their average GDP values over the observed period, and calculated a long-term growth multiple using the first and last available GDP observations for each country.

GDP Trends for Selected Countries

gdp_selected <- gdp_tidy %>%
  filter(country %in% c("United States", "China", "Japan", "Germany", "India"))

gdp_selected %>%
  ggplot(aes(x = year, y = gdp, color = country)) +
  geom_line(linewidth = 0.8) +
  labs(
    title = "GDP Trends for Selected Countries",
    x = "Year",
    y = "GDP (current US$)",
    color = "Country"
  ) +
  scale_y_continuous(labels = label_dollar(scale_cut = cut_short_scale()))

The line chart compares GDP trends across five major economies over time. The United States maintains the highest GDP throughout the period, while China experiences particularly rapid growth in the later decades. India also demonstrates a strong upward trend, starting from a lower GDP level. Overall, the chart illustrates differences in economic size and long-term growth patterns among the selected countries.

Average GDP for Selected Countries

Next, I calculate average GDP across the full period for selected countries.

gdp_average <- gdp_selected %>%
  group_by(country) %>%
  summarise(
    average_gdp = mean(gdp, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  arrange(desc(average_gdp))

gdp_average

# A tibble: 5 × 2
  country       average_gdp
  <chr>               <dbl>
1 United States     8.21e12
2 China             3.06e12
3 Japan             2.90e12
4 Germany           2.13e12
5 India             7.41e11

The summary table above shows the average GDP of each selected country across the available years.

gdp_average %>%
  ggplot(aes(
    x = reorder(country, average_gdp),
    y = average_gdp
  )) +
  geom_col(fill = "lightblue") +
  geom_text(
    aes(label = label_dollar(scale_cut = cut_short_scale())(average_gdp)),
    hjust = 1.05,
    size = 3,
    color = "black"
  ) +
  coord_flip() +
  labs(
    title = "Average GDP for Selected Countries",
    x = "Country",
    y = "Average GDP (current US$)"
  ) +
  scale_y_continuous(
    labels = label_dollar(scale_cut = cut_short_scale()),
    expand = expansion(mult = c(0, 0.05))
  ) +
  theme_minimal()

The bar chart indicates that the United States had the highest average GDP over the period, followed by China and Japan. India had the lowest average GDP among the selected countries. This difference may reflect the timing of economic growth, as India’s more substantial expansion occurred in the later years of the dataset.

To evaluate growth over time, I compare the first and last available GDP values for selected countries.

gdp_growth <- gdp_selected %>%
  group_by(country) %>%
  summarise(
    first_year = min(year),
    last_year = max(year),
    first_gdp = gdp[year == first_year][1],
    last_gdp = gdp[year == last_year][1],
    growth_multiple = last_gdp / first_gdp,
    .groups = "drop"
  ) %>%
  arrange(desc(growth_multiple))

knitr::kable(
  gdp_growth,
  caption = "GDP Growth Multiple by Country",
  col.names = c(
    "Country",
    "First Year",
    "Last Year",
    "First GDP",
    "Last GDP",
    "Growth Multiple"
  ),
  digits = 2,
  format.args = list(big.mark = ",")
)

GDP Growth Multiple by Country
Country	First Year	Last Year	First GDP	Last GDP	Growth Multiple
China	1,960	2,022	59,716,251,765	1.796317e+13	300.81
Japan	1,960	2,022	44,307,342,906	4.231141e+12	95.50
India	1,960	2,022	37,029,883,847	3.385090e+12	91.42
United States	1,960	2,022	543,300,000,000	2.546270e+13	46.87
Germany	1,970	2,022	215,838,448,138	4.072192e+12	18.87

The table compares long-term GDP growth across selected countries by calculating the growth multiple between each country’s earliest and latest available GDP values. This comparison highlights the countries that experienced the largest proportional increases in GDP over the observed period.

Interpretation and Key Findings

The tidy GDP dataset allows for easier comparison of economic trends across countries over time. The time-series graph illustrates differences in GDP levels and growth patterns, while the summary tables and bar chart provide insights into average GDP and long-term growth multiples. Overall, reshaping the original wide-format dataset into tidy format makes the data easier to analyze, visualize, and compare across countries.

Conclusion

This project demonstrated how wide-format datasets could be transformed into tidy datasets using R and the tidyr and dplyr packages. The three selected datasets—COVID-19 vaccination progress, renewable energy data, and world GDP data—had different structures, but each contained information that could be reorganized to support further analysis.

Using transformations such as pivot_longer(), the datasets were converted into formats where variables were represented as columns and observations as rows. These transformations made it easier to filter, summarize, compare, and visualize the data using dplyr and ggplot2.

Overall, this project provided practical experience working with real-world datasets and applying tidy data principles. It demonstrated how appropriate data transformations improved data organization, simplified analysis, and prepared the datasets for further exploration and visualization.

DATA 607- Project 2– Data Transformations –CodeBase

Bikash Bhowmik —- 10 Oct 2026