Python - CSV time oriented Transposing large number of columns to rows -

Python - CSV time oriented Transposing large number of columns to rows -

i have many csv files "column" oriented , need pre-process index them.

this time oriented data, big number of columns each "device" (up 128 columns) like:

ldev_xxxxxx.csv serial number : xxxxx(vsp) : 2014/06/04 05:58 : 2014/06/05 05:58 sampling rate : 1 "no.","time","00:30:00x(x2497-1)","00:30:01x(x2498-1)","00:30:02x(x2499-1)" "242","2014/06/04 10:00",0,0,0 "243","2014/06/04 10:01",0,0,0 "244","2014/06/04 10:02",9,0,0 "245","2014/06/04 10:03",0,0,0 "246","2014/06/04 10:04",0,0,0 "247","2014/06/04 10:05",0,0,0

my goal transpose (if term right one) info rows, such able manipulate info much more efficiently, such as:

"time",device,value "2014/06/04 10:00","00:30:00x(x2497-1)",0 "2014/06/04 10:00","00:30:01x(x2498-1)",0 "2014/06/04 10:00","00:30:02x(x2499-1)",0 "2014/06/04 10:01","00:30:00x(x2497-1)",0 "2014/06/04 10:01","00:30:01x(x2498-1)",0 "2014/06/04 10:01","00:30:02x(x2499-1)",0 "2014/06/04 10:02","00:30:00x(x2497-1)",9 "2014/06/04 10:02","00:30:01x(x2498-1)",0 "2014/06/04 10:02","00:30:02x(x2499-1)",0

and on...

note: have allow raw info (which uses "," separator), note need delete 6 first lines "no" column has no interest, not main goal , difficulty)

i have python starting code transpose csv data, doesn't need...

import csv import sys infile = sys.argv[1] outfile = sys.argv[2] open(infile) f: reader = csv.reader(f) cols = [] row in reader: cols.append(row) open(outfile, 'wb') f: author = csv.writer(f) in range(len(max(cols, key=len))): writer.writerow([(c[i] if i<len(c) else '') c in cols])

note number of columns arbitrary, few, , 128 depending on files.

i'm pretty sure mutual need couldn't yet find exact python code this, or couldn't get...

edit:

more precision:

each timestamp row repeated number of devices, file have much more lines (multiplied number of devices) few rows (timestamp,device,value) final desired result has been updated :-)

edit:

i able utilize script using argument1 infile , argument2 outfile :-)

edit : expect quotes (") around no., port code python 2 indication python 3 , remove debugging print

edit2 : fixed stupid bug not incrementing indexes

edit3 : new version allowing input file contain multiple headers each followed data

i not sure worth utilize csv module, because separator fixed, have no quotes, , no field containing newline or separator character : line.strip.split(',') enough.

here tried :

skip lines until 1 begins no. , read fields after 2 firsts identifiers proceed line line take date on sec field print on line each field after 2 firsts using identifier

code python 2 (remove first line from __future__ import print_function python 3)

from __future__ import print_function class transposer(object): def _skip_preamble(self): line in self.fin: if line.strip().startswith('"no."'): self.keys = line.strip().split(',')[2:] homecoming raise exception('initial line not found') def _do_loop(self): line in self.fin: elts = line.strip().split(',') dat = elts[1] ix = 0 val in elts[2:]: print(dat, self.keys[ix], val, sep=',', file = self.out) ix += 1 def transpose(self, ficin, ficout): open(ficin) fin: open(ficout, 'w') fout: self.do_transpose(fin, fout) def do_transpose(self, fin, fout): self.fin = fin self.out = fout self._skip_preamble() self._do_loop()

usage :

t = transposer() t.transpose('in', 'out')

if input file contains multiple headers, necessary reset list of keys on each header :

from __future__ import print_function class transposer(object): def _do_loop(self): line_number = 0 line in self.fin: line_number += 1 line = line.strip(); if line.strip().startswith('"no."'): self.keys = line.strip().split(',')[2:] elif line.startswith('"'): elts = line.strip().split(',') if len(elts) == (len(self.keys) + 2): dat = elts[1] ix = 0 val in elts[2:]: print(dat, self.keys[ix], val, sep=',', file = self.out) ix += 1 else: raise exception("syntax error line %d expected %d values found %d" % (line_number, len(self.keys), len(elts) - 2)) def transpose(self, ficin, ficout): open(ficin) fin: open(ficout, 'w') fout: self.do_transpose(fin, fout) def do_transpose(self, fin, fout): self.fin = fin self.out = fout self.keys = [] self._do_loop()

python csv transpose