Question: problem in fetching data using excel
0
gravatar for sharmatina189059
9 weeks ago by
United States
sharmatina18905910 wrote:

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.

kegg data genome • 224 views
ADD COMMENTlink modified 9 weeks ago by george.ry1.0k • written 9 weeks ago by sharmatina18905910
2
gravatar for Alex Reynolds
9 weeks ago by
Alex Reynolds22k
Seattle, WA USA
Alex Reynolds22k wrote:

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.

ADD COMMENTlink modified 9 weeks ago • written 9 weeks ago by Alex Reynolds22k
0
gravatar for george.ry
9 weeks ago by
george.ry1.0k
United Kingdom
george.ry1.0k wrote:

Get datamash ... it's invaluable!

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

ADD COMMENTlink modified 9 weeks ago • written 9 weeks ago by george.ry1.0k
Please log in to add an answer.

Help
Access

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.
Powered by Biostar version 2.3.0
Traffic: 652 users visited in the last hour