<h4 dir="auto">Problem description</h4>
<p dir="auto">The sparse design in Pandas is really very neat. However, I find the sparse documentation to be focused on syntax and structure, while not explaining practical applications of the sparse data structures.</p>
<p dir="auto">Practical application should include operations on series and dataframes that produce innately sparse results. One such example is <code class="notranslate">pivot</code> (and <code class="notranslate">pivot_table</code>). Pivot induces sparsity by creating elements corresponding to the cartesian product of the new index and column values. It should be able to produce a sparse DataFrame as its output, either using automatic heuristics or by accepting some kind of <code class="notranslate">sparse=bool</code> parameter.</p>
<h4 dir="auto">Code Sample</h4>
<p dir="auto">I would like to be able to do:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="&gt;&gt;&gt; df = pd.DataFrame({'x': ['a', 'a', 'c', 'a', 'b', 'b'], 'y': ['A', 'B', 'C', 'D', 'A', 'B'], 'val': [5, 6, 7, 8, 9, 10]}).pivot('x', 'y', 'val')
&gt;&gt;&gt; table = df.pivot('x', 'y', 'val', sparse=True)  # or sparse='auto'
&gt;&gt;&gt; table.density
0.5"><pre class="notranslate"><span class="pl-c1">&gt;&gt;</span><span class="pl-c1">&gt;</span> <span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>({<span class="pl-s">'x'</span>: [<span class="pl-s">'a'</span>, <span class="pl-s">'a'</span>, <span class="pl-s">'c'</span>, <span class="pl-s">'a'</span>, <span class="pl-s">'b'</span>, <span class="pl-s">'b'</span>], <span class="pl-s">'y'</span>: [<span class="pl-s">'A'</span>, <span class="pl-s">'B'</span>, <span class="pl-s">'C'</span>, <span class="pl-s">'D'</span>, <span class="pl-s">'A'</span>, <span class="pl-s">'B'</span>], <span class="pl-s">'val'</span>: [<span class="pl-c1">5</span>, <span class="pl-c1">6</span>, <span class="pl-c1">7</span>, <span class="pl-c1">8</span>, <span class="pl-c1">9</span>, <span class="pl-c1">10</span>]}).<span class="pl-en">pivot</span>(<span class="pl-s">'x'</span>, <span class="pl-s">'y'</span>, <span class="pl-s">'val'</span>)
<span class="pl-c1">&gt;&gt;</span><span class="pl-c1">&gt;</span> <span class="pl-s1">table</span> <span class="pl-c1">=</span> <span class="pl-s1">df</span>.<span class="pl-en">pivot</span>(<span class="pl-s">'x'</span>, <span class="pl-s">'y'</span>, <span class="pl-s">'val'</span>, <span class="pl-s1">sparse</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)  <span class="pl-c"># or sparse='auto'</span>
<span class="pl-c1">&gt;&gt;</span><span class="pl-c1">&gt;</span> <span class="pl-s1">table</span>.<span class="pl-s1">density</span>
<span class="pl-c1">0.5</span></pre></div>
<h4 dir="auto">Output of <code class="notranslate">pd.show_versions()</code></h4>
<details>
<h2 dir="auto">INSTALLED VERSIONS</h2>
<p dir="auto">commit: None<br>
python: 3.5.2.final.0<br>
python-bits: 64<br>
OS: Darwin<br>
OS-release: 17.3.0<br>
machine: x86_64<br>
processor: i386<br>
byteorder: little<br>
LC_ALL: None<br>
LANG: en_AU.UTF-8<br>
LOCALE: en_AU.UTF-8</p>
<p dir="auto">pandas: 0.20.1<br>
pytest: 3.3.1<br>
pip: 9.0.1<br>
setuptools: 35.0.2<br>
Cython: 0.24.1<br>
numpy: 1.11.1<br>
scipy: 0.18.1<br>
xarray: None<br>
IPython: 5.1.0<br>
sphinx: 1.5<br>
patsy: 0.4.1<br>
dateutil: 2.5.3<br>
pytz: 2016.6.1<br>
blosc: None<br>
bottleneck: 1.1.0<br>
tables: 3.2.3.1<br>
numexpr: 2.6.1<br>
feather: None<br>
matplotlib: 1.5.3<br>
openpyxl: 1.6.2<br>
xlrd: 1.0.0<br>
xlwt: 1.1.2<br>
xlsxwriter: 0.9.3<br>
lxml: None<br>
bs4: 4.5.1<br>
html5lib: 0.999999999<br>
sqlalchemy: 1.0.13<br>
pymysql: None<br>
psycopg2: None<br>
jinja2: 2.8<br>
s3fs: None<br>
pandas_gbq: None<br>
pandas_datareader: None</p>
</details>
<h4 dir="auto">Code Sample, a copy-pastable example if possible</h4>
<p dir="auto">I'm currently in the middle of upgrading old system from old pandas (0.12) to the new version (0.23.0). One of the parts of the system is duplicate columns detection in medium-sized DataFrames (~100 columns, few thousand rows). We were detecting it like this <code class="notranslate">dupes = df.T.duplicated()</code> and previously it worked but after the upgrade, it started failing. Simplest snippet to reproduce this locally</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import numpy as np
import pandas as pd

