Merging values from wide data frame into long data frame

Question

I have two data frames, one wide and one long:

    long_df = structure(list(PID = c(1001, 1001, 1001, 1002, 1002, 1002, 1002, 
1003), scan_name = c("01_001A", "01_001B", "01_001C", "01_002A", 
"01_002B", "01_002D", "01_002E", "01_003B")), row.names = c(NA, 
-8L), class = c("tbl_df", "tbl", "data.frame"))

wide_df = structure(list(PID = c(1001, 1002, 1003), scan_name_1 = c("01_001A", 
"01_002A", NA), scan_date_1 = structure(c(1206748800, 1240876800, 
NA), class = c("POSIXct", "POSIXt"), tzone = "UTC"), scan_name_2 = c("01_001B", 
"01_002B", "01_003B"), scan_date_2 = structure(c(1238544000, 
1272672000, 1424736000), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
    scan_name_3 = c("01_001C", NA, NA), scan_date_3 = structure(c(1301702400, 
    NA, NA), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
    scan_name_4 = c(NA, "01_002D", NA), scan_date_4 = structure(c(NA, 
    1400112000, NA), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
    scan_name_5 = c(NA, "01_002E", NA), scan_date_5 = structure(c(NA, 
    1430438400, NA), class = c("POSIXct", "POSIXt"), tzone = "UTC")), row.names = c(NA, 
-3L), class = c("tbl_df", "tbl", "data.frame"))

I'm trying to get the values "scan_date_1", "scan_date_2", etc, from wide_df into the long_df.

The output I'm trying to get to looks like this:

goal_df = structure(list(PID = c(1001, 1001, 1001, 1002, 1002, 1002, 1002, 
1003), scan_name = c("01_001A", "01_001B", "01_001C", "01_002A", 
"01_002B", "01_002D", "01_002E", "01_003B"), scan_date = structure(c(1206748800, 
1238544000, 1301702400, 1240876800, 1272672000, 1400112000, 1430438400, 
1424736000), class = c("POSIXct", "POSIXt"), tzone = "UTC")), row.names = c(NA, 
-8L), class = c("tbl_df", "tbl", "data.frame"))

Seems simple, but none of my attempts using merge/melt/etc, have been able to get there. Any and all help is much appreciated! (First time using "dput", so hopefully this is a replicable example)

I've edited my post to add what the goal output is, thanks @RuiBarradas — Jake Stubbs, Jul 05 '19 at 17:26

Joris C. · Answer 1 · 2019-07-05T20:15:44.570

This can be done efficiently with data.table's melt function and its patterns argument:

library(data.table)

setDT(wide_df)

goal_df <- melt(wide_df, measure = patterns("scan_name", "scan_date"), value.name = c("scan_name", "scan_date"))
goal_df <- na.omit(goal_df)

goal_df[, variable := NULL][]
#>     PID scan_name  scan_date
#> 1: 1001   01_001A 2008-03-29
#> 2: 1002   01_002A 2009-04-28
#> 3: 1001   01_001B 2009-04-01
#> 4: 1002   01_002B 2010-05-01
#> 5: 1003   01_003B 2015-02-24
#> 6: 1001   01_001C 2011-04-02
#> 7: 1002   01_002D 2014-05-15
#> 8: 1002   01_002E 2015-05-01

NB: tidyr's development version (GitHub) contains the new functions pivot_longer and pivot_wider with similar functionalities, see Tidyr-Pivoting. Using pivot_longer you could do:

library(dplyr)
library(tidyr)

mutate_at(wide_df, .vars = vars(starts_with("scan")), as.character) %>%
    pivot_longer(-PID, 
        names_to = c(".value", "id"),
        names_pattern = "(scan_date|scan_name)_(.)", 
        values_drop_na = TRUE
    ) %>%
    select(-id)
#> # A tibble: 8 x 3
#>     PID scan_name scan_date 
#>   <dbl> <chr>     <chr>     
#> 1  1001 01_001A   2008-03-29
#> 2  1001 01_001B   2009-04-01
#> 3  1001 01_001C   2011-04-02
#> 4  1002 01_002A   2009-04-28
#> 5  1002 01_002B   2010-05-01
#> 6  1002 01_002D   2014-05-15
#> 7  1002 01_002E   2015-05-01
#> 8  1003 01_003B   2015-02-24

packageVersion("tidyr")
#> ‘0.8.3.9000’

score 1 · Answer 2 · answered Jul 05 '19 at 17:34

This code lengthens wide_df, where each row represents a unique person by event.

library(magrittr)
pattern <- "^scan_(date|name)_(\\d+)$"

wide_df %>% 
  dplyr::mutate_all(as.character) %>% 
  tidyr::gather(key="key", value="value", -PID) %>% 
  dplyr::mutate(
    event_id  = as.integer(sub(pattern, "\\2", key)),
    key       = sub(pattern, "\\1", key)
  ) %>% 
  tidyr::spread(key=key, value=value) %>%
  dplyr::mutate(
    date  = as.Date(date)
  )

Result:

# A tibble: 15 x 4
   PID   event_id date       name   
   <chr>    <int> <date>     <chr>  
 1 1001         1 2008-03-29 01_001A
 2 1001         2 2009-04-01 01_001B
 3 1001         3 2011-04-02 01_001C
 4 1001         4 NA         NA     
 5 1001         5 NA         NA     
 6 1002         1 2009-04-28 01_002A
 7 1002         2 2010-05-01 01_002B
 8 1002         3 NA         NA     
 9 1002         4 2014-05-15 01_002D
10 1002         5 2015-05-01 01_002E
11 1003         1 NA         NA     
12 1003         2 2015-02-24 01_003B
13 1003         3 NA         NA     
14 1003         4 NA         NA     
15 1003         5 NA         NA

I missed your edit with `goal_df`. I believe this is the same, with two small exceptions: (1) it keeps `event_id` in case you need it, and (b) it represents `date` as a date, not a date time. If you want more options, see the whole thread around [Convert data from long format to wide format with multiple measure columns](https://stackoverflow.com/a/41126250/1082435) — wibeasley, Jul 05 '19 at 18:06

Merging values from wide data frame into long data frame

2 Answers2