我创建了一个财务交易的SQL数据库。我希望在python和pandas中创建数据帧,以按月汇总我的支出。我已经创建了下面的代码,它是有效的。但希望能提高效率。这是怎么做到的?
代码示例。
jan_spend = df[df['date'].between('2022-01-01', '2022-01-31')]
jan_spend_df = jan_spend[['date', 'amount']]
jan_total = jan_spend_df['amount'].sum()
feb_spend = df[df['date'].between('2022-02-01', '2022-02-28')]
feb_spend_df = feb_spend[['date', 'amount']]
feb_total = feb_spend_df['amount'].sum()
您可以创建一个月列,然后对具有相同月份的行进行分组和求和。
df['month'] = pd.to_datetime(df['date']).apply(lambda x: '{year}-{month}'.format(year=x.year, month=x.month))
sum = df.groupby('month')['amount'].sum()
print(sum)
结果:
month
2022-1 1064
2022-2 1297
...