i have problems finding a solution.
lets assume i have the following input.
| Identicator | Description | Value |
|---|---|---|
| A | Acc1 | 30 |
| A | Acc2 | 40 |
| A | Acc3 | -70 |
| B | Acc4 | 30 |
| B | Acc5 | 30 |
| B | Acc2 | -60 |
| C | Acc1 | 15 |
| C | Acc3 | -15 |
| D | Acc6 | 50 |
| D | Acc2 | -25 |
| D | Acc3 | 10 |
| D | Acc8 | -60 |
| D | Acc1 | 12 |
| D | Acc2 | 13 |
I have a reference number ("identicator") for purchases ("value") and the accounts. Usually transactions can be netted ((A) Acc1 + Acc2 +Acc3 = 0). The problem is, that the reference number sometimes contains two or more transactions, making the original reference number not perfect. Thus i am thinking of creating a new index. The Output could be for example:
| Identicator | Description | Value | ReferenceNo |
|---|---|---|---|
| A | Acc1 | 30 | 1 |
| A | Acc2 | 40 | 1 |
| A | Acc3 | -70 | 1 |
| B | Acc4 | 30 | 2 |
| B | Acc5 | 30 | 2 |
| B | Acc2 | -60 | 2 |
| C | Acc1 | 15 | 3 |
| C | Acc3 | -15 | 3 |
| D | Acc6 | 50 | 4 |
| D | Acc2 | -25 | 5 |
| D | Acc3 | 10 | 4 |
| D | Acc8 | -60 | 4 |
| D | Acc1 | 12 | 5 |
| D | Acc9 | 13 | 5 |
I am thinking hours how to do it but i feel this is just beyond my capabilities.
Can anybody give me a hint?
Edit1: The values are not in order. there is sometimes more than a negative value. For example it could also be -10,-40, +50 = 0
Edit2: I don't want to simply pass on my fantasies, but I also don't want to mislead anyone. I was thinking about whether it would work via iterating through groups (itertools.groupby():) to achieve f.e. a isolated view of reference A and then over "trial and error" with for loops. But... don't know.