Import your data

Pivoting

long to wide form

roundabout_long <- roundabout %>%
    
    select(name, year_completed, approaches) %>%
    
    pivot_longer(cols = c(year_completed, approaches),
                 names_to = "variable",
                 values_to = "value")

wide to long form

roundabout_long %>%
    
    pivot_wider(names_from = variable,
                values_from = value,
                values_fn = first)
## # A tibble: 27,611 × 3
##    name                       year_completed approaches
##    <chr>                               <dbl>      <dbl>
##  1 Arapahoe Ave. / 4th St.                 0          3
##  2 Arapahoe Ave. / 5th St.                 0          3
##  3 10th Ave. / B St.                       0          4
##  4 10th Ave. / D St.                       0          4
##  5 Bagley Ave. N / N 42nd St.              0          0
##  6 8th Ave. NE / NE 115th St.              0          0
##  7 8th Ave. NE / NE 117th St.              0          0
##  8 8th Ave. NE / NE 120th St.              0          0
##  9 Kendall Ave. / Grand Ave.            1997          4
## 10 Bagley Ave. N / N 43rd St.              0          0
## # ℹ 27,601 more rows

Separating and Uniting

roundabout_sep <- roundabout %>% 
    
    separate(col = address,
             into = c("address_part1", "address_part2"),
             sep = ", ",
             extra = "merge",
             fill = "right")

Separate a column

Unite two columns

roundabout_sep %>%
    
    unite(col = "address",
          c(address_part1, address_part2),
          sep = ", ",
          na.rm = TRUE)
## # A tibble: 27,887 × 18
##    name    address town_city county_area state_region country    lat  long type 
##    <chr>   <chr>   <chr>     <chr>       <chr>        <chr>    <dbl> <dbl> <chr>
##  1 Arapah… Boulde… Boulder   Boulder Co. CO           United… -105.   40.0 Traf…
##  2 Arapah… Boulde… Boulder   Boulder Co. CO           United… -105.   40.0 Traf…
##  3 10th A… Belmar… Belmar    Monmouth C… NJ           United…  -74.0  40.2 Traf…
##  4 10th A… Belmar… Belmar    Monmouth C… NJ           United…  -74.0  40.2 Traf…
##  5 Bagley… Seattl… Seattle   King Co.    WA           United… -122.   47.7 Traf…
##  6 8th Av… Seattl… Seattle   King Co.    WA           United… -122.   47.7 Traf…
##  7 8th Av… Seattl… Seattle   King Co.    WA           United… -122.   47.7 Traf…
##  8 8th Av… Seattl… Seattle   King Co.    WA           United… -122.   47.7 Traf…
##  9 Kendal… Madiso… Madison   Dane Co.    WI           United…  -89.4  43.1 Traf…
## 10 Bagley… Seattl… Seattle   King Co.    WA           United… -122.   47.7 Traf…
## # ℹ 27,877 more rows
## # ℹ 9 more variables: status <chr>, year_completed <dbl>, approaches <dbl>,
## #   driveways <dbl>, lane_type <chr>, functional_class <chr>,
## #   control_type <chr>, other_control_type <chr>, previous_control_type <chr>

Missing Values

roundabout %>%
    
    select(name, year_completed, approaches) %>%
    
    pivot_wider(names_from = year_completed,
                values_from = approaches,
                values_fn = first)
