CODEWITHSUNDEEP
jsonpandasdata-wrangling
adey27
adey27

Reputation: 469

How to create a dataframe with data from JSON output

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;

enter image description here

Any help would be appreciated!!

Upvotes: 1

Views: 76

Answers (1)

Corralien
Corralien

Reputation: 120429

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]

Upvotes: 1

Related Questions