ENH: Add Series method to explode a list-like column
State: closed

replaces #24366 
closes #16538 
closes #10511 

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

provides a nice inversion here
```
In [31]: s = pd.DataFrame({'a': pd.date_range('20190101', periods=3, tz='UTC')}).apply(lambda x: x.array, axis=1)                                                                                                                                                             

In [32]: s                                                                                                                                                                                                                                                                    
Out[32]: 
0    [2019-01-01 00:00:00+00:00]
1    [2019-01-02 00:00:00+00:00]
2    [2019-01-03 00:00:00+00:00]
dtype: object

In [33]: s.iloc[0]                                                                                                                                                                                                                                                            
Out[33]: 
<DatetimeArray>
['2019-01-01 00:00:00+00:00']
Length: 1, dtype: datetime64[ns, UTC]

In [34]: s.explode()                                                                                                                                                                                                                                                          
Out[34]: 
0   2019-01-01 00:00:00+00:00
1   2019-01-02 00:00:00+00:00
2   2019-01-03 00:00:00+00:00
dtype: datetime64[ns, UTC]
```

```
In [4]: s = pd.Series([np.arange(np.random.randint(100)) for _ in range(100000)])                                                                                                                                                                            

In [5]: s                                                                                                                                                                                                                                                    
Out[5]: 
0        [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,...
1        [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,...
2        [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,...
3                                          [0, 1, 2, 3, 4]
4        [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,...
                               ...                        
99995    [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,...
99996    [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,...
99997    [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,...
99998                                         [0, 1, 2, 3]
99999    [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,...
Length: 100000, dtype: object

In [6]: s.explode()                                                                                                                                                                                                                                          
Out[6]: 
0         0
0         1
0         2
0         3
0         4
         ..
99999    43
99999    44
99999    45
99999    46
99999    47
Length: 4950205, dtype: object

In [7]: %timeit s.explode()                                                                                                                                                                                                                                  
584 ms ± 17.9 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
```

Dataframe exploding
```
In [1]: df = pd.DataFrame( 
   ...:      [[11, range(5), 10], [22, range(3), 20]], columns=["A", "B", "C"] 
   ...: ).set_index("C") 
   ...: df                                                                                                                                                                                                                                                   
Out[1]: 
     A                B
C                      
10  11  (0, 1, 2, 3, 4)
20  22        (0, 1, 2)

In [2]: df.explode(['B'])                                                                                                                                                                                                                                    
Out[2]: 
     A  B
C        
10  11  0
10  11  1
10  11  2
10  11  3
10  11  4
20  22  0
20  22  1
20  22  2
```


Labels:
name: Enhancement
name: Reshaping
description: Concat, Merge/Join, Stack/Unstack, Explode

Comments:
Hello @jreback! 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-07-17 19:24:32 UTC
cc @changhiskhan 

