Convert DataFrame to Dictionary Containing List

Question

Stack Team! I have a data frame that I'd like to convert into a dictionary with one of the columns as the key and the values of another column appended to a list within the dictionary.

I've been able to convert the data frame into a dictionary with key value pairs for each row of the data frame, but I simply need a series or list appended to a series belonging to each dictionary key.

DataFrame:

   Team Name      ID
0  Idaho      1234
1  Idaho      5678
2  Tokyo      5432

Tried this and I get a dictionary containing dictionaries. The ID are set to keys with the row records from the data frame as values.

Code Attempt:

team_id_df.set_index('Team Name').to_dict(orient='index')

Result:

{'Idaho': {'ID': 1234, 'ID': 5678}, 'Tokyo': { 'ID': 5432}}

But I'm trying to get this:

{'Idaho': [1234, 5678],'Tokyo': [5432]}

I'm a little stumped, any suggestions for making this transformation?

Possible duplicate of [this discussion](https://stackoverflow.com/questions/26716616/convert-a-pandas-dataframe-to-a-dictionary) — Alexandre B., Jul 28 '19 at 19:36
Trying to find a post that offers various ways of doing this, but you're pretty much after: `df.groupby('Team Name').ID.agg(list).to_dict()`... — Jon Clements, Jul 28 '19 at 19:40
Thanks @JonClements, this is helpful. I did see the referenced duplicate post but I got caught up under the assumption that the agg function would transform as intended but sum my values. So if I'm understanding this correctly, the group by method transforms the orientation of the data set, and to_dict passes the grouping column as the key. The combination of agg and (list) convert the ID column into a list? Or am I reading this backwards? (Apologies, this is my first time really working with Pandas) — FamLamb, Jul 28 '19 at 19:52

score 2 · Answer 1 · edited Aug 05 '19 at 09:27

2

I would have done it in two steps. First I would have used a group by and then using a for loop, I will get what I want, iterating over the results.

b = a.groupby(by=['Team Name','ID']).agg('count')
final_dict = {}
for team_name, ID in b.index:
    final_dict.setdefault(team_name,[]).append(ID)
print(final_dict)

This gave me the following output.

edited Aug 05 '19 at 09:27

Yevhen Kuzmovych

10,940
7
28
48

answered Jul 28 '19 at 19:56

Amit

2,018
1
8
12

Please, `final_dict` and `team_name`. – Yevhen Kuzmovych Jul 28 '19 at 20:05
@ Yevhen Kuzmovych ?? – Amit Jul 28 '19 at 20:12
That was a great answer... you can skip the groupby and use df.values Final_Dict = {} for TeamName, ID in team_id_df.values: Final_Dict.setdefault(TeamName,[]).append(ID) print(Final_Dict) – Gustavo Gradvohl Jul 29 '19 at 00:48
@Gustavo Gradvohl Thank you very much. – Amit Jul 29 '19 at 03:30
@Amit Good answer. But you should use snake_case in python. Pep8-ing you a little bit :) – Yevhen Kuzmovych Jul 29 '19 at 10:06
@YevhenKuzmovych Thank you for your suggestion. Something definitely to keep in mind. – Amit Jul 29 '19 at 17:14

Convert DataFrame to Dictionary Containing List

1 Answers1