How to count the occurrence of specific items on grouped items of a csv file using python

user_15592901 0
Viewed 67

I have a .csv file with the following columns

Concelho    Classe 
Lisbon  A+
Porto   C
Faro    B-
Porto   A+
Porto   B-
Lisbon  B-
Lisbon  C
Lisbon  A+
Lisbon  A+
Lisbon  A+
Lisbon  B-
Lisbon  D
Lisbon  B-
Porto   E
Porto   E
Faro    C
Faro    B-
Faro    C

And I would like to obtain a dataframe where I have grouped "concelho" and the number of occurrences of classes (B-, A, A+): such as...

Concelho    count Class (B-,A, A+)
Lisbon  7
Porto   2
Faro    2

I tried to use groupby.count() or groupby.value_counts() from panda but I didn't manage to define the counting conditions (Classe = B- or A, or A+)

pandas count csv python
1 Answers

Use boolean masking,groupby(), isin() and rename() method:

resultdf=df[df['Classe'].isin(['B-','A','A+'])].groupby('Concelho',as_index=False).agg({'Classe':'count'}).rename(columns={'Classe':'count'})

Now if you resultdf you will get your desired output:

    Concelho    count
0   Faro          2
1   Lisbon        7
2   Porto         2

Or

you can skip the use of rename() method by using agg() method with named tuples

So you can also do this by using boolean masking,groupby(), isin() method:

resultdf=df[df['Classe'].isin(['B-','A','A+'])]\
                         .groupby('Concelho',as_index=False)\
                         .agg(count=('Classe','count'))
user_142898920
Related