6
$\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$
  • $\begingroup$ Any chance you could copy and paste the code which you used to create df? $\endgroup$ – marco_gorelli 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
8
$\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
    $\begingroup$ Great this is exactly what I needed. Thanks a lot. Accepting. $\endgroup$ – sfactor May 29 '18 at 16:35
3
$\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]}
$\endgroup$
  • $\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
1
$\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$

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.