CODEWITHSUNDEEP
pythonpandasunique
user42361
user42361

Reputation: 431

cumulative number of unique elements for pandas dataframe

i have a pandas data frame 

id tag
1  A
1  A
1  B
1  C
1  A
2  B
2  C  
2  B

I want to add a column which computes the cumulative number of unique tags over at id level. More specifically, I would like to have 

id tag count
1  A   1
1  A   1
1  B   2
1  C   3
1  A   3
2  B   1
2  C   2
2  B   2

For a given id, count will be non-decreasing. Thanks for your help!

Upvotes: 10

Views: 7721

Answers (4)

Billy Bonaros
Billy Bonaros

Reputation: 1721

You can find some other approaches in R and Python here

df = pd.DataFrame({'id':[1,1,1,1,1,2,2,2],'tag':["A","A", "B","C","A","B","C","B"]})

df['count']=df.groupby('id')['tag'].apply(lambda x: (~pd.Series(x).duplicated()).cumsum())

   id tag  count
0   1   A      1
1   1   A      1
2   1   B      2
3   1   C      3
4   1   A      3
5   2   B      1
6   2   C      2
7   2   B      2

Upvotes: 7

Anton Kopylov
Anton Kopylov

Reputation: 1

idt=[1,1,1,1,1,2,2,2]
tag=['A','A','B','C','A','B','C','B']
df=pd.DataFrame(tag,index=idt,columns=['tag'])
df=df.reset_index()
print(df)

   index tag
0      1   A
1      1   A
2      1   B
3      1   C
4      1   A
5      2   B
6      2   C
7      2   B


df['uCnt']=df.groupby(['index','tag']).cumcount()+1
print(df)

   index tag  uCnt
0      1   A     1
1      1   A     2
2      1   B     1
3      1   C     1
4      1   A     3
5      2   B     1
6      2   C     1
7      2   B     2


df['uCnt']=df['uCnt']//df['uCnt']**2
print(df)

   index tag  uCnt
0      1   A     1
1      1   A     0
2      1   B     1
3      1   C     1
4      1   A     0
5      2   B     1
6      2   C     1
7      2   B     0

df['uCnt']=df.groupby(['index'])['uCnt'].cumsum()
print(df)

   index tag  uCnt
0      1   A     1
1      1   A     1
2      1   B     2
3      1   C     3
4      1   A     3
5      2   B     1
6      2   C     2
7      2   B     2

df=df.set_index('index')
print(df)
      tag  uCnt
index          
1       A     1
1       A     1
1       B     2
1       C     3
1       A     3
2       B     1
2       C     2
2       B     2

Upvotes: 0

BrenBarn
BrenBarn

Reputation: 251365

How about this:

d['X'] = 1
d.groupby("Col").X.cumsum()

Upvotes: 1

JoeCondron
JoeCondron

Reputation: 8906

I think this does what you want: 

unique_count = df.drop_duplicates().groupby('id').cumcount() + 1
unique_count.reindex(df.index).ffill()

The +1 is because the count starts at zero. This only works if the dataframe is sorted by id. Was that intended? You can always sort beforehand.

Upvotes: 12

Related Questions