Hi pandas gurus,

I started using pandas hoping to get some large scale fast pivoting
capability to convert long form data coming out of a database to a nice
table. I have 4 columns as keys and one numerical value to average across
possibly ~2500 unique column names. The CSV file is ~3-5M rows. I have
Constructed a similar test DataFrame below to show the error. Any help is
appreciated!

With NUM_ROWS=100:      It works :) But I have 2.5M rows.
With NUM_ROWS=1000:    IndexError: index out of range for array
With NUM_ROWS=10000:  ValueError: negative dimensions are not allowed

With NUM_ROWS=10000000 & pivot.table(rows=['A', 'B', 'C'], it works! (Maxed
out at 24GB ram :D)

Out:
<class 'pandas.core.frame.DataFrame'>
MultiIndex: 381231 entries, (0, 0, -7) to (99, 299, 6)
Columns: 3000 entries, 0 to 2999
dtypes: float64(3000)

pandas.__verson__ = '0.9.1rc1'
numpy.__version__ = '1.6.2'

In [1]:

import pandas as pd

import numpy as np

In [2]:

# Generate Long File & Test Pivot

NUM_ROWS = 1000

df = pd.DataFrame({'A' : np.random.randint(100, size=NUM_ROWS), 'B' : np.random.randint(300, size=NUM_ROWS), 'C' : np.random.randint(-7, 7, size=NUM_ROWS), 'D' : np.random.randint(-19,19, size=NUM_ROWS),'E' : np.random.randint(3000, size=NUM_ROWS),'F' : np.random.randn(NUM_ROWS)})

df_pivoted = df.pivot_table(rows=['A', 'B', 'C', 'D'], cols='E', values='F')

df_pivoted

--------------------------------------------------------------------------- IndexError                                Traceback (most recent call last)<ipython-input-2-21cdba88317b> in <module>()      2 NUM_ROWS = 1000      3 df = pd.DataFrame({'A' : np.random.randint(100, size=NUM_ROWS), 'B' : np.random.randint(300, size=NUM_ROWS), 'C' : np.random.randint(-7, 7, size=NUM_ROWS), 'D' : np.random.randint(-19,19, size=NUM_ROWS),'E' : np.random.randint(3000, size=NUM_ROWS),'F' : np.random.randn(NUM_ROWS)})----> 4 df_pivoted = df.pivot_table(rows=['A', 'B', 'C', 'D'], cols='E', values='F')      5 df_pivoted
C:\Python27\lib\site-packages\pandas\tools\pivot.pyc in pivot_table(data, values, rows, cols, aggfunc, fill_value, margins)    103                   for i in range(len(rows), len(keys))]    104 --> 105     table = agged.unstack(to_unstack)    106     107     if isinstance(table, DataFrame):
C:\Python27\lib\site-packages\pandas\core\frame.pyc in unstack(self, level)   3694         """   3695         from pandas.core.reshape import unstack-> 3696         return unstack(self, level)   3697    3698     #----------------------------------------------------------------------
C:\Python27\lib\site-packages\pandas\core\reshape.pyc in unstack(obj, level)    357 def unstack(obj, level):    358     if isinstance(level, (tuple, list)):--> 359         return _unstack_multiple(obj, level)    360     361     if isinstance(obj, DataFrame):
C:\Python27\lib\site-packages\pandas\core\reshape.pyc in _unstack_multiple(data, clocs)    260                           columns=data.columns)    261 --> 262         unstacked = dummy.unstack('__placeholder__')    263         if isinstance(unstacked, Series):    264             unstcols = unstacked.index
C:\Python27\lib\site-packages\pandas\core\frame.pyc in unstack(self, level)   3694         """   3695         from pandas.core.reshape import unstack-> 3696         return unstack(self, level)   3697    3698     #----------------------------------------------------------------------
C:\Python27\lib\site-packages\pandas\core\reshape.pyc in unstack(obj, level)    361     if isinstance(obj, DataFrame):    362         if isinstance(obj.index, MultiIndex):--> 363             return _unstack_frame(obj, level)    364         else:    365             return obj.T.stack(dropna=False)
C:\Python27\lib\site-packages\pandas\core\reshape.pyc in _unstack_frame(obj, level)    399     else:    400         unstacker = _Unstacker(obj.values, obj.index, level=level,--> 401                                value_columns=obj.columns)    402         return unstacker.get_result()    403
C:\Python27\lib\site-packages\pandas\core\reshape.pyc in __init__(self, values, index, level, value_columns)     78      79         self._make_sorted_values_labels()---> 80         self._make_selectors()     81      82     def _make_sorted_values_labels(self):
C:\Python27\lib\site-packages\pandas\core\reshape.pyc in _make_selectors(self)    117         selector = self.sorted_labels[-1] + stride * group_index    118         mask = np.zeros(np.prod(self.full_shape), dtype=bool)--> 119         mask.put(selector, True)    120     121         # compress labels
IndexError: index out of range for array

Here is the code not clipped:

import pandas as pd
import numpy as np

# Generate Long File & Test Pivot
NUM_ROWS = 1000000
df = pd.DataFrame({'A' : np.random.randint(100, size=NUM_ROWS),
                                'B' : np.random.randint(300,
size=NUM_ROWS),
                                'C' : np.random.randint(-7, 7,
size=NUM_ROWS),
                                'D' : np.random.randint(-19,19,
size=NUM_ROWS),
                                'E' : np.random.randint(3000,
size=NUM_ROWS),
                                'F' : np.random.randn(NUM_ROWS)})

df_pivoted = df.pivot_table(rows=['A', 'B', 'C'], cols='E', values='F')
df_pivoted

And Here is the code that fails. Note that in the code below I am pivoting
on combinations of 4 Columns A, B, C, D, and this fails for only 1000 rows,
but the above code works on 1M rows.

import pandas as pd
import numpy as np

# Generate Long File & Test Pivot
NUM_ROWS = 1000
df = pd.DataFrame({'A' : np.random.randint(100, size=NUM_ROWS),
                                'B' : np.random.randint(300,
size=NUM_ROWS),
                                'C' : np.random.randint(-7, 7,
size=NUM_ROWS),
                                'D' : np.random.randint(-19,19,
size=NUM_ROWS),
                                'E' : np.random.randint(3000,
size=NUM_ROWS),
                                'F' : np.random.randn(NUM_ROWS)})

df_pivoted = df.pivot_table(rows=['A', 'B', 'C', 'D'], cols='E', values='F')
df_pivoted

Thanks,
Wes

Thanks Wes!

I'll gladly test it out once the issue has been tracked down.

Thanks,
-Gagi

Hi Gagi,

- Wes

- Wes

-Gagi