Is there an R function in pivot wider to spread out list-columns data from a pivoted dataset?

Question

I'm attempting to widen a dataframe from an existing one, while keeping an extra column attached, but am receiving list-columns instead of spread out cells.

I have a large dataset I am attempting to widen:

Location	Names	Result
A	Foo	1
A	Bar	1.5
B	Foo	2
B	Foo	1
C	Bar	2.5
D	Baz	0.5

I attempted:

Subset <- Data %>%
  group_by(Names) %>%
  pivot_wider(
    names_from = Names,
    values_from = Result,
    id_cols = Location
  )

And receive something like this:

Location	Foo	Bar	Baz
A	1	1.5	etc.
B	c(2,1)	etc.	etc.
C	NA	etc.	etc.

With this warning: Values from Result are not uniquely identified; output will contain list-cols. • Use values_fn = list to suppress this warning. • Use values_fn = {summary_fun} to summarise duplicates. • Use the following dplyr code to identify duplicates.

{data} %>%
    dplyr::group_by(Location, Names) %>%
    dplyr::summarise(n = dplyr::n(), .groups = "drop") %>%
    dplyr::filter(n > 1L)

I am wondering if there is any way to spread out the combined values in 1 cell to multiple rows to be something like this, but be able to perform this on many rows at once, as the dataframe is over 50 rows long.

Location	Foo	Bar
A	1	1.5
B	2	etc.
B	1
C	NA

Any way to do this within pivot_wider, or another function? Thank you!

You could an identifier column to make your rows unique, e.g. try `Data %>% mutate(row_id = row_number(), .by = c(Location, Names)) |> pivot_wider(names_from = Names, values_from = Result)`. See [pivot_wider issue "Values in `values_from` are not uniquely identified; output will contain list-cols"](https://stackoverflow.com/questions/58837773/pivot-wider-issue-values-in-values-from-are-not-uniquely-identified-output-w) — stefan, May 30 '23 at 16:09
That worked perfectly, thanks so much! Don't know how I missed that thread you mentioned, my bad! — levane07, May 30 '23 at 18:09

score 0 · Accepted Answer · answered May 30 '23 at 17:09

As suggested in the comments, one way is to add a row-id as an extra identifier, another way is to call tidyr::unnest() afterwards.

Data |>
    tidyr::pivot_wider(
        id_cols = 'location', names_from = 'names', values_from = 'result', 
        values_fn = list
    ) |> 
    tidyr::unnest(cols = -location)
# A tibble: 5 × 4
  location   foo   bar   baz
  <chr>    <dbl> <dbl> <dbl>
1 A            1   1.5  NA  
2 B            2  NA    NA  
3 B            1  NA    NA  
4 C           NA   2.5  NA  
5 D           NA  NA     0.5

score 0 · Answer 2 · answered May 30 '23 at 17:17

using data.table:

library(data.table)
dcast(setDT(df), Location + rowid(Names, Location)~Names)
#Using 'Result' as value column. Use 'value.var' to override
   Location Names Bar Baz Foo
1:        A     1 1.5  NA   1
2:        B     1  NA  NA   2
3:        B     2  NA  NA   1
4:        C     1 2.5  NA  NA
5:        D     1  NA 0.5  NA

Is there an R function in pivot wider to spread out list-columns data from a pivoted dataset?

2 Answers2