How to underestimate the values of hours under certain conditions in R

user_4529548 0
Viewed 39

I have dataset

wrong=structure(list(SAP = c("W609", "W609", "W609", "W609", "W609", 
"W609", "W609", "W609", "W609"), weekd = c(1L, 1L, 1L, 2L, 2L, 
2L, 3L, 3L, 3L), tab = c(1003L, 1004L, 1005L, 1001L, 1003L, 1004L, 
1002L, 1003L, 1005L), date = c(20220404L, 20220404L, 20220404L, 
20220405L, 20220405L, 20220405L, 20220406L, 20220406L, 20220406L
), kod = c(690L, 690L, 690L, 801L, 690L, 690L, 690L, 690L, 690L
), timeStart = c(12L, 12L, 10L, 10L, 12L, 12L, 10L, 12L, 12L), 
    timeEnd = c(22L, 22L, 20L, 20L, 22L, 22L, 20L, 22L, 22L)), class = "data.frame", row.names = c(NA, 
-9L))

How to do that if on a particular SAP on a certain day of the week work strictly 3 people no more and no less (tab column) and some tabs have same timeStart and timeEnd, for example, two tabs = 1003 and tab= 1004 the same time work from 12-22, so i need to change for any one tab randomly 1 hour less, for example 11-21.

Well, for example, manually, I did it like this desired output

   SAP weekd  tab     date kod timeStart timeEnd
1 W609     1 1003 20220404 690        12      22
2 W609     1 1004 20220404 690        **11      21**
3 W609     1 1005 20220404 690        10      20
4 W609     2 1001 20220405 801        10      20
5 W609     2 1003 20220405 690        **11      21**
6 W609     2 1004 20220405 690        12      22
7 W609     3 1002 20220406 690        10      20
8 W609     3 1003 20220406 690        **11      21**
9 W609     3 1005 20220406 690        12      22

In other words, I randomly chose a one tab with the same timeStart and timeEnd and lowered them by one hour.

Accordingly, if we had three tabs with the same timestart and timeend, well, for example, 12-22, then one would have 10-20, the second had 11-21, the third had 12-22.

How can I make these transformations correctly? Thank you for your help.

dplyr r
1 Answers

You can create indexes for each tab that have duplicated timeStart and timeEnd. After having those indexes, by filtering those indexes it could be iterated to subtract 1 for each timeStart and timeEnd except the first row for the filtered index.

I have just simplified your sample data since the only required columns are tab, timeStart and timeEnd. I have also enriched your data by adding some different rows to be able to test the different scenarios.

  wrong=structure(list(tab = c(1003L, 1004L, 1005L, 1001L, 1003L, 1004L,1002L, 1003L, 1005L,1003L,1003L,1003L,1005L), 
                     timeStart = c(12L, 12L, 10L, 10L, 12L, 12L, 10L, 12L, 12L,11L,10L,11L,10L),
                     timeEnd = c(22L, 22L, 20L, 20L, 22L, 22L, 20L, 22L, 22L,21L,20L,21L,20L)), 
                class = "data.frame", 
                row.names = c(NA,-13L))

I have just created a function that has data and tab as its arguments.

schedule_editer = function(data, tab_n) {

  wrong_f = data %>%
    filter(tab == tab_n) %>%
    mutate(Time = str_c(timeStart, timeEnd, sep = "_")) %>%
    arrange(Time) %>%
    mutate(Index = cumsum(!duplicated(Time)))
  
  listing = list()
  
  for (i in min(wrong_f$Index):max(wrong_f$Index)) {

    if (nrow(wrong_f %>% filter(Index == i)) == 1) {

      listing[[i]] = wrong_f %>% filter(Index == i)
      
    } else {

      wrong_f_i = wrong_f %>% filter(Index == i)
      wrong_f_i[-1, "timeStart"] = wrong_f_i[-1, "timeStart"] - c(1:nrow(wrong_f_i[-1, ]))
      wrong_f_i[-1, "timeEnd"] = wrong_f_i[-1, "timeEnd"] - c(1:nrow(wrong_f_i[-1, ]))
      listing[[i]] = wrong_f_i %>% mutate(Time = str_c(timeStart, timeEnd, sep = "_"))

    }
    
  }
  
  do.call(rbind, listing)
  
}

First of all, let's filter the tab for 1003 and have a look to the data that should be configured.

wrong %>%
  filter(tab == 1003)

   tab timeStart timeEnd
1 1003        12      22
2 1003        12      22
3 1003        12      22
4 1003        11      21
5 1003        10      20
6 1003        11      21

Now let's try the created function to be sure if it makes the thing that we want.

schedule_editer(wrong, 1003)

   tab timeStart timeEnd  Time Index
1 1003        10      20 10_20     1
2 1003        11      21 11_21     2
3 1003        10      20 10_20     2
4 1003        12      22 12_22     3
5 1003        11      21 11_21     3
6 1003        10      20 10_20     3
user_87511360
Related