Python - How to convert JSON File to Dataframe

Question

How can I  convert a JSON File as such  into a dataframe to do some transformations   For Example if the JSON file reads     FirstName   John     LastName   Mark     MiddleName   Lewis     username   johnlewis2     password   2910     How can I convert it to a table like such  Column - gt  FirstName   LastName   MiddleName   username   password    Row ----- gt     John   Mark  Lewis   johnlewis2  2910

User · Answer

There are 2 inputs you might have and you can also convert between them.

input: listOfDictionaries --> use @VikashSingh solution

example: [{"":{"...

The pd.DataFrame() needs a listOfDictionaries as input.

input: jsonStr --> use @JustinMalinchak solution

example: '{"":{"...

If you have jsonStr, you need an extra step to listOfDictionaries first. This is obvious as it is generated like:

jsonStr = json.dumps(listOfDictionaries)

Thus, switch back from jsonStr to listOfDictionaries first:

listOfDictionaries = json.loads(jsonStr)

User · Answer

import pandas as pd print pd json normalize your json    This will Normalize semi-structured JSON data into a flat table Output   FirstName LastName MiddleName password    username       John     Mark      Lewis     2910  johnlewis2

User · Answer

Creating dataframe from dictionary object  import pandas as pd data      name    vikash    age   27     name    Satyam    age   14   df   pd DataFrame from dict data  orient  columns    df Out 4      age  name 0   27  vikash 1   14  Satyam  If you have nested columns then you first need to normalize the data  data              name            first    vikash          last    singh              age   27               name            first    satyam          last    singh              age   14        df   pd DataFrame from dict pd json normalize data   orient  columns    df     Out 8   age name first  name last 0   27  vikash  singh 1   14  satyam  singh  Source   pandas DataFrame from dict pandas json normalize

User · Answer

jsondata      0001    FirstName   John   LastName   Mark   MiddleName   Lewis   username   johnlewis2   password   2910     import json import pandas as pd jdata   json loads jsondata  df   pd DataFrame jdata  print df T   This should look like this              FirstName LastName MiddleName password    username 0001      John     Mark      Lewis     2910  johnlewis2

[python] Python - How to convert JSON File to Dataframe

Examples related to python

Examples related to json

Examples related to pandas

Examples related to dataframe