搜索excel列以匹配文本值，打印行#s [英] Search excel columns for matching text value, print row #s

问题描述

这里我从不同的文档中获取名称和邮编值；并存储在变量中:(工作正常)

Here I grab the name and zip values from a different document; and store in variables: (works fine)

            Name = find_name.group(0)

然后我只想搜索我的 excel 文件以找到匹配项；在找到 Name 文本值的地方，获取行号:

Then I simply want to search my excel file to find a match; where the Name text value is found, get row number(s):

            data = pd.read_excel(config.Excel2)

            row_number = data[data['Member Name'].str.contains(Name)].index.min()
            print(row_number)

上面打印时输出不正确的行号，我不明白为什么.即它不会打印在我的 excel 文档中找到匹配文本值的行.它打印了一个错误的行号，与 Name 不匹配.

The above outputs the incorrect row number when printed, I cannot understand why. i.e. It does not print the row where the matching text value is found within my excel document. It prints an erroneous row number, that doesn't match the Name.

然后，我尝试过这样的事情；但这根本不输出任何内容:(输出密钥错误)

Then, I have tried something like this; but this doesn't output anything at all: (outputs Key Error)

        idx = data[data['Member Name'].str.contains(Name)].index
        row_number = idx[0] if len(idx)>0 else None
        print(row_number)

对如何实现这一目标有任何想法吗?

Any thoughts on how to achieve this?

我的 excel 如下所示(大约有 11000 行，如下所示，8 列).

My excel looks as follows (with about 11000 rows like the below, and 8 columns).

          A
1 |   Member Name     | Member Address Line 1 | Member Address Line 2
    RHONDA GILBERT       ADDRESS PT 1            ADDRESS PT 2 W/ ZIP

推荐答案

我没有你的excel文件，所以我设置了以下代码:

I do not have your excel file, so I setup the following code:

import pandas as pd
names = ["RHONDA GILBERT", "FRED FLINTSTONE", "FRED FLINTSTONE", "BARNEY RUBLE", "RHONDA GILBERT"]
add1 = ["123 Elm St", "254 Pine Ave", "254 Pine Ave", "654 Spruce Grove", "123 Elm St"]
df = pd.DataFrame(list(zip(names, add1)), 
   columns =['Member Name', 'Member Address Line 1']) 
df

它给了我以下输出:

    Member Name     Member Address Line 1
0   RHONDA GILBERT  123 Elm St
1   FRED FLINTSTONE 254 Pine Ave
2   FRED FLINTSTONE 254 Pine Ave
3   BARNEY RUBLE    654 Spruce Grove
4   RHONDA GILBERT  123 Elm St

如果我现在搜索FRED"然后我是这样写的:

If I now search for "FRED" then I write it like so:

Name = "FRED"
matches = df[df['Member Name'].str.contains(Name)]
matches

我得到的输出是这样的:

and the output I get is this:

    Member Name     Member  Address Line 1
1   FRED FLINTSTONE 254 Pine Ave
2   FRED FLINTSTONE 254 Pine Ave

请注意，如果我要求我得到的匹配索引

Note that if I ask for the indices of matches I get

matches.index
# outputs
Int64Index([1, 2], dtype='int64')

这些是df的原始索引.然后寻找索引的最小值

These are the original indices of df. So then looking for the minimum value of the index

matches.index.min()
# outputs
1

这是索引中的最小值.我不太确定您的结果与上述结果有何不同.如果你想澄清，我会改变我的解释.

This is the minimum of the indices. I am not too sure how your results deviated from the above. If you care to clarify, I will alter my explanation.

这篇关于搜索excel列以匹配文本值，打印行#s的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！