如何删除Pandas DataFrame中全为NaN的列？问题排查与解决

问题描述 #

通过Tkinter上传Excel文件生成DataFrame，其中T5列所有单元格显示为[nan]，但使用DFT.dropna(axis=1, how='all')或DFT.loc[:,DFT.notna().any(axis=0)]无法删除该列，DataFrame无变化。

DataFrame示例：

T1       T2      T3      T4     T5
0  [22.8]   [42.2]  [30.0]  [23.0]  [nan]
1  [26.4]   [56.1]  [36.7]  [25.8]  [nan]
2  [29.3]   [68.9]  [42.3]  [28.4]  [nan]
3  [32.1]   [79.7]  [47.6]  [31.3]  [nan]
4  [34.3]   [90.0]  [52.2]  [33.6]  [nan]
5  [36.1]   [99.1]  [55.8]  [35.4]  [nan]
6  [37.1]  [104.0]  [57.0]  [36.3]  [nan]
7  [37.8]  [107.0]  [58.2]  [37.2]  [nan]
8  [38.4]  [111.2]  [60.0]  [37.9]  [nan]
9   [nan]    [nan]   [nan]   [nan]  [nan]

最小可复现代码：

import tkinter.filedialog
import tkinter as tk
from tkinter import ttk
from tkmacosx import Button
import pandas as pd
import numpy as np

root = tk.Tk()
root.geometry('400x400')

label_check = tk.StringVar()

def OOE():        
   pathATC = tk.filedialog.askopenfilename(filetypes = [('Excel files', '*.xls*')], title = "Select an ATC file")  
   excel_file = pd.ExcelFile(pathATC)
   sheet_names = excel_file.sheet_names
   combo = tk.StringVar()
   def selected(event):
       print(box.get()) 
       PI_ATC = pd.read_excel(pathATC, sheet_name = box.get(),usecols="C",skiprows=8, nrows=10).to_numpy().astype(float)
       POUT_ATC = pd.read_excel(pathATC, sheet_name=box.get(),usecols="I",skiprows=8, nrows=10).to_numpy().astype(float)     
       PI_ATC[PI_ATC == 0] = 'nan'
       # POUT_ATC[POUT_ATC == 0] = 'nan'                   
       if 'Post Burn-in' in box.get() or 'Mesure 2' in box.get():
           TH1_Bi = pd.read_excel(pathATC, sheet_name=box.get(),usecols="L",skiprows=8, nrows=10).to_numpy().astype(float)
           TH2_Bi = pd.read_excel(pathATC, sheet_name=box.get(),usecols="M",skiprows=8, nrows=10).to_numpy().astype(float)
           TH3_Bi = pd.read_excel(pathATC, sheet_name=box.get(),usecols="N",skiprows=8, nrows=10).to_numpy().astype(float)
           TH4_Bi = pd.read_excel(pathATC, sheet_name=box.get(),usecols="O",skiprows=8, nrows=10).to_numpy().astype(float)
           TH5_Bi = pd.read_excel(pathATC, sheet_name=box.get(),usecols="P",skiprows=8, nrows=10).to_numpy().astype(float)
            
           dat = list(zip(TH1_Bi,TH2_Bi,TH3_Bi,TH4_Bi,TH5_Bi))
           DFT = pd.DataFrame(data=dat, columns = ['T1', 'T2', 'T3','T4', 'T5'])
           DFT = DFT.dropna(axis=1, how='all')
           print(DFT)
        
       elif 'Mesure 1' in box.get():                     
           POUT_M1 = pd.read_excel(pathATC, sheet_name=box.get(),usecols="H",skiprows=8, nrows=11).to_numpy().astype(float)
           POUT_M1[POUT_M1 == 0] = 'nan'               
         
   box = ttk.Combobox(root, textvariable=combo, value =sheet_names, state='readonly')
   box.bind("<<ComboboxSelected>>",selected)
   box.pack()

xl_btn = Button(root,text="ATC",foreground='#161327',background="#707087",command=lambda:OOE())
xl_btn.pack()

label=ttk.Label(root,text=" " ,textvariable=label_check)
label.pack()
root.mainloop() 

原因分析 #

1. 单元格存储的是数组而非单个值：通过pd.read_excel(...).to_numpy()得到的是二维数组（形状为(10,1)），直接zip后生成的DataFrame每个单元格都是长度为1的numpy数组（比如[22.8]、[nan]）。
2. dropna的判断逻辑：dropna(axis=1, how='all')只会删除那些所有单元格本身是NaN的列，但你的T5列单元格是数组对象，不是NaN，因此Pandas不会将其识别为需要删除的列。
3. NaN替换的错误方式：代码中用PI_ATC[PI_ATC == 0] = 'nan'将0替换为字符串'nan'，后续astype(float)虽能转为np.nan，但多余的字符串转换容易引发类型混淆，不如直接使用np.nan。

解决办法 #

方法一：读取时直接获取一维数组 #

在读取Excel后，用.squeeze()将二维数组转为一维，确保每个元素是单个数值而非数组：

if 'Post Burn-in' in box.get() or 'Mesure 2' in box.get():
    # 修改读取逻辑，添加squeeze()
    TH1_Bi = pd.read_excel(pathATC, sheet_name=box.get(),usecols="L",skiprows=8, nrows=10).squeeze().to_numpy().astype(float)
    TH2_Bi = pd.read_excel(pathATC, sheet_name=box.get(),usecols="M",skiprows=8, nrows=10).squeeze().to_numpy().astype(float)
    TH3_Bi = pd.read_excel(pathATC, sheet_name=box.get(),usecols="N",skiprows=8, nrows=10).squeeze().to_numpy().astype(float)
    TH4_Bi = pd.read_excel(pathATC, sheet_name=box.get(),usecols="O",skiprows=8, nrows=10).squeeze().to_numpy().astype(float)
    TH5_Bi = pd.read_excel(pathATC, sheet_name=box.get(),usecols="P",skiprows=8, nrows=10).squeeze().to_numpy().astype(float)
    
    # 替换0为np.nan，无需字符串转换
    TH1_Bi[TH1_Bi == 0] = np.nan
    TH2_Bi[TH2_Bi == 0] = np.nan
    TH3_Bi[TH3_Bi == 0] = np.nan
    TH4_Bi[TH4_Bi == 0] = np.nan
    TH5_Bi[TH5_Bi == 0] = np.nan
    
    dat = list(zip(TH1_Bi,TH2_Bi,TH3_Bi,TH4_Bi,TH5_Bi))
    DFT = pd.DataFrame(data=dat, columns = ['T1', 'T2', 'T3','T4', 'T5'])
    DFT = DFT.dropna(axis=1, how='all')
    print(DFT)

方法二：生成DataFrame后提取数组元素 #

如果不想修改读取逻辑，可以在生成DataFrame后，将每个单元格的数组元素提取出来：

dat = list(zip(TH1_Bi,TH2_Bi,TH3_Bi,TH4_Bi,TH5_Bi))
DFT = pd.DataFrame(data=dat, columns = ['T1', 'T2', 'T3','T4', 'T5'])
# 提取数组中的单个元素
DFT = DFT.applymap(lambda x: x[0] if isinstance(x, np.ndarray) else x)
# 现在可以正常删除全NaN列
DFT = DFT.dropna(axis=1, how='all')
print(DFT)

同时，建议将替换0的代码改为PI_ATC[PI_ATC == 0] = np.nan，避免字符串转换带来的潜在问题。

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