this is actually *much* simpler on Series; I did this in cython; should be pretty performant.
The primary use-case in the original PR/issues is to end up with an [expanded dataframe](https://github.com/pandas-dev/pandas/pull/24366#issuecomment-449081724).  

Making this a `Series` method is cleaner code-wise, but makes it far more cumbersome to use.

This (and the doc example) demands:
```python
df.drop(['col1','col2'],axis=1).join([df['col1'].explode(),df['col2'].explode()])[df.columns]
```
What would make users happy is:
```
df.explode(['col1','col2'])
```

How about adding some sugar? Making it available is good, but making it usable is as important.

@pilkibun I don't think we want to add that here but could be a follow up if something you want to tackle
>  I don't think we want to add that here

That's what the original PR did, it's what the original issue requested, and what the SO questions were asking for. So why on earth would you *not* include it? and why then would a followup PR by me make any difference?

@cpcloud how does this compare to semantics of unnest in various backends (postgres)?
@jreback, would you please add a DataFrame method for convenience? I think users will find this feature cumbersome without it.
I'd be -0 on adding a `DataFrame.explode` since the result can idiomatically be obtained by chaining other pandas methods. 
> I'd be -0 on adding a DataFrame.explode since the result can idiomatically be obtained by chaining 
> other pandas methods.

@mroeschke, yes, but it's very verbose. See https://github.com/pandas-dev/pandas/pull/27267#issuecomment-508970900. All the user requests specifically care about making the dataframe case painless.
I am +1 on adding the DataFrame.explode method (so have both DataFrame and Series method), as the original PR did. I agree with @changhiskhan (https://github.com/pandas-dev/pandas/pull/24366/files#r243373508) that this is the most typical use case, so I don't see why we would not provide this convenience.
cc @jorisvandenbossche 
I added the ``df.explode(subset=list-of-columns)`` (see the top of the PR); didn't update the docs or respond to your comments yet.

I even support multi-column expoding; though this is likely not very performant (as it needs a recursive merge); but its there. This may be a bit of overkill and we should *just* allow a single column explosion.
> API questions:
> 
> * For empty list, do we want a NaN? Other option could also no entry in the result? (just wondering if this was discussed, or if there is prior art, didn't really think through or about use cases)

We could, but I think marking as nan is more indicative of pandas to preserve nulls (so here the 'null' is the fact that we had an empty) but could be convinced otherwise.

> * Do we want to return a MultiIndex keeping track of a counter for the values coming from the same original row? (or have an option to enable this) That would actually make it possible to combine this with `unstack` to produce multiple columns (a function which is linked as related)

I tried this and its *pretty easy* to do this. But then I find the subsequent behavior when you merge completely non-intuitive (and .join() doesn't handle this even, so its much more complicated).


Note that exploding *multiple* columns in a DataFrame I am not too convinced we need. Its easy to support, but may not make sense to do in this current implementation (we could always add it later, though the subset argument must currently be a list, but could relax this and only accept a scalar column name if we drop this functionaility).
> Note that exploding _multiple_ columns in a DataFrame I am not too convinced we need.

Current implementation assumes a Cartesian product is wanted here right? I'm not sure that is always correct so would rather punt that behavior 

Citing arguably similar behavior with unnest in Postgres

<img width="432" alt="image" src="https://user-images.githubusercontent.com/609873/61015950-b61c7c80-a342-11e9-8ac8-80c4f5550a14.png">

A LATERAL join would be used to actually generate that product

<img width="592" alt="image" src="https://user-images.githubusercontent.com/609873/61015986-d77d6880-a342-11e9-8e3e-457469b2fe88.png">


PS found some of this in below SO post, though I think even Postgres' handling of this changed over time when looking at OP and comparing to this behavior captured on 11.4

https://stackoverflow.com/a/23004157/621736
removed the multi-column support on DataFrame.
> removed the multi-column support on DataFrame.

The single version is already a boon. Thanks! 

Just making sure, is original column order preserved?




> > removed the multi-column support on DataFrame.
> 
> The single version is already a boon. Thanks!
> 
> Just making sure, is original column order preserved?

yes
@jorisvandenbossche any additional comments when you have a chance
cc @TomAugspurger 
\+1 on limiting it to single columns of a DataFrame for now, as you indeed have options around how to combine multiple columns. In those cases you can still explode single columns separately and join in the way you want.

> > For empty list, do we want a NaN? Other option could also no entry in the result? 
>
> We could, but I think marking as nan is more indicative of pandas to preserve nulls (so here the 'null' is the fact that we had an empty) but could be convinced otherwise.

For a user, an empty list might not necessarily mean "null" (depending on your use case), as you can also have an actual NaN in the data. So currently there is no distinction in the result between both. Now, if you follow-it up with joining back to the rest of the dataframe (what the dataframe method also does), then you get a NaN anyway, so not sure this is important.

> > Do we want to return a MultiIndex keeping track of a counter for the values coming from the same original row? (or have an option to enable this) That would actually make it possible to combine this with unstack to produce multiple columns (a function which is linked as related)
>
> I tried this and its pretty easy to do this. But then I find the subsequent behavior when you merge completely non-intuitive (and .join() doesn't handle this even, so its much more complicated).

Yes, the use case is certainly not for then joining again (so it should also not be a default behaviour). The use case is to do an unstack afterwards, so you have the option to explode in multiple columns, instead of multiple rows.
For the empty list discussion, using Will's example from above, it gives 0 rows for an empty array (so no NULL):

```
test_db=# select a FROM unnest(array[]::integer[]) a;
 a 
---
(0 rows)
```

But, a NULL in the original data is also dropped when unnesting it, so both give here the same result as well, so not sure this is a good reference point. 
Does somebody know what spark does?

I personally find this intuitive to return NA for empty containers. If someone is trying to make the distinction between whether the input was NA to begin or an empty container I don't think using the result of this expression would be the easiest way to go about that regardless
Looks good, would this also work on a string concatenated by a delimiter? See [this](https://stackoverflow.com/a/51752361/9081267) method, differentiating between `explode_list` and `explode_str`. Whereas you can specify the delimiter in `explode_str`

```
df = pd.DataFrame({'Col1':[';'.join(list('abcdefg')) for _ in range(100000)]})
print(df.head(20))

             Col1
0   a;b;c;d;e;f;g
1   a;b;c;d;e;f;g
2   a;b;c;d;e;f;g
3   a;b;c;d;e;f;g
4   a;b;c;d;e;f;g
5   a;b;c;d;e;f;g
6   a;b;c;d;e;f;g
7   a;b;c;d;e;f;g
8   a;b;c;d;e;f;g
9   a;b;c;d;e;f;g
10  a;b;c;d;e;f;g
11  a;b;c;d;e;f;g
12  a;b;c;d;e;f;g
13  a;b;c;d;e;f;g
14  a;b;c;d;e;f;g
15  a;b;c;d;e;f;g
16  a;b;c;d;e;f;g
17  a;b;c;d;e;f;g
18  a;b;c;d;e;f;g
19  a;b;c;d;e;f;g
```
```
def explode_str(df, col, sep):
    s = df[col]
    i = np.arange(len(s)).repeat(s.str.count(sep) + 1)
    return df.iloc[i].assign(**{col: sep.join(s).split(sep)})

print(explode_str(df, 'Col1', ';').head(20))

  Col1
0    a
0    b
0    c
0    d
0    e
0    f
0    g
1    a
1    b
1    c
1    d
1    e
1    f
1    g
2    a
2    b
2    c
2    d
2    e
2    f
```
@ErfPy see the docs and notes

that example is supported idiomatically 
> For the empty list discussion, using Will's example from above, it gives 0 rows for an empty array (so no NULL):
> 
> ```
> test_db=# select a FROM unnest(array[]::integer[]) a;
>  a 
> ---
> (0 rows)
> ```
> 
> But, a NULL in the original data is also dropped when unnesting it, so both give here the same result as well, so not sure this is a good reference point.
> Does somebody know what spark does?

@ljin ?
> > For the empty list discussion, using Will's example from above, it gives 0 rows for an empty array (so no NULL):
> > ```
> > test_db=# select a FROM unnest(array[]::integer[]) a;
> >  a 
> > ---
> > (0 rows)
> > ```
> > 
> > 
> > But, a NULL in the original data is also dropped when unnesting it, so both give here the same result as well, so not sure this is a good reference point.
> > Does somebody know what spark does?
> 
> @ljin ?

```
>>> df.show()
+---+-----+------+
| id|array|array2|
+---+-----+------+
|  0|   []|  null|
|  1|   []|  null|
|  2|   []|  null|
|  3|   []|  null|
|  4|   []|  null|
|  5|   []|  null|
|  6|   []|  null|
|  7|   []|  null|
|  8|   []|  null|
|  9|   []|  null|
+---+-----+------+

>>> df.withColumn('exploded', F.explode(df['array'])).show()
+---+-----+------+--------+
| id|array|array2|exploded|
+---+-----+------+--------+
+---+-----+------+--------+

>>> df.withColumn('exploded', F.explode(df['array2'])).show()
+---+-----+------+--------+
| id|array|array2|exploded|
+---+-----+------+--------+
+---+-----+------+--------+
```

Both result in empty 
@icexelloss oh so the row is then excluded, ok since we propagate NaN I think that is equivalent.
@TomAugspurger ready to go here.
> Looks good on a quick skim. I don't have any opinion on whether an empty list or NaN is preferable. I suspect we'll add a keyword in the future, but `NaN` seems like a fine default.

yep
~~~
>>> import pandas
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/user/pandas/pandas/__init__.py", line 55, in <module>
    from pandas.core.api import (
  File "/home/user/pandas/pandas/core/api.py", line 5, in <module>
    from pandas.core.arrays.integer import (
  File "/home/user/pandas/pandas/core/arrays/__init__.py", line 1, in <module>
    from .base import (  # noqa: F401
  File "/home/user/pandas/pandas/core/arrays/base.py", line 14, in <module>
    from pandas.compat.numpy import function as nv
  File "/home/user/Repositories/pandas/pandas/compat/numpy/function.py", line 28, in <module>
    from pandas.util._validators import (
  File "/home/user/pandas/pandas/util/__init__.py", line 3, in <module>
    from pandas.core.util.hashing import hash_array, hash_pandas_object  # noqa
  File "/home/user/pandas/pandas/core/util/hashing.py", line 11, in <module>
    from pandas.core.dtypes.cast import infer_dtype_from_scalar
  File "/home/user/pandas/pandas/core/dtypes/cast.py", line 9, in <module>
    from pandas.util._validators import validate_bool_kwarg
  File "/home/user/pandas/pandas/util/_validators.py", line 7, in <module>
    from pandas.core.dtypes.common import is_bool
  File "/home/user/Repositories/pandas/pandas/core/dtypes/common.py", line 11, in <module>
    from pandas.core.dtypes.dtypes import (
  File "/home/user/pandas/pandas/core/dtypes/dtypes.py", line 17, in <module>
    from .inference import is_bool, is_list_like
  File "/home/user/pandas/pandas/core/dtypes/inference.py", line 26, in <module>
    is_list_like = lib.is_list_like
AttributeError: module 'pandas._libs.lib' has no attribute 'is_list_like'
~~~

This suddenly cropped up.  This commit obviously didn't cause it, but not sure what triggered this...

Can anyone else replicate this?