Values in the repeated tags in the XML file are coming as dictionary objects in pandas data frame

sklal 0
Viewed 29

I'm trying to create a data frame out of values in the XML tags in multiple XML files. While the code is working where the tags are not repeated, when they do repeat, I'm seeing values as doctionary values such as {'Michael','James',' '} for a column say FName, {'', ' '} for MName and {'Smith', ' '} for LName among others under the data frame columns. What should be the approach to fix this issue in the code?

def parse_XML(list_of_trees, df_cols):

    def get_el(el_list):
        if len(el_list) > 1:
            return [el_text.text for el_text in el_list]
        else:
            return el_list[0].text
    rows = []
    for tree in list_of_trees:
        xroot = tree.getroot()

        res = {}
        for node in xroot:
            for el in df_cols[0:]:
                if node is not None and node.find(f".//{el}") is not None:
                    el_res = get_el(node.findall(f".//{el}"))
                    if el not in res:
                        res[el] = el_res
                    elif type(res[el]) == list:
                        res[el].extend(el_res)
                    else:
                        res[el] = [res[el], el_res]
        rows.append(res)

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

    return out_df

XML example:

<PD>
  <Clt>
    <PType>xxxx</PType>
    <PNumber>xxxxx</PNumber>
    <UID>xxxx</UID>
    <TEfd>xxxxx</TEfd>
    <TExd>xxxxxx</TExd>
    <DID>xxxxx</DID>
    <CType>xxxxx</CType>
    <FName>Michael</FName>
    <MName></MName>
    <LName>Smith</LName>
    <FName>James</FName>
    <MName> </MName>
    <LName> </LName>
    <MAL>Home</MAL>
    <AddressLine1>xxxx</AddressLine1>
    <AddressLine2>xxxx</AddressLine2>
    <AddressLine3></AddressLine3>
    <City>xxxx</City>
    <State>xx</State>
    <ZipCode>xxxx</ZipCode>
    <Country>xxxx</Country>
    <Pr>
      <PrType>xxxxx</PrType>
      <PrName>xxxxxx</PrName>
      <PrID>xxxxxx</PrID>
    </Pr>
</Clt>
  <CData>
    <InceptionYear>2021</InceptionYear>
    <FName> </FName>
  </CData>
</PD>

Current output

PNumber FName               MName     LName
xxxx    {Michael,James,''} {'',' '}   {Smith,''}

Expected Output

PNumber      FName          MName     LName
    xxxx    Michael                   Smith
    xxxx    James                          
    xxxx                    NULL      NULL
xml pandas python
1 Answers

Given your XML is relatively shallow, consider the new IO method, pandas.read_xml (introduced in v1.3). The method even includes a names argument to rename same named elements.

out_df = pd.read_xml(
    "input.xml",  # OR xroot.tostring()
    xpath = ".//Clt",
    names = [
        "PType",
        "PNumber",
        "UID",
        "TEfd",
        "TExd",
        "DID",
        "CType",
        "FName1",
        "MName1",
        "LName1",
        "FName2",
        "MName2",
        "LName2",
        "MAL",
        "AddressLine1",
        "AddressLine2",
        "AddressLine3",
        "City",
        "State",
        "ZipCode",
        "Country",
        "PR"
    ]
)
Parfait0
Related