dataframe memory usage increased after .loc[] or df[a:b]

[miaoz2001]

Code Sample

import numpy
import pandas


def test1(freq):
    """
    after using df.loc[], no matter how big/small the df is, the memory usage is
    increased, almost doubled
    """
    index = pandas.date_range('2019-01-01', '2019-01-02', freq=freq,
                              tz='UTC')
    df = pandas.DataFrame(numpy.random.randn(len(index), 10), index=index,
                          columns=['ax', 'ay', 'az', 'qw', 'qx', 'qy', 'qz',
                                   'wx', 'wy', 'wz'], dtype=numpy.float32)
    idx = pandas.date_range(start='2019-01-01 12:00:00',
                            end='2019-01-01 13:00:00',
                            freq=freq, tz='UTC')
    df.info()
    df.loc[idx] = 0
    df.info()


def test2(freq):
    """
    after using df[], rough observation:
         - df is less than around 50mb, the memory usage is increased
         - df is greater than 50mb, the memory usage is NOT increased
    """
    index = pandas.date_range('2019-01-01', '2019-01-02', freq=freq,
                              tz='UTC')
    df = pandas.DataFrame(numpy.random.randn(len(index), 10), index=index,
                          columns=['ax', 'ay', 'az', 'qw', 'qx', 'qy', 'qz',
                                   'wx', 'wy', 'wz'], dtype=numpy.float32)
    df.info()
    df[df.index.min(): df.index.max()]
    df.info()


if __name__ == '__main__':
    freqs = ['40ms', '80ms', '90ms', '150ms']
    for freq in freqs:
        print(f'\n=====freq is {freq} in loc test====\n')
        test1(freq)

    for freq in freqs:
        print(f'\n=====freq is {freq} in slice test====\n')
        test2(freq)

Problem description

The memory usage of a dataframe is increased somehow after .loc or df[a:b]

• after using df.loc[], no matter how big/small the df is, the memory usage is
  increased, almost doubled
• after using df[], rough observation:
  - df is less than around 50mb, the memory usage is increased
  - df is greater than 50mb, the memory usage is NOT increased

as from the log of the above code:

=====freq is 40ms in loc test====

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 2160001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 40L
Data columns (total 10 columns):
ax    float32
ay    float32
az    float32
qw    float32
qx    float32
qy    float32
qz    float32
wx    float32
wy    float32
wz    float32
dtypes: float32(10)
memory usage: 98.9 MB
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 2160001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 40L
Data columns (total 10 columns):
ax    float32
ay    float32
az    float32
qw    float32
qx    float32
qy    float32
qz    float32
wx    float32
wy    float32
wz    float32
dtypes: float32(10)
memory usage: 178.9 MB            <------------ increased!

=====freq is 80ms in loc test====

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 1080001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 80L
Data columns (total 10 columns):
ax    1080001 non-null float32
ay    1080001 non-null float32
az    1080001 non-null float32
qw    1080001 non-null float32
qx    1080001 non-null float32
qy    1080001 non-null float32
qz    1080001 non-null float32
wx    1080001 non-null float32
wy    1080001 non-null float32
wz    1080001 non-null float32
dtypes: float32(10)
memory usage: 49.4 MB
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 1080001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 80L
Data columns (total 10 columns):
ax    1080001 non-null float32
ay    1080001 non-null float32
az    1080001 non-null float32
qw    1080001 non-null float32
qx    1080001 non-null float32
qy    1080001 non-null float32
qz    1080001 non-null float32
wx    1080001 non-null float32
wy    1080001 non-null float32
wz    1080001 non-null float32
dtypes: float32(10)
memory usage: 89.4 MB              <------------ increased!

=====freq is 90ms in loc test====

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 960001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 90L
Data columns (total 10 columns):
ax    960001 non-null float32
ay    960001 non-null float32
az    960001 non-null float32
qw    960001 non-null float32
qx    960001 non-null float32
qy    960001 non-null float32
qz    960001 non-null float32
wx    960001 non-null float32
wy    960001 non-null float32
wz    960001 non-null float32
dtypes: float32(10)
memory usage: 43.9 MB
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 960001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 90L
Data columns (total 10 columns):
ax    960001 non-null float32
ay    960001 non-null float32
az    960001 non-null float32
qw    960001 non-null float32
qx    960001 non-null float32
qy    960001 non-null float32
qz    960001 non-null float32
wx    960001 non-null float32
wy    960001 non-null float32
wz    960001 non-null float32
dtypes: float32(10)
memory usage: 83.9 MB .                <------------ increased!

