CODEWITHSUNDEEP
rcrosstab
Chandra
Chandra

Reputation: 524

R table (xtab?)

My data is in below format:

 Out Reg.   Task Date   Task State Name Task State Time
 A6ERE  01-03-2014  MANUAL  23:09:48
 A6ERE  01-03-2014  MANUAL  23:10:05
 A6ERE  01-03-2014  ASSIGN  23:24:44
 A6ERE  01-03-2014  CONFRM  23:25:15
 A6ERE  01-03-2014  ARR     23:45:07
 A6ERE  01-03-2014  STARTED 00:20:00
 A6ERE  01-03-2014  FINISH  00:39:00
 A6EED  01-03-2014  FREE    22:42:28
 A6EED  01-03-2014  MANUAL  23:37:37
 A6EED  01-03-2014  ASSIGN  23:37:41
 A6EED  01-03-2014  CONFRM  23:37:58
 A6EED  01-03-2014  ARR     00:34:26
 A6EED  01-03-2014  STARTED 01:04:00

I want to summarize like below:

Out Reg.Task Date   ARR          ASSIGN     CONFRM       FINISH     FREE        MANUAL    STARTED 
A6ERE   01-03-2014  23:45:07    23:24:44    23:25:15    00:39:00                23:10:05    00:20:00
A6EED   01-03-2014  00:34:26    23:37:41    23:37:58    01:41:00    22:42:28    23:37:37   01:04:00

I used pivot in excel and dcast in R (Creating a pivot table). They both output count of timestamps instead of time stamps.

Cross-tab in MS-Access is able to give above required output.

Is there anyway that I can acheive the same in R? Pls advise.

Thank you.

Upvotes: 0

Views: 624

Answers (1)

Roland
Roland

Reputation: 132706

It looks like you want to use the last value for duplicated values:

DF <- read.table(text="A6ERE  01-03-2014  MANUAL  23:09:48
 A6ERE  01-03-2014  MANUAL  23:10:05
 A6ERE  01-03-2014  ASSIGN  23:24:44
 A6ERE  01-03-2014  CONFRM  23:25:15
 A6ERE  01-03-2014  ARR     23:45:07
 A6ERE  01-03-2014  STARTED 00:20:00
 A6ERE  01-03-2014  FINISH  00:39:00
 A6EED  01-03-2014  FREE    22:42:28
 A6EED  01-03-2014  MANUAL  23:37:37
 A6EED  01-03-2014  ASSIGN  23:37:41
 A6EED  01-03-2014  CONFRM  23:37:58
 A6EED  01-03-2014  ARR     00:34:26
 A6EED  01-03-2014  STARTED 01:04:00")

dcast(DF, V1+V2~V3, value.var="V4", 
       fun.aggregate=function(x) tail(as.character(x), 1), fill="")

#     V1         V2      ARR   ASSIGN   CONFRM   FINISH     FREE   MANUAL  STARTED
#1 A6EED 01-03-2014 00:34:26 23:37:41 23:37:58          22:42:28 23:37:37 01:04:00
#2 A6ERE 01-03-2014 23:45:07 23:24:44 23:25:15 00:39:00          23:10:05 00:20:00

Upvotes: 1

Related Questions