extract and organise textfile to dataframe

I have a huge text file with the following structure:

AA<-tibble::tribble(
  ~`-------------------------------------------------`,
  "ABCD 2002201234 09-06-2015 10:34",
  "-------------------------------------------------",
  "Lorem ipsum",
  "Lorem ipsum",
  "Lorem ipsum Lorem ipsum",
  "Lorem ipsum: Lorem ipsum",
  "123456",
  "AB",
  "AB",
  "Lorem ipsum",
  "-------------------------------------------------",
  "ABCDEF 1001101234 05-03-2011 09:15",
  "-------------------------------------------------",
  "TEST",
  "TEST"
)

I want to organise the above into a DF with variables: ID, DATE and TEXT. ID should be the 10-digit number (in the example 2002201234 and 1001101234) DATE is self explanatory and TEXT should be all text between the bottom line ("-------------") to the upper line of next post.

Which is the easiest way to perform this?

Regards, H

in base R:

x <- paste(AA[[1]], collapse = '\n')
y <- regmatches(x, gregexec("(\\d{10}) *(.*?)\n-+([^-]+)", x, perl = TRUE))[[1]]
setNames(data.frame(t(y[2:4,])), c('ID', 'Date', 'Text'))

  ID         Date             Text                                        
  <chr>      <chr>            <chr>                                       
1 2002201234 09-06-2015 10:34 "\nLorem ipsum\nLorem ipsum\nLorem ipsum Lo…
2 1001101234 05-03-2011 09:15 "\nTEST\nTEST" 

Here is a solution using pmap which might a bit overkill or slow depending how big your file is.

You need to adjust:

• the correct date format (it's not unambiguous)
• how the text should be collapsed, right now it is with a line break

library(stringr)
library(purrr)
library(dplyr)

AA <- tibble::tribble(
  ~X1,
  "-------------------------------------------------",
  "ABCD 2002201234 09-06-2015 10:34",
  "-------------------------------------------------",
  "Lorem ipsum",
  "Lorem ipsum",
  "Lorem ipsum Lorem ipsum",
  "Lorem ipsum: Lorem ipsum",
  "123456",
  "AB",
  "AB",
  "Lorem ipsum",
  "-------------------------------------------------",
  "ABCDEF 1001101234 05-03-2011 09:15",
  "-------------------------------------------------",
  "TEST",
  "TEST"
)

line_positions <- which(str_detect(AA$X1, "-------------------------------------------------"))
id_positions <- line_positions[seq(from = 1, to = length(line_positions), by = 2)] + 1
text_start_positions <- line_positions[seq(from = 2, to = length(line_positions), by = 2)] + 1
text_stop_positions <- c(line_positions[seq(from = 3, to = length(line_positions), by = 2)] - 1, nrow(AA))

clean_AA <- pmap_dfr(list(id_positions, text_start_positions, text_stop_positions),
                     function(id, start, stop) {
  entry_info <- AA %>%
    slice(id) %>% 
    pull(X1) %>% 
    str_split(., pattern = " ")
  text_info <- AA %>% 
    slice(seq(from = start, to = stop)) %>% 
    pull(X1)
  
  data.frame(
    ID = entry_info[[1]][2],
    DATE = as.Date(entry_info[[1]][3], format = "%d-%m-%Y"),
    TEXT = paste0(text_info, collapse = "\n")
  )
})

clean_AA
#>           ID       DATE
#> 1 2002201234 2015-06-09
#> 2 1001101234 2011-03-05
#>                                                                                                       TEXT
#> 1 Lorem ipsum\nLorem ipsum\nLorem ipsum Lorem ipsum\nLorem ipsum: Lorem ipsum\n123456\nAB\nAB\nLorem ipsum
#> 2                                                                                               TEST\nTEST

^{Created on 2023-02-06 by the reprex package (v1.0.0)}

A solution using basic tidyverse packages. Look to the comments in the code for detailed explanations of the steps.

library(tidyverse)
library(lubridate)

separator <- "-------------------------------------------------"

tibble(
  tx = c(names(AA), AA[[1]]) # take first line from name to data vector, this should be done during import
  ) |> 
  mutate(
    grp = (tx == separator) %>% # detect separator lines
      {. & lead(., 2)} |> # group begins with with a separator line followed by another after 2 lines
      cumsum()
    ) |> 
  filter(tx != separator) |> # remove separator lines
  nest(text = tx) |> # nest to make document the unit of observation
  mutate(
    fst = map_chr(text, \(x) x |> # extract first line containing meta info
                    pull(1) |>
                    first()),
    id = str_extract(fst, "\\d{10}"), # Regex for 10 digit id string
    date = str_extract(fst, "\\d{2}-\\d{2}-\\d{4}") |> # regex for date
      lubridate::dmy(),
    text = map_chr(text, \(x) x |> # collapse text body to single string
                 slice(-1) |>
                 pull(1) |>
                 str_c(collapse = "\n")),
    .before = text
  ) |> 
  select(-fst) 
#> # A tibble: 2 × 4
#>     grp id         date       text                                              
#>   <int> <chr>      <date>     <chr>                                             
#> 1     1 2002201234 2015-06-09 "Lorem ipsum\nLorem ipsum\nLorem ipsum Lorem ipsu…
#> 2     2 1001101234 2011-03-05 "TEST\nTEST"

I would use some simple sequential steps within the tidyverse. I would mainly use dplyr, tidyr and stringr.

library(dplyr)
library(tidyr)
library(stringr)
AA %>%
rename_with(~ "text") %>% 
filter(!str_detect(text, "-{3,}")) %>%     #remove "-----" lines
mutate(index = cumsum(str_detect(text, ".*\\d{10}.*"))) %>%    #create id index column 
group_by(index) %>%
mutate(temp = first(text)) %>%     #separate id+date info into temporary column
extract(col = temp,
into = c("ID", "date"),
regex = ".*(\\d{10}).*(\\d{2}-\\d{2}-\\d{4}).*",
remove = TRUE) %>%     #create "ID" and "date" columns from temp id
mutate(date = lubridate::dmy(date)) %>% #convert dates into proper date class
slice(-1) %>%     #remove case headers/id rows
nest(text = text) %>%     #one case per line, with a nested text variable
ungroup()
# A tibble: 2 × 4
index ID         date       text            
<int> <chr>      <chr>      <list>          
1     1 2002201234 09-06-2015 <tibble [8 × 1]>
2     2 1001101234 05-03-2011 <tibble [2 × 1]>

This would give us the desired output, with the text column as a list of tibbles with all the text data. It is fairly easy to handle these tibbles after that:

pull(AA,text)
[[1]]
# A tibble: 8 × 1
text                    
<chr>                   
1 Lorem ipsum             
2 Lorem ipsum             
3 Lorem ipsum Lorem ipsum 
4 Lorem ipsum: Lorem ipsum
5 123456                  
6 AB                      
7 AB                      
8 Lorem ipsum             
[[2]]
# A tibble: 2 × 1
text 
<chr>
1 TEST 
2 TEST 

OR

mutate(AA, text = map(text, pull))
# A tibble: 2 × 4
index ID         date       text     
<int> <chr>      <chr>      <list>   
1     1 2002201234 09-06-2015 <chr [8]>
2     2 1001101234 05-03-2011 <chr [2]>