python - 如何将 XML 文件读入 Pandas Dataframe

Question

我有一个 xml 文件:“product.xml”，我想使用 pandas 读取它，这里是示例文件的示例:

<?xml version="1.0"?>
 <Rowset>
  <ROW>
   <Product_ID>32</Product_ID>
   <Company_ID>2</Company_ID>
   <User_ID>90</User_ID>
   <Product_Type>1</Product_Type>
   <Application_ID>BBC#:1010</Application_ID>
  </ROW>
  <ROW>
   <Product_ID>22</Product_ID>
   <Company_ID>4</Company_ID>
   <User_ID>190</User_ID>
   <Product_Type>2</Product_Type>
   <Application_ID>NBA#:1111</Application_ID>
  </ROW>
  <ROW>
   <Product_ID>63</Product_ID>
   <Company_ID>4</Company_ID>
   <User_ID>99</User_ID>
   <Product_Type>1</Product_Type>
   <Application_ID>BBC#:1212</Application_ID>
  </ROW>
  <ROW>
   <Product_ID>22</Product_ID>
   <Company_ID>2</Company_ID>
   <User_ID>65</User_ID>
   <Product_Type>2</Product_Type>
   <Application_ID>NBA#:2210</Application_ID>
  </ROW>

我正在尝试使用 pandas 生成二维表，例如:

Application ID Product Type  Product ID
BBC#:1010            1         32
NBA#:1111            2         22
BBC#:1212            1         63  
NBA#:2210            2         22   

到目前为止，我已经尝试过这段代码:

import xml.etree.cElementTree as ET
import pandas as pd
import pandas_read_xml as pdx
df = pdx.read_xml('product.xml')
path='product.xml'
dfcols = ['Application_ID', 'Product_Type', 'Product_ID']
root = et.parse(path)
rows = root.findall('.//ROW')
#NESTED LIST
xml_data = [[row.get('Application_ID'), row.get('Product_Type'), row.get('Product_ID')] for row in rows]
df_xml = pd.DataFrame(xml_data, columns=dfcols)

print(df_xml)

这种二维表怎么打印啊，求助，谢谢。

Answer 1

使用 [] 过滤和重组列:

cols = ['Application_ID', 'Product_Type', 'Product_ID']
df = pd.read_xml('product.xml')[cols]
print(df)

# Output:
  Application_ID  Product_Type  Product_ID
0      BBC#:1010             1          32
1      NBA#:1111             2          22
2      BBC#:1212             1          63
3      NBA#:2210             2          22

如果您想用 ' ' 替换列名中的 '_':

df.columns = df.columns.str.replace('_', ' ')
print(df)

# Output:
  Application ID  Product Type  Product ID
0      BBC#:1010             1          32
1      NBA#:1111             2          22
2      BBC#:1212             1          63
3      NBA#:2210             2          22