How to convert list of jsons in a nested list of jsons to csv in python?-CodePudding

How do I convert a list of jsons in a nested list of jsons to be in the following format? Can't seem to get this right and many examples use pandas where as I'd prefer to use csv.DictWriter. My thoughts are to (in a loop) read the json - in this case data, transpose it for it to be horizontal.

{"rows": [
        {
          "data": [
            {
              "A": "1",
              "B": "2"
            },
            {
              "C": "3",
              "D": "4"
            },
            {
              "E": "5",
              "F": "6"
            }
          ]
        },
       ...
       ...
       ...
        {
          "data": [
            {
              "A": "7",
              "B": "8"
            },
            {
              "C": "9",
              "D": "10"
            },
            {
              "E": "11",
              "F": "12"
            }
          ]
        }
]
}

Desired format:

A, B, C, D, E, F
1, 2, 3, 4, 5, 6
...
7, 8, 9, 10, 11, 12

I've read the json already using json.loads. Just stuck on converting this bit.

CodePudding user response：

You're probably better off not doing anything fancy. Just walk the dictionary and copy the data into a new one.

json_input = {}  # Your input JSON dictionary
transposed_data = {}
for row in json_input['rows']:
    for data_dict in row['data']:
        for key, value in data_dict.items():
            if key not in transposed_data:
                transposed_data[key] = []
            transposed_data[key].append(value)

Then you can just write out the data however you see fit. For example:

keys = transposed_data.keys()
num_values_per_key = len(transposed_data[keys[0]])
with open('output.csv', 'w') as output_file:
    output_file.write(f'{", ".join(keys)}\n')
    for index in range(num_values_per_key):
        values = []
        for key in keys:
            values.append(transposed_data[key][index])
        output_file.write(f'{", ".join(values)}\n')

It's also possible that some library providers a better way of making the CSV. I didn't spend much time here since it sounds like transposing is the main problem.

CodePudding user response：

In a just because it is possible manner I also want to show the list comprehension way of doing it. I am generally a great fan of the "pythonic way" of doing things, however in this case it does get a little messy.

import json      
import pandas as pd
json_data = '''{"rows": [
        {
          "data": [
            {
              "A": "1",
              "B": "2"
            },
            {
              "C": "3",
              "D": "4"
            },
            {
              "E": "5",
              "F": "6"
            }
          ]
        },
        {
          "data": [
            {
              "A": "7",
              "B": "8"
            },
            {
              "C": "9",
              "D": "10"
            },
            {
              "E": "11",
              "F": "12"
            }
          ]
        }
]
}'''
          
raw_data = json.loads(json_data)                  
                  
cleaned_data = [{key:val for subdict in row for key,val in subdict.items()} for row in [data["data"] for data in raw_data["rows"]]]

Out[118]: 
[{'A': '1', 'B': '2', 'C': '3', 'D': '4', 'E': '5', 'F': '6'},
 {'A': '7', 'B': '8', 'C': '9', 'D': '10', 'E': '11', 'F': '12'}]

df = pd.DataFrame(cleaned_data)

Out[120]: 
   A  B  C   D   E   F
0  1  2  3   4   5   6
1  7  8  9  10  11  12