Annual Expense Report Generation Using Python Pandas

This article demonstrates how to use Python's pandas library to process daily expense records and generate an annual financial report, illustrating the steps from data import to grouping and summarizing expenditures by year and category.

Core techniques : using DataFrame.to_period() to convert dates to yearly periods, and groupby() for aggregation.

Implementation steps :

1. Import pandas: import pandas as pd 2. Adjust display options for proper column alignment:

pd.set_option('display.unicode.ambiguous_as_wide', True)

pd.set_option('display.unicode.east_asian_width', True)

3. Load the Excel file containing expense data: df = pd.read_excel('accounts.xlsx') 4. Set the date column as index and convert to annual period:

df = df.set_index('日期', drop=True)

df = df.to_period('A')

5. Group by year and expense category, then sum the amounts:

df_year = df.groupby(['日期','支出类别'])[['金额']].sum()

print(df_year)

The resulting output, shown in the article's Figure 16, lists total spending per category for each year.