Pandas Dataframe to the dict of list with tuples

Question

I have some data in the Dataframe (say, df):

Col_1   Col_2   Col_3
Key1    ACURA   CL
Key2    ACURA   EL
Key2    ACURA   ILX
Key3    ACURA   INTEGRA
Key4    ACURA   INTEGRA

The way I want to see it:

{'key1': [('ACURA', 'CL')], 
 'key2': [('ACURA', 'EL'), ('ACURA', 'ILX')], 
 'key3': [('ACURA', 'INTEGRA')], 
 'key4': [('ACURA', 'INTEGRA')]}

I tried something like df.set_index('Col_1').T.to_dict('list') but failed as Col_1 had non-unique values. There is, of course, a simple solution to run through the df row-by-row and create my dict manually, but I prefer more Pythonic way.

Answer 1

This does what you want:

In [50]:
df.groupby('Col_1')[['Col_2','Col_3']].apply(lambda x: [tuple(x) for x in x.values]).to_dict()

Out[50]:
{'Key1': [('ACURA', 'CL')],
 'Key2': [('ACURA', 'EL'), ('ACURA', 'ILX')],
 'Key3': [('ACURA', 'INTEGRA')],
 'Key4': [('ACURA', 'INTEGRA')]}

Here we groupby on 'Col_1', we then turn the values into a tuple inside a list and on the result call to_dict