Question: problem in fetching data using excel
0
gravatar for sharmatina189059
9 months ago by
United States
sharmatina18905930 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 • 416 views
ADD COMMENTlink modified 9 months ago by george.ry1.1k • written 9 months ago by sharmatina18905930
2
gravatar for Alex Reynolds
9 months ago by
Alex Reynolds24k
Seattle, WA USA
Alex Reynolds24k 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 months ago • written 9 months ago by Alex Reynolds24k
0
gravatar for george.ry
9 months ago by
george.ry1.1k
United Kingdom
george.ry1.1k wrote:

Get datamash ... it's invaluable!

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

ADD COMMENTlink modified 9 months ago • written 9 months ago by george.ry1.1k
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: 1501 users visited in the last hour