import pylab
from itertools import imap

string_conversions=[int,float,pylab.datestr2num,str]
datatype_weight=dict([(dt,i) for i,dt in enumerate(string_conversions)])
def string_to_dt(s):
    for fn in string_conversions:
        try:
            v=fn(s)
            return fn
        except:
            pass
    return str

def dominant_datatype(dt1,dt2):
    return max((datatype_weight[dt1],dt1),(datatype_weight[dt2],dt2))[1]

upcast_functions={
    (int,float): lambda x: "." in x and x or x+".0",
    }
missing_items=set(['','.','\n','.\n'])
replace_missing=lambda x: x in missing_items and 'nan' or x

def fix_tricky_columns(fpath, delim=',', has_varnm=True ):

    def get_row_iter(f):
        row_iter=imap(lambda x: map(replace_missing,x.split(delim)),f)
        if has_varnm: row_iter.next()
        return row_iter

    def get_dominant_datatypes(dts,row):
        row_dts=[string_to_dt(x) for x in row]
        return [dominant_datatype(x,y) for x,y in zip(row_dts,dts)]

    fr=open(fpath,'r')
    row_iter=get_row_iter(fr)
    initial_datatypes=[string_to_dt(x) for x in row_iter.next()]
    datatypes=reduce(get_dominant_datatypes,row_iter,initial_datatypes)
    fr.close()

    if datatypes==initial_datatypes:
        print "Nothing done to file."
        return
    
    fr=open(fpath,'r')
    fw=open(fpath+"_fixed",'w')
    try:
        for nrow,row in enumerate(get_row_iter(fr)):
            out=[]
            for ncol,(dt,col) in enumerate(zip(datatypes,row)):
                col_dt=string_to_dt(col)
                if col_dt==dt:
                    out.append(col)
                elif (col_dt,dt) in upcast_functions:
                    out.append(upcast_functions[(col_dt,dt)](col))
                else:
                    raise "(%d,%d): Can't upcast from %s to %s" % (nrow,ncol,col_dt.__name__,dt.__name__)
            fw.write(delim.join(out)+"\n")
    finally:
        fw.close()
        fr.close()

if __name__ == '__main__':

    import csv, sys

    # creating data
    data = [['col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7'],
            ['1','3','1/97','1.12','2.11','001','bla1'],
            ['1.4','2','3/97','1.21','3.12','002','bla2'],
            ['2','1','2/97','1.12','2.11','003','bla3'],
            ['2','2','4/97','1.33','2.26','004','bla4'],
            ['2','2','5/97','1.73','2.42','005','bla15']]
    # saving data to csv file
    f = open('testdata_with_varnm.csv','wb')
    output = csv.writer(f)
    for i in data:
        output.writerow(i)
    f.close()

    fix_tricky_columns('testdata_with_varnm.csv')