Are there more elegant ways to transform ragged data into a tidy dataframe

Question

I have a dataframe that contains a column of ragged data: "topics" where each topic is a string of characters, and adjacent topics are separated from each other by a delimiter ("|" in this case):

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
  )

The events dataframe looks like:

        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

I want to transform this dataframe so that each row contains a single topic, and an indication of how many days were spent on that topic, assuming that if N topics were presented over D days, D/N days were spent on each topic.

I had to do this in a hurry, and did so as follows:

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)

which gives us

        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

I would love to know how do achieve this more elegantly.

What you're probably looking for is `unnest()`, but I haven't implemented it yet: https://github.com/hadley/tidyr/issues/3 — hadley, Aug 03 '14 at 13:40
Good to know (a) that I'm not missing an obvious dplyr/tidyr solution (b) that this notion has a place in the canon of tidyr. — David Lovell, Aug 04 '14 at 03:38

akrun · Answer 1 · 2014-08-03T12:05:54.910

You could try:

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

The dplyr could be shortened

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

score 1 · Answer 2 · answered Aug 03 '14 at 16:06

Thought i would add a base R solution, although stretching it to call it more elegant. Just a simple string split and reshape

# split topics column 
events <- cbind(events, 
                read.table(text=events$topics, sep="|", fill=TRUE, 
                                                header=FALSE, na.strings=""))

# calculate statistics
events$ntopics <- rowSums(!is.na(events[paste0("V",1:3)]))
events$daysPerTopic <- events$days / events$ntopics

# reshape
na.omit(reshape(events, varying = list(paste0("V",1:3)),
                                        v.names="topics", direction="long"))

Are there more elegant ways to transform ragged data into a tidy dataframe

2 Answers2

Linked