Convert Pandas Dataframe to_dict() with unique column values as keys

Question

How can I convert a pandas dataframe to a dict using unique column values as the keys for the dictionary? In this case I want to use unique username's as the key.

Here is my progress so far based on information found on here and online.

My test dataframe:

import pandas
import pprint

df = pandas.DataFrame({
    'username': ['Kevin', 'John', 'Kevin', 'John', 'Leslie', 'John'], 
    'sport': ['Soccer', 'Football', 'Racing', 'Tennis', 'Baseball', 'Bowling'],
    'age': ['51','32','20','19','34','27'],
    'team': ['Cowboyws', 'Packers', 'Sonics', 'Raiders', 'Wolves', 'Lakers']
})

I can create a dictionary by doing this:

dct = df.to_dict(orient='records')
pprint.pprint(dct, indent=4)

>>>>[{'age': '51', 'sport': 'Soccer', 'team': 'Cowboyws', 'username': 'Kevin'},
    {'age': '32', 'sport': 'Football', 'team': 'Packers', 'username': 'John'},
    {'age': '20', 'sport': 'Racing', 'team': 'Sonics', 'username': 'Kevin'},
    {'age': '19', 'sport': 'Tennis', 'team': 'Raiders', 'username': 'John'},
    {'age': '34', 'sport': 'Baseball', 'team': 'Wolves', 'username': 'Leslie'},
    {'age': '27', 'sport': 'Bowling', 'team': 'Lakers', 'username': 'John'}]

I tried using the groupby and apply method which got me closer but it converts all the values to lists. I want them to remain as dictionaries so i can retain the each value's key:

result = df.groupby('username').apply(lambda x: x.values.tolist()).to_dict()
pprint.pprint(result, indent=4)

{   'John': [   ['32', 'Football', 'Packers', 'John'],
                ['19', 'Tennis', 'Raiders', 'John'],
                ['27', 'Bowling', 'Lakers', 'John']],
    'Kevin': [   ['51', 'Soccer', 'Cowboyws', 'Kevin'],
                 ['20', 'Racing', 'Sonics', 'Kevin']],
    'Leslie': [['34', 'Baseball', 'Wolves', 'Leslie']]}

This is the desired result I want:

{   
    'John': [{'age': '32', 'sport': 'Football', 'team': 'Packers', 'username': 'John'},
             {'age': '19', 'sport': 'Tennis', 'team': 'Raiders', 'username': 'John'},
             {'age': '27', 'sport': 'Bowling', 'team': 'Lakers', 'username': 'John'}],
    'Kevin': [{'age': '51', 'sport': 'Soccer', 'team': 'Cowboyws', 'username': 'Kevin'},
              {'age': '20', 'sport': 'Racing', 'team': 'Sonics', 'username': 'Kevin'}],
    'Leslie': [{'age': '34', 'sport': 'Baseball', 'team': 'Wolves', 'username': 'Leslie'}]
}

score 4 · Accepted Answer · answered Jan 10 '19 at 16:12

4

Use groupby and apply. Inside the apply, call to_dict with the "records" orient (similar to what you've figured out already).

df.groupby('username').apply(lambda x: x.to_dict(orient='r')).to_dict()

answered Jan 10 '19 at 16:12

cs95

379,657
97
704
746

i felt like i was pretty close. thanks for helping me out – JokerMartini Jan 10 '19 at 16:18

score 2 · Answer 2 · answered Jan 10 '19 at 16:15

I prefer using for loop here , also you may want to drop the username columns , since it is redundant

d = {x: y.drop('username',1).to_dict('r') for x , y in df.groupby('username')}
d
Out[212]: 
{'John': [{'age': '32', 'sport': 'Football', 'team': 'Packers'},
  {'age': '19', 'sport': 'Tennis', 'team': 'Raiders'},
  {'age': '27', 'sport': 'Bowling', 'team': 'Lakers'}],
 'Kevin': [{'age': '51', 'sport': 'Soccer', 'team': 'Cowboyws'},
  {'age': '20', 'sport': 'Racing', 'team': 'Sonics'}],
 'Leslie': [{'age': '34', 'sport': 'Baseball', 'team': 'Wolves'}]}

great answer for both of you guys. that is probably a good idea to drop it — JokerMartini, Jan 10 '19 at 16:18

Convert Pandas Dataframe to_dict() with unique column values as keys

2 Answers2

Linked