unnecessary decimals appear when import data from sql to rstudio.

[osman3144]

ı have a small table like below

this is my table in sql db. and below table is imported table from sql.
vvvvvvvvvvvvvvvvvvvvvvvvvvvvv

then ı import table into rstudio. ı see data doesnt come to table as is in db. some unwanted decimals appear. how can solve this problem.

import code is below ı dont write conn elements.

#MSSQL QUERY CODE
TABLE <- sqlQuery(conn_TABLE, paste0("SELECT [NAME]
,[NUMBER]
,
,[Y]
,[Z]
,[W]
,[T]
FROM [DB].[dbo].[DATA] ;"))
THEN I SEE TABLE LİKE BELOW İN RSTUDİO

İN SQL ORİGİNAL TABLE SEEN LİKE BELOW

[M_AcostaCH]

Hi @osman3144, you could round the decimal.

df <- data.frame(
  Nombre = c("Juan", "María", "Carlos"),
  Nota = c(8.675, 9.823, 7.194))

# round
df$Nota_round <- round(df$Nota, 2)

#  Nombre  Nota Nota_round
#1   Juan 8.675       8.68
#2  María 9.823       9.82
#3 Carlos 7.194       7.19

[osman3144]

ı wonder why data comes with many decimals. in fact there are no numbers in db like such long decimals.

[FJCC]

The database display is probably rounding the values it shows. Decimal fractions (what is displayed) can't in general be represented exactly in binary (how the computer stores numbers). R is choosing to display more precise values than the database is.

[osman3144]

thanks my friend you are right. to prevent this unwanted decimals what can ı do extra.

[FJCC]

Do you want to limit the number of decimal places that are displayed when printing or in calculations? In calculations, you can use the round() function as suggested by @M_AcostaCH. For printing, you can use the options() function and its digits argument, though that adjusts the significant figures, no the decimal places and it is only a suggestion. For example,

options(digits = 3)

Converting your data frames into tibbles (using the tibble package) will change the default printing, as shown in this examples

head(df)
  ID numeric_col1 numeric_col2 numeric_col3 text_col1 text_col2 text_col3
1  1     2.875775    8.9504536     3.198206         s         g         d
2  2     7.883051    3.7446278     3.077200         x      <NA>      <NA>
3  3     4.089769    6.6511519     2.197676         t         z      <NA>
4  4     8.830174    0.9484066     3.694889         o         j         e
5  5     9.404673    3.8396964     9.842192         g         x         u
6  6     0.455565    2.7438364     1.542023         d         v         y
> df2 <- tibble::as_tibble(df)
> head(df2)
# A tibble: 6 × 7
     ID numeric_col1 numeric_col2 numeric_col3 text_col1 text_col2 text_col3
  <int>        <dbl>        <dbl>        <dbl> <chr>     <chr>     <chr>    
1     1        2.88         8.95          3.20 s         g         d        
2     2        7.88         3.74          3.08 x         NA        NA       
3     3        4.09         6.65          2.20 t         z         NA       
4     4        8.83         0.948         3.69 o         j         e        
5     5        9.40         3.84          9.84 g         x         u        
6     6        0.456        2.74          1.54 d         v         y        

There are also functions for displaying numbers as text with a particular format, but then you can't calculate with them.
What is your goal?

[osman3144]

@FJCC

but then you can't calculate with them

my goal is calculation new values with dataframe and saving them as seen. then ı import them to rstudio ı want to see values as is in original table. ı dont want unwanted decimals shortly.

[FJCC]

If you want two digits after the decimal in all numeric columns, you can do something like this.

library(dplyr)
 head(df)
  ID numeric_col1 numeric_col2 numeric_col3 text_col1 text_col2 text_col3
1  1     2.875775    8.9504536     3.198206         s         g         d
2  2     7.883051    3.7446278     3.077200         x      <NA>      <NA>
3  3     4.089769    6.6511519     2.197676         t         z      <NA>
4  4     8.830174    0.9484066     3.694889         o         j         e
5  5     9.404673    3.8396964     9.842192         g         x         u
6  6     0.455565    2.7438364     1.542023         d         v         y
> df <- df |> mutate(across(.cols=where(is.numeric), ~round(., digits = 2)))
> head(df)
  ID numeric_col1 numeric_col2 numeric_col3 text_col1 text_col2 text_col3
1  1         2.88         8.95         3.20         s         g         d
2  2         7.88         3.74         3.08         x      <NA>      <NA>
3  3         4.09         6.65         2.20         t         z      <NA>
4  4         8.83         0.95         3.69         o         j         e
5  5         9.40         3.84         9.84         g         x         u
6  6         0.46         2.74         1.54         d         v         y