r - 是否有更优雅的方法将参差不齐的数据转换为整洁的数据框

Question

我有一个数据框，其中包含一列参差不齐的数据:“主题”，其中每个主题都是一串字符，相邻主题之间用分隔符(在本例中为“|”)分隔:

library(lubridate)
events <- data.frame(
  date  =dmy(c(     "12/6/2012",           "13/7/2012",    "4/8/2012")),
  days  =    c(               1,                     6,           0.5),
  name  =    c("Intro to stats", "Stats Winter school", "TidyR tools"),
  topics=    c( "probability|R", "R|regression|ggplot", "tidyR|dplyr"),
  stringsAsFactors=FALSE
  )

events 数据框如下所示:

        date days                name              topics
1 2012-06-12  1.0      Intro to stats       probability|R
2 2012-07-13  6.0 Stats Winter school R|regression|ggplot
3 2012-08-04  0.5         TidyR tools         tidyR|dplyr

我想转换此数据框，使每一行包含一个主题，并指示在该主题上花费了多少天，假设如果在 D 天内呈现 N 个主题，则每个主题花费 D/N 天主题。

我不得不匆忙做这件事，并按如下方式做了:

library(dplyr)

events %>%
  # Figure out how many topics were delivered at each event
  mutate(
    ntopics=sapply(
      gregexpr("|", topics, fixed=TRUE),
      function(x)(1 + sum(attr(x, "match.length") > 0 ))
      )
    ) %>%
  # Create a data frame with one topic per row
  do(data.frame(
    date    =rep(   .$date, .$ntopics),
    days    =rep(   .$days, .$ntopics),
    name    =rep(   .$name, .$ntopics),
    ntopics =rep(.$ntopics, .$ntopics),
    topic   =unlist(strsplit(.$topics, "|", fixed=TRUE)),
    stringsAsFactors=FALSE
    )) %>%
  # Estimate roughly how many days were spent on each topic
  mutate(daysPerTopic=days/ntopics)

这给了我们

        date days                name ntopics       topic daysPerTopic
1 2012-06-12  1.0      Intro to stats       2 probability         0.50
2 2012-06-12  1.0      Intro to stats       2           R         0.50
3 2012-07-13  6.0 Stats Winter school       3           R         2.00
4 2012-07-13  6.0 Stats Winter school       3  regression         2.00
5 2012-07-13  6.0 Stats Winter school       3      ggplot         2.00
6 2012-08-04  0.5         TidyR tools       2       tidyR         0.25
7 2012-08-04  0.5         TidyR tools       2       dplyr         0.25

我很想知道如何更优雅地实现这一目标。

Answer 1

你可以试试:

library(data.table)
library(devtools)
source_gist(11380733) ## 

dat <- cSplit(events, "topics", sep="|", "long")

dat1 <-  dat[, c("ntopics", "daysperTopic") := {m= length(days);list(m, days/m)},
                 by=name][,c(1:3,5,4,6),with=F]

dat1
#         date days                name ntopics      topics daysPerTopic
# 1: 2012-06-12  1.0      Intro to stats       2 probability         0.50
# 2: 2012-06-12  1.0      Intro to stats       2           R         0.50
# 3: 2012-07-13  6.0 Stats Winter school       3           R         2.00
# 4: 2012-07-13  6.0 Stats Winter school       3  regression         2.00
# 5: 2012-07-13  6.0 Stats Winter school       3      ggplot         2.00
# 6: 2012-08-04  0.5         TidyR tools       2       tidyR         0.25
# 7: 2012-08-04  0.5         TidyR tools       2       dplyr         0.25

dplyr 可以缩短

library(stringr)
library(dplyr)

res <- mutate(events %>% 
 mutate(
 ntopics = str_count(
     topics, pattern = "\\|") + 1, N = row_number()) %>% 
  do(data.frame(
        .[rep(.$N, .$ntopics), ], 
     topic = unlist(strsplit(.$topics, "|", fixed = TRUE)))), 
   daysPerTopic = days/ntopics) %>%
  select(-topics, -N)
 res
 #        date days                name ntopics       topic daysPerTopic
 #1 2012-06-12  1.0      Intro to stats       2 probability         0.50
 #2 2012-06-12  1.0      Intro to stats       2           R         0.50
 #3 2012-07-13  6.0 Stats Winter school       3           R         2.00
 #4 2012-07-13  6.0 Stats Winter school       3  regression         2.00
 #5 2012-07-13  6.0 Stats Winter school       3      ggplot         2.00
 #6 2012-08-04  0.5         TidyR tools       2       tidyR         0.25
 #7 2012-08-04  0.5         TidyR tools       2       dplyr         0.25