validate tsv against json schema in python

Question

I am trying to validate rows of a TSV file against a JSON schema in python. Here is an example of the schema:

{
  "title": "employee",
  "type": "object",
  "properties": {
    "name": {
      "type": "string"
    },
    "surname": {
      "type": "string"
    },
    "position": {
      "type": "string"
    },
    "telephone": {
      "type": "integer"
    },
  "required": [
    "name",
    "surname",
    "position",
    "telephone"
  ]
}

and the TSV file is like:

NAME	SURNAME	POSITION	TELEPHONE
JOHN	JOHNSON	SECRETARY	11111

How can I check whether the elements in every row of the TSV file correspond to the fields of the JSON schema, i.e.

Are all elements listed in "required" present, and
Does the data type correspond to the types specified in the JSON schema?

How can I approach this problem?

Are there any packages in python that validate TSV using a JSON schema?

user_1531637 · Answer

You can roll your own validation with a bit of pandas and json. Note that your schema example isn't valid, as you have an open curly bracket. I've assumed that you meant to close the properties before the required tag. My example below uses this:

{
  "title": "employee",
  "type": "object",
  "properties": {
    "name": {
      "type": "string"
    },
    "surname": {
      "type": "string"
    },
    "position": {
      "type": "integer"
    },
    "telephone": {
      "type": "integer"
    }
  },
  "required": [
    "name",
    "surname",
    "position",
    "telephone"
  ]
}

Note that I also changed the data type for position to integer, as an example of something that doesn't validate. The json schema is stored in a file called input.json and the tabular data is stored in a file called input.tsv:

name  surname   position  telephone
John  Johnson  SECRETORY     111111

First we import our modules and read in our data like this:

import pandas as pd
import json

# Read tabular data
df = pd.read_csv("input.tsv", sep="	")

# Read JSON data
with open("input.json") as json_file:
    data = json.load(json_file)

We then check that all of our required columns are present:

# Check that all required columns are present
required = data["required"]
cols_ok = all([col in df.columns for col in required])  # True
print("All required columns present: %s" % (cols_ok))

We then validate the data types:

# Restructure the data types of our tabular data into a separate dataframe
dtypes = df.dtypes.reset_index()
dtypes.columns = ["column", "data_type"]
# There are discrepancies in naming conventions between your JSON file
# and how pandas reports data types, so we need to change them to a common format
dtypes.data_type = dtypes.data_type.replace("object", "string")  # Pandas calls it "object", you call it "string"
dtypes.data_type = dtypes.data_type.replace("int64", "integer")  # Pandas calls it "int64", you call it "integer"

# Extract the column names and the expected data type for that column
for column_name, subdict in data["properties"].items():
    expected_dtype = subdict["type"]
    actual_dtype = dtypes[dtypes.column == column_name].data_type.iloc[0]
    # Check if it matches our actual data types
    if expected_dtype == actual_dtype:
        print("Correct data type for column '%s' (%s)" % (column_name, expected_dtype))
    else:
        print("WRONG data type for column '%s' (expected '%s', found '%s')" % (column_name, expected_dtype, actual_dtype))

which outputs:

Correct data type for column 'name' (string)
Correct data type for column 'surname' (string)
WRONG data type for column 'position' (expected 'integer', found 'string')
Correct data type for column 'telephone' (integer)

validate tsv against json schema in python

1 Answers