r - Gather() 列出列到 R 中的行

Question

我想 gather() 列出列以在我的数据框中创建新行。我正在使用 repurrrsive 包中的《权力的游戏》数据集。下面是我设置问题的代码:

library(tidyverse)
got_chars <- repurrrsive::got_chars
    df <- got_chars %>% 
    {
      tibble::tibble(
        Name = map_chr(., 'name'),
        Gender = map_chr(.,'gender'),
        Culture = map_chr(.,'culture'),
        Born = map_chr(.,'born'),
        Alive = map_chr(.,'alive'),
        Titles = map(.,'titles'),
        Aliases = map(., "aliases"),
        Allegiances = map(., "allegiances"),
        Books = map(.,'books'),
        POV_Books = map(.,'povBooks'),
        TV_Series = map(.,'tvSeries'),
        Actor = map(.,'playedBy')
      )
    }

我希望能够做的，但无法弄清楚的是 gather() 列表列(例如 Books、POV_Books 等)以创建一个新行对于每条记录。例如:

名字 |书
席恩·葛雷乔伊 |权力的游戏
席恩·葛雷乔伊 |刀光剑影
席恩·葛雷乔伊 |群鸦的盛宴

我能得到的最接近的是:

df_books <- df %>%
  separate_rows(Books,sep="\"")

这会起作用，但会在向量中的 c() 字符后面留下一串垃圾。我可以过滤掉那些，但我觉得有更好的方法，我可能只是没有尝试正确的功能。任何建议将不胜感激，谢谢!

Answer 1

您的小标题目前看起来像这样:

df
# # A tibble: 30 x 12
#    Name               Gender Culture Born  Alive Titles Aliases Allegiances Books POV_Books TV_Series Actor
#    <chr>              <chr>  <chr>   <chr> <chr> <list> <list>  <list>      <lis> <list>    <list>    <lis>
#  1 Theon Greyjoy      Male   Ironbo… In 2… TRUE  <chr … <chr [… <chr [1]>   <chr… <chr [2]> <chr [6]> <chr…
#  2 Tyrion Lannister   Male   ""      In 2… TRUE  <chr … <chr [… <chr [1]>   <chr… <chr [4]> <chr [6]> <chr…
#  3 Victarion Greyjoy  Male   Ironbo… In 2… TRUE  <chr … <chr [… <chr [1]>   <chr… <chr [2]> <chr [1]> <chr…
#  4 Will               Male   ""      ""    FALSE <chr … <chr [… <NULL>      <chr… <chr [1]> <chr [1]> <chr…
#  5 Areo Hotah         Male   Norvos… In 2… TRUE  <chr … <chr [… <chr [1]>   <chr… <chr [2]> <chr [2]> <chr…
#  6 Chett              Male   ""      At H… FALSE <chr … <chr [… <NULL>      <chr… <chr [1]> <chr [1]> <chr…
#  7 Cressen            Male   ""      In 2… FALSE <chr … <chr [… <NULL>      <chr… <chr [1]> <chr [1]> <chr…
#  8 Arianne Martell    Female Dornish In 2… TRUE  <chr … <chr [… <chr [1]>   <chr… <chr [1]> <chr [1]> <chr…
#  9 Daenerys Targaryen Female Valyri… In 2… TRUE  <chr … <chr [… <chr [1]>   <chr… <chr [4]> <chr [6]> <chr…
# 10 Davos Seaworth     Male   Wester… In 2… TRUE  <chr … <chr [… <chr [2]>   <chr… <chr [3]> <chr [5]> <chr…
# # ... with 20 more rows

unnest() 将是一个明显的选择，但如果所有列表在它们将扩展到的值数量方面都相同，则不起作用。

library(tidyverse)
unnest(df)
# Error: All nested columns must have the same number of elements.

一种方法是使用以下函数。 flatten() 使数据变“宽”，flattenLong() 将“宽”数据变“长”。关于缺失数据的假设是，如果列表项中的向量比另一个列表项中的匹配向量短，则缺失数据位于最后。

flatten <- function(indt, cols, drop = FALSE) {
  require(data.table)
  if (!is.data.table(indt)) indt <- as.data.table(indt)
  x <- unlist(indt[, lapply(.SD, function(x) max(lengths(x))), .SDcols = cols])
  nams <- paste(rep(cols, x), sequence(x), sep = "_")
  indt[, (nams) := unlist(lapply(.SD, data.table::transpose), recursive = FALSE), .SDcols = (cols)]
  if (isTRUE(drop)) indt[, (cols) := NULL]
  indt[]
}

