如何自动整理DataFrame拆分后列中混乱的房产属性值？

问题背景 #

需要将DataFrame的Description列拆分为Type、Stories、Bedrooms、Bathrooms四列，该列多数条目格式为Type: Detached; Style: 2-Story; 3 Bedrooms; 2 Bathrooms，但部分条目顺序混乱（如1 Bathroom; 2 Bedrooms; Type: Bungalow; Style: 1-Story;）。

原使用.str.split(';', expand=True)的拆分方式无法处理无序条目，手动逐行调整的代码（如下）不适用于950+行的数据集，需要自动规则实现批量处理。

原拆分代码 #

df1[['Type','Stories','Bedrooms','Bathrooms']] = df1['Description'].str.split(';', expand=True)
df1 = df1.drop('Description', axis=1)

原手动调整示例 #

m = df1['Type'] == '2 Bathrooms'
mp = {'Type': 'Bathrooms', 'Bathrooms': 'Type'}
df1.update(df1.loc[m].rename(mp, axis=1))
df1

数据集样例 #

{'Date': {0: '2016-01-07', 1: '2016-01-07', 2: '2016-01-10', 3: '2016-01-10', 4: '2016-01-10'}, 'Price(€)': {0: 638740.0, 1: 541330.0, 2: 376039.0, 3: 546446.0, 4: 494491.0}, 'Location': {0: 'Brookville', 1: 'Brookville', 2: 'West End', 3: 'West End', 4: 'West End'}, 'Year Built': {0: 2011, 1: 2019, 2: 1964, 3: 2013, 4: 2004}, 'Size(sq ft)': {0: 1839, 1: 1551, 2: 1073, 3: 1216, 4: 1687}, 'Description': {0: 'Type: Detached; Style: 2-Story; 3 Bedrooms; 2 Bathrooms', 1: 'Type: Detached; Style: 1-Story; 3 Bedrooms; 2 Bathrooms', 2: 'Type: Terraced; Style: 1-Story; 3 Bedrooms; 1 Bathroom', 3: 'Type: Detached; Style: 1.5-Story; 2 Bedrooms; 2 Bathrooms', 4: 'Type: Detached; Style: 2-Story; 3 Bedrooms; 2 Bathrooms'}}

最优解决方案 #

方案1：正则表达式提取（推荐） #

直接通过正则匹配每个字段的内容，完全忽略条目内的顺序，代码简洁高效：

import pandas as pd
import re

# 加载数据集（示例）
data = {'Date': {0: '2016-01-07', 1: '2016-01-07', 2: '2016-01-10', 3: '2016-01-10', 4: '2016-01-10'}, 'Price(€)': {0: 638740.0, 1: 541330.0, 2: 376039.0, 3: 546446.0, 4: 494491.0}, 'Location': {0: 'Brookville', 1: 'Brookville', 2: 'West End', 3: 'West End', 4: 'West End'}, 'Year Built': {0: 2011, 1: 2019, 2: 1964, 3: 2013, 4: 2004}, 'Size(sq ft)': {0: 1839, 1: 1551, 2: 1073, 3: 1216, 4: 1687}, 'Description': {0: 'Type: Detached; Style: 2-Story; 3 Bedrooms; 2 Bathrooms', 1: 'Type: Detached; Style: 1-Story; 3 Bedrooms; 2 Bathrooms', 2: 'Type: Terraced; Style: 1-Story; 3 Bedrooms; 1 Bathroom', 3: 'Type: Detached; Style: 1.5-Story; 2 Bedrooms; 2 Bathrooms', 4: 'Type: Detached; Style: 2-Story; 3 Bedrooms; 2 Bathrooms'}}
df1 = pd.DataFrame(data)

# 添加一条无序测试数据验证效果
df1.loc[5] = ['2016-01-11', 500000.0, 'Downtown', 2000, 1400, '1 Bathroom; 2 Bedrooms;  Type: Bungalow; Style: 1-Story;']

