Create XTS with dates with different number of rows

Question

Trying to create an xts file but after formatting upon loading in, I have different number of rows for dates than I do for my data. My data has many columns with varying number of rows, anywhere from 20 to 200. I want to create a separate variable after loading in, and the variable with depend on the composite I want to look at, so I want a full data.frame with NAs before creating a variable where I will na.omit and reduce the dimensions.

Here is the code:

#load file with desired composite
allcomposites <- read.csv("Composites 2014.08.31.csv", header = T)
compositebench <- allcomposites[1, 2:ncol(allcomposites)]
dates1 <- as.Date(allcomposites$Name, format = "%m/%d/%Y")
allcomposites <- as.data.frame(lapply(allcomposites[2:nrow(allcomposites),2:ncol(allcomposites)],    as.numeric))
allcomposites <- as.xts(allcomposites, order.by = dates1)
## Error in xts(x, order.by = order.by, frequency = frequency, ...) :
##     NROW(x) must match length(order.by)

Edit to show what allcomposites looks like:

Name    Composite1  Composite2  Composite3  Composite4 Composite5
Bmark   229 229 982 612 995
8/31/2014   0.9979  0.9404  4.3808      3.9296
7/31/2014   -0.4563 -0.3038 -1.7817     -1.7248
6/30/2014   0.205   0.2234  2.2184      2.7304
5/31/2014   1.311   1.5771  3.4824      1.7601
4/30/2014   0.9096  1.0187  -1.9195     1.2964

could you give a small example of what allcomposites looks like? — DMT, Sep 17 '14 at 19:58
One problem would be that Bmark is caught up in allcomposites$Name, and you try to convert Bmark to a date. — DMT, Sep 17 '14 at 20:41
As @DMT said: you need to omit the first observation from `dates1`: `dates1 <- as.Date(allcomposites$Name[-1], format = "%m/%d/%Y")`. — Joshua Ulrich, Sep 18 '14 at 00:04

score 0 · Answer 1 · answered Sep 18 '14 at 00:16

You need to be more careful when removing the first row from dates1 as well as allcomposites.

Here's another way to accomplish your goal:

Lines <- "Name    Composite1  Composite2  Composite3  Composite4 Composite5
Bmark   229 229 982 612 995
8/31/2014   0.9979  0.9404  4.3808      3.9296
7/31/2014   -0.4563 -0.3038 -1.7817     -1.7248
6/30/2014   0.205   0.2234  2.2184      2.7304
5/31/2014   1.311   1.5771  3.4824      1.7601
4/30/2014   0.9096  1.0187  -1.9195     1.2964"

library(xts)
# use fill=TRUE because you only provided data for 4 composites
allcomp <- read.table(text=Lines, header=TRUE, fill=TRUE)
# remove the first row that contains "Bmark"
allcomp <- allcomp[-1,]
# create an xts object from the remaining data
allcomp_xts <- xts(allcomp[,-1], as.Date(allcomp[,1], "%m/%d/%Y"))

Michael Staton · Answer 2 · 2016-12-19T05:07:15.177

## Error in xts(x, order.by = order.by, frequency = frequency, ...
##     NROW(x) must match length(order.by)

I wasted hours running into this error. Regardless of whether or not I had the exact same problem, I'll show how I solved for this error message in case it saves you the pain I had.

I imported an Excel or CSV file (tried both) through several importing functions, then tried to convert my data (as either a data.frame or .zoo object) into an xts object and kept getting errors, this one included.

I tried creating a vector of dates seperately to pass in as the order.by parameter. I tried making sure the date vector the rows of the data.frame were the same. Sometimes it worked and sometimes it didn't, for reasons I can't explain. Even when it did work, R had "coerced" all my numeric data into character data. (Causing me endless problems, later. Watch for coercion, I learned.)

These errors kept happening until:

For xts conversion I used the date column from the imported Excel sheet as the order.by parameter with an as.Date() modifier, AND I *dropped the date column during the conversion to xts.*

Here's the working code:

xl_sheet <- read_excel("../path/to/my_excel_file.xlsx")
sheet_xts <- xts(xl_sheet[-1], order.by = as.Date(xl_sheet$date))

Note my date column was the first column, so the xl_sheet[-1] removed the first column.

Create XTS with dates with different number of rows

2 Answers2