forked from MickLife/KeepAccounts_v2.0
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathKeepAccounts_v2.0.py
167 lines (143 loc) · 8.57 KB
/
KeepAccounts_v2.0.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
# version: 2.0
# StartTime: 2021/1/6 12:30
# Finished: 2021/1/7 20:30
# Author: MickLife
# B站: https://space.bilibili.com/38626658
import pandas as pd
import openpyxl
import tkinter.filedialog
import datetime
import msvcrt
def strip_in_data(data): # 把列名中和数据中首尾的空格都去掉。
data = data.rename(columns={column_name: column_name.strip() for column_name in data.columns})
data = data.applymap(lambda x: x.strip().strip('¥') if isinstance(x, str) else x)
return data
def read_data_wx(path): # 获取微信数据
d_wx = pd.read_csv(path, header=16, skipfooter=0, encoding='utf-8') # 数据获取，微信
d_wx = d_wx.iloc[:, [0, 4, 7, 1, 2, 3, 5]] # 按顺序提取所需列
d_wx = strip_in_data(d_wx) # 去除列名与数值中的空格。
d_wx.iloc[:, 0] = d_wx.iloc[:, 0].astype('datetime64') # 数据类型更改
d_wx.iloc[:, 6] = d_wx.iloc[:, 6].astype('float64') # 数据类型更改
d_wx = d_wx.drop(d_wx[d_wx['收/支'] == '/'].index) # 删除'收/支'为'/'的行
d_wx.rename(columns={'当前状态': '支付状态', '交易类型': '类型', '金额(元)': '金额'}, inplace=True) # 修改列名称
d_wx.insert(1, '来源', "微信", allow_duplicates=True) # 添加微信来源标识
len1 = len(d_wx)
print("成功读取 " + str(len1) + " 条「微信」账单数据\n")
return d_wx
def read_data_zfb(path): # 获取支付宝数据
d_zfb = pd.read_csv(path, header=4, skipfooter=7, encoding='gbk') # 数据获取，支付宝
d_zfb = d_zfb.iloc[:, [2, 10, 11, 6, 7, 8, 9]] # 按顺序提取所需列
d_zfb = strip_in_data(d_zfb) # 去除列名与数值中的空格。
d_zfb.iloc[:, 0] = d_zfb.iloc[:, 0].astype('datetime64') # 数据类型更改
d_zfb.iloc[:, 6] = d_zfb.iloc[:, 6].astype('float64') # 数据类型更改
d_zfb = d_zfb.drop(d_zfb[d_zfb['收/支'] == ''].index) # 删除'收/支'为空的行
d_zfb.rename(columns={'交易创建时间': '交易时间', '交易状态': '支付状态', '商品名称': '商品', '金额（元）': '金额'}, inplace=True) # 修改列名称
d_zfb.insert(1, '来源', "支付宝", allow_duplicates=True) # 添加支付宝来源标识
len2 = len(d_zfb)
print("成功读取 " + str(len2) + " 条「支付宝」账单数据\n")
return d_zfb
def add_cols(data): # 为数据增加列
# 逻辑1，取值-1 or 1。-1表示支出，1表示收入。
data.insert(8, '逻辑1', -1, allow_duplicates=True) # 插入列，默认值为-1
for index in range(len(data.iloc[:, 2])): # 遍历第3列的值，判断为收入，则改'逻辑1'为1
if data.iloc[index, 2] == '收入':
data.iloc[index, 8] = 1
# 逻辑2，取值0 or 1。1表示计入，0表示不计入。
data.insert(9, '逻辑2', 1, allow_duplicates=True) # 插入列，默认值为1
for index in range(len(data.iloc[:, 3])): # 遍历第4列的值，判断为资金流动，则改'逻辑2'为0
col3 = data.iloc[index, 3]
if (col3 == '提现已到账') or (col3 == '已全额退款') or (col3 == '已退款') or (col3 == '退款成功') or (col3 == '还款成功') or (
col3 == '交易关闭'):
data.iloc[index, 9] = 0
# 月份
data.insert(1, '月份', 0, allow_duplicates=True) # 插入列，默认值为0
for index in range(len(data.iloc[:, 0])):
time = data.iloc[index, 0]
data.iloc[index, 1] = time.month # 访问月份属性的值，赋给这月份列
# 乘后金额
data.insert(11, '乘后金额', 0, allow_duplicates=True) # 插入列，默认值为0
for index in range(len(data.iloc[:, 8])):
money = data.iloc[index, 8] * data.iloc[index, 9] * data.iloc[index, 10]
data.iloc[index, 11] = money
return data
if __name__ == '__main__':
# 路径设置
print('提示：请在弹窗中选择要导入的【微信】账单文件\n')
path_wx = tkinter.filedialog.askopenfilename(title='选择要导入的微信账单：', filetypes=[('所有文件', '.*'), ('csv文件', '.csv')])
if path_wx == '': # 判断是否只导入了微信或支付宝账单中的一个
cancel_wx = 1
else:
cancel_wx = 0
print('提示：请在弹窗中选择要导入的【支付宝】账单文件\n')
path_zfb = tkinter.filedialog.askopenfilename(title='选择要导入的支付宝账单：', filetypes=[('所有文件', '.*'), ('csv文件', '.csv')])
if path_zfb == '': # 判断是否只导入了微信或支付宝账单中的一个
cancel_zfb = 1
else:
cancel_zfb = 0
while cancel_zfb == 1 and cancel_wx == 1:
print('\n您没有选择任何一个账单！ 请按任意键退出程序')
ord(msvcrt.getch())
path_account = tkinter.filedialog.askopenfilename(title='选择要导出的目标账本表格：', filetypes=[('所有文件', '.*'), ('Excel表格', '.xlsx')])
while path_account == '': # 判断是否选择了账本
print('\n年轻人，不选账本怎么记账？ 请按任意键退出程序')
ord(msvcrt.getch())
path_write = path_account
# path_write = 'write_test.xlsx'
# path_wx = 'D:/PythonFiles/KeepAccounts_2021_v2.0/PythonScript/微信支付账单(20201201-20201231).csv'
# path_zfb = 'D:/PythonFiles/KeepAccounts_2021_v2.0/PythonScript/alipay_record_20201201_2154_1.csv'
# path_account = 'D:/PythonFiles/KeepAccounts_2021_v2.0/PythonScript/自动记账2.0_byMick.xlsx'
# 判断是否只导入了微信或支付宝账单中的一个
if cancel_wx == 1:
data_wx = pd.DataFrame()
else:
data_wx = read_data_wx(path_wx) # 读数据
if cancel_zfb == 1:
data_zfb = pd.DataFrame()
else:
data_zfb = read_data_zfb(path_zfb) # 读数据
data_merge = pd.concat([data_wx, data_zfb], axis=0) # 上下拼接合并表格
data_merge = add_cols(data_merge) # 新增 逻辑、月份、乘后金额 3列
print("已自动计算乘后金额和交易月份，已合并数据")
merge_list = data_merge.values.tolist() # 格式转换，DataFrame->List
workbook = openpyxl.load_workbook(path_account) # openpyxl读取账本文件
sheet = workbook['明细']
maxrow = sheet.max_row # 获取最大行
print('\n「明细」 sheet 页已有 ' + str(maxrow) + ' 行数据，将在末尾写入数据')
for row in merge_list:
sheet.append(row) # openpyxl写文件
# 在最后1行写上导入时间，作为分割线
now = datetime.datetime.now()
now = '👆导入时间：' + str(now.strftime('%Y-%m-%d %H:%M:%S'))
break_lines = [now, '-', '-', '-', '-', '-', '-', '-', '-', '-', '-', '-']
sheet.append(break_lines)
workbook.save(path_write) # 保存
print("\n成功将数据写入到 " + path_write)
print("\n运行成功！write successfully! 按任意键退出")
ord(msvcrt.getch())
# def read_leixing(path): # 读取类型标记列表
# wb = openpyxl.load_workbook(path) # 打开账本文件
# ws = wb['消费类型2.0']
# x_big = [] # Lei表示大类
# read_row = ws[2]
# for cell in read_row:
# x_big.append(cell.value)
# del x_big[0]
# # x_big = ['餐饮', '办公好物', '形象', '起居', '健康', '学习', '娱乐', '通勤', '其他支出', '备用A', '收入', '理财', '其他收入', '备用B']
# x_small = [[], [], [], [], [], [], [], [], [], [], [], [], [], []] # 表示小类
# col_list = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O']
# for i in range(14):
# read_range = ws[col_list[i]] # 读取第i列
# Temp = []
# for cell in read_range:
# Temp.append(cell.value)
# Temp = [x for x in Temp if x is not None] # 去除空元素
# x_small[i] = Temp
# x = [x_big, x_small[0], x_small[1], x_small[2], x_small[3], x_small[4], x_small[5], x_small[6], x_small[7],
# x_small[8], x_small[9], x_small[10], x_small[11], x_small[12], x_small[13]]
# # x的第一个元素是大类list，后面还有14个元素是小类
# return x
# lei = read_leixing(path_account) # 读取类型列表
# 导出excel
# data_wx.to_excel(path_save_wx, sheet_name='testdata') # 导出到xlsx
# data_zfb.to_excel(path_save_zfb, sheet_name='testdata') # 导出到xlsx
# data_merge.to_excel(path_save_merge, sheet_name='testdata') # 导出到xlsx