CODEWITHSUNDEEP
pythonpandas
Py_Student
Py_Student

Reputation: 159

Need assistance calculating unique values from multiple pandas columns

I have a pd dataframe with two fields: DBA Name (facility name) & License#. There are multiple listings of the DBA name and some have the same license while others do not.

I want to find out how many instances of all the DBA names there are. Also I want to find out how many unique License #s they each have.

I tried using value_counts() but that only works with one field in a pandas df. I also tried using apply(), but that did not work.

I have showed sample code below. Give me your thoughts please. 


data = data[['DBA Name','License #']]

data:
        DBA Name                    License #
1   BUSY BUMBLE BEE ACADEMY DAYCARE 2215472.0
2   BUSY BUMBLE BEE ACADEMY DAYCARE 3793.0
3   BUSY BUMBLE BEE ACADEMY DAYCARE 2215472.0
4   BUSY BUMBLE BEE ACADEMY DAYCARE 1194190.0
5   BUSY BUMBLE BEE ACADEMY DAYCARE 2215472.0
6   BUSY BUMBLE BEE ACADEMY DAYCARE 1194190.0
7   BUSY BUMBLE BEE ACADEMY DAYCARE 1194190.0
8   BUSY BUMBLE BEE ACADEMY DAYCARE 3793.0
9   BUSY BUMBLE BEE ACADEMY DAYCARE 3793.0
10  BOTTLES TO BOOKS LEARNING CENTER 1943545.0
11  BOTTLES TO BOOKS LEARNING CENTER 1943545.0
12  BOTTLES TO BOOKS LEARNING CENTER 1926534.0
13  BOTTLES TO BOOKS LEARNING CENTER    1926534.0
14  BOTTLES TO BOOKS LEARNING CENTER    1926534.0
15  BOTTLES TO BOOKS LEARNING CENTER    1943545.0
16  BOTTLES TO BOOKS LEARNING CENTER    1926534.0
17  BOTTLES TO BOOKS LEARNING CENTER    1943545.0
18  A CHILD'S WORLD EARLY LEARNING CENTER   1357825.0
19  A CHILD'S WORLD EARLY LEARNING CENTER   1357825.0
20  A CHILD'S WORLD EARLY LEARNING CENTER   1768092.0
21  A CHILD'S WORLD EARLY LEARNING CENTER   1768092.0
22  A CHILD'S WORLD EARLY LEARNING CENTER   1357825.0
23  A CHILD'S WORLD EARLY LEARNING CENTER   1768092.0
24  A CHILD'S WORLD EARLY LEARNING CENTER   1357825.0

Upvotes: 0

Views: 58

Answers (1)

Chris
Chris

Reputation: 29742

Use pd.DataFrame.groupby with nunique and agg:

import pandas as pd

df.groupby('DBA Name').agg({'DBA Name': 'count', 'License #': 'nunique'})

Output:

                                       DBA Name  License #
DBA Name                                                  
A CHILD'S WORLD EARLY LEARNING CENTER         7          2
BOTTLES TO BOOKS LEARNING CENTER              8          2
BUSY BUMBLE BEE ACADEMY DAYCARE               9          3

Upvotes: 2

Related Questions