如何在R中利用DataFrame创建符合指定计算规则的矩阵

Got it, let's work through this with pandas—super straightforward once we break it down into steps. I'll cover both matrix requirements with actionable code you can plug into your DF_1 DataFrame.

First, let's assume your DF_1 has columns like date (datetime), D1, D2, type, and a numeric column (let's call it value for summation—replace this with your actual column name if needed). Here's how to compute each required metric:

import pandas as pd

# 确保日期列是datetime格式（如果还不是的话）
DF_1['date'] = pd.to_datetime(DF_1['date'])

# 先计算TAT列（D2-D1）
DF_1['TAT'] = DF_1['D2'] - DF_1['D1']

# Step 1: 按日期分组计算基础指标
daily_agg = DF_1.groupby('date').agg(
    mean_TAT=('TAT', 'mean'),
    count_A=('type', lambda x: (x == 'A').sum()),
    sum_A=('value', lambda x: x[DF_1.loc[x.index, 'type'] == 'A'].sum()),
    count_other=('type', lambda x: (x != 'A').sum()),
    sum_other=('value', lambda x: x[DF_1.loc[x.index, 'type'] != 'A'].sum())
).reset_index()

# Step 2: 计算当月首日起的累计值total_sum
# 先标记每条记录所属的月份
daily_agg['month'] = daily_agg['date'].dt.to_period('M')
# 按月份分组，计算每日的累计总和（这里累计的是当日sum_A+sum_other的总和，可按需调整）
daily_agg['total_sum'] = daily_agg.groupby('month')['sum_A', 'sum_other'].sum(axis=1).cumsum()

# 第一个矩阵就是这个daily_agg数据框
print(daily_agg.head())

说明： #

• Replace value with your actual numeric column name if you're summing a different field.
• If total_sum needs to accumulate a different metric (like total counts instead of sums), just swap out the columns inside the groupby sum call.

Since your original description was incomplete, I'll cover a common use case: aggregating key metrics by month for the most recent 3 months. Adjust the aggregation functions to match your exact needs.

# 筛选近三个月的数据
max_date = DF_1['date'].max()
three_months_back = max_date - pd.DateOffset(months=3)
recent_data = DF_1[DF_1['date'] >= three_months_back]

# 按月份分组计算聚合指标
monthly_agg = recent_data.groupby(recent_data['date'].dt.to_period('M')).agg(
    monthly_avg_TAT=('TAT', 'mean'),
    total_A_count=('type', lambda x: (x == 'A').sum()),
    total_A_sum=('value', lambda x: x[recent_data.loc[x.index, 'type'] == 'A'].sum()),
    total_other_count=('type', lambda x: (x != 'A').sum()),
    total_other_sum=('value', lambda x: x[recent_data.loc[x.index, 'type'] != 'A'].sum())
).reset_index()

# 重命名月份列让结果更清晰
monthly_agg.rename(columns={'date': 'month'}, inplace=True)

# 第二个矩阵就是这个monthly_agg数据框
print(monthly_agg)

说明： #

• If you need additional monthly metrics (like monthly cumulative totals, month-over-month changes), you can add new lines to the agg function or post-process the monthly_agg data frame.

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