CODEWITHSUNDEEP
pandaspandas-groupby
Danish
Danish

Reputation: 2871

GroupBy unique aggregation and with specific condition in pandas

I have a dataframe as shown below

UnitID    Sector    Start_Date      Status
1         SE1       2018-02-26      Closed
1         SE1       2019-03-27      Active
2         SE1       2017-02-26      Closed
2         SE1       2018-02-26      Closed
2         SE1       2019-02-26      Active
3         SE1       NaT             Not_in_contract
4         SE1       NaT             Not_in_contract
5         SE2       2017-02-26      Closed
5         SE2       2018-02-26      Closed
5         SE2       2019-02-26      Active
6         SE2       2018-02-26      Closed
6         SE2       2019-02-26      Active
7         SE2       2018-02-26      Closed
7         SE2       2018-07-15      Closed
8         SE2       NaT             Not_in_contract
9         SE2       NaT             Not_in_contract
10        SE2       2019-05-22      Active
11        SE2       2019-06-24      Active

From the above I would like to prepare below data frame

Sector      Number_of_unique_units     Number_of_Active_units
SE1         4                          2
SE2         7                          4

Upvotes: 1

Views: 262

Answers (1)

jezrael
jezrael

Reputation: 863166

Use GroupBy.agg with DataFrameGroupBy.nunique and custom lambda function with count number of Active by sum of boolean mask:

df1=(df.groupby('Sector').agg(Number_of_unique_units=('UnitID','nunique'),
                              Number_of_Active_units=('Status',lambda x:x.eq('Active').sum()))
                         .reset_index())
print (df1)
  Sector  Number_of_unique_units  Number_of_Active_units
0    SE1                       4                       2
1    SE2                       7                       4

Upvotes: 1

Related Questions