How can I fill with NA values using pivot

This is my dataframe:

df <- tibble(x = c('a','a','a','a','a','b','b','b'), y = 1:8)

I want this final output:

df_final <- tibble(x = c('a','a','a','a','a'), y = 1:5, x_1 = c('b','b','b',NA,NA), y_1 = c(6,7,8,NA,NA))

How can I do this using pivot_wider function?

CodePudding user response：

What you're asking for is not quite a pivoting, try this.

library(dplyr)
library(purrr) # map_dfc
df %>%
  split(., .$x) %>%
  map_dfc(~ lapply(., `length<-`, max(table(df$x))))
# Warning: Outer names are only allowed for unnamed scalar atomic inputs
# New names:
# * x -> x...1
# * y -> y...2
# * x -> x...3
# * y -> y...4
# # A tibble: 5 x 4
#   x...1 y...2 x...3 y...4
#   <chr> <int> <chr> <int>
# 1 a         1 b         6
# 2 a         2 b         7
# 3 a         3 b         8
# 4 a         4 <NA>     NA
# 5 a         5 <NA>     NA

CodePudding user response：

Another tidyverse solution. We can create an ID column by x, split the data frame by x, use reduce and left_join to join these data frames.

library(tidyverse)

df2 <- df %>%
  group_by(x) %>%
  mutate(ID = 1:n()) %>%
  split(.$x) %>%
  reduce(.f = left_join, by = "ID") %>%
  select(-ID) %>%
  setNames(c("x", "y", "x_1", "y_1"))
df2
# # A tibble: 5 x 4
#   x         y x_1     y_1
#   <chr> <int> <chr> <int>
# 1 a         1 b         6
# 2 a         2 b         7
# 3 a         3 b         8
# 4 a         4 NA       NA
# 5 a         5 NA       NA

CodePudding user response：

With tidyverse,

df %>% group_split(x) %>% reduce(merge, by = 0,all=T) %>% select(-1)

gives,

  x.x y.x  x.y y.y
1   a   1    b   6
2   a   2    b   7
3   a   3    b   8
4   a   4 <NA>  NA
5   a   5 <NA>  NA