XML Parsing Atributes and Extracting Nodes in XML pyhton

Bryam Williams Hirsch Trujillo 0
Viewed 33

I have the following xml file and I want to extract the Score Node into a pandas DF since Im loading data into my Data Lake.

But I would like to create a 4 column DF as following.

IDOPERATION 1648853
SCORE 700
PI 8.42
EXCLUSION CA

<CUSTOMERDETAIL_APCINFO BATCHID="2022090606">
    <OPERATION IDOPERATION="1648853">
        <NODO_SCORE_APC>
            <Score>
                <Score>
                    <SCORE>700</SCORE>
                    <PI>8.42</PI>
                    <EXCLUSION>CA</EXCLUSION>
                </Score>
            </Score>
        </NODO_SCORE_APC>
    </OPERATION>

Im getting the Score Node correctly but I dont know how to get the Attribute in the same DF. This is my Code

df_cols_score = ["SCORE", "PI", "EXCLUSION"]
prueba_score = []

node_atr = root.findall("./OPERATION/NODO_SCORE_APC/Score/Score")
for elm in node_atr:
    s_score = elm.findtext("SCORE")
    s_pi = elm.findtext("PI")
    s_exclusion = elm.findtext("EXCLUSION")
        
    prueba_score.append({"SCORE": s_score, "PI": s_pi, "EXCLUSION": s_exclusion})
        
df_cols_score = pd.DataFrame(rows_score, columns=df_cols_score)

df_cols_score.head()

I would appriciate the help.

xml-parsing xml python
1 Answers

After fixing the XML we can get something like the below

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

xml = '''
   <CUSTOMERDETAIL_APCINFO BATCHID="2022090606">
    <OPERATION IDOPERATION="1648853">
        <NODO_SCORE_APC>
            <Score>
                <Score>
                    <SCORE>700</SCORE>
                    <PI>8.42</PI>
                    <EXCLUSION>CA</EXCLUSION>
                </Score>
            </Score>
        </NODO_SCORE_APC>
    </OPERATION>
    <OPERATION IDOPERATION="164885p">
        <NODO_SCORE_APC>
            <Score>
                <Score>
                    <SCORE>600</SCORE>
                    <PI>5.52</PI>
                    <EXCLUSION>TK</EXCLUSION>
                </Score>
            </Score>
        </NODO_SCORE_APC>
    </OPERATION>
 </CUSTOMERDETAIL_APCINFO>
'''

root = ET.fromstring(xml)
data = []
operations = root.findall('.//OPERATION')
for oper in operations:
    _id = oper.attrib['IDOPERATION']
    score = oper.find('./NODO_SCORE_APC/Score/Score')
    entry = {e.tag: e.text for e in score}
    entry['IDOPERATION'] = _id
    data.append(entry)

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

output

  SCORE    PI EXCLUSION IDOPERATION
0   700  8.42        CA     1648853
1   600  5.52        TK     164885p
balderman0
Related