import numpy as np
import pandas as pd
from pandasql import sqldf
import datetime
# 全局定义
pysqldf = lambda q: sqldf(q, globals())
#构建数据集
df = pd.DataFrame({"issue_date":["2021-1-31","2021-2-28","2021-3-31","2021-4-30","2021-5-31","2021-6-30","2021-7-31","2021-8-31","2021-9-30","2021-10-31","2021-11-30","2021-12-31"],
"uid":["a1","a2","a1","a3","a4","a3","a5","a3","a4","a2","a4","a2"],
"issue_amount":[50,30,20,13,23,34,50,49,40,10,19,78]}
)
df['issue_date'] = df['issue_date'].astype('datetime64')
场景一:统一不同发放日期下,发放金额汇总和发放客户数去重,并分别按照asc、desc排序
# pysql处理逻辑
q = """
select
issue_date
,sum(issue_amount)
,count(distinct uid)
from df
group by issue_date
order by sum(issue_amount) asc,count(distinct uid) desc;
"""
pysqldf(q)
# pandas处理逻辑
df_r = df.groupby(['issue_date']).agg({'uid' : lambda x: x.nunique(),'issue_amount' : np.sum})
df_r.rename(columns={'uid':'uid_num','issue_amount':'issue_amount_total'},inplace=True)
df_r.sort_values(by=['uid_num','issue_amount_total'],ascending=[True,False])
未完