data = {}
for i in range(70):
    data['col_{0:02d}'.format(i)] = np.random.randint(0, 1000, 20000)
df = pd.DataFrame(data)
dupes = df.T.duplicated()
print dupes"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">numpy</span> <span class="pl-k">as</span> <span class="pl-s1">np</span>
<span class="pl-k">import</span> <span class="pl-s1">pandas</span> <span class="pl-k">as</span> <span class="pl-s1">pd</span>

<span class="pl-s1">data</span> <span class="pl-c1">=</span> {}
<span class="pl-k">for</span> <span class="pl-s1">i</span> <span class="pl-c1">in</span> <span class="pl-en">range</span>(<span class="pl-c1">70</span>):
    <span class="pl-s1">data</span>[<span class="pl-s">'col_{0:02d}'</span>.<span class="pl-en">format</span>(<span class="pl-s1">i</span>)] <span class="pl-c1">=</span> <span class="pl-s1">np</span>.<span class="pl-s1">random</span>.<span class="pl-en">randint</span>(<span class="pl-c1">0</span>, <span class="pl-c1">1000</span>, <span class="pl-c1">20000</span>)
<span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>(<span class="pl-s1">data</span>)
<span class="pl-s1">dupes</span> <span class="pl-c1">=</span> <span class="pl-s1">df</span>.<span class="pl-v">T</span>.<span class="pl-en">duplicated</span>()
<span class="pl-k">print</span> <span class="pl-s1">dupes</span></pre></div>
<h4 dir="auto">Problem description</h4>
<p dir="auto">To the contrast of the note below, this issue isn't resolved by upgrading to the newest pandas. On the contrary, it is caused by such upgrade :) Old version I've copied below from 0.12 works on a snippet above</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="def old_duplicated(self, cols=None, take_last=False):
    &quot;&quot;&quot;
    Return boolean Series denoting duplicate rows, optionally only
    considering certain columns

    Parameters
    ----------
    cols : column label or sequence of labels, optional
        Only consider certain columns for identifying duplicates, by
        default use all of the columns
    take_last : boolean, default False
        Take the last observed row in a row. Defaults to the first row

    Returns
    -------
    duplicated : Series
    &quot;&quot;&quot;

    # kludge for #1833
    def _m8_to_i8(x):
        if issubclass(x.dtype.type, np.datetime64):
            return x.view(np.int64)
        return x

    if cols is None:
        values = list(_m8_to_i8(self.values.T))
    else:
        if np.iterable(cols) and not isinstance(cols, basestring):
            if isinstance(cols, tuple):
                if cols in self.columns:
                    values = [self[cols]]
                else:
                    values = [_m8_to_i8(self[x].values) for x in cols]
            else:
                values = [_m8_to_i8(self[x].values) for x in cols]
        else:
            values = [self[cols]]

    keys = lib.fast_zip_fillna(values)
    duplicated = lib.duplicated(keys, take_last=take_last)
    return pd.Series(duplicated, index=self.index)"><pre class="notranslate"><span class="pl-k">def</span> <span class="pl-en">old_duplicated</span>(<span class="pl-s1">self</span>, <span class="pl-s1">cols</span><span class="pl-c1">=</span><span class="pl-c1">None</span>, <span class="pl-s1">take_last</span><span class="pl-c1">=</span><span class="pl-c1">False</span>):
    <span class="pl-s">"""</span>
<span class="pl-s">    Return boolean Series denoting duplicate rows, optionally only</span>
<span class="pl-s">    considering certain columns</span>
<span class="pl-s"></span>
<span class="pl-s">    Parameters</span>
<span class="pl-s">    ----------</span>
<span class="pl-s">    cols : column label or sequence of labels, optional</span>
<span class="pl-s">        Only consider certain columns for identifying duplicates, by</span>
<span class="pl-s">        default use all of the columns</span>
<span class="pl-s">    take_last : boolean, default False</span>
<span class="pl-s">        Take the last observed row in a row. Defaults to the first row</span>
<span class="pl-s"></span>
<span class="pl-s">    Returns</span>
<span class="pl-s">    -------</span>
<span class="pl-s">    duplicated : Series</span>
<span class="pl-s">    """</span>

    <span class="pl-c"># kludge for #1833</span>
    <span class="pl-k">def</span> <span class="pl-en">_m8_to_i8</span>(<span class="pl-s1">x</span>):
        <span class="pl-k">if</span> <span class="pl-en">issubclass</span>(<span class="pl-s1">x</span>.<span class="pl-s1">dtype</span>.<span class="pl-s1">type</span>, <span class="pl-s1">np</span>.<span class="pl-s1">datetime64</span>):
            <span class="pl-k">return</span> <span class="pl-s1">x</span>.<span class="pl-en">view</span>(<span class="pl-s1">np</span>.<span class="pl-s1">int64</span>)
        <span class="pl-k">return</span> <span class="pl-s1">x</span>

    <span class="pl-k">if</span> <span class="pl-s1">cols</span> <span class="pl-c1">is</span> <span class="pl-c1">None</span>:
        <span class="pl-s1">values</span> <span class="pl-c1">=</span> <span class="pl-en">list</span>(<span class="pl-en">_m8_to_i8</span>(<span class="pl-s1">self</span>.<span class="pl-s1">values</span>.<span class="pl-v">T</span>))
    <span class="pl-k">else</span>:
        <span class="pl-k">if</span> <span class="pl-s1">np</span>.<span class="pl-en">iterable</span>(<span class="pl-s1">cols</span>) <span class="pl-c1">and</span> <span class="pl-c1">not</span> <span class="pl-en">isinstance</span>(<span class="pl-s1">cols</span>, <span class="pl-s1">basestring</span>):
            <span class="pl-k">if</span> <span class="pl-en">isinstance</span>(<span class="pl-s1">cols</span>, <span class="pl-s1">tuple</span>):
                <span class="pl-k">if</span> <span class="pl-s1">cols</span> <span class="pl-c1">in</span> <span class="pl-s1">self</span>.<span class="pl-s1">columns</span>:
                    <span class="pl-s1">values</span> <span class="pl-c1">=</span> [<span class="pl-s1">self</span>[<span class="pl-s1">cols</span>]]
                <span class="pl-k">else</span>:
                    <span class="pl-s1">values</span> <span class="pl-c1">=</span> [<span class="pl-en">_m8_to_i8</span>(<span class="pl-s1">self</span>[<span class="pl-s1">x</span>].<span class="pl-s1">values</span>) <span class="pl-k">for</span> <span class="pl-s1">x</span> <span class="pl-c1">in</span> <span class="pl-s1">cols</span>]
            <span class="pl-k">else</span>:
                <span class="pl-s1">values</span> <span class="pl-c1">=</span> [<span class="pl-en">_m8_to_i8</span>(<span class="pl-s1">self</span>[<span class="pl-s1">x</span>].<span class="pl-s1">values</span>) <span class="pl-k">for</span> <span class="pl-s1">x</span> <span class="pl-c1">in</span> <span class="pl-s1">cols</span>]
        <span class="pl-k">else</span>:
            <span class="pl-s1">values</span> <span class="pl-c1">=</span> [<span class="pl-s1">self</span>[<span class="pl-s1">cols</span>]]

    <span class="pl-s1">keys</span> <span class="pl-c1">=</span> <span class="pl-s1">lib</span>.<span class="pl-en">fast_zip_fillna</span>(<span class="pl-s1">values</span>)
    <span class="pl-s1">duplicated</span> <span class="pl-c1">=</span> <span class="pl-s1">lib</span>.<span class="pl-en">duplicated</span>(<span class="pl-s1">keys</span>, <span class="pl-s1">take_last</span><span class="pl-c1">=</span><span class="pl-s1">take_last</span>)
    <span class="pl-k">return</span> <span class="pl-s1">pd</span>.<span class="pl-v">Series</span>(<span class="pl-s1">duplicated</span>, <span class="pl-s1">index</span><span class="pl-c1">=</span><span class="pl-s1">self</span>.<span class="pl-s1">index</span>)</pre></div>
