Grouping column values into a dictionary

Question

Assume I have a dataframe of the following structure:

   Name   Desc   Group ConditionType ConditionName
0  job1  desc1  group1            in         cond1
1  job1  desc1  group1            in         cond2
2  job1  desc1  group1           out         cond1
3  job2  desc2  group1            in         cond1
4  job2  desc2  group1            in         cond2
5  job2  desc2  group1            in         cond3

For the context, it's a full join of two tables; one with Name, Desc and Group columns and the other with ConditionType and ConditionName columns. The tables are joined on the column Name.

I would like to organize this DataFrame, such that I have the following structure:

   Name   Desc   Group                                      Conditions
0  job1  desc1  group1    {'in': ['cond1', 'cond2'], 'out': ['cond1']}
1  job2  desc2  group1  {'in': ['cond1', 'cond2', 'cond3'], 'out': []}

I am looking for a faster way to do this (possibly using groupby() and apply()) just not sure where to start. For-loop is really slow since the original table is over 100000 records.

Here's the code to instantiate both the initial table and the result that I seek:

table = [['job1', 'desc1', 'group1', 'in', 'cond1'],
         ['job1', 'desc1', 'group1', 'in', 'cond2'],
         ['job1', 'desc1', 'group1', 'out', 'cond1'],
         ['job2', 'desc2', 'group1', 'in', 'cond1'],
         ['job2', 'desc2', 'group1', 'in', 'cond2'],
         ['job2', 'desc2', 'group1', 'in', 'cond3']]

result = [['job1', 'desc1', 'group1', {'in': ['cond1', 'cond2'], 'out': ['cond1']}],
          ['job2', 'desc2', 'group1', {'in': ['cond1', 'cond2', 'cond3'], 'out': []}]]

table_df = pd.DataFrame(table, columns=['Name', 'Desc', 'Group', 'ConditionType', 'ConditionName'])
result_df = pd.DataFrame(result, columns=['Name', 'Desc', 'Group', 'Conditions'])

Would this work: https://stackoverflow.com/a/29876208/702383 ? — RCA, Apr 19 '18 at 17:36

jpp · Accepted Answer · 2018-04-19T17:43:33.380

There will be a pandas way, but this is the intuitive way via row iteration and collections.defaultdict.

Note there are couple of minor adjustments you may wish to make (resetting index, adding empty dictionary items).

A groupby method will not necessarily be faster. You should test with your data. For performance, use df.itertuples instead of df.iterrows.

from collections import defaultdict

d = defaultdict(lambda: defaultdict(list))

for idx, row in df.iterrows():
    d[(row.Name, row.Desc, row.Group)][row.ConditionType].append(row.ConditionName)

df['Conditions'] = df.set_index(['Name', 'Desc', 'Group']).index.map(d.get)

df = df.loc[:, ['Name', 'Desc', 'Group', 'Conditions']]\
       .drop_duplicates(subset=['Name', 'Desc', 'Group'])

print(df)

#    Name   Desc   Group                                    Conditions
# 0  job1  desc1  group1  {'in': ['cond1', 'cond2'], 'out': ['cond1']}
# 3  job2  desc2  group1           {'in': ['cond1', 'cond2', 'cond3']}

Thanks, this worked for me; and takes reasonable amount of time for the use case. — Max Mikhaylov, Apr 19 '18 at 18:43

Grouping column values into a dictionary

1 Answers1