R sorting using simple codes -
can me below problem?
dataset sample:
ticketid creation_date location person a1 01-02-2015 john b1 03-02-2015 b jack c1 03-02-2015 c mint a1 03-02-2015 d manu d1 03-02-2015 somu e1 03-02-2015 john b1 11-02-2015 b jack a1 11-02-2015 c mint b1 14-02-2015 f john b1 27-02-2015 e john problem:
1.remove duplicates of ticketid filtered in such way ->creationdate less 7 days of first occurence date. eg: ticket id,"a1", there 3 creation dates namely, "01-02-2015,03-02-2015,11-02-2015", wanted new column has "repeat flag" , flag first occurence in case being 01-02-2015 yes. because second occurence within 7 days of first incident.
2.by above logic,
->i want filter location(ticketid,creationdate) ->i want filter person(ticketid,creationdate) code:
t.first <- eg1[match(unique(eg1$ticketid), eg1$ticketid),]
how save output in same excel sheet , conditional formatting locations/engineers have more 1 incident?
code tried basic, requesting someone's asap.thanks in advance.
add-on clarify query better:
input:
ticketid creation_date location person partused deviceused
a1 01-02-2015 john monitor model1 b1 03-02-2015 b jack keyboard model2 c1 03-02-2015 c mint cable model3 a1 03-02-2015 d manu monitor model1 d1 03-02-2015 somu motherboard model2 e1 03-02-2015 john motherboard model2 b1 11-02-2015 b jack cable model2 a1 11-02-2015 c mint keyboard model3 b1 14-02-2015 f john motherboard model1 b1 27-02-2015 e john motherboard model3 i want output in below format table:
repeat flag has many conditions:for if condition if second time order created within 7 days of first order in case of a1.
the first creation date varies every ticket id.
answer1:**location repeatflag model1 model2 model3** answer2:**location person repeatflag model1 model2 model3** answer3:**location partsused repeatflag model1 model2 model3** note:this sample of dynamically changing varied rows. kindly share code tip satisfies kind of input expect automated process whereby report automatically mailed excel report once data gets refreshed in source(excel sheet).
kindly throw light how can automate well.
thanks support provided.much appreciated.
regards, vk
as understand question:
df <- data.frame(ticketid = c('a1','b1','c1','a1','d1','e1','b1','a1','b1','b1'), creation_date = as.date(c('01-02-2015','03-02-2015','03-02-2015','03-02-2015','03-02-2015','03-02-2015','11-02-2015','11-02-2015','14-02-2015','27-02-2015'), format = '%d-%m-%y'), location = c('a','b','c','d','a','a','b','c','f','e'), person = c('john','jack','mint','manu', 'somu','john', 'jack', 'mint','john','john') ) ticketid creation_date location person 1 a1 2015-02-01 john 2 b1 2015-02-03 b jack 3 c1 2015-02-03 c mint 4 a1 2015-02-03 d manu 5 d1 2015-02-03 somu 6 e1 2015-02-03 john 7 b1 2015-02-11 b jack 8 a1 2015-02-11 c mint 9 b1 2015-02-14 f john 10 b1 2015-02-27 e john library(dplyr) first_creation <- df %>% select(ticketid,first_date = creation_date) %>% group_by(ticketid) %>% slice(1) %>% ungroup() df2 <- merge(first_creation,df, all.y = t, = 'ticketid') df3 <- df2 %>% mutate(time_diff = creation_date - first_date) df_flagged <- df3 %>% group_by(ticketid) %>% mutate(within_7 = ifelse(time_diff > 7 | time_diff == 0, 'no','yes')) source: local data frame [10 x 7] groups: ticketid ticketid first_date creation_date location person time_diff within_7 1 a1 2015-02-01 2015-02-01 john 0 days no 2 a1 2015-02-01 2015-02-03 d manu 2 days yes 3 a1 2015-02-01 2015-02-11 c mint 10 days no 4 b1 2015-02-03 2015-02-03 b jack 0 days no 5 b1 2015-02-03 2015-02-11 b jack 8 days no 6 b1 2015-02-03 2015-02-14 f john 11 days no 7 b1 2015-02-03 2015-02-27 e john 24 days no 8 c1 2015-02-03 2015-02-03 c mint 0 days no 9 d1 2015-02-03 2015-02-03 somu 0 days no 10 e1 2015-02-03 2015-02-03 john 0 days no
Comments
Post a Comment