How can I convert a dataframe into a nested dictionary?

Question

I have a dataframe that looks like this:

    ORG                 SURVEY_DATE     NOS
Asset Management    2018-04-23          1.0
Asset Management    2018-05-08          1.0
Asset Management    2018-10-29          1.0
CIO                 2018-11-08          1.0
CIO                 2018-11-13          2.0

And I want to convert it to a dictionary that looks like this.

{
  "Asset Management": {
    "2019-03-30": 50,
    "2019-03-31": 40,
    "2019-04-01": 20,
    "2019-04-02": 30
  },
  "CIO": {
    "2019-03-30": 10,
    "2019-03-31": 20,
  }
}

Hi @lmiguelvargasf, Appreciate your help too! Thank you very much! — GeekyDad, Apr 16 '19 at 18:29

lmiguelvargasf · Answer 1 · 2019-04-16T18:20:23.427

2

Assumming your dataframe is in a variable called df:

>>> df.groupby('ORG').apply(lambda f: {key: value for key, value in zip(f.SURVEY_DATE, f.NOS)} ).to_dict()
{'Asset Management': {'2018-04-23': 1.0, '2018-05-08': 1.0, '2018-10-29': 1.0},
 'CIO': {'2018-11-08': 1.0, '2018-11-13': 2.0}}

edited Apr 16 '19 at 18:20

answered Apr 16 '19 at 17:26

lmiguelvargasf

63,191
45
217
228

But not exactly the expected output? – knh190 Apr 16 '19 at 17:29
No, I want to be the 'ORG' column to be my index. Please check my desired output. – GeekyDad Apr 16 '19 at 17:29
@PaulCaballero, sorry for the confusion. I updated my answer, but I am not sure how you are getting the values in which the keys are survey data elements. – lmiguelvargasf Apr 16 '19 at 17:39

knh190 · Accepted Answer · 2019-04-16T18:29:30.243

2

OK I updated my answer. Whola! Now it works.

In [9]: df
Out[9]:
                ORG SURVEY_DATE  NOS
0  Asset Management  2018-04-23  1.0
1  Asset Management  2018-05-08  1.0
2  Asset Management  2018-10-29  1.0
3               CIO  2018-11-08  1.0
4               CIO  2018-11-13  2.0

In [10]: df.groupby('ORG').apply(lambda x: dict(zip(x['SURVEY_DATE'],x['NOS']))).to_dict()
Out[10]:
{'Asset Management': {'2018-04-23': '1.0',
  '2018-05-08': '1.0',
  '2018-10-29': '1.0'},
 'CIO': {'2018-11-08': '1.0', '2018-11-13': '2.0'}}

Explanation: if you have 2 or more iteratives, you can loop through them simultaneously using zip:

x = [1,2,3]
y = [4,5,6]
for i,j in zip(x, y):
    print(i, j) # (1,4), (2,5), (3,6)

And I'm creating a dictionary from a tuple. Also lambda is just a shorthand for any one liner function definition:

foo = lambda x: x+1
# equivalent
def foo(x):
  return x+1

edited Apr 16 '19 at 18:29

answered Apr 16 '19 at 17:55

knh190

2,744
1
16
30

Hi knh190, almost actually I tried that kind of code that you provided. – GeekyDad Apr 16 '19 at 18:01
Hi @knh190, It worked! I just want to understand your code, what does the zip do here? Sorry for a newbie question I'm not really familiar with using lambda. Thank you again, really appreciate your help!! – GeekyDad Apr 16 '19 at 18:17
@PaulCaballero I updated with a short explanation. You can also find official doc too, of course. – knh190 Apr 16 '19 at 18:23

How can I convert a dataframe into a nested dictionary?

2 Answers2