# 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 necessary packages``import` `pandas as pd`` ` `# create a dataframe``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``]})`` ` `# DataFrame``print``(costumes)`` ` `# find count of Twills Shirts``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 necessary packages``import` `pandas as pd`` ` `# create a dataframe``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``]})`` ` `# DataFrame``print``(costumes)`` ` `# find count of Twills Shirts``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 necessary packages``import` `pandas as pd`` ` `# create a dataframe``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``]})`` ` `# DataFrame``print``(costumes)`` ` `# find count of Twills Shirts``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:

My Personal Notes arrow_drop_up