<html>
<head>
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 12pt;
font-family:Calibri
}
--></style></head>
<body class='hmmessage'><div dir='ltr'><p abp="3229"> Dear all, <br abp="3230">I'm writing to you because I'm not able to construct in a long data 2 new variables <br abp="3231">based on other columns for each id. Small example with only one subject:</p><p abp="3232">dt <- data.table(<br abp="3233"> id = rep(1,7),<br abp="3234"> bday = rep(as.Date("1960-10-29"),7),<br abp="3235"> start = rep(as.Date("2005-02-27"),7),<br abp="3236"> marker0 = rep(125,7),<br abp="3237"> datep = as.Date(c('2005-04-20','2005-10-28','2005-12-31','2006-08-10','2006-12-31','2007-02-19','2007-05-15')),<br abp="3238"> marker = c(10,2,0,5,3,7,1)<br abp="3239"> )</p><p abp="3240"> </p><p abp="3240">I would want to construct sistematically a new data table from three conditions (I have the first):</p><p abp="3241"> </p><p abp="3242">1) It only keeps rows whose datep variable is 31st december or is the last date within id (I can get it)<br abp="3243">newdt <- unique(rbind(dt[which(month(datep)==12 & as.POSIXlt(datep)$mday==31)], <br abp="3244"> dt[, .SD[.N], by='id'])[,list(id,init=0,marker0,sum=0,dsum=datep)])[order(id,dsum)]</p><p abp="3245"> id init marker0 sum dsum<br abp="3246">1: 1 0 125 0 2005-12-31<br abp="3247">2: 1 0 125 0 2006-12-31<br abp="3248">3: 1 0 125 0 2007-05-15 </p><p abp="3249"> </p><p abp="3250">2) It has a so-called init variable, whose value is defined in 1st row as difference (yr) between <br abp="3251"> start and bday, in 2nd row as difference between dsum of 1st row and bday and finally the 3rd<br abp="3252"> row as difference between dsum of 2nd row and bday:<br abp="3253"> id bday ini marker0 sum dsum<br abp="3254">1: 1 1960-10-29 difftime(start, bday)/365.25 125 0 2005-12-31 <br abp="3255">2: 1 1960-10-29 difftime(as.Date("2005-12-31"), bday)/365.25 125 0 2006-12-31 <br abp="3256">3: 1 1960-10-29 difftime(as.Date("2006-12-31"), bday)/365.25 125 0 2007-05-15 </p><p abp="3257"> </p><p abp="3258">3) It has also a sum variable, whose value is defined as follows:<br abp="3259"> 3a) For first row within each id: The corresponding marker0 value.<br abp="3260"> 3b) For each of the following rows within id: Previous sum value plus<br abp="3261"> the sum of marker's values across the previous year.</p><p abp="3262"> id init marker0 sum dsum <br abp="3263">1: 1 44.3 125 125 2005-12-31 <br abp="3264">2: 1 45.2 125 125+10+2=137 2006-12-31 <br abp="3265">3: 1 46.2 125 137+5+3=145 2007-05-15 </p><p abp="3262"> </p><p abp="3262">Thanks to all R-data table help community for your continuous help!</p> </div></body>
</html>