[R] sqldf and not converting integers to floating point in SQLite

[Frederik Vanrenterghem]

Hi,

I have following 2 tables:

Table 1:
POSTAL | VALUE
1000|49
1010|100
1020|50

Table 2:
INSEE | POSTAL
A|1000
B|1000
C|1010
D|1020

I would like to convert this to the following:

INSEE | VALUE_SPREAD
A|24.5
B|24.5
C|100
D|50

I can achieve this with a nested SQL query (through counting the
number of POSTAL that belong to any given INSEE, and diving the value
of the postal in that INSEE by that number).

library(sqldf)
table1 <- read.csv("c:/R/table1.csv", sep=";")
table2 <- read.csv("c:/R/table2.csv", sep=";")
table3 <- sqldf("select table2.INSEE, table1.VALUE / counts.nPostals
as value_spread from table1, table2,(select POSTAL, count(INSEE) as
nPostals from table2 group by POSTAL) counts where table1.POSTAL =
counts.POSTAL and table1.POSTAL=table2.POSTAL")

Unfortunately, the value I'm working with is an integer. In SQLite,
this results in the computed value also not being a float - so it gets
rounded up or down. In this case, I'm getting 24 for A & B instead of
24.5.

Is there a way to take care of this using other R concepts, avoiding
that problem (for instance using melt & cast)?

Thanks,
Frederik