ENH: `df.implode` as a opposite function to `df.explode`

[jlynchMicron]

Is your feature request related to a problem?

This seems to be kind of a niche issue, but I found myself needing to do this operation to format data in a way that an SQL table ARRAY structure would accept it using the pandas.to_sql function (in conjunction with sqlalchemy).

Describe the solution you'd like

The set of operations needed is described in this stackoverflow post: https://stackoverflow.com/questions/64235312/how-to-implodereverse-of-pandas-explode-based-on-a-column

Example:

df:
  NETWORK       config_id       APPLICABLE_DAYS
0   Grocery     5399            SUN
1   Grocery     5399            MON
2   Grocery     5399            TUE
3   Grocery     5399            WED

df.implode('APPLICABLE_DAYS')
   NETWORK  config_id       APPLICABLE_DAYS 
0  Grocery       5399       SUN,MON,TUE,WED

The set of operations actual used were the following:

df = (df.groupby(['NETWORK','config_id'])
      .agg({'APPLICABLE_DAYS': lambda x: ",".join(x)})
      .reset_index())

[rhshadrach]

What would an implode method look like? What would happen to columns not involved if they aren't constant in each group?

It seems to me that groupby is already the opposite of explode.

[jlynchMicron]

Hi @rhshadrach , I am not sure what you are looking for when asking "what would an implode method look like". I think the first code box I posted shows what the method call would look like: df.implode('APPLICABLE_DAYS'). Could you please clarify what else you are looking for?

"What would happen to columns not involved if they aren't constant in each group?":
That is a great question. I am not sure I have totally thought out all the edge cases relating to that, but im sure some pitfalls might exist. One question I would have is what the explode function does if a list/dict like column is not included as a selected column when calling explode? My guess would be keep the list/dict like structure in every column element "as is", instead of breaking out the list/dict like structure into separate column elements. In the case of the implode function, the function might have to return an empty dataframe or have some null column elements.

"It seems to me that groupby is already the opposite of explode.":
I would say groupby is similar to the opposite of explode, but not fully. explode leaves you with a dataframe at the end, not a groupby object. A implementation of implode would leave you with a single dataframe at the end after the aggregation and reset_index were applied. I assume the index of a dataframe resulting from an explode operation is also manipulated as well since you result with more rows than you had previously.

All in all, I think this might be a niche issue that CAN be solved by using groupby -> agg -> reset_index, but it took me a decent amount of time googling to figure out how one would perform the opposite operation of explode.

[rhshadrach]

One question I would have is what the explode function does if a list/dict like column is not included as a selected column when calling explode? My guess would be keep the list/dict like structure in every column element "as is", instead of breaking out the list/dict like structure into separate column elements.

I think you're asking about:

print(pd.DataFrame({'a': [1, 2, 3], 'b': [4, 5, 6]}).explode('a'))

This is a no-op, the result produced is pd.DataFrame({'a': [1, 2, 3], 'b': [4, 5, 6]}).

I am not sure I have totally thought out all the edge cases relating to that, but im sure some pitfalls might exist.

I'm not sure I understand even the common case, so I think it's okay to start there. For example, what is the expected output of

print(pd.DataFrame({'a': [1, 1, 2, 2], 'b': [4, 5, 6, 7]}).implode('a'))

explode leaves you with a dataframe at the end, not a groupby object

Yes, you have to do the corresponding op, as below. But I don't think this handles the case where the values in columns b and c are not uniquely determined by column a.

def implode(df, column):
    keys = [c for c in df if c != column]
    return df.groupby(keys, as_index=False).agg({column: list})[df.columns]

df = pd.DataFrame({'a': [[1, 2], [3, 4]], 'b': [5, 6], 'c': [7, 8]})
print(df)
print(df.explode('a'))
print(implode(df.explode('a'), 'a'))

produces

        a  b  c
0  [1, 2]  5  7
1  [3, 4]  6  8
   a  b  c
0  1  5  7
0  2  5  7
1  3  6  8
1  4  6  8
        a  b  c
0  [1, 2]  5  7
1  [3, 4]  6  8

[rhshadrach]

This doesn't appear actionable to me as-is, but if you would like to continue, simply comment here and can reopen!

[truongjenny]

Woudn't a potential implode function be faster than a groupby as no shuffling and sorting of keys is necessary? I assume series.explode(ignore_index=False) flattens a list consecutively (like concatenation) so similar as np.split() knowing the number of elements per group would be enough to reverse the explode?

[RonUlitsky]

We have a case where we need to create large lists of nested dictionaries from multiple frames.

i.e. Two tables with a one-to-many relation:

parent_df: DataFrame
   name     gender    age    
0  Bob      M         27
1  Bill     M         31   
2  Jill     F         25     
3  Jane     F         28   


child_df: DataFrame
   name     gender    age    parent
0  Jim      M         7      Bob
1  Joe      M         8      Bob
2  Beth     F         10     Bill
3  Brie     F         11     Jane


result: list[dict]
[
  {
    "name": "Bob",
    "gender": "M",
    "age": 27,
    "children": [
      {"name": "Jim", "gender": "M", "age": 7, "parent": "Bob"},
      {"name": "Joe", "gender": "M", "age": 8, "parent": "Bob"}
    ]
  }
  ...
]

This is our current solution:

# Sort by the foreign key
parent_df = parent_df.sort_values("name").reset_index(drop=True)
child_df = child_df.sort_values("parent").reset_index(drop=True)

# Create nested dicts
child_df['dict'] = Series(data=child_df.to_dict(orient='records'))

# Reverse explosion by foreign key -- most relevant piece to this issue
parent_df['children'] = child_df.groupby('parent').agg(lambda x: list(x))['dict']

# Create parent dict.
return parent_df.to_dict(orient='records')

Perhaps this is not the best way to obtain the result, but if it is, the use of DataFrameGroupBy.agg(lambda x: list(x)) is obviously undesirable across large datasets.

Ideally, maybe a pandas.Series method for "imploding" on the index:

# Reverse explosion by foreign key
# Example of pandas.Series.implode

parent_to_children: Series = child_df.set_index('parent')['dict']

parent_to_children 
Bob   {...}
Bob   {...}
Bill  {...}
Jane  {...} 


# Call implode method: parent_df['children'] = parent_to_children.implode()

# Result:
parent_df['children']
Bob   [{...}, {...}]
Bill  [{...}]
Jane  [{...}]

Any thoughts @rhshadrach ?

[rhshadrach]

parent_to_children.implode()

How will this differ from parent_to_children.groupby("parent").agg(list)?

Woudn't a potential implode function be faster than a groupby as no shuffling and sorting of keys is necessary?

In general groupby does not sort the entire dataset, and sorting of the result can be disabled; I'm not sure what is meant by "shuffle" here.

[RonUlitsky]

Thanks for taking the time @rhshadrach .

My assumption was that SeriesGroupBy.agg or SeriesGroupBy.apply are relatively slow methods and are saved for cases where the manipulations rely on the content of the data being manipulated. I was under the impression that an "implode" method is a general enough case to have its own method which could implement a more efficient algorithm.

If I am wrong in this assumption, then I apologize, and thank you once again for taking a look at this.