[R] Decimals in R/SQL

[jim holtman]

The problem is that you data is 'integer' and I assume that the
database is keeping everything integer.  You can do what you are
doing, or convert to 'numeric':

> x <- read.table(textConnection("           ST                  AGEP                    PWGTP
+ 33323    1                30                 130
+ 33324    1                10                 186
+ 33325    1                 2                 162
+ 33326    1                80                  93
+ 33327    1                29                 135
+ 33328    1                66                  54
+ 33329    1                62                  54
+ 33330    1                21                 138
+ 33331    1                29                 103
+ 33332    1                 7                 144
+ 33333    1                 5                 143"), header = TRUE)
> closeAllConnections()
> str(x)
'data.frame':   11 obs. of  3 variables:
 $ ST   : int  1 1 1 1 1 1 1 1 1 1 ...
 $ AGEP : int  30 10 2 80 29 66 62 21 29 7 ...
 $ PWGTP: int  130 186 162 93 135 54 54 138 103 144 ...
> require(sqldf)
> xsum <- sqldf('
+     select ST, sum(AGEP * PWGTP) / sum(PWGTP)
+         from x
+         group by ST
+ ')
> xsum
  ST sum(AGEP * PWGTP) / sum(PWGTP)
1  1                             23
> # change to numeric instead of integer
> x$AGEP <- as.numeric(x$AGEP)
> str(x)
'data.frame':   11 obs. of  3 variables:
 $ ST   : int  1 1 1 1 1 1 1 1 1 1 ...
 $ AGEP : num  30 10 2 80 29 66 62 21 29 7 ...
 $ PWGTP: int  130 186 162 93 135 54 54 138 103 144 ...
> xsum <- sqldf('
+     select ST, sum(AGEP * PWGTP) / sum(PWGTP)
+         from x
+         group by ST
+ ')
> xsum
  ST sum(AGEP * PWGTP) / sum(PWGTP)
1  1                       23.81446
>


On Wed, Apr 13, 2011 at 12:42 PM, Rachel Licata <RachelL at kff.org> wrote:
> Thanks Jim.  It appears the issue may only be in SQLite.  SS09 is a large table and here is a subset of the variables I am working with.
>
>
> SS09
>            ST                  AGEP                    PWGTP
> 33323    1                30                 130
> 33324    1                10                 186
> 33325    1                 2                 162
> 33326    1                80                  93
> 33327    1                29                 135
> 33328    1                66                  54
> 33329    1                62                  54
> 33330    1                21                 138
> 33331    1                29                 103
> 33332    1                 7                 144
> 33333    1                 5                 143
>
> z <- dbGetQuery( connSQLite , "select ST, SUM(AGEP*PWGTP/SUM(PWGTP) as wgtage from ss09 group by ST")
>
> ST wgtage
> 1   1     37
> 2   2     33
> 3   4     36
> 4   5     37
> 5   6     35
>
> z <- dbGetQuery( connSQLite , "select ST, SUM(AGEP*PWGTP)*1.000000000/SUM(PWGTP)*1.00000000000000 as wgtage from ss09 group by ST")
>
> ST   wgtage
> 1   1 37.57083
> 2   2 33.94322
> 3   4 36.14499
> 4   5 37.51233
> 5   6 35.65581
>
> -----Original Message-----
> From: jim holtman [mailto:jholtman at gmail.com]
> Sent: Wednesday, April 13, 2011 12:16 PM
> To: Rachel Licata
> Cc: r-help at r-project.org
> Subject: Re: [R] Decimals in R/SQL
>
> You at least have to provide a subset of 'ss09' so we can see what the
> original data looks like.  I have not had any problems with decimals
> in using sqldf.
>
>> x <- as.data.frame(matrix(runif(100)*100, 10))
>> x$key <- sample(1:3, 10, TRUE)
>> require(sqldf)
>> xsum <- sqldf('
> +     select key, sum(V1 * V2) / sum(V3)
> +         from x
> +         group by key
> + ')
>>
>>
>> xsum
>  key sum(V1 * V2) / sum(V3)
> 1   1               19.38166
> 2   2               17.40503
> 3   3               71.48818
>> dput(xsum)
> structure(list(key = 1:3, `sum(V1 * V2) / sum(V3)` = c(19.3816573628268,
> 17.4050302312273, 71.4881812227571)), .Names = c("key", "sum(V1 * V2) / sum(V3)"
> ), row.names = c(NA, 3L), class = "data.frame")
>>
>
>
>
> On Wed, Apr 13, 2011 at 11:10 AM, Rachel Licata <RachelL at kff.org> wrote:
>> Hello,
>>
>> When I am writing in sqldf or RSQLite I lose the decimals in my matrix.
>> The only way I can get decimals is by multiplying by 1.00000, etc.  I
>> have tried manipulating the options, but it is only effective once I
>> multiply by 1.0000.
>>
>> I appreciate any suggestions!
>>
>> Thanks!
>>
>> Example:
>>
>> z <- sqldf ("select ST,
>> SUM(AGEP*PWGTP)*1.000000000/SUM(PWGTP)*1.00000000000000 as wgtage from
>> ss09 group by ST")
>>
>> z shouldn't be whole numbers.
>>
>>
>>        [[alternative HTML version deleted]]
>>
>> ______________________________________________
>> R-help at r-project.org mailing list
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>>
>
>
>
> --
> Jim Holtman
> Data Munger Guru
>
> What is the problem that you are trying to solve?
>



-- 
Jim Holtman
Data Munger Guru

What is the problem that you are trying to solve?