从Pandas数据框中删除取消行

Question

我有一份发给客户的发票清单。然而，有时会发送错误的发票，之后又被取消。我的Pandas数据框架看起来像这样，只是更大（约300万行）。

index | customer | invoice_nr | amount | date
---------------------------------------------------
0     | 1        | 1          | 10     | 01-01-2016
1     | 1        | 1          | -10    | 01-01-2016
2     | 1        | 1          | 11     | 01-01-2016
3     | 1        | 2          | 10     | 02-01-2016
4     | 2        | 3          | 7      | 01-01-2016
5     | 2        | 4          | 12     | 02-01-2016
6     | 2        | 4          | 8      | 02-01-2016
7     | 2        | 4          | -12    | 02-01-2016
8     | 2        | 4          | 4      | 02-01-2016
...   | ...      | ...        | ...    | ...
...   | ...      | ...        | ...    | ...

现在，我想要删除所有满足以下条件的行：customer，invoice_nr和date相同，但是amount的值相反。
发票的更正总是在同一天进行，具有相同的发票号。发票号唯一绑定到客户，并且始终对应于一个交易（可以由多个组件组成，例如对于customer = 2，invoice_nr = 4）。发票的更正仅发生在更改已收费用或将amount拆分为较小的组件时。因此，在相同的invoice_nr上不会重复取消值。

非常感谢您提供如何编写此程序的任何帮助。

- Niels Alebregtse

---

@KrishnachandraSharma，我不太确定您的意思。我应该将invoice_nr和date作为dict键来读取吗？那么我该如何处理具有相同invoice_nr和date的多行数据呢？ - Niels Alebregtse

由于您想删除所有具有相同“invoice_nr”和“date”的行，因此准备将关键字符串设置为“invoice_nr#date”将帮助您识别要删除的重复行。 - Krishnachandra Sharma

感谢您的快速回复。然而，我不想删除所有具有相同“invoice_nr”和“date”的行，而只是那些具有相反“amount”值的行。例如，我不想从数据框中删除第3行。 - Niels Alebregtse

在实际数据中，第三行的“amount”列是否可能是值“10”？ - jezrael

这是一个不错的问题，但我认为你应该让它更清晰。这些是否总是有相同的日期？相同的发票号码？同一张发票号码是否可以分配给同一客户，用于其他事项？ - ayhan

Answer 1

def remove_cancelled_transactions(df):
    trans_neg = df.amount < 0
    return df.loc[~(trans_neg | trans_neg.shift(-1))]

groups = [df.customer, df.invoice_nr, df.date, df.amount.abs()]
df.groupby(groups, as_index=False, group_keys=False) \
  .apply(remove_cancelled_transactions)

- piRSquared

---

嗯，我觉得这个问题更复杂，因为你需要去除相反的数值。而且这非常困难... - jezrael

谢谢。嗯，我认为你的解决方案更好，因为更通用。我的解决方案更快，但有些值找不到。 - jezrael

谢谢 @jezrael。这是一个有趣的问题。我还在思考中。 - piRSquared

谢谢@piRSquared！我很高兴你喜欢这个问题。你的解决方案非常棒。不幸的是，对于我的300万行数据，groupby需要很长时间。但是，对于较小的数据集，这绝对有效，并且正好符合我的需求！ - Niels Alebregtse

Answer 2

您可以使用“ filter ”筛选数据帧中满足每个分组总和为0且模数为2的所有值： filter。请保留HTML标签，不要添加解释，并使内容易于理解。

print (df.groupby([df.customer, df.invoice_nr, df.date, df.amount.abs()])
        .filter(lambda x: (len(x.amount.abs()) % 2 == 0 ) and (x.amount.sum() == 0)))

       customer  invoice_nr  amount        date
index                                          
0             1           1      10  01-01-2016
1             1           1     -10  01-01-2016
5             2           4      12  02-01-2016
6             2           4     -12  02-01-2016

idx = df.groupby([df.customer, df.invoice_nr, df.date, df.amount.abs()])
        .filter(lambda x: (len(x.amount.abs()) % 2 == 0 ) and (x.amount.sum() == 0)).index

print (idx)      
Int64Index([0, 1, 5, 6], dtype='int64', name='index')

print (df.drop(idx))  
       customer  invoice_nr  amount        date
index                                          
2             1           1      11  01-01-2016
3             1           2      10  02-01-2016
4             2           3       7  01-01-2016
7             2           4       8  02-01-2016
8             2           4       4  02-01-2016

评论编辑：

如果在真实数据中，一个发票、一个客户和一个日期没有重复项，那么您可以使用以下方式：

 print (df)
   index  customer  invoice_nr  amount        date
0      0         1           1      10  01-01-2016
1      1         1           1     -10  01-01-2016
2      2         1           1      11  01-01-2016
3      3         1           2      10  02-01-2016
4      4         2           3       7  01-01-2016
5      5         2           4      12  02-01-2016
6      6         2           4     -12  02-01-2016
7      7         2           4       8  02-01-2016
8      8         2           4       4  02-01-2016

df['amount_abs'] = df.amount.abs()
df.drop_duplicates(['customer','invoice_nr', 'date', 'amount_abs'], keep=False, inplace=True)
df.drop('amount_abs', axis=1, inplace=True)
print (df)
   index  customer  invoice_nr  amount        date
2      2         1           1      11  01-01-2016
3      3         1           2      10  02-01-2016
4      4         2           3       7  01-01-2016
7      7         2           4       8  02-01-2016
8      8         2           4       4  02-01-2016

- jezrael

---

非常感谢您的帮助，@jezrael！与@piRSquared的解决方案类似，您的解决方案对于小数据集非常有效。不幸的是，对于我那有300万行的数据框来说，该操作需要相当长的时间。 - Niels Alebregtse

是的，您的任务非常复杂。因此，如果需要更快的速度，这是有问题的。但是有一个问题 - 在3M行中有多少发票被取消（估计）？ - jezrael

您可以通过以下代码获取包含所有重复项的 df1：df['amount_abs'] = df.amount.abs() df1 = df[df.duplicated(['customer','invoice_nr', 'date', 'amount_abs'], keep=False)] print (df1) 请问 df1 的大小是多少？ len(df1) - jezrael

嗯，我认为这只是用于测试的解决方案，因为如果您有超过2次重复，您将丢失好的数据 - 请参见示例 df = pd.DataFrame({'index': {0: 0, 1: 1, 2: 2, 3: 3, 4: 4}, 'invoice_nr': {0: 1, 1: 1, 2: 1, 3: 2, 4: 3}, 'customer': {0: 1, 1: 1, 2: 1, 3: 1, 4: 2}, 'amount': {0: 10, 1: -10, 2: 10, 3: 11, 4: 7}, 'date': {0: '01-01-2016', 1: '01-01-2016', 2: '01-01-2016', 3: '02-01-2016', 4: '01-01-2016'}}) 和值 10。那么这种情况在实际数据中可能发生吗？ - jezrael

是的，所以解决方案很复杂。 :( - jezrael

Answer 3

如果您只对这三个字段进行分组，会怎样呢？结果的总和将抵消任何已取消的发票：

df2 = df.groupby(['customer','invoice_nr','date']).sum()

结果是

customer invoice_nr date
1        1          2016/01/01      11
         2          2016/02/01      10
2        3          2016/01/01       7

- SO44

---

谢谢，这是一个不错的解决方案。然而，我现在看到我的示例数据不够完整，因为我的发票有时会被分成较小的金额，我希望将其单独考虑。我已经相应地更新了我的原始问题。 - Niels Alebregtse