add incremental value for duplicates

Question

Suppose I have a dataframe looking something like

df = pd.DataFrame(np.array([[1, 2, 3, 2], [4, 5, 6, 3], [7, 8, 9, 5]]),  columns=['a', 'b', 'c', 'repeater'])

    a   b   c   repeater
0   1   2   3   2
1   4   5   6   3
2   7   8   9   5

And I repeat every row based on the df['repeat'] like df = df.loc[df.index.repeat(df['repeater'])] So I end up with a data frame

    a   b   c   repeater
0   1   2   3   2
0   1   2   3   2
1   4   5   6   3
1   4   5   6   3
1   4   5   6   3
2   7   8   9   5
2   7   8   9   5
2   7   8   9   5
2   7   8   9   5
2   7   8   9   5

How can I add an incremental value based on the index row? So a new column df['incremental'] with the output:

    a   b   c   repeater    incremental
0   1   2   3   2           1
0   1   2   3   2           2
1   4   5   6   3           1
1   4   5   6   3           2
1   4   5   6   3           3
2   7   8   9   5           1
2   7   8   9   5           2
2   7   8   9   5           3
2   7   8   9   5           4
2   7   8   9   5           5

Use `df['incremental'] = df.groupby(level=0).cumcount()` – jezrael Jan 23 '20 at 09:37 — jezrael, Jan 23 '20 at 09:37

score 2 · Accepted Answer · answered Jan 23 '20 at 09:37

Try your code with an extra groupby and cumcount:

df = df.loc[df.index.repeat(df['repeater'])]
df['incremental'] = df.groupby(df.index).cumcount() + 1
print(df)

Output:

   a  b  c  repeater  incremental
0  1  2  3         2            1
0  1  2  3         2            2
1  4  5  6         3            1
1  4  5  6         3            2
1  4  5  6         3            3
2  7  8  9         5            1
2  7  8  9         5            2
2  7  8  9         5            3
2  7  8  9         5            4
2  7  8  9         5            5

add incremental value for duplicates

1 Answers1