83 lines
2.9 KiB
Python
83 lines
2.9 KiB
Python
|
'''
|
|||
|
|
支付宝账单
|
|||
|
|
|
|||
|
|
cchardet 使用这个包检测比chardet更准确
|
|||
|
|
'''
|
|||
|
|
import codecs
|
|||
|
|
import time
|
|||
|
|
from pathlib import Path
|
|||
|
|
|
|||
|
|
import cchardet as chardet
|
|||
|
|
import numpy as np
|
|||
|
|
import pandas as pd
|
|||
|
|
|
|||
|
|
|
|||
|
|
def detection_file_encoding(file_name): # 自动检测文件编码
|
|||
|
|
with open(file_name, 'rb') as file:
|
|||
|
|
rawdata = file.read()
|
|||
|
|
result = chardet.detect(rawdata)
|
|||
|
|
# 检测结果包含编码和置信度信息
|
|||
|
|
encoding = result['encoding']
|
|||
|
|
confidence = result['confidence']
|
|||
|
|
print(f"文件【{file_name}】 编码：{encoding}, 置信度：{confidence:.2f}")
|
|||
|
|
return encoding
|
|||
|
|
|
|||
|
|
|
|||
|
|
def encoding_conversion(source_file, target_file, source_encoding, target_encoding): # 文件编码转换
|
|||
|
|
file_path = Path(target_file)
|
|||
|
|
if file_path.exists():
|
|||
|
|
return detection_file_encoding(target_file)
|
|||
|
|
# 指定源文件的编码和目标文件的编码
|
|||
|
|
source_encoding = source_encoding # 源文件编码
|
|||
|
|
target_encoding = target_encoding # 目标文件编码
|
|||
|
|
|
|||
|
|
# 使用codecs模块打开源文件和目标文件，进行编码转换
|
|||
|
|
with codecs.open(source_file, 'r', encoding=source_encoding) as source:
|
|||
|
|
with codecs.open(target_file, 'w', encoding=target_encoding) as target:
|
|||
|
|
for line in source:
|
|||
|
|
target.write(line)
|
|||
|
|
|
|||
|
|
encoding = detection_file_encoding(target_file)
|
|||
|
|
print(f"文件已从 {source_encoding} 编码转换为 {encoding} 编码")
|
|||
|
|
return encoding
|
|||
|
|
|
|||
|
|
|
|||
|
|
def reset_account_name(name):
|
|||
|
|
if "余额宝" in name or '滴滴出行' in name:
|
|||
|
|
return "支付宝"
|
|||
|
|
elif "信用卡" in name:
|
|||
|
|
return "信用卡"
|
|||
|
|
elif "借记卡" in name:
|
|||
|
|
return "现金"
|
|||
|
|
|
|||
|
|
|
|||
|
|
class ALiPay:
|
|||
|
|
def __init__(self, csv_file):
|
|||
|
|
# 获取文件编码
|
|||
|
|
self.encoding = detection_file_encoding(csv_file)
|
|||
|
|
rename = csv_file.split("-")[1:3]
|
|||
|
|
if len(rename) > 0:
|
|||
|
|
rename = "_".join(csv_file.split("-")[1:3])
|
|||
|
|
else:
|
|||
|
|
rename = int(time.time())
|
|||
|
|
self.target_file = f'/Users/renmeng/Downloads/支付宝交易账单-{rename}.csv' # 目标文件名
|
|||
|
|
# 生成新文件，并且使用加入日期命名
|
|||
|
|
self.encoding = encoding_conversion(source_file=csv_file, target_file=self.target_file,
|
|||
|
|
source_encoding=self.encoding,
|
|||
|
|
target_encoding="utf-8")
|
|||
|
|
|
|||
|
|
def get_ali_pay_bill(self):
|
|||
|
|
# 你可以使用pandas库的skiprows参数来指定从第几行开始读取数据
|
|||
|
|
df = pd.read_csv(self.target_file, encoding=self.encoding, skiprows=2)
|
|||
|
|
df = df.drop(index=df[df['交易状态'] != '成功'].index)
|
|||
|
|
# 将日期列转换为日期时间对象
|
|||
|
|
df['创建时间'] = pd.to_datetime(df['创建时间'])
|
|||
|
|
df['账户'] = df['支付渠道'].apply(reset_account_name)
|
|||
|
|
# 格式化日期列为'%Y-%m-%d'
|
|||
|
|
df['创建时间'] = df['创建时间'].dt.strftime('%Y-%m-%d')
|
|||
|
|
df['优惠(元)'].apply(lambda x: 0 if not x.strip() else float(x))
|
|||
|
|
df['金额'] = df['订单金额(元)'].apply(lambda x: float(x) if x else 0) \
|
|||
|
|
- df['累计退款总额(元)'].apply(lambda x: float(x) if x else 0) \
|
|||
|
|
- df['优惠(元)'].apply(lambda x: 0 if not x.strip() else float(x))
|
|||
|
|
return df
|