How to filter distinct rows when identifying values alternate between columns?

Viewed 35

I have computed a correlation matrix on a bunch of variables. However, I now have double values in the dataset for pairs above and below the matrix's diagonal, which I want to remove (e.g. rows 1 and 4):

# A tibble: 10 × 4
   rowname      variable      corr      pval
   <chr>        <chr>        <dbl>     <dbl>
 1 light_blue   total_cal     0.84 0.0000458
 2 light_blue   workout_cal   0.84 0.0000459
 3 light_blue   after_cal     0.84 0.0000454
 4 total_cal    light_blue    0.84 0.0000458
 5 workout_cal  light_blue    0.84 0.0000459
 6 after_cal    light_blue    0.84 0.0000454
 7 sweat_points total_cal     0.83 0.000074 
 8 sweat_points workout_cal   0.83 0.0000739
 9 sweat_points after_cal     0.83 0.0000748
10 total_cal    sweat_points  0.83 0.000074 

How can I achieve this? The problem I have is that I can't use distinct() or unique() because the identifying values alternate between columns. Row 1 and 4 are identical for instance but, light_blue and total_cal alternate between the rowname and variable columns. Therefore they are not regarded as identical pairs by distinct() or unique() - I think.

I did some pre-processing on the data like pivoting to long format and binding the long correlation matrix with the long p-value matrix. So I could find a work-around but I'm just curious if there's a more elegant way. Perhaps there's a dplyr function I'm unaware of?

Here's my code:

# Compute correlation matrix
corr_mat <- data %>% 
    cor_mat(5:17) 

# Extract p-values from corr_mat
pval_mat <- corr_mat %>% 
    cor_get_pval()

# Transform corr_mat and add ID
corr_mat %<>% 
    pivot_longer(2:14, names_to = "variable", values_to = "corr") %>% 
    mutate(ID = row_number(), .before = "rowname")
    
# Pivot pval_mat longer
pval_mat %<>% 
    pivot_longer(2:14, names_to = "variable", values_to = "pval") %>% 
    mutate(ID = row_number(), .before = "rowname")

# Bind corr_mat and corr_pval
corr_mat %>% 
    left_join(pval_mat, by = "ID") %>% 
    select(-ends_with("y")) %>% 
    rename(rowname = rowname.x,
           variable = variable.x) %>% 
    filter(pval < .05) %>% 
    arrange(desc(corr)) %>% 
    filter(corr < .96) %>%
    unique()

And here's a manually constructed tibble containing the data shown above:

example_data <- tibble(
    rowname = c("light_blue", "light_blue", "light_blue",
                "total_cal", "workout_cal", "after_cal", "sweat_points",
                "sweat_points", "sweat_points", "total_cal"),
    variable = c("total_cal", "workout_cal", "after_cal", "light_blue",
                 "light_blue", "light_blue", "total_cal", "workout_cal",
                 "after_cal", "sweat_points"),
    corr = c(0.84, 0.84, 0.84, 0.84, 0.84, 0.84, 0.83, 0.83, 0.83, 0.83),
    pval = c(4.58e-05, 4.59e-05, 4.54e-05, 4.58e-05, 4.59e-05, 4.54e-05,
             7.4e-05, 7.39e-05, 7.48e-05, 7.4e-05))

Screenshot of data structure

1 Answers

Given your example data.frame/tibble, my proposed solution is the following:

result <- example_data[duplicated(t(apply(example_data, 1, sort))), ]

Output:

# A tibble: 4 x 4
  rowname     variable      corr      pval
  <chr>       <chr>        <dbl>     <dbl>
1 total_cal   light_blue    0.84 0.0000458
2 workout_cal light_blue    0.84 0.0000459
3 after_cal   light_blue    0.84 0.0000454
4 total_cal   sweat_points  0.83 0.000074
Related