data_analyze_platform/LIB/FRONTEND/other/bat_user_relation/main.py

from sqlalchemy import create_engine
import pandas as pd

# 连接数据库
host='rm-bp10j10qy42bzy0q7.mysql.rds.aliyuncs.com'
port=3306
db='qixiang_manage'
user='qx_query'
password='@Qx_query'
engine = create_engine('mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(user,password, host, str(port),db))
sql = "select * from py_battery_rent"
df_rent = pd.read_sql_query(sql, engine)
sql = "select * from py_battery_rent_change"
df_rent_change = pd.read_sql_query(sql, engine)


# 统计截止日期：2021年07月25日 14：00：00
'''
 预处理:
 df_rent:
    1）删除测试电池 GY开头 以及位数不对的电池
    2）删除user_id为空的行
    3) 删除 qrcode为空的行
    4）删除pay_stat 等于3 的未支付订单
    4) 时间戳=0 的值置None
 df_rent_change:
    1）删除测试电池 GY开头 以及位数不对的电池
    2）删除qrcode和new_qrcode为空的行
'''
# df_rent = pd.read_csv("data_rent.csv",sep=',',encoding="ANSI")
# print(len(df_rent))

df_rent = df_rent.dropna(axis=0, how='any', subset=['user_id', 'qrcode'], inplace=False)
df_rent = df_rent[~(df_rent['pay_stat']==3)]
df_rent['id'] = df_rent['id'].apply(lambda x:str(int(x)) if not pd.isnull(x) else None)
df_rent['return_time'] = df_rent['return_time'].apply(lambda x:x+3600*8 if x!=0 else None)
df_rent['pay_time'] = df_rent['pay_time'].apply(lambda x:x+3600*8 if x!=0 else None)
df_rent['get_time'] = df_rent['get_time'].apply(lambda x:x+3600*8 if x!=0 else None)
df_rent['end_time'] = df_rent['end_time'].apply(lambda x:x+3600*8 if x!=0 else None)
df_rent['addtime'] = pd.to_datetime(df_rent['addtime'].values,unit='s')
df_rent['pay_time'] = pd.to_datetime(df_rent['pay_time'].values,unit='s')
df_rent['get_time'] = pd.to_datetime(df_rent['get_time'].values,unit='s')
df_rent['end_time'] = pd.to_datetime(df_rent['end_time'].values,unit='s')
df_rent['return_time'] = pd.to_datetime(df_rent['return_time'].values,unit='s')
df_rent['addtime'] = df_rent['addtime'].apply(lambda x:x.strftime("%Y-%m-%d %H:%M:%S"))
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)
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)
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)
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)
df_rent = df_rent.reset_index(drop=True)
print(len(df_rent))

# df_rent_change = pd.read_csv("data_rent_change.csv",sep=',',encoding="ANSI")
print(len(df_rent_change))
df_rent_change = df_rent_change.dropna(axis=0, how='any', subset=['new_qrcode', 'qrcode'], inplace=False)
print(len(df_rent_change))
df_rent_change = df_rent_change.reset_index(drop=True)
df_rent_change['create_time'] = df_rent_change['create_time'].apply(lambda x:x+3600*8 if x!=0 else None)
df_rent_change['create_time'] = pd.to_datetime(df_rent_change['create_time'].values,unit='s')
df_rent_change['create_time'] = df_rent_change['create_time'].apply(lambda x:x.strftime("%Y-%m-%d %H:%M:%S"))
# 将更换电池的信息，补充至rent中, 旧电池添加一条租用记录和归还记录， 并将订单的pay_time 改为电池更换时间, 
df_groups = df_rent_change.groupby("rent_id")
for name, df_group in df_groups:
    df_group = df_group.sort_values("create_time")
    df_group = df_group.reset_index(drop=True)
    for i in range(0, len(df_group)):
        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']]}))

        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]],
                '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]], 
                'user_id':[df_group.loc[i,'user_id']], 'f_id':[df_group.loc[i,'f_id']]}))
  
        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']

# 生成用来排序的时间列
df_rent = df_rent.reset_index(drop=True)
df_rent['sort_time'] = [None] * len(df_rent)
for i in range(0, len(df_rent)):
   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']
df_rent['sort_time'] = pd.to_datetime(df_rent['sort_time'])
# df_rent.to_csv('ttt.csv')
df = df_rent.copy()
df_res = pd.DataFrame(columns=['sn', 'st', 'et', 'user_id', 'agent_id'])
df_groups = df.groupby("qrcode")
for name, df_group in df_groups:
    
    # 根据sn分组后的电池，首先按照记录时间排序，然后判断用户id是否发生变化，
    df_group = df_group.sort_values("sort_time") # 按照本条记录的生成时间排序
    df_group = df_group.reset_index(drop=True)
    sn = name
    user_id = df_group.loc[0, 'user_id']
    st =  df_group.loc[0, 'pay_time']
    et = None
    for i in range(1,len(df_group)):
        if df_group.loc[i, 'user_id'] == user_id:
            continue
        else:
            et = df_group.loc[i-1, 'return_time'] if not pd.isnull(df_group.loc[i-1, 'return_time']) else None
            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)
            user_id = df_group.loc[i, 'user_id']
            st =  df_group.loc[i, 'pay_time']
            et = None
    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
    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)
df_res.to_csv('result.csv')