如何在DataFrame中按条件创建新列？遇KeyError问题求助

问题描述 #

我一直尝试在DataFrame中基于条件创建新列，但持续出现KeyError，怀疑该问题与索引或表头有关，却陷入瓶颈。
我的需求是生成一个名为Gas/Coal的新列：当Technology列的值为COAL时显示coal，其余（CCGT、OCGT）显示gas。我编写了相关逻辑但未生效。

期望的DataFrame样式 #

|                | settlementDate | 03/01/2022 | 04/01/2022 | 05/01/2022 | 06/01/2022 | 07/01/2022 | 08/01/2022 |          |
|----------------|---------------:|-----------:|-----------:|-----------:|-----------:|-----------:|-----------:|----------|
| BM Unit ID   1 |     Technology |            |            |            |            |            |            | Gas/Coal |
|         CARR-2 |           CCGT |         24 |         25 |         27 |         29 |         21 |         12 | Gas      |
|         CDCL-1 |           CCGT |         13 |         22 |          8 |         25 |         12 |         16 | Gas      |
|        CNQPS-1 |           CCGT |         25 |        NaN |         33 |        NaN |        NaN |        NaN | Gas      |
|        CNQPS-2 |           CCGT |         20 |        NaN |         32 |        NaN |         32 |         26 | Gas      |
|        CNQPS-4 |           CCGT |         27 |        NaN |         41 |         33 |         35 |         31 | Gas      |
|         DAMC-1 |           CCGT |         23 |         21 |         38 |         34 |        NaN |        NaN | Gas      |
|         DIDCB6 |           CCGT |         28 |         11 |         11 |          8 |         19 |         15 | Gas      |
|         EECL-1 |           CCGT |          5 |         29 |          4 |          1 |          1 |          2 | Gas      |
|         FAWN-1 |           CCGT |          1 |          5 |          1 |          5 |          2 |          1 | Gas      |
|         FELL-1 |           CCGT |         34 |         39 |         47 |         43 |         41 |         34 | Gas      |
|         GRAI-6 |           CCGT |          4 |         18 |          2 |          4 |          3 |          3 | Gas      |
|         GRAI-7 |           CCGT |         10 |         27 |         15 |          9 |         15 |         11 | Gas      |
|         GRAI-8 |           CCGT |         11 |         28 |         36 |         16 |         26 |         25 | Gas      |
|         GRMO-1 |           CCGT |          2 |          7 |         10 |         24 |         11 |          6 | Gas      |
|         HUMR-1 |           CCGT |         22 |         30 |         37 |         37 |         33 |         28 | Gas      |
|         KEAD-2 |           CCGT |         30 |        NaN |        NaN |        NaN |        NaN |        NaN | Gas      |
|         LAGA-1 |           CCGT |         15 |         13 |         29 |         32 |         23 |         24 | Gas      |
|        PEMB-11 |           CCGT |         21 |          2 |          9 |         10 |          9 |         14 | Gas      |
|        PEMB-21 |           CCGT |          7 |          1 |          6 |         13 |          8 |          8 | Gas      |
|        PEMB-31 |           CCGT |         14 |          6 |         13 |          6 |          4 |          9 | Gas      |
|        PEMB-41 |           CCGT |          9 |          4 |          7 |          7 |         10 |         13 | Gas      |
|        PEMB-51 |           CCGT |          3 |          3 |          3 |         11 |         16 |        NaN | Gas      |
|         ROCK-1 |           CCGT |         31 |         34 |         42 |         38 |         38 |        NaN | Gas      |
|         SCCL-2 |           CCGT |         29 |        NaN |         16 |         28 |         25 |        NaN | Gas      |
|         SCCL-3 |           CCGT |         17 |         16 |         31 |          3 |         18 |         10 | Gas      |
|         SHBA-1 |           CCGT |         26 |         23 |         40 |         35 |         37 |        NaN | Gas      |
|         SHOS-1 |           CCGT |         16 |         15 |         28 |         15 |         29 |         27 | Gas      |
|         STAY-1 |           CCGT |         19 |         12 |          5 |         23 |          6 |          7 | Gas      |
|         STAY-2 |           CCGT |         18 |         20 |         18 |         21 |         24 |         20 | Gas      |
|         STAY-3 |           CCGT |          8 |         17 |         17 |         20 |         13 |         22 | Gas      |
|         STAY-4 |           CCGT |         12 |          8 |         20 |         18 |         14 |         23 | Gas      |
|        WBURB-1 |           CCGT |          6 |          9 |         22 |          2 |          7 |          5 | Gas      |
|       WBURB-41 |           COAL |         33 |         38 |         46 |         41 |         40 |         33 | coal     |
|       WBURB-43 |           COAL |         32 |         37 |         45 |         40 |         39 |         32 |          |
|         CARR-1 |           CCGT |        NaN |         33 |         26 |         27 |         22 |          4 |          |
|         DIDCB5 |           CCGT |        NaN |         10 |         35 |         22 |        NaN |        NaN |          |
|        FDUNT-1 |           OCGT |        NaN |         36 |         44 |        NaN |        NaN |        NaN |          |
|         GYAR-1 |           CCGT |        NaN |         26 |         14 |         17 |         20 |         21 |          |
|       KLYN-A-1 |           CCGT |        NaN |         24 |         12 |         19 |         27 |         29 |          |
|         LBAR-1 |           CCGT |        NaN |         19 |         25 |         31 |         28 |        NaN |          |
|         PETEM1 |           CCGT |        NaN |         35 |        NaN |        NaN |        NaN |        NaN |          |
|         SEAB-1 |           CCGT |        NaN |         32 |         34 |         36 |        NaN |         30 |          |
|         SEAB-2 |           CCGT |        NaN |         31 |         39 |         39 |         34 |        NaN |          |
|        WBURB-2 |           CCGT |        NaN |         14 |         21 |         12 |         31 |         18 |          |
|         COSO-1 |           CCGT |        NaN |        NaN |         30 |         42 |         36 |        NaN |          |
|         KEAD-1 |           CCGT |        NaN |        NaN |         43 |        NaN |        NaN |        NaN |          |
|         MRWD-1 |           CCGT |        NaN |        NaN |         19 |         26 |          5 |         19 |          |
|         SPLN-1 |           CCGT |        NaN |        NaN |         23 |         30 |         30 |        NaN |          |
|        WBURB-3 |           CCGT |        NaN |        NaN |         24 |         14 |         17 |         17 |          |

