我对python非常陌生,但我会尝试使用像pandas这样的分析工具来解决现实世界中的问题
我已经从csv导入了数据,但这里有一个数据的小复制:
df2 = pd.DataFrame({'SKU': [22335, 22335, 22335, 22335, 33442, 33442, 33442, 33442],
'Date': ['2019-12-31', '2020-01-07', '2020-01-14', '2020-01-21', '2019-12-31', '2020-01-07', '2020-01-14', '2020-01-21'],
'Urgent': [10,8,4,20,50,45,65,32],
'Delivered': [4,7,12,10, 35,75,23,42]})
有两个项目SKU编号,22335和33442,一周开始日期,每周设备紧急请求和每周设备交付数量。此时,我已了解如何在整个数据集上计算for循环,对于每一行,该循环引用前一行的计算值:
# Create new numeric column 'Result'
df['Result'] = np.nan
# Assign initial value for the first row of 'Result' (Should be first row in each SKU group)
df.loc[0, 'Result'] = df.loc[0, 'Delivered'] + df.loc[1, 'Delivered'] - df.loc[0, 'Urgent']
# Loop through each row except for last row to calculate
for i in range(1, len(df)-1):
df.loc[i,'Result'] = max(df.loc[i-1, 'Result'], 0) + df.loc[i+1, 'Delivered'] - df.loc[i, 'Urgent']
print(df)
但是,我的下一步是仅对每个单独的SKU执行上述操作(分别为22335和33442)。我尝试使用groupby按日期按SKU对每一行进行排名,但我不知道如何在循环中引用这一点:
# Convert Date datatype
df['Date'] = pd.to_datetime(df['Date'])
# Use groupby to create ranking by SKU and Date
df['SKURank'] = df.groupby('SKU')['Date'].rank(ascending = True).astype('int64')
我曾尝试定义一个函数,该函数可以在每次迭代中引用自己的输出,然后使用.apply样式的循环调用该函数,但没有成功,但说实话,我完全不明白这一点
我还尝试遵守拆分、应用、组合原则,按SKU对数据进行分组,应用循环,然后将所有行重新组合在一起,但我真的不知道从哪里开始
以下是我的主要问题:
对于每个单独的SKU组,我应该使用什么样的循环来执行与上述代码相同的任务(为组中的第一行返回初始值,然后循环通过后续的每一行)?
如果推荐的循环形式(无论性能如何,我还没有那么快)要求我事先定义一个函数,我如何创建一个函数,在每个SKU的第一行之后的每一行引用它自己的输出?
是的,我创建了一个带有嵌套if语句的巨型for循环。是的,这太可怕了。不,它并没有做我需要的一切,比如在数据帧的最后一行执行循环。如果下面的任何部分有意义,并且您可以为我指出如何使其真正起作用的方向,我将非常感谢您的建议
import pandas as pd
import numpy as np
# Create dataframe for two SKUs, a weekly process date, urgent requested quantity, and delivered quantity
df = pd.DataFrame({'SKU': [22335, 22335, 22335, 22335, 33442, 33442, 33442, 33442],
'Date': ['2019-12-31', '2020-01-07', '2020-01-14', '2020-01-21', '2019-12-31', '2020-01-07', '2020-01-14', '2020-01-21'],
'Urgent': [10,8,4,20,50,45,65,32],
'Delivered': [4,7,12,10, 35,75,23,42]})
# Create new numeric column 'Result'
df['Result'] = np.nan
# Convert Date datatype and create 3 necessary columns
df['Date'] = pd.to_datetime(df['Date'])
df['Result'] = np.nan
df['WeeklyMiss'] = np.nan
df['Logic'] = ''
# Create list of unique SKUs in dataframe
skulst = df.SKU.unique()
print(skulst)
# Set initial indeces value
skunum = 0
i = 0
# While loop with nested for loop to iterate over the dataframe
while skunum <= len(skulst):
for i in range(0, len(df)-1):
# Calculate first SKU row
if i == 0 and df.loc[i, 'SKU'] == skulst[skunum]:
df.loc[i, 'Result'] = max(df.loc[i, 'Delivered'] + df.loc[i+1, 'Delivered'] - df.loc[i, 'Urgent'], 0)
df.loc[i, 'WeeklyMiss'] = min(df.loc[i, 'Delivered'] + df.loc[i+1, 'Delivered'] - df.loc[i, 'Urgent'], 0)
df.loc[i, 'Logic'] = 'First Row'
# Calculate next SKU rows
elif i > 0 and df.loc[i, 'SKU'] == skulst[skunum] and df.loc[i+1,'SKU'] == skulst[skunum]:
df.loc[i, 'Result'] = max(df.loc[i+1, 'Delivered'] + min(df.loc[i-1, 'Result'], df.loc[i, 'Delivered']) - df.loc[i, 'Urgent'], 0)
df.loc[i, 'WeeklyMiss'] = min(df.loc[i-1, 'Result'] + df.loc[i+1, 'Delivered'] - df.loc[i, 'Urgent'], 0)
df.loc[i, 'Logic'] = 'Next SKU Row'
# Calculate last SKU row
elif i > 0 and df.loc[i, 'SKU'] == skulst[skunum] and (df.loc[i+1,'SKU'] != skulst[skunum] or i == len(df)):
df.loc[i, 'Result'] = max(df.loc[i-1, 'Result'] - df.loc[i, 'Urgent'], 0)
df.loc[i, 'WeeklyMiss'] = min(df.loc[i-1, 'Result'] - df.loc[i, 'Urgent'], 0)
df.loc[i, 'Logic'] = 'Last SKU Row'
# Calculate first SKU row and switch to next SKU
elif i > 0 and i < len(df) and df.loc[i, 'SKU'] != skulst[skunum] and df.loc[i-1,'SKU'] == skulst[skunum] :
df.loc[i, 'Result'] = max(df.loc[i, 'Delivered'] + df.loc[i+1, 'Delivered'] - df.loc[i, 'Urgent'], 0)
df.loc[i, 'WeeklyMiss'] = min(df.loc[i, 'Delivered'] + df.loc[i+1, 'Delivered'] - df.loc[i, 'Urgent'], 0)
df.loc[i, 'Logic'] = 'First SKU Row'
if skunum + 1 <= len(skulst):
skunum += 1
else:
df.loc[i, 'Result'] = max(df.loc[i-1, 'Result'] - df.loc[i, 'Urgent'], 0)
df.loc[i, 'WeeklyMiss'] = min(df.loc[i-1, 'Result'] - df.loc[i, 'Urgent'], 0)
df.loc[i, 'Logic'] = 'Last SKU Row'
continue
else:
print(df)
break
请参阅pandas文档中的Group By: split-apply-combine指南,了解如何迭代组
相关问题 更多 >
编程相关推荐