Take Symmetrical Mean of a tibble (ignoring the NAs)

Question

I have a tibble where the rows and columns are the same IDs and I would like to take the mean (ignoring the NAs) to make the df symmetrical. I am struggling to see how.

data <- tibble(group = LETTERS[1:4], 
               A = c(NA, 10, 20, NA),
               B = c(15, NA, 25, 30),
               C = c(20, NA, NA, 10),
               D = c(10, 12, 15, NA)
               )

I would normally do

A <- as.matrix(data[-1])
(A + t(A))/2

But this does not work because of the NAs.

Edit: below is the expected output.

output <- tibble(group = LETTERS[1:4],
                 A = c(NA, 12.5, 20, 10),
                 B = c(12.5, NA, 25, 21),
                 C = c(20, 25, NA, 12.5),
                 D = c(10, 21, 12.5, NA))

No. This doesnt work because then you half the non-NA value when taking the mean — zimia, Jun 02 '21 at 09:48

Zaw · Answer 1 · 2021-06-04T15:44:50.370

Here is a suggestion using tidyverse code.

library(tidyverse)

data <- tibble(group = LETTERS[1:4], 
               A = c(NA, 10, 20, NA),
               B = c(15, NA, 25, 30),
               C = c(20, NA, NA, 10),
               D = c(10, 12, 15, NA)
)

A <- data %>% 
  pivot_longer(-group, values_to = "x")

B <- t(data) %>% 
  as.data.frame() %>% 
  setNames(LETTERS[1:4]) %>% 
  rownames_to_column("group") %>% 
  pivot_longer(-group, values_to = "y") %>% 
  left_join(A, by = c("group", "name")) %>% 
  mutate(
    mean = if_else(!(is.na(x) | is.na(y)), (x + y)/2, x),
    mean = if_else(is.na(mean) & !is.na(y), y, mean)
    ) %>% 
  select(-x, -y) %>% 
  pivot_wider(names_from = name, values_from = mean)

B

## A tibble: 4 x 5
#  group     A     B     C     D
#  <chr> <dbl> <dbl> <dbl> <dbl>
#1 A      NA    12.5  20    10  
#2 B      12.5  NA    25    21  
#3 C      20    25    NA    12.5
#4 D      10    21    12.5  NA

score 0 · Accepted Answer · answered Jun 02 '21 at 10:14

Okay so this is how I ended up doing this. I would have preferred if I didnt use a for loop because the actual data I have is much bigger but beggars cant be choosers!

A <- as.matrix(data[-1])

for (i in 1:nrow(A)){
  for (j in 1:ncol(A)){
    if(is.na(A[i,j])){
      A[i,j] <- A[j, i]
    }
  }
}

output <- (A + t(A))/2
output %>% 
  as_tibble() %>% 
  mutate(group = data$group) %>% 
  select(group, everything())

# A tibble: 4 x 5
  group     A     B     C     D
  <chr> <dbl> <dbl> <dbl> <dbl>
1 A      NA    12.5  20    10  
2 B      12.5  NA    25    21  
3 C      20    25    NA    12.5
4 D      10    21    12.5  NA

Take Symmetrical Mean of a tibble (ignoring the NAs)

2 Answers2