0

colA is what I currently have.

However, I'm trying to generate colB.

I want colB to contain the number 001 for each value. However if the associated colA value exists twice in that column, I want the colB number to then be 002, and so on.

Hopefully the example below gives a better idea of what I'm looking for based on the colA values. I've been struggling to put together any real code for this.

EDIT: Struggling to explain this in words, so if you can think of a better way to explain it feel free to update my question.

colA   colB
BJ02   001
BJ02   002
CJ02   001
CJ03   001
CJ02   002
DJ01   001
DJ02   001
DJ07   001
DJ07   002
DJ07   003
  • 3
    you can get most of it with `df.groupby('colA').cumcount()+1`, do you need the exact same format with eading 0 as you described? – Ben.T Oct 18 '21 at 21:04
  • For string formatting, you can follow up with something like `colB.astype(str).str.zfill(3)`. – hilberts_drinking_problem Oct 18 '21 at 21:15
  • 1
    The canonical [groupby cumcount](https://stackoverflow.com/q/23435270/15497888). Links to similar cumcount + zfill, https://stackoverflow.com/q/67631910/15497888, https://stackoverflow.com/q/48443698/15497888, https://stackoverflow.com/q/57986728/15497888 – Henry Ecker Oct 18 '21 at 21:19

2 Answers2

2

Use groupby_cumcount:

df['colB'] = df.groupby('colA').cumcount().add(1)
print(df)

# Output
   colA  colB
0  BJ02     1
1  BJ02     2
2  CJ02     1
3  CJ03     1
4  CJ02     2
5  DJ01     1
6  DJ02     1
7  DJ07     1
8  DJ07     2
9  DJ07     3

Suggested by @HenryEcker, use zfill:

df['colB'] = df.groupby('colA').cumcount().add(1).astype(str).str.zfill(3)
print(df)

# Output:
   colA colB
0  BJ02  001
1  BJ02  002
2  CJ02  001
3  CJ03  001
4  CJ02  002
5  DJ01  001
6  DJ02  001
7  DJ07  001
8  DJ07  002
9  DJ07  003
Corralien
  • 109,409
  • 8
  • 28
  • 52
  • 1
    [`.astype(str).str.zfill(3)`](https://stackoverflow.com/questions/69622493/add-column-with-numbers-based-on-count-of-value-in-other-column-in-pandas#comment123061802_69622493) could be added to match output above. Like @hilberts_drinking_problem recommended – Henry Ecker Oct 18 '21 at 21:20
0

You can use Counter() to count the frequency of each value in colA, then create a function to generate a list of values for colB.

from collections import Counter    
def count_value(colA):
     new_col = []
     colA = df[colA].tolist()
     freq_table = Counter(colA) # count the frequency of each value
     for value in colA:
          new_col.append('00' + str(freq_table[value]))
     return new_col
df['colB'] = count_value(df['colA'])
Newcomer
  • 73
  • 7