I have a dataframe like
Sr.No ID A B C D1 Tom Earth English BMW2 Tom Mars Spanish BMW Green 3 Michael Mercury Hindi Audi Yellow4 John Venus Portugese Mercedes Blue5 John German Audi Red
I am trying to convert this to a dictionary by ID like :
{'ID' : 'Tom', 'A' : ['Earth', 'Mars'], 'B' : ['English', 'Spanish'], 'C' : ['BMW', 'BMW'], 'D':['Green'] }, {'ID' : 'Michael', 'A' : ['Mercury'], 'B' : ['Hindi'], 'C' : ['Audi'],'D':['Yellow']},{'ID' : 'John', 'A' : ['Venus'], 'B' : ['Portugese', 'German'], 'C' : ['Mercedes', 'Audi'], 'D':['Blue', 'Red'] }
This is somewhat similar to what I want.
I also tried ,
df.set_index('ID').to_dict()
but this gives me dictionary of length 5 instead of 3. Any help would be appreciated.
Grouping by 'ID'
and apply to_dict
to each group with orient='list'
comes pretty close:
df.groupby('ID').apply(lambda dfg: dfg.to_dict(orient='list')).to_dict()
Out[25]:
{'John': {'A': ['Venus', nan],'B': ['Portugese', 'German'],'C': ['Mercedes', 'Audi'],'D': ['Blue', 'Red'],'ID': ['John', 'John'],'Sr.No': [4, 5]},'Michael': {'A': ['Mercury'],'B': ['Hindi'],'C': ['Audi'],'D': ['Yellow'],'ID': ['Michael'],'Sr.No': [3]},'Tom': {'A': ['Earth', 'Mars'],'B': ['English', 'Spanish'],'C': ['BMW', 'BMW'],'D': [nan, 'Green'],'ID': ['Tom', 'Tom'],'Sr.No': [1, 2]}}
It should just be a matter of formatting the result slightly.
Edit: to remove 'ID'
from the dictionaries:
df.groupby('ID').apply(lambda dfg: dfg.drop('ID', axis=1).to_dict(orient='list')).to_dict()
Out[5]:
{'John': {'A': ['Venus', nan],'B': ['Portugese', 'German'],'C': ['Mercedes', 'Audi'],'D': ['Blue', 'Red'],'Sr.No': [4, 5]},'Michael': {'A': ['Mercury'],'B': ['Hindi'],'C': ['Audi'],'D': ['Yellow'],'Sr.No': [3]},'Tom': {'A': ['Earth', 'Mars'],'B': ['English', 'Spanish'],'C': ['BMW', 'BMW'],'D': [nan, 'Green'],'Sr.No': [1, 2]}}