CODEWITHSUNDEEP
pythonpandasgroup-by
user3202086
user3202086

Reputation: 17

pandas group by on column values and get count

I have result, student, version and status columns. in this I want to do group by using Student , Version and result = pass count and result = fail count

similarly like df.groupby(["student", "version", "result=pass"]).size().reset_index(name="new_result")

the below is my data frame

result student version status
pass Student-A L-1.0 Active
fail Student-A L-1.0 Active
fail Student-A L-1.0 Active
pass Student-A M-1.0 Active
fail Student-A M-1.0 Active
pass Student-B N-1.0 Active
pass Student-B N-1.0 Active
pass Student-B N-1.0 Active
pass Student-C O-1.0 Active
pass Student-C O-1.0 Active
fail Student-C O-1.0 Active
fail Student-C P-1.0 Active
fail Student-C P-1.0 Active

I want my output data frame as below:

student version pass_count fail_count status total_count (pass+fail)
Student-A L-1.0 1 2 Active 3
Student-A M-1.0 1 1 Active 2
Student-B N-1.0 3 0 Active 3
Student-C O-1.0 1 1 Active 2
Student-C P-1.0 0 2 Active 2

I am able to get pass and fail count using the below but not total count, anyone pls help

pd.pivot_table(master_df, index=['status', 'student', 'version'], columns=['result'], aggfunc=len, fill_value=0)

Upvotes: 0

Views: 2600

Answers (1)

SeaBean
SeaBean

Reputation: 23207

If you want to get the total count, you can use:

df.groupby(["student", "version"])['result'].count().reset_index(name='total_count')

Result:

     student version  total_count
0  Student-A   L-1.0            3
1  Student-A   M-1.0            2
2  Student-B   N-1.0            3
3  Student-C   O-1.0            3
4  Student-C   P-1.0            2

Or, if you want to get it based on your pivot table, you can use:

df2 = pd.pivot_table(df, index=['status', 'student', 'version'], columns=['result'], aggfunc=len, fill_value=0).reset_index().rename_axis(columns=None)

df2['total_count'] = df2['fail'] + df2['pass']

Result:

print(df2)

   status    student version  fail  pass  total_count
0  Active  Student-A   L-1.0     2     1            3
1  Active  Student-A   M-1.0     1     1            2
2  Active  Student-B   N-1.0     0     3            3
3  Active  Student-C   O-1.0     1     2            3
4  Active  Student-C   P-1.0     2     0            2

Upvotes: 1

Related Questions