# 正则提取各字段
df1['Type'] = df1['Description'].str.extract(r'Type:\s*(.*?)(?=;|$)', flags=re.IGNORECASE).str.strip()
df1['Stories'] = df1['Description'].str.extract(r'Style:\s*(.*?)(?=;|$)', flags=re.IGNORECASE).str.strip()
df1['Bedrooms'] = df1['Description'].str.extract(r'(\d+)\s*Bedroom(s?)', flags=re.IGNORECASE).iloc[:,0].str.strip()
df1['Bathrooms'] = df1['Description'].str.extract(r'(\d+)\s*Bathroom(s?)', flags=re.IGNORECASE).iloc[:,0].str.strip()

# 可选：将卧室、浴室数量转为数值类型
df1[['Bedrooms', 'Bathrooms']] = df1[['Bedrooms', 'Bathrooms']].astype(float)

# 删除原Description列
df1 = df1.drop('Description', axis=1)

print(df1)

关键说明：

• r'Type:\s*(.*?)(?=;|$)'：匹配Type:后的内容，直到分号或行尾，\s*处理多余空格，(?=;|$)避免捕获分号
• flags=re.IGNORECASE：忽略大小写，兼容type:/STYLE:等格式
• 卧室/浴室直接提取数字部分，不管位置，完美适配无序条目

方案2：键值对解析（更灵活） #

如果需要更定制化的逻辑，可以将每个Description拆分为键值对后匹配字段：

import pandas as pd
import re

def parse_description(desc):
    # 拆分条目并清理空内容
    items = [item.strip() for item in desc.split(';') if item.strip()]
    result = {'Type': None, 'Stories': None, 'Bedrooms': None, 'Bathrooms': None}
    
    for item in items:
        if ':' in item:
            # 处理带冒号的条目（Type/Style）
            key, value = item.split(':', 1)
            key = key.strip().lower()
            value = value.strip()
            if key == 'type':
                result['Type'] = value
            elif key == 'style':
                result['Stories'] = value
        else:
            # 处理不带冒号的条目（Bedrooms/Bathrooms）
            if 'bedroom' in item.lower():
                result['Bedrooms'] = re.search(r'\d+', item).group()
            elif 'bathroom' in item.lower():
                result['Bathrooms'] = re.search(r'\d+', item).group()
    return pd.Series(result)

# 加载数据集（示例）
data = {'Date': {0: '2016-01-07', 1: '2016-01-07', 2: '2016-01-10', 3: '2016-01-10', 4: '2016-01-10'}, 'Price(€)': {0: 638740.0, 1: 541330.0, 2: 376039.0, 3: 546446.0, 4: 494491.0}, 'Location': {0: 'Brookville', 1: 'Brookville', 2: 'West End', 3: 'West End', 4: 'West End'}, 'Year Built': {0: 2011, 1: 2019, 2: 1964, 3: 2013, 4: 2004}, 'Size(sq ft)': {0: 1839, 1: 1551, 2: 1073, 3: 1216, 4: 1687}, 'Description': {0: 'Type: Detached; Style: 2-Story; 3 Bedrooms; 2 Bathrooms', 1: 'Type: Detached; Style: 1-Story; 3 Bedrooms; 2 Bathrooms', 2: 'Type: Terraced; Style: 1-Story; 3 Bedrooms; 1 Bathroom', 3: 'Type: Detached; Style: 1.5-Story; 2 Bedrooms; 2 Bathrooms', 4: 'Type: Detached; Style: 2-Story; 3 Bedrooms; 2 Bathrooms'}}
df1 = pd.DataFrame(data)

# 添加无序测试数据
df1.loc[5] = ['2016-01-11', 500000.0, 'Downtown', 2000, 1400, '1 Bathroom; 2 Bedrooms;  Type: Bungalow; Style: 1-Story;']

# 解析Description列并合并到原DataFrame
new_cols = df1['Description'].apply(parse_description)
df1 = pd.concat([df1.drop('Description', axis=1), new_cols], axis=1)

print(df1)

关键说明：

• 先拆分每个条目，区分带冒号和不带冒号的类型
• 统一转为小写匹配，避免格式差异
• 可灵活扩展逻辑，比如处理异常值或新字段

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