파이썬_판매 분석1 (주간 매출, 품목별 매출 비중, 아이템별 TOP 판매 제품 분석)

### 날짜 포맷 세팅###

#년도 제외 오늘 날짜(월일)
from datetime import date
checkdate=date.today().strftime('%m%d')
checkdate
#년도 제외 오늘 날짜(월일)
from datetime import date
checkdate=date.today().strftime('%m%d')
checkdate
'0404'
#년도 포함 오늘 날짜(년도.월.일)
from datetime import date
checkdate1=date.today().strftime('%Y.%m.%d')
checkdate1
'2022.04.04'
#주차
from datetime import date
today = date.today()
thisweek = today.isocalendar()[1]+1
thisweek
15
from datetime import date
today = date.today()
thisyear = today.isocalendar()[0]
thisyear
2022
thisweek1 = str(thisyear) + '-' + str(thisweek)
thisweek1
'2022-15'
from datetime import date
today = date.today()
lastweek = today.isocalendar()[1]
lastweek
14
lastweek1 = str(thisyear) + '-' + str(lastweek)
lastweek1
'2022-14'

### POR점검###

import pandas as pd
import numpy as np
por=pd.read_excel("C:\\Users\\AD0246\\Desktop\\TRACKING\\POR 19S~ HEAD 0321.xls", header=0) #날짜 직접입력
por=por.dropna(axis=0, subset=['스타일']) #스타일#공란일경우DROP

por=por[por['ITEM'].isin(['CP','CB','WM','BN','HT','SC','MC'])] #특정품목만 필터

por['입고수량']=por['입고수량'].fillna(0) #공란일 경우, 숫자 '0'기재
por['미입고수량']=por['발주']-por['입고수량']

por['SKU'] = por.agg('{0[스타일]}{0[MLB컬러]}'.format, axis=1) #SKU컬럼 생성
por=por.rename(columns={"MLB컬러": "Col"}) #칼럼명통일
por['대표품번컬러'] = por.agg('{0[대표품번]}{0[Col]}'.format, axis=1) #대표품번컬러 컬럼 생성

#por=PO release 원본
#Por=생산스케줄 & POR merge 용

Por=por[['생산처명','SKU','대표품번','Col','대표품번컬러','스타일','판매국가','발주','입고수량','미입고수량']]
Por.head()

###PO별 생산국 사전점검###

import pandas as pd
import numpy as np

track_df2=pd.read_excel("C:\\Users\\AD0246\\Desktop\\TRACKING\\TRACKING ALL SEASON HEAD 0321.xls", header=2)
new_names=track_df2.columns.values.tolist()[9:30]

track_df=pd.read_excel("C:\\Users\\AD0246\\Desktop\\TRACKING\\TRACKING ALL SEASON HEAD 0321.xls", header=0)
old_names=track_df.columns.values.tolist()[9:30]

track_df.rename(columns=dict(zip(old_names, new_names)), inplace=True)
track_df=track_df.drop(labels=[0,1],axis=0)

#원산지 표기법 통일
track_df.loc[track_df['원산지']=='VN', '원산지'] = '베트남'
track_df.loc[track_df['원산지']=='KR', '원산지'] = '한국'
track_df.loc[track_df['원산지']=='CN', '원산지'] = '중국'
track_df=track_df[['스타일','시즌','원산지']]

track_df=track_df.dropna(axis=0, subset=['스타일']) #스타일미기재 시 DROP

porelease = pd.merge(Por, track_df, on =['스타일'], how='left')
porelease.head()

### 판매데이터 점검 ###

import pandas as pd
import numpy as np

sales=pd.read_excel("C:\\Users\\AD0246\\Desktop\\주차 판매분석\\{}주차 판매.xls".format(thisweek), header=0)
sales=sales[sales['IT'].isin(['CP','BN','WM','HT','CB','MC','SC'])]

lastweeksales=sales['2022-14'].sum()
print('전주 판매')
print(lastweeksales)

last2weekssales=sales['2022-13'].sum()
print(last2weekssales)
(lastweeksales)-(last2weekssales)

lastweek_hat=sales.loc[sales['IT'] == 'HT', '2022-14'].sum()
print(lastweek_hat)
((lastweek_hat)/(lastweeksales)*100).round(0)

porelease1=porelease[['대표품번','생산처명']]
porelease1.drop_duplicates('대표품번',keep='first', inplace=True)
salestest = pd.merge(sales, porelease1, on=['대표품번'], how='left')
salestest['2022-13'].sum() #데이터 DOUBLE CHECKING용 계산

### 협력사별 판매 TOP5 품번 ###

협력사1sales=salestest.loc[salestest['생산처명'] == '협력사1']
협력사1salesTOP=협력사1sales.sort_values(by='2022-14' ,ascending=False)
협력사1salesTOP=협력사1salesTOP[['대표품번','Col']].reset_index(drop=True).head()
협력사1salesTOP['품번'] = 협력사1salesTOP['대표품번'].str[4:8]
협력사1salesTOP[['품번','Col']].head()