How to merge csv fields based on key

Question

I have this list:

key,value
1,"1"
1,"2"
1,"3"
2,"1"
2,"2"
2,"3"
2,"4"
2,"5"
3,"1"
3,"2"
3,"3"

How i can merge the value based on having the same key so it become:

key,value
1,"1,2,3"
2,"1,2,3,4,5"
3,"1,2,3"

I'm using this code from Python csv: merge rows with same field but the result is:

1,"1",1,"2",1,"3"
3,"1",3,"2",3,"3"
2,"1",2,"2",2,"3",2,"4",2,"5"

I can not see where have you posted your code you have been coding so far? — Elis Byberi, Oct 28 '17 at 16:43

score 1 · Answer 1 · answered Oct 28 '17 at 16:25

IIUC, if you want to see the possibility of such, without a business case for it, here's what you can do with a pandas dataframe d that has the key,value:

d.groupby(['key'])['value'].apply(lambda x: ','.join(x)).reset_index()


    key value
0   1   1,2,3
1   2   1,2,3,4,5
2   3   1,2,3

score 0 · Answer 2 · answered Oct 28 '17 at 16:36

You can try this:

s = """
key,value
1,"1"
1,"2"
1,"3"
2,"1"
2,"2"
2,"3"
2,"4"
2,"5"
3,"1"
3,"2"
3,"3"
 """
import itertools
new_data = [i.split(',') for i in s.split('\n')][2:-1]
new_data = [[int(a), b[1:-1]] for a, b in new_data]
final_data = [(a, ','.join([d for c, d in list(b)])) for a, b in itertools.groupby(new_data, key=lambda x:x[0])]

Output:

[(1, '1,2,3'), (2, '1,2,3,4,5'), (3, '1,2,3')]

score 0 · Answer 3 · answered Oct 28 '17 at 16:41

You can also try using defaultdict:

In [15]: li = [(1, '1'), (1, '2'), (1, '3'), (2, '1'), (2, '2'), (2, '3'), (2, '4'), (2, '5'), (3, '1'), (3, '2'), (3, '3')]

In [16]: dic = defaultdict(list)
In [17]: for key, value in li:
    ...:     dic[key].append(value)

In [18]: dic
Out[18]: 
defaultdict(list,
            {1: ['1', '2', '3'],
             2: ['1', '2', '3', '4', '5'],
             3: ['1', '2', '3']})

How to merge csv fields based on key

3 Answers3