| 1 | #!/usr/bin/env python2
|
| 2 | from __future__ import print_function
|
| 3 | """
|
| 4 | Usage:
|
| 5 | csv2html.py foo.csv
|
| 6 |
|
| 7 | Note: it's run with python2 AND python3
|
| 8 |
|
| 9 | Attempts to read foo_schema.csv. If not it assumes everything is a string.
|
| 10 |
|
| 11 | Things it handles:
|
| 12 |
|
| 13 | - table-sort.js integration <colgroup>
|
| 14 | - <table id="foo"> for making columns sortable
|
| 15 | - for choosing the comparator to use!
|
| 16 | - for highlighting on sort
|
| 17 | - static / visual
|
| 18 | - Aligning right for number, left for strings.
|
| 19 | - highlighting NA numbers in red (only if it's considered a number)
|
| 20 | - formatting numbers to a certain precision
|
| 21 | - or displaying them as percentages
|
| 22 | - changing CSV headers like 'elapsed_ms' to 'elapsed ms'
|
| 23 | - Accepting a column with a '_HREF' suffix to make an HTML link
|
| 24 | - We could have something like type:
|
| 25 | string/anchor:shell-id
|
| 26 | string/href:shell-id
|
| 27 | - But the simple _HREF suffix is simpler. Easier to write R code for.
|
| 28 |
|
| 29 | Implementation notes:
|
| 30 | - To align right: need a class on every cell, e.g. "num". Can't do it through
|
| 31 | <colgroup>.
|
| 32 | - To color, can use <colgroup>. table-sort.js needs this.
|
| 33 |
|
| 34 | TODO:
|
| 35 | Does it make sense to implement <rowspan> and <colspan> ? It's nice for
|
| 36 | visualization.
|
| 37 | """
|
| 38 |
|
| 39 | try:
|
| 40 | import html
|
| 41 | except ImportError:
|
| 42 | import cgi as html
|
| 43 | import csv
|
| 44 | import optparse
|
| 45 | import os
|
| 46 | import re
|
| 47 | import sys
|
| 48 |
|
| 49 |
|
| 50 | def log(msg, *args):
|
| 51 | if args:
|
| 52 | msg = msg % args
|
| 53 | print(msg, file=sys.stderr)
|
| 54 |
|
| 55 |
|
| 56 | class NullSchema:
|
| 57 | def VerifyColumnNames(self, col_names):
|
| 58 | pass
|
| 59 |
|
| 60 | def IsNumeric(self, col_name):
|
| 61 | return False
|
| 62 |
|
| 63 | def ColumnIndexIsNumeric(self, index):
|
| 64 | return False
|
| 65 |
|
| 66 | def ColumnIndexIsInteger(self, index):
|
| 67 | return False
|
| 68 |
|
| 69 | def ColumnIndexHasHref(self, index):
|
| 70 | return False
|
| 71 |
|
| 72 | def HasCssClassColumn(self):
|
| 73 | return False
|
| 74 |
|
| 75 |
|
| 76 | INTEGER_TYPES = ('integer',)
|
| 77 |
|
| 78 | # for sorting, right-justification
|
| 79 | # Note: added 'float' as alias for 'double' to be compatible with TSV8
|
| 80 | NUMERIC_TYPES = ('double', 'float', 'number') + INTEGER_TYPES
|
| 81 |
|
| 82 |
|
| 83 | # schema.tsv -> sqlite
|
| 84 | SQL_TYPES = {
|
| 85 | 'integer': 'INTEGER',
|
| 86 | 'double': 'REAL',
|
| 87 | 'float': 'REAL',
|
| 88 | 'number': 'REAL',
|
| 89 | 'string': 'TEXT',
|
| 90 | }
|
| 91 |
|
| 92 |
|
| 93 | class Schema:
|
| 94 | def __init__(self, rows):
|
| 95 | schema_col_names = rows[0]
|
| 96 | assert 'column_name' in schema_col_names, schema_col_names
|
| 97 | assert 'type' in schema_col_names, schema_col_names
|
| 98 |
|
| 99 | # Schema columns
|
| 100 | s_cols = {}
|
| 101 | s_cols['column_name'] = []
|
| 102 | s_cols['type'] = []
|
| 103 | s_cols['precision'] = []
|
| 104 | s_cols['strftime'] = []
|
| 105 | for row in rows[1:]:
|
| 106 | for i, cell in enumerate(row):
|
| 107 | name = schema_col_names[i]
|
| 108 | s_cols[name].append(cell)
|
| 109 |
|
| 110 | self.type_lookup = dict(
|
| 111 | (name, t) for (name, t) in
|
| 112 | zip(s_cols['column_name'], s_cols['type']))
|
| 113 |
|
| 114 | # NOTE: it's OK if precision is missing.
|
| 115 | self.precision_lookup = dict(
|
| 116 | (name, p) for (name, p) in
|
| 117 | zip(s_cols['column_name'], s_cols['precision']))
|
| 118 |
|
| 119 | self.strftime_lookup = dict(
|
| 120 | (name, p) for (name, p) in
|
| 121 | zip(s_cols['column_name'], s_cols['strftime']))
|
| 122 |
|
| 123 | #log('SCHEMA %s', schema_col_names)
|
| 124 | #log('type_lookup %s', self.type_lookup)
|
| 125 | #log('precision_lookup %s', self.precision_lookup)
|
| 126 |
|
| 127 | self.col_names = None
|
| 128 | self.col_has_href = None
|
| 129 |
|
| 130 | self.s_cols = s_cols # for ToSqlite()
|
| 131 |
|
| 132 | def ToSqlite(self):
|
| 133 | #print(self.s_cols)
|
| 134 |
|
| 135 | column_name = self.s_cols['column_name']
|
| 136 | typ = self.s_cols['type']
|
| 137 |
|
| 138 | cols = []
|
| 139 | for n, t in zip(column_name, typ):
|
| 140 | sqlite_type = SQL_TYPES[t.lower()]
|
| 141 | cols.append(' %s %s' % (n, sqlite_type))
|
| 142 | return ',\n'.join(cols)
|
| 143 |
|
| 144 | def VerifyColumnNames(self, col_names):
|
| 145 | """Assert that the column names we got are all in the schema."""
|
| 146 | if 0:
|
| 147 | for name in col_names:
|
| 148 | log('%s : %s', name, self.type_lookup[name])
|
| 149 |
|
| 150 | n = len(col_names)
|
| 151 | self.col_has_href = [False] * n
|
| 152 | for i in range(n-1):
|
| 153 | this_name, next_name = col_names[i], col_names[i+1]
|
| 154 | if this_name + '_HREF' == next_name:
|
| 155 | self.col_has_href[i] = True
|
| 156 |
|
| 157 | #log('href: %s', self.col_has_href)
|
| 158 | self.col_names = col_names
|
| 159 |
|
| 160 | def IsNumeric(self, col_name):
|
| 161 | return self.type_lookup[col_name] in NUMERIC_TYPES
|
| 162 |
|
| 163 | def ColumnIndexIsNumeric(self, index):
|
| 164 | col_name = self.col_names[index]
|
| 165 | return self.IsNumeric(col_name)
|
| 166 |
|
| 167 | def ColumnIndexIsInteger(self, index):
|
| 168 | col_name = self.col_names[index]
|
| 169 | return self.type_lookup[col_name] in INTEGER_TYPES
|
| 170 |
|
| 171 | def ColumnIndexHasHref(self, index):
|
| 172 | """
|
| 173 | Is the next one?
|
| 174 | """
|
| 175 | return self.col_has_href[index]
|
| 176 |
|
| 177 | def ColumnPrecision(self, index):
|
| 178 | col_name = self.col_names[index]
|
| 179 | return self.precision_lookup.get(col_name, 1) # default is arbitrary
|
| 180 |
|
| 181 | def HasStrfTime(self, col_name):
|
| 182 | # An explicit - means "no entry"
|
| 183 | return self.strftime_lookup.get(col_name, '-') != '-'
|
| 184 |
|
| 185 | def ColumnStrftime(self, index):
|
| 186 | col_name = self.col_names[index]
|
| 187 | return self.strftime_lookup.get(col_name, '-')
|
| 188 |
|
| 189 | def HasCssClassColumn(self):
|
| 190 | # It has to be the first column
|
| 191 | return self.col_names[0] == 'ROW_CSS_CLASS'
|
| 192 |
|
| 193 |
|
| 194 | def PrintRow(row, schema, css_class_pattern):
|
| 195 | """Print a CSV row as HTML, using the given formatting.
|
| 196 |
|
| 197 | Returns:
|
| 198 | An array of booleans indicating whether each cell is a number.
|
| 199 | """
|
| 200 | # TODO: cache this computation
|
| 201 | if css_class_pattern:
|
| 202 | row_class_pat, r = css_class_pattern.split(None, 2)
|
| 203 | cell_regex = re.compile(r)
|
| 204 | else:
|
| 205 | row_class_pat = None
|
| 206 | cell_regex = None
|
| 207 |
|
| 208 | i = 0
|
| 209 | n = len(row)
|
| 210 |
|
| 211 | row_classes = []
|
| 212 |
|
| 213 | if schema.HasCssClassColumn():
|
| 214 | i += 1 # Don't print this row
|
| 215 | # It's a CSS class
|
| 216 | row_classes.append(row[0])
|
| 217 |
|
| 218 | if cell_regex:
|
| 219 | for cell in row:
|
| 220 | if cell_regex.search(cell):
|
| 221 | row_classes.append(row_class_pat)
|
| 222 | break
|
| 223 |
|
| 224 | h = ' class="%s"' % ' '.join(row_classes) if row_classes else ''
|
| 225 | print(' <tr%s>' % h)
|
| 226 |
|
| 227 | while True:
|
| 228 | if i == n:
|
| 229 | break
|
| 230 |
|
| 231 | cell = row[i]
|
| 232 | css_classes = []
|
| 233 | cell_str = cell # by default, we don't touch it
|
| 234 |
|
| 235 | if schema.ColumnIndexIsInteger(i):
|
| 236 | css_classes.append('num') # right justify
|
| 237 |
|
| 238 | try:
|
| 239 | cell_int = int(cell)
|
| 240 | except ValueError:
|
| 241 | pass # NA?
|
| 242 | else:
|
| 243 | # commas AND floating point
|
| 244 | cell_str = '{:,}'.format(cell_int)
|
| 245 |
|
| 246 | # Look up by index now?
|
| 247 | elif schema.ColumnIndexIsNumeric(i):
|
| 248 | css_classes.append('num') # right justify
|
| 249 |
|
| 250 | try:
|
| 251 | cell_float = float(cell)
|
| 252 | except ValueError:
|
| 253 | pass # NA
|
| 254 | else:
|
| 255 | # Floats can also be timestamps
|
| 256 | fmt = schema.ColumnStrftime(i)
|
| 257 | if fmt not in ('-', ''):
|
| 258 | from datetime import datetime
|
| 259 | t = datetime.fromtimestamp(cell_float)
|
| 260 | if fmt == 'iso':
|
| 261 | cell_str = t.isoformat()
|
| 262 | else:
|
| 263 | cell_str = t.strftime(fmt)
|
| 264 | else:
|
| 265 | # commas AND floating point to a given precision
|
| 266 | # default precision is 1
|
| 267 | precision = schema.ColumnPrecision(i)
|
| 268 | cell_str = '{0:,.{precision}f}'.format(cell_float, precision=precision)
|
| 269 |
|
| 270 | # Percentage
|
| 271 | #cell_str = '{:.1f}%'.format(cell_float * 100)
|
| 272 |
|
| 273 | # Special CSS class for R NA values.
|
| 274 | if cell.strip() == 'NA':
|
| 275 | css_classes.append('na') # make it red
|
| 276 |
|
| 277 | if css_classes:
|
| 278 | print(' <td class="{}">'.format(' '.join(css_classes)), end=' ')
|
| 279 | else:
|
| 280 | print(' <td>', end=' ')
|
| 281 |
|
| 282 | s = html.escape(cell_str)
|
| 283 | # If it's an _HREF, advance to the next column, and mutate 's'.
|
| 284 | if schema.ColumnIndexHasHref(i):
|
| 285 | i += 1
|
| 286 | href = row[i]
|
| 287 | if href:
|
| 288 | s = '<a href="%s">%s</a>' % (html.escape(href), html.escape(cell_str))
|
| 289 |
|
| 290 | print(s, end=' ')
|
| 291 | print('</td>')
|
| 292 |
|
| 293 | i += 1
|
| 294 |
|
| 295 | print(' </tr>')
|
| 296 |
|
| 297 |
|
| 298 | def PrintColGroup(col_names, schema):
|
| 299 | """Print HTML colgroup element, used for JavaScript sorting."""
|
| 300 | print(' <colgroup>')
|
| 301 | for i, col in enumerate(col_names):
|
| 302 | if i == 0 and schema.HasCssClassColumn():
|
| 303 | continue
|
| 304 | if col.endswith('_HREF'):
|
| 305 | continue
|
| 306 |
|
| 307 | # CSS class is used for sorting
|
| 308 | if schema.IsNumeric(col) and not schema.HasStrfTime(col):
|
| 309 | css_class = 'number'
|
| 310 | else:
|
| 311 | css_class = 'case-insensitive'
|
| 312 |
|
| 313 | # NOTE: id is a comment only; not used
|
| 314 | print(' <col id="{}" type="{}" />'.format(col, css_class))
|
| 315 | print(' </colgroup>')
|
| 316 |
|
| 317 |
|
| 318 | def PrintTable(css_id, schema, col_names, rows, opts):
|
| 319 | print('<table id="%s">' % css_id)
|
| 320 | print(' <thead>')
|
| 321 | print(' <tr>')
|
| 322 | for i, col in enumerate(col_names):
|
| 323 | if i == 0 and schema.HasCssClassColumn():
|
| 324 | continue
|
| 325 | if col.endswith('_HREF'):
|
| 326 | continue
|
| 327 |
|
| 328 | heading_str = html.escape(col.replace('_', ' '))
|
| 329 | if schema.ColumnIndexIsNumeric(i):
|
| 330 | print(' <td class="num">%s</td>' % heading_str)
|
| 331 | else:
|
| 332 | print(' <td>%s</td>' % heading_str)
|
| 333 | print(' </tr>')
|
| 334 |
|
| 335 | for i in range(opts.thead_offset):
|
| 336 | PrintRow(rows[i], schema, opts.css_class_pattern)
|
| 337 |
|
| 338 | print(' </thead>')
|
| 339 |
|
| 340 | print(' <tbody>')
|
| 341 | for row in rows[opts.thead_offset:]:
|
| 342 | PrintRow(row, schema, opts.css_class_pattern)
|
| 343 | print(' </tbody>')
|
| 344 |
|
| 345 | PrintColGroup(col_names, schema)
|
| 346 |
|
| 347 | print('</table>')
|
| 348 |
|
| 349 |
|
| 350 | def ReadFile(f, tsv=False):
|
| 351 | """Read the CSV file, returning the column names and rows."""
|
| 352 |
|
| 353 | if tsv:
|
| 354 | c = csv.reader(f, delimiter='\t', doublequote=False,
|
| 355 | quoting=csv.QUOTE_NONE)
|
| 356 | else:
|
| 357 | c = csv.reader(f)
|
| 358 |
|
| 359 | # The first row of the CSV is assumed to be a header. The rest are data.
|
| 360 | col_names = []
|
| 361 | rows = []
|
| 362 | for i, row in enumerate(c):
|
| 363 | if i == 0:
|
| 364 | col_names = row
|
| 365 | continue
|
| 366 | rows.append(row)
|
| 367 | return col_names, rows
|
| 368 |
|
| 369 |
|
| 370 | def CreateOptionsParser():
|
| 371 | p = optparse.OptionParser()
|
| 372 |
|
| 373 | # We are taking a path, and not using stdin, because we read it twice.
|
| 374 | p.add_option(
|
| 375 | '--schema', dest='schema', metavar="PATH", type='str',
|
| 376 | help='Path to the schema.')
|
| 377 | p.add_option(
|
| 378 | '--tsv', dest='tsv', default=False, action='store_true',
|
| 379 | help='Read input in TSV format')
|
| 380 | p.add_option(
|
| 381 | '--css-class-pattern', dest='css_class_pattern', type='str',
|
| 382 | help='A string of the form CSS_CLASS:PATTERN. If the cell contents '
|
| 383 | 'matches the pattern, then apply the given CSS class. '
|
| 384 | 'Example: osh:^osh')
|
| 385 | # TODO: Might want --tfoot-offset from the bottom too? Default 0
|
| 386 | p.add_option(
|
| 387 | '--thead-offset', dest='thead_offset', default=0, type='int',
|
| 388 | help='Put more rows in the data in the thead section')
|
| 389 | return p
|
| 390 |
|
| 391 |
|
| 392 | def main(argv):
|
| 393 | (opts, argv) = CreateOptionsParser().parse_args(argv[1:])
|
| 394 |
|
| 395 | try:
|
| 396 | csv_path = argv[0]
|
| 397 | except IndexError:
|
| 398 | raise RuntimeError('Expected CSV filename.')
|
| 399 |
|
| 400 | schema = None
|
| 401 | if opts.schema:
|
| 402 | try:
|
| 403 | schema_f = open(opts.schema)
|
| 404 | except IOError as e:
|
| 405 | raise RuntimeError('Error opening schema: %s' % e)
|
| 406 | else:
|
| 407 | if csv_path.endswith('.csv'):
|
| 408 | schema_path = csv_path.replace('.csv', '.schema.csv')
|
| 409 | elif csv_path.endswith('.tsv'):
|
| 410 | schema_path = csv_path.replace('.tsv', '.schema.tsv')
|
| 411 | else:
|
| 412 | raise AssertionError(csv_path)
|
| 413 |
|
| 414 | #log('schema path %s', schema_path)
|
| 415 | try:
|
| 416 | schema_f = open(schema_path)
|
| 417 | except IOError:
|
| 418 | schema_f = None # allowed to have no schema
|
| 419 |
|
| 420 | if schema_f:
|
| 421 | if opts.tsv:
|
| 422 | r = csv.reader(schema_f, delimiter='\t', doublequote=False,
|
| 423 | quoting=csv.QUOTE_NONE)
|
| 424 | else:
|
| 425 | r = csv.reader(schema_f)
|
| 426 |
|
| 427 | schema = Schema(list(r))
|
| 428 | else:
|
| 429 | schema = NullSchema()
|
| 430 | # Default string schema
|
| 431 |
|
| 432 | #log('schema %s', schema)
|
| 433 |
|
| 434 | with open(csv_path) as f:
|
| 435 | col_names, rows = ReadFile(f, opts.tsv)
|
| 436 |
|
| 437 | schema.VerifyColumnNames(col_names)
|
| 438 |
|
| 439 | filename = os.path.basename(csv_path)
|
| 440 | css_id, _ = os.path.splitext(filename)
|
| 441 | PrintTable(css_id, schema, col_names, rows, opts)
|
| 442 |
|
| 443 |
|
| 444 | if __name__ == '__main__':
|
| 445 | try:
|
| 446 | main(sys.argv)
|
| 447 | except RuntimeError as e:
|
| 448 | print('FATAL: %s' % e, file=sys.stderr)
|
| 449 | sys.exit(1)
|