Transform data frame/matrix to a different organized matrix

Question

I have a very similar doubt to my last one (Transform data frame into matrix with counts). Again, I have data files structured like this:

OTU1    PIA0    1120
OTU2    PIA1    2
OTU2    PIA3    6
OTU2    PIA4    10
OTU2    PIA5    1078
OTU2    PIN1    24
OTU2    PIN2    45
OTU2    PIN3    261
OTU2    PIN4    102
OTU3    PIA0    16
OTU3    PIA1    59
OTU3    PIA2    27
OTU3    PIA3    180
OTU3    PIA4    200
OTU3    PIA5    251
OTU3    PIN0    36
OTU3    PIN1    61
OTU3    PIN2    156
OTU3    PIN3    590
OTU3    PIN4    277
OTU4    PIA0    401
OTU4    PIN0    2

But now I want to create a file that combines counts from the three columns, more or less like this:

PIA0    1120    OTU1
PIA0    16    OTU3
PIA0    401    OTU4
PIA1    2    OTU2
PIA1    59    OTU3
PIA2    27    OTU3
PIA3    6    OTU2
PIA3    180    OTU3
PIA4    10    OTU2
PIA4    200    OTU3
PIA5    1078    OTU2
PIA5    251    OTU3
PIN0    36    OTU3
PIN0    2    OTU4
PIN1    24    OTU2
PIN1    61    OTU3
PIN2    45    OTU2
PIN2    156    OTU3
PIN3    261    OTU2
PIN3    590    OTU3
PIN4    102    OTU2
PIN4    277    OTU3

I mean, what I need to do is a new ordination of the data, leaving the first column for data from the second column (that now will appear organized) and the other two for data from third and, finally, first columns.

Would it be possible to achieve this with the R package 'reshape2' again? Using 'dcast' or 'acast' maybe?

The goal is to build a matrix like the second one in the example so I can run PHYLOCOM program with these data.

Thanks a lot once again!!!

Answer 1

x <- read.table(textConnection("
OTU1    PIA0    1120
OTU2    PIA1    2
OTU2    PIA3    6
OTU2    PIA4    10
OTU2    PIA5    1078
OTU2    PIN1    24
OTU2    PIN2    45
OTU2    PIN3    261
OTU2    PIN4    102
OTU3    PIA0    16
OTU3    PIA1    59
OTU3    PIA2    27
OTU3    PIA3    180
OTU3    PIA4    200
OTU3    PIA5    251
OTU3    PIN0    36
OTU3    PIN1    61
OTU3    PIN2    156
OTU3    PIN3    590
OTU3    PIN4    277
OTU4    PIA0    401
OTU4    PIN0    2"))

y <- x[with(x, order(V2, V1)), c(2, 3, 1)]
head(y)
     V2   V3   V1
1  PIA0 1120 OTU1
10 PIA0   16 OTU3
21 PIA0  401 OTU4
2  PIA1    2 OTU2
11 PIA1   59 OTU3
12 PIA2   27 OTU3