HI,
You may try this:

dat1<- read.table(text="
CustID TripDate Store Bread Butter Milk Eggs
1 2-Jan-12 a 2 0 2 1 
1 6-Jan-12 c 0 3 3 0 
1 9-Jan-12 a 3 3 0 0
1 31-Mar-13 a 3 0 0 0
2 31-Aug-12 a 0 3 3 0
2 24-Sep-12 a 3 3 0 0
2 25-Sep-12 b 3 0 0 0
",sep="",header=TRUE,stringsAsFactors=FALSE)
dat2<- dat1[,-c(1:3)]

res<- lapply(seq_len(ncol(dat2)),function(i) 
{x1<-cbind(dat1[,c(1:3)],dat2[,i]);colnames(x1)[4]<- 
colnames(dat2)[i];x2<-x1[x1[,4]!=0,];within(x2, 
{daysbetweentrips<-unlist(tapply(as.Date(x2$TripDate,"%d-%b-%y"),list(x2$CustID),function(x)
 
c(NA,as.numeric(diff(x)))));previoustripstore<-ave(x2$Store,x2$CustID,FUN=function(x)
 c(NA,x[-length(x)]));Nexttripstore<- ave(x2$Store,x2$CustID,FUN=function(x) 
c(x[-1],NA))})})


 res
#[[1]]
 # CustID  TripDate Store Bread Nexttripstore previoustripstore daysbetweentrips
#1      1  2-Jan-12     a     2             a              <NA>               NA
#3      1  9-Jan-12     a     3             a                 a                7
#4      1 31-Mar-13     a     3          <NA>                 a              447
#6      2 24-Sep-12     a     3             b              <NA>               NA
#7      2 25-Sep-12     b     3          <NA>                 a                1

#[[2]]
 # CustID  TripDate Store Butter Nexttripstore previoustripstore
#2      1  6-Jan-12     c      3             a              <NA>
#3      1  9-Jan-12     a      3          <NA>                 c
#5      2 31-Aug-12     a      3             a              <NA>
#6      2 24-Sep-12     a      3          <NA>                 a
 # daysbetweentrips
#2               NA
#3                3
#5               NA
#6               24

#[[3]]
 # CustID  TripDate Store Milk Nexttripstore previoustripstore daysbetweentrips
#1      1  2-Jan-12     a    2             c              <NA>               NA
#2      1  6-Jan-12     c    3          <NA>                 a                4
#5      2 31-Aug-12     a    3          <NA>              <NA>               NA

#[[4]]
 # CustID TripDate Store Eggs Nexttripstore previoustripstore daysbetweentrips
#1      1 2-Jan-12     a    1          <NA>              <NA>               NA



A.K.


Hi, I have a very quick question.. I have a data which has sales per 
category per trip of each customer at different store locations, like 
below..(dataset1 frome xcel attachment) CustID  TripDate        Store   Bread   
Butter  Milk    Eggs
1       2-Jan-12          a     2       0       2       1
1       6-Jan-12          c     0       3       3       0
1       9-Jan-12          a     3       3       0       0
1       31-Mar-13 a     3       0       0       0
2       31-Aug-12 a     0       3       3       0
2       24-Sep-12 a     3       3       0       0
2       25-Sep-12 b     3       0       0       0 Here i have shown 4 items and 
their sales per customer per trip at each 
store... However, my data contains around 100 columns with item names.. 
All i need to do is following: 1. Create a separate dataframe for each item. 
That is, create 100 
dataframs one for each item.. Within the dataframe for Butter, for 
example, will be contained columns 1-3 and Butter column, specifically 
filtered for rows where butter>0 in sales..(so rows 1,4,7 will be 
dropped from this dataframe)..Likewise for all items...(sample output 
for butter is: (dataset2) CustID        TripDate        Store   Butter
1       6-Jan-12           c    3
1       9-Jan-12           a    3
2       31-Aug-12  a    3
2       24-Sep-12  a    3 2. In same loop, create new derived variables within 
each dataframe for 
each item... like create a lag variable for TripDate, create lag 
variable for storename in next trip, storename in previous trip etc... 
and also # days between trips to each store for each customer...(an 
example for Butter dataframe with new derived variables would be...)
Dataset needs to be sorted by CustID, TripDate, Store before creating 
derived variables (dataset3)Book1.xlsx CustID   TripDate        Store   Butter  
NextTripstore previoustripstore 
daysbetweentrips
1       6-Jan-12           c    3       a                     -        -
1       9-Jan-12           a    3       -                     c        -
2       31-Aug-12  a    3       a                     -        -
2       24-Sep-12  a    3       -                     a      24 Point of 
creating multiple item level dataframes is, i will use them 
iteratively as i will perform some regression on these datasets, using 
same set of variables each time

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

Reply via email to