Python通过使用字典比较表中的值来选择项

Question

我有一个包含12列的表，希望根据第二列（sseqid）选择第一列（qseqid）中的项。这意味着第二列（sseqid）在第11列和第12列中以不同的值重复，分别是evalue和bitscore。 我想得到的是最低的evalue和最高的bitscore（当evalue相同时，其余的列可以忽略，数据在下面）。你知道吗

因此，我制作了一个简短的代码，它使用第二列作为字典的键。我可以从第二列中得到五个不同的项目，它们的列表是qseqid+evalue和qseqid+bitscore。你知道吗

代码如下：

#!usr/bin/python

filename = "data.txt"

readfile = open(filename,"r")

d = dict()

for i in readfile.readlines():
    i = i.strip()
    i = i.split("\t")
    d.setdefault(i[1], []).append([i[0],i[10]])
    d.setdefault(i[1], []).append([i[0],i[11]])

for x in d:
    print(x,d[x])

readfile.close()

但是，我正在努力为每个sseqid获取具有最低evalue和最高bitscore的qseqid。 有什么好的逻辑来解决这个问题吗？你知道吗

data.txt文件（包括头行，»表示制表符）

qseqid»sseqid»pident»length»mismatch»gapopen»qstart»qend»sstart»send»evalue»bitscore
ACLA_022040»TBB»32.71»431»258»8»39»468»24»423»2.00E-76»240
ACLA_024600»TBB»80»435»87»0»1»435»1»435»0»729
ACLA_031860»TBB»39.74»453»251»3»1»447»1»437»1.00E-121»357
ACLA_046030»TBB»75.81»434»105»0»1»434»1»434»0»704
ACLA_072490»TBB»41.7»446»245»3»4»447»3»435»2.00E-120»353
ACLA_010400»EF1A»27.31»249»127»8»69»286»9»234»3.00E-13»61.6
ACLA_015630»EF1A»22»491»255»17»186»602»3»439»8.00E-19»78.2
ACLA_016510»EF1A»26.23»122»61»4»21»127»9»116»2.00E-08»46.2
ACLA_023300»EF1A»29.31»447»249»12»48»437»3»439»2.00E-45»155
ACLA_028450»EF1A»85.55»443»63»1»1»443»1»442»0»801
ACLA_074730»CALM»23.13»147»101»4»6»143»2»145»7.00E-08»41.2
ACLA_096170»CALM»29.33»150»96»4»34»179»2»145»1.00E-13»55.1
ACLA_016630»CALM»23.9»159»106»5»58»216»4»147»5.00E-12»51.2
ACLA_031930»RPB2»36.87»1226»633»24»121»1237»26»1219»0»734
ACLA_065630»RPB2»65.79»1257»386»14»1»1252»4»1221»0»1691
ACLA_082370»RPB2»27.69»1228»667»37»31»1132»35»1167»7.00E-110»365
ACLA_061960»ACT»28.57»147»95»5»146»284»69»213»3.00E-12»57.4
ACLA_068200»ACT»28.73»463»231»13»16»471»4»374»1.00E-53»176
ACLA_069960»ACT»24.11»141»97»4»581»718»242»375»9.00E-09»46.2
ACLA_095800»ACT»91.73»375»31»0»1»375»1»375»0»732

下面是更具可读性的表格内容：

