CODEWITHSUNDEEP
rparsingmultilinereadrdelimited
MrPmosh
MrPmosh

Reputation: 98

Data Wrangling in R: Malformed input delimited file with records spreading to multiple lines [readr]

I have a delimited file that has errors when it was dumped to a flat file. Multiple records where wrapped and the overflow was saved as a new line.

Visuals for better understanding what I'm trying to explain: 

    "|A|B|C|D|E|F|...|X|Y|"
[1] "|1|2|3|2|1|1|...|4|1|"
[2] "|2|3|4|4|5|1|...|0|0|"
[3] "|8|7|6|7|...
[4]  |7|9|1|....
[5]  |2|3|7|"

Notice what should've been TableA[3] is now split into rows 3 through 5 and only rows 3 and 5 are quoted.

I've used read_delim from the package readr with the following parameters

##Attempt 1
read_delim("data/TableA.txt",delim = "|",col_names = T)
## Strangely resulting in an nx1 table, without any parsing done.

##Attempt 2
read_delim("data/TableA.txt",delim = "|",col_names = T,quote="")
## Results in a parsed nx25 tibble like this:

\"A  B  C  D  E  F  ...  X  Y\"
\"1  2  3  2  1  1  ...  4  1\"
\"2  3  4  4  5  1  ...  0  0\"
\"8  7  6  7  
  7  9  1
  2  3  7\"                                "

problems(TableA) ##outputs:
# A tibble: 6 x 5
    row col   expected   actual     file              
  <int> <chr> <chr>      <chr>      <chr>             
1    26 NA    26 columns 8 columns  'data/TableA.txt'
2    27 NA    26 columns 17 columns 'data/TableA.txt'
3    28 NA    26 columns 3 columns  'data/TableA.txt'
4   160 NA    26 columns 8 columns  'data/TableA.txt'
5   161 NA    26 columns 17 columns 'data/TableA.txt'
6   162 NA    26 columns 3 columns  'data/TableA.txt'

Note the q-marks at the start and end of the first and last variable names respectively and the same for its values. When i tried quote='"' or quote="\"" got back the same results as Attempt 1.

I would like to know if there's a way to solve this by parametrizing readr::read_delim correctly

EDIT: This is the output for dput(readLines("data/TableA.txt")) I "hashed" some of the data for privacy reasons.

Element 1 and 5 are correct in form. 2 -> 4 present the problem as described.

c("\"8x9|x|x|x|x|x|x|47|SDPA|Colmados|COMERCIAL||||||Unknown|Unknown|Unknown|HAINA|SANTO DOMINGO|||47|809|x\"", 
"\"8x9|x|x|x|SECUNDARIO|x|x|15", 
"|SDPA|x|||x||0x2xx8|xxx6|Unknown|Unknown|Unknown|xS|SxxGO|||15", 
"|8x9|xx4\"", "\"809|3xx00|xx|Sxx|PRINCIPAL|DISTRITO NACIONAL|xxxx|86|SDPA|Bexxs|COMERCIAL||vexsxxcom|www.axx.do|00x54|1xx-7|$0 - $5M|0 - 25|$0 - $500K|LOxS|Santo Domingo|||86|8xx9|33xx0\""
)

Upvotes: 0

Views: 138

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 269644

Questions to SO should include a complete minimal reproducible example but as there was none we have included one in the Note at the end with the assumption that every record is either 6 fields on one line or 6 fields split into three lines of 3, 2 and 1 field in that order.

Count the fields on each line and coalesce multi-line fields. Finally re-read.

library(readr)

cnt <- count_fields("nzam.dat", tokenizer_delim("|"))
L <- read_lines("nzam.dat")
L2 <- tapply(L, cumsum(cnt == 6 | cnt == 3), paste, collapse = "|")
read_delim(L2, delim = "|", col_names = FALSE, col_types = NULL, trim_ws = TRUE)

giving:

# A tibble: 3 x 6
     X1    X2    X3    X4    X5    X6
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1     1     2     3     4     5     6
2    10    11    12    17    18    21
3     1     2     3     4     5     6

Another approach using only base R is the following. Note that what = 0 specifies that all entries are numeric.

 s <- scan("nzam.dat", what = 0, sep = "|", quiet = TRUE)
 as.data.frame(matrix(s, ncol = 6, byrow = TRUE))

Note

Lines <- "1|2|3|4|5|6
10|11|12
17|18
21
1|2|3|4|5|6"
cat(Lines, file = "nzam.dat")

Upvotes: 2

Related Questions