[Python] Extracting data of this xml to Python dataframe

Discussão em 'Python' iniciado por Stack, Janeiro 27, 2025.

  1. Stack

    Stack Membro Participativo

    I cannot get the extraction of this xml data to dataframe to work properly. This is my xml sample. In reality I have multiple stacks of "Entity" which represent one line of data in the desired result. To save space I only pasted one instance of "Entity" here:

    <EntityCollection>

    <Entities>

    <Entity>

    <Attributes>

    <KeyValuePairOfstringanyType>

    <key>client_number</key>

    <value type="b:string">ABC123345</value>

    </KeyValuePairOfstringanyType>

    <KeyValuePairOfstringanyType>

    <key>my_data.client_type</key>

    <value type="AliasedValue">

    <AttributeLogicalName>my_type</AttributeLogicalName>

    <EntityLogicalName>my_data1</EntityLogicalName>

    <NeedFormatting>true</NeedFormatting>

    <ReturnType>123</ReturnType>

    <Value type="OptionSetValue">

    <Value>345</Value>

    </Value>

    </value>

    </KeyValuePairOfstringanyType>

    <KeyValuePairOfstringanyType>

    <key>my_data.status</key>

    <value type="AliasedValue">

    <AttributeLogicalName>status</AttributeLogicalName>

    <EntityLogicalName>my_data1</EntityLogicalName>

    <NeedFormatting>true</NeedFormatting>

    <ReturnType>123</ReturnType>

    <Value type="OptionSetValue">

    <Value>67</Value>

    </Value>

    </value>

    </KeyValuePairOfstringanyType>

    <KeyValuePairOfstringanyType>

    <key>my_data.date</key>

    <value type="AliasedValue">

    <AttributeLogicalName>date</AttributeLogicalName>

    <EntityLogicalName>my_data1</EntityLogicalName>

    <NeedFormatting>true</NeedFormatting>

    <ReturnType>89</ReturnType>

    <Value type="b:string">2024-01-01</Value>

    </value>

    </KeyValuePairOfstringanyType>

    <KeyValuePairOfstringanyType>

    <key>my_data.country</key>

    <value type="AliasedValue">

    <AttributeLogicalName>country</AttributeLogicalName>

    <EntityLogicalName>my_data1</EntityLogicalName>

    <NeedFormatting>true</NeedFormatting>

    <ReturnType>123</ReturnType>

    <Value type="OptionSetValue">

    <Value>456</Value>

    </Value>

    </value>

    </KeyValuePairOfstringanyType>

    <KeyValuePairOfstringanyType>

    <key>client_code</key>

    <value type="b:guid">some_code456</value>

    </KeyValuePairOfstringanyType>

    <KeyValuePairOfstringanyType>

    <key>my_data.my_data1id</key>

    <value type="AliasedValue">

    <AttributeLogicalName>my_data1id</AttributeLogicalName>

    <EntityLogicalName>my_data1</EntityLogicalName>

    <NeedFormatting>true</NeedFormatting>

    <ReturnType>13</ReturnType>

    <Value type="b:guid">some_code123</Value>

    </value>

    </KeyValuePairOfstringanyType>

    </Attributes>

    <EntityState nil="true"/>

    <FormattedValues>

    <KeyValuePairOfstringstring>

    <key>my_data.client_type</key>

    <value>client123</value>

    </KeyValuePairOfstringstring>

    <KeyValuePairOfstringstring>

    <key>my_data.status</key>

    <value>OK</value>

    </KeyValuePairOfstringstring>

    <KeyValuePairOfstringstring>

    <key>my_data.country</key>

    <value>some_country</value>

    </KeyValuePairOfstringstring>

    </FormattedValues>

    <Id>some_code456</Id>

    <KeyAttributes/>

    <LogicalName>some-id</LogicalName>

    <RelatedEntities/>

    <RowVersion>ID123</RowVersion>

    </Entity>

    </Entities>

    </EntityCollection>



    The elements in 'key' (or in 'AttributeLogicalName') should be the headers. The "value"/"Value" should be the actual data. This is the desired outcome (first row).[​IMG]

    As the data is very nested, I tried using xml.etree but did not come very far as I not sure how to proceed form here:

    for _, elem in ET.iterparse(my_xml):
    if len(elem) == 0:
    print(f'{elem.tag} {elem.attrib} text={elem.text}')
    else:
    print(f'{elem.tag} {elem.attrib}')


    How can I get the desired result?

    Continue reading...
    Stack, Janeiro 27, 2025
    #1

Compartilhe esta Página