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.

Answer 1

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"])