Convert monetary values "R$" to double type

Asked

Viewed 373 times

0

Hello, I have several CSV files, in which were stored in some columns monetary values in real ex: "R$ 1.986,00". However, I intend to pass all CSV to Sqlite and then analyze the data in Power BI. Therefore, instead of exporting the CSV data to Sqlite in character type, I would like to export them as double type (I think this would be the appropriate option, I believe), in order to facilitate the data processing process in Power BI.

In this context, I would like to know if there is any function or package in the R that is possible to transform data from a data.frame (imported from a CSV) that are in real money format ("R$1,986.69") to double type ("1986.69")?

Follow the example of columns that are stored in the CSV. In the specific case, will be about 27 thousand CSV files, with an estimated 20 million lines that will be consolidated in Sqlite.

inserir a descrição da imagem aqui

Thanks for your support.

After the help of @Rui Barradas, I made a complement in the regular expression to pick up several situations. It was like this, as an example:

inserir a descrição da imagem aqui

r double

1 answer

3


Try to apply the function converteeach of the columns with the problematic values.

converte <- function(x) as.numeric(sub(",", "\\.", gsub("R\\$|\\.", "", x)))

x <- "R$ 5.500,00"
converte(x)
#[1] 5500

How the functions sub and gsub vector, converte also is and processes entire columns.

  • In this case, I try to base the image I posted, I would only need to write the following steps so that the data of the dataframe would be changed?: csv$salario_base <- sub(",", "\\.", gsub("R\\$|\\.", "", csv$salario_base)) csv$salaraio_vantagens <- sub(",", "\\.", gsub("R\\$|\\.", "", csv$salaraio_vantagens)) csv$salario_gratificacao <- sub(",", "\\.", gsub("R\\$|\\.", "", csv$salario_gratificacao))

  • Thanks a lot, @Rui Barradas. Helped a lot.

  • @Georgesantiago In response to the first comment, yes it would be enough to write this but the function converte is more convenient, avoid writing the same code several times. As for the second comment, you are welcome, that’s why we are here.

Browser other questions tagged r double

You are not signed in. Login or sign up in order to post.