0            1           2      3        4        5      6    7      8    9        10       11
qseqid       sseqid pident length mismatch  gapopen qstart qend sstart send    evalue bitscore
ACLA_022040  TBB     32.71    431      258        8    39   468     24  423  2.00E-76      240
ACLA_024600  TBB        80    435       87        0     1   435      1  435         0      729
ACLA_031860  TBB     39.74    453      251        3     1   447      1  437 1.00E-121      357
ACLA_046030  TBB     75.81    434      105        0     1   434      1  434         0      704
ACLA_072490  TBB      41.7    446      245        3     4   447      3  435 2.00E-120      353
ACLA_010400  EF1A    27.31    249      127        8    69   286      9  234  3.00E-13     61.6
ACLA_015630  EF1A       22    491      255       17   186   602      3  439  8.00E-19     78.2
ACLA_016510  EF1A    26.23    122       61        4    21   127      9  116  2.00E-08     46.2
ACLA_023300  EF1A    29.31    447      249       12    48   437      3  439  2.00E-45      155
ACLA_028450  EF1A    85.55    443       63        1     1   443      1  442         0      801
ACLA_074730  CALM    23.13    147      101        4     6   143      2  145  7.00E-08     41.2
ACLA_096170  CALM    29.33    150       96        4    34   179      2  145  1.00E-13     55.1
ACLA_016630  CALM     23.9    159      106        5    58   216      4  147  5.00E-12     51.2
ACLA_031930  RPB2    36.87   1226      633       24   121  1237     26 1219         0      734
ACLA_065630  RPB2    65.79   1257      386       14     1  1252      4 1221         0     1691
ACLA_082370  RPB2    27.69   1228      667       37    31  1132     35 1167 7.00E-110      365
ACLA_061960  ACT     28.57    147       95        5   146   284     69  213  3.00E-12     57.4
ACLA_068200  ACT     28.73    463      231       13    16   471      4  374  1.00E-53      176
ACLA_069960  ACT     24.11    141       97        4   581   718    242  375  9.00E-09     46.2
ACLA_095800  ACT     91.73    375       31        0     1   375      1  375         0      732

Answer 1

既然您是Python新手，我很高兴有几个例子说明如何手动执行此操作，但是为了进行比较，我将展示如何使用^{}库来执行此操作，这使处理表格数据变得更加简单。你知道吗

因为您没有提供示例输出，所以我假设对于给定的sseqid，通过“每个sseqid具有最低的evalue和最高的bitscore”，您的意思是“最低evalues中的最高bitscore”；如果您希望这些分别，那也很简单。你知道吗

import pandas as pd
df = pd.read_csv("acla1.dat", sep="\t")
df = df.sort(["evalue", "bitscore"],ascending=[True, False])
df_new = df.groupby("sseqid", as_index=False).first()

产生

>>> df_new
  sseqid       qseqid  pident  length  mismatch  gapopen  qstart  qend  sstart  send        evalue  bitscore
0    ACT  ACLA_095800   91.73     375        31        0       1   375       1   375  0.000000e+00     732.0
1   CALM  ACLA_096170   29.33     150        96        4      34   179       2   145  1.000000e-13      55.1
2   EF1A  ACLA_028450   85.55     443        63        1       1   443       1   442  0.000000e+00     801.0
3   RPB2  ACLA_065630   65.79    1257       386       14       1  1252       4  1221  0.000000e+00    1691.0
4    TBB  ACLA_024600   80.00     435        87        0       1   435       1   435  0.000000e+00     729.0

基本上，首先我们将数据文件读入一个名为DataFrame的对象，这有点像Excel工作表。然后我们按evalue升序排序（这样低的evalue优先），按bitscore降序排序（这样高的bitscore优先）。然后我们可以用groupby来收集相等sseqid的组中的数据，并在每个组中取第一个，由于排序的原因，这将是我们想要的。你知道吗

Answer 2

虽然不像使用pandas库那样优雅和简洁，但是不借助第三方模块就可以做您想做的事情。下面使用collections.defaultdict类来帮助创建具有可变长度记录列表的字典。使用AttrDict类是可选的，但是它使得访问每个基于字典的记录的字段变得更容易，并且比通常的dict['fieldname']语法看起来更简单。你知道吗

import csv
from collections import defaultdict, namedtuple
from itertools import imap
from operator import itemgetter

data_file_name = 'data.txt'
DELIMITER = '\t'
ssqeid_dict = defaultdict(list)

# from http://stackoverflow.com/a/1144405/355230
def multikeysort(items, columns):
    comparers = [((itemgetter(col[1:].strip()), -1) if col.startswith('-') else
                  (itemgetter(col.strip()),      1)) for col in columns]
    def comparer(left, right):
        for fn, mult in comparers:
            result = cmp(fn(left), fn(right))
            if result:
                return mult * result
        else:
            return 0
    return sorted(items, cmp=comparer)

