Transpose single column after NA to multiple columns with R-CodePudding

I would like to transpose a single column to multiple columns after two NA's. I have tried to transpose at every nth row, but there is no pattern.

Example:

x<-data.frame(col1=c('A','F',1,15,'','','A','Z','$35','P',2,'','','B','ER',3,'P',56,'YT65','','','B','AZ','$5','PO',28,'',''))

What I am hoping to accomplish is:

col1	col 2	col3	col4	col5	col6
A	F	1	15
A	Z	$35	P	2
B	ER	3	P	56	YT65
B	AZ	$5	PO	28

CodePudding user response：

It's a little convoluted, but you could do:

z <- lapply(split(x$col1, cumsum(!nzchar(x$col1)) %/% 2), function(x) { 
  if(!nzchar(x[1])) x[-1] else x
})

z <- do.call(rbind, lapply(z, function(x) {
  c(x, rep('', max(lengths(z)) - length(x)))
  }))

as.data.frame(z[rowSums(z == '') != ncol(z), colSums(z == '') != nrow(z)])
#>   V1 V2  V3 V4 V5   V6
#> 0  A  F   1 15        
#> 1  A  Z $35  P  2     
#> 2  B ER   3  P 56 YT65
#> 3  B AZ  $5 PO 28

CodePudding user response：

A possible alternative approach:

library(tidyverse)

df <- data.frame(col1 = c("A", "F", 1, 15, "", "", 
                         "A", "Z", "$35", "P", 2, "", "", 
                         "B", "ER", 3, "P", 56, "YT65", "", "", 
                         "B", "AZ", "$5", "PO", 28, "", ""))

df |> 
  mutate(group = cumsum(col1 == "")) |> 
  filter(col1 != "") |> 
  group_by(group) |> 
  mutate(col = row_number()) |> 
  ungroup() |> 
  pivot_wider(names_from = col, values_from = col1) |> 
  select(-group)
#> # A tibble: 4 × 6
#>   `1`   `2`   `3`   `4`   `5`   `6`  
#>   <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 A     F     1     15    <NA>  <NA> 
#> 2 A     Z     $35   P     2     <NA> 
#> 3 B     ER    3     P     56    YT65 
#> 4 B     AZ    $5    PO    28    <NA>

^{Created on 2022-06-07 by the reprex package (v2.0.1)}