文件： https://wwr.lanzoui.com/iUXX1pply6b
密码:8jir

 

脚本 如下：

import  pandas  as pd
import numpy as np
import datetime
df=pd.read_excel(r'E:\桌面\会员.xlsx')#这个会直接默认读取到这个Excel的第一个表单

# 取会员 
df1 = df[df['HUIYUAN'].str.contains('Y')]

# 总的天数
row = int(df1.shape[0]/9) + 1
print(row)


# 格式化指标
cus_T = [[],[],[]]
cus_0 = [[],[],[]]
cus_1 = [[],[],[]]
DAY = []
for n in range(row):
    
    #初始化日期!!!!!!!!!!!  不然DATE会变成str
    DATE = datetime.datetime.strptime('2021-05-24','%Y-%m-%d')
    # 更新日期
    i=datetime.timedelta(days=n)
    print(i)
    DATE = (DATE + i).strftime('%Y-%m-%d')
    print(DATE)
    DAY = np.hstack((DAY,DATE))
    
    
    #取天
    daliy = df1[df1['ORDER_DATE'].str.contains(DATE)]
    
    # 开始排序
    d1 = daliy.sort_values(['CUS_FLAG'], ascending=[True])
    d2 = d1.sort_values(['ZB'], ascending=[True])
    
    #  不含直播
    A = d2[d2['ZB'].str.contains('0')][['人数']]

    #  含直播
    B = d2[d2['ZB'].str.contains('1')][['人数']]

    #  TTL
    C = d2[d2['ZB'].str.contains('TTL')][['人数']]   
    
    #  开始增加 （TTL 不含直播）数列
    cus_T = np.hstack((cus_T,C))   
    cus_0 = np.hstack((cus_0,A))
    
    # 判断直播data
    R1 = np.array(B);   R2 = np.append(R1,10)
    if R2[0] == 10: B = [[0],[0],[0]]
    cus_1 = np.hstack((cus_1,B))
    

TTL = []
TTL = np.vstack((cus_T,cus_0,cus_1))
TTL = TTL.astype(int)# 转类型 int
print(TTL)


# 开始写入excel
data_df = pd.DataFrame(TTL)
data_df.columns = DAY
data_df.index = ['TTL_new','TTL_old','TTL_t','0_new','0_old','0_t','1_new','1_old','1_t']

writer = pd.ExcelWriter('E:\桌面\data.xlsx')
data_df.to_excel(writer,float_format='%.5f')
writer.save()