CODEWITHSUNDEEP
pythonjsonexport-to-csv
ben
ben

Reputation: 111

Python: JSON to CSV

I am receiving a JSON file from a Docparser API, which I would like to convert to a CSV document.

The structure is here below:

{
  "type": "object",
  "properties": {
    "id": {
      "type": "string"
    },
    "document_id": {
      "type": "string"
    },
    "remote_id": {
      "type": "string"
    },
    "file_name": {
      "type": "string"
    },
    "page_count": {
      "type": "integer"
    },
    "uploaded_at": {
      "type": "string"
    },
    "processed_at": {
      "type": "string"
    },
    "table_data": [
      {
        "type": "array",
        "items": {
          "type": "object",
          "properties": {
            "account_ref": {
              "type": "string"
            },
            "client": {
              "type": "string"
            },
            "transaction_type": {
              "type": "string"
            },
            "key_4": {
              "type": "string"
            },
            "date_yyyymmdd": {
              "type": "string"
            },
            "amount_excl": {
              "type": "string"
            }
          },
          "required": [
            "account_ref",
            "client",
            "transaction_type",
            "key_4",
            "date_yyyymmdd",
            "amount_excl"
          ]
        }

      }
    ]
  }
}

The first problem that I have is how to only work with the table_data section?

My second problem is writing the actual code that allows me to put each section, i.e. account_ref, client, etc., into their own columns. I had so many changes to my code, the output varied from adding the properties into columns and dumping the table_data part into one cell, to only printing the headers into a single cell (as a list).

Here's my current code (which is not working correctly):

import pydocparser
import json
import pandas as pd

parser = pydocparser.Parser()
parser.login('API')

data2 = str(parser.fetch("Name of Parser", 'documentID'))
data2 = str(data2).replace("'", '"') # I had to put this in because it kept saying that it needs double quotes.

y = json.loads(str(data2))

json_file = open(r"C:\File.json", "w")
json_file.write(str(y))
json_file.close()
df1 = df = pd.DataFrame({str(y)})
df1.to_csv(r"C:\jsonCSV.csv")

Thanks for your help!

Upvotes: 0

Views: 99

Answers (1)

Golden
Golden

Reputation: 417

Pandas has a nice built in function called pandas.json_noramlize() If you're using pandas version lower then 1.0.0 use pandas.io.json.json_normalize(), it should split the columns nicely. read more about it here:

>1.0.0: https://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.io.json.json_normalize.html

=<1.0.0 https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.json_normalize.html

Upvotes: 1

Related Questions