Python: Add column to a csv file based on the first column and second column

Question

I have a csv file with duplicate values in its first column, example:

mg,known,127
mg,unknown,142
pnt,known,37
pnt,unknown,0
lmo,known,75
lmo,unknown,3
sl,known,197
sl,unknown,21
oc,unknown,32
oc,known,163
sv,known,368
sv,unknown,308
az,unknown,6
az,known,241
bug,unknown,1
bug,known,167
li,unknown,15
li,known,174
lg,known,3

What I want to do is construct a new csv file such that example:

header1, known, unknown
mg, 127, 142
pnt, 37, 0

I am trying to figure out how I can really construct the row:

def read_stats(path):
    has_seen = set()
    with open(writepath, 'wb') as write_csv:
        with open(path, 'r') as csv_file:
            data_reader = csv.reader(csv_file, delimiter=',')
            for line in data_reader:
                if line[0] in has_seen:

This is where I am currently struck, do I have to keep a pointer to the next row?

Raymond Hettinger · Accepted Answer · 2013-04-17 03:29:29Z

Here's one approach that accumulates results in an OrderedDict:

>>> import csv
>>> import collections

>>> d = collections.OrderedDict()
>>> for header1, category, value in csv.reader(datafile):
        d.setdefault(header1, {})[category] = value

>>> for header1, m in d.items():
        print ', '.join([header1, m['known'], m['unknown']])

mg, 127, 142
pnt, 37, 0
lmo, 75, 3
sl, 197, 21
oc, 163, 32
sv, 368, 308
az, 241, 6
bug, 167, 1
li, 174, 15

If you can assume the lines always come in consecutive pairs with the known group first, you can create an intermediate result for knowns and emit a complete row for unkwowns:

>>> for header1, category, value in csv.reader(data):
        if category == 'known':
            result = [header1, value]
        else:
            result += [value]
            print ', '.join(result)

mg, 127, 142
pnt, 37, 0
lmo, 75, 3
sl, 197, 21
oc, 163, 32
sv, 368, 308
az, 241, 6
bug, 167, 1
li, 174, 15

Collectives™ on Stack Overflow

Python: Add column to a csv file based on the first column and second column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related