Problem formulating Xpath for pandas DataFrame

user_19262738 0
Viewed 62

I'm tryin to retrieve some informations from an XML file with python. All i want is to set a pandas DataFrame with all the PrdRef infos that are related to a NIR tag matching with his 'Text()'

<Ressources xmlns="http://www.johndo.tr/brm">
...
<RessInd>
    <CdRet>01</CdRet>
    <NIR>456464644466</NIR>
    <DTO>01011988</DTO>
    <PrdRef>
        <PrdRefTyp>M</PrdRefTyp>
        <PrdRefVal>21M01</PrdRefVal>
        ...
    </PrdRef>
</RessInd>
<RessInd>
     ... etc
</RessInd>

here is my code :

import pandas as pd

df_prdref = pd.read_xml("to_parse.xml",xpath="//data:PrdRef/../data:RessInd/data:NIR[text()='456464644466']",namespaces={"data": "http://johndo.tr/brm"})
print(df_prdref )

Unfortunatly no matter what i've tried, read_xml() always return error or nothing ..

pandas xpath python
2 Answers

It seems, that the namespaces are not considered when evaluating XPath. An option would be to build an XPath expression without namespaces using local-name() function. The expression will be a bit longer, but it seems to work:

import pandas as pd

df_prdref = pd.read_xml(xml, xpath="//*[local-name()='RessInd' and ./*[local-name()='NIR' and text()='456464644466']]/*[local-name()='PrdRef']")
print(df_prdref)
user_141686230

After correcting the namespace URI, adjust XPath to run a bracketed conditional before required nodes:

df_prdref = pd.read_xml(
   "to_parse.xml", 
    xpath="//data:RessInd[data:NIR='456464644466']/data:PrdRef",
    namespaces={"data": "http://www.johndo.tr/brm"}
)
print(df_prdref)
user_14224510
Related