How do I count the amount of times a unique value in a dataframe meets a requirement?

Tags:



I have a total of about 9000 rows and 26 different units in the df. An example of the df is below:

Unit Total_time
E271 0 days 00:05:32

I want to create a column with the amount of times each unit appears in the df and a separate one that counts the amount of times each unit’s time is under 6 min. The new df should look like this:

Unit Total_dispatches Amount_time_under Perc_success_rate
E271 1154 883 76.5%

Any help would be appreciated!

Answer

You can do it something like this:

data = [
    {"Unit": "E1", "Total_time":pd.to_timedelta('0 days 00:05:01.00003')},
    {"Unit": "E1", "Total_time":pd.to_timedelta('0 days 00:07:01.00003')},
    {"Unit": "E1", "Total_time":pd.to_timedelta('0 days 00:05:01.00003')}  
]

df = pd.DataFrame(data)


df["Total_dispatches"] = df.groupby("Unit").transform("count")

time_limit = pd.to_timedelta('0 days 00:06:00.00000')
df["under_6_min"] = (df["Total_time"]<time_limit).astype(int)

df["Amount_time_under"] = df.groupby("Unit")["under_6_min"].transform("sum")

df["Perc_success_rate"] = (df["Amount_time_under"]/df["Total_dispatches"])*100


Source: stackoverflow