count in pandas — practice examples (Bamboo Weekly)

Count the values that are actually there — which is not the same as counting rows.

Have you ever called count on a data frame expecting one number and gotten back a whole column of them? That surprise is the entire story of this method, and it is worth sitting with, because nearly every count bug I have seen starts right there.

count reports how many non-missing values there are. It looks at each column, skips anything absent — NaN, None, NaT, pd.NA — and tells you how many real values survive. On a data frame that means one number per column. On a series it means a single integer. What it never tells you is how many rows you have. That is len(df), which is instant, exact, and does not care about missing data.

Once you stop reading count as "how many rows" and start reading it as "how many values did I actually get," it turns into one of the best diagnostics in Pandas. The gap between the row count and the count is the number of holes, and the holes are usually where the interesting questions hide.

Official documentation: DataFrame.count and Series.count

The arguments that earn their keep

There are two, and both of them belong to the data frame version:

df.count()                     # non-null values per column — the default
df.count(axis='columns')       # non-null values per row
df.count(numeric_only=True)    # only the numeric columns

Series.count() takes no arguments whatsoever — one axis, nothing to restrict, nothing to configure. That is a short parameter list for a method this widely used, and it is a good sign: count does one thing.

A worked example, on real data

Bamboo Weekly #15 worked with a data set of every Eurovision Song Contest entrant from 1956 through 2020 — one row per country per year, with the performer, the song, where it placed, and how many points it collected along the way. It is a good data set for this method because the contest kept changing its rules, which means whole columns exist only for some of those years.

import pandas as pd

url = ('https://github.com/Spijkervet/eurovision-dataset/releases/download/'
       '2020.0/contestants.csv')

df = pd.read_csv(url)
len(df)

That is the row count, and len is how you get it. Now ask count the same question and watch it answer a different one:

df.count()

year                 1603
to_country_id        1603
to_country           1603
performer            1603
song                 1600
place_contest        1562
sf_num                557
running_final        1321
running_sf            522
place_final          1320
points_final         1308
place_sf              522
points_sf             522
points_tele_final     104
points_jury_final     104
points_tele_sf        144
points_jury_sf        144
composers            1561
lyricists             930
lyrics               1603
youtube_url          1603
dtype: int64

Twenty-one numbers, one per column, and only five of them equal 1603. That is the whole method in one output. points_tele_final has 104 values, because the televote and jury scores were reported separately for only four contests. sf_num has 557, because semi-finals did not exist until 2004.

Flip that around by subtracting from the row count and you have a missing-data audit in one line, which is how I most often use this method:

(len(df) - df.count()).sort_values(ascending=False).head(8)

points_tele_final    1499
points_jury_final    1499
points_jury_sf       1459
points_tele_sf       1459
running_sf           1081
place_sf             1081
points_sf            1081
sf_num               1046
dtype: int64

Before writing a line of analysis, I know which columns are nearly empty and which ones I can trust. Every one of these gaps turns out to be a rule change rather than a data-quality problem — but I would rather learn that in one line than in a debugging session an hour later.

Turn the axis and you count across each row instead of down each column:

df.count(axis='columns').head()

0    11
1    12
2    12
3    11
4    12
dtype: int64

Eleven or twelve populated fields out of 21 for those 1956 entries. Later rows score higher, which is another way of seeing the same story.

count is also an aggregation, so it works after a groupby. Here is the query from Bamboo Weekly #15, counting entrants per year:

df.groupby('year')['to_country'].count().tail()

year
2016    42
2017    42
2018    43
2019    41
2020    41
Name: to_country, dtype: int64

count or size?

This is where count earns its reputation for confusing people, and it is also where it becomes genuinely powerful. On a groupby, size counts rows and count counts values, so the difference between them is exactly the number of missing values in whatever column you chose:

df.groupby('year').size().tail(6)

year
2015    40
2016    42
2017    42
2018    43
2019    41
2020    41
dtype: int64

df.groupby('year')['points_final'].count().tail(6)

year
2015    27
2016    26
2017    26
2018    26
2019    26
2020     0
Name: points_final, dtype: int64

Forty-one countries entered in 2019 and 26 of them scored points in the final, because the other 15 were knocked out in the semi-finals. And 2020 shows 41 entrants and zero finalists, because that contest was cancelled. Neither fact required a filter, an isna, or a comparison. Subtracting one aggregation from the other was enough.

So: reach for size when you want rows, and for count when the presence of a value is itself what you are measuring.

Four mistakes people make

Expecting a row count. df.count() gives you a series with one entry per column, not a number, and no argument changes that. Worse, the per-column answers are often close enough to the row count to look right. Use len(df) for rows, or df.shape if you want the column count too. Both read the data frame's structure instead of scanning its contents, so they are also far faster.

Treating groupby(...).count() and groupby(...).size() as synonyms. They differ by exactly the missing values, as above, and that difference is silent. This is why so many Bamboo Weekly solutions count a column chosen specifically because it has no gaps — an ID column, usually. If your grouped counts are mysteriously low, the column you counted has holes in it.

Reaching for count when you meant value_counts. They sound like the same idea and they answer completely different questions. df['to_country'].count() returns 1603: how many rows have a country. But df['to_country'].value_counts() returns a series telling you Germany appears 65 times, France 64, Belgium 63. One counts the values; the other counts each distinct value. If you want a breakdown, you want value_counts.

Confusing count with .str.count(). These share a name and nothing else. df['composers'].count() is 1561 — the number of songs with a composer credited. df['composers'].str.count(';') counts semicolons inside each string, one result per row:

(df['composers'].str.count(';') + 1).value_counts().sort_index()

composers
1.0     1082
2.0      273
3.0      129
4.0       52
5.0       18
6.0        6
11.0       1
Name: count, dtype: int64

Composers are separated by semicolons, so adding one turns the semicolon count into a count of people. Most Eurovision songs have a single composer; one has eleven. Note the float64 dtype, too — .str.count() returns NaN for the 42 rows with no composer listed, where plain count would simply have skipped them.

Where it shows up in Bamboo Weekly

Thirty-six Bamboo Weekly exercises use count on real data. Four worth studying, all free to read:

Bamboo Weekly #13: Python developers asks how many people responded to the JetBrains survey, then spends the answer talking readers out of count — and times the alternatives to show what the method costs you as well as what it gets wrong.

Bamboo Weekly #73: Avocado hand groups emergency room visits by month and counts the case-number column, saying why that column in particular: it has few or no missing values. That is the habit worth copying whenever you count after a groupby.

Bamboo Weekly #51: Academy Awards starts with groupby('Film')['FilmId'].count(), discovers it answers the wrong question — films get nominated several times in one year — and switches to nunique. A clean demonstration of counting values when you meant to count distinct ones.

Bamboo Weekly #4: Eating well uses count as a denominator, dividing the count of children eating fewer than one vegetable a day by the count of the whole column, then repeating the trick per state with two groupby calls. When both halves of a fraction skip missing values the same way, the percentage stays honest.

Practice it

Work through a .count() exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/count/

Go deeper

count sits at the front of a small family of missing-data methods. isna marks the gaps, dropna removes the rows that have them, and fillna replaces them; count is how you find out whether any of that is necessary. On the other side, groupby is where count does most of its work. The Pandas user guide on working with missing data explains what Pandas considers absent in the first place, which is the assumption underneath every number count returns.

More Pandas videos on Python and Pandas with Reuven Lerner.

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

.isna() — when you want the missing values themselves rather than a count of what is present
.mean() — which is the number the count belongs next to, since both skip missing values

See it on real data

Below are the 36 Bamboo Weekly exercises that use count on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.

pandas count