R: Reading number with comma and negative number with negative sign at the end

Question

I am having problems reading a CSV file that has a number in a strange format. I would like to read the value as a number into R.

I am reading the CSV file using read.csv normally into a DF.

The problem is that one of the columns read the value as a factor variable.

Example: CSV file:

713,78-;713,78;577,41-;577,41;123,82-;123,82

After I read it into a dataframe the result is:

[1] 713,78- 713,78  577,41- 577,41  123,82- 123,82 
6 Levels: 713,78- 713,78  577,41- 577,41  123,82- 123,82

In the case illustrated above, I would like the following output:

[1] -713.78  713.78 -577.41  577.41 -123.82  123.82

Where the column number would be class Numeric.

Actually, those are valid negative decimals…in _Dutch_. I tried setting the locale to `en_NL` but nothing would honor it for reading the #'s. I may do a PR for `readr` — hrbrmstr, Sep 15 '15 at 20:43

score 4 · Answer 1 · answered Sep 15 '15 at 19:53

It should work in general:

fixData <- function(x)
{
  x <- gsub(',', '.', x)
  x[grep('-$', x)] <- paste0('-', x[grep('-$', x)])
  x <- as.numeric(sub('-$', '', x))
  return(x)
}
myData <- read.csv2(file, stringsAsFactors = F)
fixedData <- sapply(myData , fixData )

score 1 · Answer 2 · answered Sep 15 '15 at 19:35

That's an ugly number format.

This should get it to what you want it to be.

x <- factor(c("713,78-", "713,78", "577,41-", "577,41", "123,82-", "123,82"))

scalar <- ifelse(grepl("-", x), -1, 1)
x <- as.character(x)
x <- gsub(",", ".", x)
x <- gsub("-", "", x)
x <- as.numeric(x) * scalar

R: Reading number with comma and negative number with negative sign at the end

2 Answers2