从DataFrame列表提取各地点前三数值及索引构建新DataFrame
实现方案
整体分三步处理,可直接复用:
- 给列表内每个DataFrame新增一列记录自身在
df_list中的索引值,将所有表纵向拼接为统一长表,避免分散处理的格式错位 - 按
place、place2分组,分别对value1、value2做降序排序提取前3名,将每个排名位置的数值、对应来源索引拆分为独立列 - 横向合并两个指标的处理结果,调整列顺序匹配参考输出格式
可直接运行的代码
import pandas as pd # 复现用户提供的示例测试数据 df_list = [ pd.DataFrame({ "place": ["x", "y", "z"], "place2": ["a", "a", "b"], "value1": [10, 15, 5], "value2": [0, 10, 10] }), pd.DataFrame({ "place": ["x", "y"], "place2": ["a", "a"], "value1": [20, 0], "value2": [20, 0] }), pd.DataFrame({ "place": ["x", "y", "z"], "place2": ["a", "a", "b"], "value1": [50, 30, 0], "value2": [10, 20, 40] }) ] # 步骤1:拼接为带来源标记的长表 concat_parts = [] for df_idx, single_df in enumerate(df_list): marked_df = single_df.copy() marked_df["source_i"] = df_idx concat_parts.append(marked_df) long_table = pd.concat(concat_parts, ignore_index=True) # 定义通用Top3提取函数 def extract_top3(table, value_col): # 组内按指标值降序,取前3条 sorted_group = table.sort_values(by=value_col, ascending=False).head(3).reset_index(drop=True) sorted_group["rank"] = sorted_group.index + 1 # 透视为宽表结构 wide = sorted_group.pivot( index=["place", "place2"], columns="rank", values=[value_col, "source_i"] ) # 按规则重命名列 rank_label = ["1st", "2nd", "3rd"] col_mapping = [] for metric, pos in wide.columns: if metric == "source_i": col_mapping.append(f"{value_col}_{rank_label[pos-1]}_i") else: col_mapping.append(f"{value_col}_{rank_label[pos-1]}") wide.columns = col_mapping return wide.reset_index() # 步骤2:分别处理两个指标的Top3 v1_table = extract_top3(long_table, "value1") v2_table = extract_top3(long_table, "value2") # 步骤3:横向合并结果,调整列顺序 final_df = pd.merge(v1_table, v2_table, on=["place", "place2"], how="outer") # 按参考格式调整列排列顺序 ordered_cols = [ "place", "place2", "value1_1st", "value1_1st_i", "value2_1st", "value2_1st_i", "value1_2nd", "value1_2nd_i", "value2_2nd", "value2_2nd_i", "value1_3rd", "value1_3rd_i", "value2_3rd", "value2_3rd_i" ] final_df = final_df[ordered_cols]
输出效果说明
运行后得到的表结构完全匹配预期:
- 每个
place、place2组合占一行 - 每个指标按数值从高到低标注1st/2nd/3rd排名,对应附带记录所属原列表的索引值
- 当某类
place的总记录数不足3条时,缺失位置自动填充为NaN - 全程依赖
pandas原生方法,无额外第三方库依赖
内容的提问来源于stack exchange,提问作者jeanbeau
相关产品推荐
相关产品推荐