# from http://stackoverflow.com/a/15109345/355230
class AttrDict(dict):
    def __init__(self, *args, **kwargs):
        super(AttrDict, self).__init__(*args, **kwargs)
        self.__dict__ = self

with open(data_file_name, 'rb') as data_file:
    reader = csv.DictReader(data_file, delimiter=DELIMITER)
    format_spec = '\t'.join([('{%s}' % field) for field in reader.fieldnames])
    for rec in (AttrDict(r) for r in reader):
        # Convert the two sort fields to numeric values for proper ordering.
        rec.evalue, rec.bitscore = map(float, (rec.evalue, rec.bitscore))
        ssqeid_dict[rec.sseqid].append(rec)

for ssqeid in sorted(ssqeid_dict):
    # Sort each group of recs with same ssqeid. The first record after sorting
    # will be the one sought that has the lowest evalue and highest bitscore.
    selected = multikeysort(ssqeid_dict[ssqeid], ['evalue', '-bitscore'])[0]
    print format_spec.format(**selected)

输出（»表示制表符）：

ACLA_095800»    ACT»    91.73»  375»    31»     0»      1»      375»    1»      375»    0.0»    732.0
ACLA_096170»    CALM»   29.33»  150»    96»     4»      34»     179»    2»      145»    1e-13»  55.1
ACLA_028450»    EF1A»   85.55»  443»    63»     1»      1»      443»    1»      442»    0.0»    801.0
ACLA_065630»    RPB2»   65.79»  1257»   386»    14»     1»      1252»   4»      1221»   0.0»    1691.0
ACLA_024600»    TBB»    80»     435»    87»     0»      1»      435»    1»      435»    0.0»    729.0

Answer 3

#!usr/bin/python
import csv

DATA = "data.txt"

class Sequence:
    def __init__(self, row):
        self.qseqid   =       row[0]
        self.sseqid   =       row[1]
        self.pident   = float(row[2])
        self.length   =   int(row[3])
        self.mismatch =   int(row[4])
        self.gapopen  =   int(row[5])
        self.qstart   =   int(row[6])
        self.qend     =   int(row[7])
        self.sstart   =   int(row[8])
        self.send     =   int(row[9])
        self.evalue   = float(row[10])
        self.bitscore = float(row[11])

    def __str__(self):
        return (
            "{qseqid}\t"
            "{sseqid}\t"
            "{pident}\t"
            "{length}\t"
            "{mismatch}\t"
            "{gapopen}\t"
            "{qstart}\t"
            "{qend}\t"
            "{sstart}\t"
            "{send}\t"
            "{evalue}\t"
            "{bitscore}"
        ).format(**self.__dict__)

def entries(fname, header_rows=1, dtype=list, **kwargs):
    with open(fname) as inf:
        incsv = csv.reader(inf, **kwargs)

        # skip header rows
        for i in range(header_rows):
            next(incsv)

        for row in incsv:
            yield dtype(row)

def main():
    bestseq = {}
    for seq in entries(DATA, dtype=Sequence, delimiter="\t"):
        # see if a sequence with the same sseqid already exists
        prev = bestseq.get(seq.sseqid, None)

        if (
            prev is None
            or seq.evalue < prev.evalue
            or (seq.evalue == prev.evalue and seq.bitscore > prev.bitscore)
        ):
            bestseq[seq.sseqid] = seq

    # display selected sequences
    keys = sorted(bestseq)
    for key in keys:
        print(bestseq[key])

if __name__ == "__main__":
    main()

结果是

ACLA_095800     ACT     91.73   375     31      0       1       375     1       375     0.0     732.0
ACLA_096170     CALM    29.33   150     96      4       34      179     2       145     1e-13   55.1
ACLA_028450     EF1A    85.55   443     63      1       1       443     1       442     0.0     801.0
ACLA_065630     RPB2    65.79   1257    386     14      1       1252    4       1221    0.0     1691.0
ACLA_024600     TBB     80.0    435     87      0       1       435     1       435     0.0     729.0