Pandas pivot_table MultiIndex and dropna=False generates all combinations of modalities instead of keeping existing one only
State: closed

- [ ] closes #18030

There was already another proposed fix #28540 which never concluded. I propose a more straightforward solution to simply deprecate the current behaviour, for the following reasons:

- The current behaviour was never documented. [`pivot_table`](https://pandas.pydata.org/docs/reference/api/pandas.pivot_table.html) says:
   -  dropna: bool, default True: Do not include columns whose entries are all NaN.
   - However the deprecated behaviour is about adding rows.
- In the discussion of #18030 no one could explain the purpose of the Cartesian product, and it is unlogical, there is no case where `pivot_table` should return more rows than what is given by the index input, if `index`, `columns` and `values` are all strict, pairwise disjoint subsets of the original tables columns+index-columns.

If one calls `df.pivot_table(index=df.index.names, columns=..., values=..., dropna=False)`, it currently creates a table with `product(df.index.names)` many rows. However, the documentation specifies for the input `index`:

> If an array is passed, it must be the same length as the data. The list can contain any of the other types (except list). Keys to group by on the pivot table index. If an array is passed, it is being used as the same manner as column values

Which of course is a bit self-contradictory, but the gist is: if `len(index)=len(values)`, then use `index` as the new index, else, use the columns looked up via `df[index]`. This documented behaviour is violated by the current, unlogical behaviour when `dropna=False`

Labels:
name: Stale

Comments:
Hello @randolf-scholz! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:



There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2022-02-15 02:23:56 UTC
@jreback  I think one could just drop the tests if all they do is check for the behaviour that I think should be deprecated. I had a look at the [first failure](https://dev.azure.com/pandas-dev/pandas/_build/results?buildId=74054&view=logs&j=915dbb23-6529-54ae-059d-ec0b59ba10e8&t=86b091c2-17fe-52fe-2454-5f56a371e155&l=1802). The base table is:

<table>
<thead>
  <tr>
    <th></th>
    <th>amount</th>
    <th>customer</th>
    <th>month</th>
    <th>product</th>
    <th>quantity</th>
  </tr>
</thead>
<tbody>
  <tr>
    <td>0</td>
    <td>60000</td>
    <td>A</td>
    <td>201307</td>
    <td>a</td>
    <td>2000000</td>
  </tr>
  <tr>
    <td>1</td>
    <td>100000</td>
    <td>A</td>
    <td>201309</td>
    <td>b</td>
    <td>500000</td>
  </tr>
  <tr>
    <td>2</td>
    <td>50000</td>
    <td>B</td>
    <td>201308</td>
    <td>c</td>
    <td>1000000</td>
  </tr>
  <tr>
    <td>3</td>
    <td>30000</td>
    <td>C</td>
    <td>201310</td>
    <td>d</td>
    <td>1000000</td>
  </tr>
</tbody>
</table>

and it computes `pv_col = df.pivot_table("quantity", "month", ["customer", "product"], dropna=False)`

<table>
<thead>
  <tr>
    <th>customer</th>
    <th colspan="4">A</th>
    <th colspan="4">B</th>
    <th colspan="4">C</th>
  </tr>
</thead>
<tbody>
  <tr>
    <td>product</td>
    <td>a</td>
    <td>b</td>
    <td>c</td>
    <td>d</td>
    <td>a</td>
    <td>b</td>
    <td>c</td>
    <td>d</td>
    <td>a</td>
    <td>b</td>
    <td>c</td>
    <td>d</td>
  </tr>
  <tr>
    <td>month</td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <td>201307</td>
    <td>2000000.0</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>201308</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>1000000.0</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>201309</td>
    <td>NaN</td>
    <td>500000.0</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>201310</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>1000000.0</td>
  </tr>
</tbody>
</table>

and `pv_ind = df.pivot_table("quantity", ["customer", "product"], "month", dropna=False)`

<table>
<thead>
  <tr>
    <th></th>
    <th>month</th>
    <th>201307</th>
    <th>201308</th>
    <th>201309</th>
    <th>201310</th>
  </tr>
</thead>
<tbody>
  <tr>
    <td>customer</td>
    <td>product</td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <td rowspan="4">A</td>
    <td>a</td>
    <td>2000000.0</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>b</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>500000.0</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>c</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>d</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td rowspan="4">B</td>
    <td>a</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>b</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>c</td>
    <td>NaN</td>
    <td>1000000.0</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>d</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td rowspan="4">C</td>
    <td>a</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>b</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>c</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>d</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>1000000.0</td>
  </tr>
</tbody>
</table>

and then just checks whether `pv_col.columns` and `pv_ind.index` is equal to the Cartesian product.

I would argue that actually neither behaviour is documented nor logical. In `pv_col`, the MultiIndex tuples (C,a), (C,b) and (C,c) appear, which are not present in the original data and might not even make semantic sense.

This behaviour should get deprecated as well; the reason I restored it in my 2nd commit is because completely removing it causes that too much gets deleted










@jreback  If a user really wants the full Cartesian product, I think it would make more sense to offer a routine that extends an existing `MultiIndex` to the full Cartesian product of the levels, if such a thing does not exist already.

Again, because one cannot assume a-priori that the full Cartesian product of the level values makes semantic sense, the pivot table should operate conservatively and not add these. This also makes it more efficient. I had a case where the current behaviour bloated the size and runtime of the pivot table by a factor of 10.
@jreback So the actual pivot tables that should be generated in the example are:

<table>
<thead>
  <tr>
    <th>customer</th>
    <th colspan="2">A</th>
    <th>B</th>
    <th>C</th>
  </tr>
</thead>
<tbody>
  <tr>
    <td>product</td>
    <td>a</td>
    <td>b</td>
    <td>c</td>
    <td>d</td>
  </tr>
  <tr>
    <td>month</td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <td>201307</td>
    <td>2000000.0</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>201308</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>1000000.0</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>201309</td>
    <td>NaN</td>
    <td>500000.0</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>201310</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>1000000.0</td>
  </tr>
</tbody>
</table>

and

<table>
<thead>
  <tr>
    <th></th>
    <th>month</th>
    <th>201307</th>
    <th>201308</th>
    <th>201309</th>
    <th>201310</th>
  </tr>
</thead>
<tbody>
  <tr>
    <td>customer</td>
    <td>product</td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <td rowspan="2">A</td>
    <td>a</td>
    <td>2000000.0</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>b</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>500000.0</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>B</td>
    <td>c</td>
    <td>NaN</td>
    <td>1000000.0</td>
    <td>NaN</td>
    <td>NaN</td>
  </tr>
  <tr>
    <td>C</td>
    <td>d</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>NaN</td>
    <td>1000000.0</td>
  </tr>
</tbody>
</table>

Also, as a side note for a potential future issue: it should be noted that the pivot operation converts the integers to float here. It would make more sense to keep the data type of the `values` columns and use pandas nullable data types to add the missing values instead.
this is breaking a lot of tests
This pull request is stale because it has been open for thirty days with no activity. Please [update](https://pandas.pydata.org/pandas-docs/stable/development/contributing.html#updating-your-pull-request) and respond to this comment if you're still interested in working on this.
Thanks for the pull request, but it appears to have gone stale. If interested in continuing, please merge the main branch, address the review comments, and we can reopen.