/
validate.py
executable file
·191 lines (169 loc) · 6.62 KB
/
validate.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
#!/usr/bin/env python3
import argparse
import csv
from dateutil.parser import parse
import os
import pickle
import subprocess
import sys
import yaml
#= Function ===========
# Load spreadsheet data
#======================
def read_spreadsheet(datafile):
print('Loading spreadsheet data from file "{0}" ... '.format(
datafile), end='')
if datafile.endswith(('.xlsx','.xls')):
print('converting Excel ... ', end='')
cmd = ['in2csv',datafile]
rawdata = subprocess.check_output(cmd)
reader = csv.DictReader(rawdata.decode('utf-8').split('\n'))
cols = reader.fieldnames
data = list(reader)
else:
with open(datafile, 'r') as f:
reader = csv.DictReader(f)
cols = reader.fieldnames
data = list(reader)
print("complete.")
return cols, data
#= Function ======
# Read schema file
#=================
def read_schema(schemafile):
print('Loading schema from file "{0}" ... '.format(schemafile), end='')
with open(schemafile, 'r') as f:
schema = yaml.load(f)
print("{0} main rules loaded.".format(len(schema)))
return schema
#= Function ===============
# Pull data from Excel File
#==========================
def convert_excel(excelfile):
bashCommand = "in2csv {0}".format(excelfile)
return subprocess.check_output(bashCommand.split())
#= Function ===================
# Check for ASCII control chars
#==============================
def charcheck(rawlines):
with open('control.p', 'rb') as f:
chars = pickle.load(f)
print('\nChecking raw bytes for control characters other than newlines ...')
for n, rawline in enumerate(rawlines):
for c in chars:
if c[0] in rawline and c[3] != "^J":
charlocation = rawline.find(c[0])
print('\tFound {0} ({1}) at {2} in line {3}'.format(c[4],
c[3], charlocation, n))
#= Function ==================
# Validate data against schema
#=============================
def validate(colnames, data, schema):
violations = {}
result = []
for rule_num, rule in enumerate(schema):
violations[rule] = []
# require specified columns
if rule == 'required':
print("{0}. Checking for required columns ... ".format(
rule_num+1), end='')
for reqcol in schema['required']:
if reqcol not in colnames:
violations[rule].append(
"Column {0} is required.".format(reqcol))
# disallow any columns not specified
elif rule == 'allowed':
print("{0}. Checking allowed columns ... ".format(
rule_num+1), end='')
for col in colnames:
if col not in schema['allowed']:
violations[rule].append(
"Column '{0}' not in allowed cols list".format(col))
# require columns to be populated
elif rule == 'populated':
print("{0}. Checking for populated columns ... ".format(
rule_num+1), end='')
for row_num, row in enumerate(data):
for popcol in schema['populated']:
if row[popcol] == '':
violations[rule].append(
"Column '{0}' is empty in row {1}.".format(
popcol, row_num+2))
# require columns to contain numeric data only
elif rule == 'numeric':
print("{0}. Checking numeric columns ... ".format(
rule_num+1), end='')
for row_num, row in enumerate(data):
for numcol in schema['numeric']:
try:
row[numcol] = int(row[numcol])
except ValueError:
violations[rule].append(
"Column '{0}' in row {1} is not numeric.".format(
numcol, row_num+2))
# require parseable date string
elif rule == 'date':
print("{0}. Checking date columns ... ".format(
rule_num+1), end='')
for row_num, row in enumerate(data):
for datcol in schema['date']:
try:
parse(row[datcol])
except ValueError:
violations[rule].append(
"Column '{0}' in row {1} is not a date.".format(
datcol, row_num+2))
# require values from controlled value list
elif rule == 'controlled':
print("{0}. Checking controlled values ... ".format(
rule_num+1), end='')
for row_num, row in enumerate(data):
for concol in schema['controlled']:
if row[concol] not in schema['controlled'][concol]:
violations[rule].append(
"Column '{0}' in row {1} has illegal value.".format(
concol, row_num+2))
# raise an error for non-conforming schema entries
else:
violations.append("Schema error: '{0}' not valid.".format(rule))
# report success or failure of each rule evaluation
if violations[rule]:
print("failed!")
result.extend([v for v in violations[rule]])
else:
print("passed.")
return result
#===========
# Main logic
#===========
# parse arguments
parser = argparse.ArgumentParser(description='Validate Tabular Data')
parser.add_argument('--schema', '-s', action='store',
help='path to schema file to validate against')
parser.add_argument('--output', '-o', action='store',
help='path to outputfile (for cleaned data)')
parser.add_argument('filename', help='data file to be validated or cleaned')
args = parser.parse_args()
# print output header
print("")
border = "=" * 40
print("\n".join([border, "# VALIDATE.PY: TABULAR DATA VALIDATION #", border]))
# load data
datafile = args.filename
colnames, data = read_spreadsheet(datafile)
# report stats
print("Data has {0} columns and {1} rows.".format(len(colnames), len(data)))
# load schema and validate
if args.schema:
schema = read_schema(args.schema)
violations = validate(colnames, data, schema)
if violations:
print("\nResult: Failure")
print("===============")
for n, v in enumerate(violations):
print("{0}. {1}".format(n+1, v))
print("")
else:
print("Result: Successful Validation!\n")
# save output file
# check encoding and convert to utf8