如何将XML文件转换为R Data Frame并提取datetime属性？

没问题，我帮你搞定这个XML转DataFrame的需求，特别是提取那些datetime属性。在R里我们可以用xml2包来解析XML，再配合tidyverse工具链来整理数据，下面是完整的示例代码和步骤说明：

1. 安装并加载必要的包 #

首先确保你安装了所需的包，如果没有的话先安装：

install.packages(c("xml2", "tidyverse", "lubridate"))

然后加载包：

library(xml2)
library(tidyverse)
library(lubridate) # 用来转换datetime格式

2. 读取XML数据 #

你可以把XML内容保存为本地文件，或者直接用字符串传入。这里我们直接用你提供的XML字符串示例：

xml_content <- '<?xml version="1.0" encoding="UTF-8"?> <Group snapshotTime="2018-05-30T19:33:44.352Z"> <Links> <rel>self</rel> <href>https:cloud.com/Group/1</href> </Links> <Links> <rel>last</rel> <href>https:cloud.com/Group/6</href> </Links> <Links> <rel>next</rel> <href>https:cloud.com/Group/2</href> </Links> <Equipment> <EquipmentHeader> <Name>CASE IH</Name> <Model>1100</Model> <EquipmentID> Desk</EquipmentID> <SerialNumber>1231</SerialNumber> <PIN>123</PIN> </EquipmentHeader> <Location datetime="2012-06-25T11:14:54.000Z"> <Latitude>12.573722</Latitude> <Longitude>-45.515805</Longitude> </Location> <Ophrs datetime="2012-03-01T17:42:37.000Z"> <Hour>1968.80</Hour> </Ophrs> </Equipment> <Equipment> <EquipmentHeader> <Name>CALL</Name> <Model>L2048</Model> <EquipmentID>1MM772GP4</EquipmentID> <SerialNumber>1TT772GPVJF688214</SerialNumber> <PIN>1TT772G4</PIN> </EquipmentHeader> <Location datetime="2018-05-30T19:22:46.000Z"> <Latitude>15.518556</Latitude> <Longitude>-55.422444</Longitude> </Location> <CumulativeIdleHours datetime="2018-05-30T19:02:46.000Z"> <Hour>14.74</Hour> </CumulativeIdleHours> <Ophrs datetime="2018-05-30T19:22:48.000Z"> <Hour>52.35</Hour> </Ophrs> <Distance datetime="2018-05-30T19:02:46.000Z"> <OdometerUnits>kilometre</OdometerUnits> <Odometer>130.9</Odometer> </Distance> <FuelUsed datetime="2018-05-30T19:02:46.000Z"> <FuelUnits>litre</FuelUnits> <FuelConsumed>395</FuelConsumed> </FuelUsed> </Equipment> </Group>'

# 解析XML文档
doc <- read_xml(xml_content)

3. 提取数据并构建DataFrame #

我们会遍历每个<Equipment>节点，提取设备头部信息、各个带datetime属性的子节点数据，最后合并成一个结构化的DataFrame：

equipment_df <- doc %>%
  xml_find_all("//Equipment") %>% # 定位所有设备节点
  map_df(function(equip) {
    # 1. 提取设备基础信息
    header <- equip %>% xml_find_first("EquipmentHeader")
    header_info <- tibble(
      Name = xml_find_first(header, "Name") %>% xml_text(),
      Model = xml_find_first(header, "Model") %>% xml_text(),
      EquipmentID = xml_find_first(header, "EquipmentID") %>% xml_text(),
      SerialNumber = xml_find_first(header, "SerialNumber") %>% xml_text(),
      PIN = xml_find_first(header, "PIN") %>% xml_text()
    )
    
    # 2. 提取Location信息（含datetime属性）
    location <- equip %>% xml_find_first("Location")
    location_info <- tibble(
      Location_datetime = xml_attr(location, "datetime"),
      Latitude = xml_find_first(location, "Latitude") %>% xml_text() %>% as.numeric(),
      Longitude = xml_find_first(location, "Longitude") %>% xml_text() %>% as.numeric()
    )
    
    # 3. 提取Ophrs信息（含datetime属性）
    ophrs <- equip %>% xml_find_first("Ophrs")
    ophrs_info <- tibble(
      Ophrs_datetime = xml_attr(aphrs, "datetime"),
      Ophrs_Hour = xml_find_first(aphrs, "Hour") %>% xml_text() %>% as.numeric()
    )
    
    # 4. 提取CumulativeIdleHours（处理部分设备无此节点的情况）
    idle_hours <- equip %>% xml_find_first("CumulativeIdleHours")
    idle_info <- if (!is.na(idle_hours)) {
      tibble(
        CumulativeIdleHours_datetime = xml_attr(idle_hours, "datetime"),
        CumulativeIdleHours_Hour = xml_find_first(idle_hours, "Hour") %>% xml_text() %>% as.numeric()
      )
    } else {
      tibble(CumulativeIdleHours_datetime = NA, CumulativeIdleHours_Hour = NA)
    }
    
    # 5. 提取Distance信息（处理部分设备无此节点的情况）
    distance <- equip %>% xml_find_first("Distance")
    distance_info <- if (!is.na(distance)) {
      tibble(
        Distance_datetime = xml_attr(distance, "datetime"),
        Distance_Units = xml_find_first(distance, "OdometerUnits") %>% xml_text(),
        Distance_Odometer = xml_find_first(distance, "Odometer") %>% xml_text() %>% as.numeric()
      )
    } else {
      tibble(Distance_datetime = NA, Distance_Units = NA, Distance_Odometer = NA)
    }
    
    # 6. 提取FuelUsed信息（处理部分设备无此节点的情况）
    fuel_used <- equip %>% xml_find_first("FuelUsed")
    fuel_info <- if (!is.na(fuel_used)) {
      tibble(
        FuelUsed_datetime = xml_attr(fuel_used, "datetime"),
        FuelUsed_Units = xml_find_first(fuel_used, "FuelUnits") %>% xml_text(),
        FuelUsed_Consumed = xml_find_first(fuel_used, "FuelConsumed") %>% xml_text() %>% as.numeric()
      )
    } else {
      tibble(FuelUsed_datetime = NA, FuelUsed_Units = NA, FuelUsed_Consumed = NA)
    }
    
    # 合并所有信息为一行
    bind_cols(header_info, location_info, ophrs_info, idle_info, distance_info, fuel_info)
  })

# 将字符串格式的datetime转换为R的datetime类型
equipment_df <- equipment_df %>%
  mutate(
    across(ends_with("_datetime"), ymd_hms)
  )

4. 查看最终结果 #

运行print(equipment_df)就能看到结构化的DataFrame，所有datetime属性都被成功提取并转换为可操作的datetime类型。

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