CODEWITHSUNDEEP
pythonparsingawkcsv
crazyim5
crazyim5

Reputation: 714

count multiple tabs tsv file

I'm trying to parse a huge tab limited file (tsv file) and convert it into comma separated value file. The issue I'm having is that not all entries in the tsv file are complete and some of them are left incomplete and denoted by more than one tab spacing between the entries. Now when I'm converting this into csv file, I want "n.a" in between them indicating the absence of any entry in that field of the record.

For example, consider the student record sample(1 tab = 4 spaces, bear with my poor formatting)

Name    Age    Department    GPA
Kevin    21    Computer Science    3.4
Tom    20        3.8
Kelsey    22    Psychology        (2 tab spaces here)

In the above example the first record indicates the field title and every row is a record. We can observe that the 'Department' field entry is missing for Tom and 'GPA' field entry is missing for Kelsey. My output should be something like this: 

"Name","Age","Department","GPA"
"Kevin","21","Computer Science","3.4"
"Tom","20","n.a","3.8"
"Kelsey","22","Psychology","n.a"

My Questions:
1) How can I resolve this issue? Python, java, bash, awk any script would do
2) Observe that the space between the words "Computer" & "Science" in the 2nd row under 'department' field is ignored and preserved. So the resulting script shouldn't be counting spaces.

Doing this perfectly is very important since I would be feeding the data for search indexing. Thanks in advance.

Upvotes: 0

Views: 1732

Answers (4)

David Robinson
David Robinson

Reputation: 78600

This could be done in python very simply as:

import sys
[infile, outfile] = sys.argv[1:]

with open(infile) as inf:
    with open(outfile) as outf:
        for l in inf:
            outf.write(','.join(l.split('\t')).replace(',,',',n.a.,'))

The script would be used like

python convert_csv.py infile outfile

Upvotes: 4

Birei
Birei

Reputation: 36262

One way using awk:

awk '
    ## Split line with tabs, join them in output with commas.
    BEGIN {
        FS = "\t";
        OFS = ",";
    }

    ## For each line, check if any field is blank, and substitute with
    ## "n.a". Add double quotes, recompute line and print.
    {
        for ( i = 1; i <= NF; i++ ) {
            if ( $i == "" ) {
                $i = "n.a";
            }
            $i = "\"" $i "\"";
        }
        $1 = $1;
        print $0;
    }
' infile

Run it with following output:

"Name","Age","Department","GPA"
"Kevin","21","Computer Science","3.4"
"Tom","20","n.a","3.8"
"Kelsey","22","Psychology","n.a"

Upvotes: 1

dckrooney
dckrooney

Reputation: 3121

In python,

inputFile = open.("yourFile.tsv", "r")
outputFile = open.("output.csv", "w")

for line in inputFile:
    entry = line.split("\t")
    for i in range(len(entry)):
        if entry[i] == '':
            entry[i] = "n.a"
    outputFile.write(",".join(entry))

inputFile.close()
outputFile.close()

Should work, although it's not particularly Pythonic.

Upvotes: 0

Joran Beasley
Joran Beasley

Reputation: 113968

just use split('\t') on each line...

>>> x="a\t\tb"
>>> x
'a\t\tb'
>>> print x
a               b
>>> x.split("\t")
['a', '', 'b']
>>>

Upvotes: 0

Related Questions