Hi, I'm trying to read in a bunch of CSV files into R where many columns are coded like $111.11. When reading them in they are treated as factors. I'm wondering if there is an easy way to convert them into numeric in R (as I don't want to modify the source data)? I've done some searches and can't seem to find an easy way to do this. I apologise if this is a trivial question, I haven't been using R for a while. Many thanks in advance! Cheers Kev Kevin Wang> Senior Advisor, Health and Human Services Practice > Government Advisory Services > > KPMG > 10 Shelley Street > Sydney NSW 2000 Australia > > Tel +61 2 9335 8282 > Fax +61 2 9335 7001 >kevinwang@kpmg.com.au> Protect the environment: think before you print > >[[alternative HTML version deleted]]
If you use Linux, we can simply use "sed" (in Linux terminal, NOT R) to delete all leading '$' from the file "test.dat" by $ sed -e 's/\$//g' test.dat > newdata.dat And now R will read all this dollar as numeric. Bests, Ruihong On 05/05/2010 09:16 AM, Wang, Kevin (SYD) wrote:> Hi, > > I'm trying to read in a bunch of CSV files into R where many columns are > coded like $111.11. When reading them in they are treated as factors. > > I'm wondering if there is an easy way to convert them into numeric in R > (as I don't want to modify the source data)? I've done some searches > and can't seem to find an easy way to do this. > > I apologise if this is a trivial question, I haven't been using R for a > while. > > Many thanks in advance! > > Cheers > > Kev > > Kevin Wang > >> Senior Advisor, Health and Human Services Practice >> Government Advisory Services >> >> KPMG >> 10 Shelley Street >> Sydney NSW 2000 Australia >> >> Tel +61 2 9335 8282 >> Fax +61 2 9335 7001 >> >> > kevinwang at kpmg.com.au > > >> Protect the environment: think before you print >> >> >> > > [[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. > >
Hi, Something similar to this maybe?> test <- as.factor("$111.11") > test[1] $111.11 Levels: $111.11> as.numeric(substring(as.character(test),2))[1] 111.11 To be applied to your data.frame columns. /Fredrik On Wed, May 5, 2010 at 9:16 AM, Wang, Kevin (SYD) <kevinwang at kpmg.com.au> wrote:> Hi, > > I'm trying to read in a bunch of CSV files into R where many columns are > coded like $111.11. ?When reading them in they are treated as factors. > > I'm wondering if there is an easy way to convert them into numeric in R > (as I don't want to modify the source data)? ?I've done some searches > and can't seem to find an easy way to do this. > > I apologise if this is a trivial question, I haven't been using R for a > while. > > Many thanks in advance! > > Cheers > > Kev > > Kevin Wang >> Senior Advisor, Health and Human Services Practice >> Government Advisory Services >> >> KPMG >> 10 Shelley Street >> Sydney ?NSW ?2000 ?Australia >> >> Tel ? +61 2 9335 8282 >> Fax ? +61 2 9335 7001 >> > kevinwang at kpmg.com.au > >> Protect the environment: think before you print >> >> > > > ? ? ? ?[[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. >-- "Life is like a trumpet - if you don't put anything into it, you don't get anything out of it."
Kev- The most reliable way to do the conversion is as follows:> x = factor(c('$112.11','$119.15','$121.32')) > as.numeric(sub('\\$','',as.character(x)))[1] 112.11 119.15 121.32 This way negative quantities and numbers without dollar signs are handled correctly. There's certainly no need to create a new input file. It may be easier to understand as as.numeric(sub('$','',as.character(x),fixed=TRUE)) which gives the same result. - Phil Spector Statistical Computing Facility Department of Statistics UC Berkeley spector at stat.berkeley.edu On Wed, 5 May 2010, Wang, Kevin (SYD) wrote:> Hi, > > I'm trying to read in a bunch of CSV files into R where many columns are > coded like $111.11. When reading them in they are treated as factors. > > I'm wondering if there is an easy way to convert them into numeric in R > (as I don't want to modify the source data)? I've done some searches > and can't seem to find an easy way to do this. > > I apologise if this is a trivial question, I haven't been using R for a > while. > > Many thanks in advance! > > Cheers > > Kev > > Kevin Wang >> Senior Advisor, Health and Human Services Practice >> Government Advisory Services >> >> KPMG >> 10 Shelley Street >> Sydney NSW 2000 Australia >> >> Tel +61 2 9335 8282 >> Fax +61 2 9335 7001 >> > kevinwang at kpmg.com.au > >> Protect the environment: think before you print >> >> > > > [[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. >
Hi,
I'm trying to check a column of dates and creating indicator variables
showing different time periods.
For example, if I were to do this in Excel I would have something like:
Year to Dec 2004=IF(AND(referral date<DATE(2005,1,1), OR($closure
date>DATE(2003,12,31), LEN($closure date) = 0)), "Yes",
"No")
for the "Year to Dec 2004" column.
I've read the data into R and converted the two columns (referral date and
closure date) to as.Date() format (found this format from r-help):
referral <- as.Date(issues.sub$Referraldate, "%d/%m/%Y")
closure <- as.Date(issues.sub$Dateofclosure, "%d/%m/%Y")
But then have trouble doing the "if" checking. I tried something
like:
Dec04<- ifelse(referral < as.Date("1/1/2005",
"%d/%m/%Y") &
(closure > as.Date("31/12/2003",
"%d/%m/%Y") || closure = ""), "Yes",
"No")
but got an error message:
Error in closure > as.Date("31/12/2003", "%d/%m/%Y") ||
closure = "" :
could not find function "||<-"
I haven't really played with dates in R before so would really appreciate
some guidance on this.
(I'm avoiding Excel because there are over 130,000 rows to check, and I need
to repeat this process for each quarter since Dec 2004. When I tried this in
Excel it just took all the memory I have on my computer and freezes the entire
workbook).
Cheers
Kevin
Kevin Wang
Senior Advisor, Health and Human Services Practice
Government Advisory Services
KPMG
10 Shelley Street
Sydney NSW 2000 Australia
Tel +61 2 9335 8282
Fax +61 2 9335 7001
kevinwang at kpmg.com.au
******************************************************************
The information in this e-mail is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this
e-mail by anyone else is unauthorised. If you have received this
communication in error, please notify us immediately by return
e-mail with the subject heading "Received in error" or telephone
+61 2 93357000, then delete the email and destroy any copies of it.
If you are not the intended recipient, any disclosure, copying,
distribution or any action taken or omitted to be taken in reliance
on it, is prohibited and may be unlawful. Any opinions or advice
contained in this e-mail are subject to the terms and conditions
expressed in the governing KPMG client engagement letter. Opinions,
conclusions and other information in this e-mail and any
attachments that do not relate to the official business of the firm
are neither given nor endorsed by it.
KPMG cannot guarantee that e-mail communications are secure or
error-free, as information could be intercepted, corrupted,
amended, lost, destroyed, arrive late or incomplete, or contain
viruses.
KPMG, an Australian partnership and a member firm of the KPMG
network of independent member firms affiliated with KPMG
International Cooperative (?KPMG International?), a Swiss entity.
KPMG International provides no services to clients.
Liability limited by a scheme approved under Professional Standards
Legislation.
*