从现有数据集创建新数据集时为何得到0条观测值？

问题背景 #

我有一个包含7593条观测值、22个变量的数据集coll，计划通过筛选创建新数据集coll2，使用的R脚本如下：

# identify field names
 cols<- c("MD_EARN_WNE_P10","PFTFAC","MEDIAN_HH_INC","PCTPELL",
     "UG25ABV","PCIP10","PCIP11", "PCIP14", "PCIP15",
     "PCIP26", "PCIP27", "PCIP29", "PCIP40", "PCIP41", 
     "PCIP47")

 # set select fields to numeric
 coll[cols] <- lapply(coll[cols], function(x) {as.numeric(levels(x))[x]})


 # create Fields
 coll$selective<-as.factor(ifelse(coll$CCUGPROF==14|coll$CCUGPROF==15,
                             1,0))

 coll$STEM<- (coll$PCIP10+coll$PCIP11+coll$PCIP14+coll$PCIP15+
           coll$PCIP26+coll$PCIP27+coll$PCIP29+coll$PCIP40+
           coll$PCIP41+coll$PCIP47)

# subset
coll<-subset(coll, PREDDEG == 3 & MAIN == 1 & 
           (CCUGPROF==5|CCUGPROF==6|CCUGPROF==7|
              CCUGPROF==8 |CCUGPROF==9 |CCUGPROF==10 |
              CCUGPROF==11 |CCUGPROF==12 |
              CCUGPROF==13 |CCUGPROF==14 |CCUGPROF==15),
         select=c(selective,
                  MD_EARN_WNE_P10,
                  MEDIAN_HH_INC,
                  STEM,
                  PCTPELL,
                  UG25ABV))

# remove specfied values
wordList <- c("PrivacySuppressed","NULL",NA)
coll2<-subset(coll, !MD_EARN_WNE_P10 %in% wordList 
          & !selective %in% wordList
          & !MEDIAN_HH_INC %in% wordList
          & !STEM %in% wordList
          & !PCTPELL %in% wordList
          & !UG25ABV %in% wordList)

# adjust units for percent fields
coll2$STEM<-coll2$STEM*100
coll2$PCTPELL<-coll2$PCTPELL*100
coll2$UG25ABV<-coll2$UG25ABV*100

 # adjust units for continuous variables
coll2$MD_EARN_WNE_P10<-coll2$MD_EARN_WNE_P10/1000
coll2$MEDIAN_HH_INC<-coll2$MEDIAN_HH_INC/1000

执行后得到的coll2是包含6个变量但0条观测值的数据集，处理后coll数据集前10行的dput结果如下：

> dput(head(coll,10))
structure(list(selective = structure(c(1L, 1L, 1L, 1L, 1L, 2L, 
1L, 1L, 2L, 2L), levels = c("0", "1"), class = "factor"), 
MD_EARN_WNE_P10 = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, 
NA_real_, NA_real_), MEDIAN_HH_INC = c(NA_real_, NA_real_, 
NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_
), STEM = c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), PCTPELL = 
c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, 
NA_real_, NA_real_), UG25ABV = c(NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_
 )), row.names = c(1L, 2L, 3L, 4L, 5L, 6L, 8L, 9L, 10L, 11L), class = "data.frame")

核心错误原因 #

• 因子转数值逻辑失效：你使用as.numeric(levels(x))[x]转换因子为数值，但原始列中存在"PrivacySuppressed"、"NULL"这类非数值水平，as.numeric(levels(x))会将这些字符串直接转为NA，导致所有目标列全变为缺失值。
• NA判断逻辑错误：在R中，NA %in% c(...)返回结果为NA，而subset会自动剔除判断结果为NA的行。由于coll中所有数值列都是NA，你的筛选条件!MD_EARN_WNE_P10 %in% wordList会让所有行的判断结果为NA，最终全部被剔除。另外selective是因子类型，"PrivacySuppressed"、"NULL"不在其水平内，这部分判断完全多余。
• 未提前清理非数值标记：转换前未将非数值标记替换为NA，直接转换导致有效数据丢失，后续筛选又排除所有NA，自然没有观测值保留。

修正方案 #

1. 先清理非数值标记，再转换因子为数值 #

先把目标列中的非数值标记替换为NA，再进行类型转换：

cols<- c("MD_EARN_WNE_P10","PFTFAC","MEDIAN_HH_INC","PCTPELL",
         "UG25ABV","PCIP10","PCIP11", "PCIP14", "PCIP15",
         "PCIP26", "PCIP27", "PCIP29", "PCIP40", "PCIP41", 
         "PCIP47")

# 替换非数值标记为NA，再转换为数值
coll[cols] <- lapply(coll[cols], function(x) {
  x[x %in% c("PrivacySuppressed", "NULL")] <- NA
  if(is.factor(x)) as.numeric(as.character(x)) else as.numeric(x)
})

2. 修正筛选逻辑，正确处理缺失值 #

用!is.na()替代%in%来排除缺失值，去掉对selective的无效判断：

# 筛选非缺失值的行
coll2<-subset(coll, 
              !is.na(MD_EARN_WNE_P10) & 
                !is.na(MEDIAN_HH_INC) & 
                !is.na(STEM) & 
                !is.na(PCTPELL) & 
                !is.na(UG25ABV))

3. 检查初始子集筛选条件 #

运行以下代码查看初始子集筛选后coll的行数，确认是否有符合条件的观测值：

nrow(coll)

如果结果为0，说明PREDDEG == 3 & MAIN == 1以及CCUGPROF的取值范围设置过严，需要检查原始数据中这些变量的取值分布。

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