I have a dataframe like this.

booking_id | booking_category | vehicle_number |
---|---|---|

1 | x | abc |

2 | x | def |

3 | y | abc |

4 | y | ghi |

5 | z | ghi |

6 | z | ghi |

7 | z | abc |

8 | z | abc |

9 | x | def |

10 | x | ghi |

I need to get the unique count of vehicle_number who have done booking in only ‘x’ category. For example in this dataframe “def” is the only vehicle_number who have done bookings in “x” category. So the count will be 1. I dont want to run a loop as it will take lots of time.

## Answer

Create a frequency table with `crosstab`

, then check for the counts to make sure only `x`

category has count greater than `0`

s = pd.crosstab(df['vehicle_number'], df['booking_category']) m = s.pop('x').ge(1) & s.eq(0).all(1)

Details

>>> s booking_category x y z vehicle_number abc 1 1 2 def 2 0 0 ghi 1 1 2 >>> m vehicle_number abc False def True ghi False dtype: bool

Result

>>> m.sum() 1 >>> list(m[m].index) ['def']