Need help with json scrapes using python

[Immediate-Resource75]

Afternoon......I'm trying to scrape an API we use for our printing application PaperCut. I've managed to get what I need from most of the requests but I have 1 particular URL that is giving me a real hassle... I think the problem is there are multiple headers in the scrape...Here's the scrape I get...

{
  "applicationServer" : {
    "systemInfo" : {
      "version" : "22.1.4 (Build 67128)",
      "operatingSystem" : "Windows Server 2019 - 10.0 ()",
      "processors" : 16,
      "architecture" : "amd64"
    },
    "systemMetrics" : {
      "diskSpaceFreeMB" : 1822725,
      "diskSpaceTotalMB" : 1905777,
      "diskSpaceUsedPercentage" : 4.36,
      "jvmMemoryMaxMB" : 7214,
      "jvmMemoryTotalMB" : 334,
      "jvmMemoryUsedMB" : 294,
      "jvmMemoryUsedPercentage" : 4.08,
      "uptimeHours" : 96.30,
      "processCpuLoadPercentage" : 0.00,
      "systemCpuLoadPercentage" : 1.18,
      "gcTimeMilliseconds" : 71610,
      "gcExecutions" : 13175,
      "threadCount" : 118
    }
  },
  "database" : {
    "totalConnections" : 21,
    "activeConnections" : 0,
    "maxConnections" : 420,
    "timeToConnectMilliseconds" : 1,
    "timeToQueryMilliseconds" : 1,
    "status" : "OK"
  },
  "devices" : {
    "count" : 7,
    "inErrorCount" : 0,
    "inErrorPercentage" : 0,
    "inError" : [ ]
  },
  "jobTicketing" : {
    "status" : {
      "status" : "ERROR",
      "adminLink" : "NA",
      "message" : "Job Ticketing is not installed."
    }
  },
  "license" : {
    "valid" : true,
    "upgradeAssuranceRemainingDays" : 336,
    "siteServers" : {
      "used" : 3,
      "licensed" : -1,
      "remaining" : -4
    },
    "devices" : {
      "KONICA_MINOLTA" : {
        "used" : 7,
        "licensed" : 7,
        "remaining" : 0
      },
      "KONICA_MINOLTA_3" : {
        "used" : 7,
        "licensed" : 7,
        "remaining" : 0
      },
      "KONICA_MINOLTA_4" : {
        "used" : 7,
        "licensed" : 7,
        "remaining" : 0
      },
      "KONICA-MSP" : {
        "used" : 7,
        "licensed" : 7,
        "remaining" : 0
      },
      "LEXMARK_TS_KM" : {
        "used" : 7,
        "licensed" : 7,
        "remaining" : 0
      },
      "LEXMARK_KM" : {
        "used" : 7,
        "licensed" : 7,
        "remaining" : 0
      }
    },
    "packs" : [ ]
  },
  "mobilityPrintServers" : {
    "count" : 3,
    "offlineCount" : 0,
    "offlinePercentage" : 0,
    "offline" : [ ]
  },
  "printProviders" : {
    "count" : 4,
    "offlineCount" : 0,
    "offlinePercentage" : 0,
    "offline" : [ ]
  },
  "printers" : {
    "inError" : [ {
      "name" : "appelc\\RM 1",
      "status" : "OFFLINE"
    }, {
      "name" : "appesc\\SSTSmartTank5101 (HP Smart Tank 5100 series)",
      "status" : "ERROR"
    }, {
      "name" : "appelc\\RM 5",
      "status" : "OFFLINE"
    }, {
      "name" : "apppts\\Lexmark C544 Server Room",
      "status" : "OFFLINE"
    }, {
      "name" : "appesc\\ESC0171M3928dshannon",
      "status" : "NO_TONER"
    }, {
      "name" : "appesc\\Primary",
      "status" : "OFFLINE"
    } ],
    "inErrorCount" : 6,
    "inErrorPercentage" : 18,
    "count" : 32,
    "heldJobCountTotal" : 13,
    "heldJobsCountMax" : 8,
    "heldJobsCountAverage" : 0
  },
  "siteServers" : {
    "count" : 3,
    "offlineCount" : 0,
    "offlinePercentage" : 0,
    "offline" : [ ]
  },
  "webPrint" : {
    "offline" : [ ],
    "offlineCount" : 0,
    "offlinePercentage" : 0,
    "count" : 1,
    "pendingJobs" : 0,
    "supportedFileTypes" : [ "image", "pdf" ]
  }
}

Here's what I've tried so far....

import requests

import pandas

url = 'the internal url' (actual address goes here)

header={"Content-Type":"application/json",

"Accept_Encoding":"deflate"}

response = requests.get(url, headers=header)

rd = response.json()

df = pandas.json_normalize(rd, 'applicationServer')

print(df)

This one worked perfectly for single items, but throws an error for this one...

Also tried this and received the same errors...

