
On Sep 6, 2012, at 2:02 AM, HJ YAN wrote:

> Hi Arun and David
> 
> 
> Thanks a lot for your reply and sorry for sending the csv file.  (p.s. I can download my csv file from the email I sent, so I'm not sure why this doesn`t work for other users...)

I already explained why. You did not read the information about what file formats are acceptable. (And you have not yet learned to post in plain text either.) Please read the two links at the bottom of every r-help posting.

> 
> Anyway, below I used dput()  and I am attaching the output from R: 
> 
> 
> structure(list(V1 = c(532703L, 532703L, 532703L, 532703L, 532703L, 
> 532703L, 532703L, 532703L, 532703L, 532703L, 532703L, 532703L, 
> 532703L, 532703L, 532703L, 532703L, 532703L, 532703L, 532703L, 
> 
snipped

> 0.04, 0.04, 0.04, 0.04, 0.04, 0.04, 0.04, 0.04, 0.04, 0.04, 0.04, 
> 0.04, 0.04, 0.04, 0.04, 0.04, 0.04, 0.04, 0.04, 0.04, 0.04, 0.04, 
> 0.04, 0.04, 0.04, 0.04)), .Names = c("V1", "V2", "V3"), class = "data.frame", row.names = c(NA, 
> -912L))
> 
> 
> Let make my question clearer:
> 
> I want to make summary of the data above into a table as below to show the days that there are any data available, e.g.value=1 if
> there are any data available for that day, otherwise value=0. There are three id in my data: 532703, 532704 and 532705. data are collected at 10 minutes interval each day (e.g. 144 observations for a day if no missing data). 


I showed you how to use xtabs as well. The only additional information you need is a method to convert those factors that need to be Date-times into POSIXct format and a further method to group into Dates.

> 
> 
>               28/04     29/04    30/04    01/05   02/05
> 532703     0              1         1           1        0
> 532704     1              1         1           1        1
> 532705     0              0         1           1        0


Given the larger number of data
> dat$V2dt <- as.POSIXct(dat$V2, format="%d/%m/%Y %H:%M")
> str(dat)
'data.frame':	912 obs. of  4 variables:
 $ V1  : int  532703 532703 532703 532703 532703 532703 532703 532703 532703 532703 ...
 $ V2  : Factor w/ 490 levels "01/05/2012 00:00",..: 258 259 260 261 262 263 264 265 266 267 ...
 $ V3  : num  0.03 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 0.04 ...
 $ V2dt: POSIXct, format: "2012-04-29 09:10:00" "2012-04-29 09:20:00" "2012-04-29 09:30:00" "2012-04-29 09:40:00" ...

> tbl <- xtabs( ~ V1+as.Date(V2dt), data=dat)

tbl
        as.Date(V2dt)
V1       2012-04-29 2012-04-30 2012-05-01 2012-05-02
  532703         47        144         86          0
  532704        111        144        144         91
  532705          0          0        122         23

That's probably a better method than converting to the D-M format because that gets arranged inappropriately.

> xtabs( ~ V1+ format( as.Date(V2dt), '%d/%m'), data=dat)
        format(as.Date(V2dt), "%d/%m")
V1       01/05 02/05 29/04 30/04
  532703    86     0    47   144
  532704   144    91   111   144
  532705   122    23     0     0

This would be a quick way to get your 1/0 desires met:

> (tbl >0)+0
        format(as.Date(V2dt), "%d/%m")
V1       01/05 02/05 29/04 30/04
  532703     1     0     1     1
  532704     1     1     1     1
  532705     1     1     0     0


Logical vectors get converted to 1/0 when you add 0 to them


I suppose you could save the xtabs output as an object and mess around with the column names. If that is your need I'm leaving that as an exercise for the reader.

> 
> 
> Sorry for the confusion to David, and hope this is clear now.
> 
> Many thanks again.
> 
> Best wishes,
> 
> HJ
> 
> 

> 
> I want to create a table (as below) to summarize the attached data
> (Test.csv, which can be read into R by using 'read.csv(Test.csv, header=F)'
> ), to indicate the day that there are any data available, e.g.value=1 if
> there are any data available for that day, otherwise value=0.
> 
> 
>               28/04     29/04    30/04    01/05   02/05
> 532703     0              1         1           1        0
> 532704     1              1         1           1        1
> 532705     0              0         1           1        0
> 
> Only Column A (Names: automatically stored as integer if being read into R)
> and Column B (date/time: automatically stored as factor if being read into
> R) are useful for this task.
> 
> Could anyone kindly provide me some hints/ideas about how to write some
> code to to this job please?
> 
> 
> Many thanks in advance!
> 
> Best wishes
> HJ
> 
> ______________________________________________
> R-help@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.
> 
> 
--- 

David Winsemius, MD
Alameda, CA, USA


	[[alternative HTML version deleted]]


