Project 2 — Data Tidying

Author

Daanish Rasheed

Published

October 11, 2026

Data Tidying and Transformation

Approach

This project will tidy three sets of data from the Week 5 Discussion 5A list and answer a question about each: the Motherboard/VICE News Kia and Hyundai theft data (https://docs.google.com/spreadsheets/d/1-6614t_Ata5k7oESrm-HAdF1uOe8aPGRaQECl44GYeE/edit?gid=0#gid=0), the World Bank life expectancy at birth indicator (https://data.worldbank.org/indicator/SP.DYN.LE00.IN) and the Social Security Administration top 5 baby names (https://www.ssa.gov/oact/babynames/top5names.html).

Two of these have two header rows, so I will read in no header, bind the two rows together to make names for the columns and then pivot with tidyr::pivot_longer() and pivot_wider(), using dplyr to clean. All files will be read in as text first, in order to decide how to handle any non-numeric markers (%, .., N/A).

I expect to see different issues in each file. The Kia/Hyundai data has many empty cells, percentages stored as text, month labels like Dec/19 that are not real dates, and inconsistent or misspelled city names. The World Bank file has year headers such as 1990 [YR1990], missing values indicated by .., footer rows that are not data, and regional aggregates mixed in with countries, and the SSA data is clean but sex and rank are stored in the header, and the 1920s and 2020s are incomplete decades.

Code Base

Code

library(readr)
library(tidyr)
library(dplyr)
library(stringr)
library(ggplot2)
library(scales)
library(knitr)

# Raw wide-format files, read straight from GitHub
url_kia  <- "https://raw.githubusercontent.com/daanishrasheed/DATA607/refs/heads/main/Project%202/Motherboard%20VICE%20News%20Kia%20Hyundai%20Theft%20Data%20-%20Data.csv"
url_wb   <- "https://raw.githubusercontent.com/daanishrasheed/DATA607/refs/heads/main/Project%202/LifeExpectancy.csv"
url_ssa  <- "https://raw.githubusercontent.com/daanishrasheed/DATA607/refs/heads/main/Project%202/ssa_top5_names.csv"

Kia and Hyundai Thefts

Code

kia_raw <- read_csv(url_kia, col_names = FALSE,
                    col_types = cols(.default = col_character()))

cat("Raw rows:", nrow(kia_raw), "| raw columns:", ncol(kia_raw), "\n")

Raw rows: 47 | raw columns: 211

Code

kia_raw

# A tibble: 47 × 211
   X1    X2    X3    X4    X5    X6    X7    X8    X9    X10   X11   X12   X13  
   <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
 1 <NA>  Denv… <NA>  <NA>  El P… <NA>  <NA>  Port… <NA>  <NA>  Atla… <NA>  <NA> 
 2 <NA>  Kia/… All   Perc… Kia/… All   Perc… Kia/… All   Perc… Kia/… All   Perc…
 3 Dec/… 48    615   8%    13    103   13%   13    592   2%    <NA>  <NA>  <NA> 
 4 Jan/… 21    519   4%    9     95    9%    12    559   2%    <NA>  <NA>  <NA> 
 5 Feb/… 28    402   7%    5     64    8%    10    498   2%    <NA>  <NA>  <NA> 
 6 Mar/… 35    508   7%    6     75    8%    4     465   1%    <NA>  <NA>  <NA> 
 7 Apr/… 39    616   6%    1     57    2%    7     488   1%    <NA>  <NA>  <NA> 
 8 May/… 37    692   5%    1     62    2%    7     387   2%    <NA>  <NA>  <NA> 
 9 Jun/… 57    747   8%    8     77    10%   11    423   3%    <NA>  <NA>  <NA> 
10 Jul/… 57    786   7%    5     62    8%    11    528   2%    <NA>  <NA>  <NA> 
# ℹ 37 more rows
# ℹ 198 more variables: X14 <chr>, X15 <chr>, X16 <chr>, X17 <chr>, X18 <chr>,
#   X19 <chr>, X20 <chr>, X21 <chr>, X22 <chr>, X23 <chr>, X24 <chr>,
#   X25 <chr>, X26 <chr>, X27 <chr>, X28 <chr>, X29 <chr>, X30 <chr>,
#   X31 <chr>, X32 <chr>, X33 <chr>, X34 <chr>, X35 <chr>, X36 <chr>,
#   X37 <chr>, X38 <chr>, X39 <chr>, X40 <chr>, X41 <chr>, X42 <chr>,
#   X43 <chr>, X44 <chr>, X45 <chr>, X46 <chr>, X47 <chr>, X48 <chr>, …

The file is read with no header and every column as text. Row 1 holds the city only in the first of each group of three columns, row 2 holds the measure, and the data starts in row 3.

Code

kia_header <- tibble(
  city    = unlist(kia_raw[1, ], use.names = FALSE),
  measure = unlist(kia_raw[2, ], use.names = FALSE)
) |>
  fill(city) |>
  mutate(new_name = if_else(is.na(measure), "month",
                            str_c(city, measure, sep = " - ")))

kia_header

# A tibble: 211 × 3
   city     measure      new_name               
   <chr>    <chr>        <chr>                  
 1 <NA>     <NA>         month                  
 2 Denver   Kia/Hyundais Denver - Kia/Hyundais  
 3 Denver   All          Denver - All           
 4 Denver   Percent      Denver - Percent       
 5 El Paso  Kia/Hyundais El Paso - Kia/Hyundais 
 6 El Paso  All          El Paso - All          
 7 El Paso  Percent      El Paso - Percent      
 8 Portland Kia/Hyundais Portland - Kia/Hyundais
 9 Portland All          Portland - All         
10 Portland Percent      Portland - Percent     
# ℹ 201 more rows

Code

kia_long <- kia_raw |>
  slice(-(1:2)) |>
  setNames(kia_header$new_name) |>
  pivot_longer(-month,
               names_to = c("city", "measure"),
               names_sep = " - ",
               values_to = "value") |>
  pivot_wider(names_from = measure, values_from = value) |>
  rename(kia_hyundai_thefts = `Kia/Hyundais`,
         total_thefts = All,
         Percentage = Percent)

kia_long

# A tibble: 3,150 × 5
   month  city           kia_hyundai_thefts total_thefts Percentage
   <chr>  <chr>          <chr>              <chr>        <chr>     
 1 Dec/19 Denver         48                 615          8%        
 2 Dec/19 El Paso        13                 103          13%       
 3 Dec/19 Portland       13                 592          2%        
 4 Dec/19 Atlanta        <NA>               <NA>         <NA>      
 5 Dec/19 Chicago        46                 767          6%        
 6 Dec/19 Virginia Beach <NA>               <NA>         <NA>      
 7 Dec/19 Louisville     14                 <NA>         <NA>      
 8 Dec/19 San Diego      25                 430          6%        
 9 Dec/19 Sacramento     2                  <NA>         <NA>      
10 Dec/19 Dallas         21                 <NA>         <NA>      
# ℹ 3,140 more rows

Drop the two header rows, apply the new column names, and reshape the data from wide to long. The first pivot stacks every city and measure pair into rows. The second pivot spreads the three measures back out into columns, so that each row represents one city in one month.

Code

kia_clean <- kia_long |>
  mutate(
    # counts are text; "N/A" in the percent column becomes NA (warning silenced)
    across(c(kia_hyundai_thefts, total_thefts), parse_number),
    pct_reported = suppressWarnings(parse_number(Percentage)) / 100,
    # "Dec/19" -> a real date (first day of that month)
    month_start = as.Date(sprintf("%04d-%02d-01",
                                  2000 + as.integer(str_sub(month, 5, 6)),
                                  match(str_sub(month, 1, 3), month.abb))),
    # correct misspellings and a state written out in full
    city = str_replace_all(city, c("Cincinatti"    = "Cincinnati"))
  )

kia_clean

# A tibble: 3,150 × 7
   month  city           kia_hyundai_thefts total_thefts Percentage pct_reported
   <chr>  <chr>                       <dbl>        <dbl> <chr>             <dbl>
 1 Dec/19 Denver                         48          615 8%                 0.08
 2 Dec/19 El Paso                        13          103 13%                0.13
 3 Dec/19 Portland                       13          592 2%                 0.02
 4 Dec/19 Atlanta                        NA           NA <NA>              NA   
 5 Dec/19 Chicago                        46          767 6%                 0.06
 6 Dec/19 Virginia Beach                 NA           NA <NA>              NA   
 7 Dec/19 Louisville                     14           NA <NA>              NA   
 8 Dec/19 San Diego                      25          430 6%                 0.06
 9 Dec/19 Sacramento                      2           NA <NA>              NA   
10 Dec/19 Dallas                         21           NA <NA>              NA   
# ℹ 3,140 more rows
# ℹ 1 more variable: month_start <date>

Fix types and inconsistent values.

Code

kia_tidy <- kia_clean |>
  drop_na(kia_hyundai_thefts, total_thefts) |>
  filter(total_thefts > 0) |>
  mutate(share = kia_hyundai_thefts / total_thefts) |>
  select(city, month_start, kia_hyundai_thefts, total_thefts, share)

kia_tidy

# A tibble: 2,792 × 5
   city          month_start kia_hyundai_thefts total_thefts  share
   <chr>         <date>                   <dbl>        <dbl>  <dbl>
 1 Denver        2019-12-01                  48          615 0.0780
 2 El Paso       2019-12-01                  13          103 0.126 
 3 Portland      2019-12-01                  13          592 0.0220
 4 Chicago       2019-12-01                  46          767 0.0600
 5 San Diego     2019-12-01                  25          430 0.0581
 6 Fort Worth    2019-12-01                  12          352 0.0341
 7 Bakersfield   2019-12-01                   9          360 0.025 
 8 Omaha, NE     2019-12-01                  11          277 0.0397
 9 Lubbock, TX   2019-12-01                   6          130 0.0462
10 Irving, Texas 2019-12-01                   2           58 0.0345
# ℹ 2,782 more rows

Rows were dropped for any city-month with no theft counts or with zero total thefts, because a share of 0/0 is undefined and those rows cannot contribute to the analysis. The share was recomputed from the counts, because the published percent is rounded and sometimes missing.

Code

kia_monthly <- kia_tidy |>
  group_by(month_start) |>
  summarise(pooled_pct = sum(kia_hyundai_thefts) / sum(total_thefts),
            median_pct = median(share),
            )
kia_monthly |>
  pivot_longer(c(pooled_pct, median_pct),
               names_to = "measure", values_to = "share") |>
  mutate(measure = recode(measure,
                          pooled_pct = "Pooled across cities",
                          median_pct = "Median city")) |>
  ggplot(aes(month_start, share, colour = measure)) +
  geom_line() +
  scale_y_continuous() +
  labs(title = "Kia and Hyundai Thefts Through Time",
       x = "Month", y = "Percentage of all vehicle thefts")

For each month, two summaries were computed across the cities that reported: the pooled share (total Kia/Hyundai thefts divided by total thefts) and the median city share. Comparing the two shows whether the rise was driven by a few large cities or was broad.

Code

kia_monthly

# A tibble: 45 × 3
   month_start pooled_pct median_pct
   <date>           <dbl>      <dbl>
 1 2019-12-01      0.0447     0.0469
 2 2020-01-01      0.0407     0.0389
 3 2020-02-01      0.0377     0.0378
 4 2020-03-01      0.0444     0.0472
 5 2020-04-01      0.0373     0.0357
 6 2020-05-01      0.0377     0.0316
 7 2020-06-01      0.0404     0.0345
 8 2020-07-01      0.0440     0.0463
 9 2020-08-01      0.0497     0.0435
10 2020-09-01      0.0504     0.0390
# ℹ 35 more rows

The pooled share began from 4.5% for December 2019 and had risen to its peak of 33.6% in May 2023, an increase of seven times. The path taken by the median city is consistent with that, and thus we can conclude that the rise is universal and not just concentrated in a few megacities. The steepest increase occurs during the second part of 2022.

World Bank Life Expectancy

Code

wb_raw <- read_csv(url_wb)
wb_raw

# A tibble: 270 × 16
   `Series Name`     `Series Code` `Country Name` `Country Code` `1990 [YR1990]`
   <chr>             <chr>         <chr>          <chr>          <chr>          
 1 Life expectancy … SP.DYN.LE00.… Africa Easter… AFE            51.09632645430…
 2 Life expectancy … SP.DYN.LE00.… Africa Wester… AFW            48.45115887336…
 3 Life expectancy … SP.DYN.LE00.… Arab World     ARB            62.75597272998…
 4 Life expectancy … SP.DYN.LE00.… Caribbean sma… CSS            67.04515051400…
 5 Life expectancy … SP.DYN.LE00.… Central Europ… CEB            70.65633133725…
 6 Life expectancy … SP.DYN.LE00.… Early-demogra… EAR            60.63832613250…
 7 Life expectancy … SP.DYN.LE00.… East Asia & P… EAS            68.28254925844…
 8 Life expectancy … SP.DYN.LE00.… East Asia & P… EAP            67.15058425458…
 9 Life expectancy … SP.DYN.LE00.… East Asia & P… TEA            67.10594181736…
10 Life expectancy … SP.DYN.LE00.… Euro area      EMU            75.70557501212…
# ℹ 260 more rows
# ℹ 11 more variables: `2000 [YR2000]` <chr>, `2016 [YR2016]` <chr>,
#   `2017 [YR2017]` <chr>, `2018 [YR2018]` <chr>, `2019 [YR2019]` <chr>,
#   `2020 [YR2020]` <chr>, `2021 [YR2021]` <chr>, `2022 [YR2022]` <chr>,
#   `2023 [YR2023]` <chr>, `2024 [YR2024]` <chr>, `2025 [YR2025]` <chr>

Code

wb_tidy <- wb_raw |>
  filter(!is.na(`Series Code`)) |>
  select(country = `Country Name`, country_code = `Country Code`,
         matches("\\[YR")) |>
  pivot_longer(matches("\\[YR"),
               names_to = "year", values_to = "life_expectancy") |>
  mutate(
    year            = as.integer(str_extract(year, "\\d{4}")),
    life_expectancy = as.numeric(na_if(life_expectancy, ".."))
  )

wb_tidy

# A tibble: 3,180 × 4
   country                     country_code  year life_expectancy
   <chr>                       <chr>        <int>           <dbl>
 1 Africa Eastern and Southern AFE           1990            51.1
 2 Africa Eastern and Southern AFE           2000            52.6
 3 Africa Eastern and Southern AFE           2016            62.2
 4 Africa Eastern and Southern AFE           2017            62.6
 5 Africa Eastern and Southern AFE           2018            63.3
 6 Africa Eastern and Southern AFE           2019            63.9
 7 Africa Eastern and Southern AFE           2020            63.8
 8 Africa Eastern and Southern AFE           2021            63.0
 9 Africa Eastern and Southern AFE           2022            64.5
10 Africa Eastern and Southern AFE           2023            65.1
# ℹ 3,170 more rows

Code

wb_tidy <- wb_tidy |> drop_na(life_expectancy)
wb_tidy

# A tibble: 2,904 × 4
   country                     country_code  year life_expectancy
   <chr>                       <chr>        <int>           <dbl>
 1 Africa Eastern and Southern AFE           1990            51.1
 2 Africa Eastern and Southern AFE           2000            52.6
 3 Africa Eastern and Southern AFE           2016            62.2
 4 Africa Eastern and Southern AFE           2017            62.6
 5 Africa Eastern and Southern AFE           2018            63.3
 6 Africa Eastern and Southern AFE           2019            63.9
 7 Africa Eastern and Southern AFE           2020            63.8
 8 Africa Eastern and Southern AFE           2021            63.0
 9 Africa Eastern and Southern AFE           2022            64.5
10 Africa Eastern and Southern AFE           2023            65.1
# ℹ 2,894 more rows

Code

income_groups <- c(WLD = "World", HIC = "High income",
                   UMC = "Upper middle income", LMC = "Lower middle income",
                   LIC = "Low income")

wb_groups <- wb_tidy |>
  filter(country_code %in% names(income_groups)) |>
  mutate(group = factor(income_groups[country_code], levels = income_groups))

wb_groups

# A tibble: 55 × 5
   country     country_code  year life_expectancy group      
   <chr>       <chr>        <int>           <dbl> <fct>      
 1 High income HIC           1990            74.5 High income
 2 High income HIC           2000            76.0 High income
 3 High income HIC           2016            79.7 High income
 4 High income HIC           2017            79.9 High income
 5 High income HIC           2018            80.0 High income
 6 High income HIC           2019            80.2 High income
 7 High income HIC           2020            79.2 High income
 8 High income HIC           2021            78.8 High income
 9 High income HIC           2022            79.4 High income
10 High income HIC           2023            80.1 High income
# ℹ 45 more rows

Code

ggplot(wb_groups, aes(year, life_expectancy, colour = group, group = group)) +
  geom_line() +
  geom_point() +
  labs(title = "Life expectancy rose in every income group",
       subtitle = "Data exist only for 1990, 2000 and 2016 to 2024",
       x = "Year", y = "Life expectancy at birth (years)", colour = NULL)

There is an increase in life expectancy across all income categories from 1990 through 2024; moreover, these groups do not change their ranking throughout that period, being arranged from the highest (high income) to the lowest (low income). The average of the world ranks between upper middle and lower middle income groups – increases from 65 years in 1990 to 73 years in 2024. High income increased from around 74 to 80 years while the low increased from about 50 to 65. The steepness of the curve corresponding to low income indicates that the difference between the extremes decreased from about 24 years in 1990 to 15 years in 2024, but remains quite significant. Each of those income groups has a pronounced decline in 2020–2021, being the most noticeable in the case of lower middle income category. All groups return to the pre-decline levels by 2024.

SSA top 5 baby names

Code

ssa_raw <- read_csv(url_ssa, col_names = FALSE, col_types = cols(.default = col_character()))

Code

ssa_header <- tibble(
  sex  = unlist(ssa_raw[1, ], use.names = FALSE),
  rank = unlist(ssa_raw[2, ], use.names = FALSE)
) |>
  fill(sex) |>
  mutate(new_name = if_else(is.na(sex), "year", str_c(sex, rank, sep = " - ")))

ssa_header

# A tibble: 11 × 3
   sex   rank   new_name      
   <chr> <chr>  <chr>         
 1 <NA>  Year   year          
 2 Girls Rank 1 Girls - Rank 1
 3 Girls Rank 2 Girls - Rank 2
 4 Girls Rank 3 Girls - Rank 3
 5 Girls Rank 4 Girls - Rank 4
 6 Girls Rank 5 Girls - Rank 5
 7 Boys  Rank 1 Boys - Rank 1 
 8 Boys  Rank 2 Boys - Rank 2 
 9 Boys  Rank 3 Boys - Rank 3 
10 Boys  Rank 4 Boys - Rank 4 
11 Boys  Rank 5 Boys - Rank 5

Code

ssa_tidy <- ssa_raw |>
  slice(-(1:2)) |>
  setNames(ssa_header$new_name) |>
  pivot_longer(-year,
               names_to  = c("sex", "rank"),
               names_sep = " - ",
               values_to = "name") |>
  mutate(year   = as.integer(year),
         rank   = parse_number(rank),
         decade = year %/% 10 * 10) |>
  arrange(year, sex, rank)

ssa_tidy

# A tibble: 1,000 × 5
    year sex    rank name     decade
   <int> <chr> <dbl> <chr>     <dbl>
 1  1926 Boys      1 Robert     1920
 2  1926 Boys      2 John       1920
 3  1926 Boys      3 James      1920
 4  1926 Boys      4 William    1920
 5  1926 Boys      5 Charles    1920
 6  1926 Girls     1 Mary       1920
 7  1926 Girls     2 Dorothy    1920
 8  1926 Girls     3 Betty      1920
 9  1926 Girls     4 Helen      1920
10  1926 Girls     5 Margaret   1920
# ℹ 990 more rows

Code

ssa_top <- ssa_tidy |>
  filter(rank == 1) |>
  count(sex, name, name = "years_at_rank_1") |>
  group_by(sex) |>
  slice_max(years_at_rank_1, n = 5, with_ties = FALSE) |>
  ungroup() |>
  arrange(sex, desc(years_at_rank_1))

ssa_top

# A tibble: 10 × 3
   sex   name     years_at_rank_1
   <chr> <chr>              <int>
 1 Boys  Michael               44
 2 Boys  Robert                15
 3 Boys  Jacob                 14
 4 Boys  James                 13
 5 Boys  Liam                   9
 6 Girls Mary                  30
 7 Girls Jennifer              15
 8 Girls Emily                 12
 9 Girls Jessica                9
10 Girls Lisa                   8

Code

ssa_top |>
  transmute(Sex = str_to_title(sex), Name = name,
            `Years at rank 1` = years_at_rank_1)

# A tibble: 10 × 3
   Sex   Name     `Years at rank 1`
   <chr> <chr>                <int>
 1 Boys  Michael                 44
 2 Boys  Robert                  15
 3 Boys  Jacob                   14
 4 Boys  James                   13
 5 Boys  Liam                     9
 6 Girls Mary                    30
 7 Girls Jennifer                15
 8 Girls Emily                   12
 9 Girls Jessica                  9
10 Girls Lisa                     8

Code

ssa_top

# A tibble: 10 × 3
   sex   name     years_at_rank_1
   <chr> <chr>              <int>
 1 Boys  Michael               44
 2 Boys  Robert                15
 3 Boys  Jacob                 14
 4 Boys  James                 13
 5 Boys  Liam                   9
 6 Girls Mary                  30
 7 Girls Jennifer              15
 8 Girls Emily                 12
 9 Girls Jessica                9
10 Girls Lisa                   8

Michael held rank 1 for boys in 44 of the 100 years, and Mary held it for girls in 30 years. Both are far ahead of any other name, with the next closest, Robert for boys and Jennifer for girls, at 15 years each.

Conclusion

There was an increase from approximately 4% to 34% in the total number of car thefts reported for Kia and Hyundai among the different cities in the data, and this increase occurred widely and was not restricted to some cities only. In the life expectancy data, there was an increase in the number of years gained by each income group from 1990 to 2024; the difference between rich and poor countries decreased from 24.2 years to 15.2 years, and the drop that occurred from 2019 to 2021 happened in all income groups. In the baby names data, a handful of names dominated rank 1 (Michael for 44 years and Mary for 30 years).

Regarding the theft data, cities and manufacturers need to follow the share of Kia/Hyundai monthly, as it changed rapidly and could be seen from the data months before the peak point. In case of life expectancy, the difference that remains between the groups by their income is the number that should be watched, as the increase hides this difference. And finally, for all the sets of data, the takeaway is that the raw structure concealed the variables, once the headers became the columns, the questions became easy to answer.

The theft figures should be confirmed with the police departments themselves, since there is variation in reporting among the cities and some cities (Fremont and St. Petersburg) have less than 20 months of records. Including the size of the population or the number of automobiles in each city would transform the raw figures into rates of theft per capita. The life expectancy analysis should be repeated using the 2025 figures when available, and including additional years between 2000 and 2016 would eliminate the linear blank space in the graph. And lastly, the baby names analysis could include the SSA figures for all name occurrences.