GroupBy and Aggregate Using Pandas

Remove duplicates in one column, and create a list of tags in another column using Pandas.

Consider this. I have a table of data. With me, yes, I know, big surprise. This table of data has a column with values, and some of these values repeat. This is the names column. Another column has categories the names belong to. Some of the names belong to more than one category, but the category currently does not have more than one value.

I want to change this so none of the names repeat, and the categories are in a column with a list, similar to how tags are stored. Thankfully, Pandas has a really handy way to do this - one I forget most of the time and have to look up. Hence, I am documenting it here so you and I both can find it easily. The magic sauce is this little snippet.

groupby_column = 'name'
aggregate_column = 'data_collection'
agg_df = df.groupby(groupby_column).aggregate({aggregate_column: list})

This outputs a very simple dataframe (agg_df) with the names as unique values and the categories now as a list.

To get the final result, we need to add the aliases and description columns back on. After all, who knows what X4072_I is, anyway? We accomplish this by first creating a dataframe to add back on without the aggregation column (`drop`), and with the grouping column as the index. Next, after joining this new dataframe on, we pull the grouping column out of the index, remove any duplicate names, and clean up the index so it is sequential.

df_alias = df.drop(columns=aggregate_column).set_index(groupby_column)

agg_df.join(df_alias).reset_index(groupby_column).drop_duplicates(groupby_column).reset_index(drop=True)

The output, initially 21 rows, is now only 10.

If we want to consolidate this into a succicent function, one easily copied and pasted, here is what it looks like.

import pandas as pd

def aggregate_column(df: pd.DataFrame, groupby_column: str = 'name', 
                     aggregate_column: str = 'data_collection') -> pd.DataFrame:
    
    # make sure the columns are in the dataframe
    assert groupby_column in df.columns, f'"groupby_column", {groupby_column}, '
                                         'does not appear to be in the input '
                                         'DataFrame columns.'
    assert aggregate_column in df.columns, f'"aggregate_column", {aggregate_column}, '
                                           'does not appear to be in the input '
                                           'DataFrame columns.'
    
    # create aggregated dataframe
    agg_df = df.groupby(groupby_column).aggregate({aggregate_column: list})
    
    # create a dataframe of values without the aggregation column ready to join
    df_alias = 	df.drop(columns=aggregate_column).\
        set_index(groupby_column)
    
    # join the aliases on and clean up the result
    out_df = agg_df.join(df_alias).\
        reset_index(groupby_column).\
        drop_duplicates(groupby_column).\
        reset_index(drop=True)
    
    return out_df

Yes, this post likely is somewhat harder to read than most, but hopefully all of the details make it easier to get the point of you really are interesed in how to accomplish this, and with the entire thing cosolodated into a succicent function at the end, easy to copy and paste if you do not want to worry about the details.

Have fun and happy Monday!