-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathExcel_Vlookup_python
113 lines (102 loc) · 5.89 KB
/
Excel_Vlookup_python
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
df_aggregated=df.groupby(['POSTING_YEAR', 'POSTING_MONTH','KEY'])['GROSS_SALES_QTY'].sum().reset_index()
lysm=[] #lysm : last Year Same Month
lysm_minus_1=[] #lysm : last Year Same Month minus 1
lysm_minus_2=[] #lysm : last Year Same Month minus 2
lylm=[]
lylm_minus_1=[]
lylm_minus2=[]
avg_sales_3m=[]
for i in range(len(df_aggregated)):
curr_key= df_aggregated.iloc[i,2]
lysm_key=int(curr_key)-100 #lysm : last Year Same Month
lysm_minus_1_key=int(curr_key)-200 #lysm : last Year Same Month minus 1
lysm_minus_2_key=int(curr_key)-300 #lysm : last Year Same Month minus 2
#calculate lysm
if lysm_key in df_aggregated.iloc[:,2].tolist():
a1=df_aggregated.index[df_aggregated['KEY'] == lysm_key].tolist()
lysm.append(df_aggregated.iloc[a1,3].tolist()[0])
else:
lysm.append(df_aggregated.iloc[i,3].tolist())
#calculate lysm minus 1
if lysm_minus_1_key in df_aggregated.iloc[:,2].tolist():
a2=df_aggregated.index[df_aggregated['KEY'] == lysm_minus_1_key].tolist()
lysm_minus_1.append(df_aggregated.iloc[a2,3].tolist()[0])
else:
if lysm_key in df_aggregated.iloc[:,2].tolist():
lysm_minus_1.append(df_aggregated.iloc[a1,3].tolist()[0])
else:
lysm_minus_1.append(df_aggregated.iloc[i,3].tolist())
#calculate lysm minus 2
if lysm_minus_2_key in df_aggregated.iloc[:,2].tolist():
a3=df_aggregated.index[df_aggregated['KEY'] == lysm_minus_2_key].tolist()
lysm_minus_2.append(df_aggregated.iloc[a3,3].tolist()[0])
else:
if lysm_minus_1_key in df_aggregated.iloc[:,2].tolist():
lysm_minus_2.append(df_aggregated.iloc[a2,3].tolist()[0])
elif lysm_key in df_aggregated.iloc[:,2].tolist():
lysm_minus_2.append(df_aggregated.iloc[a1,3].tolist()[0])
else:
lysm_minus_2.append(df_aggregated.iloc[i,3].tolist())
#calculate lylm key M-1
if str(curr_key)[4:] == '01':
yr=int(str(curr_key)[:4])-1
new_key=str(yr)+str(12)
lylm_key=int(new_key)
else:
lylm_key=curr_key-1 #lysm : last Year Same Month
#calculate lylm-1 key M-2
if str(curr_key)[4:] == '01':
yr=int(str(curr_key)[:4])-1
new_key_1=str(yr)+str(11)
lylm_minus2_key=int(new_key_1)
elif str(curr_key)[4:] == '02':
yr=int(str(curr_key)[:4])-1
new_key_1=str(yr)+str(12)
lylm_minus2_key=int(new_key_1) #lysm : last Year Same Month
else:
lylm_minus2_key=curr_key-2
#lylm : last Year Same Month LM-1
if lylm_key in df_aggregated.iloc[:,2].tolist():
am1=df_aggregated.index[df_aggregated['KEY'] == lylm_key].tolist()
lylm.append(df_aggregated.iloc[am1,3].tolist()[0]) #201903
elif lylm_key-100 in df_aggregated.iloc[:,2].tolist():
am1=df_aggregated.index[df_aggregated['KEY'] == lylm_key-100].tolist()
lylm.append(df_aggregated.iloc[am1,3].tolist()[0]) #201803
elif lylm_key-200 in df_aggregated.iloc[:,2].tolist():
am1=df_aggregated.index[df_aggregated['KEY'] == lylm_key-200].tolist()
lylm.append(df_aggregated.iloc[am1,3].tolist()[0]) #201703
elif lylm_key-300 in df_aggregated.iloc[:,2].tolist():
am1=df_aggregated.index[df_aggregated['KEY'] == lylm_key-300].tolist()
lylm.append(df_aggregated.iloc[am1,3].tolist()[0]) #201603
elif lylm_key-400 in df_aggregated.iloc[:,2].tolist():
am1=df_aggregated.index[df_aggregated['KEY'] == lylm_key-400].tolist()
lylm.append(df_aggregated.iloc[am1,3].tolist()[0]) #201503
elif lylm_key-500 in df_aggregated.iloc[:,2].tolist():
am1=df_aggregated.index[df_aggregated['KEY'] == lylm_key-500].tolist()
lylm.append(df_aggregated.iloc[am1,3].tolist()[0]) #201603
else:
lylm.append(df_aggregated.iloc[i,3].tolist()) #201603
#lylm : last Year last Month LM-2
if lylm_minus2_key in df_aggregated.iloc[:,2].tolist():
am1=df_aggregated.index[df_aggregated['KEY'] == lylm_minus2_key].tolist()
lylm_minus2.append(df_aggregated.iloc[am1,3].tolist()[0]) #201903
elif lylm_minus2_key-100 in df_aggregated.iloc[:,2].tolist():
am1=df_aggregated.index[df_aggregated['KEY'] == lylm_minus2_key-100].tolist()
lylm_minus2.append(df_aggregated.iloc[am1,3].tolist()[0]) #201803
elif lylm_minus2_key-200 in df_aggregated.iloc[:,2].tolist():
am1=df_aggregated.index[df_aggregated['KEY'] == lylm_minus2_key-200].tolist()
lylm_minus2.append(df_aggregated.iloc[am1,3].tolist()[0]) #201703
elif lylm_minus2_key-300 in df_aggregated.iloc[:,2].tolist():
am1=df_aggregated.index[df_aggregated['KEY'] == lylm_minus2_key-300].tolist()
lylm_minus2.append(df_aggregated.iloc[am1,3].tolist()[0]) #201603
elif lylm_minus2_key-400 in df_aggregated.iloc[:,2].tolist():
am1=df_aggregated.index[df_aggregated['KEY'] == lylm_minus2_key-400].tolist()
lylm_minus2.append(df_aggregated.iloc[am1,3].tolist()[0]) #201503
elif lylm_minus2_key-500 in df_aggregated.iloc[:,2].tolist():
am1=df_aggregated.index[df_aggregated['KEY'] == lylm_minus2_key-500].tolist()
lylm_minus2.append(df_aggregated.iloc[am1,3].tolist()[0]) #20160
elif lylm_key in df_aggregated.iloc[:,2].tolist():
am1=df_aggregated.index[df_aggregated['KEY'] == lylm_key].tolist()
lylm_minus2.append(df_aggregated.iloc[am1,3].tolist()[0]) #20160
else:
lylm_minus2.append(df_aggregated.iloc[i,3].tolist()) #201603