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

user_144157 0
Viewed 147353

I have a csv file where some of the numerical values are expressed as strings with commas as thousand separator, e.g. "1,513" instead of 1513. What is the simplest way to read the data into R?

I can use read.csv(..., colClasses="character"), but then I have to strip out the commas from the relevant elements before converting those columns to numeric, and I can't find a neat way to do that.

r-faq csv r
11 Answers

We can also use readr::parse_number, the columns must be characters though. If we want to apply it for multiple columns we can loop through columns using lapply

df[2:3] <- lapply(df[2:3], readr::parse_number)
df

#  a        b        c
#1 a    12234       12
#2 b      123  1234123
#3 c     1234     1234
#4 d 13456234    15342
#5 e    12312 12334512

Or use mutate_at from dplyr to apply it to specific variables. 

library(dplyr)
df %>% mutate_at(2:3, readr::parse_number)
#Or
df %>% mutate_at(vars(b:c), readr::parse_number)

data

df <- data.frame(a = letters[1:5], 
                 b = c("12,234", "123", "1,234", "13,456,234", "123,12"),
                 c = c("12", "1,234,123","1234", "15,342", "123,345,12"), 
                 stringsAsFactors = FALSE)
user_39629140

Using read_delim function, which is part of readr library, you can specify additional parameter:

locale = locale(decimal_mark = ",")

read_delim("filetoread.csv", ";", locale = locale(decimal_mark = ","))

*Semicolon in second line means that read_delim will read csv semicolon separated values.

This will help to read all numbers with a comma as proper numbers.

Regards

Mateusz Kania

user_100156370
Related