Using Pandas to convert API response to single CSV row

user_17099757 0
Viewed 69

I'm looping through a list of domains to gather info on and I'd like to convert an API response into a single CSV row and I'm having trouble getting things in the right format. Here is an example of the API response:

[
   {
      "domainName":"something I made up.com",
      "resourceType":"web",
      "ipv4":"34.102.136.180",
      "geolocation":{
         "city":"The Greenhouse",
         "country":"US",
         "latitude":"37.41889",
         "longitude":"-122.10361",
         "postalCode":"",
         "region":"California",
         "timezone":"-07:00"
      },
   {
      "domainName":"somethingimadeup.com",
      "resourceType":"ns",
      "ipv4":"34.102.136.180",
      "geolocation":{
         "city":"The Greenhouse",
         "country":"US",
         "latitude":"37.41889",
         "longitude":"-122.10361",
         "postalCode":"",
         "region":"California",
         "timezone":"-07:00"
      },
   {
      "domainName":"ns03.domaincontrol.com",
      "resourceType":"www.web",
      "ipv4":"97.74.101.2",
      "geolocation":{
         "city":"Sweetwater Ranch",
         "country":"US",
         "latitude":"33.60421",
         "longitude":"-111.8882",
         "postalCode":"",
         "region":"Arizona",
         "timezone":"-07:00"
      },
]

into something like this:

domain Resource Type ipv4 Resource Type ipv4
example.com web 34.102.136.184 ns 34.102.136.184

I've been trying to figure it out with Pandas but not having much luck, I'm also not sure how to address each response having a different amount of resources, ie, more than one NS, can anyone help? I'm still learning python and that's not exactly what management heard.

I can share my code but it's just a for loop targeting the domain column that exists in the input csv at this point.

pandas csv format api python
1 Answers

You can use the json library to parse it, along with pandas' to_csv method to save it.

Example:

import json
import pandas as pd

api_response = ...

parsed = json.loads(api_response) # Parse the api response
df = pd.DataFrame(parsed[0]) # Index the first result and create a dataframe with it

with open("output.csv", "w+") as f: # Create a output.csv file
    df.to_csv(f) # Write the csv data to the file
user_138360510
Related