0

I have a data frame/tibble where I've imported a file of plain text (txt). The text very consistent and is grouped by chapter. Sometimes the chapter text is only one row, sometimes it's multiple row. Data is in one column like this:

# A tibble: 10,708 x 1
   x                                                                     
   <chr>                                                                                                                                   
 1 "Chapter 1 "                                                          
 2 "Chapter text. "     
 3 "Chapter 2 "                                                          
 4 "Chapter text. "    
 5 "Chapter 3 "
 6 "Chapter text. "
 7 "Chapter text. "
 8 "Chapter 4 "

I'm trying to clean the data to have a new column for Chapter and the text from each chapter in another column, like this:

# A tibble: 10,548 x 2
   x                                Chapter   
   <chr>                             <chr>
 1 "Chapter text. "               "Chapter 1 "
 2 "Chapter text. "               "Chapter 2 "
 3 "Chapter text. "               "Chapter 3 " 
 4 "Chapter text. "               "Chapter 4 "

I've been trying to use regex to split the and group the data at each occurance of the word 'Chapter #' (chapter followed by a number, but cannot get the result I want. Any advice is much appreciated.

Seth Brundle
  • 160
  • 7
  • is the order of rows 7 and 8 correct? or should it be reversed? or should there be "chapter text. " below row 8? – Shree Nov 21 '18 at 00:12

1 Answers1

1

Based on "Sometimes the chapter text is only one row, sometimes it's multiple row" I am assuming text in rows 6 and 7 belong to chapter 3 and there is no text for chapter 4 in your test data (Your desired output is probably a bit wrong).

Here's a way using dplyr and tidyr. Just run it piece-by-piece and you'll see how the data gets transformed.

df %>% 
  mutate(
    id = cumsum(grepl("[0-9].$", x)),
    x = ifelse(grepl("[0-9].$", x), paste0(x, ":"), x)
  ) %>% 
  group_by(id) %>% 
  summarize(
    chapter = paste0(x, collapse = "")
  ) %>% 
  separate(chapter, into = c("chapter", "text"), sep = ":", extra = "merge")

# A tibble: 4 x 3
     id chapter      text                          
  <int> <chr>        <chr>                         
1     1 "Chapter 1 " "Chapter text. "              
2     2 "Chapter 2 " "Chapter text. "              
3     3 "Chapter 3 " "Chapter text. Chapter text. "
4     4 "Chapter 4 " ""

Data -

df <- structure(list(x = c("Chapter 1 ", "Chapter text. ", "Chapter 2 ", 
"Chapter text. ", "Chapter 3 ", "Chapter text. ", "Chapter text. ", 
"Chapter 4 ")), .Names = "x", class = "data.frame", row.names = c(NA, 
-8L))
Shree
  • 10,835
  • 1
  • 14
  • 36
  • This works great, and you're right. I made a mistake in my example output (as you mentioned). – Seth Brundle Nov 23 '18 at 18:18
  • I also realized that some of the chapters had periods following the numbers and some didn't. I edited the regex to be "Chapter [0-9]" and this seemed to fix the problem with this. Sometimes I have the Chapter # not on it's own row, but buried in text on another row. Any ideas how to handle that? An example from your Data (chapter 3): c("Chapter 1 ", "Chapter text. ", "Chapter 2 ", "Chapter text. ", "This is some text. Chapter 3 ", "Chapter text. ", "Chapter text. ", "Chapter 4 ")) – Seth Brundle Nov 23 '18 at 18:25