本文主要是介绍Python——pd.pivot_table，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
import pandas as pd
import time
import datetime
from dateutil.relativedelta import relativedelta
import numpy as npnow = datetime.datetime.now() #现在的时间
last_d_0 = now + relativedelta(days = -1) # 前 1 天——今天
last_d_1 = now + relativedelta(days = -3) # 前 3 天—— 昨天业绩的前两天（今日）
last_d_2= now + relativedelta(days = -4) # 前 4 天—— 昨天业绩的前三天（昨日）
last_d_3 = now + relativedelta(days = -9) # 前 9 天—— 前一周
last_d_4 = now + relativedelta(days= -16) # 前 16天—— 前两周
last_d_5 = last_d_1+ relativedelta(months= -1) # 前一月
last_d_6 = last_d_2+ relativedelta(months= -2) # 前两月today = datetime.datetime.strftime(now,'%Y%m%d')
last_d_0 = datetime.datetime.strftime(last_d_0,'%Y%m%d')
last_d_1 = datetime.datetime.strftime(last_d_1,'%Y-%m-%d')
last_d_2 = datetime.datetime.strftime(last_d_2,'%Y-%m-%d')
last_d_3 = datetime.datetime.strftime(last_d_3,'%Y-%m-%d')
last_d_4 = datetime.datetime.strftime(last_d_4,'%Y-%m-%d')
last_d_5 = datetime.datetime.strftime(last_d_5,'%Y-%m-%d')
last_d_6 = datetime.datetime.strftime(last_d_6,'%Y-%m-%d')path = 'D:/众结资料/1日常工作内容/每日销售开发业绩(Python)/'+last_d_0+'/自定义sku报表本月.xlsx'
print(path)
path1 = 'D:/众结资料/1日常工作内容/每日销售开发业绩(Python)/'+last_d_0+'/自定义sku报表上月.xlsx'
path2 = '自定义sku报表本月.xlsx'fh = open(path , 'rb')
data_last_m_1 = pd.read_excel(fh,skiprows=[0, 1, 2, 3, 4])
data_last_m_1=data_last_m_1[data_last_m_1['平台']=='ebay']
data_last_m_1=data_last_m_1[data_last_m_1['订单类型']=='sale']fh1 = open(path1, 'rb')
data_last_m_2 = pd.read_excel(fh1,skiprows=[0, 1, 2, 3, 4])
data_last_m_2=data_last_m_2[data_last_m_2['平台']=='ebay']
data_last_m_2=data_last_m_2[data_last_m_2['订单类型']=='sale']fh2 = open(path2, 'rb')
data_last_m_3 = pd.read_excel(fh2,skiprows=[0, 1, 2, 3, 4])
data_last_m_3=data_last_m_3[data_last_m_3['平台']=='ebay']
data_last_m_3=data_last_m_3[data_last_m_3['订单类型']=='sale']result = pd.concat([data_last_m_2,data_last_m_1,data_last_m_3],axis=0)
result.drop_duplicates()
result.index = range(len(result))
# result['时间'] = [datetime.datetime.strftime(datetime.datetime.strptime(result['付款时间'][i],'%Y-%m-%d %H:%M:%S'),'%Y-%m-%d') for i in range(len(result))]
result['时间'] = [datetime.datetime.strftime(result['付款时间'][i],'%Y-%m-%d') for i in range(len(result))]result_w_1 = result[result['时间'] >= last_d_3]
result_w_1 = result_w_1[result_w_1['时间'] <= last_d_1]
print("前一周订单"+last_d_3+" " +last_d_1)
result_w_1 = pd.pivot_table(result_w_1, index=['平台账号', '站点'], values=['订单总金额(包含客户运费、平台补贴)','数量'],aggfunc=np.sum, fill_value=0).reset_index()
result_w_1.to_excel('每日订单/前一周订单.xlsx',index=None)#前两周
result_w_2 = result[result['时间'] >= last_d_4]
result_w_2 = result_w_2 [result_w_2 ['时间'] < last_d_3]
print("前两周订单"+last_d_4+" " +last_d_3)
result_w_2 = pd.pivot_table(result_w_2, index=['平台账号', '站点'], values=['订单总金额(包含客户运费、平台补贴)','数量'],aggfunc=np.sum, fill_value=0).reset_index()
result_w_2.to_excel('每日订单/前两周订单.xlsx',index=None)# 今日订单
result_d_1 = result[result['时间'] >= last_d_1]
print("今日订单"+last_d_1)
result_d_1 = pd.pivot_table(result_d_1, index=['平台账号', '站点'], values=['订单总金额(包含客户运费、平台补贴)','数量'],aggfunc=np.sum, fill_value=0).reset_index()
result_d_1.to_excel('每日订单/今日订单.xlsx',index=None)#昨日订单
result_d_2 = result[result['时间'] >= last_d_2 ]
result_d_2 = result_d_2[result_d_2['时间'] < last_d_1 ]
print("昨日订单"+last_d_2)
result_d_2 = pd.pivot_table(result_d_2, index=['平台账号', '站点'], values=['订单总金额(包含客户运费、平台补贴)','数量'],aggfunc=np.sum, fill_value=0).reset_index()
result_d_2.to_excel('每日订单/昨日订单.xlsx',index=None)#前一个月订单
result_d_3 = result[result['时间'] >= last_d_5 ]
# result_d_3 = result_d_3[result_d_3['时间'] < last_d_1 ]
print("前一月订单"+last_d_5)
result_d_3 = pd.pivot_table(result_d_3, index=['平台账号'], values=['订单总金额(包含客户运费、平台补贴)'],aggfunc=[np.sum,len], fill_value=0).reset_index()
result_d_3.to_excel('每日订单/前一月订单.xlsx')#前两个月订单
result_d_4 = result[result['时间'] >= last_d_6 ]
result_d_4 = result_d_4[result_d_4['时间'] < last_d_5 ]
print("前两月订单"+last_d_6+" " +last_d_5)
result_d_4 = pd.pivot_table(result_d_4, index=['平台账号'], values=['订单总金额(包含客户运费、平台补贴)'],aggfunc=[np.sum,len], fill_value=0).reset_index()
result_d_4.to_excel('每日订单/前两月订单.xlsx')#前一个周站点每天订单
result_d_5 = result[result['时间'] >= last_d_3]
# result_d_3 = result_d_3[result_d_3['时间'] < last_d_1 ]
print("前一周站点每天订单"+last_d_5)
## python _ excel index:行 columns:列 values:值 aggfunc:是求和还是次数、平均数
result_d_5 = pd.pivot_table(result_d_5, index=['站点','国家'], columns=['时间'],values=['订单总金额(包含客户运费、平台补贴)','毛利','平台账号'],aggfunc={"订单总金额(包含客户运费、平台补贴)":np.sum,"毛利":np.sum,"平台账号":len}, fill_value=0).reset_index()
result_d_5.to_excel('每日订单/前一周站点每天订单.xlsx')#前一个月每天订单
result_d_5 = result[result['时间'] > last_d_5 ]
# result_d_3 = result_d_3[result_d_3['时间'] < last_d_1 ]
print("前一月sku每天订单"+last_d_5)
result_d_5 = pd.pivot_table(result_d_5, index=['平台账号','站点'], columns=['时间'],values=['订单总金额(包含客户运费、平台补贴)'],aggfunc=[np.sum], fill_value=0).reset_index()
result_d_5.to_excel('每日订单/前一月sku每天订单.xlsx')这篇关于Python——pd.pivot_table的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
