CODEWITHSUNDEEP
pandas
Somendra
Somendra

Reputation: 13

Group by and take count of first occurrence Pandas Dataframe

A pandas dataframe has 2 columns.

Number Character
111 a
111 a
111 b
111 b
111 c
222 b
222 a
222 c
222 b
222 a
222 c
333 c
333 a
333 a
333 b
333 c
333 c

Now I want to find for each number group first Character column value count.

Desired Output

Number Character Count
111 a 2
222 b 2
333 c 3

so we need group by on Number and need first value in Character column count.

Upvotes: 1

Views: 948

Answers (1)

jezrael
jezrael

Reputation: 863611

first filter only first values by GroupBy.transform and GroupBy.first comparing by original values in boolean indexing and then count values by GroupBy.size:

df = df[df.groupby('Number')['Character'].transform('first').eq(df['Character'])]

df = df.groupby(['Number','Character']).size().reset_index(name='count')
print (df)    
   Number Character  count
0     111         a      2
1     222         b      2
2     333         c      3

Upvotes: 1

Related Questions