generate a weighted matrix from r dataframe

user_12149817 0
Viewed 65

I have a toy example of a dataframe:

df <- data.frame(matrix(, nrow = 5, ncol = 0))
df["A|A"] <- c(0.3, 0, 0, 100, 23)
df["A|B"]= c(0, 0, 0.3, 10, 0.23)
df["A|C"]= c(0.3, 0.1, 0, 100, 2)
df["B|B"]= c(0, 0, 0, 12, 2)
df["B|B"]= c(0, 0, 0.3, 0, 0.23)
df["B|C"]= c(0.3, 0, 0, 21, 3)
df["C|A"]= c(0.3, 0, 1, 100, 0)
df["C|B"]= c(0, 0, 0.3, 10, 0.2)
df["C|C"]= c(0.3, 0, 1, 1, 0.3)

I need to get a matrix with counts of non-zero values between A and A, A and B, ..., C and C.

enter image description here

I started splitting the colnames and assigning them to variables. But I don't know how to create a matrix with certain rows and columns in a loop

counts <- colSums(df != 0)
df <- rbind(df, counts)
for(i in colnames(df)) {
  cluster1 <- (strsplit(i, "\\|")[[1]])[1]
  cluster2 <- (strsplit(i, "\\|")[[1]])[2]
  
}
dataframe r matrix
2 Answers

A base R option

> table(read.table(text = rep(names(df), colSums(df > 0)), sep = "|"))
   V2
V1  A B C
  A 3 3 4
  B 0 2 3
  C 3 3 4

or a longer version

table(
    data.frame(
        do.call(
            rbind,
            strsplit(
                as.character(subset(stack(df), values > 0)$ind),
                "\\|"
            )
        )
    )
)

gives

   X2
X1  A B C
  A 3 3 4
  B 0 2 3
  C 3 3 4
user_121587570

Reshape the data into 'long' format with pivot_longer, then separate the 'name' column into two, and reshape back to 'wide' with pivot_wider, specifying the values_fn as a lambda function to get the count of non-zero values

library(dplyr)
library(tidyr)
df %>% 
    pivot_longer(cols = everything()) %>%
    separate(name, into = c('name1', 'name2')) %>%
    pivot_wider(names_from = name2, values_from = value, 
       values_fn = list(value = ~ sum(. > 0)), values_fill = 0)

-output

# A tibble: 3 x 4
  name1     A     B     C
  <chr> <int> <int> <int>
1 A         3     3     4
2 B         0     2     3
3 C         3     3     4
user_37322710
Related