splitting matrix of strings into numeric matrix in R

user_12222518 0
Viewed 35

I have a (large) matrix of strings of two numbers. I want to convert the matrix to a numeric matrix where the strings are separated into different columns while preserving the row order and string order of the original matrix. I am doing this in R, in which I am mostly a novice/self-taught. Example input and desired output below

input
test <- matrix[1:5,1:5]
test
     col1    col2   col3   col4   col5
row1 "0,0"   "0,0"  "0,0"  "0,0"  "0,1"            
row2 "0,0"   "0,0"  "0,0"  "0,0"  "0,0"            
row3 "0,0"   "0,0"  "0,2"  "0,0"  "0,0"            
row4 "0,0"   "0,0"  "0,2"  "0,0"  "0,0"            
row5 "0,0"   "0,0"  "0,0"  "0,0"  "1,0"    

desired output
      col1 col1.1 col2  col2.1 col3  col3.1 col4  col4.1 col5  col5.1
row1  0    0      0     0      0     0      0     0      0     1            
row2  0    0      0     0      0     0      0     0      0     0            
row3  0    0      0     0      0     2      0     0      0     0            
row4  0    0      0     0      0     2      0     0      0     0            
row5  0    0      0     0      0     0      0     0      1     0

So far I've tried using strsplit and lapply/unlist, and I can make a matrix that doesn't preserve structure of the original matrix, but I need the original structure for downstream applications.

bad output
> matrix(as.numeric(unlist(strsplit(test,","))),nrow=nrow(test))
     [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10]
[1,]    0    0    0    0    0    2    0    0    0     0
[2,]    0    0    0    0    0    0    0    0    1     0
[3,]    0    0    0    0    0    2    0    0    0     0
[4,]    0    0    0    0    0    0    0    0    0     1
[5,]    0    0    0    0    0    0    0    0    0     0
r matrix string
2 Answers

Here is an option in base R with read.csv on each of the column

do.call(cbind, lapply(test, function(x) read.csv(text = x, header = FALSE)))

-output

col1.V1 col1.V2 col2.V1 col2.V2 col3.V1 col3.V2 col4.V1 col4.V2 col5.V1 col5.V2
1       0       0       0       0       0       0       0       0       0       1
2       0       0       0       0       0       0       0       0       0       0
3       0       0       0       0       0       2       0       0       0       0
4       0       0       0       0       0       2       0       0       0       0
5       0       0       0       0       0       0       0       0       1       0

If it is a matrix, can use apply with MARGIN = 2 to loop over the columns and read the data with read.csv

do.call(cbind, apply(as.matrix(test), 2, function(x) 
       read.csv(text = x, header = FALSE)))

data

test <- structure(list(col1 = c("0,0", "0,0", "0,0", "0,0", "0,0"), 
      col2 = c("0,0", 
"0,0", "0,0", "0,0", "0,0"), col3 = c("0,0", "0,0", "0,2", "0,2", 
"0,0"), col4 = c("0,0", "0,0", "0,0", "0,0", "0,0"), col5 = c("0,1", 
"0,0", "0,0", "0,0", "1,0")), class = "data.frame", row.names = c("row1", 
"row2", "row3", "row4", "row5"))

NOTE: Here, we used the initial input data as data.frame

user_37322710

Another base R option with read.csv

> read.csv(text = do.call(paste,c(asplit(test,2),sep = ",")), header = FALSE)
  V1 V2 V3 V4 V5 V6 V7 V8 V9 V10
1  0  0  0  0  0  0  0  0  0   1
2  0  0  0  0  0  0  0  0  0   0
3  0  0  0  0  0  2  0  0  0   0
4  0  0  0  0  0  2  0  0  0   0
5  0  0  0  0  0  0  0  0  1   0

Data

> dput(test)
structure(c("0,0", "0,0", "0,0", "0,0", "0,0", "0,0", "0,0", 
"0,0", "0,0", "0,0", "0,0", "0,0", "0,2", "0,2", "0,0", "0,0",
"0,0", "0,0", "0,0", "0,0", "0,1", "0,0", "0,0", "0,0", "1,0"
), .Dim = c(5L, 5L), .Dimnames = list(c("row1", "row2", "row3",
"row4", "row5"), c("col1", "col2", "col3", "col4", "col5")))
user_121587570
Related