Pandas: Extract data from column A that does not exist for column B

user_8457850 0
Viewed 172

My data includes invoices and I have to check whether an invoice was already paid or not. For each invoice I loop through all report dates. If on one day, the invoice doen't show up, it means the customer already made a payment and of course it won't appear again on subsequent days.

You can see from the table below that invoice C was paid on 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

I wrote the code below, it worked but took too long because there are around 800k entries. This is very inefficient. I wonder if there is a more efficient way to solve this using 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

As a result I want a new column which shows Open/Closed for each row.

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
jupyter-notebook pandas python-3.x python
3 Answers

Here is on way we using crosstab, then is the the invoice eq to 0 , which means the previous invoices should be count as 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
user_79645270

You need to use dummy variables to get the presense of InvoiceNo, then do a sum as per the date and stack it.

Here, 0 means closed and 1 means open. You can easily map them with the 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')

Output:

  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
user_96094470

One way would be to access each dataframe grouped per Invoice No with groupby and conditionally check with np.where if any of the dates are equal to the max date in our dataframe:

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
user_90812670
Related