Split the rows in data frame based on timestamp using R

Question

I have the below-unstructured ticketing dataset with the work notes update. Each ticket has multiple work notes based on timestamps. I need to split the Work notes column with each row having the timestamp and its corresponding update similar to the one shown in Expected output

I.NO    Ticket No:               Worknotes                  
0         198822       2015-06-19 01:57:11 -Account Service
1         198822       Event closed
2         198822     Acknowledged 
3         198822     2015-06-19 01:58:33- Lawrence David 
4         198822     Data unavialable and hence ticket closed     
5         198824     2015-06-19 02:07:01- Account Service
6         198824     User requested for database information   
7         198824     2015-06-19 02:07:34- Cecilia Trandau 
8         198824     Backup in progress. Under discusion 
9         198824     2015-06-20 02:07:01- Account Service
10        198824     Auto closed 

########## Edited    **Output of dput**

structure(list(I.NO = c(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10), `Ticket No:` = c(198822, 
198822, 198822, 198822, 198822, 198824, 198824, 198824, 198824, 
198824, 198824), Worknotes = c("2015-06-19 01:57:11 -Account Service", 
"Event closed", "Acknowledged", "2015-06-19 01:58:33- Lawrence David", 
"Data unavialable and hence ticket closed", "2015-06-19 02:07:01- Account Service", 
"User requested for database information", "2015-06-19 02:07:34- Cecilia Trandau", 
"Backup in progress. Under discusion", "2015-06-20 02:07:01- Account Service", 
"Auto closed")), row.names = c(NA, -11L), class = c("tbl_df", 
"tbl", "data.frame"))
# A tibble: 6 x 3
   I.NO `Ticket No:` Worknotes                               
  <dbl>        <dbl> <chr>                                   
1     0       198822 2015-06-19 01:57:11 -Account Service    
2     1       198822 Event closed                            
3     2       198822 Acknowledged                            
4     3       198822 2015-06-19 01:58:33- Lawrence David     
5     4       198822 Data unavialable and hence ticket closed
6     5       198824 2015-06-19 02:07:01- Account Service  

###########################

**Expected Output**

   **Ticket No:**       **Worknotes**                  
    198822     2015-06-19 01:57:11 -Account Service
                      Event closed
                      Acknowledge
    198822     2015-06-19 01:58:33- Lawrence David 
               Data unavailable and hence ticket closed 
    198824     2015-06-19 02:07:01- Account Service
               User requested for database information
    198824     2015-06-19 02:07:34- Cecilia Trandau 
               Backup in progress. Under discusion 

    198824     2015-06-20 02:07:01- Account Service
               Auto closed

Could you explain the data structure of your expected output? Is there supposed to be a vector in the worknote column for each ticket number that can hold multiple values? A list? Or are there three rows in a table for ticket 19822 with two of the rows simply not having an entry in the Ticket No column? — Mario Niepel, Jan 08 '21 at 17:10
Hi Akshi, please do not post essentially [the same question](https://stackoverflow.com/questions/65624418/in-r-how-to-split-a-column-based-on-datetime) twice. As I commented last time, it is unclear how your data is formatted in R, so it is *extremely* challenging for us to help . Please [edit] your question with the output of `head(dput(data))` replacing `data` with the name of your data object. — Ian Campbell, Jan 08 '21 at 17:19

Ian Campbell · Accepted Answer · 2021-01-08T18:49:01.977

Here is an approach with grouping on cumsum and str_detect:

library(tidyverse)
data %>%
  mutate(grouper = cumsum(str_detect(Worknotes,"^[0-9\\-]{10}"))) 
# A tibble: 11 x 4
    I.NO `Ticket No:` Worknotes                                grouper
   <dbl>        <dbl> <chr>                                      <int>
 1     0       198822 2015-06-19 01:57:11 -Account Service           1
 2     1       198822 Event closed                                   1
 3     2       198822 Acknowledged                                   1
 4     3       198822 2015-06-19 01:58:33- Lawrence David            2
 5     4       198822 Data unavialable and hence ticket closed       2
 6     5       198824 2015-06-19 02:07:01- Account Service           3
 7     6       198824 User requested for database information        3
 8     7       198824 2015-06-19 02:07:34- Cecilia Trandau           4
 9     8       198824 Backup in progress. Under discusion            4
10     9       198824 2015-06-20 02:07:01- Account Service           5
11    10       198824 Auto closed                                    5

From here, we can group_by, summarise and paste:

data %>%
    mutate(grouper = cumsum(str_detect(Worknotes,"^[0-9\\-]{10}"))) %>%
    group_by(`Ticket No:`, grouper) %>%
    summarise(Worknotes = paste(Worknotes, collapse = "\n")) %>%
    select(-grouper) -> result
result
  `Ticket No:` Worknotes                                                                      
         <dbl> <chr>                                                                          
1       198822 "2015-06-19 01:57:11 -Account Service\nEvent closed\nAcknowledged"             
2       198822 "2015-06-19 01:58:33- Lawrence David\nData unavialable and hence ticket closed"
3       198824 "2015-06-19 02:07:01- Account Service\nUser requested for database information"
4       198824 "2015-06-19 02:07:34- Cecilia Trandau\nBackup in progress. Under discusion"    
5       198824 "2015-06-20 02:07:01- Account Service\nAuto closed"

Note that \n does not parse with print() in R, but it does parse with cat():

cat(as.matrix(result[1,2]))
2015-06-19 01:57:11 -Account Service
Event closed
Acknowledged

Split the rows in data frame based on timestamp using R

1 Answers1