Trying to show Count by department in Python

Question

This is a simple piece of code for some reason I can't think of at the moment. I am trying to get the count of task by status by department. For instance something like this:

 Department      Task           Status
   Sales         Sell           Pendiing
   Sales         Presentation   Complete
   Tech          Merge Data     Complete 
   Tech          Consolidate    Pending 
   Tech          Presentation   Complete

What I want for here is to be able to break down by department of the count of completed in the status column. Something like this:

Department    Status        Count
  Sales       Completed       1
  Tech        Completed       2

So far my code sees the count for all departments but I can't figure out the best way to group by.

Code for reference:

counts = df['Department'].groupby('Status').count()

Answer 1

You need to groupby Department and Status; you can then count each group and rename the Task column if desired. Then you can use reset_index if required to return a row-indexed dataframe:

df2 = df.groupby(['Department', 'Status']).count().rename(columns={'Task':'Count'}).reset_index()

Output (for your sample data):

  Department    Status Count
0      Sales  Complete     1
1      Sales  Pendiing     1
2       Tech  Complete     2
3       Tech   Pending     1

You can then filter that on Status if required:

df2[df2['Status'] == 'Complete']

Output:

  Department    Status  Count
0      Sales  Complete      1
2       Tech  Complete      2