python - 计算条件发生的数据框

Question

如下所示的数据框。

我想知道什么时候销售额>20，(在它之前的5个数据中)有多少次库存>10。

理想的输出是:

2018/12/26 has Sales 36 when 2 times.
2018/11/19 has Sales 34 when 2 times.

这是我用 xlrd 做的:

import xlrd
from datetime import datetime

old_file = xlrd.open_workbook("C:\\Sales.xlsx")
the_sheet = old_file.sheet_by_name("Sales")

for row_index in range(1, the_sheet.nrows):
    Dates = the_sheet.cell(row_index, 0).value
    Inventory = the_sheet.cell(row_index, 1).value
    Sales = the_sheet.cell(row_index, 2).value

    list_of_Inventory = []

    for i in range(1,5):
        list_of_Inventory.append(the_sheet.cell(row_index - i, 1).value)

    if Sales > 20:
        print str(Dates) + " has Sales " + str(Sales) + " when " + str(sum(i > 10 for i in list_of_Inventory)) + " times."

效果不佳。

解决问题的正确方法是什么？感谢 pandas 中的一些指导。

谢谢。

附注这是数据。

data = {'Date':     ["2018/12/29","2018/12/26","2018/12/24","2018/12/15","2018/12/11","2018/12/8","2018/11/28","2018/11/20","2018/11/19","2018/11/11","2018/11/6","2018/11/1","2018/10/28","2018/10/11","2018/9/25","2018/9/24"], 
'Inventory': [5,5,5,22,5,25,5,15,15,5,5,15,0,22,2,10],
'Sales' : [0,36,18,0,0,17,18,17,34,16,0,0,18,18,51,18]}

df = pd.DataFrame(data)

Answer 1

我认为您不会绕过数据帧的迭代(根据输出的具体情况)。因此，只要您的数据不是很大，那应该不是问题。这是您可以实现的另一个快速解决方案:

for idx in df.loc[df.Sales > 20].index:
    inv = df.loc[idx-4:idx, 'Inventory'].ge(10)
    date, _, sales = df.loc[idx]
    if len(inv) >= 5:
        print(f'{date} has Sales {sales} when {inv.sum()} times')

2018/11/19 has Sales 34 when 2 times
2018/9/25 has Sales 51 when 2 times