使用Pandas如何基于文件当前路径清空指定目录列的时间戳

原始数据与现有代码 #

现有如下结构的DataFrame，记录文件路径、名称、创建/修改时间：

full_path             name             created           modified
0    C:\T1\1.txt            1.txt            14:04:30             NaN
1    C:\T1\1.txt            1.txt              NaN              14:04:30
2    C:\T1\T2\1.txt         1.txt            14:10:30              NaN
3    C:\T1\T2\1.txt         1.txt              NaN              14:10:30
4    C:\T1\T2\T3\1.txt      1.txt            14:15:30             NaN
5    C:\T1\T2\T3\1.txt      1.txt              NaN              14:15:30
6    C:\T1\T2\T3\T4\1.txt   1.txt            14:20:30             NaN

现有处理代码如下：

from pathlib import PurePath
import numpy as np
import pandas as pd

df = pd.DataFrame({
    'full_path': {0: 'C:\\T1\\1.txt', 1: 'C:\\T1\\1.txt',
                  2: 'C:\\T1\\T2\\1.txt', 3: 'C:\\T1\\T2\\1.txt',
                  4: 'C:\\T1\\T2\\T3\\1.txt',
                  5: 'C:\\T1\\T2\\T3\\1.txt',
                  6: 'C:\\T1\\T2\\T3\\T4\\1.txt'},
    'name': {0: '1.txt', 1: '1.txt', 2: '1.txt', 3: '1.txt',
             4: '1.txt', 5: '1.txt', 6: '1.txt'},
    'created': {0: '14:04:30', 1: np.nan, 2: '14:10:30', 3: np.nan,
                4: '14:15:30', 5: np.nan, 6: '14:20:30'},
    'modified': {0: np.nan, 1: '14:04:30', 2: np.nan, 3: '14:10:30',
                 4: np.nan, 5: '14:15:30', 6: np.nan}
})

df['folder'] = df['full_path'].apply(lambda x: PurePath(x).parent.name)
g = df.groupby('name')
df['full_path'] = g['full_path'].transform('last')
df['c_m'] = df['created'].combine_first(df['modified'])
index_cols = ['full_path', 'name']
df = df.pivot_table(index=index_cols,
                    columns='folder',
                    values='c_m',
                    aggfunc='first')
summary_cols = ['created', 'modified']
df = df.reset_index() \
    .merge(g[summary_cols].agg({'created': 'first', 'modified': 'last'}),
           on='name')
df = df[[*index_cols,
         *summary_cols,
         *df.columns.difference(summary_cols + index_cols)]] \
    .rename_axis(None, axis=1)
print(df)

现有代码输出结果：

full_path   name  created modified        T1        T2        T3        T4
0  C:\T1\T2\T3\T4\1.txt  1.txt  14:04:30  14:15:30  14:04:30  14:10:30  14:15:30  14:20:30

新场景与需求 #

当文件1.txt从T4文件夹移回T3文件夹，新增路径为C:\T1\T2\T3\1.txt、创建时间为14:30:30的记录后，新的原始DataFrame如下：

full_path             name             created           modified
0    C:\T1\1.txt            1.txt            14:04:30             NaN
1    C:\T1\1.txt            1.txt              NaN              14:04:30
2    C:\T1\T2\1.txt         1.txt            14:10:30              NaN
3    C:\T1\T2\1.txt         1.txt              NaN              14:10:30
4    C:\T1\T2\T3\1.txt      1.txt            14:15:30             NaN
5    C:\T1\T2\T3\1.txt      1.txt              NaN              14:15:30
6    C:\T1\T2\T3\T4\1.txt   1.txt            14:20:30             NaN
7    C:\T1\T2\T3\1.txt      1.txt            14:30:30             NaN

期望处理后T4列的时间戳变为空值，目标效果如下：

full_path   name  created modified        T1        T2        T3   T4
0  C:\T1\T2\T3\1.txt  1.txt  14:04:30  14:30:30  14:04:30  14:10:30  14:30:30  NaN

需要修改两处核心逻辑：

1. 透视表的聚合函数从first改为last，取每个文件夹下最新的时间记录，保证T3列显示最新的14:30:30
2. 新增过滤逻辑：只有文件最终存储路径的父文件夹列保留值，其余文件夹列设为空值，实现T4列置空的效果

修改后的完整代码如下：

from pathlib import PurePath
import numpy as np
import pandas as pd

# 新的原始DataFrame
df = pd.DataFrame({
    'full_path': {0: 'C:\\T1\\1.txt', 1: 'C:\\T1\\1.txt',
                  2: 'C:\\T1\\T2\\1.txt', 3: 'C:\\T1\\T2\\1.txt',
                  4: 'C:\\T1\\T2\\T3\\1.txt', 5: 'C:\\T1\\T2\\T3\\1.txt',
                  6: 'C:\\T1\\T2\\T3\\T4\\1.txt', 7: 'C:\\T1\\T2\\T3\\1.txt'},
    'name': {0: '1.txt', 1: '1.txt', 2: '1.txt', 3: '1.txt',
             4: '1.txt', 5: '1.txt', 6: '1.txt',7: '1.txt'},
    'created': {0: '14:04:30', 1: np.nan, 2: '14:10:30', 3: np.nan,
                4: '14:15:30', 5: np.nan, 6: '14:20:30',7:'14:30:30'},
    'modified': {0: np.nan, 1: '14:04:30', 2: np.nan, 3: '14:10:30',
                 4: np.nan, 5: '14:15:30', 6: np.nan,7:np.nan}
})

# 获取路径所有父文件夹名称的函数
def get_all_parents(path):
    p = PurePath(path)
    return set(part for part in p.parts if part not in (p.drive, p.name))

df['folder'] = df['full_path'].apply(lambda x: PurePath(x).parent.name)
g = df.groupby('name')
# 记录文件最新存储路径
df['final_path'] = g['full_path'].transform('last')
df['c_m'] = df['created'].combine_first(df['modified'])

# 透视表聚合改为last取每个文件夹最新时间
index_cols = ['final_path', 'name']
df_pivot = df.pivot_table(index=index_cols,
                    columns='folder',
                    values='c_m',
                    aggfunc='last')

summary_cols = ['created', 'modified']
df = df_pivot.reset_index() \
    .merge(g[summary_cols].agg({'created': 'first', 'modified': 'last'}),
           on='name')

# 非当前路径父文件夹的列置空
folder_cols = df_pivot.columns.tolist()
for idx, row in df.iterrows():
    current_parents = get_all_parents(row['final_path'])
    for col in folder_cols:
        if col not in current_parents:
            df.loc[idx, col] = np.nan

# 调整列名和列顺序
df = df.rename(columns={'final_path':'full_path'})
df = df[[*['full_path','name'],
         *summary_cols,
         *df.columns.difference(summary_cols + ['full_path','name'])]] \
    .rename_axis(None, axis=1)
print(df)

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