How to create a dataframe with data from JSON output

Question

I've used a web API to import data from a specific website. I was able to import the data in JSON format. I am very new to python, hence finding hard to transform it to a tabular format which I can use it for my data analysis. Here's my sample code;

import json
import ast
import pandas as pd
import requests
from pandas import json_normalize

result = requests.get('https://discover.data.vic.gov.au/api/3/action/datastore_search?resource_id=dfgdfgdfgdsfgdfgd&limit=200') << #Open Source

data = result.json()

Sample JSOn output;

{'help': 'https://discover.data.vic.gov.au/api/3/action/help_show?name=datastore_search',
 'success': True,
 'result': {'include_total': True,
  'resource_id': 'bc71e010-253a-482a-bdbc-d65d1befe526',
  'fields': [{'type': 'int', 'id': '_id'},
   {'type': 'text', 'id': 'LGA'},
   {'type': 'text', 'id': 'lga_pid'},
   {'type': 'text', 'id': 'population'},
   {'type': 'text', 'id': 'active'},
   {'type': 'text', 'id': 'cases'},
   {'type': 'text', 'id': 'rate'},
   {'type': 'text', 'id': 'new'},
   {'type': 'text', 'id': 'band'},
   {'type': 'text', 'id': 'LGADisplay'},
   {'type': 'text', 'id': 'data_date'},
   {'type': 'text', 'id': 'file_processed_date'}],
  'records_format': 'objects',
  'records': [{'_id': 1,
    'LGA': 'Alpine (S)',
    'lga_pid': 'VIC242',
    'population': '12814',
    'active': '0',
    'cases': '1',
    'rate': '0',
    'new': '0',
    'band': '0',
    'LGADisplay': 'Alpine',
    'data_date': '14/06/2021',
    'file_processed_date': '15/06/2021'},

Actual output has number of _id = "81"

Tried using this function;

pd.json_normalize(data, record_path=['result'])

Unable to convert them to tabular format, Desired output;

Any help would be appreciated!!

Please, give a valid resource_id.

Corralien
– Corralien

2021-06-15 11:48:27 +00:00
Commented Jun 15, 2021 at 11:48 — Corralien
– Corralien, Commented Jun 15, 2021 at 11:48
@Corralien, updated resource_id

adey27
– adey27

2021-06-15 11:57:48 +00:00
Commented Jun 15, 2021 at 11:57 — adey27
– adey27, Commented Jun 15, 2021 at 11:57

Corralien · Accepted Answer · 2021-06-15 12:09:19Z

1

Is it what you expect?

>>> pd.DataFrame(data['result']['records'])

    _id               LGA lga_pid  ...    LGADisplay   data_date file_processed_date
0     1        Alpine (S)  VIC242  ...        Alpine  14/06/2021          15/06/2021
1     2       Ararat (RC)  VIC220  ...        Ararat  14/06/2021          15/06/2021
2     3      Ballarat (C)  VIC241  ...      Ballarat  14/06/2021          15/06/2021
3     4       Banyule (C)  VIC188  ...       Banyule  14/06/2021          15/06/2021
4     5    Bass Coast (S)  VIC173  ...    Bass Coast  14/06/2021          15/06/2021
..  ...               ...     ...  ...           ...         ...                 ...
76   77      Wodonga (RC)  VIC192  ...       Wodonga  14/06/2021          15/06/2021
77   78       Wyndham (C)  VIC221  ...       Wyndham  14/06/2021          15/06/2021
78   79         Yarra (C)  VIC202  ...         Yarra  14/06/2021          15/06/2021
79   80  Yarra Ranges (S)  VIC206  ...  Yarra Ranges  14/06/2021          15/06/2021
80   81  Yarriambiack (S)  VIC200  ...  Yarriambiack  14/06/2021          15/06/2021

[81 rows x 12 columns]

answered Jun 15, 2021 at 12:09

Corralien

121k8 gold badges43 silver badges68 bronze badges

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

2 Comments

adey27 Over a year ago

oh. Thank you!. I was trying to use For loop and experimenting with other options. appreciate your help

Corralien Over a year ago

If this is what you wanted, don't forget to accept the answer to point out to other users you found a solution.

Collectives™ on Stack Overflow

How to create a dataframe with data from JSON output

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related