XML檔案到pandasDataFrame-有解無憂

我在將 XML 轉換為 DataFrame 時遇到問題。我有以下示例 XML：

<Fruits>
    <Fruit ReferenceDate="2022-09-22"
                FruitName="Apple">
        <Identifier FruitIdentifier="111"
                    FruitBrand="GoldenApple"/>
        <FruitInformation Country="Turkey"
                          Colour="Green"/>
        <CompanyInformation CompanyName="GlobalFruits"
                            Location="USA"/>
        <Languages>
            <LanguageDependent CountryId="GB"
                               LanguageId="EN">
                <FreeText1>Sample sentence 1.</FreeText1>
                <FreeText2>Sample sentence 2.</FreeText2>
            </LanguageDependent>
        </Languages>
    </Fruit>
    <Fruit ReferenceDate="2022-09-22"
                FruitName="Orange">
        <Identifier FruitIdentifier="222"
                    FruitBrand="BestOrange"/>
        <FruitInformation Country="Egypt"
                          Colour="Orange"/>
        <CompanyInformation CompanyName="FreshFood"
                            Location="UK"/>
        <Languages>
            <LanguageDependent CountryId="GB"
                               LanguageId="EN">
                <FreeText1>Sample sentence 3.</FreeText1>
                <FreeText2>Sample sentence 4.</FreeText2>
            </LanguageDependent>
        </Languages>
    </Fruit>
</Fruits>

我想將其轉換為 DataFrame。決賽桌應如下圖所示： XML 檔案到 pandas DataFrame

如果這是一個重復的問題，我很抱歉，但我沒有找到適合我的解決方案。

到目前為止，我有以下代碼：

import pandas as pd 
import xml.etree.ElementTree as et 

xtree = et.parse("fruits.xml")
xroot = xtree.getroot() 

df_cols = ["ReferenceDate", "FruitName", "FruitIdentifier",
           "FruitBrand", "Country", "Colour", "CompanyName",
           "Location", "CountryId", "LanguageId"]
rows = []

for node in xroot.iter(): 
    ReferenceDate = node.attrib.get("ReferenceDate")
    FruitName = node.attrib.get("FruitName")
    FruitIdentifier = node.attrib.get("FruitIdentifier")
    FruitBrand = node.attrib.get("FruitBrand")
    Country = node.attrib.get("Country")
    Colour = node.attrib.get("Colour")
    CompanyName = node.attrib.get("CompanyName")
    Location = node.attrib.get("Location")
    CountryId = node.attrib.get("CountryId")
    LanguageId = node.attrib.get("LanguageId")
    
    rows.append({"ReferenceDate": ReferenceDate, "FruitName": FruitName, 
                  "FruitIdentifier": FruitIdentifier, "FruitBrand": FruitBrand,
                  "Country": Country, "Colour": Colour, "CompanyName": CompanyName, "Location": Location,
                  "CountryId": CountryId, "LanguageId": LanguageId})

out_df = pd.DataFrame(rows, columns = df_cols)

我有兩個主要問題：

無法獲取文本（FreeText1 和 FreeText2）；
子查詢中的每組屬性都有自己的行。

uj5u.com熱心網友回復：

以下作品

import pandas as pd
import xml.etree.ElementTree as ET

xml = '''<Fruits>
    <Fruit ReferenceDate="2022-09-22"
                FruitName="Apple">
        <Identifier FruitIdentifier="111"
                    FruitBrand="GoldenApple"/>
        <FruitInformation Country="Turkey"
                          Colour="Green"/>
        <CompanyInformation CompanyName="GlobalFruits"
                            Location="USA"/>
        <Languages>
            <LanguageDependent CountryId="GB"
                               LanguageId="EN">
                <FreeText1>Sample sentence 1.</FreeText1>
                <FreeText2>Sample sentence 2.</FreeText2>
            </LanguageDependent>
        </Languages>
    </Fruit>
    <Fruit ReferenceDate="2022-09-22"
                FruitName="Orange">
        <Identifier FruitIdentifier="222"
                    FruitBrand="BestOrange"/>
        <FruitInformation Country="Egypt"
                          Colour="Orange"/>
        <CompanyInformation CompanyName="FreshFood"
                            Location="UK"/>
        <Languages>
            <LanguageDependent CountryId="GB"
                               LanguageId="EN">
                <FreeText1>Sample sentence 3.</FreeText1>
                <FreeText2>Sample sentence 4.</FreeText2>
            </LanguageDependent>
        </Languages>
    </Fruit>
</Fruits>'''

