CODEWITHSUNDEEP
databaserreshape
SarahDew
SarahDew

Reputation: 444

Complex data transformation

I need to transform following (simplified) dataset, created by following code:

structure(list(W1.1 = structure(c(1L, NA, NA), .Names = c("case1", 
"case2", "case3"), .Label = "1", class = "factor"), R1.1 = structure(c(1L, 
NA, NA), .Names = c("case1", "case2", "case3"), .Label = "2", class = "factor"), 
    W1.2 = structure(c(NA, 1L, NA), .Names = c("case1", "case2", 
    "case3"), .Label = "1", class = "factor"), R1.2 = structure(c(NA, 
    1L, NA), .Names = c("case1", "case2", "case3"), .Label = "1", class = "factor"), 
    W2.1 = structure(c(NA, 1L, NA), .Names = c("case1", "case2", 
    "case3"), .Label = "1", class = "factor"), R2.1 = structure(c(NA, 
    1L, NA), .Names = c("case1", "case2", "case3"), .Label = "1", class = "factor"), 
    W2.2 = structure(c(1L, NA, NA), .Names = c("case1", "case2", 
    "case3"), .Label = "2", class = "factor"), R2.2 = structure(c(1L, 
    NA, NA), .Names = c("case1", "case2", "case3"), .Label = "1", class = "factor"), 
    W3.1 = structure(c(1L, NA, NA), .Names = c("case1", "case2", 
    "case3"), .Label = "1", class = "factor"), R3.1 = structure(c(1L, 
    NA, NA), .Names = c("case1", "case2", "case3"), .Label = "1", class = "factor"), 
    W3.2 = structure(c(1L, 1L, NA), .Names = c("case1", "case2", 
    "case3"), .Label = "1", class = "factor"), R3.2 = structure(c(1L, 
    1L, NA), .Names = c("case1", "case2", "case3"), .Label = "1", class = "factor"), 
    age = structure(c(3L, 1L, 2L), .Names = c("case1", "case2", 
    "case3"), .Label = c("20", "48", "56"), class = "factor"), 
    gender = structure(c(2L, 1L, 2L), .Names = c("case1", "case2", 
    "case3"), .Label = c("female", "male"), class = "factor")), .Names = c("W1.1", 
"R1.1", "W1.2", "R1.2", "W2.1", "R2.1", "W2.2", "R2.2", "W3.1", 
"R3.1", "W3.2", "R3.2", "age", "gender"), row.names = c(NA, 3L
), class = "data.frame")

For the new data I want: - a row dedicated to every x.x, with info on the Rx.x value, age and gender. - only have a row returned when Wx.x was 1. When 2 or NA, I don't need it.

For my example this dataset should look something like this:

  incident type Where Reported age gender
1        1  1.1     1        2  56   male
2        2  3.1     1        1  56   male
3        3  3.2     1        1  56   male
4        4  1.2     1        1  20 female
5        5  2.1     1        1  20 female
6        6  3.2     1        1  20 female

Note: the "Where" column can even be omitted since it should be a constant vector of 1, and I don't need it for the analysis.

Upvotes: 2

Views: 111

Answers (1)

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193527

This is (mostly) a problem to be tackled by reshape(). Assuming your original dataset is called "temp":

First, reshape it from a wide format to a long format.

temp.long <- reshape(temp, direction = "long",
                     idvar=c("age", "gender"), 
                     varying = which(!names(temp) %in% c("age", "gender")), 
                     sep = "")
temp.long
#               age gender time    W    R
# 56.male.1.1    56   male  1.1    1    2
# 20.female.1.1  20 female  1.1 <NA> <NA>
# 48.male.1.1    48   male  1.1 <NA> <NA>
# 56.male.1.2    56   male  1.2 <NA> <NA>
# 20.female.1.2  20 female  1.2    1    1
# 48.male.1.2    48   male  1.2 <NA> <NA>
# 56.male.2.1    56   male  2.1 <NA> <NA>
# 20.female.2.1  20 female  2.1    1    1
# 48.male.2.1    48   male  2.1 <NA> <NA>
# 56.male.2.2    56   male  2.2    2    1
# 20.female.2.2  20 female  2.2 <NA> <NA>
# 48.male.2.2    48   male  2.2 <NA> <NA>
# 56.male.3.1    56   male  3.1    1    1
# 20.female.3.1  20 female  3.1 <NA> <NA>
# 48.male.3.1    48   male  3.1 <NA> <NA>
# 56.male.3.2    56   male  3.2    1    1
# 20.female.3.2  20 female  3.2    1    1
# 48.male.3.2    48   male  3.2 <NA> <NA>

Second, do some cleanup.

temp.long <- na.omit(temp.long)
temp.long <- temp.long[-which(temp.long$W == 2), ]
temp.long <- temp.long[order(rev(temp.long$gender), temp.long$time), ]
rownames(temp.long) <- NULL
temp.long$incident <- seq(nrow(temp.long))
temp.long
#   age gender time W R incident
# 1  56   male  1.1 1 2        1
# 2  56   male  3.1 1 1        2
# 3  56   male  3.2 1 1        3
# 4  20 female  1.2 1 1        4
# 5  20 female  2.1 1 1        5
# 6  20 female  3.2 1 1        6

You can do further cleanup to change your column names and column order if it's important.

Upvotes: 5

Related Questions