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))