Combining rows in a CSV file and updating quantities with Python

Question

I'm pretty new to python and I'm attempting to take a parts list from my furniture software and organize it in a way that works better. I'm starting with a csv file that writes each piece as its own item. So if I have 2 chair legs that are the same piece, I get

Leg 1,x,y,z,notes  
Leg 2,x,y,z,notes

What I would like to end up with is

2,Leg 1,x,y,z,notes

where the first column is now quantity and is updated whenever python sees a new row with the same x,y,z,notes.

Right now I have a code, which I found most of from another post, that can write a file that eliminates duplicates, and it seems like I should be able to make it add quantities relatively easily, but I can't quite figure it out. I've looked at other posts, but haven't seen anything about updating within the same file, only comparing one file to another.
Here is the code I've been using to eliminate duplicates:

import csv

input_file  = 'Brook Field 4 Drawer.csv'
output_file = 'updated_quantities.csv'

with open(input_file, 'rb') as infile, open(output_file, 'wb') as outfile:
    incsv = csv.reader(infile)
    outcsv = csv.writer(outfile)

    pieces = set()
    for row in incsv:
        piece = tuple(row[1:4])
        if piece not in pieces:
            outcsv.writerow(row)
            pieces.add(piece)

Can anyone suggest a solution? Am I even on the right track at the moment?

score 0 · Answer 1 · edited May 23 '17 at 11:51

Here you go :

from itertools import groupby

csv_content = \
    [['Leg', '1', 'x', 'y', 'z', 'notes'],
     ['Leg', '2', 'x', 'y', 'z', 'notes'],
     ['Leg', '3', 'abc', 'y', 'z', 'notes']]

groups = {str(l[2:]): l for l in csv_content}

for key, group in groupby([str(e[2:]) for e in csv_content]):
    groups[key].insert(0, len(list(group)))

csv_read_output = list(groups.itervalues())

Partly inspired by : How to count the frequency of the elements in a list?

score 0 · Accepted Answer · answered Sep 23 '15 at 00:44

Not quite right. Instead of a `set' you would need a dictionary I suppose, so you can keep increasing a counter whenever the item is found. Below is my suggestion:

pieces = {}
for row in incsv:
    piece = tuple(row)
    if piece not in pieces:
        pieces[piece] = 0
    else:
        pieces[piece] += 1
for item, count in pieces.items():
    row = (count,) + item
    outcsv.writerow(row)

Combining rows in a CSV file and updating quantities with Python

2 Answers2