python lambda to increment the value based on existing column

Question

I have two columns Letter & Values, new column should have incremented value based on "Letter" column as shown below:

import pandas as pd

df = pd.DataFrame(data=[['a', 'one'],
                        ['a', 'two'],
                        ['b', 'three'],
                        ['b', 'four'],
                        ['c', 'five'],
                        ['c', 'five'],
                        ['c', 'five']
                        ],
                  columns=['Letter', 'value'])


#df['counter'] = df['value'].shift().where(df['Letter'].shift() == df['Letter'], '')

print(df)

df['counter'] = df.apply(lambda x: x+1 if df['Letter'].shift() == df['Letter'] else 1, axis=1)

#print(df)
'''
Expected output
  Letter  value  counter
0      a    one   p1
1      a    two   p2
2      b  three   p1
3      b   four   p2
4      c   five   p1
5      c   five   p2
6      c   five   p3
'''

Can you please help me in fixing the above code? Thanks.

Editing my question to be more precise, I need to convert rows to columns later(i have that code working using pivot function)

score 2 · Accepted Answer · answered Sep 23 '19 at 13:41

2

There is definitely a more elegant way to do this in pandas, but I'm forgetting the name of the function:

# Create a constant, valued-at-one column for summing each row
>>> df['counter'] = df.assign(ind=1).groupby('Letter')['ind'].cumsum()
>>> df
  Letter  value  counter
0      a    one        1
1      a    two        2
2      b  three        1
3      b   four        2
4      c   five        1
5      c   five        2
6      c   five        3

answered Sep 23 '19 at 13:41

blacksite

12,086
10
64
109

3

`df['counter'] = df.groupby('Letter').cumcount()+1` – Scott Boston Sep 23 '19 at 13:47
Thanks a lot @blacksite, Scott, both solutions worked like gem. – Rakesh007 Sep 23 '19 at 13:52

python lambda to increment the value based on existing column

1 Answers1