我需要在数据框文本列中搜索国家名称或首都名称,然后将匹配结果保存在新列中。我当前的解决方案有效但需要很长时间。我想知道是否有可能使它更有效,最好是以矢量化的方式。
国家和首都列表存储在单独的 countries 数据框中。
我的主数据框df:
date text
0 2016-01-01 Bla bla bla bla
1 2016-01-01 Blu blu Nigeria
2 2016-01-01 Hey ho Norway
3 2016-01-01 This is text Paris
4 2016-01-01 Lorem lorem ipsum
countries 数据框:
name capital
0 France Paris
1 Germany Berlin
2 Norway Oslo
3 China Beijing
我目前的解决方案:
def extract_countries(row):
matches = []
for country, adj in countries[['name', 'capital']].values:
if any([country in row.text, adj in row.text]):
matches.append(country)
return ', '.join(matches)
df['countries'] = df.apply(extract_countries, axis=1)
期望的结果:
date text countries
0 2016-01-01 Bla bla bla bla NaN
1 2016-01-01 Blu blu Nigeria Nigeria
2 2016-01-01 Hey ho Norway Norway
3 2016-01-01 This is text Paris France
4 2016-01-01 Lorem lorem ipsum NaN
5 2016-01-01 Germany attacked Benin Germany, Benin
请您参考如下方法:
这是一种方法。注意 NaN(“不是数字”)不适用于字符串列,因此我在未找到匹配项的地方留下空字符串。
import pandas as pd
df = pd.DataFrame([['2016-01-01', 'Bla bla bla bla'], ['2016-01-01', 'Blu blu Nigeria'],
['2016-01-01', 'Hey ho Norway'], ['2016-01-01', 'This is text Paris'],
['2016-01-01', 'Lorem lorem ipsum']], columns=['date', 'text'])
countries = pd.DataFrame([['France', 'Paris'], ['Germany', 'Berlin'], ['Norway', 'Oslo'],
['China', 'Beijing']], columns=['name', 'capital'])
ctry_set = set(countries.name)
cap_set = set(countries.capital)
df['countries'] = df['text'].apply(lambda x: ', '.join(i for i in ctry_set if i in x))
df['capitals'] = df['text'].apply(lambda x: ', '.join(i for i in cap_set if i in x))
# date text countries capitals
# 0 2016-01-01 Bla bla bla bla
# 1 2016-01-01 Blu blu Nigeria
# 2 2016-01-01 Hey ho Norway Norway
# 3 2016-01-01 This is text Paris Paris
# 4 2016-01-01 Lorem lorem ipsum
