Pandas III: value_counts(), duplicated(), min(), max()

In Pandas II, we began to clean up the Metal Bands by Nation data set. We eliminated an unneeded column and filled some missing values. Now, we are going to examine the other columns, assess their data types and value, and take action as needed.

Image by Nick115 from Pixabay

This blog is part of a series of tutorials called Data in Day. Follow these tutorials to create your first end-to-end data science project in just one day. This is a fun easy project that will teach you the basics of setting up your computer for a data science project and introduce you to some of the most popular tools available. It is a great way to get acquainted with the data science workflow.

  1. General Setup for Data Science Projects with Python
  2. Virtual Environments I: Installing Pyenv with Homebrew
  3. Virtual Environments II: Creating a Virtual Environment with Pyenv and Installing Data Science Packages
  4. Jupyter Notebooks I: Getting Started with Jupyter Notebooks
  5. GitHub I: Getting Started with GitHub
  6. Pandas I: read_csv(), head(), tail(), info(), and describe()
  7. Pandas II: drop(), isna()
  8. Pandas III: value_counts(), duplicated(), min(), and max()

I. Examining Data Types

  1. Open your project notebook, then click Kernel > Restart and Run All to re-execute our previous work.

2. It would be good to see what other kinds of values we are dealing with. According to what we learned from entering info we have five object (non-numeric) columns, and one numeric column. The numeric column is our target variable — the number of fans (more on that later). The rest of the data is categorical data that informs us about the qualities of the different bands.

II. Value Counts in Python with Pandas

4. The next column we will look at is “origin”. First, we will check the unique values using value_counts(), like this:


There are many values, and Pandas is only displaying the head and the tail of the results. We can wrap the line of code above in len to find out exactly how many unique countries of origin that we have.


III. Duplicates in Python with Pandas

3. Five thousand metal bands seems like a lot, so maybe we can check for duplicates just to make sure all of the rows are unique bands. We can check it like this:


This returns the number of rows where the value of “band_name” is a duplicate. We can drop them using duplicated():

df = df.drop(df[df[“band_name”].duplicated()].index)

Your notebook should look something like this:

IV. Using min() and max() with Python and Pandas

5. Next, we have the column “formed”. Calling value_counts() will return all the unique years and the frequency by which they appear. However, it might be more helpful to find out the earliest and latest years so we can get a range. Enter this into the next cell:


Looking at the min() and max() revealed another problem. A few of the rows have a value of “-“. Now, we need to make a decision if we are going to drop those rows, or replace those values.

Even though info() indicated that “formed” is an object column, year data is actually numeric. So if we decide to replace “-“, using a substitute like “Missing” will cause problems later on. We are better off using a year that does not appear anywhere else in the data, like “1900”.

V. Transforming Data in Python with Pandas using replace() and astype()

7. You can take care of that replacement by using replace(). To apply replace() to a column in a Pandas data frame, you can use this:

df[“formed”] = df[“formed”].replace(“-”, “1900”)

Now, our min and max should be 1900–2016. If you need to see the actual minimum, simply enter this:

df[df[“formed”] != “1900”][‘formed’].min()

This will display the minimum value of the column `“formed”` in part of df such that the column `“formed”` does not (`!=`) have a value of 1900.

8. Now that we eliminated non-numeric characters from the “formed” column, we can transform the entire column into a numeric column with astype():

df[“formed”] = df[“formed”].astype(int)

9. Now, let’s do the same for the “split” column, since it is also a year, which is numeric data. That is, check the values with value_counts(), replace the “-“ with replace(), and change the data type with astype(). We will also check the min() and max().

VI. What Did We Do?

  1. Examined the data types of columns using info().
  2. Addressed duplicates with duplicated().
  3. Addressed nulls using isna() and fillna().
  4. Checked ranges using min() and max().
  5. Cleaned year data in “formed” and “split” with replace().
  6. Changed the data type of a column using .astype().

VII. What’s Next?

In Pandas IV, we continue the analysis of the Metal Bands by Nation data set by analyzing and transforming the “style” column.

🐍 Python Enthusiast 🐼 Pandas Aficionado ⭐ Experience NLP using SpaCy and NLTK 🔎 Relentlessly curious 👩🏻‍💻

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store