如何根据日期和名称转换熊猫数据框

Question

我正在尝试不用而没有达到我所需的输出的数据框架，这将感谢一些帮助：

输入数据：

date         name   value
2022-07-01   Anna    5
2022-07-01   Jim     3
2022-04-29   Anna    4
2022-04-29   Jim     2
2022-04-21   Anna    1
2021-07-29   Jim     5
2021-07-29   Anna    5

所需的输出：

Name         Last_30_days   Last_365_days
Anna            5              15
Jim             3              10

我最接近的尝试：

df_new = (df.set_index('date')
        .groupby('name', sort=False)
        .resample('MS')['value'].sum()
        .groupby(level=0)
        .cumsum()
        .reset_index(name='value'))

Answer 1

计算新的二进制列，以检查是否包括当天，然后枢纽，然后con绕所有选定的一天差异。

days_select = [30, 60, 365]
pd.concat([df.assign(in_last_n_days=(pd.Timestamp.today() - df.date) < pd.Timedelta(f"{ndays}d"))
             .query("in_last_n_days")
             .pivot_table(index="name", values="value", aggfunc=sum)
             .rename(columns={"value": f"last_{ndays}_days"})
           for ndays in days_select], axis=1)

      last_30_days  last_60_days  last_365_days
name                                           
Anna             5             5             15
Jim              3             3             10

Answer 2

尝试以下尝试：

daydiff = (pd.Timestamp('now') - df['date']).dt.days
bins = [0, 30, 365, np.inf]
labels = [f'Last_{i}_days' for i in bins[1:-1]] + ['Greater than 365 days']
df['period'] = pd.cut(daydiff, bins=bins, labels=labels)
df_out = df.pivot_table('value', 'name', 'period', aggfunc='sum')
print(df_out)

输出：

period  Last_30_days  Last_365_days  Greater than 365 days
name                                                     
Anna               5             10                      0
Jim                3              7                      0

并且，您可以添加一个暨以沿行累积总数：

daydiff = (pd.Timestamp('now') - df['date']).dt.days
bins = [0,30,365,np.inf]
labels = [f'Last_{i}_days' for i in bins[1:-1]] + ['Greater than 365 days']
df['period'] = pd.cut(daydiff, bins=bins, labels=labels)
df_out = df.pivot_table('value', 'name', 'period', aggfunc='sum')
df_out = df_out.cumsum(axis=1)
print(df_out)

输出：

period  Last_30_days  Last_365_days  Greater than 365 days
name                                                     
Anna               5             15                     15
Jim                3             10                     10

Answer 3

df_pivot = pd.pivot_table(df, values='value',index='name',columns='date')
df_pivot.fillna(0,inplace=True)
expected_df  = pd.DataFrame(df_pivot['2021-07-29'] + df_pivot['2022-04-21'] + df_pivot['2022-04-29'] + df_pivot['2022-07-01'])
expected_df['last_365_days'] = df_pivot['2021-07-29'] + df_pivot['2022-04-21'] + df_pivot['2022-04-29'] + df_pivot['2022-07-01']
del expected_df[0]
expected_df['last_30_days'] = df_pivot['2022-07-01']