I have an excel file with JSON output in the following form:

{"text1":"1","text2":"1","text3":"0"}

I'd like to convert this to the following tabular format

I've tried using json_normalize() but the response is stored in object type and it doesn't work

Thanks!

CodePudding user response：

It will be an object datatype as your input is

a ={"text1":"1","text2":"1","text3":"0"}

and not this

a ={"text1":1,"text2":1,"text3":0}

pd.json_normalize(a).astype(int)

should solve the problem

CodePudding user response：

To convert that JSON output to a pandas Dataframe simply wrap it with the pandas DataFrame() as so:

import pandas as pd

jsonOutput = {"text1":"1","text2":"1","text3":"0"}

for key in jsonOutput.keys():
    jsonOutput[key] = [jsonOutput[key]]

pd.DataFrame(jsonOutput)

outputs:

If the corresponding values for the keys in your json output are arrays, you don't need to iterate through the keys