Pandas中fillna后pivot再replace失效问题求助

问题核心原因 #

你遇到的无效替换本质是占位符填充的位置错误：

• 你用fillna('yy')填充了整个DataFrame的所有NaN，但pivot操作的values参数指定的是Reading列——你的示例数据中Reading列本身没有NaN，pivot后产生的NaN是因为部分行组合没有对应的Reading值，并非来自你填充的'yy'占位符。
• 你填充的'yy'都在SampledBy、LabID、Overflow这些作为pivot索引的列里，这些值会成为多级索引的一部分，不会出现在pivot后的数值列中，因此后续replace('yy', np.nan)找不到目标值。

解决方案 #

方案1：无需提前填充占位符（推荐） #

直接执行pivot操作，pivot后自动生成的NaN就是缺失值，可直接保留或按需处理：

import pandas as pd
import numpy as np

# 加载数据
data_as_dict ={'SiteID': {0: 'Somewhere Creek D/S',  1: 'Somewhere Creek D/S',  2: 'Somewhere Creek D/S',  3: 'Somewhere Creek D/S',  4: 'Somewhere Creek D/S',  5: 'Somewhere Creek D/S',  6: 'Somewhere Creek D/S',  7: 'Somewhere Creek D/S',  8: 'Somewhere Creek D/S'}, 'ParameterID': {0: 'EW_APHA1030E.IONBAL',  1: 'EW_APHA1030E.IONBAL',  2: 'EW_APHA1030E.SUM_OF_IONS',  3: 'EW_APHA1030E.SUM_OF_IONS',  4: 'EW_APHA1030E.TFSS',  5: 'EW_APHA2120C_UV.COLOUR_TRUE',  6: 'EW_APHA2130.TURB_BEFORE',  7: 'EW_APHA2320.ALK_BICAR',  8: 'EW_APHA2320.ALK_BICAR'}, 'SampleDate': {0: '2017-04-03 09:30:00',  1: '2019-04-17 13:30:00',  2: '2017-04-03 09:30:00',  3: '2017-04-03 09:30:01',  4: '2017-04-03 09:30:00',  5: '2017-04-03 09:30:00',  6: '2017-04-03 09:30:00',  7: '2017-04-03 09:30:00',  8: '2019-04-17 13:30:00'}, 'Reading': {0: 15.0,  1: -0.7,  2: 278.0,  3: 975.0,  4: 278.0,  5: 35.0,  6: 20.0,  7: 98.0,  8: 230.0}, 'SampledBy': {0: 'dafdsfd',  1: np.nan,  2: 'dafdsfd',  3: np.nan,  4: 'dafdsfd',  5: 'dafdsfd',  6: 'dafdsfd',  7: 'dafdsfd',  8: np.nan}, 'LabID': {0: 'dagfdfda',  1: np.nan,  2: 'dagfdfda',  3: np.nan,  4: 'dagfdfda',  5: 'dagfdfda',  6: 'dagfdfda',  7: 'dagfdfda',  8: np.nan}, 'Overflow': {0: np.nan,  1: np.nan,  2: np.nan,  3: np.nan,  4: np.nan,  5: np.nan,  6: np.nan,  7: np.nan,  8: np.nan}, 'Symbol': {0: '%',  1: '%',  2: 'mg/L',  3: 'mg/L',  4: 'mg/L',  5: 'Hazen',  6: 'NTU',  7: 'mg/L',  8: 'mg/L'}, 'Description': {0: 'Anion-Cation Balance',  1: 'Anion-Cation Balance',  2: 'Sum of Ions',  3: 'Sum of Ions',  4: 'TFSS',  5: 'Colour (True)',  6: 'Turbidity',  7: 'Bicarbonate Alkalinity as CaCO3',  8: 'Bicarbonate Alkalinity as CaCO3'}, 'Parameter': {0: 'Anion-Cation Balance %',  1: 'Anion-Cation Balance %',  2: 'Sum of Ions mg/L',  3: 'Sum of Ions mg/L',  4: 'TFSS mg/L',  5: 'Colour (True) Hazen',  6: 'Turbidity NTU',  7: 'Bicarbonate Alkalinity as CaCO3 mg/L',  8: 'Bicarbonate Alkalinity as CaCO3 mg/L'}}

df = pd.DataFrame.from_dict(data_as_dict)

# 直接执行pivot，无需提前填充占位符
dsf = df.pivot(
    index=['SiteID', 'ParameterID', 'SampleDate', 'SampledBy', 'LabID', 'Overflow', 'Symbol', 'Description'],
    columns='Parameter',
    values='Reading'
)

# 检查缺失值情况
print("检查NaN值情况：")
print(dsf.isna().any().any())

