CODEWITHSUNDEEP
r
user113156
user113156

Reputation: 7107

extracting observations from matrix where columns and rows match a "key"

Given a matrix m how can I create a TRUE/ FALSE or 1 / 0 matrix where the columns and rows match some "key" in a data frame?

My goal is to assign a 1 or 0 to the location in the matrix where the columns match the cols and the rows match the rows in the colsrows_df. Then essentially just extract the observations where this is true or paste them into the colsrows_df next to the correct columns.

The below forloop just creates diagonally 1's and 0's

     m <- matrix(runif(30), nrow = 20, ncol = 20)

    dimnames(m) <- list(c(paste0("ID", 1:5, "_2000"), paste0("ID", 1:5, "_2001"), paste0("ID", 1:5, "_2002"), paste0("ID", 1:5, "_2003")),
                        c(paste0("ID", 1:5, "_2000"), paste0("ID", 1:5, "_2001"), paste0("ID", 1:5, "_2002"), paste0("ID", 1:5, "_2003")))




    cols <- colnames(m)
    rows <- rownames(m)

    library(tidyr)

    library(dplyr)
    colsrows <- cbind(cols, rows)

# Here I just separate the rows/cols and then add an extra year and paste them back together

    colsrows_df <- colsrows %>%
      data.frame %>%
      separate(cols, c("id_col", "year_col"), "_", remove = FALSE) %>%
      separate(rows, c("id_row", "year_row"), "_", remove = FALSE) %>%
      mutate(year_row_plus_1 = as.numeric(year_row) + 1,
             rows = paste0(id_row,"_", year_row_plus_1)) %>%
      select(cols, rows)


    colsrows_df

    for(i in 1:nrow(colsrows)){
      m[i, ] <- colnames(m) == colsrows_df$cols
      m[, i] <- rownames(m) == colsrows_df$rows
    }
    m

EDIT:

This seems to "solve" the problem however I am not sure how robust it is.

ids <- colsrows_df[colsrows_df$cols %in% colnames(m) & 
                     colsrows_df$rows %in% rownames(m), ]

res <- melt(m[as.matrix(colsrows_df[colsrows_df$cols %in% colnames(m) & 
                          colsrows_df$rows %in% rownames(m), ][2:1])])

cbind(ids, res)

Upvotes: 0

Views: 56

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388962

I think can you first filter colsrows_df with rownames and colnames which are actually present in m then change the order of columns, convert to matrix , use it to subset m and change those values to 1. 

m[as.matrix(colsrows_df[colsrows_df$cols %in% colnames(m) & 
                       colsrows_df$rows %in% rownames(m), ][2:1])] <- 1

Then convert remaining ones to 0

m[m != 1] <- 0

Upvotes: 1

Related Questions