r convert number saved as char in tibble to double NA introduced

user_10696147 0
Viewed 331

I'd like to convert the relevant columns in the following tibble to numeric (double precision):

# A tibble: 6 x 6
  Date       Open      High       Low      Close   Shares
  <chr>      <chr>     <chr>     <chr>     <chr>   <chr> 
1 16.04.2021 53,64     54,12     53,64     54,12   50    
2 15.04.2021 53,19     53,19     53,19     53,19   -     
3 14.04.2021 53,29     53,29     53,29     53,29   -     
4 13.04.2021 52,86     52,86     52,86     52,86   -     
5 12.04.2021 53,17     53,17     53,17     53,17   -     
6 09.04.2021 53,18     53,18     53,18     53,18   -

However, if I apply as.numeric to the relevant columns, NA would be introduced.

What is the most efficient way to convert the entries in the relevant columns to double without generating the NAs?

Reproducible sample data:

df <- tribble(
  ~Date,       ~Open,      ~High,       ~Low,      ~Close,   ~Shares,
 "16.04.2021",  "53,64",     "54,12",     "53,64",     "54,12",   50,    
 "15.04.2021",  "53,19",     "53,19",     "53,19",     "53,19",   NA,     
 "14.04.2021",  "53,29",     "53,29",     "53,29",     "53,29",   NA,     
 "13.04.2021",  "52,86",     "52,86",     "52,86",     "52,86",   NA,     
 "12.04.2021",  "53,17",     "53,17",     "53,17",     "53,17",   NA,     
 "09.04.2021",  "53,18",     "53,18",     "53,18",     "53,18",   NA 
)
tibble time-series r
3 Answers

You can replace comma with a dot and convert to numeric. Use lapply to apply the function to multiple columns.

df[2:5] <- lapply(df[2:5], function(x) as.numeric(sub(',', '.', x)))

Using dplyr :

library(dplyr)
library(readr)

df %>%
  mutate(across(Open:Close, ~parse_number(., locale = locale(decimal_mark = ","))))
user_39629140

That reason you can't turn them into numeric values are , as decimal separator instead of .. So you can use the following code:

library(dplyr)
library(stringr)

df %>%
  mutate(across(Open:Close, ~ str_replace(., ",", "\\.")),
         across(Open:Close, as.numeric))

# A tibble: 6 x 6
  Date        Open  High   Low Close Shares
  <chr>      <dbl> <dbl> <dbl> <dbl>  <dbl>
1 16.04.2021  53.6  54.1  53.6  54.1     50
2 15.04.2021  53.2  53.2  53.2  53.2     NA
3 14.04.2021  53.3  53.3  53.3  53.3     NA
4 13.04.2021  52.9  52.9  52.9  52.9     NA
5 12.04.2021  53.2  53.2  53.2  53.2     NA
6 09.04.2021  53.2  53.2  53.2  53.2     NA
user_143145200

First escape the "." in your regular expression.

Second replace the commas with a "." before you can convert to numeric

df  %>% 
  mutate(across(2:5, ~as.numeric(gsub(",", ".", gsub("\\.", "", .)))))

Output:

  Date        Open  High   Low Close Shares
  <chr>      <dbl> <dbl> <dbl> <dbl> <chr> 
1 16.04.2021  53.6  54.1  53.6  54.1 50    
2 15.04.2021  53.2  53.2  53.2  53.2 -     
3 14.04.2021  53.3  53.3  53.3  53.3 -     
4 13.04.2021  52.9  52.9  52.9  52.9 -     
5 12.04.2021  53.2  53.2  53.2  53.2 -     
6 09.04.2021  53.2  53.2  53.2  53.2 -
user_133216470
Related