Save xts object as csv file

Question

I loaded a csv file using:

data = read.csv(file="/home/stefanos/R/data_frames_new/temp2.csv", header=TRUE, sep=",")

The first 4 lines of the temp2.csv file are:

nodeId,battery,date,idHistoric,temperature,longitude,latitude
3,78.00,2013-04-01 00:04:03,30163676,13.74,-3.80176,43.46192
3,78.00,2013-04-01 00:09:01,30164278,13.67,-3.80176,43.46192
3,78.00,2013-04-01 00:13:59,30164875,13.67,-3.80176,43.46192

I want to group it by nodeId and find the mean value of the temperature for every 15 minutes. So I type:

df <- xts(x = data[, c("nodeId", "battery", "idHistoric", "temperature", "longitude", "latitude")], order.by = as.POSIXct(data[, "date"], tz = "GMT", format = "%Y-%m-%d %H:%M:%S"))

and then:

df2 <- by(df,df$nodeId,function(x){
   ends <- endpoints(x, on = "minutes", k = 15)
   xx    <- period.apply(x, ends, mean)
 })

My problem is that I cannot write the df2 to csv file. I have not yet been able to do so. When I print df2 in screen I see the following structure:

/*********************************************/

INDICES: 3
                    nodeId  battery idHistoric temperature longitude latitude
2013-04-01 00:13:59      3 78.00000   30164276    13.69333  -3.80176 43.46192
2013-04-01 00:28:54      3 79.00000   30166075    13.78000  -3.80176 43.46192
[...]
------------------------------------------------------------ 
INDICES: 4
                    nodeId  battery idHistoric temperature longitude latitude
2013-04-01 00:13:07      4 87.00000   30164172    14.42667  -3.80098 43.46199
2013-04-01 00:28:01      4 87.33333   30165964    14.49000  -3.80098 43.46199
------------------------------------------------------------ 
INDICES: 5
                    nodeId  battery idHistoric temperature longitude latitude
2013-04-01 00:13:31      5 83.00000   30164224    13.84667  -3.80058 43.46203
2013-04-01 00:28:26      5 83.66667   30166018    14.06000  -3.80058 43.46203
------------------------------------------------------------ 
INDICES: 6
                    nodeId  battery idHistoric temperature longitude latitude
2013-04-01 00:12:52      6 78.00000   30164128    13.99667  -3.79979 43.46212
2013-04-01 00:28:52      6 79.00000   30165983    13.97333  -3.79979 43.46212

/*********************************************/

So how can I save it in CSV?

Answer 1

You can do this (as mentioned by @Roland in the comment)

write.table(do.call(rbind,df2),file='test.csv')

Here a complete example with your data. You can use read.zoo to create your xts object, in one liner command:

library(zoo)
## you replace text= here by file=temp2.csv
dat <- read.zoo(text='nodeId,battery,date,idHistoric,temperature,longitude,latitude
3,78.00,2013-04-01 00:13:59,30163676,13.74,-3.80176,43.46192
3,78.00,2013-04-01 00:28:54,30163676,13.74,-3.80176,43.46192
4,78.00,2013-04-01 00:13:07,30164278,13.67,-3.80176,43.46192
4,78.00,2013-04-01 00:28:01,30163676,13.74,-3.80176,43.46192
5,78.00,2013-04-01 00:13:31,30163676,13.74,-3.80176,43.46192
5,78.00,2013-04-01 00:28:26,30164875,13.67,-3.80176,43.46192
6,78.00,2013-04-01 00:12:52,30164875,13.67,-3.80176,43.46192
6,78.00,2013-04-01 00:28:52,30164875,13.67,-3.80176,43.46192',header=TRUE,
                tz='',sep=',',index=3)

Then you create and save your list by group,

library(xts)
df2 <- by(dat,dat$nodeId,function(x){
  ends <- endpoints(x, on = "minutes", k = 1)
  xx    <- period.apply(x, ends, mean)
})

write.table(do.call(rbind,df2),file='test.csv')

To read it again you do just

read.table('test.csv')
                      nodeId battery idHistoric temperature longitude latitude
