converting column values to column names and keeping all values without aggregating, Id has duplicate values

Question

Time | StartSTop | Id

2020-04-01 03:46:03 | START | LB1

2020-04-01 04:27:30 | STOP | LB1

2020-04-01 10:07:08 | START | LB1

2020-04-01 10:40:10 | STOP | LB1

2020-04-02 17:01:23 | START | LB2

2020-04-02 17:40:50 | STOP | LB2

2020-04-03 17:04:56 | START | LB3

2020-04-03 17:38:47 | STOP | LB3

2020-04-04 18:35:34 | START | LB4

2020-04-04 19:11:05 | STOP | LB4

After Pivot Expected Data frame using StartStop as column names not aggregating batch ids

Expected Data Frame:

Id | Start | Stop

LB1 |2020-04-01 03:46:03 | 2020-04-01 04:27:30

LB2 |2020-04-01 10:07:08 | 2020-04-01 10:40:10

LB3 |2020-04-02 17:01:23 | 2020-04-02 17:40:50

resultant id should have duplicate values.

dont close the question i didnt find answers in other section — nick r, Sep 14 '20 at 18:46

akrun · Accepted Answer · 2020-09-14T19:00:53.777

We can create a sequence column first

library(dplyr)
library(data.table)
df1 %>%
  mutate(rn = rowid(Id, StartSTop)) %>%
  pivot_wider(names_from = StartSTop, values_from = Time) %>%
  select(-rn)
# A tibble: 5 x 3
#  Id        START               STOP               
#  <chr>     <chr>               <chr>              
#1 LB053272A 2020-04-01 03:46:03 2020-04-01 04:27:30
#2 LB053272A 2020-04-01 10:07:08 2020-04-01 10:40:10
#3 LB053314A 2020-04-02 17:01:23 2020-04-02 17:40:50
#4 LB053330A 2020-04-03 17:04:56 2020-04-03 17:38:47
#5 LB053355A 2020-04-04 18:35:34 2020-04-04 19:11:05

data

df1 <- structure(list(Time = c("2020-04-01 03:46:03", "2020-04-01 04:27:30", 
"2020-04-01 10:07:08", "2020-04-01 10:40:10", "2020-04-02 17:01:23", 
"2020-04-02 17:40:50", "2020-04-03 17:04:56", "2020-04-03 17:38:47", 
"2020-04-04 18:35:34", "2020-04-04 19:11:05"), StartSTop = c("START", 
"STOP", "START", "STOP", "START", "STOP", "START", "STOP", "START", 
"STOP"), Id = c("LB053272A", "LB053272A", "LB053272A", "LB053272A", 
"LB053314A", "LB053314A", "LB053330A", "LB053330A", "LB053355A", 
"LB053355A")), class = "data.frame", row.names = c(NA, -10L))

using above code, i got reslut like this LB053272A 2020-04-01 03:46:03 NA LB053272A NA 2020-04-01 04:27:30 LB053272A 2020-04-01 10:07:08 NA LB053272A NA 2020-04-01 10:40:10 — nick r, Sep 14 '20 at 18:53

converting column values to column names and keeping all values without aggregating, Id has duplicate values

1 Answers1

data