python - 将列表与列匹配并从列中提取匹配值

Question

我在匹配数据框的列表和列时遇到问题，并从匹配中提取列中的特定匹配值。

数据集:

    address
0   58 Chatham Street, Chatham, New Jersey, 07928
1   3420 W. MacArthur Blvd. Ste. C, Santa Ana, California
2   2016 Chalk Rd, Wake Forest, North Carolina, 27587

我有一个包含州名的列表

state = ['New York','New Jersey','California',...]

渴望结果

    address                                                   State
0   58 Chatham Street, Chatham, New Jersey, 07928             New Jersey
1   3420 W. MacArthur Blvd. Ste. C, Santa Ana, California     California
2   2016 Chalk Rd, Wake Forest, North Carolina, 27587         North Carolina

我试过的代码

for i in state:
    ship_add['state'] = ship_add['address'].str.strip(i)

Answer 1

使用:

state = ['New York','New Jersey','California','North Carolina']

#word boundary
pat = '|'.join(r"\b{}\b".format(x) for x in state)
#if not necessary words boundary
#pat = '|'.join(state)
df['State'] = df['address'].str.extract('('+ pat + ')', expand=False)
print (df)
                                             address           State
0      58 Chatham Street, Chatham, New Jersey, 07928      New Jersey
1  3420 W. MacArthur Blvd. Ste. C, Santa Ana, Cal...      California
2  2016 Chalk Rd, Wake Forest, North Carolina, 27587  North Carolina

如果匹配拆分值:

state = ['New York','New Jersey','California','North Carolina']

df1 = df['address'].str.split(', ', expand=True)
df['State'] = df1.where(df1.isin(state)).ffill(1).iloc[:, -1]
print (df)
                                             address           State
0      58 Chatham Street, Chatham, New Jersey, 07928      New Jersey
1  3420 W. MacArthur Blvd. Ste. C, Santa Ana, Cal...      California
2  2016 Chalk Rd, Wake Forest, North Carolina, 27587  North Carolina