Pivot_Wider Issue "Values in 'Values_From' Are Not Uniquely Identified; Output Will Contain List-Cols"

pivot_wider issue Values in `values_from` are not uniquely identified; output will contain list-cols

Create a unique identifier row for each name and then use pivot_wider

library(dplyr)

d %>%
  group_by(name) %>%
  mutate(row = row_number()) %>%
  tidyr::pivot_wider(names_from = name, values_from = val) %>%
  select(-row)

# A tibble: 51 x 4
#   time          x1 `C Farolillo` `Plaza Eliptica`
#   <date>     <dbl>         <dbl>            <dbl>
# 1 2016-04-20  51.5             7               32
# 2 2016-04-21  56.3             3               25
# 3 2016-04-22  56.3             7               31
# 4 2016-04-23  57.9            13               34
# 5 2016-04-24  58.7             7               26
# 6 2016-04-25  59.0             9               33
# 7 2016-04-26  64.5            20               35
# 8 2016-04-27  61.9            19               43
# 9 2016-04-28  60.3             4               22
#10 2016-04-29  59.4             5               22
# … with 41 more rows

Error in pivot_wider in R doubled: Values in `Data` are not uniquely identified; output will contain list-cols

1) You don't need category2

2) There is no explanation of "no.donors" but based on your expected output it seems any category which repeats twice is given that value.

library(dplyr)
df %>%
 select(-category2) %>%
 group_by(council_name, period, category) %>%
 mutate(category = ifelse(row_number() == 1, category, "no.donors")) %>%
 tidyr::pivot_wider(names_from = category,
                    values_from = data)


#  council_name         period   glass fridges paper no.donors
#  <chr>                <chr>    <dbl>   <dbl> <dbl>     <dbl>
#1 Barking and Dagenham 1st 2006   333     222   100        98
#2 Barking and Dagenham 2nd 2006   450     540    33       450
#3 Barnet               1st 2006   560     120    NA        NA

use pivot_longer and pivot_wider in combination

The code doesn't necessarily fail but return a warning since you have more than one value in each cell. If the number of values in each column are going to be the same you can unnest the list output.

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(starts_with('Data'), values_drop_na = TRUE) %>%
  arrange(name) %>%
  pivot_wider(names_from = name,values_from = value, values_fn = list) %>%
  unnest()

#  Data1 Data2 Data3
#  <dbl> <dbl> <dbl>
#1     3     3     7
#2     2     5     5
#3     1     3     1

pivot_wider: How to add rows for non unique values?

You can renumber the num_name column, cast the data to wide and fill the values.

library(dplyr)
library(tidyr)

species_list %>%
  group_by(num_sp, language) %>%
  mutate(num_name = row_number()) %>%
  pivot_wider(names_from = language, values_from = name) %>%
  fill(latin, english) %>%
  ungroup
  

#  num_sp num_name latin                english           
#   <dbl>    <int> <chr>                <chr>             
#1    100        1 marinus thingus      NA                
#2    101        1 aquaticus stuffae    blob              
#3    101        2 aquaticus stuffae    water being       
#4    101        3 aquaticus stuffae    marine creature   
#5    102        1 altermarinus stuffae other marine stuff
#6    103        1 NA                   unknown thingy    

Pivot_wider in tidyr creates list cols even when there is no duplicate or missing data

The issue is because of NA values. There is around 59 rows with NA in them.

library(readxl)
library(tidyr)

df_testing <- read_excel("Testing_Data.xlsx")
df_testing %>% filter(is.na(`Tag No.`))

# A tibble: 59 x 4
#   `Tag No.` Reading Date                Time               
#   <chr>       <dbl> <dttm>              <dttm>             
# 1 NA             NA NA                  NA                 
# 2 NA             NA NA                  NA                 
# 3 NA             NA NA                  NA                 
# 4 NA             NA NA                  NA                 
# 5 NA             NA NA                  NA                 
# 6 NA             NA NA                  NA                 
# 7 NA             NA NA                  NA                 
# 8 NA             NA NA                  NA                 
# 9 NA             NA NA                  NA                 
#10 NA             NA NA                  NA                 
# … with 49 more rows

Dropping the NA rows doesn't give list columns.

df_output <- pivot_wider(na.omit(df_testing), names_from = `Tag No.`, values_from = Reading)
df_output

Clarification on Reshaping DataFrame from LONG to WIDE in R with Gather/Spread

You need to be more explicit on what you want, we can only assume. You cannot expect any values that are not present in your widest format. I take a guess here in that you want something like this.

test.long2 %>%
    pivot_wider(id_cols = c("pid", "timewave"), 
    names_from = pid, 
    values_from = c(dev_icd, lab_bnp), 
    names_sep = "_pid")

# A tibble: 5 x 5
  timewave dev_icd_pid1 dev_icd_pid2 lab_bnp_pid1 lab_bnp_pid2
  <fct>    <fct>        <fct>               <dbl>        <dbl>
1 1        No           Yes                  388          949.
2 2        No           NA                   199           NA 
3 3        No           NA                   388.          NA 
4 4        No           NA                   318           NA 
5 5        No           NA                   154           NA 

Pivot_wider : tydr Pivot table filled with NA values

You can do this:

df %>% 
  pivot_wider(id_cols = -ID, names_from = Variable, values_from = Score) %>% 
  mutate(ID = row_number(), .before = Compagny)

# A tibble: 2 x 5
     ID Compagny  size lenght diameter
  <int> <chr>    <int>  <int>    <int>
1     1 x           12     15        8
2     2 y           20      4        7

---

Related Topics