Python: Converting a nested Dictionary object into pandas dataframe

Question

I am trying to read a dictionary in the below format:

d = \
  {
  'indent_insert': {1000037121: [('0 - Success',)], 1000037122: [('0 - Success',)]},
  'indent_detail_insert': {1000037121: [('0 - Success',)], 1000037122: [('22051 - duplicate key violation error',)]}
  }

This is not JSON per say as it has no " around ['0 - Success',)]. The above is being generated from the below code snippets:

key = ['indent_insert','indent_detail_insert']
result = []
indent_lst = ['1000037121','1000037122']
c_indlts_l = [('0-Success'),('22051 - duplicate key violation error')]
c_ind_l = [('0-Success'),('0-Success')]
indtls_results = dict(zip(indent_lst,c_indlts_l))
ind_result = dict(zip(indent_lst,c_ind_l))
result.append(ind_result)
result.append(indtls_results)
d = dict(zip(key,result))

Now I want a dataframe like below:

IndentNo     indent_insert     indent_details_insert
1000037121   ('0-Success')        ('0 - Success')
1000037122   ('0-Success')        ('22051 - duplicate key violation error')

In order to achieve the same I am trying the following:

with open(os.path.join(dir_path,f_ind),'r') as f_r:
  d = f_r.read()
df = pd.DataFrame.from_dict(d)

I am getting

ValueError: DataFrame constructor not properly called!

What I am missing out here.

your snippet does not reproduce exactly the same structure as the given dictionary (and there is a typo) which one is the right structure? — Ben.T
– Ben.T, Commented Dec 6, 2021 at 12:49
@Corralien: It is clearly mentioned in question. My input is d and my output is the dataframe. — pythondumb
– pythondumb, Commented Dec 6, 2021 at 13:09
@Corralien: Ah!! gotcha!!. I have edited the question. Thanks for pointed that out. — pythondumb
– pythondumb, Commented Dec 6, 2021 at 13:27

Corralien · Accepted Answer · 2021-12-06 21:20:20Z

3

If your file content a dict-like string, use literal_eval from ast module:

import ast

with open('log_indent.log') as f_r:
    data = ast.literal_eval(f_r.read())
    df = pd.DataFrame({k: v for k, v in data.items() if isinstance(v, dict)})
    df = df.applymap(lambda x: x[0])

Output:

>>> df
             indent_insert indent_detail_insert
1000037121  (0 - Success,)       (0 - Success,)
1000037122  (0 - Success,)       (0 - Success,)
1000037123  (0 - Success,)       (0 - Success,)
1000037124  (0 - Success,)       (0 - Success,)
1000034565  (0 - Success,)       (0 - Success,)
1000034566  (0 - Success,)       (0 - Success,)
1000039683  (0 - Success,)       (0 - Success,)
1000039684  (0 - Success,)       (0 - Success,)
1400028290  (0 - Success,)       (0 - Success,)
1000038541  (0 - Success,)       (0 - Success,)
1000037155  (0 - Success,)       (0 - Success,)
1000037156  (0 - Success,)       (0 - Success,)
1000037157  (0 - Success,)       (0 - Success,)
1000037158  (0 - Success,)       (0 - Success,)
1000033191  (0 - Success,)       (0 - Success,)
1000038190  (0 - Success,)       (0 - Success,)
1000038191  (0 - Success,)       (0 - Success,)
1000038192  (0 - Success,)       (0 - Success,)
1000037937  (0 - Success,)       (0 - Success,)
1000038193  (0 - Success,)       (0 - Success,)
1400028211  (0 - Success,)       (0 - Success,)
1000037062  (0 - Success,)       (0 - Success,)
1000038864  (0 - Success,)       (0 - Success,)
1000037470  (0 - Success,)       (0 - Success,)
1000037471  (0 - Success,)       (0 - Success,)
1000037472  (0 - Success,)       (0 - Success,)
1000037473  (0 - Success,)       (0 - Success,)
1000037474  (0 - Success,)       (0 - Success,)
1000037475  (0 - Success,)       (0 - Success,)
1000037476  (0 - Success,)       (0 - Success,)
1000037477  (0 - Success,)       (0 - Success,)
1400028259  (0 - Success,)       (0 - Success,)
1400015221  (0 - Success,)       (0 - Success,)
1400024437  (0 - Success,)       (0 - Success,)

edited Dec 6, 2021 at 21:20

answered Dec 6, 2021 at 13:36

Corralien

121k8 gold badges43 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

pythondumb Over a year ago

I am getting ValueError: Mixing dicts with non-Series may lead to ambiguous ordering

Corralien Over a year ago

I updated my answer but I really don't understand your error. Can you share your file to understand if there is special characters inside, please?

pythondumb Over a year ago

How can I share the file?

Corralien Over a year ago

wetransfer, dropbox or any other solutions.

pythondumb Over a year ago

Here it goes. wetransfer.com/downloads/…

|

Learning is a mess · Accepted Answer · 2021-12-06 12:41:20Z

0

pd.DataFrame.from_dict expects a dict input, not a string:

import json
with open(os.path.join(dir_path,f_ind),'r') as f_r:
  j_s = f_r.read()
df = pd.DataFrame.from_dict(json.loads(j_s))

answered Dec 6, 2021 at 12:41

Learning is a mess

8,3178 gold badges42 silver badges82 bronze badges

5 Comments

pythondumb Over a year ago

As I have mentioned that d is not a JSON. Therefore json.loads(j_s) will throw an error like JSONDecodeError: Expecting property name enclosed in double quotes.

Learning is a mess Over a year ago

Can you parse with json.loads( j_s.replace('"', '\''))?

pythondumb Over a year ago

Not working. Same error.

user2261062 Over a year ago

you already have the dictionary, so you don't need any JSON loading. Just do df = pd.DataFrame.from_dict(d)

pythondumb Over a year ago

@SembeiNorimaki: I have updated the question. This is the OP. So from_dict(d) and earlier from_dict(j_s) are same thing.

Collectives™ on Stack Overflow

Python: Converting a nested Dictionary object into pandas dataframe

2 Answers 2

7 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related