BUG: inconsistent space stripping in read_xml

[avm19]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

# Note the blank space in <b>bar </b>

str = """
<top>
    <a>
        <b>foo</b>
        <c>
            <d>1</d>
            <e>2</e>
        </c>
    </a>
    <a>
        <b>bar </b>
        <c>
            <d>3</d>
            <e>4</e>
        </c>
    </a>
</top>
"""
import pandas as pd
print("bar" in pd.read_xml(str, xpath='//a')['b'].values) # True
print("bar " in pd.read_xml(str, xpath='//a')['b'].values)  # False
pd.read_xml(str, xpath='//c[../b="bar "]') # works
pd.read_xml(str, xpath='//c[../b="bar"]') # fails

Issue Description

The content of text XML nodes is stripped of spaces in the output of pd.read_xml(), but not when processing. This inconsistency is unexpected and requires the user to implement additional logic.

Expected Behavior

I expect consistent behaviour, which is either:

print("bar" in pd.read_xml(str, xpath='//a')['b'].values) # False
print("bar " in pd.read_xml(str, xpath='//a')['b'].values)  # True
pd.read_xml(str, xpath='//c[../b="bar "]') # works
pd.read_xml(str, xpath='//c[../b="bar"]') # fails

or

print("bar" in pd.read_xml(str, xpath='//a')['b'].values) # True
print("bar " in pd.read_xml(str, xpath='//a')['b'].values)  # False
pd.read_xml(str, xpath='//c[../b="bar "]') # fails
pd.read_xml(str, xpath='//c[../b="bar"]') # works

Installed Versions

INSTALLED VERSIONS
------------------
commit           : 965ceca9fd796940050d6fc817707bba1c4f9bff
python           : 3.11.4.final.0
python-bits      : 64
OS               : Linux
OS-release       : 5.15.0-1029-azure
Version          : #36~20.04.1-Ubuntu SMP Tue Dec 6 17:00:26 UTC 2022
machine          : x86_64
processor        : x86_64
byteorder        : little
LC_ALL           : None
LANG             : en_CA.UTF-8
LOCALE           : en_CA.UTF-8

pandas           : 2.0.2
numpy            : 1.25.0
pytz             : 2023.3
dateutil         : 2.8.2
setuptools       : 67.7.2
pip              : 23.1.2
Cython           : None
pytest           : None
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : 4.9.2
html5lib         : None
pymysql          : None
psycopg2         : None
jinja2           : None
IPython          : 8.14.0
pandas_datareader: None
bs4              : None
bottleneck       : None
brotli           : None
fastparquet      : None
fsspec           : None
gcsfs            : None
matplotlib       : None
numba            : None
numexpr          : None
odfpy            : None
openpyxl         : None
pandas_gbq       : None
pyarrow          : None
pyreadstat       : None
pyxlsb           : None
s3fs             : None
scipy            : None
snappy           : None
sqlalchemy       : None
tables           : None
tabulate         : None
xarray           : None
xlrd             : None
zstandard        : None
tzdata           : 2023.3
qtpy             : None
pyqt5            : None

[avm19]

I haven't found a universal solution like replacing

pd.read_xml(str, xpath='//c[../b="bar"]') # fails

with

pd.read_xml(str, xpath='//c[starts-with(../b, "bar")]')

as it would work for "bar " but not " bar".

Other XPATH 1.0 String Functions do not seem to provide an easy solution either:

• contains does not distinguish between "bar" and " bar bar "
• normalize-space does not distinguish between "bar bar" and "bar bar" (with double space).

I wonder what was the rationale for stripping spaces from text content in the first place?

[ParfaitG]

There is no inconsistency. As designed, read_xml does call str.strip() on parsed text to remove leading and trailing whitespace. The rationale is to retain the pertinent textual data for migration to data frame columns without line breaks, carriages, whitespace, and other non-print characters which can exist in original XML such as below example:

<top>
    <a>
        <b>foo</b>
        <c>
            <d>1</d>
            <e>2</e>
        </c>
    </a>
    <a>
        <b>
                 bar 
</b>
        <c>
            <d>3</d>
            <e>4</e>
        </c>
    </a>
</top>

Additionally, dtypes are determined by parsed data and to properly convert to default or user's specified data types, strip assures reliability in conversion.

Therefore, to correctly run, XPath should use original text which in your case includes the trailing whitespace. So below reflects consistent, designed behavior:

pd.read_xml(str, xpath='//c[../b="bar "]')     # WORKS USING ORIGINAL TEXT
pd.read_xml(str, xpath='//c[../b="bar"]')      # FAILS NOT USING ORIGINAL TEXT

Also, read_xml will remove such trailing space so below also reflects designed behavior:

print("bar" in pd.read_xml(str, xpath='//a')['b'].values)      # TRUE SINCE TEXT IS STRIPPED
print("bar " in pd.read_xml(str, xpath='//a')['b'].values)     # FALSE SINCE TEXT IS STRIPPED

[avm19]

@ParfaitG Thank you for the response.

There is no inconsistency. As designed, read_xml does call str.strip() on parsed text to remove leading and trailing whitespace. The rationale is to retain the pertinent textual data for migration to data frame columns without line breaks, carriages, whitespace, and other non-print characters which can exist in original XML such as below example:

Well, the inconsistency is between XPATH and pandas. As you said, pandas decided that leading and trailing whitespaces are irrelevant and should be removed. XPATH decides that they are relevant and must be kept by default. I understand pandas' reasoning (which I suppose is that whitespace characters between tags and text may be artefacts of formatting), but XPATH is a much older standard.

It would be nice to have an easy way of making pandas logic agree with XPATH. Unfortunately, XPATH does not have an equivalent of str.strip(). XPATH's normalize-space() function also replaces internal whitespaces with single spaces, unlike pandas. This operation makes more sense than what pandas is currently doing. For example, consider changing indentations from 4-space to 2-space:

<top>
    <a>
        Lorem ipsum dolor sit amet,
        consectetur adipiscing elit
    </a>
</top>

becomes

<top>
  <a>
    Lorem ipsum dolor sit amet,
    consectetur adipiscing elit
  </a>
</top>

Pandas will treat the texts as different, while XPATH's normalize-space() will consider them equivalent.

[ParfaitG]

Correct. Pandas will only strip leading and trailing whitespace and not any embedded whitespace or nonprint characters within a string. These two XML will differ in that respect due to the inner line break and tabs (\n\t vs \n\t\t).

Thinking now, I understand your earlier need. We can adjust pandas read_xml to preserve all original characters by removing the strip() calls. Dense, complex text was not the original intent when first designing this method.

[avm19]

To be frank, in my use case this was not a serious issue. But it confused me and took some time initially, so I decided to report this.

I think removing the strip() calls from text nodes would be a good idea, because a user can always do Series.str.strip() on a column, or whatever transformation is preferred.