Show duplicate value on a separate row in pivot wider

user_12070334 0
Viewed 105

I have seen tons of answers but could not get it right. Basically I want to show duplciate on separate row while performing pivot widerSample data.I created a unique variable as well but the result was either nested row or separate row for each column.

df <- structure(list(identifier = c("e1", "e1", "e2", "e2", "e1", "e1", 
"e1", "e1", "e2", "e2"), label = c("Monaco", "became", "the", 
"first", "the", "the", "Monaco", "became", "the", "first"), id = c("CP1", 
"CP1", "CP1", "CP1", "CP1", "CP1", "CP2", "CP2", "CP2", "CP2"
), value = c(0L, 0L, 1L, 0L, 10L, 1L, 1L, 0L, 1L, 0L)), class = "data.frame", row.names = c(NA, 
-10L))

library(tidyverse)

#My try 

df %>%
group_by(identifier,label) %>% 
mutate(rn=row_number()) %>% 
  pivot_wider(  names_from="id",
              values_from="value")
tidyr dplyr tidyverse r
2 Answers
library(data.table)
library(tidyr)
unnest( dcast(setDT(df), identifier + label ~ id, value.var = "value", 
              fill = NA, fun.aggregate = list), cols = c("CP1", "CP2"))
# # A tibble: 6 x 4
#   identifier label    CP1   CP2
#   <chr>      <chr>  <int> <int>
# 1 e1         Monaco     0     1
# 2 e1         became     0     0
# 3 e1         the       10    NA
# 4 e1         the        1    NA
# 5 e2         first      0     0
# 6 e2         the        1     1
user_63562780

You can use -

library(dplyr)
library(tidyr)

df %>%
  pivot_wider(names_from=id,values_from=value, values_fn = list) %>%
  unnest(cols = c(CP1, CP2))

#  identifier label    CP1   CP2
#  <chr>      <chr>  <int> <int>
#1 e1         Monaco     0     1
#2 e1         became     0     0
#3 e2         the        1     1
#4 e2         first      0     0
#5 e1         the       10    NA
#6 e1         the        1    NA

You were close with your attempt as well, you had to include id in group_by -

df %>%
  group_by(identifier,label, id) %>% 
  mutate(rn=row_number()) %>% 
  pivot_wider(names_from=id,values_from=value)
user_39629140
Related