Hi Srecko, Try this: dat1<- read.table(text=" id module? event?????? time time_on_task Categ??? url 1??? sys? login 1373502892?????????? 80???? B???????? http:// 2?? task??? add 1373502892?????????? 80???? A???????? http://post/add?id=33&idp=67 3?? task??? add 1373502972?????????? 23???? A???????? http://post/add?id=34&idp=67 4??? sys? login 1373502892?????????? 80???? B????????? http:// 5?? list delete 1373502995????????? 901???? C????????? http:// 6?? list?? view 1373503896????????? 100???? D?????????? http:// 7?? task??? add 1373503996?????????? NA???? A??????? http://post/add?id=35&idp=99 ",sep="",header=TRUE,stringsAsFactors=FALSE) vec1<-as.numeric(gsub(".*\\?.*=(\\d+)\\&.*","\\1",dat1$url[dat1$Categ=="A"])) dat2<- read.table(text=" id idpost idtopic iduser 1?? 45????? 33?????? 101 2?? 46????? 34?????? 102 3?? 47????? 33?????? 103 4?? 48????? 33?????? 101 5?? 49????? 35?????? 104 ",sep="",header=TRUE) ?student_list<- c(101:102,104:107) ?vec2<-with(dat2,tapply(iduser,list(idtopic),FUN=function(x) all(x%in% student_list))) dat1$Categ[dat1$Categ=="A"][match(vec1,as.numeric(names(vec2)))[!vec2]]<-"F" ?dat1 #? id module? event?????? time time_on_task Categ????????????????????????? url #1? 1??? sys? login 1373502892?????????? 80???? B????????????????????? http:// #2? 2?? task??? add 1373502892?????????? 80???? F http://post/add?id=33&idp=67 #3? 3?? task??? add 1373502972?????????? 23???? A http://post/add?id=34&idp=67 #4? 4??? sys? login 1373502892?????????? 80???? B????????????????????? http:// #5? 5?? list delete 1373502995????????? 901???? C????????????????????? http:// #6? 6?? list?? view 1373503896????????? 100???? D????????????????????? http:// #7? 7?? task??? add 1373503996?????????? NA???? A http://post/add?id=35&idp=99 A.K.
From: srecko joksimovic <sreckojoksimovic at gmail.com>
To: arun <smartpink111 at yahoo.com>
Sent: Thursday, August 29, 2013 6:04 PM
Subject: Re: [R] Add new calculated column to data frame
To: arun <smartpink111 at yahoo.com>
Sent: Thursday, August 29, 2013 6:04 PM
Subject: Re: [R] Add new calculated column to data frame
"Did you mean to separate the number 33 from the link? ", yes that is correct. It should be something like this: #? id module? event?????? time time_on_task Categ ? ?url #1? 1??? sys? login 1373502892?????????? 80???? B ? ? ? ? http:// #2? 2?? task??? add 1373502892?????????? 80???? A ? ? ? ??http://post/add?id=33&idp=67 #3? 3?? task??? add 1373502972?????????? 23???? A ? ? ? ??http://post/add?id=34&idp=67 #4? 4??? sys? login 1373502892?????????? 80???? B ? ? ? ? ?http:// #5? 5?? list delete 1373502995????????? 901???? C ? ? ? ? ?http:// #6? 6?? list?? view 1373503896????????? 100???? D ? ? ? ? ? http:// #7? 7?? task??? add 1373503996?????????? NA???? A ? ? ? ?http://post/add?id=35&idp=99 from this table I should get 3 rows with 3 URLs:?http://post/add?id=33&idp=67,?http://post/add?id=34&idp=67, and?http://post/add?id=35&idp=99 For each of them, I need to extract id (33, 34, and 35). Once I do that, I need to obtain users from this table: id idpost idtopic iduser 1 ? 45 ? ? ?33 ? ? ? 101 2 ? 46 ? ? ?34 ? ? ? 102 3 ? 47 ? ? ?33 ? ? ? 103 4 ? 48 ? ? ?33 ? ? ? 101 5 ? 49 ? ? ?35 ? ? ? 104 again, for each id. This means:? id = 33 => 101, 103 id = 34 => 102 id = 35 => 104 Next, for each vector I need to check whether or not all it's values are in the students list (101,102, 104,105, 106,107) id = 33 => FALSE (since 103 is not in the list) id = 34 => TRUE id = 35 => TRUE This means that category for row 2 in the first table is not A any more, but F... Thanks, Srecko On Thu, Aug 29, 2013 at 2:56 PM, arun <smartpink111 at yahoo.com> wrote: HI Srecko, >Did you mean to separate the number 33 from the link? Could you provide a reproducible example with the output you expected? >Tx. > > >Arun > > > > > >________________________________ >From: srecko joksimovic <sreckojoksimovic at gmail.com> >To: arun <smartpink111 at yahoo.com> >Sent: Thursday, August 29, 2013 5:38 PM > >Subject: Re: [R] Add new calculated column to data frame > > > >Hi Arun, > >I really appreciate your help, and we did a great job :) >but, now I think that R can do anything, so I'd like to try one more thing, if you don't mind... > >from the table with categories,? > >#? id module? event?????? time time_on_task Categ ? ?url >#1? 1??? sys? login 1373502892?????????? 80???? B ? ? ? ? http: >#2? 2?? task??? add 1373502892?????????? 80???? A ? ? ? ??http: >#3? 3?? task??? add 1373502972?????????? 23???? A ? ? ? ??http: >#4? 4??? sys? login 1373502892?????????? 80???? B ? ? ? ? ?http: >#5? 5?? list delete 1373502995????????? 901???? C >#6? 6?? list?? view 1373503896????????? 100???? D >#7? 7?? task??? add 1373503996?????????? NA???? A > > >I'd like to use only certain category (for example A). Each of these fields has an url whose format is something like http://post/add?id=33&idp=45. First step would be to extract this id (33 in this case). Based on that value, I want to find all "iduser" from the following table: > >id idpost idtopic iduser >1 ? 45 ? ? ?33 ? ? ? 101 >2 ? 46 ? ? ?34 ? ? ? 102 > >3 ? 47 ? ? ?33 ? ? ? 103 > >4 ? 48 ? ? ?33 ? ? ? 101 > >5 ? 49 ? ? ?35 ? ? ? 104 > > >The next step would be to check if at least one of these values (iduser) is not in the vectors "users" (only ids). If that is the case, I want to change category to F, if not, I want to keep the same category. > >If this is too much for one question, I'll implement this in Java, but I'd really like to try this with R. Maybe this id extraction from url is the most important problem... I tried most of these steps, but still not able to put them all together... > >Thank you so much for your time. >Srecko > > > > > > > > >On Thu, Aug 29, 2013 at 12:22 PM, arun <smartpink111 at yahoo.com> wrote: > >Hi Srecko, >>No problem. >> >>Arun >> >> >> >> >> >> >>________________________________ >>From: srecko joksimovic <sreckojoksimovic at gmail.com> >>To: arun <smartpink111 at yahoo.com> >>Sent: Thursday, August 29, 2013 3:19 PM >> >>Subject: Re: [R] Add new calculated column to data frame >> >> >> >>This is great Arun, thank you again. >> >>I was thinking to use sqldf and issue query for each module-action combination, but this is much better. Since I have table with categories (module, action, category), I could create vector "levels" based on the first two columns and vector "labels" based on the category column and that should to the work... >> >>Best, >>Srecko >> >> >> >>On Thu, Aug 29, 2013 at 12:16 PM, arun <smartpink111 at yahoo.com> wrote: >> >>Hi Srecko, >>> >>>You didn't mention the order in which the letters are assigned.? If you need a different order, just change the order in the ",levels=c(....),". >>>Arun >>> >>> >>> >>> >>>----- Original Message ----- >>>From: arun <smartpink111 at yahoo.com> >>>To: srecko joksimovic <sreckojoksimovic at gmail.com> >>>Cc: R help <r-help at r-project.org> >>> >>>Sent: Thursday, August 29, 2013 3:13 PM >>>Subject: Re: [R] Add new calculated column to data frame >>> >>> >>> >>>Hi, >>>You could try this: >>>dat1<- read.table(text=" >>>id? module??? event?????? time?????????????????????? time_on_task >>>1?? sys???????? login???????? 1373502892?????????? 80 >>>2?? task??????? add????????? 1373502892?????????? 80 >>>3?? task??????? add????????? 1373502972?????????? 23 >>>4?? sys???????? login???????? 1373502892?????????? 80 >>>5?? list???????? delete?????? 1373502995????????? 901 >>>6?? list????????? view???????? 1373503896????????? 100 >>>7?? task??????? add????????? 1373503996?????????? NA >>>",sep="",header=TRUE,stringsAsFactors=FALSE) >>>?dat1$Categ<-as.character(factor(with(dat1,paste(module,event,sep="_")),levels=c("task_add","sys_login","list_delete","list_view"),labels=LETTERS[1:4])) >>> >>> >>>dat1 >>>#? id module? event?????? time time_on_task Categ >>>#1? 1??? sys? login 1373502892?????????? 80???? B >>>#2? 2?? task??? add 1373502892?????????? 80???? A >>>#3? 3?? task??? add 1373502972?????????? 23???? A >>>#4? 4??? sys? login 1373502892?????????? 80???? B >>>#5? 5?? list delete 1373502995????????? 901???? C >>>#6? 6?? list?? view 1373503896????????? 100???? D >>>#7? 7?? task??? add 1373503996?????????? NA???? A >>>A.K. >>> >>>________________________________ >>>From: srecko joksimovic <sreckojoksimovic at gmail.com> >>>To: arun <smartpink111 at yahoo.com> >>>Cc: R help <R-help at r-project.org> >>>Sent: Thursday, August 29, 2013 2:34 PM >>>Subject: Re: [R] Add new calculated column to data frame >>> >>> >>> >>>Hi Arun, >>> >>>There is one more question... you explained me how to use?split(dat1,cumsum(dat1$action=="login")) in one of previous questions, and that is great. >>>Now, if I have something like this: >>> >>>id ?module ? ?event?????? time ? ? ? ? ? ? ? ? ? ? ? time_on_task >>>1 ? sys ? ? ? ? login ? ? ? ? 1373502892?????????? 80 >>>2 ? task ? ? ? ?add ? ? ? ? ?1373502892?????????? 80 >>> >>>3 ? task ? ? ? ?add ? ? ? ? ?1373502972?????????? 23 >>>4 ? sys ? ? ? ? login ? ? ? ? 1373502892?????????? 80 >>>5 ? list ? ? ? ? delete ? ? ? 1373502995????????? 901 >>>6 ? list ? ? ? ? ?view ? ? ? ? 1373503896????????? 100 >>>7 ? task ? ? ? ?add ? ? ? ? ?1373503996?????????? NA >>>I know how to split at each "login" occurrence, and I know how to add new column with time differences. But, how to add new column "category" which will be calculated based on columns "module" and "even"? For example if module=task and event=add => category= A... >>> >>>Srecko >>> >>> >>> >>> >>> >>>On Thu, Aug 29, 2013 at 11:22 AM, arun <smartpink111 at yahoo.com> wrote: >>> >>>Hi Srecko, >>>>No problem. >>>>Regards, >>>>Arun >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>>________________________________ >>>>From: srecko joksimovic <sreckojoksimovic at gmail.com> >>>>To: arun <smartpink111 at yahoo.com> >>>>Sent: Thursday, August 29, 2013 2:22 PM >>>> >>>>Subject: Re: [R] Add new calculated column to data frame >>>> >>>> >>>> >>>>Sorry... I should figure it out... >>>> >>>>thanks so much! >>>>Srecko >>>> >>>> >>>> >>>>On Thu, Aug 29, 2013 at 11:21 AM, arun <smartpink111 at yahoo.com> wrote: >>>> >>>>Hi, >>>>>The one you showed is: >>>>> >>>>>dat1$time_on_task<- c(diff(dat1$time),NA) >>>>> >>>>>?dat1 >>>>>#? id? event?????? time time_on_task >>>>>#1? 1??? add 1373502892?????????? 80 >>>>> >>>>>#2? 2??? add 1373502972?????????? 23 >>>>>#3? 3 delete 1373502995????????? 901 >>>>>#4? 4?? view 1373503896????????? 100 >>>>>#5? 5??? add 1373503996?????????? NA >>>>> >>>>> >>>>> >>>>> >>>>>________________________________ >>>>>From: srecko joksimovic <sreckojoksimovic at gmail.com> >>>>> >>>>>To: arun <smartpink111 at yahoo.com> >>>>>Cc: R help <r-help at r-project.org> >>>>>Sent: Thursday, August 29, 2013 2:15 PM >>>>>Subject: Re: [R] Add new calculated column to data frame >>>>> >>>>> >>>>> >>>>> >>>>>Thanks Arun, >>>>> >>>>>this is great. However, it should be just a little bit different: >>>>> >>>>>#? id? event?????? time time_on_task >>>>>#1? 1??? add 1373502892 ? ? ? ? ? 80 >>>>>#2? 2??? add 1373502972 ? ? ? ? ? 23 >>>>>#3? 3 delete 1373502995 ? ? ? ? ? 901 >>>>>#4? 4?? view 1373503896 ? ? ? ? ?100 >>>>>#5? 5??? add 1373503996 ? ? ? ? ?NA >>>>> >>>>> >>>>>When I calculate difference, I need to know how long each activity was. It is id2-id1 for the first activity... >>>>> >>>>> >>>>> >>>>>On Thu, Aug 29, 2013 at 11:03 AM, arun <smartpink111 at yahoo.com> wrote: >>>>> >>>>> >>>>>> >>>>>>Hi, >>>>>>Try: >>>>>>dat1<- read.table(text=" >>>>>>id??? event??? time >>>>>> >>>>>>1??? add????? 1373502892 >>>>>>2??? add????? 1373502972 >>>>>>3??? delete? 1373502995 >>>>>>4??? view????? 1373503896 >>>>>>5??? add????? 1373503996 >>>>>>",sep="",header=TRUE,stringsAsFactors=FALSE) >>>>>>?dat1$time_on_task<- c(NA,diff(dat1$time)) >>>>>>?dat1 >>>>>>#? id? event?????? time time_on_task >>>>>>#1? 1??? add 1373502892?????????? NA >>>>>>#2? 2??? add 1373502972?????????? 80 >>>>>>#3? 3 delete 1373502995?????????? 23 >>>>>>#4? 4?? view 1373503896????????? 901 >>>>>>#5? 5??? add 1373503996????????? 100 >>>>>> >>>>>>#Not sure whether this depends on the values of "event" or not.. >>>>>>A.K. >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>>----- Original Message ----- >>>>>>From: srecko joksimovic <sreckojoksimovic at gmail.com> >>>>>>To: R help <R-help at r-project.org> >>>>>>Cc: >>>>>>Sent: Thursday, August 29, 2013 1:52 PM >>>>>>Subject: [R] Add new calculated column to data frame >>>>>> >>>>>>Hi, >>>>>> >>>>>>I have a following data set: >>>>>>id? ? event? ? time (in sec) >>>>>>1? ? ?add? ? ? 1373502892 >>>>>>2? ? ?add? ? ? 1373502972 >>>>>>3? ? ?delete? ?1373502995 >>>>>>4? ? ?view? ? ? 1373503896 >>>>>>5? ? ?add? ? ? ?1373503996 >>>>>>... >>>>>> >>>>>>I'd like to add new column "time on task" which is time elapsed between two >>>>>>events (id2 - id1...). What would be the best approach to do that? >>>>>> >>>>>>Thanks, >>>>>>Srecko >>>>>> >>>>>>??? [[alternative HTML version deleted]] >>>>>> >>>>>>______________________________________________ >>>>>>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. >>>>>> >>>>>> >>>>> >>>> >>> >> >