logo标签列表

Pandas：在500万行上使用Apply和正则表达式字符串匹配

pythonregexpandasclassificationapply
4

问题：我想根据“描述”列适当地对数据框中的每一行进行分类。 为此，我想基于常见单词列表提取关键字。 首先，我将关键短语拆分为单词（例如，“Food Store”变为“Food”和“Store”）。然后，我检查我的数据框中是否有任何一行同时包含“Food”和“Store”两个单词。不幸的是，我编写的代码速度太慢。如何优化代码以处理500万行数据？

示例数据：

这是我的数据框的前30行：

   bank_report_id transaction_date  amount                                        description type_codes              category
0              14698       2016-04-26   -3.00  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings
1              14698       2016-04-25 -110.00                                  ROGERSWL 1TIME _V                    Uncategorized
2              14698       2016-04-25  -10.50                                     SUBWAY # x6664               Restaurants/Dining
3              14698       2016-04-25   -1.00  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings
4              14698       2016-04-25  -73.75                                    TICKETMASTER CA                    Entertainment
5              14698       2016-04-25   -6.20                                     HAPPY ONE STOP                 Home Improvement
6              14698       2016-04-25   -7.74                                    BOOSTERJUICE-19               Restaurants/Dining
7              14698       2016-04-25  -28.49                                    LEISURE-FIRST O                    Uncategorized
8              14698       2016-04-22   -3.16                                    MCDONALD'S #400               Restaurants/Dining
9              14698       2016-04-22   -0.50  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings
10             14698       2016-04-22  -10.50                                     SUBWAY # x6664               Restaurants/Dining
11             14698       2016-04-21  -19.87                                     TRAFALGAR ESSO                    Gasoline/Fuel
12             14698       2016-04-21   -1.00  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings
13             14698       2016-04-20   -3.76                                    MCDONALD'S #400               Restaurants/Dining
14             14698       2016-04-20   -1.00  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings
15             14698       2016-04-20  -40.00                                     TRAFALGAR ESSO                    Gasoline/Fuel
16             14698       2016-04-19  -10.07                                     TRAFALGAR ESSO                    Gasoline/Fuel
17             14698       2016-04-19   -5.21                                    TIM HORTONS #24               Restaurants/Dining
18             14698       2016-04-19   -3.50  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings
19             14698       2016-04-18   -1.00  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings
20             14698       2016-04-18   -5.21                                    TIM HORTONS #24               Restaurants/Dining
21             14698       2016-04-18  -22.57                                     WAL-MART #3170              General Merchandise
22             14698       2016-04-18  -16.94                                    URBAN PLANET #1                   Clothing/Shoes
23             14698       2016-04-18  -12.95                                     LCBO/RAO #0545               Restaurants/Dining
24             14698       2016-04-18  -13.87                                     TRAFALGAR ESSO                    Gasoline/Fuel
25             14698       2016-04-18  -41.75                                     NON-TD ATM W/D             ATM/Cash Withdrawals
26             14698       2016-04-18   -4.19                                     SUBWAY # x6338               Restaurants/Dining
27             14698       2016-04-15   -0.50  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings
28             14698       2016-04-15  -35.06                                       UNION BURGER               Restaurants/Dining
29             14698       2016-04-15  -25.00                                     PIONEER STN #1                      Electronics

以下是单词列表的小部分：

['Exxon Mobil', 'Shell', 'Food Store', 'Pizza', 'Walgreens', 'Payday Loan', 'NSF', 'Lincoln', 'Apartment', 'Homes']

我的解决方案：

def get_matches(row):

    keywords = pd.read_csv('Keywords.csv', encoding='ISO-8859-1')['description'].apply(lambda x: x.lower()).str.split(
        " ").tolist()

    split_description = [d.lower() for d in row['description'].split(" ")]

    thematches = []
    for group in keywords:
        matches = [any([bool(re.search(y, x)) for x in split_description]) for y in group]

        if all(matches):
            thematches.append(" ".join(group))

    if len(thematches) > 0:
        return thematches
    else:
        return "NA"

df['match'] = df.apply(get_matches, axis=1)

期望输出：

    bank_report_id transaction_date  amount                                        description type_codes              category              match
