Pivot wider without list cols or excessive rows being created

user_9496387 0
Viewed 269

I have a dataframe of string patterns currently in a long format (below), but I need in a wide format.

df <- structure(list(lep = c("ef", "efe", "efef", "efefe", "efefef", 
                             "efefefe", "efefefef", "efefefefe", "efefefefef", "efefefefefef", 
                             "eg"), char_n = c(2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 12L, 2L
                             ), char_1 = c("e", "e", "e", "e", "e", "e", "e", "e", "e", "e", 
                                           "e")), row.names = c(NA, -11L), class = c("tbl_df", "tbl", "data.frame"
                                           ))
# A tibble: 11 x 3
   lep          char_n char_1
   <chr>         <int> <chr> 
 1 ef                2 e     
 2 efe               3 e     
 3 efef              4 e     
 4 efefe             5 e     
 5 efefef            6 e     
 6 efefefe           7 e     
 7 efefefef          8 e     
 8 efefefefe         9 e     
 9 efefefefef       10 e     
10 efefefefefef     12 e     
11 eg                2 e

This is the output I would like

# A tibble: 2 x 11
   char_1 x2    x3    x4    x5    x6     x7      x8       x9        x10        x12         
   <chr>  <chr> <chr> <chr> <chr> <chr>  <chr>   <chr>    <chr>     <chr>      <chr>       
 1 e      ef    efe   efef  efefe efefef efefefe efefefef efefefefe efefefefef efefefefefef        
 2 e      eg    NA    NA    NA    NA     NA      NA       NA        NA         NA

However using the following I have only been able to get the below output:

df %>%
mutate(row = row_number()) %>%
  pivot_wider(names_from = char_n, values_from=lep) %>% select(-row) %>%
  janitor::clean_names(.)
 
# A tibble: 11 x 11
   char_1 x2    x3    x4    x5    x6     x7      x8       x9        x10        x12         
   <chr>  <chr> <chr> <chr> <chr> <chr>  <chr>   <chr>    <chr>     <chr>      <chr>       
 1 e      ef    NA    NA    NA    NA     NA      NA       NA        NA         NA          
 2 e      NA    efe   NA    NA    NA     NA      NA       NA        NA         NA          
 3 e      NA    NA    efef  NA    NA     NA      NA       NA        NA         NA          
 4 e      NA    NA    NA    efefe NA     NA      NA       NA        NA         NA          
 5 e      NA    NA    NA    NA    efefef NA      NA       NA        NA         NA          
 6 e      NA    NA    NA    NA    NA     efefefe NA       NA        NA         NA          
 7 e      NA    NA    NA    NA    NA     NA      efefefef NA        NA         NA          
 8 e      NA    NA    NA    NA    NA     NA      NA       efefefefe NA         NA          
 9 e      NA    NA    NA    NA    NA     NA      NA       NA        efefefefef NA          
10 e      NA    NA    NA    NA    NA     NA      NA       NA        NA         efefefefefef
11 e      eg    NA    NA    NA    NA     NA      NA       NA        NA         NA

I'm not sure if altering how I'm changing to a wider format or the steps after should be different?

Any help would be appreciated!

tidyr dplyr r
2 Answers

We create the 'rn' based on the column 'char_n' and then use pivot_wider

library(dplyr)
library(tidyr)
library(stringr)
df %>%
    mutate(
           rn = cumsum(c(TRUE, diff(char_n) < 0)), char_n = str_c('x', char_n)) %>% 
    pivot_wider(names_from = char_n,  values_from = lep) %>%
    select(-rn)

-output

# A tibble: 2 x 11
#  char_1 x2    x3    x4    x5    x6     x7      x8       x9        x10        x12         
#  <chr>  <chr> <chr> <chr> <chr> <chr>  <chr>   <chr>    <chr>     <chr>      <chr>       
#1 e      ef    efe   efef  efefe efefef efefefe efefefef efefefefe efefefefef efefefefefef
#2 e      eg    <NA>  <NA>  <NA>  <NA>   <NA>    <NA>     <NA>      <NA>       <NA>
user_37322710

In base R, you could use reshape:

reshape(transform(df, id = cumsum(c(0,diff(char_n)<0))), 
         dir='wide', idvar = c('char_1','id'), timevar = 'char_n')

   char_1 id lep.2 lep.3 lep.4 lep.5  lep.6   lep.7    lep.8     lep.9     lep.10       lep.12
1       e  0    ef   efe  efef efefe efefef efefefe efefefef efefefefe efefefefef efefefefefef
11      e  1    eg  <NA>  <NA>  <NA>   <NA>    <NA>     <NA>      <NA>       <NA>         <NA>
user_83802720
Related