0

Need help . (pls direct to other solution if already answered, couldn't find a relevant one)

In a work schedule of employees like :

  EMP_COUNT     TYPE startdate   enddate
1         5  REGULAR  1/1/2014 3/15/2014
2         2 OVERTIME  1/7/2014 1/10/2014
3         5 MONTHEND 3/16/2014 3/31/2014
4         2 OVERTIME 3/30/2014 3/31/2014

I want to create another dataframe from above aggregated EMP count , constraints are ;

Total rows to be created for each schedule are to match EMP Count. A unique ID need to be created for each start date and end date duration arching 1 or more schedules, say 1 to 5. Then if any other schedule date is within its range unique ids for those would be 6 and 7 (for second row in Work Schedule). If any other mutually exclusive schedule is present , ID creation would start from 1 again (work schedule row3 type month end ) ID 1 to 5 again in this case.. If same also covers any other date range of schedule, the IDs would be incremental 6 and 7 for last row. The final output would be like this dataframe:

   ID     TYPE startdate   enddate
1   1  REGULAR  1/1/2014 3/15/2014
2   2  REGULAR  1/1/2014 3/15/2014
3   3  REGULAR  1/1/2014 3/15/2014
4   4  REGULAR  1/1/2014 3/15/2014
5   5  REGULAR  1/1/2014 3/15/2014
6   6 OVERTIME  1/7/2014 1/10/2014
7   7 OVERTIME  1/7/2014 1/10/2014
8   1 MONTHEND 3/16/2014 3/22/2014
9   2 MONTHEND 3/16/2014 3/22/2014
10  3 MONTHEND 3/16/2014 3/22/2014
11  4 MONTHEND 3/16/2014 3/22/2014
12  5 MONTHEND 3/16/2014 3/22/2014
13  6 OVERTIME 3/30/2014 3/31/2014
14  7 OVERTIME 3/30/2014 3/31/2014
Moksha
  • 11
  • 1
  • 3
  • This question will get you started: http://stackoverflow.com/questions/25633942/r-creating-rows-based-upon-counter-value/25634060 , as in `dat[rep(rownames(dat),dat$EMP_COUNT),]` – thelatemail Sep 05 '14 at 04:38
  • You'll also need `sequence` to help get the correct "ID" values. – A5C1D2H2I1M1N2O1R2T1 Sep 05 '14 at 04:46
  • @AnandaMahto - is this question not slightly different in that the `ID` numbering starts and stops at differing places dependent on date overlaps? – thelatemail Sep 05 '14 at 04:54
  • @thelatemail, I was thinking about the same thing. I keep forgetting about the immediacy of dupe closing and was still expecting a "possible duplicate of" comment to appear. – A5C1D2H2I1M1N2O1R2T1 Sep 05 '14 at 04:55
  • @AnandaMahto - you underestimate your own power! ;-) – thelatemail Sep 05 '14 at 04:58
  • Is your data always structured this regularly? REGULAR followed by OVERTIME followed by MONTHEND followed by OVERTIME? – A5C1D2H2I1M1N2O1R2T1 Sep 05 '14 at 06:06
  • @anandaMahto - we can define that as an input order. in general it may be just regular and month end, overtime may / may not be needed. – Moksha Sep 05 '14 at 06:33
  • Is there a way we can take cumulative sum of EMP Count when dates spanning regular start and regular end. To loop in any overtime rows to be created. Similarly, for MONTHEND cumulative sum of EMPCOUNT within start date and end date. – Moksha Sep 05 '14 at 06:34
  • U see the REGULAR period schedule and MONTHEND are always exclusive in dates . any OVERTIME emp to be added would fall in either of the 2 date ranges. – Moksha Sep 05 '14 at 06:41
  • @thelatemail - any clue on getting ID generated as per date constraints. – Moksha Sep 05 '14 at 16:51

0 Answers0