How to Count Occurrences of Specific Value in Pandas Column?
In this article, we will discuss how to count occurrences of a specific column value in the pandas column.
Dataset in use:
We can count by using the value_counts() method. This function is used to count the values present in the entire dataframe and also count values in a particular column.
Syntax: data[‘column_name’].value_counts()[value]
where
- data is the input dataframe
- value is the string/integer value present in the column to be counted
- column_name is the column in the dataframe
Example: To count occurrences of a specific value
Python3
# import pandas module import pandas as pd # create a dataframe # with 5 rows and 4 columns data = pd.DataFrame({ 'name' : [ 'sravan' , 'ojsawi' , 'bobby' , 'rohith' , 'gnanesh' , 'sravan' , 'sravan' , 'ojaswi' ], 'subjects' : [ 'java' , 'php' , 'java' , 'php' , 'java' , 'html/css' , 'python' , 'R' ], 'marks' : [ 98 , 90 , 78 , 91 , 87 , 78 , 89 , 90 ], 'age' : [ 11 , 23 , 23 , 21 , 21 , 21 , 23 , 21 ] }) # count values in name column print (data[ 'name' ].value_counts()[ 'sravan' ]) # count values in subjects column print (data[ 'subjects' ].value_counts()[ 'php' ]) # count values in marks column print (data[ 'marks' ].value_counts()[ 89 ]) |
Output:
3
2
1
If we want to count all values in a particular column, then we do not need to mention the value.
Syntax:
data['column_name'].value_counts()
Example: To count the occurrence of a value in a particular column
Python3
# import pandas module import pandas as pd # create a dataframe # with 5 rows and 4 columns data = pd.DataFrame({ 'name' : [ 'sravan' , 'ojsawi' , 'bobby' , 'rohith' , 'gnanesh' , 'sravan' , 'sravan' , 'ojaswi' ], 'subjects' : [ 'java' , 'php' , 'java' , 'php' , 'java' , 'html/css' , 'python' , 'R' ], 'marks' : [ 98 , 90 , 78 , 91 , 87 , 78 , 89 , 90 ], 'age' : [ 11 , 23 , 23 , 21 , 21 , 21 , 23 , 21 ] }) # count all values in name column print (data[ 'name' ].value_counts()) # count all values in subjects column print (data[ 'subjects' ].value_counts()) # count all values in marks column print (data[ 'marks' ].value_counts()) # count all values in age column print (data[ 'age' ].value_counts()) |
Output:
If we want to get the results in order (like ascending and descending order), we have to specify the parameter
Syntax:
Ascending order:
data[‘column_name’].value_counts(ascending=True)
Descending Order:
data[‘column_name’].value_counts(ascending=False)
Example: To get results in an ordered fashion
Python3
# import pandas module import pandas as pd # create a dataframe # with 5 rows and 4 columns data = pd.DataFrame({ 'name' : [ 'sravan' , 'ojsawi' , 'bobby' , 'rohith' , 'gnanesh' , 'sravan' , 'sravan' , 'ojaswi' ], 'subjects' : [ 'java' , 'php' , 'java' , 'php' , 'java' , 'html/css' , 'python' , 'R' ], 'marks' : [ 98 , 90 , 78 , 91 , 87 , 78 , 89 , 90 ], 'age' : [ 11 , 23 , 23 , 21 , 21 , 21 , 23 , 21 ] }) # count all values in name column in ascending order print (data[ 'name' ].value_counts(ascending = True )) # count all values in subjects column in ascending order print (data[ 'subjects' ].value_counts(ascending = True )) # count all values in marks column in descending order print (data[ 'marks' ].value_counts(ascending = False )) # count all values in age column in descending order print (data[ 'age' ].value_counts(ascending = False )) |
Output:
Dealing with missing values
Here we can count the occurrence with or without NA values. By using dropna parameter to include NA values if set to True, it will not count NA if set to False.
Syntax:
Include NA values:
data[‘column_name’].value_counts(dropna=True)
Exclude NA Values:
data[‘column_name’].value_counts(dropna=False)
Example: Dealing with missing values
Python3
# import pandas module import pandas as pd #import numpy import numpy # create a dataframe # with 5 rows and 4 columns data = pd.DataFrame({ 'name' : [ 'sravan' , 'ojsawi' , 'bobby' , 'rohith' , 'gnanesh' , 'sravan' , 'sravan' , 'ojaswi' , numpy.nan], 'subjects' : [ 'java' , 'php' , 'java' , 'php' , 'java' , 'html/css' , 'python' , 'R' , numpy.nan], 'marks' : [ 98 , 90 , 78 , 91 , 87 , 78 , 89 , 90 , numpy.nan], 'age' : [ 11 , 23 , 23 , 21 , 21 , 21 , 23 , 21 , numpy.nan] }) # count all values in name column including NA print (data[ 'name' ].value_counts(dropna = False )) # count all values in subjects column including NA print (data[ 'subjects' ].value_counts(dropna = False )) # count all values in marks column excluding NA print (data[ 'marks' ].value_counts(dropna = False )) # count all values in age column excluding NA print (data[ 'age' ].value_counts(dropna = True )) |
Output:
Count values with relative frequencies
We are going to add normalize parameter to get the relative frequencies of the repeated data. It is set to True.
Syntax:
data[‘column_name’].value_counts(normalize=True)
Example: Count values with relative frequencies
Python3
# import pandas module import pandas as pd # create a dataframe # with 5 rows and 4 columns data = pd.DataFrame({ 'name' : [ 'sravan' , 'ojsawi' , 'bobby' , 'rohith' , 'gnanesh' , 'sravan' , 'sravan' , 'ojaswi' ], 'subjects' : [ 'java' , 'php' , 'java' , 'php' , 'java' , 'html/css' , 'python' , 'R' ], 'marks' : [ 98 , 90 , 78 , 91 , 87 , 78 , 89 , 90 ], 'age' : [ 11 , 23 , 23 , 21 , 21 , 21 , 23 , 21 ] }) # count all values in name with relative frequencies print (data[ 'name' ].value_counts(normalize = True )) |
Output:
sravan 0.375
ojaswi 0.125
ojsawi 0.125
bobby 0.125
rohith 0.125
gnanesh 0.125
Name: name, dtype: float64
Get details
If we want to get the details like count, mean, std, min, 25%, 50%,75%, max, then we have to use describe() method.
Syntax:
data['column_name'].describe()
Example: Get details
Python3
# import pandas module import pandas as pd # create a dataframe # with 5 rows and 4 columns data = pd.DataFrame({ 'name' : [ 'sravan' , 'ojsawi' , 'bobby' , 'rohith' , 'gnanesh' , 'sravan' , 'sravan' , 'ojaswi' ], 'subjects' : [ 'java' , 'php' , 'java' , 'php' , 'java' , 'html/css' , 'python' , 'R' ], 'marks' : [ 98 , 90 , 78 , 91 , 87 , 78 , 89 , 90 ], 'age' : [ 11 , 23 , 23 , 21 , 21 , 21 , 23 , 21 ] }) # get about age print (data[ 'age' ].describe()) |
Output:
count 8.000000
mean 20.500000
std 3.964125
min 11.000000
25% 21.000000
50% 21.000000
75% 23.000000
max 23.000000
Name: age, dtype: float64
Using size() with groupby()
Here this will return the count of all occurrences in a particular column.
Syntax:
data.groupby('column_name').size()
Example: Count of all occurrences in a particular column
Python3
# import pandas module import pandas as pd # create a dataframe # with 5 rows and 4 columns data = pd.DataFrame({ 'name' : [ 'sravan' , 'ojsawi' , 'bobby' , 'rohith' , 'gnanesh' , 'sravan' , 'sravan' , 'ojaswi' ], 'subjects' : [ 'java' , 'php' , 'java' , 'php' , 'java' , 'html/css' , 'python' , 'R' ], 'marks' : [ 98 , 90 , 78 , 91 , 87 , 78 , 89 , 90 ], 'age' : [ 11 , 23 , 23 , 21 , 21 , 21 , 23 , 21 ] }) # get the size of name print (data.groupby( 'name' ).size()) |
Output:
name
bobby 1
gnanesh 1
ojaswi 1
ojsawi 1
rohith 1
sravan 3
dtype: int64
Using count() with groupby()
Here this will return the count of all occurrences in a particular column across all columns.
Syntax:
data.groupby('column_name').count()
Example: Count of all occurrences in a particular column
Python3
# import pandas module import pandas as pd # create a dataframe # with 5 rows and 4 columns data = pd.DataFrame({ 'name' : [ 'sravan' , 'ojsawi' , 'bobby' , 'rohith' , 'gnanesh' , 'sravan' , 'sravan' , 'ojaswi' ], 'subjects' : [ 'java' , 'php' , 'java' , 'php' , 'java' , 'html/css' , 'python' , 'R' ], 'marks' : [ 98 , 90 , 78 , 91 , 87 , 78 , 89 , 90 ], 'age' : [ 11 , 23 , 23 , 21 , 21 , 21 , 23 , 21 ] }) # get the count of name across all columns print (data.groupby( 'name' ).count()) |
Output:
Using bins
If we want to get the count in a particular range of values, then the bins parameter is applied. We can specify the number of ranges(bins).
Syntax:
(data['column_name'].value_counts(bins)
where,
- data is the input dataframe
- column_name is the column to get bins
- bins is the total number of bins to be specified
Example: Get count in particular range of values
Python3
# import pandas module import pandas as pd # create a dataframe # with 5 rows and 4 columns data = pd.DataFrame({ 'name' : [ 'sravan' , 'ojsawi' , 'bobby' , 'rohith' , 'gnanesh' , 'sravan' , 'sravan' , 'ojaswi' ], 'subjects' : [ 'java' , 'php' , 'java' , 'php' , 'java' , 'html/css' , 'python' , 'R' ], 'marks' : [ 98 , 90 , 78 , 91 , 87 , 78 , 89 , 90 ], 'age' : [ 11 , 23 , 23 , 21 , 21 , 21 , 23 , 21 ] }) # get count of age column with 6 bins print (data[ 'age' ].value_counts(bins = 6 )) # get count of age column with 4 bins print (data[ 'age' ].value_counts(bins = 4 )) |
Output:
(19.0, 21.0] 4
(21.0, 23.0] 3
(10.987, 13.0] 1
(17.0, 19.0] 0
(15.0, 17.0] 0
(13.0, 15.0] 0
Name: age, dtype: int64
(20.0, 23.0] 7
(10.987, 14.0] 1
(17.0, 20.0] 0
(14.0, 17.0] 0
Name: age, dtype: int64
Using apply()
If we want to get a count of all columns across all columns, then we have to use apply() function. In that we will use value_counts() method.
Syntax:
data.apply(pd.value_counts)
Example: Get count of all columns across all columns
Python3
# import pandas module import pandas as pd # create a dataframe # with 5 rows and 4 columns data = pd.DataFrame({ 'name' : [ 'sravan' , 'bobby' , 'sravan' , 'sravan' , 'ojaswi' ], 'subjects' : [ 'java' , 'php' , 'java' , 'html/css' , 'python' ], 'marks' : [ 98 , 90 , 78 , 91 , 87 ], 'age' : [ 11 , 23 , 23 , 21 , 21 ] }) # get all count data. apply (pd.value_counts) |
Output:
Contact Us