Reading file containing numerical values and unknown errors (random strings) in R

Question

Some .csv files with numerical data I work with contain errors, each error is marked as random string, for example after reading in, data frame could look like that :

set.seed(123)
rand.str <-  paste0(letters[sample(10)], collapse="")
wrong.output <- data.frame(a=1:5, b=c(4:5, rand.str, 7:8), stringsAsFactors=FALSE)

in this case proper output is :

proper.output <- data.frame(a=1:5, b=c(4:5, NA, 7:8))

after reading with read.csv each column with at least one character value is treated as character column.

Can I mark errors (random strings) as NAs while reading-in file? If not, what is the most convenient, proper or fastest method for subsetting them with NA's ?

There is na.strings argument in read.csv, but it is the solution only in simpler cases where it can be used like: na.strings=c("-", "unavailable")

(can't see any duplicate, so I guess there is simple, workaround)

colClasses suggestion does not work

read.csv("test.txt", sep=",", colClasses = c("numeric", "numeric"))

Error in scan(file = file, what = what, sep = sep, quote = quote, dec = dec, : scan() expected 'a real', got 'chdgfajibe' In addition: Warning message: In read.table(file = file, header = header, sep = sep, quote = quote, : incomplete final line found by readTableHeader on 'test.txt'

have you tried setting colClasses=c("numeric") within read.csv ? — Aramis7d
– Aramis7d, Commented Feb 1, 2017 at 13:16

Community · Accepted Answer · 2017-05-23 10:29:27Z

1

I adapted this solution from a different solution for csv reading which is 7 years back. I thought it is a cleaner solution. It gives your desired output.

setClass("Alpha")
# replacing words with empty characters
setAs("character", "Alpha", 
      function(from) as.numeric(gsub('[[:alpha:]]+', '', from) ) )
read.csv('data.csv', colClasses = c('numeric','Alpha'))

output

Source: How to read data when some numbers contain commas as thousand separator?

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Feb 1, 2017 at 15:34

user5249203

4,6781 gold badge22 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Qbik · Accepted Answer · 2017-02-01 13:10:34Z

0

solution is :

wrong.output[] <- lapply(wrong.output, as.numeric)

answered Feb 1, 2017 at 13:10

Qbik

6,20517 gold badges65 silver badges101 bronze badges

Collectives™ on Stack Overflow

Reading file containing numerical values and unknown errors (random strings) in R

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related