I have an input output table with the origin (input field) as rows and the destination (output field) as columns. Here's an example:
Mexico Thailand Vietnam
USA 0 3 6
Italy 3 7 8
France 9 3 1
Germany 3 6 7
I want to convert the table so that the origin is in column1, destination is in column 2, and value is in column 3 so that it would look like this:
origin destination value
USA Mexico 0
USA Thailand 3
USA Vietnam 6
Italy Mexico 3
Italy Thailand 7
Italy Vietnam 8
France Mexico 9
France Thailand 3
France Vietnam 1
Germany Mexico 3
Germany Thailand 6
Germany Vietnam 7
There is a simple solution using the melt function from the reshape2 package:
#sample data
Mexico<-c(0, 3, 9,3)
Thailand <-c(3, 7, 3, 6)
Vietnam <-c(6, 8, 1, 7)
names<-c("USA", "Italy", "France", "Germany")
df<-data.frame(names, Mexico, Thailand, Vietnam)
library(reshape2)
melt(df )
The package "tidyr" has a similar functionality.
library(tidyr)
gather(df, "names")