BUG: `DataFrame.loc` with `MultiIndex`, unexpected increase of memory

[trendelkampschroer]

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

def make_data():
    dates = pd.date_range("2001", "2020")
    ids = [f"id_{i + 1}" for i in range(1000)]
    index = pd.MultiIndex.from_product([dates, ids], names=["date", "id"])
    columns = [f"col_{i + 1}" for i in range(10)]
    return pd.DataFrame(0.0, index=index, columns=columns)

if __name__ == "__main__":
    frame = make_data()

    t0 = frame.index.get_level_values("date").min()
    end = pd.date_range("2001", "2020", freq="M")
    m = psutil.Process(os.getpid()).memory_info().rss

    print(f"Memory (resident set size): {m / (1024 * 1024):.2f} MB.")
    for t in end:
        frame.loc(axis=0)[t0:t, :]
        m = psutil.Process(os.getpid()).memory_info().rss
        print(f"Memory (resident set size): {m / (1024 * 1024):.2f} MB.")

Issue Description

Accessing or slicing a DataFrame with a MultiIndex that has two levels, the first with Timestamp entries and the second with string valued identifiers, shows an unexpected increase in memory usage.

One my machine, Apple M1, the initial memory usage (before the loc access) is roughly 700MB. During the for-loop the memory footprint increases to up to 7000MB.

Expected Behavior

Maximum memory footprint should not be increasing with the number of loop iterations. The maximum memory footprint should not exceed (roughly) twice the memory required for the (initial) frame.

Installed Versions

NSTALLED VERSIONS

commit : 478d340
python : 3.9.16.final.0
python-bits : 64
OS : Darwin
OS-release : 22.4.0
Version : Darwin Kernel Version 22.4.0: Mon Mar 6 20:59:28 PST 2023; root:xnu-8796.101.5~3/RELEASE_ARM64_T6000
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : None
LOCALE : en_GB.UTF-8

pandas : 2.0.0
numpy : 1.24.2
pytz : 2023.3
dateutil : 2.8.2
setuptools : 67.6.1
pip : 23.0.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
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

[topper-123]

I cannot replicate this on my machine (Macbook Air M1). My memory usage lies typically and constantly around 400-500 MB.

I see you did these tests on Pandas 2.0.0. Are you able to try again on the pandas development branch?

[trendelkampschroer]

With the nightly build https://pandas.pydata.org/docs/dev/getting_started/install.html the memory now stays roughly constant over the loop iterations. Apparently fixed.

However, changing to ids = [f"id_{i + 1}" for i in range(5000)] in function make_data again recovers the unexpectedly large memory footprint that I was observing before. Also the .loc access seems to be very slow now.

Replacing .loc by .iloc access with

i0, i = frame.index.slice_locs(start=t0, end=t)
frame.iloc[i0:i, :]

shows constant memory usage and is a lot faster than the .loc access. So I don't think that the development version is free of that bug.

I am using a freshly installed conda environment with a pip install of the pandas nightly build, cf. below for package versions

- bzip2=1.0.8=h3422bc3_4
  - ca-certificates=2022.12.7=h4653dfc_0
  - libblas=3.9.0=16_osxarm64_openblas
  - libcblas=3.9.0=16_osxarm64_openblas
  - libcxx=16.0.2=h4653b0c_0
  - libexpat=2.5.0=hb7217d7_1
  - libffi=3.4.2=h3422bc3_5
  - libgfortran=5.0.0=12_2_0_hd922786_31
  - libgfortran5=12.2.0=h0eea778_31
  - liblapack=3.9.0=16_osxarm64_openblas
  - libopenblas=0.3.21=openmp_hc731615_3
  - libsqlite=3.40.0=h76d750c_1
  - libzlib=1.2.13=h03a7124_4
  - llvm-openmp=16.0.3=h1c12783_0
  - ncurses=6.3=h07bb92c_1
  - numpy=1.24.3=py311hb8f3215_0
  - openssl=3.1.0=h53f4e23_3
  - pip=23.1.2=pyhd8ed1ab_0
  - psutil=5.9.5=py311he2be06e_0
  - python=3.11.3=h1456518_0_cpython
  - python_abi=3.11=3_cp311
  - pytz=2023.3=pyhd8ed1ab_0
  - readline=8.2=h92ec313_1
  - setuptools=67.7.2=pyhd8ed1ab_0
  - tk=8.6.12=he1e0b03_0
  - wheel=0.40.0=pyhd8ed1ab_0
  - xz=5.2.6=h57fd34a_0
  - pip:
      - pandas==2.1.0.dev0+696.g042ebab024
      - python-dateutil==2.8.2
      - six==1.16.0
      - tzdata==2023.3

[topper-123]

Thanks for the follow-up, @trendelkampschroer.

You are right, I see the issue now the range is pumped up to 5000. I also see the improvement when using iloc that you mention.

I tried replacing frame.loc(axis=0)[t0:t, :] with frame.loc(axis=0)[t0:t] and the memory/performance issue went away.

So this an issue with doing .loc operations on both axes in the square brackets. Most likely pandas is doing some copying that on the surface is not needed, but I'm not sure we want to skip it here or if that has some consequences in other situations. You are welcome to investigate, of course, and get back if you've found a solution.

I'll leave this issue open. If you do investigate and figure it isn't practical to fix this can you you get back to this thread.

[trendelkampschroer]

@topper-123 thanks for the follow up. I'll try out your suggestion.

I agree that this should stay open. Adding the colon which makes explicit that all entries in the second level (id) are in the slice should not result in a reduced performance or increased memory footprint. Otherwise we'd have here a case of "implicit is better than explicit". Especially since the performance implications can be quite severe.

Unfortunately I lack the expertise with pandas (indexing) internals to dissect this to the point where I might be able to shed a light on what's going on here. Happy to be enlightened by any one stumbling upon this though.

[topper-123]

Ok no problem. At least you yourself can get the performance up by dropping the colon.

[trendelkampschroer]

I can confirm that frame.loc[t0:t] does not exhibit the unexpected memory footprint for version 2.0.1 and 1.5.3. Thanks for your help with this @topper-123 .