main.py 6.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110
  1. from sqlalchemy import create_engine
  2. import pandas as pd
  3. # 连接数据库
  4. host='rm-bp10j10qy42bzy0q7.mysql.rds.aliyuncs.com'
  5. port=3306
  6. db='qixiang_manage'
  7. user='qx_query'
  8. password='@Qx_query'
  9. engine = create_engine('mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(user,password, host, str(port),db))
  10. sql = "select * from py_battery_rent"
  11. df_rent = pd.read_sql_query(sql, engine)
  12. sql = "select * from py_battery_rent_change"
  13. df_rent_change = pd.read_sql_query(sql, engine)
  14. # 统计截止日期:2021年07月25日 14:00:00
  15. '''
  16. 预处理:
  17. df_rent:
  18. 1)删除测试电池 GY开头 以及位数不对的电池
  19. 2)删除user_id为空的行
  20. 3) 删除 qrcode为空的行
  21. 4)删除pay_stat 等于3 的未支付订单
  22. 4) 时间戳=0 的值置None
  23. df_rent_change:
  24. 1)删除测试电池 GY开头 以及位数不对的电池
  25. 2)删除qrcode和new_qrcode为空的行
  26. '''
  27. # df_rent = pd.read_csv("data_rent.csv",sep=',',encoding="ANSI")
  28. # print(len(df_rent))
  29. df_rent = df_rent.dropna(axis=0, how='any', subset=['user_id', 'qrcode'], inplace=False)
  30. df_rent = df_rent[~(df_rent['pay_stat']==3)]
  31. df_rent['id'] = df_rent['id'].apply(lambda x:str(int(x)) if not pd.isnull(x) else None)
  32. df_rent['return_time'] = df_rent['return_time'].apply(lambda x:x+3600*8 if x!=0 else None)
  33. df_rent['pay_time'] = df_rent['pay_time'].apply(lambda x:x+3600*8 if x!=0 else None)
  34. df_rent['get_time'] = df_rent['get_time'].apply(lambda x:x+3600*8 if x!=0 else None)
  35. df_rent['end_time'] = df_rent['end_time'].apply(lambda x:x+3600*8 if x!=0 else None)
  36. df_rent['addtime'] = pd.to_datetime(df_rent['addtime'].values,unit='s')
  37. df_rent['pay_time'] = pd.to_datetime(df_rent['pay_time'].values,unit='s')
  38. df_rent['get_time'] = pd.to_datetime(df_rent['get_time'].values,unit='s')
  39. df_rent['end_time'] = pd.to_datetime(df_rent['end_time'].values,unit='s')
  40. df_rent['return_time'] = pd.to_datetime(df_rent['return_time'].values,unit='s')
  41. df_rent['addtime'] = df_rent['addtime'].apply(lambda x:x.strftime("%Y-%m-%d %H:%M:%S"))
  42. df_rent['pay_time'] = df_rent['pay_time'].apply(lambda x:x.strftime("%Y-%m-%d %H:%M:%S") if not pd.isna(x) else x)
  43. df_rent['get_time'] = df_rent['get_time'].apply(lambda x:x.strftime("%Y-%m-%d %H:%M:%S") if not pd.isna(x) else x)
  44. df_rent['end_time'] = df_rent['end_time'].apply(lambda x:x.strftime("%Y-%m-%d %H:%M:%S") if not pd.isna(x) else x)
  45. df_rent['return_time'] = df_rent['return_time'].apply(lambda x:x.strftime("%Y-%m-%d %H:%M:%S") if not pd.isna(x) else x)
  46. df_rent = df_rent.reset_index(drop=True)
  47. print(len(df_rent))
  48. # df_rent_change = pd.read_csv("data_rent_change.csv",sep=',',encoding="ANSI")
  49. print(len(df_rent_change))
  50. df_rent_change = df_rent_change.dropna(axis=0, how='any', subset=['new_qrcode', 'qrcode'], inplace=False)
  51. print(len(df_rent_change))
  52. df_rent_change = df_rent_change.reset_index(drop=True)
  53. df_rent_change['create_time'] = df_rent_change['create_time'].apply(lambda x:x+3600*8 if x!=0 else None)
  54. df_rent_change['create_time'] = pd.to_datetime(df_rent_change['create_time'].values,unit='s')
  55. df_rent_change['create_time'] = df_rent_change['create_time'].apply(lambda x:x.strftime("%Y-%m-%d %H:%M:%S"))
  56. # 将更换电池的信息,补充至rent中, 旧电池添加一条租用记录和归还记录, 并将订单的pay_time 改为电池更换时间,
  57. df_groups = df_rent_change.groupby("rent_id")
  58. for name, df_group in df_groups:
  59. df_group = df_group.sort_values("create_time")
  60. df_group = df_group.reset_index(drop=True)
  61. for i in range(0, len(df_group)):
  62. df_rent = df_rent.append(pd.DataFrame({'addtime':[df_group.loc[i,'create_time']],'qrcode':[df_group.loc[i,'qrcode']], 'return_time':[df_group.loc[i,'create_time']],'user_id':[df_group.loc[i,'user_id']], 'f_id':[df_group.loc[i,'f_id']]}))
  63. df_rent = df_rent.append(pd.DataFrame({'addtime':[df_rent.loc[df_rent[(df_rent['id']==str(int(df_group.loc[i,'rent_id'])))].index,'pay_time'].values[0]],
  64. 'qrcode':[df_group.loc[i,'qrcode']], 'pay_time':[df_rent.loc[df_rent[(df_rent['id']==str(int(df_group.loc[i,'rent_id'])))].index,'pay_time'].values[0]],
  65. 'user_id':[df_group.loc[i,'user_id']], 'f_id':[df_group.loc[i,'f_id']]}))
  66. df_rent.loc[df_rent[(df_rent['id']==str(int(df_group.loc[i,'rent_id'])))].index,'pay_time'] = df_group.loc[i,'create_time']
  67. # 生成用来排序的时间列
  68. df_rent = df_rent.reset_index(drop=True)
  69. df_rent['sort_time'] = [None] * len(df_rent)
  70. for i in range(0, len(df_rent)):
  71. df_rent.loc[i, 'sort_time'] = df_rent.loc[i, 'pay_time'] if not pd.isnull(df_rent.loc[i, 'pay_time']) else df_rent.loc[i, 'return_time']
  72. df_rent['sort_time'] = pd.to_datetime(df_rent['sort_time'])
  73. # df_rent.to_csv('ttt.csv')
  74. df = df_rent.copy()
  75. df_res = pd.DataFrame(columns=['sn', 'st', 'et', 'user_id', 'agent_id'])
  76. df_groups = df.groupby("qrcode")
  77. for name, df_group in df_groups:
  78. # 根据sn分组后的电池,首先按照记录时间排序,然后判断用户id是否发生变化,
  79. df_group = df_group.sort_values("sort_time") # 按照本条记录的生成时间排序
  80. df_group = df_group.reset_index(drop=True)
  81. sn = name
  82. user_id = df_group.loc[0, 'user_id']
  83. st = df_group.loc[0, 'pay_time']
  84. et = None
  85. for i in range(1,len(df_group)):
  86. if df_group.loc[i, 'user_id'] == user_id:
  87. continue
  88. else:
  89. et = df_group.loc[i-1, 'return_time'] if not pd.isnull(df_group.loc[i-1, 'return_time']) else None
  90. df_res = df_res.append(pd.DataFrame({'sn':[sn], 'st':[st], 'et':[et], 'user_id':[user_id], 'agent_id':[df_group.loc[i-1, 'f_id']]}), ignore_index=True)
  91. user_id = df_group.loc[i, 'user_id']
  92. st = df_group.loc[i, 'pay_time']
  93. et = None
  94. et = df_group.loc[len(df_group)-1, 'return_time'] if not pd.isnull(df_group.loc[len(df_group)-1, 'return_time']) else None
  95. df_res = df_res.append(pd.DataFrame({'sn':[sn], 'st':[st], 'et':[et], 'user_id':[user_id], 'agent_id':[df_group.loc[len(df_group)-1, 'f_id']]}), ignore_index=True)
  96. df_res.to_csv('result.csv')
  97. <<<<<<< HEAD
  98. =======
  99. >>>>>>> master