如何拆分DataFrame的REGION/CATEGORY列并生成Region和Category列

我有一个包含REGION/CATEGORY列的DataFrame，数据如下：

|REGION/CATEGORY|
|--|-|
|NORTHERN REGION|
|THERMAL|
|HYDRO|
|NUCLEAR|
|WESTERN REGION|
|THERMAL|
|HYDRO|
|NUCLEAR|
|SOUTHERN REGION|
|THERMAL|
|HYDRO|
|NUCLEAR|
|EASTERN REGION|
|THERMAL|
|HYDRO|
|NORTH EASTERN REGION|
|THERMAL|
|HYDRO|
|ALL INDIA REGION|
|THERMAL|
|HYDRO|
|NUCLEAR|

我希望将该列拆分为DataFrame中的两列，列名分别为Region和Category。已知：

REGION = ['NORTHERN REGION','WESTERN REGION','SOUTHERN REGION','EASTERN REGION','NORTH EASTERN REGION']
CATEGORY = ['THERMAL','NUCLEAR','HYDRO']

期望输出示例（完整输出需包含所有区域对应类别）：

我尝试了以下代码但未成功：

for df['REGION'] in df:
    if df['REGION'] == 'REGION':
        df['REGION'] = df['REGION'].append('REGION')
    elif df['CATEGORY'] == CATEGORY:
            df['CATEGORY'] = df['CATEGORY'].append('CATEGORY')

请问正确的实现方法是什么？

核心逻辑是先标记区域行并向下传递区域值，再筛选出类别行，最终整理成目标结构。

方法一：用Pandas内置方法高效处理 #

import pandas as pd

# 定义已知列表
REGION = ['NORTHERN REGION','WESTERN REGION','SOUTHERN REGION','EASTERN REGION','NORTH EASTERN REGION']
CATEGORY = ['THERMAL','NUCLEAR','HYDRO']

# 构造示例DataFrame
data = {'REGION/CATEGORY': [
    'NORTHERN REGION', 'THERMAL', 'HYDRO', 'NUCLEAR',
    'WESTERN REGION', 'THERMAL', 'HYDRO', 'NUCLEAR',
    'SOUTHERN REGION', 'THERMAL', 'HYDRO', 'NUCLEAR',
    'EASTERN REGION', 'THERMAL', 'HYDRO',
    'NORTH EASTERN REGION', 'THERMAL', 'HYDRO',
    'ALL INDIA REGION', 'THERMAL', 'HYDRO', 'NUCLEAR'
]}
df = pd.DataFrame(data)

# 1. 标记并填充区域值：筛选区域行，用ffill让下方类别行继承最近的区域
df['Region'] = df['REGION/CATEGORY'].where(df['REGION/CATEGORY'].isin(REGION + ['ALL INDIA REGION']))
df['Region'] = df['Region'].ffill()

# 2. 提取类别值：只保留属于类别列表的内容
df['Category'] = df['REGION/CATEGORY'].where(df['REGION/CATEGORY'].isin(CATEGORY))

# 3. 过滤无效行，重置索引并添加id列
result = df.dropna(subset=['Category']).reset_index(drop=True)
result['id'] = range(11, 11 + len(result))

# 调整列顺序
result = result[['id', 'Region', 'Category']]
print(result)

方法二：循环遍历（适合理解基础逻辑） #

如果想手动控制遍历过程，可以用循环构建结果列表：

import pandas as pd

REGION = ['NORTHERN REGION','WESTERN REGION','SOUTHERN REGION','EASTERN REGION','NORTH EASTERN REGION']
CATEGORY = ['THERMAL','NUCLEAR','HYDRO']

data = {'REGION/CATEGORY': [
    'NORTHERN REGION', 'THERMAL', 'HYDRO', 'NUCLEAR',
    'WESTERN REGION', 'THERMAL', 'HYDRO', 'NUCLEAR',
    'SOUTHERN REGION', 'THERMAL', 'HYDRO', 'NUCLEAR',
    'EASTERN REGION', 'THERMAL', 'HYDRO',
    'NORTH EASTERN REGION', 'THERMAL', 'HYDRO',
    'ALL INDIA REGION', 'THERMAL', 'HYDRO', 'NUCLEAR'
]}
df = pd.DataFrame(data)

current_region = None
result_list = []

for item in df['REGION/CATEGORY']:
    # 遇到区域则更新当前区域
    if item in REGION or item == 'ALL INDIA REGION':
        current_region = item
    # 遇到类别则添加到结果列表
    elif item in CATEGORY:
        result_list.append({'Region': current_region, 'Category': item})

# 转换为DataFrame并添加id
result = pd.DataFrame(result_list)
result['id'] = range(11, 11 + len(result))
result = result[['id', 'Region', 'Category']]
print(result)

原代码失败原因 #

1. 循环语法错误：for df['REGION'] in df不符合Pandas的迭代逻辑，无法正确遍历每行数据。
2. 条件判断错误：df['REGION'] == 'REGION'是将整列与字符串比较，不是判断当前值是否属于区域列表。
3. 数据操作错误：append方法的使用完全错误，Pandas中修改列值需针对行或元素操作，不能直接对列调用append。

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