Unstructured textual Data to Data Frame

Question

I am trying to convert several rows of this textual data into a data frame using R. I am unable to use read.delim effectively. I want all these rows to be populated into 10 fixed columns delimited by : Thank You.

*** 
Type:status
Origin: abc
Text: abc
URL: 
ID: 123
Time: Fri Jul 22 15:07:37 CDT 2011
RetCount: 0
Favorite: false
MentionedEntities: 
Hashtags: 
***
***
Type:status
Origin: cde
Text: rty
URL: http://ocs
ID: 456
Time: Thu Jul 21 14:09:47 CDT 2011
RetCount: 0
Favorite: false
MentionedEntities: 
Hashtags:  rty
***
***
.
..
...

Answer 1

Something like this could work:

a <- readLines(textConnection("
*** 
Type:status
Origin: abc
Text: abc
URL: 
ID: 123
Time: Fri Jul 22 15:07:37 CDT 2011
RetCount: 0
Favorite: false
MentionedEntities: 
Hashtags: 
***
***
Type:status
Origin: cde
Text: rty
URL: http://ocs
ID: 456
Time: Thu Jul 21 14:09:47 CDT 2011
RetCount: 0
Favorite: false
MentionedEntities: 
Hashtags:  rty
***
***"))


ids <- c("Type", "Origin", "Text", "URL", "ID", "Time", "RetCount", "Favorite", "MentionedEntities", "Hashtags")

sapply(ids, function(id) sapply(strsplit(a[grepl(id[1], a)], ":"), "[[", 2))

Answer 2

Use this answer to read the file as one string: https://stackoverflow.com/a/9069670/1412059

Now process the string like this:

text <- "*** 
Type:status
Origin: abc
Text: abc
URL: 
ID: 123
Time: Fri Jul 22 15:07:37 CDT 2011
RetCount: 0
Favorite: false
MentionedEntities: 
Hashtags: 
***
***
Type:status
Origin: cde
Text: rty
URL: http://ocs
ID: 456
Time: Thu Jul 21 14:09:47 CDT 2011
RetCount: 0
Favorite: false
MentionedEntities: 
Hashtags:  rty
***
***"

#replace : with ; to have a nice seperator    
text <- gsub("(?<![[:digit:]p]):", ";", text, perl=TRUE)

dat <- read.table(text=text, sep=";", comment.char = "*", fill=TRUE)
dat$id <- rep(seq_len(nrow(dat)/10), each=10)
library(reshape2)
dcast(dat, id~V1, value.var="V2")
#  id Favorite Hashtags   ID MentionedEntities Origin RetCount Text                          Time   Type         URL
#1  1    false           123                      abc        0  abc  Fri Jul 22 15:07:37 CDT 2011 status            
#2  2    false      rty  456                      cde        0  rty  Thu Jul 21 14:09:47 CDT 2011 status  http://ocs

Answer 3

You could also do: (Using a from @rengis)

In case you have http and https as URL

text1 <- gsub("(?<=[0-9]|http|https):(*SKIP)(*F)|:", ";", a, perl=TRUE)
text2 <- text1[!grepl("\\*|^$", text1)]
res <- do.call(data.frame,c(split(gsub(".*; ?", "", text2),
              gsub(";.*", "", text2)), stringsAsFactors=FALSE))


res
#   Favorite Hashtags  ID MentionedEntities Origin RetCount Text
#1    false          123                      abc        0  abc
#2    false      rty 456                      cde        0  rty
#                         Time   Type        URL
#1 Fri Jul 22 15:07:37 CDT 2011 status           
#2 Thu Jul 21 14:09:47 CDT 2011 status http://ocs

Or use cSplit

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

 DT <- cSplit(as.data.frame(text2), "text2",";", "wide")[,
                                n:= seq_len(.N), by=text2_1]

 dcast.data.table(DT, n~text2_1, value.var="text2_2")
 #    n Favorite Hashtags   ID MentionedEntities Origin RetCount Text
 # 1: 1    false           123                      abc        0  abc
 # 2: 2    false      rty  456                      cde        0  rty
                             Time   Type         URL
 #1:  Fri Jul 22 15:07:37 CDT 2011 status            
 #2:  Thu Jul 21 14:09:47 CDT 2011 status  http://ocs

Update

Based on the new info, i.e. colons popping up:

 a <- readLines(textConnection("
*** 
Type:status
Origin: abc
Text: abc
URL: 
ID: 123
Time: Fri Jul 22 15:07:37 CDT 2011
RetCount: 0
Favorite: false
MentionedEntities: 
Hashtags: 
***
***
Type:status
Origin: cde: andgg
Text: rty: asndf
URL: http://ocs
ID: 456
Time: Thu Jul 21 14:09:47 CDT 2011
RetCount: 0
Favorite: false
MentionedEntities: 
Hashtags:  rty
***
***"))

text1 <- gsub("(?<=[0-9]|http|https):(*SKIP)(*F)|^([^:]+):(.*)",
                                             "\\1;\\2", a, perl=TRUE)
text2 <- text1[!grepl("\\*|^$", text1)]
splitGroup <- sub(";.*", "", text2)

res <- do.call(data.frame,c(split(gsub(".*; ?", "", text2),
             factor(splitGroup, levels=unique(splitGroup))), stringsAsFactors=FALSE))
res
#    Type     Origin       Text        URL  ID                         Time
#1 status        abc        abc            123 Fri Jul 22 15:07:37 CDT 2011
#2 status cde: andgg rty: asndf http://ocs 456 Thu Jul 21 14:09:47 CDT 2011
#   RetCount Favorite MentionedEntities Hashtags
#1        0    false                           
#2        0    false                        rty

Answer 4

Here is a function that seems to get the job done. It doesn't use a delimiter, but uses readLines and a couple of regular expressions.

readData <- function(file, stringsAsFactors = TRUE) 
{
    rl <- readLines(file)                        ## read the file
    rl2 <- rl[!grepl("[*]+", rl)]                ## remove the '***' elements
    sub <- sub("^[A-Za-z]+[:]( ?)+", "", rl2)    ## make the row data
    mat <- matrix(sub, ncol = 10, byrow = TRUE,  ## create a matrix
        dimnames = list(NULL, gsub("[:](.*)", "", rl2[1:10])))  
    as.data.frame(mat, stringsAsFactors = stringsAsFactors)
}

And here is a run with your data, where the file "new.txt" was created with your example data.

readData("new.txt")
#     Type Origin Text        URL  ID                         Time RetCount Favorite MentionedEntities Hashtags
# 1 status    abc  abc            123 Fri Jul 22 15:07:37 CDT 2011        0    false                           
# 2 status    cde  rty http://ocs 456 Thu Jul 21 14:09:47 CDT 2011        0    false                        rty