<p dir="auto">but the new one now fails with</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;/home/modintsov/workspace/DataRobot/playground.py&quot;, line 56, in &lt;module&gt;
    dupes = df.T.duplicated()
  File &quot;/home/modintsov/.virtualenvs/dev/local/lib/python2.7/site-packages/pandas/core/frame.py&quot;, line 4384, in duplicated
    ids = get_group_index(labels, shape, sort=False, xnull=False)
  File &quot;/home/modintsov/.virtualenvs/dev/local/lib/python2.7/site-packages/pandas/core/sorting.py&quot;, line 95, in get_group_index
    return loop(list(labels), list(shape))
  File &quot;/home/modintsov/.virtualenvs/dev/local/lib/python2.7/site-packages/pandas/core/sorting.py&quot;, line 86, in loop
    return loop(labels, shape)

... many-many lines of the same...

  File &quot;/home/modintsov/.virtualenvs/dev/local/lib/python2.7/site-packages/pandas/core/sorting.py&quot;, line 60, in loop
    stride = np.prod(shape[1:nlev], dtype='i8')
  File &quot;/home/modintsov/.virtualenvs/dev/local/lib/python2.7/site-packages/numpy/core/fromnumeric.py&quot;, line 2566, in prod
    out=out, **kwargs)
RuntimeError: maximum recursion depth exceeded"><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "/home/modintsov/workspace/DataRobot/playground.py", line 56, in &lt;module&gt;
    dupes = df.T.duplicated()
  File "/home/modintsov/.virtualenvs/dev/local/lib/python2.7/site-packages/pandas/core/frame.py", line 4384, in duplicated
    ids = get_group_index(labels, shape, sort=False, xnull=False)
  File "/home/modintsov/.virtualenvs/dev/local/lib/python2.7/site-packages/pandas/core/sorting.py", line 95, in get_group_index
    return loop(list(labels), list(shape))
  File "/home/modintsov/.virtualenvs/dev/local/lib/python2.7/site-packages/pandas/core/sorting.py", line 86, in loop
    return loop(labels, shape)

