Import your data

ufo_sightings <- readr::read_csv('https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2023/2023-06-20/ufo_sightings.csv')
## Rows: 96429 Columns: 12
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr  (7): city, state, country_code, shape, reported_duration, summary, day_...
## dbl  (1): duration_seconds
## lgl  (1): has_images
## dttm (2): reported_date_time, reported_date_time_utc
## date (1): posted_date
## 
## ℹ 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.
set.seed(1234)
ufo_sightings_small <- ufo_sightings %>% select(city, state, reported_date_time, duration_seconds) %>% sample_n(10)

ufo_sightings_small
## # A tibble: 10 × 4
##    city        state reported_date_time  duration_seconds
##    <chr>       <chr> <dttm>                         <dbl>
##  1 Minneapolis MN    2019-10-25 21:30:00            18000
##  2 Danville    CA    2006-07-24 04:30:00              120
##  3 Avon        NY    2001-10-22 02:00:00                3
##  4 Glendale    AZ    2016-03-02 05:00:00               60
##  5 Mayville    NY    2007-12-01 20:00:00               85
##  6 Dallas      TX    2011-04-26 03:00:00              180
##  7 Mount Horeb WI    1999-02-02 08:00:00               40
##  8 Scarborough ME    2015-08-07 23:00:00               10
##  9 Phoenix     AZ    1990-08-22 05:19:00             1800
## 10 Slocomb     AL    1970-03-31 03:00:00              120

Pivoting

long to wide form

ufo_sightings_wide <- ufo_sightings_small %>%
    
    pivot_wider(names_from = city, values_from = reported_date_time)

ufo_sightings_wide
## # A tibble: 10 × 12
##    state duration_seconds Minneapolis         Danville           
##    <chr>            <dbl> <dttm>              <dttm>             
##  1 MN               18000 2019-10-25 21:30:00 NA                 
##  2 CA                 120 NA                  2006-07-24 04:30:00
##  3 NY                   3 NA                  NA                 
##  4 AZ                  60 NA                  NA                 
##  5 NY                  85 NA                  NA                 
##  6 TX                 180 NA                  NA                 
##  7 WI                  40 NA                  NA                 
##  8 ME                  10 NA                  NA                 
##  9 AZ                1800 NA                  NA                 
## 10 AL                 120 NA                  NA                 
## # ℹ 8 more variables: Avon <dttm>, Glendale <dttm>, Mayville <dttm>,
## #   Dallas <dttm>, `Mount Horeb` <dttm>, Scarborough <dttm>, Phoenix <dttm>,
## #   Slocomb <dttm>

wide to long form

ufo_sightings_wide %>%
    
    pivot_longer(cols = c('Minneapolis', 'Danville', 'Avon', 'Glendale', 'Mayville', 'Dallas', 'Mount Horeb', 'Scarborough', 'Phoenix', 'Slocomb'), names_to = "city", values_to = "reported_date_time")
## # A tibble: 100 × 4
##    state duration_seconds city        reported_date_time 
##    <chr>            <dbl> <chr>       <dttm>             
##  1 MN               18000 Minneapolis 2019-10-25 21:30:00
##  2 MN               18000 Danville    NA                 
##  3 MN               18000 Avon        NA                 
##  4 MN               18000 Glendale    NA                 
##  5 MN               18000 Mayville    NA                 
##  6 MN               18000 Dallas      NA                 
##  7 MN               18000 Mount Horeb NA                 
##  8 MN               18000 Scarborough NA                 
##  9 MN               18000 Phoenix     NA                 
## 10 MN               18000 Slocomb     NA                 
## # ℹ 90 more rows

Separating and Uniting

Separate a column

ufo_sightings_small %>%
    
    # Convert the date object to character
    
    mutate(reported_date_time = as.character(reported_date_time)) %>%
    
    separate(reported_date_time, into = c("reported_date", "reported_time"), sep = " ")
## # A tibble: 10 × 5
##    city        state reported_date reported_time duration_seconds
##    <chr>       <chr> <chr>         <chr>                    <dbl>
##  1 Minneapolis MN    2019-10-25    21:30:00                 18000
##  2 Danville    CA    2006-07-24    04:30:00                   120
##  3 Avon        NY    2001-10-22    02:00:00                     3
##  4 Glendale    AZ    2016-03-02    05:00:00                    60
##  5 Mayville    NY    2007-12-01    20:00:00                    85
##  6 Dallas      TX    2011-04-26    03:00:00                   180
##  7 Mount Horeb WI    1999-02-02    08:00:00                    40
##  8 Scarborough ME    2015-08-07    23:00:00                    10
##  9 Phoenix     AZ    1990-08-22    05:19:00                  1800
## 10 Slocomb     AL    1970-03-31    03:00:00                   120

Unite two columns

ufo_sightings_small %>%
    
    unite(new, reported_date_time,duration_seconds)
## # A tibble: 10 × 3
##    city        state new                      
##    <chr>       <chr> <chr>                    
##  1 Minneapolis MN    2019-10-25 21:30:00_18000
##  2 Danville    CA    2006-07-24 04:30:00_120  
##  3 Avon        NY    2001-10-22 02:00:00_3    
##  4 Glendale    AZ    2016-03-02 05:00:00_60   
##  5 Mayville    NY    2007-12-01 20:00:00_85   
##  6 Dallas      TX    2011-04-26 03:00:00_180  
##  7 Mount Horeb WI    1999-02-02 08:00:00_40   
##  8 Scarborough ME    2015-08-07 23:00:00_10   
##  9 Phoenix     AZ    1990-08-22 05:19:00_1800 
## 10 Slocomb     AL    1970-03-31 03:00:00_120

Missing Values

ufo_sightings_wide %>%
    
    pivot_longer(cols = c('Minneapolis', 'Danville', 'Avon', 'Glendale', 'Mayville', 'Dallas', 'Mount Horeb', 'Scarborough', 'Phoenix', 'Slocomb'), names_to = "city", values_to = "reported_date_time", values_drop_na = TRUE)
## # A tibble: 10 × 4
##    state duration_seconds city        reported_date_time 
##    <chr>            <dbl> <chr>       <dttm>             
##  1 MN               18000 Minneapolis 2019-10-25 21:30:00
##  2 CA                 120 Danville    2006-07-24 04:30:00
##  3 NY                   3 Avon        2001-10-22 02:00:00
##  4 AZ                  60 Glendale    2016-03-02 05:00:00
##  5 NY                  85 Mayville    2007-12-01 20:00:00
##  6 TX                 180 Dallas      2011-04-26 03:00:00
##  7 WI                  40 Mount Horeb 1999-02-02 08:00:00
##  8 ME                  10 Scarborough 2015-08-07 23:00:00
##  9 AZ                1800 Phoenix     1990-08-22 05:19:00
## 10 AL                 120 Slocomb     1970-03-31 03:00:00