如何获取Pandas DataFrame非空值位置并去除双向重复项

一、获取DataFrame中非空值的位置 #

问题描述 #

现有如下Pandas DataFrame：

0   1   2   3   4   5
0   NaN NaN 7.0 NaN NaN NaN
1   NaN NaN 9.0 NaN NaN NaN
2   5.0 NaN 3.0 NaN 9.0 NaN
3   NaN NaN NaN NaN NaN NaN
4   NaN NaN NaN NaN NaN 1.0

需要获取所有非空值的位置，格式为行索引-列索引，例如7.0的位置是0-2，期望输出如下：

expected = ["0-2", "1-2", "2-0", "2-2", "2-4", "4-5"]

构造DataFrame的代码 #

import pandas as pd
import numpy as np

mylist=[[np.nan, np.nan, 7, np.nan, np.nan, np.nan],[np.nan, np.nan, 9, np.nan, np.nan, np.nan],[5, np.nan, 3, np.nan, 9, np.nan],[np.nan, np.nan, np.nan, np.nan, np.nan, np.nan],[np.nan, np.nan,np.nan, np.nan,np.nan, 1]]
df = pd.DataFrame(mylist)

解决方案 #

利用df.stack()筛选非空值并获取其索引，再将索引格式化为指定字符串：

# 获取非空值的位置列表
result = [f"{row}-{col}" for row, col in df.stack().index]
print(result)
# 输出结果与期望一致：['0-2', '1-2', '2-0', '2-2', '2-4', '4-5']

二、去除双向重复项 #

问题描述 #

当前获取的结果中存在双向重复项（如34-35与35-34视为重复），示例输入：

out = ['34-35', '35-34',
 '41-42', '42-41',
 '46-47', '47-46',
 '59-63', '63-59',
 '75-76', '76-75',
 '87-88', '88-87']

需要去除这类重复，得到唯一值列表：

expected = ['34-35', '41-42', '46-47', '59-63', '75-76', '87-88']

解决方案 #

将每个位置字符串拆分为两个索引值，排序后重新拼接，再通过集合去重，最后按需排序：

# 拆分、排序并去重
unique_set = {'-'.join(sorted(item.split('-'))) for item in out}
# 转换为列表并按第一个索引排序（保持结果有序）
unique_result = sorted(unique_set, key=lambda x: int(x.split('-')[0]))
print(unique_result)
# 输出结果与期望一致：['34-35', '41-42', '46-47', '59-63', '75-76', '87-88']

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