## # A tibble: 27,611 × 78
##    name       `0` `1997` `2012` `2002` `2007` `2008` `2006` `2014` `1989` `1988`
##    <chr>    <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
##  1 Arapaho…     3     NA     NA     NA     NA     NA     NA     NA     NA     NA
##  2 Arapaho…     3     NA     NA     NA     NA     NA     NA     NA     NA     NA
##  3 10th Av…     4     NA     NA     NA     NA     NA     NA     NA     NA     NA
##  4 10th Av…     4     NA     NA     NA     NA     NA     NA     NA     NA     NA
##  5 Bagley …     0     NA     NA     NA     NA     NA     NA     NA     NA     NA
##  6 8th Ave…     0     NA     NA     NA     NA     NA     NA     NA     NA     NA
##  7 8th Ave…     0     NA     NA     NA     NA     NA     NA     NA     NA     NA
##  8 8th Ave…     0     NA     NA     NA     NA     NA     NA     NA     NA     NA
##  9 Kendall…    NA      4     NA     NA     NA     NA     NA     NA     NA     NA
## 10 Bagley …     0     NA     NA     NA     NA     NA     NA     NA     NA     NA
## # ℹ 27,601 more rows
## # ℹ 67 more variables: `2016` <dbl>, `2010` <dbl>, `2015` <dbl>, `2005` <dbl>,
## #   `2009` <dbl>, `2003` <dbl>, `2017` <dbl>, `2011` <dbl>, `2013` <dbl>,
## #   `2018` <dbl>, `2019` <dbl>, `2004` <dbl>, `2001` <dbl>, `2000` <dbl>,
## #   `1994` <dbl>, `1992` <dbl>, `1995` <dbl>, `1996` <dbl>, `1998` <dbl>,
## #   `1999` <dbl>, `1890` <dbl>, `1991` <dbl>, `1905` <dbl>, `1986` <dbl>,
## #   `1987` <dbl>, `1990` <dbl>, `1968` <dbl>, `1982` <dbl>, `1993` <dbl>, …
roundabout %>%
    
    summarise(
        missing_names = sum(is.na(name)),
        missing_years = sum(is.na(year_completed)),
        missing_approaches = sum(is.na(approaches)),
        missing_status = sum(is.na(status))
    )
## # A tibble: 1 × 4
##   missing_names missing_years missing_approaches missing_status
##           <int>         <int>              <int>          <int>
## 1             0             0                  0              0
roundabout %>%
    
    select(name, status) %>%
    
    distinct() %>%
    
    complete(name, status)
## # A tibble: 82,833 × 2
##    name                                             status  
##    <chr>                                            <chr>   
##  1 (access road) / (parking)                        Existing
##  2 (access road) / (parking)                        Removed 
##  3 (access road) / (parking)                        Unknown 
##  4 (airport terminal roadway) / (parking lots)      Existing
##  5 (airport terminal roadway) / (parking lots)      Removed 
##  6 (airport terminal roadway) / (parking lots)      Unknown 
##  7 (apartment entrance) / (parking)                 Existing
##  8 (apartment entrance) / (parking)                 Removed 
##  9 (apartment entrance) / (parking)                 Unknown 
## 10 (Carroll Community College entrance) / (parking) Existing
## # ℹ 82,823 more rows
roundabout %>%
    
    fill(name, .direction = "down")
## # A tibble: 27,887 × 18
##    name    address town_city county_area state_region country    lat  long type 
##    <chr>   <chr>   <chr>     <chr>       <chr>        <chr>    <dbl> <dbl> <chr>
##  1 Arapah… Boulde… Boulder   Boulder Co. CO           United… -105.   40.0 Traf…
##  2 Arapah… Boulde… Boulder   Boulder Co. CO           United… -105.   40.0 Traf…
##  3 10th A… Belmar… Belmar    Monmouth C… NJ           United…  -74.0  40.2 Traf…
##  4 10th A… Belmar… Belmar    Monmouth C… NJ           United…  -74.0  40.2 Traf…
##  5 Bagley… Seattl… Seattle   King Co.    WA           United… -122.   47.7 Traf…
##  6 8th Av… Seattl… Seattle   King Co.    WA           United… -122.   47.7 Traf…
##  7 8th Av… Seattl… Seattle   King Co.    WA           United… -122.   47.7 Traf…
##  8 8th Av… Seattl… Seattle   King Co.    WA           United… -122.   47.7 Traf…
##  9 Kendal… Madiso… Madison   Dane Co.    WI           United…  -89.4  43.1 Traf…
## 10 Bagley… Seattl… Seattle   King Co.    WA           United… -122.   47.7 Traf…
## # ℹ 27,877 more rows
## # ℹ 9 more variables: status <chr>, year_completed <dbl>, approaches <dbl>,
## #   driveways <dbl>, lane_type <chr>, functional_class <chr>,
## #   control_type <chr>, other_control_type <chr>, previous_control_type <chr>