Python add new column with repeating value based on two other columns

Question

I have the following df:

I want to add third column with repeating values that (1) increment by one if there is a change in column "Item" and that also (2) restarts if there is a change in column "Doc"

Doc Item  NewCol
 1    1     1
 1    1     1
 1    2     2
 1    3     3
 2    1     1 
 2    2     2

What is the best way to achieve this? Thanks a lot.

Please supply the code on how you store the DF, it will be easier to help you that way — Tamir
– Tamir, Commented Nov 12, 2020 at 9:55
@Tamir You can use pd.read_clipboard unless it's MulitIndex or having datetime — Ch3steR
– Ch3steR, Commented Nov 12, 2020 at 9:56

jezrael · Accepted Answer · 2020-11-12 09:56:37Z

2

Use GroupBy.transform wth custom lambda function with factorize:

df['NewCol'] = df.groupby('Doc')['Item'].transform(lambda x: pd.factorize(x)[0]) + 1
print (df)
   Doc  Item  NewCol
0    1     1       1
1    1     1       1
2    1     2       2
3    1     3       3
4    2     1       1
5    2     2       2

If values in Item are integers is possible use GroupBy.rank:

df['NewCol'] = df.groupby('Doc')['Item'].rank(method='dense').astype(int)

answered Nov 12, 2020 at 9:56

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python add new column with repeating value based on two other columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related