Skip to content

Summarizing data containing data/time information (as factor)

6 messages · HJ YAN, David Winsemius, arun

Original
HJ YAN
# 
Dear R user

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
arun
# 
Hi,

I couldn't find any attached data.? Could you dput() the data?
A.K.



----- Original Message -----
From: HJ YAN <yhj204 at googlemail.com>
To: r-help at r-project.org
Cc: 
Sent: Wednesday, September 5, 2012 7:57 PM
Subject: [R] Summarizing data containing data/time information (as factor)

Dear R user

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 at 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
#
On Sep 5, 2012, at 4:57 PM, HJ YAN wrote:

            

      
      
      
    

        
Unfortunately you did not read theinformation about posting attachments carefully enough and you csv filewas scrubbed by the mailserver.. I will attempt to recreate it:

            

      
      
      
    

        
+ 532703     0              1         1           1        0
+ 532704     1              1         1           1        1
+ 532705     0              0         1           1        0", header=TRUE)

            

      
      
      
    

        
At this point I think I have something similar to your original and will show how xtabs() can be used:

            

      
      
      
    

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

The dates are not sorted the same but if you used Date formatted values, they might.

            

      
      
      
    

        
dt
id       2012-04-28 2012-04-29 2012-04-30 2012-05-01 2012-05-02
  532703          0          1          1          1          0
  532704          1          1          1          1          1
  532705          0          0          1          1          0

            

      
      
      
    

        

      
      
    

        
David Winsemius, MD
Alameda, CA, USA

  
  


  


      

    

    

      
      

        
      

    

    

      
      

        


  

        

      arun
    

    

      
      #
    

  


  

      
Hi,
May be this is what you wanted:
I named your dput() data as dat1.
?source("HuYan.txt")
head(dat1)
dat1$date<- as.Date(dat1$V2,format="%d/%m/%Y %H:%M")
dat2<-aggregate(V3~date+V1,data=dat1,mean)
dat2$date1<-gsub(".*-(.*)-(.*)","\\2/\\1",dat2$date)
?dat2$V3<-1
?xtabs(V3~V1+date1,data=dat2)
??????? date1
V1?????? 01/05 02/05 28/04 29/04 30/04
? 532703???? 1???? 0???? 0???? 1???? 1
? 532704???? 1???? 1???? 1???? 1???? 1
? 532705???? 1???? 0???? 0???? 0???? 1
A.K.