Pandas处理Git提交数据：实现每日聚合信息关联详细提交记录并修复嵌套列表问题

问题背景 #

我正在用Python Pandas处理包含Git提交记录的数据集（DataFrame包含sha、timestamp、date、author、insertion、deletion等字段），需求是按作者分组后进行每日提交数据聚合，生成包含作者、每日提交统计信息（commitInfo）及对应时间戳的结构化数据，同时要为每个commitInfo条目新增details字段，存储对应日期的所有详细提交对象。

当前已实现结构 #

已成功生成如下结构的结果：

[
  {
    "author": "Author1",
    "commitInfo": [
      {
        "sha": 30,
        "insertion": 572,
        "deletion": 495,
        "filepath": 30,
        "merges": 0
      },
      {
        "sha": 18,
        "insertion": 337,
        "deletion": 47,
        "filepath": 18,
        "merges": 0
      }
    ],
    "timestamp": ["2021-01-12T00:00:00+00:Z", "2021-01-13T00:00:00+00:Z"]
  },
  {
    "author": "Author2",
    "commitInfo": [
      {
        "sha": 35,
        "insertion": 601,
        "deletion": 127,
        "filepath": 35,
        "merges": 0
      }
    ],
    "timestamp": ["2021-01-12T00:00:00+00:Z"]
  }
]

期望目标结构 #

希望为每个commitInfo条目新增details字段，存储对应日期的所有详细提交对象：

[
  {
    "author": "Author1",
    "commitInfo": [
      {
        "sha": 30,
        "insertion": 572,
        "deletion": 495,
        "filepath": 30,
        "merges": 0,
        "details": [
          {"sha": "commitsha", "insertion": 10, "deletion": 10, "date": ""},
          {"sha": "", "insertion": 100, "deletion": 80, "date": ""}
        ]
      },
      {
        "sha": 18,
        "insertion": 337,
        "deletion": 47,
        "filepath": 18,
        "merges": 0,
        "details": []
      }
    ],
    "timestamp": ["2021-01-12T00:00:00+00:Z", "2021-01-13T00:00:00+00:Z"]
  },
  {
    "author": "Author2",
    "commitInfo": [
      {
        "sha": 35,
        "insertion": 601,
        "deletion": 127,
        "filepath": 35,
        "merges": 0,
        "details": []
      }
    ],
    "timestamp": ["2021-01-12T00:00:00+00:Z"]
  }
]

遇到的问题 #

我尝试通过自定义commits_metrics函数获取详细提交对象，但当存在多个日期索引时，函数返回嵌套列表，而我需要的是将对应日期的详细记录直接关联到该日期的commitInfo条目中的details字段，而不是返回一个大的嵌套列表。

当前代码实现 #

def commits_metrics(group):
    shas = []
    for index in list(group.index):
        author = index[0]
        date = pd.to_datetime(index[1]).date()
        filtered_data = filter_df[(filter_df['timelessdate'] == date) & (filter_df['author'] == author)]
        result = filtered_data[['sha', 'author', 'timelessdate', 'insertion', 'deletion']].to_dict('records')
        shas.append(result)
    return shas

after = pd.to_datetime("2020-12-28", utc=True)
before = pd.to_datetime("2021-01-10", utc=True)
filter_df = self.df[(self.df["date"] > after) & (self.df["date"] < before)]
filter_df.insert(loc=3, column='timelessdate', value=pd.to_datetime(self.df['date']).dt.date)

if filter_df.size:
    timed_commits = filter_df.set_index(["date"])
    grouped = timed_commits.groupby(by=["author"])
    resampled = grouped.resample("D").agg(
        {
            "sha": "size",
            "insertion": "sum",
            "deletion": "sum",
            "filepath": "size",
            "merges": "max",
        }
    )
    resampled_copy = resampled.loc[:]
    resampled_copy['merges'] = resampled_copy['merges'].fillna(0)
    less_than_zero = resampled_copy.copy()
    resampled_copy['commitType'] = less_than_zero['merges'].apply(lambda merge_col: 'CODE_COMMIT' if merge_col <=0 else 'MERGE_COMMIT')
    if resampled_copy.size:
        result = [
            {
                "author": key,
                "commitInfo": [g for g in group.to_dict(orient="records")],
                "timestamp": [index[1] for index in list(group.index)],
                "commits": commits_metrics(group)
            }
            for key, group in resampled_copy.groupby("author")
        ]
        print('result', result)
    else:
        print("empty resampled")
else:
    print("empty filter_df")

解决方案 #

我们可以不用单独的commits_metrics函数，而是在生成每个commitInfo条目时，直接根据对应的作者和日期去filter_df中查询详细记录并添加到details字段。这样既避免了嵌套列表的问题，又能精准关联每个日期的详细数据。

修改后的代码 #

after = pd.to_datetime("2020-12-28", utc=True)
before = pd.to_datetime("2021-01-10", utc=True)
filter_df = self.df[(self.df["date"] > after) & (self.df["date"] < before)]
# 注意这里要使用filter_df的date列来生成timelessdate，避免引用原df可能带来的索引问题
filter_df['timelessdate'] = pd.to_datetime(filter_df['date']).dt.date

if not filter_df.empty:
    # 按作者分组后每日聚合，重置索引方便后续遍历
    resampled = filter_df.groupby("author").resample("D", on="date").agg(
        {
            "sha": "size",
            "insertion": "sum",
            "deletion": "sum",
            "filepath": "size",
            "merges": "max",
        }
    ).reset_index()
    
    # 处理merges字段和commitType
    resampled['merges'] = resampled['merges'].fillna(0)
    resampled['commitType'] = resampled['merges'].apply(lambda x: 'CODE_COMMIT' if x <= 0 else 'MERGE_COMMIT')
    
    # 生成最终结果
    result = []
    for author, group in resampled.groupby("author"):
        commit_info_list = []
        timestamps = []
        for _, row in group.iterrows():
            # 获取当前行的日期（转成date对象匹配timelessdate）
            target_date = row['date'].date()
            # 查询该作者该日期的所有详细提交记录
            details = filter_df[
                (filter_df['author'] == author) & 
                (filter_df['timelessdate'] == target_date)
            ][['sha', 'insertion', 'deletion', 'date']].to_dict('records')
            
            # 构造commitInfo条目，添加details字段
            commit_info = row.drop(['author', 'date']).to_dict()
            commit_info['details'] = details
            commit_info_list.append(commit_info)
            # 收集ISO格式的时间戳字符串
            timestamps.append(row['date'].isoformat())
        
        result.append({
            "author": author,
            "commitInfo": commit_info_list,
            "timestamp": timestamps
        })
    
    print('result', result)
else:
    print("empty filter_df")

关键改进点 #

1. 避免嵌套列表问题：不再生成单独的嵌套commits列表，而是在遍历每个作者的每日聚合记录时，直接查询对应日期的详细数据并嵌入到commitInfo的details字段中。
2. 索引处理优化：使用reset_index()将聚合后的多级索引转为普通列，更方便遍历和操作；同时生成timelessdate时直接基于filter_df，避免引用原DataFrame导致的索引不匹配问题。
3. 精准关联数据：通过author和target_date精准过滤出对应日期的详细提交记录，确保每个commitInfo条目的details只包含当天的提交数据。
4. 时间戳格式统一：将datetime类型的日期转为ISO格式的字符串，符合目标结构中的时间戳格式。

这样修改后，就能生成你期望的包含details字段的结构化数据啦！

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