0

If I have Column 1 and Column 2 from the example below, how can I create Column 3?

Every time Column 2 increases by 1, Column 3 has to increase by 1, but only within each group in column 1.

In other words, Column 3 should count in the same way as in Column 2, but start over every time column 1 increases by 1.

Example

Cleptus
  • 3,446
  • 4
  • 28
  • 34
nielsen
  • 383
  • 1
  • 6
  • 1
    Looks like : `df.groupby(['Col1'])['Col2'].transform(lambda x: pd.factorize(x)[0]+1)` but [Please don't post images of code/data (or links to them)](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question) – anky Apr 15 '20 at 17:38

1 Answers1

2

You can try:

df["col3"] = df.groupby("col1")['col2'].apply(lambda x: x - x.iloc[0] + 1).reset_index(drop=True)

Explanations:

  1. Use groupby to group according the column ColA

  2. For each group, subtract the first value from col2 to column col2 and add 1

  3. Reset index with reset_index and drop=False to remove the two level index.

Alexandre B.
  • 5,387
  • 2
  • 17
  • 40
  • 1
    This worked perfectly. Thank you! For the future readers, you might want to change the names of the columns under your explanations. – nielsen Apr 16 '20 at 08:34