在Pandas中拆分分组数据并获取每个ID的最新Home位置记录

需求说明 #

需要将宽表格式的DataFrame转换为长表，筛选出Location为Home的记录，最终保留每个ID对应的最新日期记录。

原数据 #

ID           Date01     Location01 Date02     Location02  Date03     Location03
0      jane         1/01/2023  Home       2/24/2024  Home
1      sarah        2/03/2024  Home        
2      ricardo      2/05/2024  Home       4/28/2024  Office      5/03/2024  Home
3      thomas       3/23/2024  Home         
4      lee          4/01/2024  Home       5/07/2024  Satellite    
5      patrick      1/02/2024  Home
6      anya         3/05/2022  Office     4/19/2024  Home          

期望中间输出（长表格式） #

ID           Date      Location
0      jane         1/01/2023 Home
1      jane         2/24/2024 Home  
2      sarah        2/03/2024 Home         
3      ricardo      2/05/2024 Home
4      ricardo      4/28/2024 Office   
5      ricardo      5/03/2024 Home
6      thomas       3/23/2024 Home          
7      lee          4/01/2024 Home   
8      lee          5/07/2024 Satellite     
9      patrick      1/02/2024 Home   
10     anya         3/05/2022 Office    
11     anya         4/19/2024 Home          

期望最终输出 #

ID           Date      Location
0      jane         2/24/2024 Home    
1      sarah        2/03/2024 Home              
2      ricardo      5/03/2024 Home
3      thomas       3/23/2024 Home              
4      lee          4/01/2024 Home     
5      patrick      1/02/2024 Home     
6      anya         4/19/2024 Home

解决方案（基于分层索引+堆叠） #

用Pandas的分层索引和stack()方法可以高效实现宽表转长表，步骤如下：

步骤1：设置分层列索引 #

把原数据中带序号的列（比如Date01、Location01）拆分为两层索引：第一层是类型（Date/Location），第二层是序号（01/02/03）。

import pandas as pd

# 处理列名，生成分层索引
df.columns = pd.MultiIndex.from_tuples(
    [col.split('0') if '0' in col else (col, '') for col in df.columns],
    names=['Type', 'Num']
)

步骤2：堆叠列索引转长表 #

用stack()堆叠第二层索引（序号），把宽表转成长表，同时重置索引清理结构：

# 堆叠Num层，转长表
long_df = df.stack(level='Num').reset_index(level='Num', drop=True).reset_index()
# 重命名列，去掉多余的层级名
long_df.columns = ['ID', 'Date', 'Location']

步骤3：清理空值并筛选Home记录 #

去掉Date或Location为空的行，然后筛选Location等于Home的记录：

# 清理空值
long_df = long_df.dropna(subset=['Date', 'Location'])
# 筛选Home记录
home_df = long_df[long_df['Location'] == 'Home']

步骤4：按ID提取最新日期记录 #

先把Date列转为日期格式，再按ID分组，取每组中日期最大的记录：

# 转换日期格式
home_df['Date'] = pd.to_datetime(home_df['Date'], format='%m/%d/%Y')
# 按ID分组，取最新日期的记录
final_df = home_df.sort_values('Date').groupby('ID').last().reset_index()
# 把日期转回原格式
final_df['Date'] = final_df['Date'].dt.strftime('%m/%d/%Y')

执行完以上步骤后，final_df就是期望的最终输出。

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