High Memory Usage when Concatenating Partially Indexed Series to Multiindexed Dataframe

[cyniphile]

Code Sample, a copy-pastable example if possible

import numpy as np
import pandas as pd
import resource
​
multindex = pd.MultiIndex.from_product([range(1000), range(1000)])
df = pd.DataFrame(np.random.randint(low=0, high=10, size=(len(multindex), 1)),
                  columns=['a'], index=multindex)
print('base:', resource.getrusage(resource.RUSAGE_SELF).ru_maxrss)
​
df['int_col'] = 0
print("insert int col:", 
      resource.getrusage(resource.RUSAGE_SELF).ru_maxrss)
​
df['str_col'] = 'hey'
print("insert string col:",
      resource.getrusage(resource.RUSAGE_SELF).ru_maxrss)
​
s1 = pd.Series(1, index=multindex)
df['multindex_int'] = s1
print("insert multindexed int col:", 
      resource.getrusage(resource.RUSAGE_SELF).ru_maxrss)
​
partial_multindex = multindex[:1000]
s2 = pd.Series(1, index=partial_multindex)
df['partial_multindex_int'] = s2
print("insert partial multindexed int col:", 
      resource.getrusage(resource.RUSAGE_SELF).ru_maxrss)

Output:

base: 91360
insert int col 99188
insert string col 107108
insert multindexed int col 122664
insert partial multindexed int col 257984

Problem description

I have a big Dataframe df (~100m rows) with a multiindex. I want to concat a series s to it as a new column. This series' index is a subset of df's index.

If I simply do df['new'] = s or df = pd.concat([df, s], axis=1) these operations take up a massive amount of memory (as much as df) and crash my computer. Adding a new column df['new'] = "hi" doesn't cause this problem. See code example; memory usage doubles on the last operation, but clearly not much new data is being added.

Expected Output

n/a

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.6.3.final.0
python-bits: 64
OS: Linux
OS-release: 4.13.0-16-lowlatency
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.22.0
pytest: None
pip: 9.0.3
setuptools: 39.0.1
Cython: 0.28.1
numpy: 1.14.2
scipy: 1.0.1
pyarrow: None
xarray: None
IPython: 6.2.1
sphinx: None
patsy: 0.5.0
dateutil: 2.7.2
pytz: 2018.4
blosc: None
bottleneck: 1.2.1
tables: 3.4.2
numexpr: 2.6.4
feather: None
matplotlib: 2.1.2
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 1.0.1
sqlalchemy: 1.2.6
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: 0.6.0+16.g1763dbd
None

[mroeschke]

Related tracking is in #38650 so closing in favor of that issue