如何提取数据框对角线元素并合并行名与列名以压缩数据？

Problem Description #

I have a data frame where only the diagonal positions hold valid values (all other entries are NA). I want to extract these diagonal elements, combine the matching row and column names using an underscore as a new identifier, and get a condensed result like the example below.

Raw Data Example

1750:10-K:2006 1800:10-K:2006 1923:10-K:2006 2488:10-K:2006
1750:10-K:2005 0.9291217 NA NA NA
1800:10-K:2005 NA 0.9690067 NA NA
1923:10-K:2005 NA NA 0.8584429 NA
2488:10-K:2005 NA NA NA 0.956372
2969:10-K:2005 NA NA NA NA
3133:10-K:2005 NA NA NA NA
3197:10-K:2005 NA NA NA NA
3333:10-K:2005 NA NA NA NA
3370:10-K:2005 NA NA NA NA
3673:10-K:2005 NA NA NA NA
2969:10-K:2006 3133:10-K:2006 3197:10-K:2006 3333:10-K:2006
1750:10-K:2005 NA NA NA NA
1800:10-K:2005 NA NA NA NA
1923:10-K:2005 NA NA NA NA
2488:10-K:2005 NA NA NA NA
2969:10-K:2005 0.861327 NA NA NA
3133:10-K:2005 NA 0.9375159 NA NA
3197:10-K:2005 NA NA 0.9633629 NA
3333:10-K:2005 NA NA NA 0.9752259
3370:10-K:2005 NA NA NA NA
3673:10-K:2005 NA NA NA NA
3370:10-K:2006 3673:10-K:2006
1750:10-K:2005 NA NA
1800:10-K:2005 NA NA
1923:10-K:2005 NA NA
2488:10-K:2005 NA NA
2969:10-K:2005 NA NA
3133:10-K:2005 NA NA
3197:10-K:2005 NA NA
3333:10-K:2005 NA NA
3370:10-K:2005 0.941602 NA
3673:10-K:2005 NA 0.9745789

Desired Output

1750:10-K:2005_1750:10-K:2006 0.9291217
1800:10-K:2005_1800:10-K:2006 0.9690067
1923:10-K:2005_1923:10-K:2006 0.8584429
2488:10-K:2005_2488:10-K:2006 0.956372
...

Data Structure

structure(list(`1750:10-K:2006` = c(0.929121725727165, NA, NA, NA, NA, NA, NA, NA, NA, NA), `1800:10-K:2006` = c(NA, 0.96900670959669, NA, NA, NA, NA, NA, NA, NA, NA), `1923:10-K:2006` = c(NA, NA, 0.858442889654398, NA, NA, NA, NA, NA, NA, NA), `2488:10-K:2006` = c(NA, NA, NA, 0.956371967288172, NA, NA, NA, NA, NA, NA), `2969:10-K:2006` = c(NA, NA, NA, NA, 0.861326963904054, NA, NA, NA, NA, NA), `3133:10-K:2006` = c(NA, NA, NA, NA, NA, 0.93751593784196, NA, NA, NA, NA), `3197:10-K:2006` = c(NA, NA, NA, NA, NA, NA, 0.963362873672737, NA, NA, NA), `3333:10-K:2006` = c(NA, NA, NA, NA, NA, NA, NA, 0.975225879729218, NA, NA), `3370:10-K:2006` = c(NA, NA, NA, NA, NA, NA, NA, NA, 0.941602039119482, NA), `3673:10-K:2006` = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 0.974578948898938)), row.names = c("1750:10-K:2005", "1800:10-K:2005", "1923:10-K:2005", "2488:10-K:2005", "2969:10-K:2005", "3133:10-K:2005", "3197:10-K:2005", "3333:10-K:2005", "3370:10-K:2005", "3673:10-K:2005"), class = "data.frame")

Solution #

Here are two simple, effective ways to get your desired output in R:

Method 1: Base R Approach

This uses built-in functions to directly extract diagonal values and combine identifiers:

# Load your data frame
df <- structure(list(`1750:10-K:2006` = c(0.929121725727165, NA, NA, NA, NA, NA, NA, NA, NA, NA), `1800:10-K:2006` = c(NA, 0.96900670959669, NA, NA, NA, NA, NA, NA, NA, NA), `1923:10-K:2006` = c(NA, NA, 0.858442889654398, NA, NA, NA, NA, NA, NA, NA), `2488:10-K:2006` = c(NA, NA, NA, 0.956371967288172, NA, NA, NA, NA, NA, NA), `2969:10-K:2006` = c(NA, NA, NA, NA, 0.861326963904054, NA, NA, NA, NA, NA), `3133:10-K:2006` = c(NA, NA, NA, NA, NA, 0.93751593784196, NA, NA, NA, NA), `3197:10-K:2006` = c(NA, NA, NA, NA, NA, NA, 0.963362873672737, NA, NA, NA), `3333:10-K:2006` = c(NA, NA, NA, NA, NA, NA, NA, 0.975225879729218, NA, NA), `3370:10-K:2006` = c(NA, NA, NA, NA, NA, NA, NA, NA, 0.941602039119482, NA), `3673:10-K:2006` = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 0.974578948898938)), row.names = c("1750:10-K:2005", "1800:10-K:2005", "1923:10-K:2005", "2488:10-K:2005", "2969:10-K:2005", "3133:10-K:2005", "3197:10-K:2005", "3333:10-K:2005", "3370:10-K:2005", "3673:10-K:2005"), class = "data.frame")

# Extract diagonal values
diag_vals <- diag(df)

# Merge row and column names into a single identifier
names(diag_vals) <- paste(rownames(df), colnames(df), sep = "_")

# Convert to a clean data frame (optional but easier to work with)
result_df <- data.frame(Combined_ID = names(diag_vals), Value = diag_vals, row.names = NULL)

print(result_df)

Method 2: Tidyverse Approach

If you prefer a pipe-based, readable workflow, use tidyverse functions to reshape and filter the data:

library(tidyverse)

# Load your data frame
df <- structure(...) # Same structure as above

result_df <- df %>%
  rownames_to_column("Row_ID") %>%
  pivot_longer(-Row_ID, names_to = "Col_ID", values_to = "Value") %>%
  filter(!is.na(Value)) %>%
  mutate(Combined_ID = paste(Row_ID, Col_ID, sep = "_")) %>%
  select(Combined_ID, Value)

print(result_df)

Both methods will produce the condensed output you need, with combined row/column identifiers paired with their corresponding diagonal values.

内容的提问来源于stack exchange，提问作者user8959427