Import csv without thousand delimiter and convert from factor to numeric without loss of decimal separator

Question

I have a List data.list with 5 columns, which looks like this:

Code  Price_old MB        Price_new   Product
CZ    898.00    20.00     1.001.00    Type 1
CZ    890.00    300.00    1.016.33    Type 2           
CZ    890.00    1.000.00  1.016.63    Type 2 
CZ    899.00    200.00    1.019.33    Type 2
NO    999.00    50.00     1.025.75    Type 3
NO    999.00    600.00    1.025.75    Type 3

This is directly imported from a .csv. What I want to know is a way to convert columns 2, 3 and 4 from factor to numeric (as.numeric(levels(f))[f] did not work!) (1 and 5 are character) without losing any information.

Conversion with mutate_if(is.factor, as.numeric) ended up losing all decimal points: 1.025.75 -> 102575, 50.00 -> 5000, etc.

Conversion with sapply

indx <- sapply(data.list, is.factor)
data.list[indx] <- sapply(data.list[indx], 
                          function(x) as.numeric(as.character(x)))

produced roughly 200 NAs by coercion in each column of my full dataset, data I can not do without.

Second, I want to find a solution to convert all numeric values to this format: "####.##".

I searched in many related blogs and posts, but did not find a proper solution to my problem. Hope someone has an ace up the sleeve.

Cheers

How to the numbers look like in the csv if you open them in a text editor? I can't imagine that it would be 1.000.00 — phiver
Which OS and would you be open to pre-processing the file with awk and friends? — vaettchen
@phiver, they look ordinary, as i would like to have them (1025.75). @vaettchen, Im using R on Windows 10. I am not familiar with awk — Luke666
In that case, check if your read.csv is doing what it should do. Try the readr package or maybe fread from data.table or the solution from Benjamin. — phiver

Benjamin Benjamin · Accepted Answer · 2018-01-22T13:41:37

Using the answer from https://stackoverflow.com/a/38626760/1017276

Essentially, you want to remove all but the last period.

csvfile <- 
"Code,Price_old,MB,Price_new,Product
CZ,898.00,20.00,1.001.00,Type 1
CZ,890.00,300.00,1.016.33,Type 2
CZ,890.00,1.000.00,1.016.63,Type 2
CZ,899.00,200.00,1.019.33,Type 2
NO,999.00,50.00,1.025.75,Type 3
NO,999.00,600.00,1.025.75,Type 3"

csvfile <- textConnection(csvfile)

df <- read.csv(csvfile, stringsAsFactors = FALSE)

df[2:4] <- lapply(df[2:4],
                  function(x) as.numeric(gsub("\\.(?=[^.]*\\.)", "", x, perl = TRUE)))

df

Import csv without thousand delimiter and convert from factor to numeric without loss of decimal separator

1 Answers