Question 171953 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How can I convert a multilevel dict to a Python dataframe using JSON?

I am getting output web api response as below.

d = { 'L1': [{'att_1': 10, 'att_2': 'whatever',
                  'att_4': 214, 'att_5': 'YES'}],
  'L2': [{'att_1': 11, 'att_2': 'whatever News',
                  'att_4': 230, 'att_5': 'another'}],
  'L3': [{'att_1': 12, 'att_2': 'whatever says',
                  'att_4': 213, 'att_5': 'YES'}]}

from above multilevel json/dict i need pandas data frame as below.

      att_1    att_2           att_4     att_5
L1    10       whatever        214       YES
L2    11       whatever  News  230       another
L3    12       whatever says   213       YES

I tried as below

for k, v in d.items():
   print(k)
   print(v)

pd.DataFrame.from_dict({k:(v,orient='index') for k, v in d.items()}axis=1)

I am getting error. could you pls provide the solution?

Tags:

3

Answers


  1. 0

    For general solution create DataFrames in dictionary comprehension with concat, last remove second level of MultiIndex by DataFrame.droplevel:

    df = pd.concat({k: pd.DataFrame(v) for k, v in d.items()}).droplevel(1)
print (df)
    att_1          att_2  att_4    att_5
L1     10       whatever    214      YES
L2     11  whatever News    230  another
L3     12  whatever says    213      YES

    If there are one element lists is possible use:

    df = pd.DataFrame.from_dict({k: v[0] for k, v in d.items()}, orient='index')
print (df)
    att_1          att_2  att_4    att_5
L1     10       whatever    214      YES
L2     11  whatever News    230  another
L3     12  whatever says    213      YES
    Login or Signup to reply.
  2. 0

    After fixing the typos in your d ('Lx' instead of Lx), you can re-arange/fix your code this way :

    df = pd.DataFrame.from_dict({k: v[0] for k, v in d.items()}, orient="index")

    ​
    Output :

    print(df)

    att_1          att_2  att_4    att_5
L1     10       whatever    214      YES
L2     11  whatever News    230  another
L3     12  whatever says    213      YES
    Login or Signup to reply.
  3. 0

    You can do like this:

    df = pd.DataFrame.from_records((v[0] for v in d.values()), index=d.keys())

+--+-----+-------------+-----+-------+
|  |att_1|att_2        |att_4|att_5  |
+--+-----+-------------+-----+-------+
|L1|10   |whatever     |214  |YES    |
|L2|11   |whatever News|230  |another|
|L3|12   |whatever says|213  |YES    |
+--+-----+-------------+-----+-------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search