0

I have a CSV file and I'd like to reshape the first 3 columns into a matrix. I have a data.frame that looks like this.

x a 1 
x b 2 
x c 3 
y a 3 
y b 3 
y c 2

I want this in matrix form so I can feed it to heatmap to make a plot. The result should look something like:

    a    b    c
x   1    2    3
y   3    3    2

I have tried cast from the reshape package and I have tried writing a manual function to do this but I do not seem to be able to get it right.

The CSV file is very large (900mB) and in the first column there's a lot of x,y,z's so to speak.

dizzyLife
  • 63
  • 1
  • 1
  • 6

1 Answers1

1

It can be done using dcast fucntion.

library(reshape2)
textData <- "x a 1 
x b 2 
x c 3 
y a 3 
y b 3 
y c 2 "

data <- read.table(textConnection(textData), header=FALSE)
dcast(data, V1 ~ V2, value.var="V3")
Kumar Manglam
  • 2,780
  • 1
  • 19
  • 28
  • Specifying the data may be unpractical becuase there are too many observations in the csv file (900mB). Is there a way I can load the csv into R and convert it into a matrix? – dizzyLife Nov 16 '16 at 04:01
  • You can create a matrix by using `library(reshape2)` and function `dcast` in it library(reshape2) dcast(dt,A~B) A a b c 1 X 1 2 3 2 Y 3 3 2 #data dt <- data.frame(A = c(rep("X",3),rep("Y",3)),B=c("a","b","c","a","b","c"), C= c(1,2,3,3,3,2)) – Arun kumar mahesh Nov 16 '16 at 04:07
  • Try `data.table` package. They also have an implementation of `dcast`, which is optimised and will take only the specified columns only. Alternatively you can read entire data, and delete the columns, which are not required in `dcast`. – Kumar Manglam Nov 16 '16 at 04:07
  • Thanks. How would you export the table into a excel spreadsheet? – dizzyLife Nov 16 '16 at 05:53
  • You can use `write.csv` function. – Kumar Manglam Nov 16 '16 at 06:09