root = ET.fromstring(xml)
rows = []

for f in root.findall('.//Fruit'):
    ReferenceDate = f.attrib.get("ReferenceDate")
    FruitName = f.attrib.get("FruitName")
    FruitIdentifier = f.find("Identifier").attrib.get("FruitIdentifier")
    FruitBrand = f.find("Identifier").attrib.get("FruitBrand")
    Country = f.find("FruitInformation").attrib.get("Country")
    Colour = f.find("FruitInformation").attrib.get("Colour")
    CompanyName = f.find("CompanyInformation").attrib.get("CompanyName")
    Location = f.find("CompanyInformation").attrib.get("Location")
    CountryId = f.find(".//LanguageDependent").attrib.get("CountryId")
    LanguageId = f.find(".//LanguageDependent").attrib.get("LanguageId")
    FreeText1 = f.find(".//LanguageDependent/FreeText1").text
    FreeText2 = f.find(".//LanguageDependent/FreeText2").text

    rows.append({"ReferenceDate": ReferenceDate, "FruitName": FruitName,
                 "FruitIdentifier": FruitIdentifier, "FruitBrand": FruitBrand,
                 "Country": Country, "Colour": Colour, "CompanyName": CompanyName, "Location": Location,
                 "CountryId": CountryId, "LanguageId": LanguageId, "FreeText1": FreeText1,
                 "FreeText2": FreeText2})

df = pd.DataFrame(rows)
print(df)

輸出

ReferenceDate FruitName  ...           FreeText1           FreeText2
0    2022-09-22     Apple  ...  Sample sentence 1.  Sample sentence 2.
1    2022-09-22    Orange  ...  Sample sentence 3.  Sample sentence 4.

[2 rows x 12 columns]

uj5u.com熱心網友回復：

雖然您的 XML 對于單個來說不夠淺pandas.read_xml，但所需的資料對于可以水平合并的多個呼叫來說足夠一致：

fruits_df = (
    pd.concat([
        pd.read_xml("fruits.xml", xpath=".//Fruit", attrs_only = True, parser="etree"),
        pd.read_xml("fruits.xml", xpath=".//Identifier", attrs_only = True, parser="etree"),
        pd.read_xml("fruits.xml", xpath=".//FruitInformation", attrs_only = True, parser="etree"),
        pd.read_xml("fruits.xml", xpath=".//CompanyInformation", attrs_only = True, parser="etree"),
        pd.read_xml("fruits.xml", xpath=".//LanguageDependent", parser="etree")
       ], 
       axis=1
    )
)

或者，通過串列理解：

fruits_df = (
    pd.concat(
        [
            pd.read_xml("fruits.xml", xpath=f".//{elem}", parser="etree")
            for elem in
            ["Fruit", "Identifier", "FruitInformation", "CompanyInformation", "LanguageDependent"]
       ], 
       axis=1
    ).dropna(axis="columns")
)

輸出

  ReferenceDate FruitName  FruitIdentifier   FruitBrand Country  Colour   CompanyName Location CountryId LanguageId           FreeText1           FreeText2
0    2022-09-22     Apple              111  GoldenApple  Turkey   Green  GlobalFruits      USA        GB         EN  Sample sentence 1.  Sample sentence 2.
1    2022-09-22    Orange              222   BestOrange   Egypt  Orange     FreshFood       UK        GB         EN  Sample sentence 3.  Sample sentence 4.

