Lengthen data frame with duplicate names

Question

I have a data.frame that contains duplicate column names that I want to lengthen. I don't want to fix the names because they correspond to values in my future column. I am trying to use pivot_longer but it throws an error.

Error: Can't transform a data frame with duplicate names.

I looked at the documentation for the function and used the "names_repair" argument to get around the issue but it didn't help.

I also found this issue on tidyvere's github but I'm not sure what's going on in there.

Here's my code:

library(dplyr)
library(tidyr)
df %>% 
  mutate_all(as.character) %>% 
  pivot_longer(-a, names_to = "Names", values_to = "Values", names_repair = "minimal")

Is there a way to do this?

Desired output:

  a     Names Values
  <chr> <chr> <chr> 
1 1     b     4     
2 1     c     a     
3 1     c     d     
4 2     b     5     
5 2     c     b     
6 2     c     e     
7 3     b     6     
8 3     c     c     
9 3     c     f 

Sample data:

df <- setNames(data.frame(c(1,2,3), 
                          c(4,5,6), 
                          c("a","b","c"), 
                          c("d","e","f"), 
                          stringsAsFactors = F), 
               c("a","b","c","c"))

Answer 1

The problem is not pivot_wider, it can be used on data.frames containing columns with the same name - mutate can't. So we need to transform the columns to character columns either by (i) using base R or (ii) if you want to stay in the larger tidyverse purrr::modify_at (after all a data.frame is always a list). After that its just a regular call to pivot_wider.

df <- setNames(data.frame(c(1,2,3), 
                          c(4,5,6), 
                          c("a","b","c"), 
                          c("d","e","f"), 
                          stringsAsFactors = F), 
               c("a","b","c","c"))

library(dplyr)
library(tidyr)

# Alternatively use base R to transform cols to character
# df[,c("a", "b")] <- lapply(df[,c("a", "b")], as.character)

df %>%
  purrr::modify_at(c("a","b"), as.character) %>% 
  pivot_longer(-a,
               names_to = "Names",
               values_to = "Values")
#> # A tibble: 9 x 3
#>   a     Names Values
#>   <chr> <chr> <chr> 
#> 1 1     b     4     
#> 2 1     c     a     
#> 3 1     c     d     
#> 4 2     b     5     
#> 5 2     c     b     
#> 6 2     c     e     
#> 7 3     b     6     
#> 8 3     c     c     
#> 9 3     c     f

^{Created on 2021-02-23 by the reprex package (v0.3.0)}