Pandas：在500万行上使用Apply和regex字符串匹配

Question

问题：我正在尝试根据description列对数据帧的每一行进行适当的分类。为了做到这一点，我想根据常用词的列表提取关键字。首先，我把关键短语分成词（即‘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

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

^{pr2}$

我的解决方案：

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]

Answer 1

你可以试试这样的方法：

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

{a1总是更快速地使用循环。在

如果您不喜欢[]在没有匹配项的地方，可以使用此项将它们更改为np.nan或任何您喜欢的：

^{pr2}$

有关使用pandas提升性能的更多信息，请访问以下链接：

topic

document

输出：

# 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]

希望这对你有帮助。在

Answer 2

我会做两件事：

1. 由于您只使用'description'列，请尝试将其导出为列表df.description.tolist()。使用此列表处理字符串，然后您可以pd.concat您的结果。我相信这可以消除pandas的开销。 Numpy数组被认为是更优化的，但是，我不太确定字符串操作是否真的是这样。但你也可以试试看。

2. 并行化你的代码。joblib提供了一个非常简单的界面。（https://pythonhosted.org/joblib/parallel.html）