3.2013-04-01 00:13:59      3      78   30163676       13.74  -3.80176 43.46192
3.2013-04-01 00:28:54      3      78   30163676       13.74  -3.80176 43.46192
4.2013-04-01 00:13:07      4      78   30164278       13.67  -3.80176 43.46192
4.2013-04-01 00:28:01      4      78   30163676       13.74  -3.80176 43.46192
5.2013-04-01 00:13:31      5      78   30163676       13.74  -3.80176 43.46192
5.2013-04-01 00:28:26      5      78   30164875       13.67  -3.80176 43.46192
6.2013-04-01 00:12:52      6      78   30164875       13.67  -3.80176 43.46192
6.2013-04-01 00:28:52      6      78   30164875       13.67  -3.80176 43.46192

EDIT to save/ and read it again as a zoo objects, I transform sligthly the rownames of the binded list:

dd <- do.call(rbind,df2)
rownames(dd) <- gsub('*.[.]','',rownames(dd))
write.table(dd,file='test.csv')

Now I can read this again :

read.zoo('test.csv',index=0,tz='')
                    nodeId battery idHistoric temperature longitude latitude
2013-04-01 00:12:52      6      78   30164875       13.67  -3.80176 43.46192
2013-04-01 00:13:07      4      78   30164278       13.67  -3.80176 43.46192
2013-04-01 00:13:31      5      78   30163676       13.74  -3.80176 43.46192
2013-04-01 00:13:59      3      78   30163676       13.74  -3.80176 43.46192
2013-04-01 00:28:01      4      78   30163676       13.74  -3.80176 43.46192
2013-04-01 00:28:26      5      78   30164875       13.67  -3.80176 43.46192
2013-04-01 00:28:52      6      78   30164875       13.67  -3.80176 43.46192
2013-04-01 00:28:54      3      78   30163676       13.74  -3.80176 43.46192

EDIT2

Thanks to @Gsee excellent answer you can do the following :

do.call(rbind, unname(df2))

This will keep the row names right, so no need to use a regular expression as I did in my previous edit.

Answer 2

If you look at str(df2), you'll see that it is a named list. Generally, when you have a list and you want to convert it into a single object, you can use something like do.call(rbind, df2). This is the same as rbind(df2[[1]], df2[[2]], df2[[3]], df2[[4]]), but will work with a list of any length.

In this case, your list has names

> names(df2)
[1] "3" "4" "5" "6"

So, if you just do.call(rbind, df2), the rownames will not be quite what you want -- they'll be prepended with the names of the list.

> rownames(do.call(rbind, df2))
[1] "3.2013-04-01 00:13:59" "3.2013-04-01 00:28:54" "4.2013-04-01 00:13:07"
[4] "4.2013-04-01 00:28:01" "5.2013-04-01 00:13:31" "5.2013-04-01 00:28:26"
[7] "6.2013-04-01 00:12:52" "6.2013-04-01 00:28:52"

The solution is to unname the list

do.call(rbind, unname(df2))

Since you're working with xts, you're probably going to want to coerce that to an xts object:

> as.xts(do.call(rbind, unname(df2)))
                    nodeId battery idHistoric temperature longitude latitude
2013-04-01 00:12:52      6      78   30164875       13.67  -3.80176 43.46192
2013-04-01 00:13:07      4      78   30164278       13.67  -3.80176 43.46192
2013-04-01 00:13:31      5      78   30163676       13.74  -3.80176 43.46192
2013-04-01 00:13:59      3      78   30163676       13.74  -3.80176 43.46192
2013-04-01 00:28:01      4      78   30163676       13.74  -3.80176 43.46192
2013-04-01 00:28:26      5      78   30164875       13.67  -3.80176 43.46192
2013-04-01 00:28:52      6      78   30164875       13.67  -3.80176 43.46192
2013-04-01 00:28:54      3      78   30163676       13.74  -3.80176 43.46192

Finally, I find it convenient to use write.zoo to write csv files of xts or zoo objects:

write.zoo(as.xts(do.call(rbind, unname(df2))), file="test.csv", sep=",")