Skip to content

Read timeseries from csv file with xts or quantmod

8 messages · Achim Zeileis, Jeff Ryan, Gabor Grothendieck +2 more

Original
Mark Breman
# 
Hi,
I have a timeseries in a csv file that I want to read into an xts object.

The csv file looks like:

1948-01-01 51,7
1948-02-01 50,2
1948-03-01 43,3
1948-04-01 45,4
1948-05-01 49,5
1948-06-01 53
1948-07-01 48,4
1948-08-01 45,1
1948-09-01 42,1
1948-10-01 47,2
1948-11-01 42,4
1948-12-01 35
1949-01-01 32,9
1949-02-01 31,3
1949-03-01 34,5
1949-04-01 35,5
1949-05-01 32,6
1949-06-01 31,6
etc...

I have attached it to this post.

I tried to read the file with getSymbols() but it gives me an error:
------------------------------------- start trace
----------------------------------------------
dec=",")
Error in dimnames(x) <- dn :
  length of 'dimnames' [2] not equal to array extent
------------------------------------ end trace
-------------------------------------------------

Strange because read.csv() can read this file into a data.frame:

-------------------------------------- start trace
-----------------------------------------------
[1] "data.frame"
-------------------------------------- end trace
-------------------------------------------------

I tried to be smart and convert the data.frame to an xts object, but this
also gives me an error:

------------------------------------- start trace
-------------------------------------------------
Error in as.POSIXlt.character(x, tz, ...) :
  character string is not in a standard unambiguous format
------------------------------------- end trace
---------------------------------------------------

I am using R 2.8.1 for windows with xts 0.6-6 and quantmod 0.3-10.

What am I doing wrong here?

Regards,

-Mark-
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://stat.ethz.ch/pipermail/r-sig-finance/attachments/20090724/c030bac0/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: US_PMI_Monthly.csv
Type: application/octet-stream
Size: 12407 bytes
Desc: not available
URL: <https://stat.ethz.ch/pipermail/r-sig-finance/attachments/20090724/c030bac0/attachment.obj>
Achim Zeileis
#
On Fri, 24 Jul 2009, Mark Breman wrote:

            

      
      
      
    

        
You can use read.zoo() from the "zoo" package and the as.xts() to coerce 
to an "xts" series.

   library("xts")
   x <- as.xts(read.zoo("US_PMI_Monthly.csv", dec = ","))

Note that you have to set the decimal separator appropriately.

hth,
Z

            

      
      
      
    

  
  


  


      

    

    

      
      

        


  

        

      Jeff Ryan
    

    

      
      #
    

  


  

      
Doing nothing wrong, getSymbols.csv isn't general enough to handle this.

You can either read in with read.zoo (best option), and then convert
to xts if needed, or write/modify your own getSymbols.csv code to read
the correct columns.

Essentially getSymbols.csv is a simple function to illustrate the
generalization facility of getSymbols.  The idea is to write
getSymbols.XXXX calls that map to your specific data.

That said, a univariate case is sufficiently common to have
getSymbols.csv be smart enough to handle.  I will make the changes to
do this.

Thanks,

Jeff

        
On Fri, Jul 24, 2009 at 9:41 AM, Mark Breman<breman.mark at gmail.com> wrote:

            

      
      
      
    

        

      
      
    

        

  
    

      
      
    

  


  


      

    

    

      
      

        
      

    

    

      
      

        
      

    

    

      
      

        


  

        

      Gabor Grothendieck
    

    

      
      #
    

  


  

      
Try this:

Lines <- "1948-01-01 51,7
1948-02-01 50,2
1948-03-01 43,3
1948-04-01 45,4
1948-05-01 49,5
1948-06-01 53
1948-07-01 48,4
1948-08-01 45,1
1948-09-01 42,1
1948-10-01 47,2
1948-11-01 42,4
1948-12-01 35
1949-01-01 32,9
1949-02-01 31,3
1949-03-01 34,5
1949-04-01 35,5
1949-05-01 32,6
1949-06-01 31,6"
library(xts)
# z <- read.zoo("myfile.txt", dec = ",")
z <- read.zoo(textConnection(Lines), dec = ",")
x <- as.xts(z)

        
On Fri, Jul 24, 2009 at 10:41 AM, Mark Breman<breman.mark at gmail.com> wrote:

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      Joshua Ulrich
    

    

      
      #
    

  


  

      
Mark,

The as.xts(data.frame) case doesn't work because your data.frame does
not have rownames.  as.xts() converts the rownames into the xts index.
 If you read in your data with the following command, you can then
convert the object to xts.

            

      
      
      
    

        
V2
1948-01-01 51.7
1948-02-01 50.2
1948-03-01 43.3
1948-04-01 45.4
1948-05-01 49.5
1948-06-01 53.0

I would also note that your data, though in a CSV file, is not in CSV
format.  This may have unforeseen consequences when using commands
written for CSV format files (getSymbols.csv, read.csv, etc.).

HTH,
Josh
--
http://www.fosstrading.com

        
On Fri, Jul 24, 2009 at 10:07 AM, Mark Breman<breman.mark at gmail.com> wrote: