How to make complicated transformation of string in column based on value of other column?

Viewed 70

I have a dataframe:

ID          time             value       operation
K1   2020-10-12 07:35:47    K1-0735       create
K1   2020-10-12 07:35:49    K1-0735       upload
K1   2020-10-12 07:35:50    
K1   2020-10-12 07:35:55    K1-0735       create   
K1   2020-10-12 07:35:58    K1-0735       upload
K1   2020-10-12 07:37:19    
KK   2020-10-13 08:11:09    KK-0811       create
KK   2020-10-13 08:11:09    KK-0811       create
KK   2020-10-13 08:11:12    KK-0811       upload       
KK   2020-10-13 08:11:15
KK   2020-10-13 08:11:25    KK-0811       create
KK   2020-10-13 08:11:26    KK-0811       upload  

As you see column "value" is a join of value of column "ID" and hour and minute from "timestamp" without spaces. I want to add seconds to that value to make it unique within a minute. However they must have same seconds of they are in bundle of "operation" column of values create and upload.

So desired result is:

ID          time             value         operation
K1   2020-10-12 07:35:47    K1-073547       create
K1   2020-10-12 07:35:49    K1-073547       upload
K1   2020-10-12 07:35:50    
K1   2020-10-12 07:35:55    K1-073555       create   
K1   2020-10-12 07:35:58    K1-073555       upload
K1   2020-10-12 07:37:19    
KK   2020-10-13 08:11:09    KK-081109       create
KK   2020-10-13 08:11:09    KK-081109       create
KK   2020-10-13 08:11:12    KK-081109       upload       
KK   2020-10-13 08:11:15
KK   2020-10-13 08:11:25    KK-081125       create
KK   2020-10-13 08:11:26    KK-081125       upload  

How could i do that transformation with the type of condition?

Code to build dataframe:

ID <- c("K1","K1","K1","K1","K1","K1","KK","KK","KK","KK","KK","KK")

time <- as.Date(c('2020-10-12 07:35:47','2020-10-12 07:35:49','2020-10-12 07:35:50',
                     '2020-10-12 07:35:55','2020-10-12 07:35:58', '2020-10-12 07:37:19',
                     '2020-10-13 08:11:09','2020-10-13 08:11:09','2020-10-13 08:11:12',
                     '2020-10-13 08:11:15','2020-10-13 08:11:25','2020-10-13 08:11:26'))

value <- c("K1-0735","K1-0735",NA,"K1-0735","K1-0735",NA,"KK-0811","KK-0811","KK-0811",
           NA,"KK-0811","KK-0811")

operation <- c("create", "upload", NA,"create", "upload", NA,"create","create", "upload",
               NA,"create", "upload")
data <- data.frame(ID, time, value,operation)
3 Answers

We can do a lot of this by using the rleid function from data.table. I changed your data to be named dat instead of data.

library(tidyverse)
library(data.table)
library(lubridate)

dat %>%
  mutate(index = rleid(value)) %>%
  group_by(index) %>%
  mutate(min_time = min(time)) %>%
  mutate(value2 = paste0(ID, "-", 
                         str_pad(hour(min_time), 2, pad = 0),
                         str_pad(minute(min_time), 2, pad = 0),
                         str_pad(second(min_time), 2, pad = 0))) %>%
  mutate(value2 = ifelse(is.na(value), NA, value2)) %>% 
  ungroup() %>%
  select(ID, time, value = value2, operation)

   ID    time                value     operation
   <chr> <dttm>              <chr>     <chr>    
 1 K1    2020-10-12 07:35:47 K1-073547 create   
 2 K1    2020-10-12 07:35:49 K1-073547 upload   
 3 K1    2020-10-12 07:35:50 NA        NA       
 4 K1    2020-10-12 07:35:55 K1-073555 create   
 5 K1    2020-10-12 07:35:58 K1-073555 upload   
 6 K1    2020-10-12 07:37:19 NA        NA       
 7 KK    2020-10-13 08:11:09 KK-081109 create   
 8 KK    2020-10-13 08:11:09 KK-081109 create   
 9 KK    2020-10-13 08:11:12 KK-081109 upload   
10 KK    2020-10-13 08:11:15 NA        NA       
11 KK    2020-10-13 08:11:25 KK-081125 create   
12 KK    2020-10-13 08:11:26 KK-081125 upload   

There is also a solution which will work in the event that the minute is different for the same bundle (which is not covered in your sample data, but could happen). It uses the fact that bundles are separated by a row with NA

dat %>%
    mutate(row_sep = cumsum(is.na(value))) %>%
    group_by(row_sep) %>%
    mutate(min_time = min(as.POSIXct(ifelse(is.na(value), NA_POSIXct_, time),
                              origin = "1970-01-01 00:00.00 UTC"),
                       na.rm = TRUE)) %>%
    mutate(value2 = paste0(ID, "-", 
                           str_pad(hour(min_time), 2, pad = 0),
                           str_pad(minute(min_time), 2, pad = 0),
                           str_pad(second(min_time), 2, pad = 0))) %>%
    mutate(value2 = ifelse(is.na(value), NA, value2)) %>% 
    ungroup() %>%
    select(ID, time, value = value2, operation)

   ID    time                value     operation
   <chr> <dttm>              <chr>     <chr>    
 1 K1    2020-10-12 07:35:47 K1-073547 create   
 2 K1    2020-10-12 07:35:49 K1-073547 upload   
 3 K1    2020-10-12 07:35:50 NA        NA       
 4 K1    2020-10-12 07:35:55 K1-073555 create   
 5 K1    2020-10-12 07:35:58 K1-073555 upload   
 6 K1    2020-10-12 07:37:19 NA        NA       
 7 KK    2020-10-13 08:11:09 KK-081109 create   
 8 KK    2020-10-13 08:11:09 KK-081109 create   
 9 KK    2020-10-13 08:11:12 KK-081109 upload   
10 KK    2020-10-13 08:11:15 NA        NA       
11 KK    2020-10-13 08:11:25 KK-081125 create   
12 KK    2020-10-13 08:11:26 KK-081125 upload

Here's a somewhat cumbersome approach but I think it can lead you somewhere.

The strategy is to find start/stop points in the operation vector.

Here's your data, mind that I used lubridate::as_datetime() because you used as.Date() which removes the timestamp. Also, stringsAsFactors=FALSE

ID <- c("K1","K1","K1","K1","K1","K1","KK","KK","KK","KK","KK","KK")

time <- lubridate::as_datetime(c('2020-10-12 07:35:47','2020-10-12 07:35:49','2020-10-12 07:35:50',
                  '2020-10-12 07:35:55','2020-10-12 07:35:58', '2020-10-12 07:37:19',
                  '2020-10-13 08:11:09','2020-10-13 08:11:09','2020-10-13 08:11:12',
                  '2020-10-13 08:11:15','2020-10-13 08:11:25','2020-10-13 08:11:26'))

value <- c("K1-0735","K1-0735",NA,"K1-0735","K1-0735",NA,"KK-0811","KK-0811","KK-0811",
           NA,"KK-0811","KK-0811")

operation <- c("create", "upload", NA,"create", "upload", NA,"create","create", "upload",
               NA,"create", "upload")
df <- data.frame(ID, time, value,operation, stringsAsFactors = FALSE)

We are going to create the thing



library(dplyr)
library(stringr)

# try to find the positions
create_cases <- str_detect(df$operation, "create") %>% which()
upload_cases <- str_detect(df$operation, "upload") %>% which()

# check that they have different lengths and there are contiguous create cases

# check for contiguous create cases
contiguous <- which(diff(create_cases) == 1)
# remove the contiguous cases from the open vector
create_cases <- create_cases[-(contiguous+1)]

# so now we match lengths and we can create ranges in our data frame

# create column in df
df$bundle <- NA
for(i in seq_along(create_cases)){
  df$bundle[create_cases[i]:upload_cases[i]] <- paste0("bundle", i)
}

Now we can use grouping to select the seconds properly and paste them


df %>% 
  group_by(bundle) %>%
  # get the first of the seconds in the group
  mutate(seconds_to_paste = first(lubridate::second(time)),
         new_id = paste0(value, str_pad(seconds_to_paste, width = 2, pad=0)),
         # clean the NA pasted
         new_id = ifelse(str_detect(new_id, "NA"), NA, new_id))

Which produces (I left the extra columns to check everything matches the expected result, you can select() the ones you want later)

# A tibble: 12 x 7
# Groups:   bundle [5]
   ID    time                value   operation bundle  seconds_to_paste new_id   
   <chr> <dttm>              <chr>   <chr>     <chr>              <dbl> <chr>    
 1 K1    2020-10-12 07:35:47 K1-0735 create    bundle1               47 K1-073547
 2 K1    2020-10-12 07:35:49 K1-0735 upload    bundle1               47 K1-073547
 3 K1    2020-10-12 07:35:50 NA      NA        NA                    50 NA       
 4 K1    2020-10-12 07:35:55 K1-0735 create    bundle2               55 K1-073555
 5 K1    2020-10-12 07:35:58 K1-0735 upload    bundle2               55 K1-073555
 6 K1    2020-10-12 07:37:19 NA      NA        NA                    50 NA       
 7 KK    2020-10-13 08:11:09 KK-0811 create    bundle3                9 KK-081109
 8 KK    2020-10-13 08:11:09 KK-0811 create    bundle3                9 KK-081109
 9 KK    2020-10-13 08:11:12 KK-0811 upload    bundle3                9 KK-081109
10 KK    2020-10-13 08:11:15 NA      NA        NA                    50 NA       
11 KK    2020-10-13 08:11:25 KK-0811 create    bundle4               25 KK-081125
12 KK    2020-10-13 08:11:26 KK-0811 upload    bundle4               25 KK-081125

Here is another approach using pivot_wider and a self join:

ID <- c("K1","K1","K1","K1","K1","K1","KK","KK","KK","KK","KK","KK")

time <- as.POSIXct(c('2020-10-12 07:35:47','2020-10-12 07:35:49','2020-10-12 07:35:50',
                  '2020-10-12 07:35:55','2020-10-12 07:35:58', '2020-10-12 07:37:19',
                  '2020-10-13 08:11:09','2020-10-13 08:11:09','2020-10-13 08:11:12',
                  '2020-10-13 08:11:15','2020-10-13 08:11:25','2020-10-13 08:11:26'))

value <- c("K1-0735","K1-0735",NA,"K1-0735","K1-0735",NA,"KK-0811","KK-0811","KK-0811",
           NA,"KK-0811","KK-0811")

operation <- c("create", "upload", NA,"create", "upload", NA,"create","create", "upload",
               NA,"create", "upload")
data <- data.frame(ID, time, value,operation)

library(tidyverse)
library(lubridate)

data %>% 
  pivot_wider(id_cols = c(ID, value),
              names_from = operation,
              values_from = time) %>%
  unnest(create) %>%
  mutate(value2 = paste0(value, str_pad(second(create), 2, pad = 0))) %>% 
  select(ID, value, create, value2) %>% 
  distinct() %>% 
  left_join(data, ., by = c("ID", "value", "time" = "create")) %>% 
  mutate(value = case_when(
    !is.na(value2) & !is.na(value) ~ value2,
    !is.na(lag(value2)) & !is.na(value) ~ lag(value2))
    ) %>%
  select(-value2)

#>    ID                time     value operation
#> 1  K1 2020-10-12 07:35:47 K1-073547    create
#> 2  K1 2020-10-12 07:35:49 K1-073547    upload
#> 3  K1 2020-10-12 07:35:50      <NA>      <NA>
#> 4  K1 2020-10-12 07:35:55 K1-073555    create
#> 5  K1 2020-10-12 07:35:58 K1-073555    upload
#> 6  K1 2020-10-12 07:37:19      <NA>      <NA>
#> 7  KK 2020-10-13 08:11:09 KK-081109    create
#> 8  KK 2020-10-13 08:11:09 KK-081109    create
#> 9  KK 2020-10-13 08:11:12 KK-081109    upload
#> 10 KK 2020-10-13 08:11:15      <NA>      <NA>
#> 11 KK 2020-10-13 08:11:25 KK-081125    create
#> 12 KK 2020-10-13 08:11:26 KK-081125    upload

Created on 2021-01-14 by the reprex package (v0.3.0)

Related