方案2：仅填充指定列的NaN并后续处理索引 #

如果必须对非Reading列填充占位符，可仅填充目标列，之后再处理索引中的占位符：

import pandas as pd
import numpy as np

# 加载数据
data_as_dict ={'SiteID': {0: 'Somewhere Creek D/S',  1: 'Somewhere Creek D/S',  2: 'Somewhere Creek D/S',  3: 'Somewhere Creek D/S',  4: 'Somewhere Creek D/S',  5: 'Somewhere Creek D/S',  6: 'Somewhere Creek D/S',  7: 'Somewhere Creek D/S',  8: 'Somewhere Creek D/S'}, 'ParameterID': {0: 'EW_APHA1030E.IONBAL',  1: 'EW_APHA1030E.IONBAL',  2: 'EW_APHA1030E.SUM_OF_IONS',  3: 'EW_APHA1030E.SUM_OF_IONS',  4: 'EW_APHA1030E.TFSS',  5: 'EW_APHA2120C_UV.COLOUR_TRUE',  6: 'EW_APHA2130.TURB_BEFORE',  7: 'EW_APHA2320.ALK_BICAR',  8: 'EW_APHA2320.ALK_BICAR'}, 'SampleDate': {0: '2017-04-03 09:30:00',  1: '2019-04-17 13:30:00',  2: '2017-04-03 09:30:00',  3: '2017-04-03 09:30:01',  4: '2017-04-03 09:30:00',  5: '2017-04-03 09:30:00',  6: '2017-04-03 09:30:00',  7: '2017-04-03 09:30:00',  8: '2019-04-17 13:30:00'}, 'Reading': {0: 15.0,  1: -0.7,  2: 278.0,  3: 975.0,  4: 278.0,  5: 35.0,  6: 20.0,  7: 98.0,  8: 230.0}, 'SampledBy': {0: 'dafdsfd',  1: np.nan,  2: 'dafdsfd',  3: np.nan,  4: 'dafdsfd',  5: 'dafdsfd',  6: 'dafdsfd',  7: 'dafdsfd',  8: np.nan}, 'LabID': {0: 'dagfdfda',  1: np.nan,  2: 'dagfdfda',  3: np.nan,  4: 'dagfdfda',  5: 'dagfdfda',  6: 'dagfdfda',  7: 'dagfdfda',  8: np.nan}, 'Overflow': {0: np.nan,  1: np.nan,  2: np.nan,  3: np.nan,  4: np.nan,  5: np.nan,  6: np.nan,  7: np.nan,  8: np.nan}, 'Symbol': {0: '%',  1: '%',  2: 'mg/L',  3: 'mg/L',  4: 'mg/L',  5: 'Hazen',  6: 'NTU',  7: 'mg/L',  8: 'mg/L'}, 'Description': {0: 'Anion-Cation Balance',  1: 'Anion-Cation Balance',  2: 'Sum of Ions',  3: 'Sum of Ions',  4: 'TFSS',  5: 'Colour (True)',  6: 'Turbidity',  7: 'Bicarbonate Alkalinity as CaCO3',  8: 'Bicarbonate Alkalinity as CaCO3'}, 'Parameter': {0: 'Anion-Cation Balance %',  1: 'Anion-Cation Balance %',  2: 'Sum of Ions mg/L',  3: 'Sum of Ions mg/L',  4: 'TFSS mg/L',  5: 'Colour (True) Hazen',  6: 'Turbidity NTU',  7: 'Bicarbonate Alkalinity as CaCO3 mg/L',  8: 'Bicarbonate Alkalinity as CaCO3 mg/L'}}

df = pd.DataFrame.from_dict(data_as_dict)

# 仅填充非Reading列的NaN
df[['SampledBy', 'LabID', 'Overflow']] = df[['SampledBy', 'LabID', 'Overflow']].fillna('yy')

# 执行pivot
dsf = df.pivot(
    index=['SiteID', 'ParameterID', 'SampleDate', 'SampledBy', 'LabID', 'Overflow', 'Symbol', 'Description'],
    columns='Parameter',
    values='Reading'
)

# 将索引中的'yy'转回NaN
dsf = dsf.reset_index()
dsf[['SampledBy', 'LabID', 'Overflow']] = dsf[['SampledBy', 'LabID', 'Overflow']].replace('yy', np.nan)
dsf = dsf.set_index(['SiteID', 'ParameterID', 'SampleDate', 'SampledBy', 'LabID', 'Overflow', 'Symbol', 'Description'])

# 检查占位符是否已替换
print("检查'yy'值是否存在：")
print((dsf.index.get_level_values('SampledBy') == 'yy').any())

内容的提问来源于Stack Exchange，提问作者flashliquid