Performing Excel like countifs in Python Pandas
Last Updated :
22 Nov, 2021
In this article, we are performing an excel-like countifs in Pandas. In Excel the data is in the form of a Table, So we can perform many arithmetic operations like the sum of values, Average and count of rows, etc. by specifying the criterion on specified columns. Similarly, we can perform all those operations on Pandas DataFrame in Python. As DataFrame also maintains the data in Tabular Format.
Countifs
It is an operation used to find the count of rows by specifying one or more conditions (Same as applying a filter in an online shopping application) to get desired results. There are a few similar methods like count() such as sum(), mean(), etc. which are used to find the sum of data and average of data correspondingly.
Example 1: Performing an excel-like countifs in Pandas
Python3
import pandas as pd
costumes = pd.DataFrame({ 'Brand' : [ 'Twills' , 'Wrogn' ,
'Twills' , 'Trigger' ,
'Twills' , 'Wrogn' , ],
'Costume_Type' : [ 'Shirt' , 'Shirt' ,
'Shirt' , 'Jeans' ,
'T-Shirt' , 'Jeans' ],
'price' : [ 1699 , 1999 , 1569 ,
2000 , 569 , 2400 ]})
print (costumes)
twills_Shirt_Count = costumes.query('Brand = = "Twills" \
& Costume_Type = = "Shirt" ')[' Costume_Type'].count()
print ( 'Number of Twills Shirts-' , end = "")
print (twills_Shirt_Count)
|
Output:
Number of Twills Shirts-2
Explanation: As we have 3 Twills branded items but in that 3 we have 2 records in where costume type as shirts so it returned 2 as result.
Example 2: Here also we use the same above DataFrame but instead of finding the count of Twills branded shirt find Count of Shirts of any Brand.
Python3
import pandas as pd
costumes = pd.DataFrame({ 'Brand' : [ 'Twills' , 'Wrogn' ,
'Twills' , 'Trigger' ,
'Twills' , 'Wrogn' , ],
'Costume_Type' : [ 'Shirt' , 'Shirt' ,
'Shirt' , 'Jeans' ,
'T-Shirt' , 'Jeans' ],
'price' : [ 1699 , 1999 , 1569 ,
2000 , 569 , 2400 ]})
print (costumes)
Shirt_Count = costumes.query( 'Costume_Type=="Shirt"' )
[ 'Costume_Type' ].count()
print ( '\nNumber of Shirts-' , end = "")
print (Shirt_Count)
|
Output:
Example 3: Using the above Costume DataFrame find the count of jeans whose price is less than or equal to 2000
Python3
import pandas as pd
costumes = pd.DataFrame({ 'Brand' : [ 'Twills' , 'Wrogn' ,
'Twills' , 'Trigger' ,
'Twills' , 'Wrogn' , ],
'Costume_Type' : [ 'Shirt' , 'Shirt' ,
'Shirt' , 'Jeans' ,
'T-Shirt' , 'Jeans' ],
'price' : [ 1699 , 1999 , 1569 ,
2000 , 569 , 2400 ]})
print (costumes)
Jeans_Count = costumes.query( 'Costume_Type=="Jeans" & price<=2000' )[
'Costume_Type' ].count()
print ( '\nNumber of Jeans below or equals to Rs.2000-' , end = " " )
print (Jeans_Count)
|
Output:
Share your thoughts in the comments
Please Login to comment...