How to create a data frame with equal row/column names from non-symetric data

craszer 0
Viewed 23

I have data that looks like this:

  country1 country2 value
1      GER      USA     3
2      AUS      AUS     6
3      YUG      YUG     4
4      SIN      GER     3

Example code here:

country1 <- c("GER", "AUS", "YUG", "SIN")
country2 <- c("USA", "AUS", "YUG", "GER")
value <- c(3, 6, 4, 3)
mydf <- as.data.frame(cbind(country1, country2, value))

The USA is only present in "country2" while SIN is only present in the "country1" column.

I want to create a crosstable, that has all countries from both columns ("country1", "country2") as columns and rows. So far, I´ve found this in the forum, but it doesn't solve the problem:

mydf$country1 <- as.factor(mydf$country1)
mydf$country2 <- as.factor(mydf$country2)
mydf$value <- as.numeric(mydf$value)
testtable <- as.data.frame.matrix(xtabs(value ~ mydf$country1 + mydf$country2, mydf))

But this returns SIN only as a row and USA only as a column:

   AUS GER USA YUG
AUS   6   0   0   0
GER   0   0   3   0
SIN   0   3   0   0
YUG   0   0   0   4

How can I end up with a "symetric" data frame, where rows and columns are equal?

dataframe r
2 Answers

We may need

lvls <- sort(unique(unlist(mydf[1:2])))
 mydf[1:2] <- lapply(mydf[1:2], factor, levels = lvls)
xtabs(value ~ ., mydf)

-output

     country2
country1 AUS GER SIN USA YUG
     AUS   6   0   0   0   0
     GER   0   0   0   3   0
     SIN   0   3   0   0   0
     USA   0   0   0   0   0
     YUG   0   0   0   0   4
akrun0

Add all levels when converting to factor:

country1 <- c("GER", "AUS", "YUG", "SIN")
country2 <- c("USA", "AUS", "YUG", "GER")
country1 <- factor(country1, levels = union(country1, country2))
country2 <- factor(country2, levels = union(country1, country2))
value <- c(3, 6, 4, 3)
mydf <- data.frame(country1, country2, value)

as.data.frame.matrix(xtabs(value ~ mydf$country1 + mydf$country2, mydf))

output

    GER AUS YUG SIN USA
GER   0   0   0   0   3
AUS   0   6   0   0   0
YUG   0   0   4   0   0
SIN   3   0   0   0   0
USA   0   0   0   0   0
Maël0
Related