python - 如何在 Pandas 数据框中按日期汇总所有金额？

Question

我有包含字段 last_payout 和 amount 的数据框。我需要对每个月的所有 amount 求和并绘制输出。

df[['last_payout','amount']].dtypes

last_payout    datetime64[ns]
amount           float64
dtype: object

-

df[['last_payout','amount']].head

<bound method NDFrame.head of                last_payout  amount
0      2017-02-14 11:00:06          23401.0
1      2017-02-14 11:00:06          1444.0
2      2017-02-14 11:00:06          0.0
3      2017-02-14 11:00:06          0.0
4      2017-02-14 11:00:06          290083.0

我使用了 jezrael 的 answer 中的代码绘制每月的交易数量。

(df.loc[df['last_payout'].dt.year.between(2016, 2017), 'last_payout']
         .dt.to_period('M')
         .value_counts()
         .sort_index()
         .plot(kind="bar")
)

每月交易次数:

如何对每个月的所有 amount 求和并绘制输出？我应该如何扩展上面的代码来做到这一点？

我尝试实现 .sum 但没有成功。

Answer 1

PeriodIndex解决方案:

groupby按 月 期间 to_period并聚合 sum:

df['amount'].groupby(df['last_payout'].dt.to_period('M')).sum().plot(kind='bar')

---

DatetimeIndex解决方案:

使用resample按 月 (M) 或月初 (MS) 汇总 sum:

s = df.resample('M', on='last_payout')['amount'].sum()
#alternative
#s = df.groupby(pd.Grouper(freq='M', key='last_payout'))['amount'].sum()
print (s)
last_payout
2017-02-28     23401.0
2017-03-31      1444.0
2017-04-30    290083.0
Freq: M, Name: amount, dtype: float64

或者:

s = df.resample('MS', on='last_payout')['amount'].sum()
#s = df.groupby(pd.Grouper(freq='MS', key='last_payout'))['amount'].sum()
print (s)
last_payout
2017-02-01     23401.0
2017-03-01      1444.0
2017-04-01    290083.0
Freq: MS, Name: amount, dtype: float64

然后是必要的格式x标签:

ax = s.plot(kind='bar')
ax.set_xticklabels(s.index.strftime('%Y-%m'))

设置:

import pandas as pd

temp=u"""last_payout,amount
2017-02-14 11:00:06,23401.0
2017-03-14 11:00:06,1444.0
2017-03-14 11:00:06,0.0
2017-04-14 11:00:06,0.0
2017-04-14 11:00:06,290083.0"""
#after testing replace 'pd.compat.StringIO(temp)' to 'filename.csv'
df = pd.read_csv(pd.compat.StringIO(temp), parse_dates=[0])
print (df)
          last_payout    amount
0 2017-02-14 11:00:06   23401.0
1 2017-03-14 11:00:06    1444.0
2 2017-03-14 11:00:06       0.0
3 2017-04-14 11:00:06       0.0
4 2017-04-14 11:00:06  290083.0