基于R语言创建多数据框变量标签交叉表的技术问询

使用haven包导入全球创业观察（Global Entrepreneurship Monitor，GEM）的SPSS数据集后，需要创建一张交叉表：横向列出所有数据框的唯一变量标签，纵向列出各个数据框，若某个数据框包含对应的变量标签，则在对应位置标记为x。以下是初始代码和两个数据集的最小可复现示例（MRE），请修正代码实现需求。

初始代码 #

library(labelled)
library(tidyverse)
# Get an overview of the same variables across all data frames
# combine data frames into a list
df_list <- list(GEM2001, GEM2002)

# extract unique variable names across all data frames
var_label <- Reduce(union, lapply(df_list, var_label))

# custom function to extract variable labels from each data frame and compare to unique names
check_vars <- function(df) {
  vars_in_df <- var_label(df)
  sapply(var_label, function(x) ifelse(x %in% vars_in_df, "x", ""))
}

# apply custom function to each data frame in the list
df_var_mat <- lapply(df_list, check_vars)

# create cross table
cross_table <- table(df_var_mat, dnn = c("DataFrame", "Variable"), useNA = "ifany")

最小可复现示例（MRE） #

# 第一个数据集: 
GEM2001 <- structure(list(setid = structure(c(7700001, 7700002, 7700003, 
7700004, 7700005, 7700006), label = "Harmonization ID", format.spss = "F12.0", display_width = 14L), 
    setid_ne = structure(c(1000000007700001, 1000000007700002, 
    1000000007700003, 1000000007700004, 1000000007700005, 1000000007700006
    ), label = "Alternative ID variable to avoid duplicates across years", format.spss = "F15.0", display_width = 17L), 
    yrsurv = structure(c(2001, 2001, 2001, 2001, 2001, 2001), label = "Year survey was administered", format.spss = "F4.0"), 
    country = structure(c(7, 7, 7, 7, 7, 7), label = "Country", format.spss = "F4.0", display_width = 9L, labels = c(`United States` = 1, 
    Russia = 7, Egypt = 20, `South Africa` = 27, Greece = 30, 
    Netherlands = 31, Belgium = 32, France = 33, Spain = 34, 
    Hungary = 36, Italy = 39, Romania = 40, Switzerland = 41, 
    Austria = 43, `United Kingdom` = 44, Denmark = 45, Sweden = 46, 
    Norway = 47, Poland = 48, Germany = 49, Peru = 51, Mexico = 52, 
    Argentina = 54, Brazil = 55, Chile = 56, Colombia = 57, Malaysia = 60, 
    Australia = 61, Indonesia = 62, Philippines = 63, `New Zealand` = 64, 
    Singapore = 65, Thailand = 66, Japan = 81, Korea = 82, Vietnam = 84, 
    China = 86, Turkey = 90, India = 91, Pakistan = 92, Iran = 98, 
    Canada = 101, Morocco = 212, Algeria = 213, Tunisia = 216, 
    Libya = 218, Ghana = 233, Nigeria = 234, Angola = 244, Barbados = 246, 
    Ethiopia = 251, Uganda = 256, Zambia = 260, Namibia = 264, 
    Malawi = 265, Botswana = 267, Portugal = 351, Luxembourg = 352, 
    Ireland = 353, Iceland = 354, Finland = 358, Lithuania = 370, 
    Latvia = 371, Estonia = 372, Serbia = 381, Montenegro = 382, 
    Croatia = 385, Slovenia = 386, `Bosnia and Herzegovina` = 387, 
    Macedonia = 389, `Czech Republic` = 420, Slovakia = 421, 
    Guatemala = 502, `El Salvador` = 503, `Costa Rica` = 506, 
    Panama = 507, Venezuela = 582, Bolivia = 591, Ecuador = 593, 
    Suriname = 597, Uruguay = 598, `* 'Azores'` = 620, Tonga = 676, 
    Vanuatu = 678, Kazakstan = 701, `Shenzhen*` = 755, `Puerto Rico` = 787, 
    `Dominican Republic` = 809, `Hong Kong` = 852, `Trinidad & Tobago` = 868, 
    Jamaica = 876, Bangladesh = 880, Taiwan = 886, Lebanon = 961, 
    Jordan = 962, Syria = 963, `Saudi Arabia` = 966, Yemen = 967, 
    `West Bank & Gaza Strip` = 970, `United Arab Emirates` = 971, 
    Israel = 972), class = c("haven_labelled", "vctrs_vctr", 
    "double")), weight = structure(c(0.947503767410607, 0.919003654090076, 
    0.924603676356567, 1.01710404415125, 0.716602849315504, 0.83510332049034
    ), label = "Weight provided by data vendor", format.spss = "F8.6", display_width = 10L)), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"), label = "JN725 - IAE - GEM 2009")

# 第二个数据集
GEM2002 <- structure(list(setid = structure(c(1121800009, 1121800025, 1121800031, 
1121800035, 1121800036, 1121800039), label = "Harmonization ID", format.spss = "F12.0", display_width = 14L), 
    setid_ne = structure(c(2000001121800009, 2000001121800025, 
    2000001121800031, 2000001121800035, 2000001121800036, 2000001121800039
    ), label = "Alternative ID variable to avoid duplicates across years", format.spss = "F15.0", display_width = 17L), 
    yrsurv = structure(c(2002, 2002, 2002, 2002, 2002, 2002), label = "Year survey was administered", format.spss = "F4.0"), 
    country = structure(c(1, 1, 1, 1, 1, 1), label = "Country", format.spss = "F4.0", display_width = 9L, labels = c(`United States` = 1, 
    Russia = 7, Egypt = 20, `South Africa` = 27, Greece = 30, 
    Netherlands = 31, Belgium = 32, France = 33, Spain = 34, 
    Hungary = 36, Italy = 39, Romania = 40, Switzerland = 41, 
    Austria = 43, `United Kingdom` = 44, Denmark = 45, Sweden = 46, 
    Norway = 47, Poland = 48, Germany = 49, Peru = 51, Mexico = 52, 
    Argentina = 54, Brazil = 55, Chile = 56, Colombia = 57, Malaysia = 60, 
    Australia = 61, Indonesia = 62, Philippines = 63, `New Zealand` = 64, 
    Singapore = 65, Thailand = 66, Japan = 81, Korea = 82, Vietnam = 84, 
    China = 86, Turkey = 90, India = 91, Pakistan = 92, Iran = 98, 
    Canada = 101, Morocco = 212, Algeria = 213, Tunisia = 216, 
    Libya = 218, Ghana = 233, Nigeria = 234, Angola = 244, Barbados = 246, 
    Ethiopia = 251, Uganda = 256, Zambia = 260, Namibia = 264, 
    Malawi = 265, Botswana = 267, Portugal = 351, Luxembourg = 352, 
    Ireland = 353, Iceland = 354, Finland = 358, Lithuania = 370, 
    Latvia = 371, Estonia = 372, Serbia = 381, Montenegro = 382, 
    Croatia = 385, Slovenia = 386, `Bosnia and Herzegovina` = 387, 
    Macedonia = 389, `Czech Republic` = 420, Slovakia = 421, 
    Guatemala = 502, `El Salvador` = 503, `Costa Rica` = 506, 
    Panama = 507, Venezuela = 582, Bolivia = 591, Ecuador = 593, 
    Suriname = 597, Uruguay = 598, `* 'Azores'` = 620, Tonga = 676, 
    Vanuatu = 678, Kazakstan = 701, `Shenzhen*` = 755, `Puerto Rico` = 787, 
    `Dominican Republic` = 809, `Hong Kong` = 852, `Trinidad & Tobago` = 868, 
    Jamaica = 876, Bangladesh = 880, Taiwan = 886, Lebanon = 961, 
    Jordan = 962, Syria = 963, `Saudi Arabia` = 966, Yemen = 967, 
    `West Bank & Gaza Strip` = 970, `United Arab Emirates` = 971, 
    Israel = 972), class = c("haven_labelled", "vctrs_vctr", 
    "double")), weight = structure(c(0.666652666946661, 1.35532689346212, 
    0.886868262634747, 0.247242055158897, 1.7567198656027, 0.595583088338233
    ), label = "Weight provided by data vendor", format.spss = "F8.6", display_width = 10L)), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"), label = "JN725 - IAE - GEM 2009")

修正后的代码及解释 #

初始代码的核心问题是最后用table()处理列表时格式转换错误，无法生成预期的交叉表。以下是修正后的代码：

library(labelled)
library(tidyverse)

# 将数据框放入列表，并给每个元素命名（方便后续显示数据框名称）
df_list <- list(GEM2001 = GEM2001, GEM2002 = GEM2002)

# 提取所有数据框中的变量标签，去重得到完整的变量标签列表
all_var_labels <- Reduce(union, lapply(df_list, function(x) unname(var_label(x))))

# 自定义函数：检查单个数据框包含哪些变量标签，返回标记后的向量
check_vars_in_df <- function(df) {
  current_labels <- unname(var_label(df))
  sapply(all_var_labels, function(lab) ifelse(lab %in% current_labels, "x", ""))
}

# 对列表中的每个数据框应用函数，得到标记后的矩阵
var_markers <- lapply(df_list, check_vars_in_df)

# 将结果转换为数据框，整理成交叉表格式
cross_table <- bind_rows(var_markers, .id = "DataFrame") %>%
  pivot_longer(cols = -DataFrame, names_to = "VariableLabel", values_to = "Exists") %>%
  pivot_wider(names_from = VariableLabel, values_from = Exists)

# 查看结果
print(cross_table)

关键修正点： #

1. 给列表元素命名：让交叉表直接显示数据框名称（如GEM2001、GEM2002），而非默认索引。
2. 处理变量标签的属性：用unname()剥离变量标签的原变量名属性，只保留标签文本，避免匹配错误。
3. 改用tidyverse函数整理格式：通过bind_rows、pivot_longer和pivot_wider将列表转换为标准的交叉表数据框，直接实现"数据行-变量列"的需求格式。

运行后会得到如下格式的交叉表（示例中两个数据集变量标签完全一致，所以全为x，实际有差异时会显示空值）：

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