-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathBills_Record.py
281 lines (219 loc) · 12.8 KB
/
Bills_Record.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
import pandas as pd
import matplotlib
import matplotlib.pyplot as plt
plt.rcParams['font.family'] = ['SimHei'] # replace with your installed Chinese font
matplotlib.use("Agg")
import numpy as np
def Load_Check():
print('Bills_Record Class load successfully!')
return True
class Chart():
def __init__(self, ax):
self.ax = ax
def TestDraw(self):
self.ax.cla()
self.ax.plot([1,2,3], [1,2,3])
self.ax.grid(True) # 添加网格线
def DrawPie(self,datas:list,labels:list,title:str='',colors:list=None):
self.ax.cla()
self.ax.pie(datas, labels=labels, colors=colors, autopct='%1.2f%%')
self.ax.set_title(title, fontsize=15) # 添加标题
# 设置图例字体属性
LegendFont = {'size': 9}
self.ax.legend(labels, loc='upper right', prop=LegendFont)
def DrawBar(self,x:list,y:list,x_label:str='',y_label:str='',title:str='',color:str=None):
self.ax.cla()
self.ax.bar(x, y, color=color)
self.ax.set_title(title, fontsize=15) # 添加标题
self.ax.set_xlabel(x_label, fontsize=12) # 添加X轴标签
self.ax.set_ylabel(y_label, fontsize=12) # 添加X轴标签
self.ax.grid(True) # 添加网格线
for a, b in zip(x, y):#给数据点添加数据
self.ax.annotate('%.2f'%b, xy=(a,b), xytext=(a,b+1), va= 'bottom', ha='center', fontsize=10)
def DrawLine(self,x:list,y:list,label:str='',x_label:str='',y_label:str='',title:str='',color:str=None):
self.ax.cla()
self.ax.plot(x, y, ls='--', lw=1.0, c=color, marker='.', label=label)
self.ax.set_title(title, fontsize=15) # 添加标题
self.ax.set_xlabel(x_label, fontsize=12) # 添加X轴标签
self.ax.set_ylabel(y_label, fontsize=12) # 添加X轴标签
self.ax.grid(True) # 添加网格线
for a, b in zip(x, y):#给数据点添加数据
self.ax.annotate('%.2f'%b, xy=(a,b), xytext=(a,b+1), va= 'bottom', ha='center', fontsize=10)
class Bills_Record:
'''
用于进行消费分析的集成类，集成了相关的函数以及变量\n
注：返回的图片都是(np.ndarray类型的RGB图像)
该类包含以下的分析功能：\n
Bills_Record.Check():用于检查类是否正常加载\n
Bills_Record.Load_Bills_Excel():从电脑上加载Excel数据集(.xlsx文件)\n
Bills_Record.Save_Bills_Excel():将内部数据保存为Excel数据集(.xlsx文件)\n
Bills_Record.Add_New_Record():向表中添加新的消费记录\n
Bills_Record.Get_Types_And_Datas():获取指定时间段内的消费记录按不同类别划分的总类别及每个类别下的数据\n
Bills_Record.Draw_Bar_Chart():绘制柱状图\n
Bills_Record.Draw_Pie_Chart():绘制饼图\n
Bills_Record.Draw_Line_Chart():绘制折线图\n
Bills_Record.Monthly_Consumption_Type_Analyse():分析指定月份的消费额与消费类型的关系，返回一张饼图和柱状图\n
Bills_Record.Monthly_Consumption_Method_Analyse():分析指定月份的消费额与消费方式的关系，返回一张饼图和柱状图\n
Bills_Record.Yearly_Consumption_Type_Analyse():分析指定年份的消费额与消费类型的关系，返回一张饼图和柱状图\n
Bills_Record.Yearly_Consumption_Method_Analyse():分析指定年份的消费额与消费方式的关系，返回一张饼图和柱状图\n
Bills_Record.Year_Monthly_Consumption_Change_Analyse():分析指定年份中每月消费总额的变化趋势，返回一张折线图\n
Bills_Record.Daily_Consumption():计算指定时间段内的日均消费额\n
'''
def __init__(self) -> None:
self.Bills=None
self.Chart=None
def Check(self):
print('passed')
def Load_Bills_Excel(self,FilePath):
'''从电脑上加载Excel数据集(.xlsx文件)'''
self.Bills=pd.read_excel(FilePath)
self.Bills.fillna(0, inplace=True)
def Save_Bills_Excel(self,FilePath):
'''将内部数据保存为Excel数据集(.xlsx文件)'''
self.Bills.to_excel(FilePath, sheet_name='Consumptions', index=False)
def Add_New_Record(self,DataDict:dict):
'''向表中添加新的消费记录'''
self.Bills = self.Bills.append(DataDict, ignore_index=True)
def Get_Types_And_Datas(self,StartDate:pd.Timestamp,EndDate:pd.Timestamp,colname:str):
'''获取指定时间段内的消费记录按不同类别划分的总类别及每个类别下的数据'''
mask=(self.Bills['日期'] >= StartDate) & (self.Bills['日期'] <= EndDate)
SubBills=self.Bills.loc[mask]
Types=list(SubBills[colname].unique())
Datas=[]
for Type in Types[:]:
mask=self.Bills[colname]==Type
SubSubBills=SubBills.loc[mask]
Sum=abs(SubSubBills['消费额'].sum())
if Sum<0.01:
Types.remove(Type)
else:
Datas.append(Sum)
Types,Datas=zip(*sorted(list(zip(Types,Datas)),reverse=True,key=lambda x:x[1]))
return Types,Datas
#绘制图像的函数
def Draw_Bar_Chart(self,x,y,xlabel='',ylabel='',title=''):
'''Draw bar chart'''
fig, ax = plt.subplots(figsize=(6, 6))
plt.rcParams['font.size'] = 10
ax.bar(x, y)
# Add labels and title
ax.set_xlabel(xlabel)
ax.set_ylabel(ylabel)
plt.title(title)
for a, b in zip(x, y):#给数据点添加数据
plt.text(a, b, '%.2f'%b, ha='center', va= 'bottom', fontsize=10)
#Then store the chart as a numpy.ndarray to be a RGB_image
fig.canvas.draw()
Image = np.frombuffer(fig.canvas.tostring_rgb(), dtype=np.uint8)
Image = Image.reshape(fig.canvas.get_width_height()[::-1] + (3,))
if __name__ == '__main__':
plt.show()
# plt.close()# 释放内存
return Image
def Draw_Pie_Chart(self,datas,labels,title='',colors=None):
'''Draw pie chart'''
fig, ax = plt.subplots(figsize=(6, 6))
plt.rcParams['font.size'] = 10
ax.pie(datas, labels=labels, colors=colors, autopct='%1.2f%%')
ax.legend(labels, loc='upper right')
plt.title(title)
#Then store the chart as a numpy.ndarray to be a RGB_image
fig.canvas.draw()
Image = np.frombuffer(fig.canvas.tostring_rgb(), dtype=np.uint8)
Image = Image.reshape(fig.canvas.get_width_height()[::-1] + (3,))
if __name__ == '__main__':
plt.show()
# plt.close()# 释放内存
return Image
def Draw_Line_Chart(self,x,y,label='',xlabel='',ylabel='',title='',figsize=(12, 6)):
'''Draw line chart'''
fig, ax = plt.subplots(figsize=figsize)
plt.rcParams['font.size'] = 10
ax.plot(x, y, ls='--', lw=1.0, c='Red', marker='.', label=label)
ax.legend(loc='upper right')
plt.grid(True)
# Add labels and title
ax.set_xlabel(xlabel)
ax.set_ylabel(ylabel)
plt.title(title)
for a, b in zip(x, y):#给数据点添加数据
plt.text(a, b, '%.2f'%b, ha='center', va= 'bottom', fontsize=10)
#Then store the chart as a numpy.ndarray to be a RGB_image
fig.canvas.draw()
Image = np.frombuffer(fig.canvas.tostring_rgb(), dtype=np.uint8)
Image = Image.reshape(fig.canvas.get_width_height()[::-1] + (3,))
if __name__ == '__main__':
plt.show()
# plt.close()# 释放内存
return Image
def Monthly_Consumption_Type_Analyse(self,year=2023,month=1):
'''分析指定月份的消费额与消费类型的关系，返回一张饼图和柱状图'''
start_date = pd.Timestamp(f'{year}-{month}-01')
end_date = start_date + pd.offsets.MonthEnd(1)
Types,Datas = self.Get_Types_And_Datas(StartDate=start_date,EndDate=end_date,colname='消费类型')
return (
self.Draw_Pie_Chart(datas=Datas,labels=Types,title=f'{year}-{month}消费分类百分比，消费总额:{round(sum(Datas),2)}'),
self.Draw_Bar_Chart(y=Datas,x=Types,xlabel='消费类型',ylabel='消费额',title=f'{year}-{month}消费分类柱状图，消费总额:{round(sum(Datas),2)}')
)
def Monthly_Consumption_Method_Analyse(self,year=2023,month=1):
'''分析指定月份的消费额与消费方式的关系，返回一张饼图和柱状图'''
start_date = pd.Timestamp(f'{year}-{month}-01')
end_date = start_date + pd.offsets.MonthEnd(1)
Methods,Datas = self.Get_Types_And_Datas(StartDate=start_date,EndDate=end_date,colname='支付方式')
return (
self.Draw_Pie_Chart(datas=Datas,labels=Methods,title=f'{year}-{month}消费支付方式百分比，消费总额:{round(sum(Datas),2)}'),
self.Draw_Bar_Chart(y=Datas,x=Methods,xlabel='消费支付方式',ylabel='消费额',title=f'{year}-{month}消费支付方式柱状图，消费总额:{round(sum(Datas),2)}')
)
def Yearly_Consumption_Type_Analyse(self,year=2023):
'''分析指定年份的消费额与消费类型的关系，返回一张饼图和柱状图'''
start_date = pd.Timestamp(f'{year}-01-01')
end_date = start_date + pd.offsets.MonthEnd(12)
Types,Datas = self.Get_Types_And_Datas(StartDate=start_date,EndDate=end_date,colname='消费类型')
return (
self.Draw_Pie_Chart(datas=Datas,labels=Types,title=f'{year}消费分类百分比，消费总额:{round(sum(Datas),2)}'),
self.Draw_Bar_Chart(y=Datas,x=Types,xlabel='消费类型',ylabel='消费额',title=f'{year}消费分类柱状图，消费总额:{round(sum(Datas),2)}')
)
def Yearly_Consumption_Method_Analyse(self,year=2023):
'''分析指定年份的消费额与消费方式的关系，返回一张饼图和柱状图'''
start_date = pd.Timestamp(f'{year}-01-01')
end_date = start_date + pd.offsets.MonthEnd(12)
Methods,Datas = self.Get_Types_And_Datas(StartDate=start_date,EndDate=end_date,colname='支付方式')
return (
self.Draw_Pie_Chart(datas=Datas,labels=Methods,title=f'{year}消费支付方式百分比，消费总额:{round(sum(Datas),2)}'),
self.Draw_Bar_Chart(y=Datas,x=Methods,xlabel='消费支付方式',ylabel='消费额',title=f'{year}消费支付方式柱状图，消费总额:{round(sum(Datas),2)}')
)
def Year_Monthly_Consumption_Change_Analyse(self,year=2023):
'''分析指定年份中每月消费总额的变化趋势，返回一张折线图'''
Datas=[]
for month in range(1,13):#计算每一个月的消费总额
start_date = pd.Timestamp(f'{year}-{month}-01')
end_date = start_date + pd.offsets.MonthEnd(1)
mask=(self.Bills['日期'] >= start_date) & (self.Bills['日期'] <= end_date)
Year_Month_ConsumptionDataFrame = self.Bills.loc[mask]
Sum=abs(Year_Month_ConsumptionDataFrame['消费额'].sum())
Datas.append(Sum)
return self.Draw_Line_Chart(x=list([str(i)+'月' for i in range(1,13)]),y=Datas,label='消费额',xlabel='月份',ylabel='消费额',title=f'{year}年消费随月份变化曲线')
def Daily_Consumption(self,year:int=2023,month:int=1) -> float:
'''计算指定时间段内的日均消费额，month in [1,12]时计算每月的日均，month=-1时计算当年的日均'''
if month > 0:
StartDate = pd.Timestamp(f'{year}-{month}-01')
EndDate = StartDate + pd.offsets.MonthEnd(1)
elif month == -1:
StartDate = pd.Timestamp(f'{year}-01-01')
EndDate = StartDate + pd.offsets.MonthEnd(12)
mask=(self.Bills['日期'] >= StartDate) & (self.Bills['日期'] <= EndDate)
SubBills=self.Bills.loc[mask]
Sum=abs(SubBills['消费额'].sum())
Days=(EndDate-StartDate).days
return round(Sum/Days,2)
if __name__ == '__main__':
BillsRecord=Bills_Record()
BillsRecord.Load_Bills_Excel('E:\#刘济伟\###生活\记账/2023.xlsx')
# print(BillsRecord.Bills)
# BillsRecord.Add_New_Record({'日期':pd.Timestamp('2025-01-01'),'消费额':-1000,'支付方式':'微信钱包','消费类型':'测试'})
# print(BillsRecord.Bills)
# BillsRecord.Monthly_Consumption_Type_Analyse(month=5)
# BillsRecord.Yearly_Consumption_Type_Analyse(year=2023)
# BillsRecord.Yearly_Consumption_Method_Analyse(year=2023)
# BillsRecord.Year_Monthly_Consumption_Change_Analyse(year=2023)
print(BillsRecord.Day_Average(StartDate=pd.Timestamp('2023-05-01'),EndDate=pd.Timestamp('2023-05-31')))