Using pivot wider (R) to make multiple lines into a single line

Question

Apologies for the simple Q as always. I'm struggling to get pivot wider to do what I want.

I have a tab delimited dataset like this:

ID  filepath
ID1 /tmp/ID1_1.txt
ID1 /tmp/ID1_2.txt
ID2 /tmp/ID2_1.txt
ID2 /tmp/ID2_2.txt
ID3 /tmp/ID3_1.txt
ID3 /tmp/ID3_2.txt

and I want to transform it to something like this:

ID  filepath_1  filepath_2
ID1 /tmp/ID1_1.txt  /tmp/ID1_2.txt
ID2 /tmp/ID2_1.txt  /tmp/ID2_2.txt
ID3 /tmp/ID3_1.txt  /tmp/ID3_2.txt

I figure pivot wider is the way to go, but it's not behaving..

Would appreciate some guidance!

Answer 1

I believe you need a "names" column to use pivot_wider(). You can create one using mutate() e.g.

library(tidyverse)
df <- tibble::tribble(
         ~"ID", ~"filepath",
  "ID1", "/tmp/ID1_1.txt",
  "ID1", "/tmp/ID1_2.txt",
  "ID2", "/tmp/ID2_1.txt",
  "ID2", "/tmp/ID2_2.txt",
  "ID3", "/tmp/ID3_1.txt",
  "ID3", "/tmp/ID3_2.txt"
  )

df %>%
  mutate(names = ifelse(str_extract(filepath, "_.") == "_1", "filepath_1", "filepath_2")) %>% 
  pivot_wider(id_cols = ID, values_from = filepath, names_from = names)
#> # A tibble: 3 x 3
#>   ID    filepath_1     filepath_2    
#>   <chr> <chr>          <chr>         
#> 1 ID1   /tmp/ID1_1.txt /tmp/ID1_2.txt
#> 2 ID2   /tmp/ID2_1.txt /tmp/ID2_2.txt
#> 3 ID3   /tmp/ID3_1.txt /tmp/ID3_2.txt

^{Created on 2021-07-23 by the reprex package (v2.0.0)}

Answer 2

you need an identifier to signalize which row goes into which column:

# dummy data read from plain text
df <- data.table::fread("ID  filepath
ID1 /tmp/ID1_1.txt
ID1 /tmp/ID1_2.txt
ID2 /tmp/ID2_1.txt
ID2 /tmp/ID2_2.txt
ID3 /tmp/ID3_1.txt
ID3 /tmp/ID3_2.txt") 

df %>%
    dplyr::group_by(ID) %>%
    dplyr::mutate(rn = paste0("filepath_",dplyr::row_number())) %>%
    dplyr::ungroup() %>%
    tidyr::pivot_wider(names_from = rn, values_from = filepath)

# A tibble: 3 x 3
  ID    filepath_1     filepath_2    
  <chr> <chr>          <chr>         
1 ID1   /tmp/ID1_1.txt /tmp/ID1_2.txt
2 ID2   /tmp/ID2_1.txt /tmp/ID2_2.txt
3 ID3   /tmp/ID3_1.txt /tmp/ID3_2.txt