Is there an "ungroup by" operation opposite to .groupby in pandas?

Question

Suppose we take a pandas dataframe...

    name  age  family
0   john    1       1
1  jason   36       1
2   jane   32       1
3   jack   26       2
4  james   30       2

Then do a groupby() ...

group_df = df.groupby('family')
group_df = group_df.aggregate({'name': name_join, 'age': pd.np.mean})

Then do some aggregate/summarize operation (in my example, my function name_join aggregates the names):

def name_join(list_names, concat='-'):
    return concat.join(list_names)

The grouped summarized output is thus:

        age             name
family                      
1        23  john-jason-jane
2        28       jack-james

Question:

Is there a quick, efficient way to get to the following from the aggregated table?

    name  age  family
0   john   23       1
1  jason   23       1
2   jane   23       1
3   jack   28       2
4  james   28       2

(Note: the age column values are just examples, I don't care for the information I am losing after averaging in this specific example)

The way I thought I could do it does not look too efficient:

create empty dataframe
from every line in group_df, separate the names
return a dataframe with as many rows as there are names in the starting row
append the output to the empty dataframe

possible duplicate of [pandas: How do I split text in a column into multiple columns?](http://stackoverflow.com/questions/17116814/pandas-how-do-i-split-text-in-a-column-into-multiple-columns) — Andy Hayden, Nov 21 '13 at 18:20
@AndyHayden: perhaps but that question's title sucks; this one is straightforward. (So if only the example use-case needs improving, best to improve it instead of closing this) — smci, Jul 18 '19 at 01:57
*"A table, stored in a pandas dataframe"* is circumlocution. Just learn to see a dataframe as a table (if that is what it represents). — smci, Jul 18 '19 at 02:02
The question is slightly unwieldy: instead of doing the aggregate/summarize operation then reversing it, just stop after the `groupby()`, do some averaging on `age` if necessary, then do `reset_index()` — smci, Jul 18 '19 at 02:07

score 50 · Accepted Answer · edited Jul 18 '19 at 01:43

50

The rough equivalent is .reset_index(), but it may not be helpful to think of it as the "opposite" of groupby().

You are splitting a string in to pieces, and maintaining each piece's association with 'family'. This old answer of mine does the job.

Just set 'family' as the index column first, refer to the link above, and then reset_index() at the end to get your desired result.

edited Jul 18 '19 at 01:43

smci

32,567
20
113
146

answered Nov 21 '13 at 13:58

Dan Allan

34,073
6
70
63

brilliant! I'm still looking at what the combination of apply, lambda, pd.Series and stack does, but it works exactly as intended. thanks! – mkln Nov 21 '13 at 14:08

score 18 · Answer 2 · answered Mar 30 '21 at 22:40

18

It turns out that pd.groupby() returns an object with the original data stored in obj. So ungrouping is just pulling out the original data.

group_df = df.groupby('family')
group_df.obj

Example

>>> dat_1 = df.groupby("category_2")
>>> dat_1
<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7fce78b3dd00>
>>> dat_1.obj
    order_date          category_2     value
1   2011-02-01  Cross Country Race  324400.0
2   2011-03-01  Cross Country Race  142000.0
3   2011-04-01  Cross Country Race  498580.0
4   2011-05-01  Cross Country Race  220310.0
5   2011-06-01  Cross Country Race  364420.0
..         ...                 ...       ...
535 2015-08-01          Triathalon   39200.0
536 2015-09-01          Triathalon   75600.0
537 2015-10-01          Triathalon   58600.0
538 2015-11-01          Triathalon   70050.0
539 2015-12-01          Triathalon   38600.0

[531 rows x 3 columns]

answered Mar 30 '21 at 22:40

Matt Dancho

6,840
3
35
26

2

This is a good hack, but I'm afraid it may not be future proof. I have in mind Hadley Wickham's [talk](https://www.youtube.com/watch?v=izFssYRsLZs&list=PL9HYL-VRX0oRjeraSIEaY0V_9gx52wdkV) about maintainable code. He warned against off-label usage of functions. The function maintainer might not be aware that end users use the function this way, so he/she might modify the function behavior, unaware that it might break down existing downstream code. What do you think? – hnagaty Sep 29 '21 at 10:06
@HanyNagaty Yes - It's of course a possibility. It would be smart of us to request an ungroup() method be added to pandas, which would simply return the grouped_df.obj. They would add unit tests to make sure a test fails if the ungroup() method doesn't work. – Matt Dancho Oct 06 '21 at 18:19
2

@HanyNagaty I've opened a GitHub Issue on Pandas here. Please support it if you'd like this feature. https://github.com/pandas-dev/pandas/issues/43902 – Matt Dancho Oct 06 '21 at 18:24
@MaddDancho Yes I like it and I made a comment there. – hnagaty Oct 07 '21 at 08:05

score 7 · Answer 3 · answered Dec 02 '19 at 19:48

Here's a complete example that recovers the original dataframe from the grouped object

def name_join(list_names, concat='-'):
    return concat.join(list_names)

print('create dataframe\n')
df = pandas.DataFrame({'name':['john', 'jason', 'jane', 'jack', 'james'], 'age':[1,36,32,26,30], 'family':[1,1,1,2,2]})
df.index.name='indexer'
print(df)
print('create group_by object')
group_obj_df = df.groupby('family')
print(group_obj_df)

print('\nrecover grouped df')
group_joined_df = group_obj_df.aggregate({'name': name_join, 'age': 'mean'})
group_joined_df


create dataframe

          name  age  family
indexer                    
0         john    1       1
1        jason   36       1
2         jane   32       1
3         jack   26       2
4        james   30       2
create group_by object
<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7fbfdd9dd048>

recover grouped df 
                   name  age
family                      
1       john-jason-jane   23
2            jack-james   28

print('\nRecover the original dataframe')
print(pandas.concat([group_obj_df.get_group(key) for key in group_obj_df.groups]))

Recover the original dataframe
          name  age  family
indexer                    
0         john    1       1
1        jason   36       1
2         jane   32       1
3         jack   26       2
4        james   30       2

score 1 · Answer 4 · answered Sep 12 '19 at 06:13

1

There are a few ways to undo DataFrame.groupby, one way is to do DataFrame.groupby.filter(lambda x:True), this gets back to the original DataFrame.

answered Sep 12 '19 at 06:13

xuancong84

1,412
16
17

AttributeError: 'function' object has no attribute 'filter' – user1953366 Jul 31 '20 at 10:06
1

In the context of the question it would be group_df.filter(lambda x:True). Worked for me. Has the overhead of copying to a new Dataframe. – user2219808 Sep 09 '20 at 15:02

Is there an "ungroup by" operation opposite to .groupby in pandas?

Question:

4 Answers4

Example

Linked