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?
Your data in the dataframe is really a factor; you can see that with 'str'. Here is an outline of what you can do to remove the extra characters and then convert to a number.> x <- data.frame(actual06="$146,990.33") > str(x)'data.frame': 1 obs. of 1 variable: $ actual06: Factor w/ 1 level "$146,990.33": 1> typeof(x$actual06)[1] "integer"> # need to convert to character and get rid of extra characters > y <- as.character(x$actual06) > y[1] "$146,990.33"> (z <- gsub("[$,]", "", y)) # now a numeric character string[1] "146990.33"> as.numeric(z)[1] 146990.3> >On 10/17/07, Roger Mason <rmason at esd.mun.ca> 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? > > ______________________________________________ > 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 Cincinnati, OH +1 513 646 9390 What is the problem you are trying to solve?
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
Hello Moshe, Moshe Olshansky <m_olshansky at yahoo.com> writes:> Try > x <- as.integer(Membersind06$actual06) > and then > x/10I think I tried that without success (but I tried so many things, that one may have escaped me). In the meantime I changed the database relation to a numeric type [numeric(10,2)] because I discovered that the "money" type will be (or has already been) dropped from forthcoming versions of postgresql. Thanks for you help, Roger