1

I have a table of transactional data, with item numbers in each cell. For each non-missing entry, I would like to output an (ID, ITEM) pair, where ID is the row number and ITEM is the data. For instance, consider the following input table:

(dat <- matrix(c(25, 39, 35, 39, 15, 26, 7, 227, 52, 120, 249, 422, 229, 104, 185, 390, 164, 124, 674, 449, 262, 143, 214, NA, 240, 205, 712, 704, 283, 320, 350, NA, 274, 401, 733, 825, 294, 569, 529, NA, 328, 581, 759, 857, 352, 620, 658, NA, 368, 704, 854, 895, 381, 798, 682, NA, 448, 814, 950, 937, 708, NA, 782, NA, 538, 825, NA, 954, 738, NA, 809, NA, 561, 834, NA, 964, 766, NA, 849, NA), nrow=8))
#      [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10]
# [1,]   25   52  164  240  274  328  368  448  538   561
# [2,]   39  120  124  205  401  581  704  814  825   834
# [3,]   35  249  674  712  733  759  854  950   NA    NA
# [4,]   39  422  449  704  825  857  895  937  954   964
# [5,]   15  229  262  283  294  352  381  708  738   766
# [6,]   26  104  143  320  569  620  798   NA   NA    NA
# [7,]    7  185  214  350  529  658  682  782  809   849
# [8,]  227  390   NA   NA   NA   NA   NA   NA   NA    NA

The eighth row would be converted to the following:

data.frame(ID=c(8, 8), ITEM=c(227, 390))
#   ID ITEM
# 1  8  227
# 2  8  390

I would like each row to be converted in this way.

josliber
  • 43,891
  • 12
  • 98
  • 133
Mahesh
  • 21
  • 1
  • Do not post a picture of your data. Please read http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example, and http://stackoverflow.com/help/mcve – pcantalupo Oct 14 '15 at 20:02

1 Answers1

0

You could generate a table of all pairs (including the pairs with missing item numbers) and then use complete.cases to limit to just the pairs with non-missing data:

out <- data.frame(ID=c(row(dat)), ITEM=c(dat))
out <- out[complete.cases(out),]
head(out)
#   ID ITEM
# 1  1   25
# 2  2   39
# 3  3   35
# 4  4   39
# 5  5   15
# 6  6   26

c(row(dat)) returns a vector indicating the row number of each piece of data in your input matrix, while c(dat) is a vector with the matrix data.

josliber
  • 43,891
  • 12
  • 98
  • 133