Convert multi-stage event data to longitudinal data in r

Question

I have a data set on job promotions. For each person, I have the exact data on when they were hired by the company, promoted, and left the company. I also have a few characteristics. I am using r.

SubjectID Entry     Stage1    Stage2    Stage3    Stage4   Exit     Race  Edu
   1      1/12/1990 1/12/1990 1/12/1990  4/3/1994          5/5/1994 B     M
   2      1/17/1991 1/17/1991 3/3/1991   3/18/1992 1/1/1993         W     C 
   3      1/24/1991 1/24/1991 5/6/1994                              B     M

I would like to convert this data into a long longitudinal dataset where each date reports the stage an individual is on, while also reporting time invariant characteristics. I have an end date of 1/1/1995 after which no observations occur. I've looked into the reshape package but it doesn't have what I need.

My data:

structure(list(ï..Name = structure(c(2L, 1L, 4L, 3L), .Label = c("Ademulegun, Sauel Adesujo", "Bassey, Wellington Umo", "Imo, U. O.", "Lawan, Umar"), class = "factor"), Mons = c(0L, 0L, 0L, 0L), Sandhurst = c(0L, 0L, 1L, 0L), Entry = structure(c(2L, 3L, 1L, 4L), .Label = c("2/6/1953", "4/30/1949", "6/11/1949", "6/4/1955"), class = "factor"), Second.Lieutenant = structure(c(2L, 3L, 1L, 4L), .Label = c("2/6/1953", "4/30/1949", "6/11/1949", "6/4/1955"), class = "factor"), Lieutenant = structure(c(2L, 1L, 4L, 3L), .Label = c("12/20/1949", "4/30/1949", "5/3/1958", "8/1/1955"), class = "factor"), Captain = structure(c(2L, 3L, 1L, 4L), .Label = c("", "2/7/1951", "3/5/1952", "5/3/1958"), class = "factor"), Major = structure(c(4L, 3L, 1L, 2L), .Label = c("", "1/15/1963", "12/27/1958", "6/21/1957" ), class = "factor"), Lieutenant.Colonel = structure(c(4L, 3L, 1L, 2L), .Label = c("", "1/15/1963", "10/3/1962", "8/30/1962" ), class = "factor"), Colonel = structure(c(3L, 2L, 1L, 1L ), .Label = c("", "10/3/1962", "2/26/1966"), class = "factor"), Brigadier.General = structure(c(3L, 2L, 1L, 1L), .Label = c("", "10/3/1962", "2/26/1966"), class = "factor"), Depature = structure(c(2L, 1L, 3L, 4L), .Label = c("1/15/1966", "11/1/1966", "5/8/1956", "7/6/1967"), class = "factor"), ethnicity = structure(c(1L, 4L, 3L, 2L), .Label = c("Efik", "Igbo", "Kanuri", "Yoruba" ), class = "factor")), class = "data.frame", row.names = c(NA, -4L))

I'm looking for something like this:

Name  Date       Mons Sandhurst Ethnicity Rank
Bassey 4/30/1949  0     0       Efik      Lieutenant
Bassey 5/1/1949   0     0       Efik      Lieutenant
....
Bassey 2/7/1951   0     0       Efik      Captain

Please provide your sample data using dput, so we know what we are working with (dates, characters, etc...). Also, please provide your desired output of the sample data. — Wimpel

Wimpel Wimpel · Accepted Answer · 2018-10-05T06:57:44

data.table solution

library(data.table)

sample data

df <- structure(list(Name = structure(c(2L, 1L, 4L, 3L), .Label = c("Ademulegun, Sauel Adesujo", "Bassey, Wellington Umo", "Imo, U. O.", "Lawan, Umar"), class = "factor"), Mons = c(0L, 0L, 0L, 0L), Sandhurst = c(0L, 0L, 1L, 0L), Entry = structure(c(2L, 3L, 1L, 4L), .Label = c("2/6/1953", "4/30/1949", "6/11/1949", "6/4/1955"), class = "factor"), Second.Lieutenant = structure(c(2L, 3L, 1L, 4L), .Label = c("2/6/1953", "4/30/1949", "6/11/1949", "6/4/1955"), class = "factor"), Lieutenant = structure(c(2L, 1L, 4L, 3L), .Label = c("12/20/1949", "4/30/1949", "5/3/1958", "8/1/1955"), class = "factor"), Captain = structure(c(2L, 3L, 1L, 4L), .Label = c("", "2/7/1951", "3/5/1952", "5/3/1958"), class = "factor"), Major = structure(c(4L, 3L, 1L, 2L), .Label = c("", "1/15/1963", "12/27/1958", "6/21/1957" ), class = "factor"), Lieutenant.Colonel = structure(c(4L, 3L, 1L, 2L), .Label = c("", "1/15/1963", "10/3/1962", "8/30/1962" ), class = "factor"), Colonel = structure(c(3L, 2L, 1L, 1L ), .Label = c("", "10/3/1962", "2/26/1966"), class = "factor"), Brigadier.General = structure(c(3L, 2L, 1L, 1L), .Label = c("", "10/3/1962", "2/26/1966"), class = "factor"), Depature = structure(c(2L, 1L, 3L, 4L), .Label = c("1/15/1966", "11/1/1966", "5/8/1956", "7/6/1967"), class = "factor"), ethnicity = structure(c(1L, 4L, 3L, 2L), .Label = c("Efik", "Igbo", "Kanuri", "Yoruba" ), class = "factor")), class = "data.frame", row.names = c(NA, -4L))

reshape using data.tables fast melt

library( data.table )

data.table::melt( data = setDT( df ),
                  id.vars = c("Name", "Mons", "Sandhurst", "ethnicity" ),
                  value.name = "Date",
                  variable.name = "Rank",
                  na.rm = TRUE )

result

#                         Name Mons Sandhurst ethnicity               Rank       Date
# 1:    Bassey, Wellington Umo    0         0      Efik              Entry  4/30/1949
# 2: Ademulegun, Sauel Adesujo    0         0    Yoruba              Entry  6/11/1949
# 3:               Lawan, Umar    0         1    Kanuri              Entry   2/6/1953
# 4:                Imo, U. O.    0         0      Igbo              Entry   6/4/1955
# 5:    Bassey, Wellington Umo    0         0      Efik  Second.Lieutenant  4/30/1949
# 6: Ademulegun, Sauel Adesujo    0         0    Yoruba  Second.Lieutenant  6/11/1949
# 7:               Lawan, Umar    0         1    Kanuri  Second.Lieutenant   2/6/1953
# 8:                Imo, U. O.    0         0      Igbo  Second.Lieutenant   6/4/1955
# 9:    Bassey, Wellington Umo    0         0      Efik         Lieutenant  4/30/1949

reorder as needed...

note: If you want the 'empty' Dates to disappear in the molten data, make sure to punt NA's in the empty dates in the source data.. na.rm = TRUE in the melting function then removes them.

Convert multi-stage event data to longitudinal data in r

1 Answers