Skip to main content
 首页 » 编程设计

python之为 datex2 到 Pandas Dataframe 的转换创建 XSLT 文件

2024年11月24日10kerrycode

我正在尝试遵循此答案中给出的 DatexII 到 pandas Dataframe 转换的解决方案:https://stackoverflow.com/a/47357282/5449497

但我不知道如何设置所需的 xslt 文件。

我的 xml 文件如下所示:

<?xml version='1.0' encoding='UTF-8'?> 
<d2LogicalModel modelBaseVersion="2" xmlns="http://datex2.eu/schema/2/2_0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://datex2.eu/schema/2/2_0 http://bast.s3.amazonaws.com/schema/1412764802683/DATEXII_DaV-MDM-001_dyn.xsd" xsi:type="D2LogicalModel"> 
    <exchange> 
        <supplierIdentification> 
            <country>de</country> 
            <nationalIdentifier>DE-MDM-Landesbetrieb Straßenbau NRW, Verkehrszentrale</nationalIdentifier> 
        </supplierIdentification> 
    </exchange> 
    <payloadPublication lang="DE" xsi:type="ElaboratedDataPublication"> 
        <publicationTime>2018-02-17T23:59:42.364+01:00</publicationTime> 
        <publicationCreator> 
            <country>de</country> 
            <nationalIdentifier>DE-MDM-Landesbetrieb Straßenbau NRW, Verkehrszentrale</nationalIdentifier> 
        </publicationCreator> 
        <periodDefault>60.0</periodDefault> 
        <timeDefault>2018-02-17T23:59:42.364+01:00</timeDefault> 
        <headerInformation> 
            <confidentiality>noRestriction</confidentiality> 
            <informationStatus>real</informationStatus> 
        </headerInformation> 
        <referenceSettings> 
            <predefinedNonOrderedLocationGroupReference id="dav.nw.mq" targetClass="PredefinedNonOrderedLocationGroup" version="201610261425"/> 
        </referenceSettings> 
        <elaboratedData> 
            <basicData xsi:type="TrafficFlow"> 
                <pertinentLocation xsi:type="LocationByReference"> 
                    <predefinedLocationReference id="mq.MQ_A1.0816_HFB_SW" targetClass="PredefinedLocation" version="201610261425"/> 
                </pertinentLocation> 
                <forVehiclesWithCharacteristicsOf> 
                    <vehicleType>car</vehicleType> 
                </forVehiclesWithCharacteristicsOf> 
                <vehicleFlow> 
                    <vehicleFlowRate>600</vehicleFlowRate> 
                </vehicleFlow> 
            </basicData> 
        </elaboratedData> 
        <elaboratedData> 
            <basicData xsi:type="TrafficFlow"> 
                <pertinentLocation xsi:type="LocationByReference"> 
                    <predefinedLocationReference id="mq.MQ_A1.0816_HFB_SW" targetClass="PredefinedLocation" version="201610261425"/> 
                </pertinentLocation> 
                <forVehiclesWithCharacteristicsOf> 
                    <vehicleType>lorry</vehicleType> 
                </forVehiclesWithCharacteristicsOf> 
                <vehicleFlow> 
                    <vehicleFlowRate>0</vehicleFlowRate> 
                </vehicleFlow> 
            </basicData> 
        </elaboratedData> 
        <elaboratedData> 
            <basicData xsi:type="TrafficSpeed"> 
                <pertinentLocation xsi:type="LocationByReference"> 
                    <predefinedLocationReference id="mq.MQ_A1.0816_HFB_SW" targetClass="PredefinedLocation" version="201610261425"/> 
                </pertinentLocation> 
                <forVehiclesWithCharacteristicsOf> 
                    <vehicleType>car</vehicleType> 
                </forVehiclesWithCharacteristicsOf> 
                <averageVehicleSpeed> 
                    <speed>108.0</speed> 
                </averageVehicleSpeed> 
            </basicData> 
        </elaboratedData> 
    </payloadPublication> 
</d2LogicalModel> 

我的 Jupyter Notebook Python 代码如下所示:

from io import StringIO 
import lxml.etree as et 
import pandas as pd 
 
# LOAD XML AND XSL FILES 
doc = et.parse('/home/User/Desktop/DataTest/traffic.xml') 
xsl = et.parse('/home/User/Desktop/DataTest/traffic.xsl') 
 
# INITIALIZE AND RUN TRANSFORMATION 
transform = et.XSLT(xsl) 
# CONVERT RESULT TO STRING  
result = str(transform(doc)) 
 
