def run5():
df = du_old_excel('奖品发放')
df['奖励'] = df['金额'].apply(lambda x: str(x) + '元') + df['类型']
df = pd.DataFrame(df.groupby(['奖励']).size()).T
df.reset_index(inplace=True)
list1 = ['1元红包', '2元红包', '5元红包', '10元红包', '20元红包', '30元红包']
try:
df = df[list1]
except KeyError:
# 判定红包类型是否存在于list1
error = [l for l in list1 if l not in list(df.columns)]
# 把不存在的部分保存进df
for x in error:
df[x] = 0
df = df[list1]
df.fillna(0, inplace=True)
df['总和'] = df.apply(lambda x: x.sum(), axis=1)
print('第五个表运行完毕.')
return df
def run5():
df = du_old_excel('奖品发放')
df['奖励'] = df['金额'].apply(lambda x: str(x) + '元') + df['类型']
df['日期'] = df['兑换日期'].apply(lambda x: x[:10])
df = pd.DataFrame(df.groupby(['日期', '奖励']).size())
df.reset_index(inplace=True)
df = pd.pivot_table(df, values=0, index='日期', columns='奖励')
list1 = ['2元红包', '5元红包', '10元红包', '20元红包', '50元红包', '10元话费', '20元话费', '50元话费', '100元话费']
try:
df = df[list1]
except KeyError:
# 判定红包类型是否存在于list1
error = [l for l in list1 if l not in list(df.columns)]
# 把不存在的部分保存进df
for x in error:
df[x] = 0
df = df[list1]
df.fillna(0, inplace=True)
df['总和'] = df.apply(lambda x: x.sum(), axis=1)
print('\n第五个表运行完毕……')
return df
def run1():
df = du_old_excel('充值')
df_map = pd.read_excel('C:\\Users\Administrator\Desktop\map.xlsx')
df_map = df_map[['product_id', 'Flag']]
df['time'] = df['pay_time'].apply(lambda x: x.split(' ')[0])
df['week'] = df['time'].apply(lambda x: fx(x))
# 合并匹配表
df = pd.merge(left=df, right=df_map, on='product_id', how='left')
df = pd.DataFrame(
[df.groupby(['Flag']).size(),
df.groupby(['Flag'])['amount'].sum()]).T
df.reset_index(inplace=True)
df.sort_values(by=df.columns[-2], ascending=0, inplace=True)
df.fillna(0, inplace=True)
df.columns = ['类型', '充值次数', '充值金额']
df['次数占比'] = df['充值次数'] / (df['充值次数'].sum())
df['金额占比'] = df['充值金额'] / (df['充值金额'].sum())
print('第一个表运行完毕')
return df
def run6():
df = du_old_excel('我要赚钱')
df['gen_time'] = df['gen_time'].apply(lambda x: x.split(' ')[0])
del df['invite_id']
del df['player_id']
df.set_index('gen_time', inplace=True)
df6 = pd.DataFrame(df.sum()).T
df6['被推广用户人数'] = df.shape[0]
df6['盈亏'] = df6['recharge'] - df6['get_redgem'] / 10 - df6[
'redgem'] / 10 - df6['gold'] / 20000
df6.rename(columns={
'get_redgem': '获得红宝石',
'recharge': '充值金额',
'redgem': '奖励红宝石',
'gold': '奖励金币',
0: '被推广用户人数'
},
inplace=True)
df6 = df6[['被推广用户人数', '获得红宝石', '充值金额', '奖励红宝石', '奖励金币', '盈亏']]
print('第六个表运行完毕.')
return df6
def run6():
df = du_old_excel('我要赚钱')
df['gen_time'] = df['gen_time'].apply(lambda x: x.split(' ')[0])
del df['invite_id']
del df['player_id']
df.set_index('gen_time', inplace=True)
df_s = pd.DataFrame(df.groupby('gen_time').size())
df.reset_index(inplace=True)
df = df.groupby('gen_time').sum()
df['盈亏'] = df['recharge'] - df['get_redgem'] / 10 - df['redgem'] / 10 - df[
'gold'] / 20000
df.reset_index(inplace=True)
df = pd.merge(left=df_s, right=df, on='gen_time', how='left')
df.rename(columns={
'get_redgem': '获得红宝石',
'recharge': '充值金额',
'redgem': '奖励红宝石',
'gold': '奖励金币',
0: '被推广用户人数'
},
inplace=True)
print('\n第六个表运行完毕……')
return df
def run2():
try:
# 读取数据
df = du_old_excel('充值2天')
df3 = du_old_excel('注册CPA')
except FileNotFoundError:
print('\n缺少运行数据，请先下载……')
exit()
df = df[(df['channel'] == 800106) | (df['channel'] == 800113)]
# 取str时间函数
def time_c(df, col, sp=' '):
df[col] = df[col].apply(lambda x: pd.to_datetime(str(x).split(sp)[0]))
return df
# 取出充值时间为昨天的数据
df = time_c(df, 'pay_time')
df = df[df['pay_time'] == pd.to_datetime(yesterday)]
# 注册数据清洗用于匹配
df3 = time_c(df3, '注册时间')
df3['flag'] = 'new'
df3.rename(columns={'用户ID': 'player_id'}, inplace=True)
df3 = df3[['player_id', 'flag']]
# 匹配到充值数据
df = pd.merge(left=df, right=df3, on='player_id', how='left')
df = df[df['flag'].notnull()]
df = pd.DataFrame([
df.groupby('channel')['amount'].sum(),
df.groupby('channel')['player_id'].unique()
]).T
df['新用户付费人数'] = df['player_id'].apply(lambda x: len(x))
df['(付费率)'] = ''
df = df[['新用户付费人数', '(付费率)', 'amount']]
df.to_excel(or_path + 'CPA注册新用户付费率.xlsx')
exit()
return df
def run2():
df = du_old_excel('充值')
df3 = du_old_excel('注册')
# 提取充值数据的日期
df['day'] = df['pay_time'].apply(lambda x: x.split(' ')[0].split('/')[2])
df['on'] = df['player_id'].apply(lambda x: str(x)) + '|' + df['day']
# # 整理前2天当日的注册数据
df3['Flag'] = 'new'
df3.rename(columns={'用户ID': 'player_id'}, inplace=True)
df3['day'] = df3['注册时间'].apply(lambda x: x.split(' ')[0].split('/')[2])
df3['on'] = df3['player_id'].apply(lambda x: str(x)) + '|' + df3['day']
df3 = df3[['on', 'Flag']]
df = pd.merge(left=df, right=df3, on='on', how='left')
df['Flag'].fillna('old', inplace=True)
i = 0
df_form = pd.DataFrame()
def df_f(df):
# 人数计算
df_form.loc[i, '新用户量'] = len(
df[df['Flag'] == 'new']['player_id'].unique())
df_form.loc[i, '总用户量'] = len(df['player_id'].unique())
df_form.loc[i, '新用户占比'] = '%.2f%%' % (df_form.loc[i, '新用户量'] /
df_form.loc[i, '总用户量'] * 100)
# 金额消费计算
df_form.loc[i, '新用户消费金额'] = df[df['Flag'] == 'new']['amount'].sum()
df_form.loc[i, '总消费'] = df['amount'].sum()
df_form.loc[i, '新用户消费占比'] = '%.2f%%' % (df_form.loc[i, '新用户消费金额'] /
df_form.loc[i, '总消费'] * 100)
return df_form
df_form = df_f(df)
# 删除多余2列
del df_form['总用户量']
del df_form['总消费']
print('第二个表运行完毕.')
return df_form
def run6():
df = du_old_excel('我要赚钱')
df['奖励一状态'].replace({'已完成': 1, '未完成': 0}, inplace=True)
df['奖励二状态'].replace({'已完成': 1, '未完成': 0}, inplace=True)
df['奖励三状态'].replace({'已完成': 1, '未完成': 0}, inplace=True)
df['time'] = df['关系建立时间'].apply(lambda x: x.split(' ')[0])
df1 = df.groupby(['time'])['奖励一状态'].sum()
df2 = df.groupby(['time'])['奖励二状态'].sum()
df3 = df.groupby(['time'])['奖励三状态'].sum()
df4 = df.groupby(['time']).size()
df = pd.DataFrame([df1, df2, df3, df4]).T
df.reset_index(inplace=True)
df['day'] = df['time'].apply(lambda x: int(x.split('/')[-1]))
## 因为后台的对【关系建立时间】的修改，这里不需要再取出时间段了
# df = df[(df['day'] >= int(str(bef_yesterday)[-2:])) & (df['day'] <= int(str(yesterday)[-2:]))]
# print(df)
# exit()
df.rename(columns={'Unnamed 0': '总和'}, inplace=True)
df['一档完成率'] = (df['奖励一状态'] /
df['总和']).apply(lambda x: '%.2f%%' % (x * 100))
df['二档完成率'] = (df['奖励二状态'] /
df['总和']).apply(lambda x: '%.2f%%' % (x * 100))
df['三档完成率'] = (df['奖励三状态'] /
df['总和']).apply(lambda x: '%.2f%%' % (x * 100))
df = df[[
'time', '奖励一状态', '奖励二状态', '奖励三状态', '一档完成率', '二档完成率', '三档完成率', '总和'
]]
df.rename(columns={
'奖励一状态': '奖励一完成人数',
'奖励二状态': '奖励二完成人数',
'奖励三状态': '奖励三完成人数',
'time': '日期'
},
inplace=True)
print('\n第六个表运行完毕……')
# print(df)
# exit()
return df
def run6():
df = du_old_excel('我要赚钱')
df['奖励一状态'].replace({'已完成': 1, '未完成': 0}, inplace=True)
df['奖励二状态'].replace({'已完成': 1, '未完成': 0}, inplace=True)
df['奖励三状态'].replace({'已完成': 1, '未完成': 0}, inplace=True)
del df['被推广玩家']
del df['推广玩家']
df.set_index('关系建立时间', inplace=True)
df = pd.DataFrame(df.sum()).T
print('\n第六个表运行完毕……')
return df
def run5():
df = du_old_excel('奖品发放')
df['奖励'] = df['金额'].apply(lambda x: str(x) + '元') + df['类型']
df = pd.DataFrame(df.groupby(['奖励']).size()).T
df.reset_index(inplace=True)
try:
df = df[['2元红包', '5元红包', '8元红包', '10元红包', '10元话费', '100元话费']]
except KeyError:
df = df[['2元红包', '5元红包', '8元红包', '10元红包', '10元话费']]
df['100元话费'] = 0
df.fillna(0, inplace=True)
df['总和'] = df.apply(lambda x: x.sum(), axis=1)
print('第五个表运行完毕.')
return df
def run6():
df = du_old_excel('我要赚钱')
df['奖励一状态'].replace({'已完成': 1, '未完成': 0}, inplace=True)
df['奖励二状态'].replace({'已完成': 1, '未完成': 0}, inplace=True)
df['奖励三状态'].replace({'已完成': 1, '未完成': 0}, inplace=True)
or_num = df.shape[0]
df = pd.DataFrame(df.sum()).T
df = df[['奖励一状态', '奖励二状态', '奖励三状态']]
df['总和'] = or_num
df['一档完成率'] = (df['奖励一状态'] / df['总和']).apply(lambda x: '%.2f%%' % (x * 100))
df['二档完成率'] = (df['奖励二状态'] / df['总和']).apply(lambda x: '%.2f%%' % (x * 100))
df['三档完成率'] = (df['奖励三状态'] / df['总和']).apply(lambda x: '%.2f%%' % (x * 100))
df = df[['奖励一状态', '奖励二状态', '奖励三状态', '一档完成率', '二档完成率', '三档完成率', '总和']]
print('第六个表运行完毕.')
return df