flattenLong <- function(indt, cols) {
  ob <- setdiff(names(indt), cols)
  x <- flatten(indt, cols, TRUE)
  mv <- lapply(cols, function(y) grep(sprintf("^%s_", y), names(x)))
  setorderv(melt(x, measure.vars = mv, value.name = cols), ob)[]
}

这是一种使用方法，将其应用于所有 list 列。

flattenLong(df, names(df)[sapply(df, is.list)])
#               Name Gender  Culture                                     Born Alive variable
#   1: Aeron Greyjoy   Male Ironborn In or between 269 AC and 273 AC, at Pyke  TRUE        1
#   2: Aeron Greyjoy   Male Ironborn In or between 269 AC and 273 AC, at Pyke  TRUE        2
#   3: Aeron Greyjoy   Male Ironborn In or between 269 AC and 273 AC, at Pyke  TRUE        3
#   4: Aeron Greyjoy   Male Ironborn In or between 269 AC and 273 AC, at Pyke  TRUE        4
#   5: Aeron Greyjoy   Male Ironborn In or between 269 AC and 273 AC, at Pyke  TRUE        5
# ---                                                                                      
# 476:          Will   Male                                                   FALSE       12
# 477:          Will   Male                                                   FALSE       13
# 478:          Will   Male                                                   FALSE       14
# 479:          Will   Male                                                   FALSE       15
# 480:          Will   Male                                                   FALSE       16
#                                      Titles        Aliases           Allegiances                Books
#   1:              Priest of the Drowned God   The Damphair House Greyjoy of Pyke    A Game of Thrones
#   2: Captain of the Golden Storm (formerly) Aeron Damphair                    NA     A Clash of Kings
#   3:                                     NA             NA                    NA    A Storm of Swords
#   4:                                     NA             NA                    NA A Dance with Dragons
#   5:                                     NA             NA                    NA                   NA
# ---                                                                                                 
# 476:                                     NA             NA                    NA                   NA
# 477:                                     NA             NA                    NA                   NA
# 478:                                     NA             NA                    NA                   NA
# 479:                                     NA             NA                    NA                   NA
# 480:                                     NA             NA                    NA                   NA
#              POV_Books TV_Series         Actor
#   1: A Feast for Crows  Season 6 Michael Feast
#   2:                NA        NA            NA
#   3:                NA        NA            NA
#   4:                NA        NA            NA
#   5:                NA        NA            NA
# ---                                          
# 476:                NA        NA            NA
# 477:                NA        NA            NA
# 478:                NA        NA            NA
# 479:                NA        NA            NA
# 480:                NA        NA            NA

您还可以执行以下任何操作来处理单个列:

flattenLong(df[c(names(df)[!sapply(df, is.list)], "Books")], "Books")

flattenLong(df[c("Name", "Gender", "Culture", "Born", "Alive", "Books")], "Books")

df %>% 
  select(Name, Gender, Culture, Born, Alive, Books) %>%
  flattenLong("Books")

---

这与“tidyverse”方法完全不同。它以不同方式处理NULL，并且unnest每个组的长度相同。考虑以下数据集:

mydf <- data.frame(V1 = c("a", "b", "c"), 
                   V2 = I(list(c(10, 20), NA_real_, c(20, 40, 60))), 
                   V3 = I(list(NULL, c("x", "y", "z"), c("BA", "BB"))))
mydf                   
#   V1         V2      V3
# 1  a     10, 20        
# 2  b         NA x, y, z
# 3  c 20, 40, 60  BA, BB

差异 #1:每组值的数量:

# Note the resulting number of values per group
# Equivalent of
# as.data.table(mydf)[, list(unlist(V2)), V1]
mydf %>% select(V1, V2) %>% unnest()
#   V1 V2
# 1  a 10
# 2  a 20
# 3  b NA
# 4  c 20
# 5  c 40
# 6  c 60

flattenLong(mydf[c("V1", "V2")], "V2")
#    V1 variable V2
# 1:  a     V2_1 10
# 2:  a     V2_2 20
# 3:  a     V2_3 NA
# 4:  b     V2_1 NA
# 5:  b     V2_2 NA
# 6:  b     V2_3 NA
# 7:  c     V2_1 20
# 8:  c     V2_2 40
# 9:  c     V2_3 60

区别 #2:处理 NULL 值

mydf %>% select(V1, V3) %>% unnest()
# Error: Each column must either be a list of vectors or a list of data frames [V3]

flattenLong(mydf[c("V1", "V2")], "V2")
#    V1 variable V3
# 1:  a     V3_1 NA
# 2:  a     V3_2 NA
# 3:  a     V3_3 NA
# 4:  b     V3_1  x
# 5:  b     V3_2  y
# 6:  b     V3_3  z
# 7:  c     V3_1 BA
# 8:  c     V3_2 BB
# 9:  c     V3_3 NA