问题:我想根据“描述”列适当地对数据框中的每一行进行分类。 为此,我想基于常见单词列表提取关键字。 首先,我将关键短语拆分为单词(例如,“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]