wwl wwl - 1 year ago 74
JSON Question

JSON to CSV in python: each JSON file has different keys

I'm trying to write many JSON files to a CSV file. Each JSON file has several keys, but different files have different keys. Here are three JSON files as an example.

file A:

{"a": 1, "c": 2}

file B:

{"b": 5, "d": 3}

file C:

{"a": 6, "b": 7}

I'd like one CSV file like this with four columns and three rows:

a b c d

1 2

5 3

6 7

One way to do this is by multiple try/except statements using csv writer. But that becomes infeasible as I am dealing with a large number of keys. Are there any alternatives?

Answer Source

You can append each JSON file to a list and then create dataframes and concatenate.

a = {"a": 1, "c": 2}
b = {"b": 5, "d": 3}
c = {"a": 6, "b": 7}
data = [a, b, c]

>>> pd.concat([pd.DataFrame(s, index=[0]) for s in data]).reset_index()
    a   b   c   d
0   1 NaN   2 NaN
1 NaN   5 NaN   3
2   6   7 NaN NaN
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download