如何将Pandas DataFrame转换为层级结构化数据集/对象？

我有一个近900万行、30列的DataFrame，列越往后数据越具体，导致前列数据重复严重。示例表格如下：

我希望将其转换为按park_code分组的层级结构化对象，示例结构如下：

park code: acad
campgrounds: campground1, campground2
parking lots: parking_lot1, parking_lot2, parking_lot3

park code: bisc
campgrounds: campground3, ....
.......

我习惯用SQL，正在学Pandas，完全不知道怎么实现，附上当前的fetch_results函数及调用代码，求帮助：

函数调用 #

data_handler.fetch_results(['Wildlife Watching', 'Arts and Culture'], ['Restroom'], ['Acadia National Park'], ['ME'])

当前代码 #

def fetch_results(self, activities_selection, amenities_selection, parks_selection, states_selection):
    activities_selection_df = self.activities_df['park_code'][self.activities_df['activity_name'].isin(activities_selection)].drop_duplicates()
    amenities_selection_df = self.amenities_parks_df['park_code'][self.amenities_parks_df['amenity_name'].isin(amenities_selection)].drop_duplicates()
    states_selection_df = self.activities_df['park_code'][self.activities_df['park_states'].isin(states_selection)].drop_duplicates()
    parks_selection_df = self.activities_df['park_code'][self.activities_df['park_name'].isin(parks_selection)].drop_duplicates()
    data = activities_selection_df[activities_selection_df.isin(amenities_selection_df) & activities_selection_df.isin(states_selection_df) & activities_selection_df.isin(parks_selection_df)].drop_duplicates()
    pandas_select_df = pd.DataFrame(data, columns=['park_code'])

    results_df = pd.merge(pandas_select_df, self.activities_df, on='park_code', how='left')
    results_df = pd.merge(results_df, self.amenities_parks_df[['park_code', 'amenity_name', 'amenity_url']], on='park_code', how='left')
    results_df = pd.merge(results_df, self.campgrounds_df[['park_code', 'campground_name', 'campground_url', 'campground_road', 'campground_classification', 'campground_general_ADA', 'campground_wheelchair_access', 'campground_rv_info', 'campground_description', 'campground_cell_reception', 'campground_camp_store', 'campground_internet', 'campground_potable_water', 'campground_toilets', 'campground_campsites_electric', 'campground_staff_volunteer']], on='park_code', how='left')
    results_df = pd.merge(results_df, self.places_df[['park_code', 'places_title', 'places_url']], on='park_code', how='left')
    results_df = pd.merge(results_df, self.parking_lot_df[['park_code', "parking_lots_name", "parking_lots_ADA_facility_description", "parking_lots_is_lot_accessible", "parking_lots_number_oversized_spaces", "parking_lots_number_ADA_spaces", "parking_lots_number_ADA_Step_Free_Spaces", "parking_lots_number_ADA_van_spaces", "parking_lots_description"]], on='park_code', how='left')
    # print(self.campgrounds_df.to_string(max_rows=20))
    print(results_df.to_string(max_rows=40))

解决方案 #

核心思路 #

类比SQL的GROUP BY park_code结合字符串聚合函数，用Pandas的groupby分组后，对每个分组内的目标列去重并拼接成字符串，实现层级结构输出。

修改后的代码 #

在fetch_results函数末尾，替换原有的print(results_df.to_string(max_rows=40))为以下代码：

# 定义需要聚合的列：展示名称 -> DataFrame中的列名
agg_columns = {
    'campgrounds': 'campground_name',
    'parking lots': 'parking_lots_name',
    'activities': 'activity_name',
    'amenities': 'amenity_name'
}

# 先去重减少数据量，避免重复计算
results_df = results_df.drop_duplicates(subset=['park_code'] + list(agg_columns.values()))

# 按park_code分组，对每个列去重后拼接为逗号分隔的字符串
grouped_data = results_df.groupby('park_code').agg(
    **{key: (col, lambda x: ', '.join(sorted(x.dropna().unique()))) for key, col in agg_columns.items()}
).reset_index()

# 输出层级结构化内容
for _, row in grouped_data.iterrows():
    print(f"park code: {row['park_code']}")
    for category in agg_columns.keys():
        print(f"{category}: {row[category]}")
    print()

关键说明 #

1. 性能优化：900万行的数据量很大，先通过drop_duplicates剔除重复组合，能大幅降低分组计算的压力。
2. 空值处理：用dropna()过滤空值，避免输出里出现无效的空字符串。
3. 扩展性：如果需要更细的层级（比如每个营地对应专属停车场），可以嵌套分组，但当前方案先满足基础的按公园聚合的需求。

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