How to read in key-value pair from a json file as a pandas dataframe?

Question

I have a JSON file (stored at PATH) of the form:

{"key1":[{"col1": "1", "col2": "first", "col3": "1", "col4": "a"}, {"col1": "1", "col2": "first", "col3": "2", "col4": "b"}, {"col1": "1", "col2": "first", "col3": "3", "col4": "c"}, {"col1": "2", "col2": "second", "col3": "1", "col4": "d"}], "key2":[], "key3": {} }

I want to create a dataframe df from just values of key1 as so:

col1    col2    col3    col4
  1    first     1       a
  1    first     2       b
  1    first     1       c
  1    second    1       d

Right now, I have written the following one-liner to deal with this situation:

pd.DataFrame(pd.read_json(PATH, orient='index').T['key1'].to_dict()).T

I realize I might be doing a lot of unneccessary operations to get to the desired data structure and wanted to know if there is a more efficient way to achieve this?

Extra:

Although certainly not the main problem, I was wondering if there is a way to also handle the case where I have an additional key with a value that is not a collection (pretend in the JSON above we also have "key4": "hello"). Currently, my code fails to deal with this scenario as the pandas operation cannot be directly applied here. If this involves extensive additional preprocessing then it is fine if this case is not handled.

modesitt · Accepted Answer · 2020-02-04 21:02:17Z

9

Why not load the json from the file first and then create the dataframe from the sub collection you want?

import json 

with open(PATH, 'r') as fp:
    data = json.load(fp)

df = pd.DataFrame.from_dict(data["key1"])

answered Feb 4, 2020 at 21:02

modesitt

7,2492 gold badges38 silver badges66 bronze badges

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

Collectives™ on Stack Overflow

How to read in key-value pair from a json file as a pandas dataframe?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related