Transform table into another table (data in rows into columns)

Question

I am relatively new to R. I made some analyses on the observer XT software (software for video coding of behaviors). For other analyses, I have to transform the exported text file (i.e. the table) in another form.

The table extracted from the observer XT looks like this :

Datetime	Behavior	Event Type
30-07-2020 23:12:23	browsing	State start
30-07-2020 23:14:30	browsing	State stop
30-07-2020 23:16:04	browsing	State start
30-07-2020 23:16:05	browsing	State stop

Here you can see that each row shows the beginning or the end of a behavior.

My goal is to take two by two each line and create a begin / stop line like this.

Start_Datetime	Stop_Datetime	Behavior
30-07-2020 23:12:23	30-07-2020 23:14:30	browsing
30-07-2020 23:16:04	30-07-2020 23:17:05	browsing

Thanks in advance.

score 2 · Answer 1 · answered Mar 14 '21 at 10:16

Create a group of every 2 rows and cast the data to wide format.

library(dplyr)
library(tidyr)

df %>%
  group_by(row = ceiling(row_number()/2)) %>%
  pivot_wider(names_from = Event.Type  , values_from = Datetime) %>%
  ungroup %>%
  select(-row) -> result

result

#  Behavior `State start`       `State stop`       
#  <chr>    <chr>               <chr>              
#1 browsing 30-07-2020 23:12:23 30-07-2020 23:14:30
#2 browsing 30-07-2020 23:16:04 30-07-2020 23:16:05

data

df <- structure(list(Datetime = c("30-07-2020 23:12:23", "30-07-2020 23:14:30", 
"30-07-2020 23:16:04", "30-07-2020 23:16:05"), Behavior = c("browsing", 
"browsing", "browsing", "browsing"), Event.Type = c("State start", 
"State stop", "State start", "State stop")), row.names = c(NA, 
-4L), class = "data.frame")

score 1 · Answer 2 · answered Mar 14 '21 at 11:48

A data.table option using dcast (thank @Ronak Shah for data)

dcast(
  setDT(df),
  Behavior + rowid(Event.Type) ~ Event.Type,
  value.var = "Datetime"
)[
  , Event.Type := NULL
][]

which gives

   Behavior         State start          State stop
1: browsing 30-07-2020 23:12:23 30-07-2020 23:14:30
2: browsing 30-07-2020 23:16:04 30-07-2020 23:16:05

Transform table into another table (data in rows into columns)

2 Answers2