11
$\begingroup$

I have a pandas dataframe df that looks like this

name    value1     value2
A       123         1
B       345         5
C       712         4
B       768         2
A       318         9
C       178         6
A       321         3

I want to convert this into a dictionary with name as a key and list of dictionaries (value1 key and value2 value) for all values that are in name

So, the output would look like this

{
 'A': [{'123':1}, {'318':9}, {'321':3}],
 'B': [{'345':5}, {'768':2}],
 'C': [{'712':4}, {'178':6}]
}

So, far I have managed to get a dictionary with name as key and list of only one of the values as a list by doing

df.set_index('name').transpose().to_dict(orient='list')

How do I get my desired output? Is there a way to aggregate all the values for the same name column and get them in the form I want?

$\endgroup$
2
  • $\begingroup$ Any chance you could copy and paste the code which you used to create df? $\endgroup$ May 29 '18 at 16:20
  • $\begingroup$ @Lupacante The dict is created from a file. so this is as it is read from it. The data needs to be in a format like that for some further processing which is sadly on in my control. $\endgroup$
    – sfactor
    May 29 '18 at 16:24
10
$\begingroup$

Does this do what you want it to?

from pandas import DataFrame

df = DataFrame([['A', 123, 1], ['B', 345, 5], ['C', 712, 4], ['B', 768, 2], ['A', 318, 9], ['C', 178, 6], ['A', 321, 3]], columns=['name', 'value1', 'value2'])

d = {}
for i in df['name'].unique():
    d[i] = [{df['value1'][j]: df['value2'][j]} for j in df[df['name']==i].index]

This returns

  Out[89]: 
{'A': [{123: 1}, {318: 9}, {321: 3}],
 'B': [{345: 5}, {768: 2}],
 'C': [{712: 4}, {178: 6}]}
$\endgroup$
1
  • 1
    $\begingroup$ Great this is exactly what I needed. Thanks a lot. Accepting. $\endgroup$
    – sfactor
    May 29 '18 at 16:35
4
$\begingroup$

The to_dict() method sets the column names as dictionary keys so you'll need to reshape your DataFrame slightly. Setting the 'ID' column as the index and then transposing the DataFrame is one way to achieve this.

The same can be done with the following line:

>>> df.set_index('ID').T.to_dict('list')
{'p': [1, 3, 2], 'q': [4, 3, 2], 'r': [4, 0, 9]}

Better to use the groupby,

df.groupby('name')[['value1','value2']].apply(lambda g: g.values.tolist()).to_dict()
$\endgroup$
3
  • $\begingroup$ I've already done this if you read the last part of the question. My requirement is a little different. $\endgroup$
    – sfactor
    May 29 '18 at 16:25
  • $\begingroup$ I am working on that $\endgroup$
    – Aditya
    May 29 '18 at 16:26
  • $\begingroup$ I guess passing the Orient = index and a little bit of modification will get you what you want $\endgroup$
    – Aditya
    May 29 '18 at 16:28
3
$\begingroup$
df.groupby('name')[['value1','value2']].apply(lambda g: g.values.tolist()).to_dict()

if you need a list of tuples explicitly:

df.groupby('name')[['value1','value2']].apply(lambda g: list(map(tuple, g.values.tolist()))).to_dict()
$\endgroup$
1
  • $\begingroup$ This is faster than the accepted solution for large datasets. $\endgroup$
    – cyram
    Apr 2 '20 at 9:36
0
$\begingroup$

Building on @nemo's answer (above) which will be faster than the accepted solution, this will give the same output that you want:

def formatRecords(g):
    keys = ['value1', 'value2']
    result = []
    for item in g.values.tolist():
        item = dict(zip(keys, item))
        result.append(item)
    return result

df_dict = df.groupby('name').apply(lambda g: formatRecords(g)).to_dict()
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.