Python: Looping through a dictionary (created from JSON) to create row(s)-CodePudding

So, I'm attempting to import Dynatrace data through an api call which returns JSON. I have the JSON imported as a dictionary of dictionaries and lists (multiple nests).

What I'm trying to do is end up with rows that I can later use to write to a csv.

I think it'll make more sense with examples.

Example of imported JSON (cut short for space, but I left 3 "sections" of what I need):

dtrace_results = json.loads(http_response.content)

which looks like:

{'totalCount': 41, 'nextPageKey': None, 
 'result': [{'metricId': 'builtin:service.response.time', 
             'data': [{'dimensions': ['SERVICE-039D18D190DF8EE1'], 
                       'dimensionMap': {'dt.entity.service': 'SERVICE-039D18D190DF8EE1'}, 
                       'timestamps': [1641491760000, 1641491820000, 1641491880000], 
                       'values': [733, None, 724]}, 
                      {'dimensions': ['SERVICE-CF6C91BB030BB3AC'], 
                       'dimensionMap': {'dt.entity.service': 'SERVICE-CF6C91BB030BB3AC'}, 
                       'timestamps': [1641491760000, 1641491820000, 1641491880000], 
                       'values': [60951.354609929076, 56788.070796460175, 57866.69565217391]}, 
                      {'dimensions': ['SERVICE-BF03689183860CA9'], 
                       'dimensionMap': {'dt.entity.service': 'SERVICE-BF03689183860CA9'}, 
                       'timestamps': [1641491760000, 1641491820000, 1641491880000], 
                       'values': [69, 74, 78.5]}
                     ]}]}

After some manipulation to get it closer to where i want it:

for row in dtrace_results['result'][0]['data']:
    row.pop('dimensions')

results of printing the above: (bullets added for clarity)

{'dimensionMap': {'dt.entity.service': 'SERVICE-039D18D190DF8EE1'}, 'timestamps': [1641492060000, 1641492120000, 1641492180000, 1641492240000], 'values': [None, 1495.3333333333333, 1451.5, None]}
{'dimensionMap': {'dt.entity.service': 'SERVICE-CF6C91BB030BB3AC'}, 'timestamps': [1641492060000, 1641492120000, 1641492180000, 1641492240000], 'values': [70568.0193548387, 64320.80487804878,55929.954545454544, 57868.15151515151]}
{'dimensionMap': {'dt.entity.service': 'SERVICE-BF03689183860CA9'}, 'timestamps': [1641492060000, 1641492120000, 1641492180000, 1641492240000], 'values': [132, 101, 81, 77]}

what I'm trying to get from the above is basically, in this example 4 rows for each row above, like this (numbers added just for clarity, not needed in results):

SERVICE-039D18D190DF8EE1,1641492060000,None
SERVICE-039D18D190DF8EE1,1641492120000,1495.3333333333333
SERVICE-039D18D190DF8EE1,1641492180000,1451.5
SERVICE-039D18D190DF8EE1,1641492240000,None
...followed by additional rows

Thanks in advance for any help.

CodePudding user response：

