Join operation takes more time.

[ericksc]

My Code Sample

When I tried to launch a join operation between multiIndexed dataframes as the following code describes
Fully code is posted on:
https://github.com/ericksc/pandas_studies

# My code here
    dates = pd.date_range(start='1980-1-1', end='2050-1-1')
    strings = np.array(
        [
            'apple', 'grape', 'orange' ,'pear', 'melon', 'banana',
            'watermelon', 'lemon', 'strawberry', 'berry', 'blackberry',
            'cherry','pineapple' , 'mango', 'papaya', 'peach', 'coffee',
            'planes', 'cars', 'houses', 'dogs', 'cats', 'computers',
            'servers', 'sun', 'moon', 'chairs', 'tables', 'screens',
            'keyboards', 'shoes', 't-shirts', 'tv', 'radio', 'door', 'windows',
            'bed', 'spoon', 'key', 'paper', 'foot', 'bee', 'ants' ,'worm',
            'pack', 'phone', 'boat', 'hair', 'yellow', 'disc'
    ])
    start = time.time()
    idx = pd.MultiIndex.from_product([dates,strings], names=['date', 'words'])
    end = time.time()
    print("MultiIndex execution time:", end-start)
    lenght = idx.shape[0]
    start = time.time()
    df_1 = pd.DataFrame({'col_A':np.random.randn(lenght),
                  'col_2':np.arange(lenght),
                  'col_b' : np.random.choice(a=[False, True], size=(lenght)),
                  'col_i' : np.random.random_integers(low=0,high=100, size=lenght),
            }, index=idx)
    end = time.time()
    print("data frame creation execution time:", end-start)
    df_2 = pd.DataFrame({'col_x':np.arange(lenght)},
                        index=idx)

    assert df_1.shape == (1278450, 4)

    start = time.time()
    df_1 = df_1.join(df_2, how='left')
    end = time.time()
    print("Join execution time:", end - start)
    assert df_1.shape == (1278450, 5)

Problem description

The join operation between multiIndexed dataframes (at least 2 levels) with a large combination of labels shows a change of time execution performance to worst from pandas 0.19.1 to 0.23.4, including 0.24.2.

• pandas 0.19.1-> join execution time -> 0.1129 seconds
• pandas 0.23.4-> join execution time -> 1.2409 seconds
• pandas 0.24.2-> join execution time -> 1.2309 seconds

This is around 10.9x times.

Expected Output

The expected output is a similar performance.

Output of pd.show_versions()

p36-pandas 0.19.1

INSTALLED VERSIONS

commit: None
python: 3.6.6.final.0
python-bits: 64
OS: Windows
OS-release: 10
machine: AMD64
processor: AMD64
byteorder: little
LC_ALL: None
LANG: None
LOCALE: None.None

pandas: 0.19.1
nose: None
pip: 19.1.1
setuptools: 41.0.1
Cython: None
numpy: 1.16.4
scipy: None
statsmodels: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.8.0
pytz: 2019.1
blosc: None
bottleneck: None
tables: None
numexpr: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
httplib2: None
apiclient: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
boto: None
pandas_datareader: None

p36-pandas 0.23.4

INSTALLED VERSIONS

commit: None
python: 3.6.6.final.0
python-bits: 64
OS: Windows
OS-release: 10
machine: AMD64
processor: AMD64
byteorder: little
LC_ALL: None
LANG: None
LOCALE: None.None

pandas: 0.23.4
pytest: 4.6.3
pip: 19.1.1
setuptools: 41.0.1
Cython: None
numpy: 1.16.4
scipy: None
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.8.0
pytz: 2019.1
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[jreback]

you are welcome to have a look

this is in master now.

In [2]: import time                                                                                                                                                                                                                                              

In [3]:     dates = pd.date_range(start='1980-1-1', end='2050-1-1') 
   ...:     strings = np.array( 
   ...:         [ 
   ...:             'apple', 'grape', 'orange' ,'pear', 'melon', 'banana', 
   ...:             'watermelon', 'lemon', 'strawberry', 'berry', 'blackberry', 
   ...:             'cherry','pineapple' , 'mango', 'papaya', 'peach', 'coffee', 
   ...:             'planes', 'cars', 'houses', 'dogs', 'cats', 'computers', 
   ...:             'servers', 'sun', 'moon', 'chairs', 'tables', 'screens', 
   ...:             'keyboards', 'shoes', 't-shirts', 'tv', 'radio', 'door', 'windows', 
   ...:             'bed', 'spoon', 'key', 'paper', 'foot', 'bee', 'ants' ,'worm', 
   ...:             'pack', 'phone', 'boat', 'hair', 'yellow', 'disc' 
   ...:     ]) 
   ...:     start = time.time() 
   ...:     idx = pd.MultiIndex.from_product([dates,strings], names=['date', 'words']) 
   ...:     end = time.time() 
   ...:     print("MultiIndex execution time:", end-start) 
   ...:     lenght = idx.shape[0] 
   ...:     start = time.time() 
   ...:     df_1 = pd.DataFrame({'col_A':np.random.randn(lenght), 
   ...:                   'col_2':np.arange(lenght), 
   ...:                   'col_b' : np.random.choice(a=[False, True], size=(lenght)), 
   ...:                   'col_i' : np.random.random_integers(low=0,high=100, size=lenght), 
   ...:             }, index=idx) 
   ...:     end = time.time() 
   ...:     print("data frame creation execution time:", end-start) 
   ...:     df_2 = pd.DataFrame({'col_x':np.arange(lenght)}, 
   ...:                         index=idx) 
   ...:  
   ...:     assert df_1.shape == (1278450, 4) 
   ...:  
   ...:     start = time.time() 
   ...:     df_result = df_1.join(df_2, how='left') 
   ...:     end = time.time() 
   ...:     print("Join execution time:", end - start) 
   ...:     assert df_result.shape == (1278450, 5)                                                                                                                                                                                                               
MultiIndex execution time: 0.02370476722717285

/Users/jreback/miniconda3/envs/pandas/bin/ipython:22: DeprecationWarning: This function is deprecated. Please call randint(0, 100 + 1) instead
data frame creation execution time: 0.07187294960021973
Join execution time: 0.7781379222869873