same row and column names R

Question

I am doing a change matrix of two periods of Land Cover in R that I usually/easily do in Excel using pivot table.

I got how to do the pivot table way in R but I'm having a bit trouble in manipulating the data frame further.

I NEED TO HAVE THE EXACT ROWS AND COLUMNS from data frames with unequal rows and columns and just put 0 to the missing rows/columns.

Sample:

df <- matrix(1:20, 4)
colnames(df) <- c('a', 'b', 'c', 'd', 'e')
rownames(df) <- c('a', 'b', 'c', 'd')
df1 <- df[-3,]

df1

In 'df1', rows 'c' and 'e' is missing. I started making a for loop that goes into rows and look for what's missing:

FinalTable <- function (df){
  for (i in rownames(df)){
    if (i != colnames(df)){
      print ('not equal')
    }
  }
}

After this, I need to do rbind to add the column that is missing in the row.

I appreciate your help.

I can send the raw and desired table for your reference.

Thanks!

Nando

Thanks! I checked on it. It's helpful. – nandonation Feb 24 '17 at 14:29 — nandonation, Feb 24 '17 at 14:29

akrun · Accepted Answer · 2017-02-24T06:21:08.300

We can create a matrix with full names, then use match to get the rows and column index based on the matching row names and column names in the original and new matrix, and then assign it to the new dataset

un1 <- sort(union(rownames(df1), colnames(df1)))
m1 <- matrix(0, dimnames = list(un1, un1), ncol=length(un1), nrow=length(un1))
i1 <- match(rownames(m1), rownames(df1), nomatch = 0)
j1 <- match(colnames(m1), colnames(df1), nomatch = 0)
i2 <- match(rownames(df1), rownames(m1))
j2 <- match(colnames(df1), colnames(m1))
m1[i2,j2] <- df1[i1,j1]
m1   
#  a b  c  d  e
#a 1 5  9 13 17
#b 2 6 10 14 18
#c 0 0  0  0  0
#d 4 8 12 16 20
#e 0 0  0  0  0

Thanks! It works! I just put 'as.matrix' to df1[i1,j1] on my data frame. Thank you very much! — nandonation, Feb 24 '17 at 14:29

same row and column names R

1 Answers1