How to append a new column on to an existing dataframe that contains a conditional count which is al-CodePudding

I am trying to add a new column to my existing dataframe that contains a conditional count, grouped by another column in the dataframe.

In my example I am counting when "Severity" ==2 and grouping by "Data Tech Group". I am achieving this count by doing the below:

df.loc[df.Severity == 2].groupby("AssignedGroup").count()

Here is my current dataframe:

Data Tech Group       Sev 1-4 count
LHR53 Data Tech             19
LHR56 Data Tech             10
LHR57 Data Tech             18
LHR54 Data Tech             76
LHR64 Data Tech              5
LHR55 Data Tech            137
LHR59 Data Tech            149

Below is the data frame I want to achieve, where if "Data Tech Group" does not meet the condition (if there are no Sev2 tickets or value is NaN), it is marked in the row as 0.

 Data Tech Group       Sev 1-4 count   Sev 2 count
    LHR53 Data Tech             19          1
    LHR56 Data Tech             10          0
    LHR57 Data Tech             18          3
    LHR54 Data Tech             76          0
    LHR64 Data Tech              5          0
    LHR55 Data Tech            137          1
    LHR59 Data Tech            149          2

Also, I am not sure if I need to mention this, but the index I have specified manually and is based on "Data Tech Group".

CodePudding user response：

I am not familiar with a direct answer, but having understood what you need follow this method. It'll get the answer. If I get a shorter fancier one, I'll amend the answer.

Make a duplicate of the df. Evaluate df.loc[df.Severity == 2].groupby("AssignedGroup").count() similar to what you did with Sev 1-4 count
Use idx_common = df1.index.intersection(df2.index.intersection) to get the common indexes to both cases.
Use either df1 or df2 to filter the necessary indexes with df = df1[idx_common]