Pandas: 从列A中提取不存在于列B中的数据

Question

我的数据包括发票，我需要检查一张发票是否已经支付。对于每一个发票，我会循环遍历所有报告日期。如果在某一天，该发票未出现，则意味着客户已经进行了付款，当然它将不会再出现在随后的几天中。

从下表可以看出，发票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

- Azrion

---

请看np.select。 - Sylvain Biehler

Answer 1

这里是使用crosstab的一种方式，然后是发票等于0，这意味着以前的发票应被视为Closed

s=pd.crosstab(df.ReportDate,df.InvoiceNo).eq(0)

Newdf=(s.iloc[::-1,:].cummax()&~s).replace({True:'Closed',False:'Open'}).stack().reindex(pd.MultiIndex.from_frame(df)).reset_index()
Newdf
Out[342]: 
   ReportDate InvoiceNo       0
0  2019-05-28         D    Open
1  2019-05-28         A    Open
2  2019-05-28         B    Open
3  2019-05-27         A    Open
4  2019-05-27         B    Open
5  2019-05-27         C  Closed
6  2019-05-26         A    Open
7  2019-05-26         B    Open
8  2019-05-26         C  Closed

- BENY

---

这段代码是最快的！我很惊讶你们如何在只有两行代码的情况下解决了它。非常感谢！！ - Azrion

Answer 2

你需要使用dummy变量来获取InvoiceNo的存在，然后按日期进行汇总并堆叠。这里，0表示关闭，1表示打开。你可以很容易地映射它们到dict中。

data = pd.concat([df,pd.get_dummies(df['InvoiceNo'])],1).drop('InvoiceNo',1).set_index('ReportDate')
r = data.groupby(data.index).sum()
r = (r.iloc[-1:].values | r) & r.iloc[-1:].values
res = r.stack().reset_index(name='Open/Closed').rename(columns={'level_1':'InvoiceNo'})
res['Open/Closed'] = res['Open/Closed'].map(map_dict={0:'Closed',1:'Open'})
res = df.merge(res, 'left')

输出：

  ReportDate InvoiceNo Open/Closed
0 2019-05-28         D        Open
1 2019-05-28         A        Open
2 2019-05-28         B        Open
3 2019-05-27         A        Open
4 2019-05-27         B        Open
5 2019-05-27         C      Closed
6 2019-05-26         A        Open
7 2019-05-26         B        Open
8 2019-05-26         C      Closed

- harvpan

---

谢谢！我猜它能工作，但是它占用了太多的内存（我得到了一个“内存错误”）。 - Azrion

Answer 3

一种方法是使用groupby访问每个按发票号分组的数据框，并使用np.where有条件地检查是否任何日期与我们数据框中的max日期相等。请保留HTML标签。

dfs = []

for _, d in df.groupby('Invoice No'):
    d['Open/Closed'] = np.where(d['Report Date'].eq(df['Report Date'].max()).any(), 'Open', 'Closed')
    dfs.append(d)

df2 = pd.concat(dfs).sort_values('Report Date', ascending=False).reset_index(drop=True)

---

  Report Date Invoice No Open/Closed
0  2019-05-28          A        Open
1  2019-05-28          B        Open
2  2019-05-28          D        Open
3  2019-05-27          A        Open
4  2019-05-27          B        Open
5  2019-05-27          C      Closed
6  2019-05-26          A        Open
7  2019-05-26          B        Open
8  2019-05-26          C      Closed

- Erfan