Turn txt file into dataframe

user_13174189 0
Viewed 73

I have a txt file with this data in it:

1 message («random_choice»)[5];
2 reply («принято»)[2][3];
3 regulate («random_choice»)[5];
4 Early reg («for instance»)[2][3][4];
4xx: Success (загрузка):
6 OK («fine»)[2][3];

I want to turn it into dataframe, consisting of three columns ID, message, comment. I also want to remove unnecessary numbers at the end in square brackets. And also some values in ID column have strings (usually xx). In these cases, column must be just empty. So, desired result must look like this:

ID      Message        Comment
1       message     random_choice
2       reply       принято
3       regulate    random_choice
4       Early reg   for instance
        Success     загрузка
6       OK          fine

How could i do that? Even when i try to read this txt file i get strange error:

df <- read.table("data_received.txt", header = TRUE)

error i get:

Error in read.table("data_received.txt", header = TRUE) : 
  more columns than column names
dataframe r regex
2 Answers

You can use strcapture for this.

Fake data, you'll likely do txt <- readLines("data_received.txt"). (Since my locale on windows is not being friendly to those strings, I'll replace with straight ascii, assuming it'll work just fine on your system.)

txt <- readLines(textConnection("1 message («random_choice»)[5];
# 2 reply («asdf»)[2][3];
# 3 regulate («random_choice»)[5];
# 4 Early reg («for instance»)[2][3][4];
# 4xx: Success (something):
# 6 OK («fine»)[2][3];"))

The breakout:

out <- strcapture("^(\\S+)\\s+([^(]+)\\s+\\((.*)\\).*$", txt, 
                  proto = data.frame(ID=0L, Message="", Comment=""))
# Warning in fun(mat[, i]) : NAs introduced by coercion
out
#   ID   Message         Comment
# 1  1   message «random_choice»
# 2  2     reply          «asdf»
# 3  3  regulate «random_choice»
# 4  4 Early reg  «for instance»
# 5 NA   Success       something
# 6  6        OK          «fine»

The proto= argument indicates what type of columns are generated. Since I set the ID=0L, it assumes it'll be integer, so anything that does not convert to integer becomes NA (which satisfies your fifth row omission).

Explanation on the regex:

  • in general:
    • * means zero-or-more of the previous character (or character class)
    • + means one-or-more
    • ? (not used, but useful nonetheless) means zero or one
    • ^ and $ mean the beginning and end of the string, respectively (a ^ within [..] is different)
    • (...) is a capture group: anything within the non-escaped parens is stored, anything not is discarded
    • [...] is a character group, any of the characters is a match; if this is instead [^..], then it is inverted: anything except what is listed
    • [[...]] is a character class
  • ^(\\S+), start with (^) one or more (+) non-space characters (\\S);
  • \\s+ one or more space character (\\s) (discarded);
  • ([^(]+) one or more character that is not a left-paren;
  • \\((.*)\\)$ a literal left-paren (\\() and then zero or more of anything (.*), all the way to a literal right-paren (\\)) and the end of the string ($).

It should be noted that \\s and \\S are non-POSIX regex characters, where it is generally suggested to use [^[:space:]] for \\S (no space chars) and [[:space:]] for \\s. Those are equivalent but I went with code-golf initially. With this replacement, it looks like

out <- strcapture("^([^[:space:]]+)[[:space:]]+([^(]+)[[:space:]]+\\((.*)\\).*$", txt,
                  proto = data.frame(ID=0L, Message="", Comment=""))
user_33582720

We can use {unglue}. Here we see you have two patterns, one contains "«" and ID, the other doesn't. {unglue} will use the first pattern that matches. any {foo} or {} expression matches the regex ".*?", and a data.frame is built from the names put between brackets.

txt <- c(
  "1 message («random_choice»)[5];", "2 reply («asdf»)[2][3];", 
  "3 regulate («random_choice»)[5];", "4 Early reg («for instance»)[2][3][4];", 
  "4xx: Success (something):", "6 OK («fine»)[2][3];")

library(unglue)
patterns <- 
  c("{id} {Message} («{Comment}»){}",
    "{} {Message} ({Comment}){}")

unglue_data(txt, patterns)
#>     id   Message       Comment
#> 1    1   message random_choice
#> 2    2     reply          asdf
#> 3    3  regulate random_choice
#> 4    4 Early reg  for instance
#> 5 <NA>   Success     something
#> 6    6        OK          fine
user_22704750
Related