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"))

Maybe a set of functions to: (1) Identify columns with same names >> (2) creating a tidy dataset with unique column names >> (3) your above code — jsv, Feb 23 '21 at 21:00
I think something like this is what you are looking for: https://stackoverflow.com/a/62063471/7973626 — Indrajeet Patil, Feb 23 '21 at 21:04
@IndrajeetPatil Thanks, but that's not the same problem. I got rid of that problem with `mutate_all(as.character)`. — hmhensen, Feb 23 '21 at 21:06
I think it is the same problem. `pivot_longer` doesn't work because `b` is `double` while `c` is `character` and you are trying to force them into a single column `Values` and `tidyr` doesn't know how to do that. — Indrajeet Patil, Feb 23 '21 at 21:10
@IndrajeetPatil I think you're right. The answer by @TimTeaFan appears to recognize that issue and uses `base` to get around it. — hmhensen, Feb 23 '21 at 21:18

TimTeaFan · Accepted Answer · 2021-02-23T21:26:41.797

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)}

Lengthen data frame with duplicate names

1 Answers1