uj5u.com熱心網友回復：

更短更通用的實作：

import xml.etree.ElementTree as ET
import pandas as pd

root = ET.parse("fruits.xml")

rows = []
for fruit in root:  # <Fruit> is child node of root node <Fruits>
    row = {}
    for node in fruit.iter():
        row.update(node.attrib)
        if node.text and not node.text.isspace():
            row[node.tag] = node.text
    rows.append(row)
            
df = pd.DataFrame(rows)

此實作使用下一個邏輯：

我們將每個子節點的屬性添加到row字典中；
如果當前節點有一些非空白文本，我們將這些文本寫入row使用標簽名稱作為鍵。

你可以幫助我的國家，查看我的個人資料資訊。

uj5u.com熱心網友回復：

嘗試以下 powershell 腳本：

using assembly System 
using assembly System.Xml.Linq 
using assembly System.Data

$dt = New-Object System.Data.DataTable("Fruits")
$dt.Columns.Add("ReferenceDate", [DateTime]) | Out-Null
$dt.Columns.Add("FruitName", [string]) | Out-Null
$dt.Columns.Add("FruitIdentifier", [string]) | Out-Null
$dt.Columns.Add("FruitBrand", [string]) | Out-Null
$dt.Columns.Add("Country", [string]) | Out-Null
$dt.Columns.Add("Colour", [string]) | Out-Null
$dt.Columns.Add("CompanyName", [string]) | Out-Null
$dt.Columns.Add("Location", [string]) | Out-Null
$dt.Columns.Add("CountryId", [string]) | Out-Null
$dt.Columns.Add("LanguageId", [string]) | Out-Null
$dt.Columns.Add("FreeText1", [string]) | Out-Null
$dt.Columns.Add("FreeText2", [string]) | Out-Null

$Filename = "c:\temp\test.xml"
$xDoc = [System.Xml.Linq.XDocument]::Load($Filename)
$fruits = $xDoc.Descendants("Fruit")

foreach($fruit in $fruits)
{
   $row = $dt.NewRow()
   $dt.Rows.Add($row)

   $date = [DateTime]$fruit.Attribute("ReferenceDate")
   $row.ReferenceDate = $date

   $name = $fruit.Attribute("FruitName").Value
   $row.FruitName = $name

   $identifier = $fruit.Element("Identifier")
   $fruitIdentifier = $identifier.Attribute("FruitIdentifier").Value
   $row.FruitIdentifier = $fruitIdentifier
   $fruitBrand = $identifier.Attribute("FruitBrand").Value
   $row.FruitBrand = $fruitBrand
   
   $fruitInformation = $fruit.Element("FruitInformation")
   $country = $fruitInformation.Attribute("Country").Value
   $row.country = $country
   $colour = $fruitInformation.Attribute("Colour").Value
   $row.Colour = $colour
   
   $companyInformation = $fruit.Element("CompanyInformation")
   $companyName = $companyInformation.Attribute("CompanyName").Value
   $row.CompanyName = $companyName
   $location = $companyInformation.Attribute("Location").Value
   $row.Location = $location

   $languageDependent = $fruit.Descendants("LanguageDependent")
   $countryId = $languageDependent.Attribute("CountryId").Value
   $row.CountryId = $countryId
   $languageId = $languageDependent.Attribute("LanguageId").Value
   $row.languageId = $languageId
   $freeText1 = $languageDependent.Element("FreeText1").Value
   $row.FreeText1 = $freeText1
   $freeText2 = $languageDependent.Element("FreeText2").Value
   $row.FreeText2 = $freeText2


 
}
# convert the datatable to an array of strings
$table = $dt | Format-Table | Out-String -Stream

foreach ($row in $table) {
   Write-Host $row
}

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/512765.html

標籤：Pythonxml数据框元素树

上一篇：使用xQuery更新XML中的第3級深度欄位值

下一篇：如何從R中的xml檔案行中提取第二個屬性