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]]
______________________________________________
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.
