How to pull data from the JSON file

Question

I have a JSON File which contains some data as below:

{
  'count': 2,
  'next': '?page=2',
  'previous': None,
  'results': [
    {
      'category': 'Triggers',
      'id': '783_23058',
      'name': 'Covid-19'
    }, 
    {
      'category': 'Sources',
      'id': '426_917746',
      'name': 'Covid19Conversations'
    }
  ]
}

I am able to extract the first 'id' and 'name' values as below

Doc_details = dict() 
for item in companies:
  doc_id = companies['results'][0]['id']
  doc_name = companies['results'][0]['name']
  Doc_details[doc_name] = doc_id

for key, value in Doc_details.items():
  print(key,value)

Output:

Covid-19 783_23058

I am new to python. Can someone help me with:

Loop through it and extract all the key,value pairs
Save the results to an excel file.

You need the json loads function. ```import json``` and then ```my_data = json.loads(raw_data)``` this will give you a dictionary with the data — Rashid 'Lee' Ibrahim, Mar 25 '20 at 15:33
@Rashid'Lee'Ibrahim It looks like OP already has the dictionary `companies` with the data. — ywbaek, Mar 25 '20 at 15:42
Please edit your question to fix the example json file (in it's current form it's invalid and we can't be sure of it's structure). Also, avoid asking two questions in one please (hint: for the second point you probably want to use a .csv file instead (Excel knows how to read csv files). — bruno desthuilliers, Mar 25 '20 at 15:42

score 1 · Answer 1 · answered Mar 25 '20 at 15:42

1

If you only want the name, id pairs, you can just do:

for result in companies['results']:
  print(result['name'], result['id'])

# =>
# Covid-19 783_23058
# Covid19Conversations 426_917746

answered Mar 25 '20 at 15:42

blueteeth

3,330
1
13
23

score 1 · Answer 2 · answered Mar 25 '20 at 15:47

IIUC: You can use inbuilt json package to parse the json file as python dict and then you can use pandas library to write the excel file:

Try this:

import json
import pandas as pd
from pandas import ExcelWriter

with open("json_file.json", "r") as file:
    info = json.load(file) # info contains all key-value pairs

    # save to excel
    writer = ExcelWriter('excel_file.xlsx')
    pd.DataFrame(info["results"]).to_excel(writer, index=False)
    writer.save()

score 1 · Accepted Answer · answered Mar 25 '20 at 15:51

If you already have the object, you can iterate through companies['results'] using list comprehension and map the objects to (key, value) pairs.

companies = {
  'count': 2,
  'next': '?page=2',
  'previous': None,
  'results': [{
    'category': 'Triggers',
    'id': '783_23058',
    'name': 'Covid-19'
  }, {
    'category': 'Sources',
    'id': '426_917746',
    'name': 'Covid19Conversations'
  }]
}

pairs = list(map(lambda x: [ x['id'], x['name'] ], companies['results']))
csv = '\n'.join('\t'.join(val for val in pair) for pair in pairs)

print(csv)

Result

783_23058   Covid-19
426_917746  Covid19Conversations

Writing to a file

Convert the list of pairs to a CSV file. See: Writing a Python list of lists to a csv file.

import csv

with open('pairs.csv', 'wb') as f:
    writer = csv.writer(f)
    writer.writerows(pairs)

How to pull data from the JSON file

3 Answers3

Result

Writing to a file