pandas 复习

pandas 复习

1、读入excel

data=pd.read_excel('D://躺椅垫跟踪//20200601//begin.xlsx',sheet_name=0,header=0,encoding='utf-8')

2、提取部分列到新的dataframe

product_1 = product[['产品SKU','产品名称','最近单价','销售状态','产品等级','产品销售负责人']]

3、merge函数连接

data=pd.merge(left=data,right = product_1,left_on = "sku",right_on = "产品SKU")

4、修改列名

data.rename(columns={'最近单价': '采购价', '产品销售负责人': '销售员'}, inplace=True)

5、groupby函数（有问题未解决）

analytics_1= analytics[['SKU','3天日均销量','7天日均销量','14天日均销量','30天日均销量']].groupby("SKU").sum()

6、整列取整可以用data.astype(int)

data['FBA仓可售库存']=data['FBA仓可售库存'].astype(int)

7、替换一列中的某个字符串

data['可卖天数']=data['可卖天数'].apply(lambda x:re.sub('nan','9999',x))

8、计算系统时间并且存取的时候采用这个时间

#now=datetime.datetime.now().strftime('%Y%m%d')
now = datetime.datetime.now()
end_time = datetime.datetime.strftime(now, '%Y-%m-%d')
data.to_excel('D://躺椅垫跟踪//20200601//躺椅垫产品销售跟踪计算'+str(end_time)+'.xlsx',index=None)

9、计算系统日期

now=datetime.date.today()
print(now)

项目代码

import pandas as pd
import re
import datetime
#data=pd.read_excel('D://躺椅垫跟踪//20200604//躺椅垫产品销售跟踪计算表.xlsm',sheet_name=0,header=0,encoding='utf-8')
data=pd.read_excel('D://躺椅垫跟踪//20200601//begin.xlsx',sheet_name=0,header=0,encoding='utf-8')
product=pd.read_excel('D://躺椅垫跟踪//20200601//exportAllProduct_2020-06-10_105929000.xlsx',sheet_name=0,header=0,encoding='utf-8',converters={'产品SKU':str})
analytics=pd.read_excel('D://躺椅垫跟踪//20200601//产品销售分析20200610091259.xlsx',sheet_name=0,header=0,encoding='utf-8')
analytics.dtypes
data.dtypes
product.dtypes
product_1 = product[['产品SKU','产品名称','最近单价','销售状态','产品等级','产品销售负责人']]
data=pd.merge(left=data,right = product_1,left_on = "sku",right_on = "产品SKU")
data.drop(['产品SKU'],axis=1,inplace=True)

data.rename(columns={'最近单价': '采购价', '产品销售负责人': '销售员'}, inplace=True)
analytics_1= analytics[['SKU','3天日均销量','7天日均销量','14天日均销量','30天日均销量']].groupby("SKU").sum()
data=pd.merge(left=data,right=analytics_1,left_on='sku',right_on=analytics_1.index)

warehouse=pd.read_excel('D://躺椅垫跟踪//20200601//warehouse.xlsx',encoding='utf-8')
analytics['在途待上架']=analytics['在途']+analytics['待上架']
analytics_2=analytics[['SKU','仓库','库存','在途待上架']]
analytics_ct=pd.merge(left=analytics_2,right=warehouse,left_on='仓库',right_on='仓库')

cn=analytics_ct.iloc[(analytics_ct['分仓']=='中国仓').values]
cn_kc=cn[['SKU','库存']].groupby(by="SKU",as_index=None).sum()
cn_zt=cn[['SKU','在途待上架']].groupby(by="SKU",as_index=None).sum()

uk=analytics_ct.iloc[(analytics_ct['分仓']=='英国仓').values]
uk_kc=uk[['SKU','库存']].groupby(by="SKU",as_index=None).sum()
uk_zt=uk[['SKU','在途待上架']].groupby(by="SKU",as_index=None).sum()

fba=analytics_ct.iloc[(analytics_ct['分仓']=='FBA仓').values]
fba_kc=fba[['SKU','库存']].groupby(by="SKU",as_index=None).sum()
fba_zt=fba[['SKU','在途待上架']].groupby(by="SKU",as_index=None).sum()

