type conversion
On Wed, 2007-10-17 at 17:20 -0230, Roger Mason wrote:
Hello, I am trying to do arithmetic on numbers extracted from a PostgreSQL database, wherein the numbers are stored as type "money". The transcript below illustrates the problem.
Membersind06 <- sqlQuery(channel,"select * from income where rowname like '%Membersind%'") names(Membersind06)
[1] "rowname" "item" "budget07" "budget08" "actual06"
Membersind06$actual06
[1] $149,625.00 Levels: $149,625.00
typeof(Membersind06$actual06)
[1] "integer"
Membersind06$actual06/10
[1] NA Warning message: / not meaningful for factors in: Ops.factor(Membersind06$actual06, 10) Can someone show me how to convert the quantity stored in Membersind06$actual06 to a numeric type such that I can perfor arithmetic operations on it?
The value is coming in as a factor:
typeof("A")
[1] "character"
typeof(factor("A"))
[1] "integer" However, note:
is.integer(factor("A"))
[1] FALSE In this case, you will likely want to use gsub() to strip the '$' and the ',' and then convert to numeric:
as.numeric(gsub("[^0-9\\.]", "", "$149,625.00"))
[1] 149625 Note that the trailing 0's are not printed by default in R, since they don't represent significant digits. Another example:
as.numeric(gsub("[^0-9\\.]", "", "$149,625.23"))
[1] 149625.2 However:
print(as.numeric(gsub("[^0-9\\.]", "", "$149,625.23")), 8)
[1] 149625.23
This has nothing to do with the way data is stored in R, only the way it
is displayed.
See ?options and review 'digits'.
So, for your data above:
Membersind06$actual06 <- as.numeric(gsub("[^0-9\\.]", "",
Membersind06$actual06))
See ?gsub and ?regex
HTH,
Marc Schwartz