r - 在 R 中使用多列旋转更宽

Question

我在将特定数据集从长数据集转换为宽数据集时遇到问题。

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

如您所见，某些 ID 对“动物”有多个观察结果，因此我想像这样创建多个列:

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

感谢任何帮助!

Answer 1

试试这个解决方案。
大部分工作是创建一个单独的 ID 列，然后为列创建唯一的名称。

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

基于您之前的一个问题的另一种选择:

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

或

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