g=analytics_ct.iloc[(analytics_ct['分仓']=='谷仓英国仓').values]
g_kc=g[['SKU','库存']].groupby(by="SKU",as_index=None).sum()
g_zt=g[['SKU','在途待上架']].groupby(by="SKU",as_index=None).sum()
#库存
data['FBA仓可售库存']=round(data['采购价']*0,0)
data=pd.merge(left=data,right=cn_kc,left_on='sku',right_on='SKU')
data.rename(columns={'库存':'中国仓可售库存'},inplace=True)
data=pd.merge(left=data,right=uk_kc,left_on='sku',right_on='SKU')
data.rename(columns={'库存':'英国仓可售库存'},inplace=True)
data['谷仓可售库存']=round(data['FBA仓可售库存']*0,0)
data.drop(['SKU_x','SKU_y'],axis=1,inplace=True)

#在途
data['FBA仓在途']=round(data['采购价']*0,0)
data=pd.merge(left=data,right=cn_zt,left_on='sku',right_on='SKU')
data.rename(columns={'在途待上架':'中国仓在途'},inplace=True)

data=pd.merge(left=data,right=uk_zt,on='SKU')
data.rename(columns={'在途待上架':'英国仓在途'},inplace=True)
data['谷仓在途']=round(data['采购价']*0,0)

data['日销']=data['7天日均销量']

data['FBA仓可售库存']=data['FBA仓可售库存'].astype(int)
data['谷仓可售库存']=data['谷仓可售库存'].astype(int)
data['谷仓在途']=data['谷仓在途'].astype(int)
data['FBA仓在途']=data['FBA仓在途'].astype(int)
data['总库存']=data['FBA仓可售库存']+data['中国仓可售库存']+data['英国仓可售库存']+data['谷仓可售库存']+data['FBA仓在途']+data['中国仓在途']+data['英国仓在途']+data['谷仓在途']
data['总在途数量']=data['FBA仓在途']+data['中国仓在途']+data['英国仓在途']+data['谷仓在途']
data['可卖天数']=data['总库存']/data['日销']


data['可卖天数']=data['可卖天数'].astype(str)
data['可卖天数']=data['可卖天数'].apply(lambda x:re.sub('nan','9999',x))
data['可卖天数']=data['可卖天数'].apply(lambda x:re.sub('inf','9999',x))
data['可卖天数']=data['可卖天数'].astype(float)
data['可卖天数']=data['可卖天数'].apply(lambda x:round(x,0))
data['可卖天数']=data['可卖天数'].astype(int)

data['库存金额']=data['采购价']*data['总库存']
data['销售趋势']=1

for i in range(0,len(data)):
    if (data['3天日均销量'][i]>data['7天日均销量'][i])and(data['7天日均销量'][i]>data['14天日均销量'][i])and(data['14天日均销量'][i]>data['30天日均销量'][i]):
        data['销售趋势'][i]='持续上升'
    elif (data['3天日均销量'][i] < data['7天日均销量'][i]) and (data['7天日均销量'][i] < data['14天日均销量'][i]) and (
            data['14天日均销量'][i] < data['30天日均销量'][i]):
        data['销售趋势'][i] = '持续下降'
    else:
        if (data['3天日均销量'][i]+data['7天日均销量'][i])>(data['14天日均销量'][i]+data['30天日均销量'][i]):
            data['销售趋势'][i] = '波动上升'
        elif (data['3天日均销量'][i]+data['7天日均销量'][i])<(data['14天日均销量'][i]+data['30天日均销量'][i]):
            data['销售趋势'][i] = '波动下降'
        else:
            data['销售趋势'][i] ='无'

profit=pd.read_csv('D://躺椅垫跟踪//20200601//全平台业绩本月(1).csv',encoding='utf-8')
profit_1=profit[['产品代码','毛利']].groupby(by='产品代码',as_index=None).sum()
profit_1.rename(columns={'产品代码': 'sku'}, inplace=True)
data=pd.merge(left=data,right=profit_1,left_on='SKU',right_on='sku')
data.drop(['sku_y','SKU'],axis=1,inplace=True)
data.rename(columns={'sku_x':'sku'},inplace=True)

now = datetime.datetime.now()
end_time = datetime.datetime.strftime(now, '%Y-%m-%d')
data.to_excel('D://躺椅垫跟踪//20200601//躺椅垫产品销售跟踪计算'+str(end_time)+'.xlsx',index=None)