Pivot wider in R with multiple columns

user_12860167 0
Viewed 374

I am having trouble converting a particular dataset from long to wide.

col1           col2
ID             55.
animal.        dog
animal         bear
animal         rabbit
shape.         circle
ID             67.
animal.        cat
shape.         square

As you can see, some IDs have multiple observations for "animal" and so I want to make multiple columns like this:

ID.   animal.  animal2 animal3  shape
55.    dog       bear.  rabbit  circle
67.    cat.      NA     NA      square

Any help is appreciated!

r
1 Answers

Try this solution.
Most of the work was creating an separate ID column and then creating the unique names for the columns.

library(tidyr)
library(dplyr)
library(vctrs)

df<- structure(list(col1 = c("ID", "animal", "animal", "animal", "shape", "ID", "animal", "shape"), 
                  col2 = c("55.", "dog", "bear", "rabbit", "circle", "67.", "cat", "square")), 
               class = "data.frame", row.names = c(NA, -8L))
   
#create the ID column
df$ID <- NA
#find the ID rows
idrows <- which(df$col1 == "ID")
#fill column and delete rows
df$ID[idrows] <- df$col2[idrows]
df <- fill(df, ID, .direction = "down")
df <- df[-idrows, ]

 #create unique names in each grouping and the pivot wider
df %>% group_by(ID) %>% 
   mutate(col1=vec_as_names(col1, repair = "unique")) %>%  
   mutate(col1=stringr::str_replace( col1, "\\.+1", "")) %>% 
   ungroup()  %>%
   pivot_wider(id_cols = "ID", names_from = "col1", values_from = "col2")


  ID    animal  animal...2 animal...3 shape
  <chr> <chr>   <chr>      <chr>      <chr> 
1 55.   dog     bear       rabbit     circle
2 67.   cat     NA         NA         square

Another alternatives based on one of your previous questions:

df %>%  group_by(ID) %>% 
   mutate(col1 = paste0(col1, data.table::rowid(col1))) %>% 
   ungroup()  %>%
   pivot_wider(id_cols = "ID", names_from = "col1", values_from = "col2")

or

df %>% 
   pivot_wider(id_cols = "ID", names_from = "col1", values_from = "col2") %>%
    unnest_wider(  "shape", names_sep = "_") %>% unnest_wider(  "animal", names_sep = "_")
user_57922440
Related