3

I have a dataframe that looks like this:

kenteken status code
0      XYZ      A  123
1      XYZ      B  456
2      ABC      C  789

And I want to convert it to a dictionary in a dictionary like this:

{'XYZ':{'code':'123', 'status':'A'}, {'code':'456', 'status':'B'}, 'ABC' : {'code':'789', 'status:'C'}}

The closest I've been able to come was the folling:

df.groupby('kenteken')['status', 'code'].apply(lambda x: x.to_dict()).to_dict()

Which yields:

{'ABC': {'status': {2: 'C'}, 'code': {2: '789'}},'XYZ': {'status': {0: 'A', 1: 'B'}, 'code': {0: '123', 1: '456'}}}

Which is close but not quite. I really don't know what to do anymore, so appreciate any help!

Brightscales
  • 43
  • 5
  • 1
    Does it have to be a dictionary? Take a look at `OrderedDict`. Maybe a list of tuples would be sufficient for your needs. – Tom Wojcik Jul 09 '18 at 11:58
  • I don't think it will be sufficient for the steps after it, but I'll give it a try** thanks! – Brightscales Jul 09 '18 at 12:02
  • df.to_dict(orient="records") gets you close. – lwileczek Jul 09 '18 at 12:07
  • 1
    Dictionaries can't have a key mapping with multiple values, which looks like what you want. However, you could have a key with a tuple of several values. Would that work? An example would be `{'XYZ': ({'code':'123', 'status':'A'}, {'code':'456', 'status':'B'})}` – Joel Jul 09 '18 at 12:07

1 Answers1

6

Does this work for you? 

a = dict(df.set_index('kenteken').groupby(level = 0).\
    apply(lambda x : x.to_dict(orient= 'records')))

print(a)

{'ABC': [{'status': 'C', 'code': 789}], 'XYZ': [{'status': 'A', 'code': 123}, {'status': 'B', 'code': 456}]}
user96564
  • 1,578
  • 5
  • 24
  • 42