Data Grouping with Pandas-CodePudding

I have a data frame

Name    Subset    Type    System
A00     IU00-A    OP      A
A00     IT00      PP      A
B01     IT-01A    PP      B
B01     IU        OP      B
B03     IM-09-B   LP      A
B03     IM03A     OP      A
B03     IT-09     OP      A
D09     IT        OP      A
D09     IM        LP      B
D09     IM        OP      A

The abouve dataframe, I need to convert it based on Grouping Name and Subset strings extracted using extractall(r'[^a-zA-Z]*([a-zA-Z] )[^,]*').groupby(level=0).agg(', '.join). And Systems, Subsets Should be mentioned serially according to Types.

Output Example:

Subset Cluster    Type Cluster    Name          System        Subsets
IU,IT             OP,PP           A00,B01       A,A,B,B       IU00-A,IT00
IM,IM,IT          LP, OP, OP      B03, D09      A,A,A,A,B,A   IM-09-B,IM03A,IT-09,IT,IM,IM

CodePudding user response：

Double groupby where we first group by "Name", then again by "Subset Cluster" and "Type Cluster" does the trick:

out = df.assign(**{'Subset Cluster': df['Subset'].str.extractall(r'[^a-zA-Z]*([a-zA-Z] )[^,]*')\
                                                 .groupby(level=0)[0].agg(', '.join)})\
        .sort_values(by=df.columns.tolist())\
        .groupby('Name', as_index=False).agg(', '.join).rename(columns={'Type':'Type Cluster'})\
        .groupby(['Subset Cluster', 'Type Cluster'], as_index=False).agg(', '.join)

Output:

  Subset Cluster  Type Cluster      Name                             Subset            System
0     IM, IM, IT    LP, OP, OP  B03, D09  IM-09-B, IM03A, IT-09, IM, IM, IT  A, A, A, B, A, A  
1         IT, IU        PP, OP  A00, B01           IT00, IU00-A, IT-01A, IU        A, A, B, B

CodePudding user response：

Beginning from your dataframe, in order to achieve your result I would use two aggregating operations since you need to do two groupings, relatively to Name and Subset Cluster. Here's the way I would do it:

df.rename(columns={'Subset': 'Subset Cluster'}, inplace=True)
df['Subsets'] = df['Subset Cluster'].apply(lambda s: s[:2])

df = df.groupby('Name').agg(lambda col: ', '.join(sorted(list(col))) ).reset_index()
df = df.groupby('Subsets').agg(lambda col: ', '.join(sorted(list(col))) ).reset_index()

df