支付宝账单_基础数据处理
支付宝账单
- 目标:基础数据处理完成
- 存入到mysql
- 选取关键字段导入到guncash
- 会计账户处理，由于涉及类目较多，技术上暂时不处理，手动处理
账单数据读取
In [ ]:
import pandas as pd
from datetime import datetime
import time
def read_csv(from_path):
table = pd.read_csv(from_path,skiprows=4,encoding = 'gb18030') ##选取表
return table
def write_csv(to_path,data):
data.to_csv(to_path,index=False)
if __name__ == "__main__":
start_time = time.time() # 开始时间
path = r'C:\Users\HP\Downloads\alipay_record_20200216_2110'
file_name =r'alipay_record_20200216_2110_1.csv'
from_path = path+"/"+file_name
print(from_path)
df = read_csv(from_path)
df.head()
end_time = time.time() #结束时间
print("程序耗时%f秒." % (end_time - start_time))
C:\Users\HP\Downloads\alipay_record_20200216_2110/alipay_record_20200216_2110_1.csv 程序耗时0.015626秒.
查看数据
In [ ]:
#去掉空格
df.columns = [i.replace(' ','') for i in df.columns]
# 删除字段
df.drop(['Unnamed:16'],axis=1,inplace=True)
df_v1 = df[df.交易创建时间 != None]
df_v1 = df[df.交易创建时间.notnull()]
df_v1.tail()
Out[ ]:
| 交易号 | 商家订单号 | 交易创建时间 | 付款时间 | 最近修改时间 | 交易来源地 | 类型 | 交易对方 | 商品名称 | 金额（元） | 收/支 | 交易状态 | 服务费（元） | 成功退款（元） | 备注 | 资金状态 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 105 | 20200102317173822941 \t | \t | 2020-01-02 03:41:26 | 2020-01-02 03:41:26 | 支付宝网站 | 即时到账交易 | 天弘基金管理有限公司 | 余额宝-2020.01.01-收益发放 | 0.30 | 收入 | 交易成功 | 0.0 | 0.0 | 已收入 | ||
| 106 | 2020010122001447941406534850\t | 210_202001012063219401962179\t | 2020-01-01 20:06:14 | 2020-01-01 20:06:15 | 2020-01-01 20:06:15 | 其他（包括阿里巴巴和外部商家） | 即时到账交易 | 滴滴出行 | 滴滴快车-李师傅 | 7.23 | 支出 | 交易成功 | 0.0 | 0.0 | 已支出 | |
| 107 | 2020010122001447941406563919\t | 210_202001016603452501032334\t | 2020-01-01 17:20:00 | 2020-01-01 17:20:01 | 2020-01-01 17:20:01 | 其他（包括阿里巴巴和外部商家） | 即时到账交易 | 滴滴出行 | 滴滴快车-胡师傅 | 9.37 | 支出 | 交易成功 | 0.0 | 0.0 | 已支出 | |
| 108 | 2020010122001447941406171990\t | 20200101064719146071043239508707\t | 2020-01-01 06:47:19 | 2020-01-01 06:47:20 | 2020-01-01 06:47:20 | 其他（包括阿里巴巴和外部商家） | 即时到账交易 | 上海都畅数字技术有限公司 | 上海地铁乘车扣款 出站时间 01-01 06:43 威宁路-虹桥火车站 | 4.00 | 支出 | 交易成功 | 0.0 | 0.0 | 已支出 | |
| 109 | 20200101312831976941 \t | \t | 2020-01-01 04:59:33 | 2020-01-01 04:59:33 | 支付宝网站 | 即时到账交易 | 天弘基金管理有限公司 | 余额宝-2019.12.31-收益发放 | 0.30 | 收入 | 交易成功 | 0.0 | 0.0 | 已收入 |
存入数据库
In [ ]:
import pymysql
from sqlalchemy import create_engine
def write_mysql(table,table_name):
try:
engine = create_engine("mysql+pymysql://kemi:kemi1016@106.12.33.33:3306/zhifubao?charset=utf8mb4")
table.to_sql(name=table_name,con=engine,if_exists='replace',index=False,chunksize=10000)
print ('数据库写入成功')
except :
print ('数据库写入失败')
if __name__ == "__main__":
start_time = time.time() # 开始时间
try:
write_mysql(df_v1,'odl_zhifubao_bill_v1')
except:
print('sql查询失败')
end_time = time.time() #结束时间
print("程序耗时%f秒." % (end_time - start_time))
C:\ProgramData\Anaconda3\lib\site-packages\pymysql\cursors.py:170: Warning: (3719, "'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.") result = self._query(query)
数据库写入成功 程序耗时0.755192秒.
数据二次加工
In [ ]:
df_v2 = df_v1[['交易创建时间','交易对方','商品名称','金额（元）','交易对方','收/支','资金状态','交易号','商家订单号','交易状态']]
df_v2.rename(columns={'交易创建时间': 'ds', '金额（元）': 'income','交易号':'id'},inplace=True)
df_v2.loc[:, 'id'] = df['交易号'].str.replace('\t','')
df_v2.loc[:, 'description'] = df['交易对方'].str.replace(' ','')+'-'+df['商品名称'].str.replace(' ','')+df['资金状态'].str.replace(' ','')
df_v2.loc[:, 'status'] = df['资金状态'].str.replace(' ','')+'-'+df['收/支'].str.replace(' ','')
df_v2.head()
C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\frame.py:3778: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy return super(DataFrame, self).rename(**kwargs) C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexing.py:543: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy self.obj[item] = s C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexing.py:362: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy self.obj[key] = _infer_fill_value(value)
Out[ ]:
| ds | 交易对方 | 商品名称 | income | 交易对方 | 收/支 | 资金状态 | id | 商家订单号 | 交易状态 | description | status | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2020-01-31 05:40:39 | 天弘基金管理有限公司 | 余额宝-2020.01.30-收益发放 | 0.09 | 天弘基金管理有限公司 | 收入 | 已收入 | 20200131361530795941 | \t | 交易成功 | 天弘基金管理有限公司-余额宝-2020.01.30-收益发放 | 已收入-收入 |
| 1 | 2020-01-30 18:56:34 | 中国移动官方旗舰店 | 152-2916-2348 陕西移动 手机 话费充值 50元 快充直充 24小时自动充 快速到帐 | 49.90 | 中国移动官方旗舰店 | 支出 | 已支出 | 2020013022001147941416389578 | T100P832334880136213271\t | 交易成功 | 中国移动官方旗舰店-152-2916-2348陕西移动手机话费充值50元快充直充24小时自动… | 已支出-支出 |
| 2 | 2020-01-30 05:13:20 | 天弘基金管理有限公司 | 余额宝-2020.01.29-收益发放 | 0.09 | 天弘基金管理有限公司 | 收入 | 已收入 | 20200130357321951941 | \t | 交易成功 | 天弘基金管理有限公司-余额宝-2020.01.29-收益发放 | 已收入-收入 |
| 3 | 2020-01-29 04:31:22 | 天弘基金管理有限公司 | 余额宝-2020.01.28-收益发放 | 0.09 | 天弘基金管理有限公司 | 收入 | 已收入 | 20200129353812088941 | \t | 交易成功 | 天弘基金管理有限公司-余额宝-2020.01.28-收益发放 | 已收入-收入 |
| 4 | 2020-01-28 03:49:16 | 天弘基金管理有限公司 | 余额宝-2020.01.27-收益发放 | 0.09 | 天弘基金管理有限公司 | 收入 | 已收入 | 20200128349846566941 | \t | 交易成功 | 天弘基金管理有限公司-余额宝-2020.01.27-收益发放 | 已收入-收入 |
In [ ]:
## 收入设置增加、减少； 默认支出为减少
df_v2['income'] =df_v2[['income','status']].apply(lambda x:x.income * -1 if '已支出' in x.status else x.income, axis=1)
C:\ProgramData\Anaconda3\lib\site-packages\ipykernel_launcher.py:2: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
数据保存
In [ ]:
df_data = df_v2[['ds','income','status','description','id']]
def write_csv(to_path,data):
data.to_csv(to_path,index=False)
if __name__ == "__main__":
start_time = time.time() # 开始时间
path = 'C:/Users/HP/Desktop'
file_name ='zhifubao_v2.csv'
path = path+"/"+file_name
print(path)
write_csv(path,df_data)
#df.head()
end_time = time.time() #结束时间
print("程序耗时%f秒." % (end_time - start_time))
print(df_data.head())
C:/Users/HP/Desktop/zhifubao_v2.csv
程序耗时0.002981秒.
ds income status \
0 2020-01-31 05:40:39 0.09 已收入-收入
1 2020-01-30 18:56:34 -49.90 已支出-支出
2 2020-01-30 05:13:20 0.09 已收入-收入
3 2020-01-29 04:31:22 0.09 已收入-收入
4 2020-01-28 03:49:16 0.09 已收入-收入
description \
0 天弘基金管理有限公司-余额宝-2020.01.30-收益发放
1 中国移动官方旗舰店-152-2916-2348陕西移动手机话费充值50元快充直充24小时自动...
2 天弘基金管理有限公司-余额宝-2020.01.29-收益发放
3 天弘基金管理有限公司-余额宝-2020.01.28-收益发放
4 天弘基金管理有限公司-余额宝-2020.01.27-收益发放
id
0 20200131361530795941
1 2020013022001147941416389578
2 20200130357321951941
3 20200129353812088941
4 20200128349846566941