UK PRTR XML数据提取：解决FacilityName条目数量不一致问题

我从UK PRTR数据集提取了uk_prtr_dataset_2021.xml文件，用R的xml2和dplyr处理数据，代码如下：

library(xml2)
library(dplyr) 

xml_file <- read_xml('uk_prtr_dataset_2021.xml') 

需要提取以下节点：

node_vec <- c("ParentCompanyName", "FacilityName", "LongitudeMeasure", "LatitudeMeasure", "MainEconomicActivityName")

for(node in seq_along(node_vec)){

  node_ref <- node_vec[node]
  var_ref <- paste0(".//rsm:",node_ref)

  temp <- xml_file %>%   
           xml_find_all(var_ref) %>%
           as_list() %>%
           simplify() %>%
           enframe() %>%
           unnest(value) %>%
           unnest(value)

print(paste0(node_ref,": ",nrow(temp)))
}

# 输出结果
[1] "ParentCompanyName: 6305"
[1] "FacilityName: 6306"
[1] "LongitudeMeasure: 6305"
[1] "LatitudeMeasure: 6305"
[1] "MainEconomicActivityName: 6305"

发现FacilityName比其他节点多1条数据，导致无法合并5列数据，如何定位并移除该多余条目？

附XML片段：

<rsm:PollutantReleaseAndTransferReport xmlns:rsm="urn:eu:com:env:prtr:data:standard:2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:eu:com:env:prtr:data:standard:2 http://www.eionet.europa.eu/schemas/eprtr/PollutantReleaseAndTransferReport_2p0.xsd">
rsm:ReportingYear2021</rsm:ReportingYear>
rsm:CountryIDUK</rsm:CountryID>
rsm:CoordinateSystemIDEPSG:4326</rsm:CoordinateSystemID>
rsm:RemarkTextNone</rsm:RemarkText>
rsm:CompetentAuthorityParty
rsm:NameBEIS</rsm:Name>
rsm:Address
rsm:StreetName1 Victoria Street</rsm:StreetName>
rsm:CityNameLondon</rsm:CityName>
rsm:PostcodeCodeSW1H 0ET</rsm:PostcodeCode>
</rsm:Address>
rsm:TelephoneCommunication
rsm:CompleteNumberText00 44 774 169 9372</rsm:CompleteNumberText>
</rsm:TelephoneCommunication>
rsm:FaxCommunication
rsm:CompleteNumberText--</rsm:CompleteNumberText>
</rsm:FaxCommunication>
rsm:EmailCommunication
rsm:EmailURIIDShamim.Choudhury@beis.gov.uk</rsm:EmailURIID>
</rsm:EmailCommunication>
rsm:ContactPersonNameShamim Choudhury</rsm:ContactPersonName>
</rsm:CompetentAuthorityParty>
rsm:CompetentAuthorityParty
rsm:NameE1</rsm:Name>
rsm:Address
rsm:StreetNameAll correspondence to: Defra, Industrial Pollution, 5F Ergon House, Horseferry Rd. London.</rsm:StreetName>
rsm:CityNameLondon</rsm:CityName>
rsm:PostcodeCodeSW1P 2AL</rsm:PostcodeCode>
</rsm:Address>
rsm:TelephoneCommunication
rsm:CompleteNumberTextChange Me</rsm:CompleteNumberText>
</rsm:TelephoneCommunication>
rsm:FaxCommunication
rsm:CompleteNumberTextChange Me</rsm:CompleteNumberText>
</rsm:FaxCommunication>
rsm:EmailCommunication
rsm:EmailURIIDprtr@defra.gsi.gov.uk</rsm:EmailURIID>
</rsm:EmailCommunication>
rsm:ContactPersonNameNational Data Manager</rsm:ContactPersonName>
</rsm:CompetentAuthorityParty>
rsm:CompetentAuthorityParty
rsm:NameE10</rsm:Name>
rsm:Address
rsm:StreetNameAll correspondence to: Defra, Industrial Pollution, 5F Ergon House, Horseferry Rd. London.</rsm:StreetName>
rsm:CityNameLondon</rsm:CityName>
rsm:PostcodeCodeSW1P 2AL</rsm:PostcodeCode>
</rsm:Address>
rsm:TelephoneCommunication
rsm:CompleteNumberTextChange Me</rsm:CompleteNumberText>
</rsm:TelephoneCommunication>
rsm:FaxCommunication
rsm:CompleteNumberTextChange Me</rsm:CompleteNumberText>
</rsm:FaxCommunication>
rsm:EmailCommunication
rsm:EmailURIIDprtr@defra.gsi.gov.uk</rsm:EmailURIID>
</rsm:EmailCommunication>
rsm:ContactPersonNameNational Data Manager</rsm:ContactPersonName>

步骤1：定位多余条目根源 #

原代码全局搜索节点，可能存在某个FacilityName不属于目标设施层级（比如嵌套在其他无关节点下）。正确的做法是先锁定所有设施的父节点（通常是rsm:Facility），再从每个设施节点内提取字段，保证每条记录的字段数量一致。

步骤2：按设施节点批量提取数据 #

替换原循环逻辑，改为基于设施父节点的批量提取：

library(xml2)
library(dplyr)
library(purrr)
library(tidyr)

xml_file <- read_xml('uk_prtr_dataset_2021.xml')

# 获取所有设施节点
facilities <- xml_find_all(xml_file, ".//rsm:Facility")

# 定义需提取的字段及对应XPath
fields <- c(
  ParentCompanyName = ".//rsm:ParentCompanyName",
  FacilityName = ".//rsm:FacilityName",
  LongitudeMeasure = ".//rsm:LongitudeMeasure",
  LatitudeMeasure = ".//rsm:LatitudeMeasure",
  MainEconomicActivityName = ".//rsm:MainEconomicActivityName"
)

# 遍历每个设施节点，提取字段并合并为数据框
prtr_data <- map_dfr(facilities, function(facility) {
  map_dfc(fields, function(xpath) {
    xml_find_first(facility, xpath) %>% 
      xml_text() %>% 
      as_tibble_col(column_name = names(xpath))
  })
})

# 检查行数，应与其他字段的6305一致
nrow(prtr_data)

步骤3：手动定位并删除异常条目 #

如果按设施提取后仍有问题，可以通过父节点路径排查异常的FacilityName：

# 提取所有FacilityName及其父节点路径
all_facility_names <- xml_find_all(xml_file, ".//rsm:FacilityName") %>%
  tibble(
    name = xml_text(.),
    parent_path = xml_path(xml_parent(.))
  )

# 筛选出父节点不是rsm:Facility的异常条目
filter(all_facility_names, !str_detect(parent_path, "rsm:Facility"))

# 删除该异常条目对应的XML节点
xml_find_all(xml_file, ".//rsm:FacilityName[not(xml_parent(.)/self::rsm:Facility)]") %>% 
  xml_remove()

# 重新提取数据即可

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