I am trying to import a text file that has html code in it. I'm trying to import with read.table and is delimited with a squiggle (~).

The text file looks like this:

id~title~content
Article-123~Title 1~<h2>Overview of Article 1</h2>

<p>This is the content of article 123.</p>
Article-456~Title 2~<h1>Problem:</h1><br>
<br>
This is the content of article 456
Article-789~Title 3~<h1>This is the content of article 789 </h1>

The code I am using gets me close:

text <- read.table("filepath/text_file.txt",
                    quote = "\"",
                    sep = "~",
                    fill = TRUE,
                    header = TRUE,
                    comment.char = "",
                    stringsAsFactors = TRUE,
                    na.strings = "\\n",
                    allowEscapes = FALSE)

I get:

id              title       content
Article-123     Title 1     <h2>Overview of Article 1</h2>
Article-456     Title 2     <h1>Problem:</h1><br>
<br>
Article-567     Title 3     <h1>This is the content of article 789 </h1>

It appears the html is adding a line break when I import into R. Instead I would like the import to look like this:

id              title       content
Article-123     Title 1     <h2>Overview of Article 1</h2>
Article-456     Title 2     <h1>Problem:</h1><br>
Article-567     Title 3     <h1>This is the content of article 789 </h1>

2 Answers

0
Calum You On Best Solutions

You can see if this works? I am not sure of ways to get read.table to account for some newlines and not others (how do you know if a newline means a new row or not?) Instead, we can try the following approach:

  1. Read in the data as lines (so each line of the text is an element of a character vector)
  2. Figure out which lines belong to each row by looking for ~ characters, and then collapse those rows, replacing the newlines. May be fragile if the HTML contains ~ anywhere.
  3. Use separate to split the newly tidied lines into the three columns.
library(tidyverse)
text <- "id~title~content
Article-123~Title 1~<h2>Overview of Article 1</h2>

<p>This is the content of article 123.</p>
Article-456~Title 2~<h1>Problem:</h1><br>
<br>
This is the content of article 456
Article-789~Title 3~<h1>This is the content of article 789 </h1>"

text_in <- read_lines(text) %>%
  tibble(line = .) %>%
  mutate(row = str_detect(line, "~") %>% cumsum) %>%
  group_by(row) %>%
  summarise(line = str_c(line, collapse = "\n")) %>%
  separate(line, into = c("id", "title", "content"), sep = "~") %>%
  slice(-1)

text_in
#> # A tibble: 3 x 4
#>     row id        title   content                                          
#>   <int> <chr>     <chr>   <chr>                                            
#> 1     2 Article-… Title 1 "<h2>Overview of Article 1</h2>\n\n<p>This is th…
#> 2     3 Article-… Title 2 "<h1>Problem:</h1><br>\n<br>\nThis is the conten…
#> 3     4 Article-… Title 3 <h1>This is the content of article 789 </h1>

Created on 2019-04-17 by the reprex package (v0.2.1)

0
Lee Mtoti On

You can try this if you are working with data.tables. My approach has the following assumptions:

  1. If columns("title" or "content") have NA that row is either <br>, comment or <p>
  2. That the text file will have more of those lines within it

Given those assumptions if you use library(readr), it will create a tibble table that you can set back to data.table at the same time removing any rows with NA.

This is the code:

text <- "id~title~content
Article-123~Title 1~<h2>Overview of Article 1</h2>

<p>This is the content of article 123.</p>
Article-456~Title 2~<h1>Problem:</h1><br>
<br>
This is the content of article 456
Article-789~Title 3~<h1>This is the content of article 789 </h1>"

library(readr)
library(data.table)
test <- na.omit(setDT(read_delim(text, delim = "~")))

test


            id   title                                      content
1: Article-123 Title 1               <h2>Overview of Article 1</h2>
2: Article-456 Title 2                        <h1>Problem:</h1><br>
3: Article-789 Title 3 <h1>This is the content of article 789 </h1>

I added this because I like using data.tables so using fread you can also do the following:

library(data.table)
test <- na.omit(fread(text,header = TRUE, sep = "~", 
                      na.strings = "", fill = TRUE, 
                      blank.lines.skip = TRUE))


test
            id   title                                      content
1: Article-123 Title 1               <h2>Overview of Article 1</h2>
2: Article-456 Title 2                        <h1>Problem:</h1><br>
3: Article-789 Title 3 <h1>This is the content of article 789 </h1>