SQL output to a dictionary of list

Question

I am trying to convert the following o/p of my SQL query (as a dataframe) to a dictionary of list:

Column A   Column B
M             N
M             O

I want the result in following format:

{M:[N,O]}

Can someone help me in this?

we do not know the format of the SQL result. You need to refine your question with more details — mad_, Aug 01 '22 at 16:21
@mad_ It is a dataframe as shown in the input. Have made the edits — loyala, Aug 01 '22 at 16:23
Do you use pandas for the SQL extraction? If so, please specify that this is a df — Epic_Yarin_God, Aug 01 '22 at 16:23

score 2 · Accepted Answer · answered Aug 01 '22 at 16:29

2

You could try like this:

a_dict = df.groupby('Column A', as_index=True)['Column B'].apply(list).to_dict()

print(a_dict)
{'M': ['N', 'O']}

answered Aug 01 '22 at 16:29

ouroboros1

9,113
3
7
26

This answer gets you going - SQL result / csv - all the same ? https://stackoverflow.com/questions/25055958/group-data-from-a-csv-file-by-field-value – irnerd Aug 01 '22 at 16:30
This solution is better because it doesn't require you to create lists. – Epic_Yarin_God Aug 01 '22 at 16:50

score 1 · Answer 2 · answered Aug 01 '22 at 16:48

This worked for me:

   tst = data.groupby("a", as_index =False).agg("|".join)
    tst['b'] = tst['b'].apply(lambda x: x.split("|"))
    a, b = tst['a'].tolist(), tst['b'].tolist()
    your_dict = dict(zip(a,b))

and vola

Please note that this is a round about way, and there is probably a better solution.

SQL output to a dictionary of list

2 Answers2