# IMPORT INTO DATAFRAME 
df = pd.read_csv(StringIO(result)) 

到目前为止,我得到了以下 XSLT (traffic.xsl):

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                              xmlns:pub="http://datex2.eu/schema/2/2_0" 
                              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                              xsi:type="D2LogicalModel"> 
  <xsl:output method="text"/> 
  <xsl:strip-space elements="*"/> 
 
  <xsl:template match="d2LogicalModel"> 
    <xsl:apply-templates select="pub:payloadPublication"/> 
  </xsl:template> 
 
  <xsl:template match="pub:payloadPublication"> 
    <xsl:apply-templates select="pub:elaboratedData"/> 
  </xsl:template> 
 
 
  <xsl:template match="pub:elaboratedData"> 
    <xsl:value-of select="concat(ancestor::pub:payloadPublication/pub:publicationTime,',', 
                                 ancestor::pub:payloadPublication/ 
                                 pub:elaboratedData/pub:basicData/@xsi:type,',', 
                                 descendant::pub:vehicleFlowRate,',', 
                                 descendant::pub:averageVehicleSpeed/@numberOfInputValuesUsed,',', 
                                 descendant::pub:speed)"/><xsl:text>&#xa;</xsl:text>     
  </xsl:template> 
 
</xsl:stylesheet> 

我得到以下输出:

deDE-MDM-Landesbetrieb Straßenbau NRW   Verkehrszentrale2018-02-17T23:59:42.364+01:00   TrafficFlow 600 Unnamed: 4  Unnamed: 5 
 
0   2018-02-17T23:59:42.364+01:00   TrafficFlow 0.0 NaN NaN NaN 
 
1   2018-02-17T23:59:42.364+01:00   TrafficFlow 600.0   NaN NaN NaN 

我不知道如何创建列名以及如何获取所需的数据作为输出:

publicationTime predefinedLocationReference vehicleType vehicleFlowRate speed 
 
2018-02-17T23:59:42.364+01:00 mq.MQ_A1.0816_HFB_SW lorry 0 NaN 
2018-02-17T23:59:42.364+01:00 mq.MQ_A1.0816_HFB_SW anyvehicle 600 NaN 
2018-02-17T23:59:42.364+01:00 mq.MQ_A1.0816_HFB_SW car NaN 108.0 

如有任何帮助,我们将不胜感激。

请您参考如下方法:

快到了!只需在标题的父模板中放置一个逗号分隔的列表,对于任何特定元素或属性值,使用 ancestor::* 用于高于 elaboratedData 和 descendant::的级别* 对于以下级别:

XSLT

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                              xmlns:pub="http://datex2.eu/schema/2/2_0" 
                              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                              xsi:type="D2LogicalModel"> 
  <xsl:output method="text"/> 
  <xsl:strip-space elements="*"/> 
 
  <xsl:template match="pub:d2LogicalModel"> 
    <xsl:apply-templates select="pub:payloadPublication"/> 
  </xsl:template> 
 
  <xsl:template match="pub:payloadPublication"> 
    <!-- HEADERS -->         
    <xsl:text>publicationTime,predefinedLocationReference,vehicleType,vehicleFlowRate,speed</xsl:text> 
    <xsl:text>&#xa;</xsl:text>     <!-- LINE BREAK --> 
    <xsl:apply-templates select="pub:elaboratedData"/> 
  </xsl:template>   
 
  <xsl:template match="pub:elaboratedData"> 
    <!-- ROWS -->  
    <xsl:value-of select="concat(ancestor::pub:payloadPublication/pub:publicationTime,',', 
                                 descendant::pub:predefinedLocationReference/@id,',', 
                                 descendant::pub:vehicleType,',', 
                                 descendant::pub:vehicleFlowRate,',', 
                                 descendant::pub:speed)"/> 
    <xsl:text>&#xa;</xsl:text>     <!-- LINE BREAK -->    
  </xsl:template> 
 
</xsl:stylesheet> 

XSLT Demo

CSV 输出

publicationTime,predefinedLocationReference,vehicleType,vehicleFlowRate,speed 
2018-02-17T23:59:42.364+01:00,mq.MQ_A1.0816_HFB_SW,car,600, 
2018-02-17T23:59:42.364+01:00,mq.MQ_A1.0816_HFB_SW,lorry,0, 
2018-02-17T23:59:42.364+01:00,mq.MQ_A1.0816_HFB_SW,car,,108.0