Using R tidyr pivot_wide to get wide-form data from multiple column names and values

Question

How can I use tidyr pivot_wide to convert this data frame from long form to wide form? I tried applying the examples on the docs page, but I must be missing something.

Data Frame

id <- c(1,1,2,2,3,3)
filename <- c('file1a.txt', 'file1b.txt', 
              'file2a.txt', 'file2b.txt', 
              'file3a.txt', 'file3b.txt')
val <- c(832, 834, 221, 878, 2, 19)

df1 <- data.frame(id, filename, val)
view(df1)

id	filename	val
1	file1a.txt	832
1	file1b.txt	834
2	file2a.txt	221
2	file2b.txt	878
3	file3a.txt	2
3	file3b.txt	19

Desired Output

id	filename1	filename2	val1	val2
1	file1a.txt	file1b.txt	832	834
2	file2a.txt	file2b.txt	221	878
3	file3a.txt	file3b.txt	2	19

Failed Attempts

df_wide <- pivot_wider(data = df1, 
                       id_cols = id, 
                       values_from = c("filename", "val"))
view(df_wide)

id	filename_	val_
1	1:2	c(832,834)
2	3:4	c(221,878)
3	5:6	c(2,19)

df_wide <- pivot_wider(data = df1, 
                       id_cols = id, 
                       names_from = c("filename", "val"), 
                       values_from = c("filename", "val"))
view(df_wide)

id	filename_file1a.txt_832	filename_file1b.txt_834	filename_file2a.txt_221	...etc
1	file1a.txt	file1b.txt	NA	...etc
2	NA	NA	file2a.txt	...etc
3	NA	NA	NA	...etc

akrun · Accepted Answer · 2021-05-16T21:32:18.713

2

We need a row sequence

library(dplyr)
library(tidyr)
library(data.table)
df1 %>%
    mutate(cn = rowid(id)) %>% 
    pivot_wider(names_from = cn, values_from = c(filename, val), names_sep="")

-output

# A tibble: 3 x 5
#     id filename1  filename2   val1  val2
#  <dbl> <chr>      <chr>      <dbl> <dbl>
#1     1 file1a.txt file1b.txt   832   834
#2     2 file2a.txt file2b.txt   221   878
#3     3 file3a.txt file3b.txt     2    19

Or do a group by row_number

df1 %>%
    group_by(id)
    mutate(cn = row_number()) %>% 
    pivot_wider(names_from = cn, values_from = c(filename, val), names_sep="")

If we need not use %>%, specify the data as the mutated original dataset, with an added column 'cn' based on the sequence of 'id'

pivot_wider(mutate(df1, cn = rowid(id)), 
   names_from = cn, values_from = c(filename, val), names_sep="")

edited May 16 '21 at 21:32

answered May 16 '21 at 21:08

akrun

874,273
37
540
662

I want to implement it without the pipes, but I am getting stuck; is there a solution without pipes – a11 May 16 '21 at 21:18
1

@a11 can you please check the update. Is that what you have in mind – akrun May 16 '21 at 21:23
@a11 wouldn't the one without %>% confuses you. – akrun May 16 '21 at 21:31

score 2 · Answer 2 · answered May 16 '21 at 21:35

2

A data.table option with dcast

> dcast(setDT(df1), id ~ rowid(id), value.var = c("filename", "val"))
   id filename_1 filename_2 val_1 val_2
1:  1 file1a.txt file1b.txt   832   834
2:  2 file2a.txt file2b.txt   221   878
3:  3 file3a.txt file3b.txt     2    19

answered May 16 '21 at 21:35

ThomasIsCoding

96,636
9
24
81

oh this is much better to me, but I already accepted the other answer. I upvoted it though, and thank you – a11 May 16 '21 at 21:41
@a11 No worries and thank your upvote :) – ThomasIsCoding May 16 '21 at 21:42

Using R tidyr pivot_wide to get wide-form data from multiple column names and values

2 Answers2

Linked

Related