How to use python to transpose and remove duplicate value in table?

Question

I need help with transposing row with python.

For example, I have this kind of file:

a | 1
a | 2
a | 3
b | 4
b | 5
b | 6

I want to make it look like this:

a | 1 | 2 | 3
b | 4 | 5 | 6

I know how to transpose the entire row by doing this:

import csv
from itertools import izip

a = izip(*csv.reader(open("fan.csv", "rb")))
csv.writer(open("fan1.csv", "wb")).writerows(a)

But i did not know how to use this to get the result that I want.

The above script will get me this result :

a | a | a | b | b | b
1 | 2 | 3 | 4 | 5 | 6

Answer 1

You don't want to transpose but group values for a key. itertools.groupby is a handy tool for that:

from itertools import groupby

r = csv.reader(open("fan.csv", "rb"))
a = [[k] + [x[1] for x in g] for k, g in groupby(r, key=lambda row: row[0])]

This groups the csv rows by the first column value and combines the grouping values with all second column values from from their respective groups into a new row.