If you're trying create a list of lists given in bullet points, you're almost there. Simply iterate over dtrace_results['result'][0]['data'] (since it's a list of dictionaries) and get the relevant values via another iteration by zipping the 3 lists. Note that since row['dimensionMap']['dt.entity.service'] is not a list, we'll have to make it a list.

out = []
for row in dtrace_results['result'][0]['data']:
    
    ts = row['timestamps']
    # we need to make 'service' a list of the same length as the other lists 
    service = [row['dimensionMap']['dt.entity.service']]*len(ts) 
    vals = row['values']
    
    for zipped in zip(service, ts, vals):
        out.append(list(zipped))

Output:

[['SERVICE-039D18D190DF8EE1', 1641491760000, 733],
 ['SERVICE-039D18D190DF8EE1', 1641491820000, None],
 ['SERVICE-039D18D190DF8EE1', 1641491880000, 724],
 ['SERVICE-CF6C91BB030BB3AC', 1641491760000, 60951.354609929076],
 ['SERVICE-CF6C91BB030BB3AC', 1641491820000, 56788.070796460175],
 ['SERVICE-CF6C91BB030BB3AC', 1641491880000, 57866.69565217391],
 ['SERVICE-BF03689183860CA9', 1641491760000, 69],
 ['SERVICE-BF03689183860CA9', 1641491820000, 74],
 ['SERVICE-BF03689183860CA9', 1641491880000, 78.5]]

CodePudding user response：

Using zip and csv will get you there I think.

Given your data like:

data = {
    'totalCount': 41,
    'nextPageKey': None,
    'result': [
        {
            'metricId': 'builtin:service.response.time',
            'data': [
                {
                    'dimensions': ['SERVICE-039D18D190DF8EE1'],
                    'dimensionMap': {'dt.entity.service': 'SERVICE-039D18D190DF8EE1'},
                    'timestamps': [1641491760000, 1641491820000, 1641491880000],
                    'values': [733, None, 724]
                },
                {
                    'dimensions': ['SERVICE-CF6C91BB030BB3AC'],
                    'dimensionMap': {'dt.entity.service': 'SERVICE-CF6C91BB030BB3AC'},
                    'timestamps': [1641491760000, 1641491820000, 1641491880000],
                    'values': [60951.354609929076, 56788.070796460175, 57866.69565217391]
                },
                {
                    'dimensions': ['SERVICE-BF03689183860CA9'],
                    'dimensionMap': {'dt.entity.service': 'SERVICE-BF03689183860CA9'},
                    'timestamps': [1641491760000, 1641491820000, 1641491880000],
                    'values': [69, 74, 78.5]
                },
                ## many more
            ]
        }
    ]
}

We might try:

results = []
for item in data["result"][0]["data"]:
    dimension = item["dimensions"][0]
    for timestamp, value in zip(item["timestamps"], item["values"]):
        results.append({
            "ID": dimension,
            "timestamp": timestamp,
            "value": value if value else "None"
        })

this would allow us to do:

print(results)

giving:

[
    {'ID': 'SERVICE-039D18D190DF8EE1', 'timestamp': 1641491760000, 'value': 733},
    {'ID': 'SERVICE-039D18D190DF8EE1', 'timestamp': 1641491820000, 'value': 'None'},
    {'ID': 'SERVICE-039D18D190DF8EE1', 'timestamp': 1641491880000, 'value': 724},
    {'ID': 'SERVICE-CF6C91BB030BB3AC', 'timestamp': 1641491760000, 'value': 60951.354609929076},
    {'ID': 'SERVICE-CF6C91BB030BB3AC', 'timestamp': 1641491820000, 'value': 56788.070796460175},
    {'ID': 'SERVICE-CF6C91BB030BB3AC', 'timestamp': 1641491880000, 'value': 57866.69565217391},
    {'ID': 'SERVICE-BF03689183860CA9', 'timestamp': 1641491760000, 'value': 69},
    {'ID': 'SERVICE-BF03689183860CA9', 'timestamp': 1641491820000, 'value': 74},
    {'ID': 'SERVICE-BF03689183860CA9', 'timestamp': 1641491880000, 'value': 78.5}
]

Turning results into a csv is then straightforward.

import csv
with open("results.csv", "w", encoding="utf-8", newline="") as file_out:
    writer = csv.DictWriter(file_out, fieldnames=results[0].keys())
    writer.writeheader()
    writer.writerows(results)

giving a csv that looks like:

ID,timestamp,value
SERVICE-039D18D190DF8EE1,1641491760000,733
SERVICE-039D18D190DF8EE1,1641491820000,None
SERVICE-039D18D190DF8EE1,1641491880000,724
SERVICE-CF6C91BB030BB3AC,1641491760000,60951.354609929076
SERVICE-CF6C91BB030BB3AC,1641491820000,56788.070796460175
SERVICE-CF6C91BB030BB3AC,1641491880000,57866.69565217391
SERVICE-BF03689183860CA9,1641491760000,69
SERVICE-BF03689183860CA9,1641491820000,74
SERVICE-BF03689183860CA9,1641491880000,78.5