Appending nested JSON data into columns using Python

Question

I am trying to get the output of a JSON response into a easier to read table with column headers. I am running into the problem of trying to access the specific JSON information. Here is what I have so far

import json
import requests
import pandas as pd

url = requests.get('https://apitest.com/api/scanners',auth=('', ''))
r = json.loads(url.content)
a = r['items']

My response is in this format here

I want to be able to append this information with the information of scanner name, antenna number and the last scan date, so I can eventually create a dataframe with the column headers

d = []
for h in a:
    d.append(
            { 'ScannerName': h['name'],
              'AntennaNumber':h['antennae']['antenna'],
              'LastScanDate': h['antennae']['lastScanDate']
                        }
            )

I receive a "TypeError: list indices must be integers or slices, not str" at this point. I wanted to know if anybody has any suggestions on a possible solution or if I need to approach this in a different way. Thank you.

Almas Zaurbekov · Accepted Answer · 2020-06-30 17:52:00Z

1

The data type of 'antennae' is a list, that's why you can not get value by using the key.

You must iterate the 'antennae' list and only then you'll be able to get the 'antenna' and 'lastScanDate' key.

Probably this would work as you expected:

d = []
for h in a:
   scanner = {
      'ScannerName' : h['name'],
      'AntennaNumber' : [],
      'LastScanDate' : []
   }

   for antennae in h['antennae']:
      scanner['AntennaNumber'].append(antennae['antenna'])
      scanner['LastScanDate'].append(antennae['lastScanDate'])

   d.append(scanner)

print(d)

answered Jun 30, 2020 at 17:52

Almas Zaurbekov

486 bronze badges

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

3 Comments

TLB Over a year ago

Thank you! I was under the impression this was my problem but was unsure of the syntax and which way to go , when I try to recreate this I only receive one scanner name,the last one. Is there another step for me to iterate so I can return them all?

Almas Zaurbekov Over a year ago

@TreyLaMonte I think it's not a mistake. If you pay attention to your screenshot, you can find out that your first record's 'antennea' is empty. Thus you will have some records with name and empty 'antennea'.

TLB Over a year ago

You are right, I am using Juypter Notebook and I split the cells accidentally separating the for loops thanks!

ph0987 · Accepted Answer · 2020-06-30 17:40:23Z

0

You can probably get almost where you want to be just with plain Pandas dataframe:

import json
import pandas as pd
import requests
url = 'https://api.wheretheiss.at/v1/satellites/25544/positions?timestamps=1593535000,1593538000'
response = requests.get(url)
r = json.loads(response.content)
pd.DataFrame(r)

(out comes a table).

To get the data from inner levels of nesting, I'd go with df.apply() with lambda functions!

answered Jun 30, 2020 at 17:40

ph0987

318 bronze badges

1 Comment

TLB Over a year ago

I am going to explore this option also, I am fairly new to Python so I will have to fiddle around with defining the functions, this gets me really close to what I am trying to accomplish.

Collectives™ on Stack Overflow

Appending nested JSON data into columns using Python

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related