... many-many lines of the same...

  File "/home/modintsov/.virtualenvs/dev/local/lib/python2.7/site-packages/pandas/core/sorting.py", line 60, in loop
    stride = np.prod(shape[1:nlev], dtype='i8')
  File "/home/modintsov/.virtualenvs/dev/local/lib/python2.7/site-packages/numpy/core/fromnumeric.py", line 2566, in prod
    out=out, **kwargs)
RuntimeError: maximum recursion depth exceeded
</code></pre></div>
<p dir="auto">Which is obviously a regression.</p>
<p dir="auto">[this should explain <strong>why</strong> the current behaviour is a problem and why the expected output is a better solution.]</p>
<p dir="auto"><strong>Note</strong>: We receive a lot of issues on our GitHub tracker, so it is very possible that your issue has been posted before. Please check first before submitting so that we do not have to handle and close duplicates!</p>
<p dir="auto"><strong>Note</strong>: Many problems can be resolved by simply upgrading <code class="notranslate">pandas</code> to the latest version. Before submitting, please check if that solution works for you. If possible, you may want to check if <code class="notranslate">master</code> addresses this issue, but that is not necessary.</p>
<p dir="auto">For documentation-related issues, you can check the latest versions of the docs on <code class="notranslate">master</code> here:</p>
<p dir="auto"><a href="https://pandas-docs.github.io/pandas-docs-travis/" rel="nofollow">https://pandas-docs.github.io/pandas-docs-travis/</a></p>
<p dir="auto">If the issue has not been resolved there, go ahead and file it in the issue tracker.</p>
<h4 dir="auto">Expected Output</h4>
<p dir="auto">I expect no exception and return of bool Series. Example above in old pandas output this</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="col_00    False
col_01    False
col_02    False
col_03    False
col_04    False
col_05    False
col_06    False
col_07    False
col_08    False
col_09    False
col_10    False
col_11    False
col_12    False
col_13    False
col_14    False
...
col_55    False
col_56    False
col_57    False
col_58    False
col_59    False
col_60    False
col_61    False
col_62    False
col_63    False
col_64    False
col_65    False
col_66    False
col_67    False
col_68    False
col_69    False
Length: 70, dtype: bool"><pre class="notranslate"><code class="notranslate">col_00    False
col_01    False
col_02    False
col_03    False
col_04    False
col_05    False
col_06    False
col_07    False
col_08    False
col_09    False
col_10    False
col_11    False
col_12    False
col_13    False
col_14    False
...
col_55    False
col_56    False
col_57    False
col_58    False
col_59    False
col_60    False
col_61    False
col_62    False
col_63    False
col_64    False
col_65    False
col_66    False
col_67    False
col_68    False
col_69    False
Length: 70, dtype: bool
</code></pre></div>
<h4 dir="auto">Output of <code class="notranslate">pd.show_versions()</code></h4>
<details>
<h2 dir="auto">[paste the output of <code class="notranslate">pd.show_versions()</code> here below this line]<br>
pd.show_versions()<br>
INSTALLED VERSIONS</h2>
<p dir="auto">commit: None<br>
python: 2.7.12.final.0<br>
python-bits: 64<br>
OS: Linux<br>
OS-release: 4.4.0-128-generic<br>
machine: x86_64<br>
processor: x86_64<br>
byteorder: little<br>
LC_ALL: None<br>
LANG: en_US.UTF-8<br>
LOCALE: None.None<br>
pandas: 0.23.0<br>
pytest: 3.5.1<br>
pip: 9.0.1<br>
setuptools: 39.2.0<br>
Cython: 0.21<br>
numpy: 1.14.3<br>
scipy: 1.1.0<br>
pyarrow: None<br>
xarray: None<br>
IPython: 5.5.0<br>
sphinx: 1.5.5<br>
patsy: 0.2.1<br>
dateutil: 2.7.3<br>
pytz: 2015.7<br>
blosc: None<br>
bottleneck: None<br>
tables: None<br>
numexpr: 2.6.5<br>
feather: None<br>
matplotlib: None<br>
openpyxl: None<br>
xlrd: 0.9.2<br>
xlwt: 0.7.5<br>
xlsxwriter: None<br>
lxml: None<br>
bs4: 4.6.0<br>
html5lib: None<br>
sqlalchemy: 1.2.7<br>
pymysql: None<br>
psycopg2: 2.7.3.2.dr2 (dt dec pq3 ext lo64)<br>
jinja2: 2.10<br>
s3fs: None<br>
fastparquet: None<br>
pandas_gbq: None<br>
pandas_datareader: None</p>
</details>