0            14698       2016-04-26   -3.00  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings      [simply save]
1            14698       2016-04-25 -110.00                                  ROGERSWL 1TIME _V                    Uncategorized           [rogers]
2            14698       2016-04-25  -10.50                                     SUBWAY # x6664               Restaurants/Dining           [subway]
3            14698       2016-04-25   -1.00  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings      [simply save]
4            14698       2016-04-25  -73.75                                    TICKETMASTER CA                    Entertainment    [ticket master]
5            14698       2016-04-25   -6.20                                     HAPPY ONE STOP                 Home Improvement                 NA
6            14698       2016-04-25   -7.74                                    BOOSTERJUICE-19               Restaurants/Dining            [juice]
7            14698       2016-04-25  -28.49                                    LEISURE-FIRST O                    Uncategorized                 NA
8            14698       2016-04-22   -3.16                                    MCDONALD'S #400               Restaurants/Dining       [mcdonald's]
9            14698       2016-04-22   -0.50  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings      [simply save]
10           14698       2016-04-22  -10.50                                     SUBWAY # x6664               Restaurants/Dining           [subway]
11           14698       2016-04-21  -19.87                                     TRAFALGAR ESSO                    Gasoline/Fuel             [esso]
12           14698       2016-04-21   -1.00  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings      [simply save]
13           14698       2016-04-20   -3.76                                    MCDONALD'S #400               Restaurants/Dining       [mcdonald's]
14           14698       2016-04-20   -1.00  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings      [simply save]
15           14698       2016-04-20  -40.00                                     TRAFALGAR ESSO                    Gasoline/Fuel             [esso]
16           14698       2016-04-19  -10.07                                     TRAFALGAR ESSO                    Gasoline/Fuel             [esso]
17           14698       2016-04-19   -5.21                                    TIM HORTONS #24               Restaurants/Dining  [tim hortons, rt]
18           14698       2016-04-19   -3.50  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings      [simply save]
19           14698       2016-04-18   -1.00  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings      [simply save]
20           14698       2016-04-18   -5.21                                    TIM HORTONS #24               Restaurants/Dining  [tim hortons, rt]
21           14698       2016-04-18  -22.57                                     WAL-MART #3170              General Merchandise               [rt]
22           14698       2016-04-18  -16.94                                    URBAN PLANET #1                   Clothing/Shoes     [urban planet]
23           14698       2016-04-18  -12.95                                     LCBO/RAO #0545               Restaurants/Dining                 NA
24           14698       2016-04-18  -13.87                                     TRAFALGAR ESSO                    Gasoline/Fuel             [esso]
25           14698       2016-04-18  -41.75                                     NON-TD ATM W/D             ATM/Cash Withdrawals                 NA
26           14698       2016-04-18   -4.19                                     SUBWAY # x6338               Restaurants/Dining           [subway]
27           14698       2016-04-15   -0.50  Simply Save TD EVERY DAY SAVINGS ACCOUNT xxxxx...                          Savings      [simply save]
28           14698       2016-04-15  -35.06                                       UNION BURGER               Restaurants/Dining           [burger]
29           14698       2016-04-15  -25.00                                     PIONEER STN #1                      Electronics          [pioneer]
你可以构建一个aho-corasick自动机，大大提高搜索速度。 - user2722968
2个回答
1
我会做两件事情：
1. 由于您只使用“description”列，请尝试将其导出为列表 df.description.tolist()。使用此列表进行字符串处理，然后可以使用 pd.concat 连接结果。我相信这可以消除 pandas 的开销。已知 Numpy 数组在优化方面更胜一筹，但我不确定在字符串操作方面是否也是如此。但您也可以尝试一下。
2. 并行化您的代码。joblib 提供了一个极好的易用界面。(https://pythonhosted.org/joblib/parallel.html)
1
你可以尝试这样做：

df['match'] = df['description type_codes'].apply(lambda x: [l  for l in match_list if l.lower() in x.lower()])

使用pandas.map和列表推导式总是比显式循环和迭代更快。

如果你不喜欢在没有匹配的地方使用[]，你可以使用以下方法将它们更改为np.nan或其他你喜欢的值：

df['match'] = df.match.apply(lambda y: np.nan if len(y)==0 else y)

如果您想了解有关使用pandas提高性能的更多信息，您可以访问以下链接：

主题

文档

输出：

# only the interesting column

0         [simply save]
1              [rogers]
2              [subway]
3         [simply save]
4                   NaN
5                   NaN
6               [juice]
7                   NaN
8          [mcdonald's]
9         [simply save]
10             [subway]
11               [esso]
12        [simply save]
13         [mcdonald's]
14        [simply save]
15               [esso]
16               [esso]
17    [tim hortons, rt]
18        [simply save]
19        [simply save]
20    [tim hortons, rt]
21                 [rt]
22       [urban planet]
23                  NaN
24               [esso]
25                  NaN
26             [subway]
27        [simply save]
28             [burger]
29            [pioneer]

希望这对你有所帮助。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接