user9292 - 3 years ago 44
R Question

# Reshaping data in R with "from" and "to"

A data frame contains three variables: ID, state (factor), and t (integer). There might be multiple observations for each ID. See below

``````id <- c(1,1,1,1,1,2,2,2,2,3,4,4,4,5,5)
state <- c("a", "b", "c", "d", "e", "a", "a", "c", "e", "b", "e", "a", "c", "b", "a")
t <- c(1,2,3,4,5, 1,2,3,4, 1, 1,2,3, 1,2)

dat <- data.frame(id, state, t)
``````

I want to create a new data frame that contains two variables: "from" and "to". For each ID, I want to see how the State is changing:
For example, for ID the state moved from a to b, then b to c, then c to d, and finally d to e. If there is one record for any ID, then
ignore (ID=3, for instance).

``````# The final data set looks like:
from <- c("a", "b", "c", "d", "a", "a", "c", "e", "a", "b")
to <- c("b", "c", "d", "e", "a", "c", "e", "a", "c", "a")

dat2 <- data.frame(from, to)
``````

We can try with `data.table`. Convert the 'data.frame' to 'data.table' (`setDT(dat)`), grouped by 'id', remove the last observation from 'state' to create 'from' and first observation to create the 'to'

``````library(data.table)
setDT(dat)[, .(from = state[-.N], to = state[-1]), id][, id := NULL][]
#     from to
# 1:    a  b
# 2:    b  c
# 3:    c  d
# 4:    d  e
# 5:    a  a
# 6:    a  c
# 7:    c  e
# 8:    e  a
# 9:    a  c
#10:    b  a
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download