[R-SIG-Finance] Importing from Excel

Gabor Grothendieck ggrothendieck at gmail.com
Mon Feb 4 21:14:23 CET 2008


Regarding read.xls in gdata note that it passes the ... to read.csv so you
could specify na.strings= which should avoid the problem
if its just due to the #NA strings.

On Mon, Feb 4, 2008 at 2:57 PM, Spencer Graves <spencer.graves at pdf.com> wrote:
>      I've had good luck with 'read.xls{gdata}', but that could have the
> same problem.
>
>      If this still gives you problems, you could put your favorite
> 'read.*(...)' in a function that tests columns you specify to see if
> they are numeric and if not tries to replace them with
>
>           is.numeric(as.character(DF[[i]]))
>
>      for column i of data.frame DF.  You could test that to see what
> percent NAs you have and write a warning, etc., whatever you want.
>
>      hope this helps.
>      Spencer
>
>
> MAB wrote:
> > Hi!
> >
> > I am trying to diff data imported from Excel.
> > I use the package xlsReadWrite.
> >
> > After I load the following XL spreadsheet
> >
> > C_Dates C_Price C_Prices_edit         C_Return_Raw        C_Return_edit
> > 1   30405   29.40         29.40                 #N/A                 #N/A
> > 2   30406   29.29         NaN                 -0.003               -0.003
> > 3   30407      NA         29.29                    0                    0
> > (#N/A is a string in one case and the formula =NA() in another)
> >
> > and try to diff the resulting object, I get:
> >
> > Error in r[i1, , drop = FALSE] - r[-nrow(r):-(nrow(r) - lag + 1), , drop =
> > FALSE] :
> >         non-numeric argument to binary operator
> >
> > I attempt to convert to numeric using as.matrix (or data.matrix),
> > but this converts the object to all characters.
> >
> > I can then use as.numeric to get a vector and rebuild the matrix but this gets
> > tedious.
> >
> > Eventually the easiest seems to be to make sure each column in the spreadsheet
> > starts with a numeric, and replace it once the object is in R.
> >
> > There is probably a better way.
> >
> > Michel
> >
> > _______________________________________________
> > R-SIG-Finance at stat.math.ethz.ch mailing list
> > https://stat.ethz.ch/mailman/listinfo/r-sig-finance
> > -- Subscriber-posting only.
> > -- If you want to post, subscribe first.
> >
>
> _______________________________________________
> R-SIG-Finance at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-sig-finance
> -- Subscriber-posting only.
> -- If you want to post, subscribe first.
>



More information about the R-SIG-Finance mailing list