CODEWITHSUNDEEP
rmatrixdataframe
user2249626
user2249626

Reputation: 453

Matrix to data frame with row/columns numbers

I have a 10x10 matrix in R, called run_off. I would like to convert this matrix to a data frame that contains the entries of the matrix (the order doesn't really matter, although I'd prefer it to be filled by row) as well as the row and columns numbers of the entries as separate columns in the data frame, so that for instance element run_off[2,3] has a row in the data frame with 3 columns, the first containing the element itself, the second containing 2 and the third containing 3.

This is what I have so far:

run_off <- matrix(data = c(45630, 23350, 2924, 1798, 2007, 1204, 1298, 563, 777, 621,
                           53025, 26466, 2829, 1748, 732, 1424, 399, 537, 340, NA,
                           67318, 42333, -1854, 3178, 3045, 3281, 2909, 2613, NA, NA,
                           93489, 37473, 7431, 6648, 4207, 5762, 1890, NA, NA, NA,
                           80517, 33061, 6863, 4328, 4003, 2350, NA, NA, NA, NA,
                           68690, 33931, 5645, 6178, 3479, NA, NA, NA, NA, NA,
                           63091, 32198, 8938, 6879, NA, NA, NA, NA, NA, NA, 
                           64430, 32491, 8414, NA, NA, NA, NA, NA, NA, NA,
                           68548, 35366, NA, NA, NA, NA, NA, NA, NA, NA,
                           76013, NA, NA, NA, NA, NA, NA, NA, NA, NA)
                    , nrow = 10, ncol = 10, byrow = TRUE)

df <- data.frame()
for (i in 1:nrow(run_off)) {
  for (k in 1:ncol(run_off)) {
    claim <- run_off[i,k]
    acc_year <- i
    dev_year <- k
    df[???, "claims"] <- claim # Problem here
    df[???, "acc_year"] <- acc_year # and here
    df[???, "dev_year"] <- dev_year # and here


  }
}

dev_year refers to the column number of the matrix entry and acc_yearto the row number. My problem is that I don't know the proper index to use for the data frame.

Upvotes: 4

Views: 2312

Answers (1)

Simon O&#39;Hanlon
Simon O&#39;Hanlon

Reputation: 60000

I am assuming you are not interested in the NA elements? You can use which and the arr.ind = TRUE argument to return a two column matrix of array indices for each value and cbind this to the values, excluding the NA values:

#  Get array indices
ind <- which( ! is.na(run_off) , arr.ind = TRUE ) 

#  cbind indices to values
out <- cbind( run_off[ ! is.na( run_off ) ] , ind )

head( as.data.frame( out ) )
#     V1 row col
#1 45630   1   1
#2 53025   2   1
#3 67318   3   1
#4 93489   4   1
#5 80517   5   1
#6 68690   6   1

Use t() on the matrix first if you want to fill by row, e.g. which( ! is.na( t( run_off ) ) , arr.ind = TRUE ) (and when you cbind it).

Upvotes: 5

Related Questions