Question: problem in fetching data using excel
0
gravatar for sharmatina189059
7 days ago by
United States
sharmatina1890590 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 • 159 views
ADD COMMENTlink modified 7 days ago by george.ry1.0k • written 7 days ago by sharmatina1890590
2
gravatar for Alex Reynolds
7 days ago by
Alex Reynolds21k
Seattle, WA USA
Alex Reynolds21k 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 7 days ago • written 7 days ago by Alex Reynolds21k
0
gravatar for george.ry
7 days 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 7 days ago • written 7 days 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: 1311 users visited in the last hour