如何在Pandas中拆分含转义换行符的地址列并补全字段

问题描述 #

现有如下Pandas数据集：

import pandas as pd

data = {'id':  ['001', '002', '003','004'],
        'address': ["William J. Clare\n290 Valley Dr.\nCasper, WY 82604\nUSA",
                    "1180 Shelard Tower\nMinneapolis, MN 55426\nUSA",
                    "William N. Barnard\n145 S. Durbin\nCasper, WY 82601\nUSA",
                    "215 S 11th ST"],
        'locality': [None, None, None,'Laramie'],
        'region': [None, None, None, 'WY'],
        'Zipcode': [None, None, None, '87656'],
        'Country': [None, None, None, 'US']
        }

df = pd.DataFrame(data)

地址列包含转义换行符\n，需要拆分该列，将拆分出的信息填充到locality、region、Zipcode、Country字段中，同时保留这些字段已有的非空值，最终得到如下格式的结果：

id  address           locality    region  Zipcode Country
001 290 Valley Dr.    Casper      WY      82604   USA
002 1180 Shelard Tower Minneapolis MN      55426   USA
003 145 S. Durbin     Casper      WY      82601   USA
004 215 S 11th ST     Laramie     WY      87656   US

要求结果保留在Pandas DataFrame中用于后续分析。

解决方案 #

1. 筛选需拆分的行 #

先定位locality为空的行，这类行需要从address中提取信息：

mask = df['locality'].isna()
split_df = df[mask].copy()

2. 拆分地址并提取各字段 #

对address列按\n拆分，逐一提取街道地址、地区、邮编、国家信息：

# 拆分地址为多列
split_address = split_df['address'].str.split('\n', expand=True)

# 更新address列为街道地址（拆分后的第2个元素）
split_df['address'] = split_address[1]

# 处理包含城市、州、邮编的行
region_info = split_address[2].str.split(', ', expand=True)
split_df['locality'] = region_info[0]

# 拆分州和邮编
region_zip = region_info[1].str.split(' ', expand=True)
split_df['region'] = region_zip[0]
split_df['Zipcode'] = region_zip[1]

# 提取国家信息
split_df['Country'] = split_address[3]

3. 合并结果 #

将处理后的行与原DataFrame中无需拆分的行合并，恢复原索引顺序：

result_df = pd.concat([split_df, df[~mask]]).sort_index()

完整代码 #

import pandas as pd

data = {'id':  ['001', '002', '003','004'],
        'address': ["William J. Clare\n290 Valley Dr.\nCasper, WY 82604\nUSA",
                    "1180 Shelard Tower\nMinneapolis, MN 55426\nUSA",
                    "William N. Barnard\n145 S. Durbin\nCasper, WY 82601\nUSA",
                    "215 S 11th ST"],
        'locality': [None, None, None,'Laramie'],
        'region': [None, None, None, 'WY'],
        'Zipcode': [None, None, None, '87656'],
        'Country': [None, None, None, 'US']
        }

df = pd.DataFrame(data)

# 筛选需拆分的行
mask = df['locality'].isna()
split_df = df[mask].copy()

# 拆分地址并提取字段
split_address = split_df['address'].str.split('\n', expand=True)
split_df['address'] = split_address[1]

region_info = split_address[2].str.split(', ', expand=True)
split_df['locality'] = region_info[0]

region_zip = region_info[1].str.split(' ', expand=True)
split_df['region'] = region_zip[0]
split_df['Zipcode'] = region_zip[1]

split_df['Country'] = split_address[3]

# 合并最终结果
result_df = pd.concat([split_df, df[~mask]]).sort_index()

# 查看结果
print(result_df)

运行结果 #

id               address     locality region Zipcode Country
0  001        290 Valley Dr.        Casper     WY   82604     USA
1  002  1180 Shelard Tower  Minneapolis     MN   55426     USA
2  003       145 S. Durbin        Casper     WY   82601     USA
3  004       215 S 11th ST       Laramie     WY   87656      US

内容的提问来源于stack exchange，提问作者Sushmitha