0

The Table for required data is provided below

I want to group the columns by User Name first and count the occurences of a single disposition code for that username

User Name Disposition Code
person1    Solved
person2    Solved
person1    Solved
person2    Not Solved
person3    Solved

Result required:

User Name Total Solved Total Not Solved
person1     2              0
person2     1              1
peson3      1              0
Erfan
  • 40,971
  • 8
  • 66
  • 78
Alter Ego
  • 71
  • 1
  • 7

2 Answers2

2

You want pd.crosstab:

pd.crosstab(index=df['UserName'], columns=df['DispositionCode'])

Disposition Code  Not Solved  Solved
UserName                          
person1                  0       2
person2                  1       1
person3                  0       1
yatu
  • 86,083
  • 12
  • 84
  • 139
-3

Also you can use groupbywith value_counts + unstack:

new_df=df.groupby('User_Name')['Disposition_Code'].apply(lambda x: x.value_counts()).unstack(fill_value=0)
print(new_df)

           Not_Solved  Solved
User_Name                    
person1             0       2
person2             1       1
person3             0       1

To get a join_df:

Option 1

join_df=df.copy()
join_df['count']=new_df.lookup(df['User_Name'],df['Disposition_Code'])
print(join_df)

  User_Name Disposition_Code  count
0   person1           Solved      2
1   person2           Solved      1
2   person1           Solved      2
3   person2       Not_Solved      1
4   person3           Solved      1

Option 2

join_df=df.copy()
join_df['count']=df.groupby(['User_Name','Disposition_Code'])['User_Name'].transform('size')
ansev
  • 30,322
  • 5
  • 17
  • 31
  • And how do i join the both old_df and new_df in same table with Key="User Name"? as i need to write to CSV file – Alter Ego Oct 30 '19 at 11:27
  • Then you would need to add an additional column. Look at the code that I added in my answer. Please consider voting my answer. Thank you!. – ansev Oct 30 '19 at 11:49
  • if you consider join_df incorrect in my answer you could add what would be the join_df dataframe that you want to get in your question – ansev Oct 30 '19 at 11:54
  • I am getting error mentioned below while trying to execute the join KeyError: 'One or more row labels was not found' – Alter Ego Oct 30 '19 at 12:57
  • then use: `join_df=df.copy() join_df['count']=df.groupby(['User_Name','Disposition_Code'])['User_Name'].transform('size')` – ansev Oct 30 '19 at 12:58
  • test.groupby("User Name").agg({"Total Offered Calls": ['sum'] , "Calls Received": ['mean'] , "Total Served In Target": ['sum'] , "Total Served In Target %": ['mean'] , "DispositionCode": ['count'] , "Total Served After Target": ['sum']}) Dispostion code is a string. how do i count it. as it is giving error. not 1 dimensional. – Alter Ego Oct 30 '19 at 13:20