R RegEx gsub() Equivalent of "Line Operations>Remove Empty Lines (Containing Blank Characters)" in CSV file

Question

I have a CSV fwith several columns: Tweet, date, etc. The spaces in some Tweets is causing blank lines and undesired truncated lines.

What works: 1. Using Notepad++'s function "Line Operations>Remove Empty Lines (Containing Blank Characters)" 2. Search and replace: \r with nothing.

However, I need to do this for a large number of files, and I can't manage to find a Regular Expression with gsub() in R that will do what the Notepadd++ function does.

Note that replacing ^[ \t]*$\r?\n with nothing and then \r with nothing does work in Notepad++, but not in R, as suggested here, but it does not work with g(sub) in R.

I have tried the following code:

tx <- readLines("tweets.csv") subbed <-gsub(pattern = "^[ \\t]*$\\r?\\n", replace = "", x = tx) subbed <-gsub(pattern = "\r", replace = "", x = subbed) writeLines(subbed, "output.csv")

This is the input:

This is the desired output:

Or, `grep("^\\w{3} \\w{3} \\d{2} \\d{2}:\\d{2}:\\d{2}", tx, value=TRUE)` — Wiktor Stribiżew, Mar 06 '20 at 14:13
Thanks @WiktorStribiżew the first one just deletes everything; the second one does work, but a lot of lines are truncated and the first row (col names) is deleted. — linguist_at_large, Mar 06 '20 at 14:34
What are your column names? Also, `grep` cannot truncate the lines, it either returns a matched line or not. — Wiktor Stribiżew, Mar 06 '20 at 15:17
I think you are just printing long lines in the console and they are only truncated there for better viewing, they are not really truncated. — Wiktor Stribiżew, Mar 06 '20 at 15:25
No, I mean that they are actually truncated in the output file, not in the console. Headers are: `"created_at";"id_str";"text";"in_reply_to_status_id";"username1";"userscreen_name1";"userlocation1";"userurl1";"userdescription1";"mediaurl"` — linguist_at_large, Mar 06 '20 at 15:33
I have tested and they are not truncated in any way. If yours are, please share the sample of the real file. Images never help when you deal with regex/patterns. — Wiktor Stribiżew, Mar 06 '20 at 15:34

Wiktor Stribiżew · Answer 1 · 2020-03-06T16:23:17.883

0

You may use

library(readtext)
tx <- readtext("weets.csv")
subbed <- gsub("(?m)^\\h*\\R?", "", tx$text, perl=TRUE)
subbed <- gsub("\r", "", subbed, fixed=TRUE)
writeLines(trimws(subbed), "output.csv")

The readtext llibrary reads the file into a single variable and thus all line break chars are kept.

edited Mar 06 '20 at 16:23

answered Mar 06 '20 at 15:23

Wiktor Stribiżew

607,720
39
448
563

Thanks. The headers are there now, but there are still incomplete lines. I illustrate with some screen caps: Original [link] (https://drive.google.com/file/d/1xFSWDnl1A8EMWf4jK2PIAVtRSAF2aY5c/view?usp=sharing) Manual editing output: [link] (https://drive.google.com/file/d/1_dW8IvHdB9jvrPHnrS_kYJvGAOX9IYmM/view?usp=sharing) Output with this code: (https://drive.google.com/file/d/1EUTK7DSbgNXE72MVkbkwzWnogQDu2qYB/view?usp=sharing) E.g. lines 24, 35 are incomplete. – linguist_at_large Mar 06 '20 at 15:46
@linguist_at_large Ok, so you mean you need to read the file in a single variable, remove all blank lines and then remove CR symbols? – Wiktor Stribiżew Mar 06 '20 at 15:52
@linguist_at_large What are the linebreaks in the file? CRLF between lines and CR inside lines? What is the encoding? – Wiktor Stribiżew Mar 06 '20 at 15:55
I would just like to find the right regex so the output looks like the one I got by editing it manually with the method described above with Notepad++ (Remove Empty Lines (Containing Blank Characters) and then delete CRs" – linguist_at_large Mar 06 '20 at 15:56
@linguist_at_large It is not a matter of a regex because `readlines` read in *lines* and you need to read in the whole file into a single variable. – Wiktor Stribiżew Mar 06 '20 at 16:08
@linguist_at_large If it still does not work, you must share the file / part of the file you are working with. No images. – Wiktor Stribiżew Mar 06 '20 at 22:56

R RegEx gsub() Equivalent of "Line Operations>Remove Empty Lines (Containing Blank Characters)" in CSV file

1 Answers1