Using python csv - Merging rows based on column

Question

Current table

Desired result

Not getting any results with this right now. Not sure where to go from here.

import csv
csv_dict = {}
with open("Test.csv", "r") as source:
    reader = csv.reader(source)
    header = next(reader)
    for row in reader:
        if row[0] in csv_dict:
            csv_dict[row[0]] += row
        else:
            csv_dict[row[0]] = row
        print (row)

Answer 1

what you are trying to do doesn't work because you're adding the whole row each time.

You want to filter out blanks and accumulate values on one key.

There are 2 ways (with 2 different outcomes):

1) standard accumulation, regardless of positions

import csv,collections
csv_dict = collections.defaultdict(list)

with open("test.csv", "r") as source:
    reader = csv.reader(source)
    header = next(reader)
    for key,*rest in reader:
        csv_dict[key] += filter(None,rest)

print(csv_dict)

You get this dictionary: {'b': ['2', '4'], 'a': ['1', '2', '3', '4']}

2) accumulation but with positions matching the original position of the non-blank value

In that case, you have to increase the list size if the index doesn't fit in.

import collections,csv

csv_dict = collections.defaultdict(list)

with open("test.csv", "r") as source:
    reader = csv.reader(source)
    header = next(reader)
    for key,*rest in reader:
        for i,r in enumerate(rest):
            if r:
                d = csv_dict[key]
                while i>=len(d):
                    d.append("")
                d[i] = r

print(csv_dict)

that yields:

{'a': ['1', '2', '3', '4'], 'b': ['', '2', '', '4']}

In both cases, there are several solutions to write this dictionary in a resulting csv file (using a csv.DictWriter, csv.writer, pandas module).