Convert an array of json objects to tsv (python)

Question

Suppose that I have the following array of json objects, I want to convert them to the tsv format.

[
  {
    "x": "1",
    "y": "2",
    "z": "3"
  },
  {
    "x": "6",
    "y": "7",
    "z": "B"
  }
]

Does anyone have a good solution to this? (python's json module only allow reading json object, but how to read an array of json object?)

x<TAB>y<TAB>z
1<TAB>2<TAB>3
6<TAB>7<TAB>8

Answer 1

A somewhat heavy handed approach would be to use Pandas

> import sys
> import pandas as pd
> table = pd.read_json('''[
  {
    "x": "1",
    "y": "2",
    "z": "3"
  },
  {
    "x": "6",
    "y": "7",
    "z": "B"
  }
]''', orient='records')
> table.to_csv(sys.stdout, sep='\t', index=False)

x   y   z
1   2   3
6   7   B

Answer 2

The first step is to convert from a JSON string to an array of Python objects using, for example, json.loads.

The final step is to write the Python objects to a file using, for example, csv.DictWriter.

Here is a complete program that demonstrates how to convert from a JSON string to tab-separated-values file.

import json
import csv

j = json.loads(r'''[
  {
    "x": "1",
    "y": "2",
    "z": "3"
  },
  {
    "x": "6",
    "y": "7",
    "z": "B"
  }
]''')

with open('output.tsv', 'w') as output_file:
    dw = csv.DictWriter(output_file, sorted(j[0].keys()), delimiter='\t')
    dw.writeheader()
    dw.writerows(j)