problem in fetching data using excel

Question

[sharmatina189059]

I have two columns in excel one has ids and another has its values. Like

IDs                   pmid
Ko43748          pmid:686888    
Ko43748          pmid:755566

so the problem is ..since ids are duplicate in first columns but has different pmid. I want my result in same cell like..

Ko43748          pmid:686888 
                 pmid:755566

how can I do this task either using excel or using programming.

Answer 1

[Alex Reynolds]

Make an executable script called, for example, merge.py:

#!/usr/bin/env python

import sys

map = {}

# skip header
sys.stdin.readline()

# read data into map
for line in sys.stdin:
    (k, v) = line.strip().split('\t')
    if k not in map:
        map[k] = []
    map[k].append(v)

# write map to standard output
for k in map:
    sys.stdout.write("%s\t%s\n" % (k, ' '.join(map[k])))

Then export your spreadsheet as a tab-delimited file and run it through this script, e.g.:

$ ./merge.py < data.tsv
Ko43748   pmid:686888 pmid:755566

You can bring this tab-delimited data back into Excel, if need be.

Answer 2

[george.ry]

Get datamash ... it's invaluable!

sort -k1,1 < inputfile | datamash -g1 collapse 2 > groups