查找适合组合条件的所有行

2018-06-19 00:21:51

我正在寻找使用python excel sql google这样做的最佳方式 - 我需要从n个值列表中找到适合k值的所有行。

例如，我有这张名为Animals的表格：

| Name     | mammal | move   |  dive |
+----------+--------+--------+-------+
| Giraffe  |  1     |    1   |   0   |
| Frog     |  0     |    1   |   1   |
| Dolphin  |  1     |    1   |   1   |
| Snail    |  0     |    1   |   0   | 
| Bacteria |  0     |    0   |   0   |

我想写一个函数foo，其行为如下：

foo（布尔值的元组，最小匹配）

foo((1,1,1),3) -> Dolphin
foo((1,1,1),2) -> Giraffe, Dolphin, Frog
foo((1,1,1),1) -> Giraffe, Dolphin, Frog, Snail
foo((1,1,0),2) -> Giraffe, Dolphin
foo((0,1,1),2) -> Dolphin, Frog
foo((0,1,1),1) -> Giraffe, Dolphin, Frog, Snail
foo((1,1,1),0) -> Giraffe, Dolphin, Frog, Snail, Bacteria

你最好的想法是什么？

这是一个纯Python 3解决方案。

data = [
    ('Giraffe',  1, 1, 0),
    ('Frog',     0, 1, 1),
    ('Dolphin',  1, 1, 1),
    ('Snail',    0, 1, 0),
    ('Bacteria', 0, 0, 0),
]

probes = [
    ((1, 1, 1), 3),
    ((1, 1, 1), 2),
    ((1, 1, 1), 1),
    ((1, 1, 0), 2),
    ((0, 1, 1), 2),
    ((0, 1, 1), 1),
    ((1, 1, 1), 0),
]

def foo(mask, minmatch):
    for name, *row in data:
        if sum(u & v for u, v in zip(mask, row)) >= minmatch:
            yield name

for mask, minmatch in probes:
    print(mask, minmatch, *foo(mask, minmatch))

产量

(1, 1, 1) 3 Dolphin
(1, 1, 1) 2 Giraffe Frog Dolphin
(1, 1, 1) 1 Giraffe Frog Dolphin Snail
(1, 1, 0) 2 Giraffe Dolphin
(0, 1, 1) 2 Frog Dolphin
(0, 1, 1) 1 Giraffe Frog Dolphin Snail
(1, 1, 1) 0 Giraffe Frog Dolphin Snail Bacteria

在Python 3.6.0上测试。它使用了一些在旧版本中不可用的语法，但很容易使它适应旧语法。

这种变体运行在较旧版本的Python上。在Python 2.6.6上测试过。

from __future__ import print_function

data = [
    ('Giraffe',  1, 1, 0),
    ('Frog',     0, 1, 1),
    ('Dolphin',  1, 1, 1),
    ('Snail',    0, 1, 0),
    ('Bacteria', 0, 0, 0),
]

probes = [
    ((1, 1, 1), 3),
    ((1, 1, 1), 2),
    ((1, 1, 1), 1),
    ((1, 1, 0), 2),
    ((0, 1, 1), 2),
    ((0, 1, 1), 1),
    ((1, 1, 1), 0),
]

def foo(mask, minmatch):
    for row in data:
        if sum(u & v for u, v in zip(mask, row[1:])) >= minmatch:
            yield row[0]

for mask, minmatch in probes:
    matches = list(foo(mask, minmatch))
    print(mask, minmatch, matches)

产量

(1, 1, 1) 3 ['Dolphin']
(1, 1, 1) 2 ['Giraffe', 'Frog', 'Dolphin']
(1, 1, 1) 1 ['Giraffe', 'Frog', 'Dolphin', 'Snail']
(1, 1, 0) 2 ['Giraffe', 'Dolphin']
(0, 1, 1) 2 ['Frog', 'Dolphin']
(0, 1, 1) 1 ['Giraffe', 'Frog', 'Dolphin', 'Snail']
(1, 1, 1) 0 ['Giraffe', 'Frog', 'Dolphin', 'Snail', 'Bacteria']

如果表是熊猫数据框：

def foo(df, val, n_match):
    results = []
    for r in  df.values:
        if sum(val & r[1:]) >= n_match:
            results.append(r[0])
    print("foo(%s), %d -> %s") % (val, n_match, ' '.join(results))

我会尝试使用python和pandas

假设“名称”栏是熊猫指数：

def foo(df, bool_index, minimum_matches):
    picked_column_index = [ idx for (idx, i) in enumerate(bool_index) if i] # select where "1" is
    picked_df = df.iloc[:, picked_column_index] #select column by location
    matched_row_bool = picked_df.sum(axis=1) >= minimum_matches
    return picked_df[matched_row_bool].index.tolist()

df是从表格（动物）读取的熊猫数据帧：

df = pandas.read_csv('animials_csv_file_path')

要么

df = pandas.read_excel('animials_xls_file_path')

它会返回一个包含匹配名称的列表

链接地址: http://www.djcxy.com/p/53563.html

上一篇: Finds all rows fitting to combinatorial condition

下一篇: Determining the number of return values in a Python function