Geek asks and answers

Condensation of several observations of the same person in one row, adding multiple as new columns

I have data that looks like this:

ID  X1    X2    X3

1   1.4   2     two
2   7.6   30    thirty
2   7.6   50    fifty
2   7.6   40    forty
3   5.6   40    forty
3   5.6   50    fifty
4   3.5   NA    NA
5   NA    2     two

The identifier indicates individuals, X1 is a measurement taken once for each person, and X2 and X3 are symbolic and string representations of the same treatment. For example, for individual 2, X1 = 7.6 and processed X2 = 30, 50, and 40 (which is related to other information, X3 = thirty, fifty, and forty)

I want it to look like this: only one row per individual user and values ​​from several observations of the same person added to new columns:

ID   X1     X2a   X3a       X2b   X3b     X2c  X3c
1    1.4    2     two       NA    NA      NA   NA
2    7.6    30    thirty    50    fifty   40   forty
3    5.6    40    forty     50    fifty   NA   NA
4    3.5    NA    NA        NA    NA      NA   NA
5    NA     2     two       NA    NA      NA   NA

X1 = NA (, 5), , . (10 000 , , 50 ), , , .

melt() cast() reshape, , , , . , (: fun.aggregate: length ), . , ? ?

(, ), , , , , ...

+3
2

:

library(data.table)
dt = data.table(your_df)

# get number of columns first (6 here)
max.N = max(dt[, .N*ncol(.SD), by = list(ID, X1)]$V1)

# now construct the result by filling in appropriate # of NA's
dt[, as.list(c(t(.SD), rep(NA, max.N - .N*ncol(.SD)))), by = list(ID, X1)]
#   ID  X1 V1     V2 V3    V4 V5    V6
#1:  1 1.4  2    two NA    NA NA    NA
#2:  2 7.6 30 thirty 50 fifty 40 forty
#3:  3 5.6 40  forty 50 fifty NA    NA
#4:  4 3.5 NA     NA NA    NA NA    NA
#5:  5  NA  2    two NA    NA NA    NA
+4

1.9.6 ( CRAN 2015-09-19), data.table dcast() value.var.

dcast(DT, ID + X1 ~ rowid(ID), value.var = c("X2", "X3"))

   ID  X1 X2_1 X2_2 X2_3   X3_1  X3_2  X3_3
1:  1 1.4    2   NA   NA    two    NA    NA
2:  2 7.6   30   50   40 thirty fifty forty
3:  3 5.6   40   50   NA  forty fifty    NA
4:  4 3.5   NA   NA   NA     NA    NA    NA
5:  5  NA    2   NA   NA    two    NA    NA


library(data.table)
DT <- fread(
"ID  X1    X2    X3
  1   1.4   2     two
  2   7.6   30    thirty
  2   7.6   50    fifty
  2   7.6   40    forty
  3   5.6   40    forty
  3   5.6   50    fifty
  4   3.5   NA    NA
  5   NA    2     two"
)
+1

More articles:


All Articles