Taking two columns from your dataframe and creating a squared dataframe

user_16994516 0
Viewed 32

Hello I have a dataframe that contains information on the correlation of two factors, which looks somewhat like this.

Factor_1 Factor_2   value
  a        b        0.8
  a        a        1
  a        d        0.6
  b        c        0.4
  b        b        1
  a        c        0.2
  b        d        0.75
  b        a        0.8
  c        a        0.2
  c        c        1
  c        d        0.1
  c        b        0.4

As you can see, when Factor_1 and Factor_2 are of the same value, their correlation is 1. Also, the number of each factor does not match (Factor_1 has a,b,c when Factor_2 has a,b,c,d.)

With this dataframe, I want to create a squared dataframe that has the values of Factor_1 as the row and column names, and the values matching each correlation value. It should look something like this.

   a    b    c
a  1    0.8  0.2

b  0.8  1    0.4

c  0.2  0.4  1

Any way to create this dataframe, using tidyverse? Thanks in advance!

tidyverse dataframe r
3 Answers

in base R:

xtabs(value~Factor_1 + Factor_2, df)[,-4]

        Factor_2
Factor_1    a    b    c  
       a 1.00 0.80 0.20 
       b 0.80 1.00 0.40 
       c 0.20 0.40 1.00

if you need it as a dataframe:

as.data.frame.matrix(xtabs(value~., df)[,1:3])

    a   b   c
a 1.0 0.8 0.2
b 0.8 1.0 0.4
c 0.2 0.4 1.0

You could also use

xtabs(value~.,subset(df, Factor_1 %in%Factor_2 & Factor_2 %in%Factor_1))

        Factor_2
Factor_1   a   b   c
       a 1.0 0.8 0.2
       b 0.8 1.0 0.4
       c 0.2 0.4 1.0
user_83802720

You may try

library(dplyr)
library(tidyr)

df %>%
  filter(Factor_2 %in% unique(Factor_1), Factor_1 %in% unique(Factor_2)) %>%
  arrange(Factor_1, Factor_2) %>%
  pivot_wider(id_cols = Factor_1, names_from = Factor_2, values_from = value) %>%
  column_to_rownames(var = "Factor_1")
    a   b   c
a 1.0 0.8 0.2
b 0.8 1.0 0.4
c 0.2 0.4 1.0
user_167291750 
    df %>% arrange(Factor_1,Factor_2) %>% 
      pivot_wider(names_from = Factor_2, values_from = value)%>%
      select(1:(nrow(.)+1))

# A tibble: 3 × 4
  Factor_1     a     b     c
  <chr>    <dbl> <dbl> <dbl>
1 a          1     0.8   0.2
2 b          0.8   1     0.4
3 c          0.2   0.4   1
user_193358040
Related