我的数据包括发票,我需要检查一张发票是否已经支付。对于每一个发票,我会循环遍历所有报告日期。如果在某一天,该发票未出现,则意味着客户已经进行了付款,当然它将不会再出现在随后的几天中。
从下表可以看出,发票C是在28/05支付的。
Report Date Invoice No
2019-05-28 D
2019-05-28 A
2019-05-28 B
2019-05-27 A
2019-05-27 B
2019-05-27 C
2019-05-26 A
2019-05-26 B
2019-05-26 C
我写了下面的代码,虽然可以工作但是因为有大约800k条目所以花费的时间太长了。这种方法非常低效。我想知道是否有更有效的方式使用pandas来解决。
# For every Invoice
for i in range(0,len(documentNo.categories)):
# If the invoice still exists in the newest Report Date (here 28/05), means that it has not been paid yet. So we can skip to check other invoices
if (df.loc[(df['Document No'] == documentNo.categories[i]) & (df['Report Date'] == reportDates.categories[len(reportDates.categories) - 1])].all(1).any()):
continue
# Decrement from date 27/05
for j in range(len(reportDates.categories) - 2,0,-1):
# If the Invoice does not exist on this date, it has been paid
if (df.loc[(df['Document No'] == documentNo.categories[i]) & (df['Report Date'] != reportDates.categories[j])].all(1).any()):
break
因此,我希望每一行都显示开放/关闭状态的新列。
Report Date Invoice No Open/Closed
2019-05-28 D Open
2019-05-28 A Open
2019-05-28 B Open
2019-05-27 A Open
2019-05-27 B Open
2019-05-27 C Closed
2019-05-26 A Open
2019-05-26 B Open
2019-05-26 C Closed
np.select
。 - Sylvain Biehler