unnecessary decimals appear when import data from sql to rstudio.

ı have a small table like below

WhatsApp Görsel 2024-01-11 saat 16.54.36_85e5bfdc

this is my table in sql db. and below table is imported table from sql.
vvvvvvvvvvvvvvvvvvvvvvvvvvvvv
WhatsApp Görsel 2024-01-11 saat 17.02.30_7f05f6f6

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
image

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

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

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.

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

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?

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.

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

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.