How to get Json keys as columns in a csv with python?

Python Help
1

I am getting JSON-Data from the AzureDevOps-API and I want to save the data in a csv-file with the JSON-Keys as column-names. I have the following code:

import requests
import pandas

api_url = "***"
Headers = { "Authorization" : "***" }
response = requests.get(api_url, headers=Headers)

obj = pandas.read_json(response.text, orient='values')
obj.to_csv('output1.csv')

I am getting the following csv-output:

Bildschirmfoto 2023-09-17 um 12.58.33
Bildschirmfoto 2023-09-17 um 12.58.331264×1254 314 KB

So the JSON-Keys are not the column names and everything is in Column A nothing in Column B and so on. How can I get the JSON-Keys as Column-Names?

2

Can’t see everything, but it seems to me that you have some extra structure in your JSON data which Pandas isn’t expecting. Can you post a small example of the JSON data you’re working with? What are you expecting your column headers to be? I’d guess id, name, url, etc, in which case you need to extract the values associated with the repository key.

3

Before giving the data to pandas, you can first load it as json, and select the list of actual values in the json object. Something like this:

response = requests.get(api_url, headers=Headers)
res = json.loads(response.text)
obj = pandas.DataFrame(res["value"])
obj.to_csv("output1.csv")

Or try:

obj = pandas.json_normalize(res["value"])

(Afterwards you’ll still need to massage the column names a bit since it appears those values contain nested dicts.)

6

Your post doesn’t show if there is any problem or what it might be. But it appears that you have a deeply nested structure, which is kind of problematic and may not be very suitable for a pandas DataFrame.
If you have a simple structure like:

res1 = [{'A':1, 'B': 2, 'C': 3}, {'A':4, 'B': 5, 'C': 6}]

or

res2 = {'A': [1,4], 'B':[2,5], 'C':[3, 6]}

then

>>> pandas.DataFrame(res1)
   A  B  C
0  1  2  3
1  4  5  6

and the same for res2.

To convert a deeply nested struct to a DataFrame, you either have to do this in separate steps - first extracting the pieces you want - or you use pandas.json_normalize or you have to use extra tools, like the flatten_json library (which is also not trivial to use in this case, since your nesting mixes dicts and list types).

I would suggest you tinker a bit with some simpler dictionaries first (dicts that contains other dicts and lists) and see how pandas converts those into DataFrames. This may show you a way to handle your original complex dictionary.
You could also do a google search for “pandas and deeply nested structs” - which will bring up some tutorials in this area.