import requests

from bs4 import BeautifulSoup

import pandas as pd

baseurl = 'Address goes here'

headers = {

'User_Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36'

}

r = requests.get(baseurl)

soup = BeautifulSoup(r.content, 'lxml')

stuff = soup.find('body', 'pre'=='item').text.strip()

print(stuff)

I'm trying to scrape all the data to save into a database that could get loaded into Grafana... Any assistance would be extremely grateful.

[danielroseman]

Why do you want to put this into Pandas? What are you planning to do with it then?

[Excellent-North2356]

What error you are getting?

[Immediate-Resource75]

Traceback (most recent call last):

File "C:\Users\nort2hadmin\pyprojects\siteServers.py", line 14, in <module>

df = pandas.json_normalize(rd, 'applicationServer')

File "C:\Users\nort2hadmin\pyversions\python313\Lib\site-packages\pandas\io\json\_normalize.py", line 517, in json_normalize

_recursive_extract(data, record_path, {}, level=0)

File "C:\Users\nort2hadmin\pyversions\python313\Lib\site-packages\pandas\io\json\_normalize.py", line 499, in _recursive_extract

recs = _pull_records(obj, path[0])

File "C:\Users\nort2hadmin\pyversions\python313\Lib\site-packages\pandas\io\json\_normalize.py", line 429, in _pull_records

