pandas.DataFrame.value_counts#
Return a Series containing counts of unique rows in the DataFrame.
New in version 1.1.0.
Columns to use when counting unique combinations.
normalize bool, default False
Return proportions rather than frequencies.
sort bool, default True
Sort by frequencies.
ascending bool, default False
Sort in ascending order.
dropna bool, default True
Don’t include counts of rows that contain NA values.
New in version 1.3.0.
Equivalent method on Series.
The returned Series will have a MultiIndex with one level per input column but an Index (non-multi) for a single label. By default, rows that contain any NA values are omitted from the result. By default, the resulting Series will be in descending order so that the first element is the most frequently-occurring row.
How to use Pandas Count and Value_Counts
Counting number of Values in a Row or Columns is important to know the Frequency or Occurrence of your data.
In this post we will see how we to use Pandas Count() and Value_Counts() functions
Let’s create a dataframe first with three columns A,B and C and values randomly filled with any integer between 0 and 5 inclusive
Pandas Count Number of Rows and Columns
First find out the shape of dataframe i.e. number of rows and columns in this dataframe
Here 5 is the number of rows and 3 is the number of columns
Pandas Count Values for each Column
We will use dataframe count() function to count the number of Non Null values in the dataframe.
We will select axis =0 to count the values in each Column
You can count the non NaN values in the above dataframe and match the values with this output
Pandas Count Values for each row
Change the axis = 1 in the count() function to count the values in each row. All None, NaN, NaT values will be ignored
Pandas Count Along a level in multi-index
Now we will see how Count() function works with Multi-Index dataframe and find the count for each level
Let’s create a Multi-Index dataframe with Name and Age as Index and Column as Salary
In this Multi-Index we will find the Count of Age and Salary for level Name
You can set the level parameter as column “Name” and it will show the count of each Name Age and Salary
Brian’s Age is missing in the above dataframe that’s the reason you see his Age as 0 i.e. No value available for his age but his Salary is present so Count is 1
Pandas Count Groupby
You can also do a group by on Name column and use count function to aggregate the data and find out the count of the Names in the above Multi-Index Dataframe function
Note: You have to first reset_index() to remove the multi-index in the above dataframe
Alternatively, we can also use the count() method of pandas groupby to compute count of group excluding missing values
if you want to write the frequency back to the original dataframe then use transform() method. You can learn more about transform here.
Pandas Count rows with Values
There is another function called value_counts() which returns a series containing count of unique values in a Series or Dataframe Columns
Let’s take the above case to find the unique Name counts in the dataframe
Sort by Frequency
You can also sort the count using the sort parameter
Sort by Ascending Order
Sort the frequencies in Ascending order
Value Counts Percentage or Relative Count
You can also get the relative frequency or percentage of each unique values using normalize parameters
Now Chris is 40% of all the values and rest of the Names are 20% each
Binning
Rather than counting you can also put these values into bins using the bins parameter
This works only for Numeric data
Pandas Value Count for Multiple Columns
value_counts() method can be applied only to series but what if you want to get the unique value count for multiple columns?
No need to worry, You can use apply() to get the count for each of the column using value_counts()
Let’s create a new dataframe
Apply pd.Series.value_counts to all the columns of the dataframe, it will give you the count of unique values for each row
Now change the axis to 0 and see what result you get, It gives you the count of unique values for each column
Alternatively, you can also use melt() to Unpivot a DataFrame from wide to long format and crosstab() to count the values for each column
Pandas Count Specific Values in Column
You can also get the count of a specific value in dataframe by boolean indexing and sum the corresponding rows
If you see clearly it matches the last row of the above result i.e. count of value 1 in each column
Pandas Count Specific Values in rows
Now change the axis to 1 to get the count of columns with value 1 in a row
You can see the first row has only 2 columns with value 1 and similarly count for 1 follows for other rows
Conclusion
Finally we have reached to the end of this post and just to summarize what we have learnt in the following lines:
- Pandas count value for each row and columns using the dataframe count() function
- Count for each level in a multi-index dataframe
- Pandas value_counts() method to find frequency of unique values in a series
- How to apply value_counts on multiple columns
- Count a Specific value in a dataframe rows and columns
if you know any other methods which can be used for computing frequency or counting values in Dataframe then please share that in the comments section below
Categories: Pandas , Python
Updated: March 9, 2020
Share on
You may also enjoy
How to get True Positive, False Positive, True Negative and False Negative from confusion matrix in scikit learn
In machine learning, we often use classification models to predict the class labels of a set of samples. The predicted labels may or may not match the true .
Pandas how to use list of values to select rows from a dataframe
In this post we will see how to use a list of values to select rows from a pandas dataframe We will follow these steps to select rows based on list of value.
How to make two plots side by side and create different size subplots in python using matplotlib
In this post we will see how to plot multiple sub-plots in the same figure. We will follow the following steps to create matplotlib subplots: Plot .
How to prevent xticks overlapping in matplotlib
In this article, we will explore how to prevent overlapping x-axis tick labels. When plotting data in a graph, the labels of the x and y axes may sometimes o.
Pandas – Count occurrences of value in a column
In this tutorial, we will look at how to count the occurrences of a value in a pandas dataframe column with the help of some examples.
Pandas value_counts() function
You can use the pandas series value_counts() function to count occurrences of each value in a pandas column. The following is the syntax:
It returns a pandas series containing the counts of unique values.
Let’s look at some examples of using the value_counts() function to get the count of occurrences of values in a column.
First, we will create a sample dataframe that we will be using throughout this tutorial.
We have created a dataframe storing the information on the Olympics performance of the legendary sprinter Usain Bolt. We will be using this dataframe throughout this tutorial.
Count occurrences of each unique value in the Column
Apply the pandas value_counts() function on the desired column. For example, let get the value counts in the “Event” column of the dataframe df. This will show the different events and their counts where Usain Bolt won an Olympics medal.
We get a pandas series with each unique value and its respective count in the “Event” column. You can see that Usain Bolt won three medals each in the “100 m” and the “200 m” event and two medals in the “4×100 m” event at the Olympics. Note that all these medals are gold medals.
Count occurrences of values in terms of proportion
At times you may want to know the proportion of each value in the column. For example, what proportion of Usain Bolt’s medals at the Olympics came from the “100 m” event. Pass normalize=True to the value_counts() function.
We now get the counts normalized as proportions.
Count occurrences of a specific value in a column
The return value from the pandas value_counts() function is a pandas series from which you can access individual counts. For example, to just count the occurrences of “200 m” in the “Event” column –
Here, we get the count of medals won in the “200 m” category by Usain Bolt as 3.
For more on the pandas value_counts() function, refer to its documentation.
With this, we come to the end of this tutorial. The code examples and results presented in this tutorial have been implemented in a Jupyter Notebook with a python (version 3.8.3) kernel having pandas version 1.0.5
Subscribe to our newsletter for more informative guides and tutorials.
We do not spam and you can opt out any time.
Counting Values in Pandas with value_counts
In this tutorial, you’ll learn how to use the Pandas value_counts method to count values in your DataFrame and to create frequency tables. Being able to count values in your dataset is an important step in better understanding the distribution of your data. The Pandas .value_counts() method is packed full of helpful parameters to allow you to fine-tune your analysis.
By the end of this tutorial, you’ll have learned:
- How to use the Pandas .value_counts() method
- How to create absolute and relative frequencies with the method
- How to handle missing data
- How to simplify binning of a column
- How to combine the .value_counts() method with the .groupby() method to explore frequencies across different groups of data
Table of Contents
Exploring the Pandas value_counts Method
The Pandas value_counts() method can be applied to both a DataFrame column or to an entire DataFrame. The behavior varies slightly between the two methods. However, for the most part, the methods will work exactly the same. There’ll be a note indicating if there are differences between the two methods.
Let’s take a look at the how the method looks and the default arguments that exist:
The method has only optional parameters, meaning if you simply want to calculate value counts you can apply the method directly without needing to worry about any arguments being passed in.
Loading a Sample Pandas DataFrame
To follow along with this tutorial, load the dataset below by copying and pasting the provided code. If you have your own data, feel free to use that dataset but your results will, of course, vary.
The dataset only has three columns, two of which can be considered categorical. The data covers different grades and class types, as well as indicating how many students are in that grade and level.
Creating a Pandas Frequency Table with value_counts
In this section, you’ll learn how to apply the Pandas .value_counts() method to a Pandas column. For example, if you wanted to count the number of times each value appears in the Students column, you can simply apply the function onto that column.
Let’s see how this looks without passing in any arguments, which will be covered off later in the tutorial.
The method returns a Series containing all the different values. Because the return type is a Series, you can easily access the data inside it using indexing. For example, if you wanted to see how often there were 20 students in a class, you could access the [20] index:
In the next section, you’ll learn how to sort your Pandas frequency table.
Sorting Your Pandas Frequency Table
Sorting a frequency table generated by the Pandas value_counts method is controlled by two different parameters. First, the sort= parameter controls whether to sort data. The ascending= parameter controls how that data is sorted. By default, Pandas will sort the data in descending order.
We can modify this behavior to sort in descending order by modifying the ascending= parameter to False . This can be helpful if you want to get a sense of the least frequent values in that dataset.
In this case, our least frequent values appears higher in the returned Series.
By toggling sorting off entirely, data are simply ordered in the order in which Pandas encounters them in your dataset. In the next section, you’ll learn how to sort the returned values by their label.
Sorting Your Pandas Frequency Table by Index
In the previous section, you learned that the .value_counts() method returns a Series that can be sorted or unsorted. There may be times, however, when you want to sort the Series by the labels of the data (i.e., its category), rather than the frequencies.
This can be accomplished by chaining the .sort_index() method onto the returned Series. The data can either be sorted in ascending order or descending order, by using the ascending= parameter. Let’s sort our frequency table by their categories in ascending order.
In the next section, you’ll learn how to calculate a Pandas value counts table that uses normalized percentages, rather than values.
Calculating a Pandas Frequecy Table with Percentages
Pandas also makes it very easy to display these frequencies as percentages. This can make the data much simpler to understand. In order to transform the counts into percentages, you can use the normalize= parameter. By default, the argument is set to False , but toggling it to True converts the values:
Remember, by default, Pandas will sort the values in descending order. Normalizing the data allows you to more easily how much of a dataset the category represents.
Binning Data in Pandas Frequency Tables
The Pandas value_counts method can also be used to bin data into different equal sized groups. This method is a convenience function for the Pandas .cut() method, and provides the number of values in each group.
Let’s split the data into four numeric groups using the bin= parameter:
Dealing with Missing Values in Pandas Frequency Tables
In this section, you’ll learn how to work with missing data while working with the .value_counts() method. In order to cover this section off in better detail, let’s insert some missing values into our DataFrame.
Now that we have missing values in our DataFrame, let’s apply the method with its default parameters and see how the results look:
By default, the method will drop any missing values. It can often be useful to include these values. This can be done by passing in True into the dropna= parameter.
Calculating a Frequency Table on Multiple Columns with value_counts
The .value_counts() can also be applied the multiple columns. The benefit of applying the method to the entire DataFrame is that you gain access to the subset= parameter. This allows you to pass in a list of columns, which will return the values in the cross-section of columns. Let’s see what the distribution of values is across the Grade and Class Type columns.
Right now, this returns a fairly messy Series. You can clean this up by sorting the index to better understand the hierarchy.
Exercises
It’s time to check your learning! Try and complete the exercises below. To solve the exercises, use the DataFrame provided below:
To verify your solution, simply toggle the question.
How often does the second-highest number of students appear in the dataset?
What percentage of values in the Students column are missing?
The .idxmax() method returns the index of the maximum value in a Series or DataFrame. Use the method to find the Grade that appears most often.
Conclusion and Recap
In this tutorial, you learned how to use the .value_counts() method to calculate a frequency table counting the values in a Series or DataFrame. The section below provides a recap of what you learned: