user9292 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)

Answer Source

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'

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