CODEWITHSUNDEEP
pythonjsonpandasdataframedictionary
R_Sengupta
R_Sengupta

Reputation: 171

Extract data into DF from JSON

I am attempting to extract data from a Json dictionary to a pandas DataFrame. However, the dictionary contains another dictionary within it. I am attaching a screenshot for reference.

enter image description here

The code which I wrote for extracting the data and creating the df are as follows:

response = requests.request("GET", url, headers=head)
extract = response.json()
df = pd.json_normalize(extract['Result'])

where I am defining the url and the headers.

Running the above code returns me a df, however, it still has one column(transactions) which is in a dictionary format. I am attaching a screenshot of the df for reference.

enter image description here

My desired output is to have the the elements of the dictionary as separate columns, within this df.

Any help on the above will be greatly appreciated.

Upvotes: 0

Views: 58

Answers (1)

Tom B.
Tom B.

Reputation: 338

Someone may have a better way but the below should work. It removes the transaction dict from extract and then combines it back into dictionary (using dictionary unpacking) before converting to a dataframe. I've tested it on some test data but without the original data, cannot test fully.

response = requests.request("GET", url, headers=head)
extract = response.json()
transaction = extract['Result']['transaction'] # Obtain copy of transaction dict
del extract['Result']['transaction']           # Delete transaction dict from 'extract'
extract = {**extract['Result'], **transaction} # Combine the two new dicts
df = pd.json_normalize(extract)                # Convert to dataframe

Upvotes: 1

Related Questions