=====freq is 150ms in loc test====

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 576001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 150L
Data columns (total 10 columns):
ax    576001 non-null float32
ay    576001 non-null float32
az    576001 non-null float32
qw    576001 non-null float32
qx    576001 non-null float32
qy    576001 non-null float32
qz    576001 non-null float32
wx    576001 non-null float32
wy    576001 non-null float32
wz    576001 non-null float32
dtypes: float32(10)
memory usage: 26.4 MB
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 576001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 150L
Data columns (total 10 columns):
ax    576001 non-null float32
ay    576001 non-null float32
az    576001 non-null float32
qw    576001 non-null float32
qx    576001 non-null float32
qy    576001 non-null float32
qz    576001 non-null float32
wx    576001 non-null float32
wy    576001 non-null float32
wz    576001 non-null float32
dtypes: float32(10)
memory usage: 46.4 MB                         <------------ increased!







=====freq is 40ms in slice test====

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 2160001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 40L
Data columns (total 10 columns):
ax    float32
ay    float32
az    float32
qw    float32
qx    float32
qy    float32
qz    float32
wx    float32
wy    float32
wz    float32
dtypes: float32(10)
memory usage: 98.9 MB                                    
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 2160001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 40L
Data columns (total 10 columns):
ax    float32
ay    float32
az    float32
qw    float32
qx    float32
qy    float32
qz    float32
wx    float32
wy    float32
wz    float32
dtypes: float32(10)
memory usage: 98.9 MB                             <------------ unchanged!

=====freq is 80ms in slice test====

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 1080001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 80L
Data columns (total 10 columns):
ax    1080001 non-null float32
ay    1080001 non-null float32
az    1080001 non-null float32
qw    1080001 non-null float32
qx    1080001 non-null float32
qy    1080001 non-null float32
qz    1080001 non-null float32
wx    1080001 non-null float32
wy    1080001 non-null float32
wz    1080001 non-null float32
dtypes: float32(10)
memory usage: 49.4 MB
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 1080001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 80L
Data columns (total 10 columns):
ax    1080001 non-null float32
ay    1080001 non-null float32
az    1080001 non-null float32
qw    1080001 non-null float32
qx    1080001 non-null float32
qy    1080001 non-null float32
qz    1080001 non-null float32
wx    1080001 non-null float32
wy    1080001 non-null float32
wz    1080001 non-null float32
dtypes: float32(10)
memory usage: 49.4 MB                                     <------------ unchanged!

=====freq is 90ms in slice test====

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 960001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 90L
Data columns (total 10 columns):
ax    960001 non-null float32
ay    960001 non-null float32
az    960001 non-null float32
qw    960001 non-null float32
qx    960001 non-null float32
qy    960001 non-null float32
qz    960001 non-null float32
wx    960001 non-null float32
wy    960001 non-null float32
wz    960001 non-null float32
dtypes: float32(10)
memory usage: 43.9 MB
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 960001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 90L
Data columns (total 10 columns):
ax    960001 non-null float32
ay    960001 non-null float32
az    960001 non-null float32
qw    960001 non-null float32
qx    960001 non-null float32
qy    960001 non-null float32
qz    960001 non-null float32
wx    960001 non-null float32
wy    960001 non-null float32
wz    960001 non-null float32
dtypes: float32(10)
memory usage: 83.9 MB                             <------------ increased!

=====freq is 150ms in slice test====

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 576001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 150L
Data columns (total 10 columns):
ax    576001 non-null float32
ay    576001 non-null float32
az    576001 non-null float32
qw    576001 non-null float32
qx    576001 non-null float32
qy    576001 non-null float32
qz    576001 non-null float32
wx    576001 non-null float32
wy    576001 non-null float32
wz    576001 non-null float32
dtypes: float32(10)
memory usage: 26.4 MB
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 576001 entries, 2019-01-01 00:00:00+00:00 to 2019-01-02 00:00:00+00:00
Freq: 150L
Data columns (total 10 columns):
ax    576001 non-null float32
ay    576001 non-null float32
az    576001 non-null float32
qw    576001 non-null float32
qx    576001 non-null float32
qy    576001 non-null float32
qz    576001 non-null float32
wx    576001 non-null float32
wy    576001 non-null float32
wz    576001 non-null float32
dtypes: float32(10)
memory usage: 46.4 MB                                  <------------ increased!

Expected Output

The memory usage of dataframe shall be no change after .loc or slicing.

Output of pd.show_versions()

[paste the output of pd.show_versions() here below this line]
INSTALLED VERSIONS

commit : None
python : 3.7.3.final.0
python-bits : 64
OS : Darwin
OS-release : 19.2.0
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : None
LOCALE : en_AU.UTF-8

