BUG: merging on int32 platforms with large blocks

[randomgambit]

Hello everyone,

I am trying to merge a ridiculously large dataframe with another ridiculously smaller one and I get

df=df.merge(slave,left_on='buyer',right_on='NAME',how='left')
OverflowError: Python int too large to convert to C long

Ram is filled at 56% prior to the merge. Am I hitting some limitations here?

master dataframe

df.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 80162624 entries, 0 to 90320839
Data columns (total 38 columns):
index                      int64

dtypes: datetime64[ns](2), float32(1), int64(3), object(32)
memory usage: 23.0+ GB

dataframe I would like to merge to the master

slave.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 55394 entries, 0 to 55393
Data columns (total 6 columns):
dtypes: object(6)
memory usage: 2.5+ MB

I am using the latest Anaconda distribution (that is, with Pandas 18.0)
Thanks for your help!

[jreback]

at a start show the dtypes of the merging columns
plus a data sample
also pd.show_versions()

[randomgambit]

yes sir

slave.NAME.dtype
Out[22]: dtype('O')

df.buyer.dtype
Out[23]: dtype('O')

pd.show_versions()

INSTALLED VERSIONS
------------------
commit: None
python: 2.7.11.final.0
python-bits: 64
OS: Windows
OS-release: 7
machine: AMD64
processor: Intel64 Family 6 Model 63 Stepping 2, GenuineIntel
byteorder: little
LC_ALL: None
LANG: en_US

pandas: 0.18.0
nose: 1.3.7
pip: 8.1.1
setuptools: 20.3
Cython: 0.23.4
numpy: 1.10.4
scipy: 0.17.0
statsmodels: None
xarray: None
IPython: 4.1.2
sphinx: 1.3.5
patsy: 0.4.0
dateutil: 2.5.1
pytz: 2016.2
blosc: None
bottleneck: 1.0.0
tables: 3.2.2
numexpr: 2.5
matplotlib: 1.5.1
openpyxl: 2.3.2
xlrd: 0.9.4
xlwt: 1.0.0
xlsxwriter: 0.8.4
lxml: 3.6.0
bs4: 4.4.1
html5lib: None
httplib2: None
apiclient: None
sqlalchemy: 1.0.12
pymysql: None
psycopg2: None
jinja2: 2.8
boto: 2.39.0

[randomgambit]

data sample I cannot unfortunately (NDA), but basically these variables are names such as jefferson von reback

but if you tell me what you need, id be happy to find out for you

[jreback]

pls show the full trace back

[randomgambit]

sure @jreback , thanks for helping (slave=infobase)

df=df.merge(infobase,left_on='buyer_name',right_on='NAME',how='left')
Traceback (most recent call last):

  File "<ipython-input-3-090669481e0c>", line 1, in <module>
    df=df.merge(infobase,left_on='buyer_name',right_on='NAME',how='left')

  File "C:\Users\m1hxb02\AppData\Local\Continuum\Anaconda2\lib\site-packages\pandas\core\frame.py", line 4421, in merge
    copy=copy, indicator=indicator)

  File "C:\Users\m1hxb02\AppData\Local\Continuum\Anaconda2\lib\site-packages\pandas\tools\merge.py", line 35, in merge
    return op.get_result()

  File "C:\Users\m1hxb02\AppData\Local\Continuum\Anaconda2\lib\site-packages\pandas\tools\merge.py", line 220, in get_result
    concat_axis=0, copy=self.copy)

  File "C:\Users\m1hxb02\AppData\Local\Continuum\Anaconda2\lib\site-packages\pandas\core\internals.py", line 4474, in concatenate_block_managers
    for placement, join_units in concat_plan]

  File "C:\Users\m1hxb02\AppData\Local\Continuum\Anaconda2\lib\site-packages\pandas\core\internals.py", line 4567, in concatenate_join_units
    empty_dtype, upcasted_na = get_empty_dtype_and_na(join_units)

  File "C:\Users\m1hxb02\AppData\Local\Continuum\Anaconda2\lib\site-packages\pandas\core\internals.py", line 4528, in get_empty_dtype_and_na
    if unit.is_null:

  File "pandas\src\properties.pyx", line 34, in pandas.lib.cache_readonly.__get__ (pandas\lib.c:44357)

  File "C:\Users\m1hxb02\AppData\Local\Continuum\Anaconda2\lib\site-packages\pandas\core\internals.py", line 4808, in is_null
    for i in range(0, total_len, chunk_len):

OverflowError: Python int too large to convert to C long

[randomgambit]

tried with copy=False, I get the same error

[jreback]

So first off, what you are trying to do is completelny inefficient. unless you are doing a multi-multi merge, you will prob be better off using .map.

On windows this is very likely to blow up as int32 are used as pointers. I don't really now specifically why this is blowing up as I can't allocate that much memory. So it is indexing past int32, which normally is not a problem (for numpy), but the line is a python line. So not sure the issue.

In [20]: total_len = 80162624*32

In [21]: chunk_len = max(total_len // 40, 1000)

In [22]: indexer = np.array(list(range(0, total_len, chunk_len)))

In [23]: ml = np.iinfo('int32').max

In [24]: indexer>ml
Out[24]:
array([False, False, False, False, False, False, False, False, False,
       False, False, False, False, False, False, False, False, False,
       False, False, False, False, False, False, False, False, False,
       False, False, False, False, False, False, False,  True,  True,
        True,  True,  True,  True,  True], dtype=bool)

In [25]: indexer
Out[25]:
array([         0,   64130099,  128260198,  192390297,  256520396,
        320650495,  384780594,  448910693,  513040792,  577170891,
        641300990,  705431089,  769561188,  833691287,  897821386,
        961951485, 1026081584, 1090211683, 1154341782, 1218471881,
       1282601980, 1346732079, 1410862178, 1474992277, 1539122376,
       1603252475, 1667382574, 1731512673, 1795642772, 1859772871,
       1923902970, 1988033069, 2052163168, 2116293267, 2180423366,
       2244553465, 2308683564, 2372813663, 2436943762, 2501073861,
       2565203960], dtype=int64)

You have lots of object dtypes. You need to make sure that these are strings (and NOT objects), e.g. an embedded integer.

Further you should simply categorize things to use less memory. Try doing a smaller frame merge (e.g. less columns), or simply get more memory.

[randomgambit]

thanks @jreback , i did not know map was a more efficient alternative than a left-merge using pd.merge. Why is that? Is this what you are suggesting?

[jreback]

if you are doing a one-many (or one-one) its way more efficient.