Cfrgtkky

I am reading text from a file in which the text is separated by | (pipes).

The text table looks like this (tweet id|date and time|tweet):

545253503963516928|Wed Dec 17 16:25:40 +0000 2014|Massachusetts Pharmacy Owners Arrested in Meningitis Deaths http://xxxxxxxxx

545235402156937217|Wed Dec 17 15:13:44 +0000 2014|For First Time, Treatment Helps Patients With Worst Kind of Stroke, Study Says http://xxxxxxxxx

I am reading this information using the following code:

nyt  <- read.table(file=".../nytimeshealth.txt", 

                                      sep="|", 

                                      header = F, 

                                      quote="", 

                                      fill=T, 

                                      stringsAsFactors = F,

                                      numerals ="no.loss",

                                      encoding = "UTF-8",

                                      na.strings = "NA")

Now, while most of the rows in the original file have 3 columns, each separated by a '|', a few of the rows have an additional '|' separator. That is to say, they have four columns, because some of the tweets themselves contain a | symbol.

545074589374881792|Wed Dec 17 04:34:43 +0000 2014|National Briefing | New England: Massachusetts: Sex-Change Surgery Denied to Inmate http://xxxxxxxxx

I know that usingfill=T option in the read.table function above allows me to read rows of unequal length (blank fields are implicitly added in the empty cells).

So, the row above becomes

71 545074589374881792 Wed Dec 17 04:34:43 +0000 2014 National Briefing

72 New England: Massachusetts: Sex-Change Surgery Denied to Inmate http://xxxxxxxxx

However, now column 3 of row 71 has incomplete information, and columns 2 and 3 of row 72 are empty while column 1 does not contain the tweet ID but a part of the tweet. Is there any way I can avoid this? I would like to remove the extra | separator wherever it appears, so that I do not lose any information.

Is this possible while reading the text file into R? Or is it something I will have to take care of before I start loading the text. What would be my best course of action?

I created a text file called text.txt with the 3 lines you provide as example of your data (the 2 easy lines without any | in the tweet as well as the one which has a | inside the tweet).

Here is the content of this file:

545253503963516928|Wed Dec 17 16:25:40 +0000 2014|Massachusetts Pharmacy Owners Arrested in Meningitis Deaths http://xxxxxxxxx

545235402156937217|Wed Dec 17 15:13:44 +0000 2014|For First Time, Treatment Helps Patients With Worst Kind of Stroke, Study Says http://xxxxxxxxx

545074589374881792|Wed Dec 17 04:34:43 +0000 2014|National Briefing | New England: Massachusetts: Sex-Change Surgery Denied to Inmate http://xxxxxxxxx

Code

library(tidyverse)



readLines("text.txt", encoding = "UTF-8") %>%

  map(., str_split_fixed, "\|", 3) %>%

  map_df(., as_tibble)

Result

# A tibble: 3 x 3

  V1                 V2                            

  <chr>              <chr>                         

1 545253503963516928 Wed Dec 17 16:25:40 +0000 2014

2 545235402156937217 Wed Dec 17 15:13:44 +0000 2014

3 545074589374881792 Wed Dec 17 04:34:43 +0000 2014

  V3                                                                            

  <chr>                                                                         

1 Massachusetts Pharmacy Owners Arrested in Meningitis Deaths http://xxxxxxxxx  

2 For First Time, Treatment Helps Patients With Worst Kind of Stroke, Study Say…

3 National Briefing | New England: Massachusetts: Sex-Change Surgery Denied to …

Here is another solution, to get back to your comment and to use your initial code. But this solution will only work if you have one | per tweet (you can have tweets with none as long as at least one tweet has one |). If you don't have any | in your tweets, or if some tweets have more than one |, it will break and you will have to edit it. So the other answer, which will work regardless of the structure of your tweets is better IMO.

I am still using my text.txt file:

df <- read.table(file = "text.txt", 

                 sep = "|", 

                 header = F, 

                 quote = "", 

                 fill = T, 

                 stringsAsFactors = F,

                 numerals = "no.loss",

                 encoding = "UTF-8",

                 na.strings = "NA")



df %>%

  mutate(V3 = paste0(V3, V4)) %>%

  select(- V4)

Result

                  V1                             V2

1 545253503963516928 Wed Dec 17 16:25:40 +0000 2014

2 545235402156937217 Wed Dec 17 15:13:44 +0000 2014

3 545074589374881792 Wed Dec 17 04:34:43 +0000 2014

                                                                                                    V3

1 Massachusetts Pharmacy Owners Arrested in Meningitis Deaths http://xxxxxxxxx 

2 For First Time, Treatment Helps Patients With Worst Kind of Stroke, Study Says http://xxxxxxxxx 

3 National Briefing  New England: Massachusetts: Sex-Change Surgery Denied to Inmate http://xxxxxxxxx