pandas : 0.25.3
numpy : 1.18.1
pytz : 2019.3
dateutil : 2.8.1
pip : 19.0.3
setuptools : 40.8.0
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
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None

[jorisvandenbossche]

@miaoz2001 if you add a print(df.memory_usage()) to the functions, you will see that it is the Index that increased in size.

In [29]: df.memory_usage() 
Out[29]: 
Index    8640008
ax       4320004
ay       4320004
az       4320004
qw       4320004
qx       4320004
qy       4320004
qz       4320004
wx       4320004
wy       4320004
wz       4320004
dtype: int64

In [30]: df.loc[idx] = 0  

In [31]: df.memory_usage()  
Out[31]: 
Index    50583048
ax        4320004
ay        4320004
az        4320004
qw        4320004
qx        4320004
qy        4320004
qz        4320004
wx        4320004
wy        4320004
wz        4320004
dtype: int64

Now, after the first time you do an indexing operation with label lookup, the Index engine's hashtable is created, and this is included in the memory usage. For this reason, the memory usage increases after indexing.
But to be honest, I was a bit surprised that the engine takes that much of memory.

For slicing this is apparently not always the case, there might be some special case code that in certain cases avoids a label lookup using the engine, I don't know (I would need to look into the code for that).

[jreback]

the hash table is based on the size of the original index (as it was sliced)

using a slice can generally be done as a view; a boolean indexer generally cannot (in this case it is mono tonic and contiguous though we likely don’t actually explicitly handle this case)

this is an expected result

[miaoz2001]

@miaoz2001 if you add a print(df.memory_usage()) to the functions, you will see that it is the Index that increased in size.

Thanks for the quick reply

@jorisvandenbossche yeah..I noticed that it is the index get increased, but didn't know why and how it comes so big...also why it stops increasing when the df is big enough?

@jreback honestly i don't quite understand why it is an expected result...isn't it a bug? can we get rid of the index hashtable after the operation?

OK if it is surely expected result and unavoidable, is there a workaround? Is there a way that can reduce the memory usage after the operation? my project uses a lot of big dataframes, i can't live with the memory increased unexpectedly, it is increased quite big, doubled.....
(say i have a df is 395.5M, after a simple/common api call df.loc[], the size of it changed to be 715.5M!)

can i 'compress' the dataframe afterwards...or anything?

Thanks!

[jreback]

you are keeping references around

df = df.loc[...]

will force garbage collection where the index will be destroyed

[miaoz2001]

you are keeping references around

df = df.loc[...]

will force garbage collection where the index will be destroyed

but that will wait for the gc to work, while it could be running out of memory while executing .loc[]...

[jreback]

if u are not operating with enough memory then you are out of luck

you should usually have 2x your dataframe size at the very least , usually more

[miaoz2001]

OK,...
i did more test and found out the boolean array won't cause the memory issue
something like:
df.loc[df.index.isin(idx)] = 0 or df.iloc[df.index.isin(idx)] = 0 or df.iloc[100:1000]
these will work just fine.

the array df.index.isin(idx) is an extra cost but it is quite small, 8M only for a 395M df.

Anyway it's better these to be mentioned in the doc..

[jreback]

it’s all depends on your usecase and how you slice

closing as expected

[jorisvandenbossche]

i did more test and found out the boolean array won't cause the memory issue

@miaoz2001 if you want to avoid the Index hashtable to be generated, you indeed need to avoid any "label-based" lookup or assignment (as you did with df.loc[..]). So indeed, boolean indexing with a mask or positional indexing with iloc are ways to do that.

[miaoz2001]

@jorisvandenbossche Thanks!
so you mean "any 'label-based' lookup/assignment"...is there any other apis besides .loc? I might need to check my code to avoid any surprise...

[jorisvandenbossche]

So it's actually also things like is_unique, is_monotonic, .. attributes that check things on the Index, because they also make use of the engine's hashtable.
Basically any index operation that uses the underlying engine will create this engine. But if you don't want those, maybe you should not have those data as the index, but just keep it as a column?

[miaoz2001]

@jorisvandenbossche , thanks for the advice, i will try to find out

[chenkigba]

OK,... i did more test and found out the boolean array won't cause the memory issue something like: df.loc[df.index.isin(idx)] = 0 or df.iloc[df.index.isin(idx)] = 0 or df.iloc[100:1000] these will work just fine.

the array df.index.isin(idx) is an extra cost but it is quite small, 8M only for a 395M df.

Anyway it's better these to be mentioned in the doc..

I also met this problem. .iloc can work for me (didn't create hash table to use memory as you disscussed)
You may see the difference when using loc and iloc
loc:

iloc:

Meanwhile, when i use DataCore.data to fetch the data, it may also create hash table such take more memory