Split pandas dataframe based on values in a column using groupby

Question

I want to split the following dataframe based on column ZZ

df = 
        N0_YLDF  ZZ        MAT
    0  6.286333   2  11.669069
    1  6.317000   6  11.669069
    2  6.324889   6  11.516454
    3  6.320667   5  11.516454
    4  6.325556   5  11.516454
    5  6.359000   6  11.516454
    6  6.359000   6  11.516454
    7  6.361111   7  11.516454
    8  6.360778   7  11.516454
    9  6.361111   6  11.516454

As output, I want a new DataFrame with the N0_YLDF column split into 4, one new column for each unique value of ZZ. How do I go about this? I can do groupby, but do not know what to do with the grouped object.

Corniculate

Answer 1

gb = df.groupby('ZZ')    
[gb.get_group(x) for x in gb.groups]

Ackler

Great answer! How do we extract the respective dataframes from gb? – Alicia

The method get_group(x) returns a new DataFrame object containing only the rows where column ZZ == x – Ackler

Answer 2

There is another alternative as the groupby returns a generator we can simply use a list-comprehension to retrieve the 2nd value (the frame).

dfs = [x for _, x in df.groupby('ZZ')]

Gastric

would this one liner work if I'm looking to make specific aggregations to every data frame? – Cannell

This one-liner simply stores the dataframes in an array. What you do next is up to you. Maybe have a look at ALollz answer to access keys. – Gastric

Answer 3

In R there is a dataframe method called split. This is for all the R users out there:

def split(df, group):
     gb = df.groupby(group)
     return [gb.get_group(x) for x in gb.groups]

Amnion

shouldn't you put it all into a series? ending with pd.Series(...) – Bicephalous

This is amazing. Is there an easy way to get the key which identifies of the group, so I can return a list of tuples, like [ (key, gb.get_group(x) ) for x in gb.group]? – Varices

I found this, which makes this easy: #42513549 – Varices

Just to provide an answer to the comment (which is explained in more detail in the link: [(key, gb.get_group(key)) for key in gb.groups] – Radburn

The same solution but with iterators def split(df, group): gb = df.groupby(group) for g in gb.groups: yield gb.get_group(g) – Hydrochloride

Answer 4

Store them in a dict, which allows you access to the group DataFrames based on the group keys.

d = dict(tuple(df.groupby('ZZ')))
d[6]

#    N0_YLDF  ZZ        MAT
#1  6.317000   6  11.669069
#2  6.324889   6  11.516454
#5  6.359000   6  11.516454
#6  6.359000   6  11.516454
#9  6.361111   6  11.516454

If you need only a subset of the DataFrame, in this case just the 'NO_YLDF' Series, you can modify the dict comprehension.

d = dict((idx, gp['N0_YLDF']) for idx, gp in df.groupby('ZZ'))
d[6]
#1    6.317000
#2    6.324889
#5    6.359000
#6    6.359000
#9    6.361111
#Name: N0_YLDF, dtype: float64

Monomerous

Answer 5

You can iterate over unique values and get groups using loc or query:

[df.loc[df['ZZ'] == i] for i in df['ZZ'].unique()]

or

[df.query('ZZ == @i') for i in df['ZZ'].unique()]

Uniat

Answer 6

Adding to user qwwqwwq answer:

gb = df.groupby('ZZ')
df_six = gb.get_group("6") #to create another dataframe with ZZ = 6
df_one = gb.get_group("7") #to create another dataframe with ZZ = 7

Beatify