Python dataframe iterate and assign unique value for each duplicate

Something this will help you out

cols = ['reference','amount']
df["ID"] = df[cols].apply(lambda row: '_'.join(row.values.astype(str)), axis=1)   
df["Unique_ID_Count"] = df.groupby(["ID"]).cumcount()+1
df["Unique_ID_Count"] = df["key"] + '_' + df["Unique_ID_Count"]

What if you groupby your 'reference' column and sum your 'amount' column. This will filter out naturally the rows that are nil:

res = df.groupby('reference',as_index=False)['amount'].sum()

print(res)
   reference  amount
0       5231     505

Then you can drop from your main data frame res's index rows:

other_rows = df.loc[~df.index.isin(res.index.tolist())]

print(other_rows)

  reference  amount
1       5231    -505
2       5231     505
3       5231    -505
4       5231    -505
5       5231     505
6       5231     505

A bit convoluted, but I guess it would work: I'd use the index instead of creating unique IDs.

I created a dataframe with another reference value, for which the amount will total to a negative value, just in case that would happen in your data:

df = pd.DataFrame(
    {'amount': [505, -505, 505, -505, -505, 505, 505, 505]}
)
df['reference'] = 5231
df2 = pd.DataFrame(
    {'amount': [505, -505, 505, -505, -505, 505, -505, -505]}
)
df2['reference'] = 6451

df = pd.concat([df, df2]).sort_values(by=['reference', 'amount']).reset_index(drop=True)

grouped = df.groupby('reference', as_index=False)
keep = []
for i, grp in grouped:
    pos_idx = grp.loc[grp.amount>0].index
    neg_idx = grp.loc[grp.amount<0].index
    gap = len(pos_idx) - len(neg_idx)
    if gap > 0:
        keep.extend(pos_idx[:gap])
    elif gap < 0:
        idx_to_keep = neg_idx[:abs(gap)].values.tolist()
        keep.extend(idx_to_keep)
df[df.index.isin(keep)]

Here's another alternative:

df["amnt_abs"] = df.amount.abs()
df2 = df.groupby(["reference", "amnt_abs"], as_index=False).sum()
df2["num_rows"] = df2.amount.abs() // df2.amnt_abs 
df2.amount /= df2.num_rows
df2 = df2.loc[df2.index.repeat(df2.num_rows), ["reference", "amount"]]
df2 = df2.reset_index(drop=True)

With the sample DataFrame

df = pd.DataFrame({
    "reference": [5231] * 8 + [5232] * 4,
    "amount": [505, -505] * 3 + [505, 505] + [-10, -10, 1, -1]
})

this

df["amnt_abs"] = df.amount.abs()
df2 = df.groupby(["reference", "amnt_abs"], as_index=False).sum()

does produce

   reference  amnt_abs  amount
0       5231       505    1010
1       5232         1       0
2       5232        10     -20

and therefore this

df2["num_rows"] = df2.amount.abs() // df2.amnt_abs 
df2.amount /= df2.num_rows

results in

   reference  amnt_abs  amount  num_rows
0       5231       505   505.0         2
1       5232         1     NaN         0
2       5232        10   -10.0         2

Now index.repeat can produce the right number of rows: This

df2 = df2.loc[df2.index.repeat(df2.num_rows), ["reference", "amount"]]
df2 = df2.reset_index(drop=True)

results in

   reference  amount
0       5231   505.0
1       5231   505.0
2       5232   -10.0
3       5232   -10.0

But: This won't work if an amount is 0. Is this allowed?