尝试的代码 #

rank_table_pivot['ave rank'] = rank_table_pivot.mean(axis=1)
rank_table_pivot['rank'] = rank_table_pivot['ave rank'].rank(method='dense', ascending=True)
rank_table_pivot.sort_values('ave rank')

def coal_gas(Technology):
    if row['Technology'] == 'COAL':
        return "coal"
    else:
        return "gas"
    
rank_table_pivot = rank_table_pivot['Technology'].apply(lambda Technology: map_Technology(Technology))

问题分析与解决方案 #

你当前代码存在几个明显问题：

1. 自定义函数coal_gas中使用了未定义的row变量，且参数为Technology但内部却去取row['Technology']，逻辑矛盾。
2. 调用apply时使用了未定义的map_Technology函数，应该是你自定义的coal_gas。
3. 直接将rank_table_pivot赋值为rank_table_pivot['Technology'].apply(...)，会把整个DataFrame替换成Technology列处理后的Series，丢失原有数据。

正确实现方式 #

推荐几种简单高效的方法：

方法1：使用numpy.where（最简洁）

import numpy as np

rank_table_pivot['Gas/Coal'] = np.where(rank_table_pivot['Technology'] == 'COAL', 'coal', 'gas')

方法2：使用apply（修正原有逻辑）

def coal_gas(row):
    if row['Technology'] == 'COAL':
        return 'coal'
    else:
        return 'gas'

# 对整个DataFrame按行调用apply
rank_table_pivot['Gas/Coal'] = rank_table_pivot.apply(coal_gas, axis=1)

方法3：使用map函数

tech_map = {'COAL': 'coal', 'CCGT': 'gas', 'OCGT': 'gas'}
rank_table_pivot['Gas/Coal'] = rank_table_pivot['Technology'].map(tech_map)

额外注意点 #

• 若出现KeyError，先确认Technology列确实存在，用print(rank_table_pivot.columns)查看列名，检查拼写或大小写是否一致。
• 排序代码rank_table_pivot.sort_values('ave rank')默认不修改原DataFrame，需加inplace=True或重新赋值：rank_table_pivot = rank_table_pivot.sort_values('ave rank')

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