This is a test version of Biostars. For the public version, visit https://www.biostars.org.
problem in fetching data using excel

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.

genome kegg data
• 2,142 views • link
updated by george.ry george.ry • written by sharmatina189059 • sharmatina189059

2 answers

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.

• 0 views • link
written by Alex Reynolds Alex Reynolds

Get datamash ... it's invaluable!

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

• 0 views • link
written by george.ry george.ry

Log in to answer this question.

More posts like this