For a given absolute amount (we'll handle reference later), you can create a function to net out positive and negative rows like

def net(df):
    if (df["amount"] == 0).any():
        return df
    df = df.sort_values(by="amount").reset_index(drop=True)
    num_pos = (df["amount"] > 0).astype(int).sum()
    num_neg = len(df) - num_pos
    diff = num_pos - num_neg
    if diff > 0:
        return df.tail(diff)

    if diff < 0:
        return df.head(abs(diff))

    return pd.DataFrame(columns=df.columns)

Then group your data frame by absolute amount and reference, and apply the net function to each group:

def main(df):
    df["abs_amt"] = df["amount"].abs()
    xform = df.groupby(["reference", "abs_amt"], as_index=False).apply(net)
    xform = xform.drop("abs_amt", axis=1).reset_index(drop=True)
    return xform

This is an interesting problem which can be vectorised with a subtle use of sorting.

Start with this dataset:

df = DataFrame(
    [
        # Two more `1` than `-1`
        ("a", 1), ("a", -1), ("a", 1),
        ("a", -1), ("a", -1), ("a", 1),
        ("a", 1), ("a", 1),
        # Three more `-2` than 2
        ("b", -2), ("b", -2), ("b", 2),
        ("b", 2), ("b", -2), ("b", -2),
        ("b", 2), ("b", -2), ("b", -2),
    ],
    columns=("reference", "amount"),
)

We sort the DataFrame by the amount column, first ascending, then descending, and finally remove any rows where the different sorts don't match.

To see why this works, take a simple example:

>>> simple_example = [-1, 1, -1, 1, 1, 1]
>>> DataFrame({"asc": sorted(simple_example), "desc": reversed(sorted(simple_example))})
   asc  desc
0   -1     1
1   -1     1
2    1     1  # <- These rows have no partner "-1" row
3    1     1  # <- and are hence not cancelled out
4    1    -1
5    1    -1

Here's one possible implementation of such an algorithm:

sort_args = {"by": ["reference", "amount"], "ignore_index": True}

asc_and_desc = concat(
    (
        df.sort_values(ascending=(True, True), **sort_args),
        df.sort_values(ascending=(True, False), **sort_args),
    ),
    axis=1,
    keys=("original", "inverse"),
)

result = asc_and_desc[
    asc_and_desc["original"]["amount"] == asc_and_desc["inverse"]["amount"]
]["original"]

This results in the expected output, with all inverse rows cancelled out:

>>> result
  reference  amount
0         a       1
1         a       1
2         b      -2
3         b      -2
4         b      -2

The only thing missing from your solution is a drop_duplicates on the subset of reference and count. The ID column wouldn't be necessary, but I kept it for clarity.

import pandas as pd
df = pd.DataFrame({'reference': [5231, 5231, 5231, 5231, 5231, 5231, 5231, 5231], 
                   'amount': [505, -505, 505, -505, -505, 505, 505, 505]})

df['ID'] = df['reference'].astype(str)+'_'+df['amount'].astype(str)
df['count'] = df.groupby('ID').cumcount()
df = df.drop_duplicates(subset=['reference', 'count'], keep=False)

OUTPUT

reference   amount  ID  count
6   5231    505 5231_505    3
7   5231    505 5231_505    4

So you'd like to remove 2 rows only when they have a matching but opposite value, and the same reference -- while keeping any number of excess rows, negative or positive?

import pandas as pd
import numpy as np
from itertools import chain

df = pd.DataFrame({
                   'reference': [5231, 5231, 5231, 5231, 5231, 5231, 5231, 5231, 1, 1, 1],
                   'amount': [505, -505, 505, -505, -505, 505, 505, 505, 1, -1, 1]
                   })

# group rows with same amount and reference (rename columnn to avoid using the size function)
grouped = df.groupby(df.columns.tolist(),as_index=False).size()
grouped.rename(columns={'size': 'counts'}, inplace=True)

The result of grouping and counting rows:

# keep track of negative indices
grouped['counts'] = grouped.counts.mask(grouped.amount < 0, -grouped.counts)
# absolute values
grouped['amount'] = grouped.amount.mask(grouped.amount < 0, -grouped.amount)
# rows with same ref and amount should be subtracted
grouped['subtract'] = grouped.duplicated(subset=['reference', 'amount'])
# the amount to be subtracted can be found by shifting the counts index
grouped['shifted'] = grouped.counts.shift().fillna(0)
# number of rows to keep (based on subtract booleans)
grouped['num_rows_to_keep'] = grouped.apply(lambda x: x.counts + int(x.shifted) if x.subtract else x.counts, 
                                            axis=1)

Result of collecting additional information on rows to keep/remove:

nested_list = grouped.apply(lambda x: [{'reference': x['reference'], 'amount': x['amount']}] * x.num_rows_to_keep if x.subtract else [], axis=1).to_list()
pd.DataFrame(list(chain.from_iterable(nested_list)))

What I believe to be your desired result, without using iterrows():