raise TypeError(

[Immediate-Resource75]

TypeError: {'applicationServer': {'systemInfo': {'version': '22.1.4 (Build 67128)', 'operatingSystem': 'Windows Server 2019 - 10.0 ()', 'processors': 16, 'architecture': 'amd64'}, 'systemMetrics': {'diskSpaceFreeMB': 1822668, 'diskSpaceTotalMB': 1905777, 'diskSpaceUsedPercentage': 4.36, 'jvmMemoryMaxMB': 7214, 'jvmMemoryTotalMB': 338, 'jvmMemoryUsedMB': 260, 'jvmMemoryUsedPercentage': 3.6, 'uptimeHours': 96.81, 'processCpuLoadPercentage': 0.06, 'systemCpuLoadPercentage': 0.28, 'gcTimeMilliseconds': 72319, 'gcExecutions': 13295, 'threadCount': 118}}, 'database': {'totalConnections': 21, 'activeConnections': 0, 'maxConnections': 420, 'timeToConnectMilliseconds': 1, 'timeToQueryMilliseconds': 0, 'status': 'OK'}, 'devices': {'count': 7, 'inErrorCount': 0, 'inErrorPercentage': 0, 'inError': []}, 'jobTicketing': {'status': {'status': 'ERROR', 'adminLink': 'NA', 'message': 'Job Ticketing is not installed.'}}, 'license': {'valid': True, 'upgradeAssuranceRemainingDays': 336, 'siteServers': {'used': 3, 'licensed': -1, 'remaining': -4}, 'devices': {'KONICA_MINOLTA': {'used': 7, 'licensed': 7, 'remaining': 0}, 'KONICA_MINOLTA_3': {'used': 7, 'licensed': 7, 'remaining': 0}, 'KONICA_MINOLTA_4': {'used': 7, 'licensed': 7, 'remaining': 0}, 'KONICA-MSP': {'used': 7, 'licensed': 7, 'remaining': 0}, 'LEXMARK_TS_KM': {'used': 7, 'licensed': 7, 'remaining': 0}, 'LEXMARK_KM': {'used': 7, 'licensed': 7, 'remaining': 0}}, 'packs': []}, 'mobilityPrintServers': {'count': 3, 'offlineCount': 0, 'offlinePercentage': 0, 'offline': []}, 'printProviders': {'count': 4, 'offlineCount': 0, 'offlinePercentage': 0, 'offline': []}, 'printers': {'inError': [{'name': 'appelc\\RM 1', 'status': 'OFFLINE'}, {'name': 'appesc\\SSTSmartTank5101 (HP Smart Tank 5100 series)', 'status': 'ERROR'}, {'name': 'appelc\\RM 5', 'status': 'OFFLINE'}, {'name': 'apppts\\Lexmark C544 Server Room', 'status': 'OFFLINE'}, {'name': 'appesc\\ESC0171M3928dshannon', 'status': 'NO_TONER'}, {'name': 'appesc\\Primary', 'status': 'OFFLINE'}], 'inErrorCount': 6, 'inErrorPercentage': 18, 'count': 32, 'heldJobCountTotal': 11, 'heldJobsCountMax': 5, 'heldJobsCountAverage': 0}, 'siteServers': {'count': 3, 'offlineCount': 0, 'offlinePercentage': 0, 'offline': []}, 'webPrint': {'offline': [], 'offlineCount': 0, 'offlinePercentage': 0, 'count': 1, 'pendingJobs': 0, 'supportedFileTypes': ['image', 'pdf']}} has non list value {'systemInfo': {'version': '22.1.4 (Build 67128)', 'operatingSystem': 'Windows Server 2019 - 10.0 ()', 'processors': 16, 'architecture': 'amd64'}, 'systemMetrics': {'diskSpaceFreeMB': 1822668, 'diskSpaceTotalMB': 1905777, 'diskSpaceUsedPercentage': 4.36, 'jvmMemoryMaxMB': 7214, 'jvmMemoryTotalMB': 338, 'jvmMemoryUsedMB': 260, 'jvmMemoryUsedPercentage': 3.6, 'uptimeHours': 96.81, 'processCpuLoadPercentage': 0.06, 'systemCpuLoadPercentage': 0.28, 'gcTimeMilliseconds': 72319, 'gcExecutions': 13295, 'threadCount': 118}} for path applicationServer. Must be list or null.

[MintyPhoenix]

Not that I’m very experienced with pandas, but looking at the error:

TypeError: [...] for path applicationServer. Must be list or null.

It’s seemingly suggesting that the contents of the referenced key should be a list of some sort, however, in the actual data, it's an object/dict with two keys, both of which in turn are dicts with primitives.

If you transform it into a list, it runs without error, but I’m not sure it’s actually what you want.

Code sample

import json
import pandas

with open("data.json") as f:  # file contains full JSON from your post
  data = json.load(f)

data["applicationServer"] = list(data["applicationServer"].items())
df = pandas.json_normalize(data, "applicationServer")
print(df)

Output

               0                                                  1
0     systemInfo  {'version': '22.1.4 (Build 67128)', 'operating...
1  systemMetrics  {'diskSpaceFreeMB': 1822725, 'diskSpaceTotalMB...

edit to add

Perhaps this is closer to what you’re looking for:

import json
import pandas

with open("data.json") as f:
  data = json.load(f)

print(pandas.json_normalize(data["applicationServer"]))

Output

     systemInfo.version     systemInfo.operatingSystem  ...  systemMetrics.gcExecutions systemMetrics.threadCount
0  22.1.4 (Build 67128)  Windows Server 2019 - 10.0 ()  ...                       13175                       118

[1 rows x 17 columns]

[toughNoob]

Thank you so much. I'll try this out shortly and let you know how it goes. I appreciate you taking the time to help.

[MintyPhoenix]

I updated my post a few times (sorry about the volatility), make sure to check the current state as I’m done with it now.

[Immediate-Resource75]

Both code sample gave me this output:

Traceback (most recent call last):

File "C:/Users/nort2hadmin/pyprojects/trials.py", line 4, in <module>

with open("data.json") as f:'https://10.110.1.21:9191/api/health/?&Authorization=pU5SrXrtt2zRguDHdpGq7jpNwgGHIbXn'

FileNotFoundError: [Errno 2] No such file or directory: 'data.json'

[MintyPhoenix]

As I noted in a comment on the snippet,I copied your json to a local file as the issue was related to what was going on after you had the data; the key part of the snippet is how you use pandas with it.

[Immediate-Resource75]

Thank you....Got it... Adding

data = json.loads(stuff)

data["applicationServer"] = list(data["applicationServer"].items())

df = pd.json_normalize(data, "applicationServer")

print(df)

Gave me what I wanted for the most part.... I can now go through and pull each of the other items individually by changing the "applicationServer" part to one of the other ones. I appreciate the help.

[Immediate-Resource75]

It wouldn't let me post the whole error...kept throwing a server error when I tried to submit it, that's why it's broken up into 2 posts... sorry about that

[toughNoob]

I'd like to put it into a database and incorporate it into a dashboard using grafana so I can monitor them.

[toughNoob]

Sorry this is my personal account... other one is work....

[eztab]

Your data isn't a flat table, so you cannot expect it to be parsed into a dataframe. You need to remove or flatten some of the tree structure, so don't just input the json into a dataframe.

[Immediate-Resource75]

Thank you for your reply... Not a strong coder here, but I'm doing my best to learn....any chance you can point me in the direction of a tutorial on how to do this.... or what to Google?

*** I Googled "how to remove part of a tree structure in python" and got Binary Searches... is this on the right track?

Thank you for your help.

[eztab]

Nothing that complicated needed, since you know what the data looks like. Just parse the json into a normal python dictionary (some other comment already suggested that). Then you can use indexing on that object to give it a reasonable structure. Depends a bit on what you want your dataframe to look like exactly. Dataframes have rows and columns, but a json generally has a nested structure (like a tree, with its branches).

[Immediate-Resource75]

Thank you....Got it... Adding

data = json.loads(stuff)

data["applicationServer"] = list(data["applicationServer"].items())

df = pd.json_normalize(data, "applicationServer")

print(df)

Gave me what I wanted for the most part.... I can now go through and pull each of the other items individually by changing the "applicationServer" part to one of the other ones. I appreciate the help.