[ENH] Add DataFrame method to explode a list-like column (GH #16538)
State: closed

Sometimes a values column is presented with list-like values on one row.
Instead we may want to split each individual value onto its own row,
keeping the same mapping to the other key columns. While it's possible
to chain together existing pandas operations (in fact that's exactly
what this implementation is) to do this, the sequence of operations
is not obvious. By contrast this is available as a built-in operation
in say Spark and is a fairly common use case.

- [x] closes #16538
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Labels:
name: API Design

Comments:
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24366?src=pr&el=h1) Report
> Merging [#24366](https://codecov.io/gh/pandas-dev/pandas/pull/24366?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/f6cf7d9e2606e82fd3934e1dac8119c43f5ee643?src=pr&el=desc) will **decrease** coverage by `<.01%`.
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/24366/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/24366?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #24366      +/-   ##
==========================================
- Coverage   92.29%   92.29%   -0.01%     
==========================================
  Files         162      162              
  Lines       51832    51843      +11     
==========================================
+ Hits        47839    47849      +10     
- Misses       3993     3994       +1
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.7% <100%> (ø)` | :arrow_up: |
| #single | `42.97% <9.09%> (-0.01%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/24366?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/24366/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `96.93% <100%> (+0.02%)` | :arrow_up: |
| [pandas/util/testing.py](https://codecov.io/gh/pandas-dev/pandas/pull/24366/diff?src=pr&el=tree#diff-cGFuZGFzL3V0aWwvdGVzdGluZy5weQ==) | `87.57% <0%> (-0.1%)` | :arrow_down: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24366?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24366?src=pr&el=footer). Last update [f6cf7d9...dbd7515](https://codecov.io/gh/pandas-dev/pandas/pull/24366?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24366?src=pr&el=h1) Report
> Merging [#24366](https://codecov.io/gh/pandas-dev/pandas/pull/24366?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/f8b0b9fa2b5811a8c49d7096dbe47254e341054e?src=pr&el=desc) will **increase** coverage by `50.33%`.
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/24366/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/24366?src=pr&el=tree)

```diff
@@             Coverage Diff             @@
##           master   #24366       +/-   ##
===========================================
+ Coverage   41.96%   92.29%   +50.33%     
===========================================
  Files         180      162       -18     
  Lines       50718    51852     +1134     
===========================================
+ Hits        21283    47859    +26576     
+ Misses      29435     3993    -25442
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.7% <100%> (?)` | |
| #single | `42.96% <9.09%> (+1%)` | :arrow_up: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/24366?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/24366/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `96.93% <100%> (+61.82%)` | :arrow_up: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/24366/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `25% <0%> (-58.34%)` | :arrow_down: |
| [pandas/compat/\_\_init\_\_.py](https://codecov.io/gh/pandas-dev/pandas/pull/24366/diff?src=pr&el=tree#diff-cGFuZGFzL2NvbXBhdC9fX2luaXRfXy5weQ==) | `58.3% <0%> (-33.7%)` | :arrow_down: |
| [pandas/io/clipboard/clipboards.py](https://codecov.io/gh/pandas-dev/pandas/pull/24366/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2NsaXBib2FyZC9jbGlwYm9hcmRzLnB5) | `28.23% <0%> (-6.55%)` | :arrow_down: |
| [pandas/plotting/\_misc.py](https://codecov.io/gh/pandas-dev/pandas/pull/24366/diff?src=pr&el=tree#diff-cGFuZGFzL3Bsb3R0aW5nL19taXNjLnB5) | `38.68% <0%> (-4.56%)` | :arrow_down: |
| [pandas/io/formats/console.py](https://codecov.io/gh/pandas-dev/pandas/pull/24366/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvY29uc29sZS5weQ==) | `74.24% <0%> (-3.89%)` | :arrow_down: |
| [pandas/\_libs/\_\_init\_\_.py](https://codecov.io/gh/pandas-dev/pandas/pull/24366/diff?src=pr&el=tree#diff-cGFuZGFzL19saWJzL19faW5pdF9fLnB5) | `100% <0%> (ø)` | :arrow_up: |
| [pandas/io/api.py](https://codecov.io/gh/pandas-dev/pandas/pull/24366/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2FwaS5weQ==) | `100% <0%> (ø)` | :arrow_up: |
| [pandas/\_libs/tslibs/\_\_init\_\_.py](https://codecov.io/gh/pandas-dev/pandas/pull/24366/diff?src=pr&el=tree#diff-cGFuZGFzL19saWJzL3RzbGlicy9fX2luaXRfXy5weQ==) | `100% <0%> (ø)` | :arrow_up: |
| [pandas/core/api.py](https://codecov.io/gh/pandas-dev/pandas/pull/24366/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvYXBpLnB5) | `100% <0%> (ø)` | :arrow_up: |
| ... and [169 more](https://codecov.io/gh/pandas-dev/pandas/pull/24366/diff?src=pr&el=tree-more) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24366?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24366?src=pr&el=footer). Last update [f8b0b9f...0323ce3](https://codecov.io/gh/pandas-dev/pandas/pull/24366?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

explode is a much better name here and more common i the sql world
Yeah, Spark has also adopted the explode terminology. AFAICT the name comes from the fact that in full implementations you can pass in function to be applied to a column with arbitrary nested data and extracts a flat sequence to then be separated onto their own rows.
re: `Series.explode` most of the use cases I've come across would use `DataFrame.explode` since I almost always still want to join it back to the rest of the data once exploded. That being said, I'm certainly not opposed to `Series.explode`, but my preference would be to keep this PR simple and add `Series.explode` if we see demand from the community.
Hello @changhiskhan! 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 2019-06-27 18:30:25 UTC
@TomAugspurger I'll rebase after you merge in #24372 to make sure the benchmarks pass
@changhiskhan can you merge master and update
@changhiskhan can you merge master & update
Think this would be a welcome change but closing as stale. Ping if you'd like to continue
I spend some time on StackOverflow, and the need to be able to unnest/explode a list to rows is a question that comes by a lot. If you guys are interested, here are two SO posts that propose a range of solutions, that might be handy for you guys or bring up some new ideas.

- [First](https://stackoverflow.com/questions/53218931/how-to-unnest-explode-a-column-in-a-pandas-dataframe/53218939#53218939)
- [Second](https://stackoverflow.com/a/51752361/9081267)
@ErfPy this is a fine PR just needs some love
be the first to resubmit it
@jreback would love to, I have many ideas, also would like to contribute to extend the docs, just have a hard time how to do that through git honestly. Have to spend some more time on how to make a good PR first I guess.

reopening, I will see what I can do with this in next few days.
superseded by #27267