如何用R实现Stata式数据集缺失值等占比统计并导出Excel

需求说明 #

作为资深Stata用户，需在R中实现以下功能：生成可导出至Excel的数据框，统计数据集中各变量的缺失值占比、空值占比、特殊编码（如-99、-77，即小于0的数值）占比、零值占比；需自动区分数值型与字符型变量——零值、特殊编码统计仅适用于数值型变量。

原Stata实现代码 #

* Load dataset
sysuse auto, clear

* Save the total number of observations in a local
count
loc N = `r(N)'


*** Numeric Vars
* Identify numeric variables in the dataset
qui ds, has(type numeric)
qui ds `r(varlist)'
loc numeric_vars "`r(varlist)'"
* Loop through numeric variables calculating the # of missing, empty, DK/RF and zero cases
foreach var in `numeric_vars' {
    qui count if `var' == .
    loc `var'_miss = `r(N)'
    loc `var'_empty = `r(N)' == _N
    qui count if `var' < 0
    loc `var'_dkrf = `r(N)'
    qui count if `var' == 0
    loc `var'_zero = `r(N)'
}

    
*** String Vars
* Identify string variables in the dataset
qui ds, has(type string)
qui ds `r(varlist)'
loc string_vars "`r(varlist)'"
* Loop through numeric variables calculating the # of missing, empty DK/RF cases
foreach var in `string_vars' {
    qui count if `var' == ""
    loc `var'_miss = `r(N)'
    loc `var'_empty = `r(N)' == _N
    loc `var'_dkrf = `r(N)'
}
* Transpose the dataset
xpose, clear var
keep _varname

* Generate place holder variables
gen empty = .
gen missing = .
gen dkrf = .
gen zero = .
    
* Loop through numeric variables counting the number of each cases
foreach var in `numeric_vars' {

        qui replace empty = ``var'_empty' if _varname == "`var'"
        qui replace missing = ``var'_miss' if _varname == "`var'"
        qui replace dkrf = ``var'_dkrf' if _varname == "`var'"
        qui replace zero = ``var'_zero' if _varname == "`var'"

    }
* Loop through string variables counting the number of each cases
foreach var in `string_vars' {

        qui replace empty = ``var'_empty' if _varname == "`var'"
        qui replace missing = ``var'_miss' if _varname == "`var'"
        qui replace dkrf = ``var'_dkrf' if _varname == "`var'"

    }
foreach var of varlist empty missing dkrf zero {
        replace `var' = round((`var' * 100) / `N', 0.2)
}

已完成的R数据加载代码 #

library(haven)
# Load Stata file
df = read_dta("http://www.stata-press.com/data/r9/auto.dta")

对应R实现方案 #

以下代码完全匹配Stata逻辑，自动区分变量类型并统计各类占比，最终导出至Excel：

# 加载所需包
library(haven)
library(dplyr)
library(tidyr)
library(openxlsx)

# 加载Stata数据集
df <- read_dta("http://www.stata-press.com/data/r9/auto.dta")

# 获取总观测数
total_obs <- nrow(df)

# 定义变量统计函数，匹配Stata逻辑
var_stats <- function(var, total_n) {
  var_type <- class(var)[1]
  
  # 初始化统计结果
  stats <- list(
    missing_pct = 0,
    empty_pct = 0,
    dkrf_pct = 0,
    zero_pct = 0
  )
  
  # 计算缺失值（NA）占比
  stats$missing_pct <- round(sum(is.na(var)) / total_n * 100, 2)
  
  if (var_type %in% c("character", "factor")) {
    # 字符/因子型变量：统计空字符串占比，Stata中DK/RF与空值等价
    empty_count <- sum(var == "", na.rm = TRUE)
    stats$empty_pct <- round(empty_count / total_n * 100, 2)
    stats$dkrf_pct <- stats$empty_pct
  } else if (var_type %in% c("numeric", "integer", "dbl")) {
    # 数值型变量：判断是否全缺失（对应Stata的empty逻辑）
    stats$empty_pct <- ifelse(sum(is.na(var)) == total_n, 100, 0)
    # 统计特殊编码（小于0的数值）占比
    dkrf_count <- sum(var < 0, na.rm = TRUE)
    stats$dkrf_pct <- round(dkrf_count / total_n * 100, 2)
    # 统计零值占比
    zero_count <- sum(var == 0, na.rm = TRUE)
    stats$zero_pct <- round(zero_count / total_n * 100, 2)
  }
  
  return(stats)
}

# 遍历所有变量生成统计结果
stats_list <- lapply(df, var_stats, total_n = total_obs)

# 转换为规整的数据框
result_df <- bind_rows(stats_list, .id = "variable_name") %>%
  select(variable_name, missing_pct, empty_pct, dkrf_pct, zero_pct)

# 导出至Excel文件
write.xlsx(result_df, "variable_statistics.xlsx", rowNames = FALSE)

输出说明 #

最终生成的Excel文件包含5列：

• variable_name：变量名称
• missing_pct：缺失值（NA）占比
• empty_pct：空值/全缺失占比（字符型为空白字符串占比，数值型为全缺失标记）
• dkrf_pct：特殊编码占比（字符型与空值占比一致，数值型为小于0的数值占比）
• zero_pct：零值占比（仅数值型变量有有效统计，字符型为0）

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