project-2.2

1.Objective

This project develops practical competency in transforming wide-format datasets into tidy formats suitable for downstream analysis. All transformations are performed using the tidyr and dplyr packages in R.

2.Dataset Selection

Labor market dataset: https://www.bls.gov/news.release/archives/laus_04082026.htm Obtained from 5A discussion post contributed by Zaina Hassan.

Data structure before tidying

Dates and measurements are spread across multiple columns and levels of column headings rather than each variable having its own individual column. For example, unemployment information for different months and years appears across several columns.

3.Data Preparation

3.1 Raw Data Construction

library(readr)
library(dplyr)

Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union

library(tidyr)
library(naniar)
library(ggplot2)
library(stringr)


row_df<-read_csv("https://raw.githubusercontent.com/lhamo07/Data-607-Assignment/refs/heads/main/project-2.3/labour_row_dataset.csv")

New names:
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...7`
• `` -> `...8`
• `` -> `...9`
• `` -> `...10`
• `` -> `...11`
• `` -> `...12`
• `` -> `...13`

Rows: 55 Columns: 13
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (13): State, Civilian labor force, ...3, ...4, ...5, Unemployed, ...7, ....

ℹ 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.

# Rename columns
row_df <- row_df %>%
  rename(
    state = 1,
    labor_force_jan_2025 = 2,
    labor_force_nov_2025 = 3,
    labor_force_dec_2025 = 4,
    labor_force_jan_2026 = 5,
    unemployed_jan_2025 = 6,
    unemployed_nov_2025 = 7,
    unemployed_dec_2025 = 8,
    unemployed_jan_2026 = 9,
    unemployment_rate_jan_2025 = 10,
    unemployment_rate_nov_2025 = 11,
    unemployment_rate_dec_2025 = 12,
    unemployment_rate_jan_2026 = 13
  )%>%
# Remove the first two unnecessary rows and the last row 
  slice(-c(1, 2, n()))
row_df

# A tibble: 52 × 13
   state          labor_force_jan_2025 labor_force_nov_2025 labor_force_dec_2025
   <chr>          <chr>                <chr>                <chr>               
 1 Alabama        2,378,428            2,387,873            2,387,810           
 2 Alaska         363,404              367,709              367,671             
 3 Arizona        3,781,221            3,815,687            3,814,917           
 4 Arkansas       1,424,605            1,448,832            1,448,666           
 5 California     19,764,218           19,878,800           19,878,043          
 6 Colorado       3,273,054            3,254,030            3,254,073           
 7 Connecticut    1,943,229            1,933,758            1,933,851           
 8 Delaware       513,593              517,116              517,069             
 9 District of C… 415,267              408,918              409,140             
10 Florida        11,122,059           11,116,493           11,117,284          
# ℹ 42 more rows
# ℹ 9 more variables: labor_force_jan_2026 <chr>, unemployed_jan_2025 <chr>,
#   unemployed_nov_2025 <chr>, unemployed_dec_2025 <chr>,
#   unemployed_jan_2026 <chr>, unemployment_rate_jan_2025 <chr>,
#   unemployment_rate_nov_2025 <chr>, unemployment_rate_dec_2025 <chr>,
#   unemployment_rate_jan_2026 <chr>

miss_var_summary(row_df)

# A tibble: 13 × 3
   variable                   n_miss pct_miss
   <chr>                       <int>    <num>
 1 state                           0        0
 2 labor_force_jan_2025            0        0
 3 labor_force_nov_2025            0        0
 4 labor_force_dec_2025            0        0
 5 labor_force_jan_2026            0        0
 6 unemployed_jan_2025             0        0
 7 unemployed_nov_2025             0        0
 8 unemployed_dec_2025             0        0
 9 unemployed_jan_2026             0        0
10 unemployment_rate_jan_2025      0        0
11 unemployment_rate_nov_2025      0        0
12 unemployment_rate_dec_2025      0        0
13 unemployment_rate_jan_2026      0        0

There is no any missing data.

glimpse(row_df)

Rows: 52
Columns: 13
$ state                      <chr> "Alabama", "Alaska", "Arizona", "Arkansas",…
$ labor_force_jan_2025       <chr> "2,378,428", "363,404", "3,781,221", "1,424…
$ labor_force_nov_2025       <chr> "2,387,873", "367,709", "3,815,687", "1,448…
$ labor_force_dec_2025       <chr> "2,387,810", "367,671", "3,814,917", "1,448…
$ labor_force_jan_2026       <chr> "2,387,649", "368,663", "3,814,207", "1,451…
$ unemployed_jan_2025        <chr> "72,543", "16,527", "157,097", "54,116", "1…
$ unemployed_nov_2025        <chr> "64,802", "17,537", "167,615", "62,349", "1…
$ unemployed_dec_2025        <chr> "64,776", "17,531", "167,319", "62,197", "1…
$ unemployed_jan_2026        <chr> "64,061", "17,722", "171,391", "63,636", "1…
$ unemployment_rate_jan_2025 <chr> "3.1", "4.5", "4.2", "3.8", "5.4", "4.3", "…
$ unemployment_rate_nov_2025 <chr> "2.7", "4.8", "4.4", "4.3", "5.5", "3.8", "…
$ unemployment_rate_dec_2025 <chr> "2.7", "4.8", "4.4", "4.3", "5.5", "3.8", "…
$ unemployment_rate_jan_2026 <chr> "2.7", "4.8", "4.5", "4.4", "5.4", "3.9", "…

3.2 Data Import and Tidying

# Strip the commas first to convert char datatype to numeric
row_df <- row_df %>%
  mutate(across(-state, ~ as.numeric(gsub(",", "", .x))))

glimpse(row_df)

Rows: 52
Columns: 13
$ state                      <chr> "Alabama", "Alaska", "Arizona", "Arkansas",…
$ labor_force_jan_2025       <dbl> 2378428, 363404, 3781221, 1424605, 19764218…
$ labor_force_nov_2025       <dbl> 2387873, 367709, 3815687, 1448832, 19878800…
$ labor_force_dec_2025       <dbl> 2387810, 367671, 3814917, 1448666, 19878043…
$ labor_force_jan_2026       <dbl> 2387649, 368663, 3814207, 1451310, 19862185…
$ unemployed_jan_2025        <dbl> 72543, 16527, 157097, 54116, 1061763, 14121…
$ unemployed_nov_2025        <dbl> 64802, 17537, 167615, 62349, 1094082, 12495…
$ unemployed_dec_2025        <dbl> 64776, 17531, 167319, 62197, 1093176, 12507…
$ unemployed_jan_2026        <dbl> 64061, 17722, 171391, 63636, 1082348, 12626…
$ unemployment_rate_jan_2025 <dbl> 3.1, 4.5, 4.2, 3.8, 5.4, 4.3, 3.6, 4.1, 5.7…
$ unemployment_rate_nov_2025 <dbl> 2.7, 4.8, 4.4, 4.3, 5.5, 3.8, 4.3, 5.3, 6.7…
$ unemployment_rate_dec_2025 <dbl> 2.7, 4.8, 4.4, 4.3, 5.5, 3.8, 4.3, 5.3, 6.7…
$ unemployment_rate_jan_2026 <dbl> 2.7, 4.8, 4.5, 4.4, 5.4, 3.9, 4.5, 5.4, 6.7…

# Reshape to long format
labour_df_long<-row_df%>%
  pivot_longer(
    cols=-state,
    names_to = c(".value", "month", "year"),
    names_pattern = "(labor_force|unemployed|unemployment_rate)_([a-z]{3})_(\\d{4})"    
  )%>%
    mutate(month = str_to_title(month))
labour_df_long

# A tibble: 208 × 6
   state   month year  labor_force unemployed unemployment_rate
   <chr>   <chr> <chr>       <dbl>      <dbl>             <dbl>
 1 Alabama Jan   2025      2378428      72543               3.1
 2 Alabama Nov   2025      2387873      64802               2.7
 3 Alabama Dec   2025      2387810      64776               2.7
 4 Alabama Jan   2026      2387649      64061               2.7
 5 Alaska  Jan   2025       363404      16527               4.5
 6 Alaska  Nov   2025       367709      17537               4.8
 7 Alaska  Dec   2025       367671      17531               4.8
 8 Alaska  Jan   2026       368663      17722               4.8
 9 Arizona Jan   2025      3781221     157097               4.2
10 Arizona Nov   2025      3815687     167615               4.4
# ℹ 198 more rows

labour_df_long %>%
  filter(month == "Jan", year == 2026) %>%
  # selects the 10 rows with the highest unemployment rates.
  slice_max(unemployment_rate, n = 10) %>%
  ggplot(aes(
    # reorder the states based on their unemployment rates
    x = reorder(state, unemployment_rate),
    y = unemployment_rate
  )) +
  geom_col() +
  coord_flip() +
  labs(
    title = "10 States with the Highest Unemployment Rates",
    x = "State",
    y = "Unemployment Rate (%)"
  ) +
  theme_minimal()

This bar chart shows the 11 states with the highest unemployment rates for January 2026, where District of Columbia lead with the highest unemployment rate.

labour_df_long %>%
  filter(month == "Jan", year == 2026) %>%
  # selects the 10 rows with the lowest unemployment rates.
  slice_min(unemployment_rate, n = 10) %>%
  ggplot(aes(
    # reorder the states based on their unemployment rates
    x = reorder(state, unemployment_rate),
    y = unemployment_rate
  )) +
  geom_col() +
  coord_flip() +
  labs(
    title = "10 States with the Lowest Unemployment Rates",
    x = "State",
    y = "Unemployment Rate (%)"
  ) +
  theme_minimal()

This bar chart shows the 11 states with the lowest unemployment rates for January 2026, where South Dakota and Hawaii lead with the lowest unemployment rate.

Conclusion

In this project, I transformed the labor market dataset from wide to long format using tidyr and dplyr. The analysis showed that the District of Columbia had the highest unemployment rate in January 2026, while South Dakota and Hawaii had the lowest. This project helped me practice data tidying and visualization in R.

Citation

Anthropic. (2026). Claude Sonnet 5.5 [Large language model]. https://claude.ai. Accessed October 11,

https://claude.ai/share/6ecc061e-08c3-4fed-995c-8637f933968d