How to count number of rows that follow a condition in two columns in pandas using Groupby
I have a data frame with multiple columns, 4 of which are
car has an associated
status, and each
company_name is linked to a unique
ID. One possible status is
Rented and I'm trying to count the number of
Rented cars for each company (in a new column called # of Rented Cars) and I have been trying to use their unique ids to do so.
I have tried using
groupby and apply but to no success.
df['# of Rented Cars'] = df.groupBy('unique_id')['status'].apply(lambda x: (x=='Rented').sum())
Using the following table as an example, you can see the values I want in the # of Rented Cars Column:
But using the code above I just get the value Nan for all values in the last column.
I think you are looking for
df['# of Rented Cars'] = df.groupBy('unique_id')['status'].transform(lambda x: (x=='Rented').sum())
df['# of Rented Cars'] = df['status'].eq('Rented').groupBy(df['unique_id']).transform('sum')