pivot_wider from only two categories

Question

I have some long data, that I could like to widen. My initial data Looks lLike this

df <- read.table(text = 'Item     Amount   
                          1000    10     
                          1000     1     
                          1500     5     
                          1500     2', 
                 header = TRUE)     
  
library(tidyverse)

Now, I would like to widen it, using tidyr's pivot_wider(),

 df  %>% 
 tidyr::pivot_wider(names_from = Item, values_from = Amount)

# A tibble: 1 x 2
  `1000`    `1500`   
  <list>    <list>   
1 <int [2]> <int [2]>
Warning message:
Values are not uniquely identified; output will contain list-cols.
* Use `values_fn = list` to suppress this warning.
* Use `values_fn = length` to identify where the duplicates arise
* Use `values_fn = {summary_fun}` to summarise duplicates

but I get some error I cannot resolve. What I expected was this, what am I doing wrong? I feel like I am overlooking something essential!

#>     # A tibble: 1 x 2
#>       `1000`   `1500` 
#>       <dbl>    <dbl>
#>       10         1
#>        5         2

score 1 · Answer 1 · answered Sep 01 '21 at 13:51

Since you don't have an identity column to uniquely identify each row, all the values are saved in a single row as a list. You may explicitly pass values_fn as list and unnest all columns to get the data in required structure.

library(dplyr)
library(tidyr)

df %>%
  pivot_wider(names_from = Item, values_from = Amount, values_fn = list) %>%
  unnest(everything())

#  `1000` `1500`
#   <int>  <int>
#1     10      5
#2      1      2

If columns can be of different length, create a unique id and then use pivot_wider.

library(dplyr)
library(tidyr)

df %>%
  mutate(rowid = data.table::rowid(Item)) %>%
  pivot_wider(names_from = Item, values_from = Amount)

pivot_wider from only two categories

1 Answers1