我有下表，想將每一行分成三列：州、郵政編碼和城市。州和郵政編碼很簡單，但我無法提取城市。我想過在街道同義詞之后和狀態之前拆分每個字符串，但我似乎弄錯了循環，因為它只會使用我列表中的最后一項。輸入數據： Address Text0 11 North Warren Circle Lisbon Falls ME 042521 227 Cony Street Augusta ME 043302 70 Buckner Drive Battle Creek MI3 718 Perry Street Big Rapids MI4 14857 Martinsville Road Van Buren MI5 823 Woodlawn Ave Dallas TX 752086 2525 Washington Avenue Waco TX 767107 123 South Main St Dallas TX 75201我試圖實現的輸出（對于所有行，但我只寫了前兩個以節省時間） City State Postcode 0 Lisbon Falls ME 042521 Augusta ME 04330我的代碼：# Extract postcode and statedf["Zip"] = df["Address Text"].str.extract(r'(\d{5})', expand = True)df["State"] = df["Address Text"].str.extract(r'([A-Z]{2})', expand = True)# Split after these substringsstreet_synonyms = ["Circle", "Street", "Drive", "Road", "Ave", "Avenue", "St"]# This is where I got stuckdf["Syn"] = df["Address Text"].apply(lambda x: x.split(syn))df
1 回答
30秒到達戰場
TA貢獻1828條經驗 獲得超6個贊
這是一種方法：
import pandas as pd
# data
df = pd.DataFrame(
['11 North Warren Circle Lisbon Falls ME 04252',
'227 Cony Street Augusta ME 04330',
'70 Buckner Drive Battle Creek MI',
'718 Perry Street Big Rapids MI',
'14857 Martinsville Road Van Buren MI',
'823 Woodlawn Ave Dallas TX 75208',
'2525 Washington Avenue Waco TX 76710',
'123 South Main St Dallas TX 75201'],
columns=['Address Text'])
# Extract postcode and state
df["Zip"] = df["Address Text"].str.extract(r'(\d{5})', expand=True)
df["State"] = df["Address Text"].str.extract(r'([A-Z]{2})', expand=True)
# Split after these substrings
street_synonyms = ["Circle", "Street", "Drive", "Road", "Ave", "Avenue", "St"]
def find_city(address, state, street_synonyms):
for syn in street_synonyms:
if syn in address:
# remove street
city = address.split(syn)[-1]
# remove State and postcode
city = city.split(state)[0]
return city
df['City'] = df.apply(lambda x: find_city(x['Address Text'], x['State'], street_synonyms), axis=1)
print(df[['City', 'State', 'Zip']])
"""
City State Zip
0 Lisbon Falls ME 04252
1 Augusta ME 04330
2 Battle Creek MI NaN
3 Big Rapids MI NaN
4 Van Buren MI 14857
5 Dallas TX 75208
6 nue Waco TX 76710
7 Dallas TX 75201
"""
添加回答
舉報
0/150
提交
取消