Mathew K Analytics

Lesson 56 · Pandas Full Course

Explore a Real Dataset with Pandas: Netflix Data Analysis Project | Pandas Course #56

In this lesson you'll be able to: Run a first-look checklist on any new dataset Repair text dates and text durations so you can calculate with them Ask…

What you'll learn

Datasets used in this lesson

Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.

📓 Full notebook

Download .ipynb

Pandas 56: Exploring Real Datasets with Pandas#

In this lesson you'll be able to:

  • Run a first-look checklist on any new dataset
  • Repair text dates and text durations so you can calculate with them
  • Ask questions with groupby and catch odd rows along the way
  • Split multi-value cells with str.split and explode
  • Turn a question into a chart and a one-line finding Dataset: Netflix titles (data/pandas/netflix_titles.csv)

Warm-up: profile groups with groupby#

Lesson 55 profiled clusters with groupby. Same habit, new data.

import pandas as pd
df = pd.read_csv("data/pandas/netflix_titles.csv")
print(df.shape)
df[["title", "type", "country", "release_year"]].head(3)
(7787, 12)
title type country release_year
0 3% TV Show Brazil 2020
1 7:19 Movie Mexico 2016
2 23:59 Movie Singapore 2011
df.groupby("type")["release_year"].agg(["size", "median", "min"])
size median min
type
Movie 5377 2016.0 1942
TV Show 2410 2018.0 1925

Run a first-look checklist#

Problem: know what you're holding before you trust any answer.

df.dtypes.value_counts()
str      11
int64     1
Name: count, dtype: int64

Quick check: only one column is numeric. Which one?

df.select_dtypes("number").columns.tolist()
['release_year']
df.nunique().sort_values().head(6)
type              2
rating           14
release_year     73
duration        216
listed_in       492
country         681
dtype: int64
missing = df.isna().mean().mul(100).round(1)
missing.sort_values(ascending=False).head(5)
director      30.7
cast           9.2
country        6.5
date_added     0.1
rating         0.1
dtype: float64
print(df.duplicated().sum())
print(df["show_id"].is_unique, df["title"].is_unique)
0
True True
df.sample(3, random_state=42)[["title", "type", "duration"]]
title type duration
7324 Unchained: The Untold Story of Freestyle Motoc... Movie 92 min
4694 Our Idiot Brother Movie 90 min
1322 Chhota Bheem Kungfu Dhamaka Movie 112 min

Your turn#

Rating has 14 values. Which five are the rarest?

df["rating"].value_counts().tail(5)
rating
NR          84
G           39
TV-Y7-FV     6
UR           5
NC-17        3
Name: count, dtype: int64

Repair the date column#

Problem: date_added is text, so we can't ask 'when?' questions.

Common mistake: trusting text to be perfectly clean

pd.to_datetime(df["date_added"], format="%B %d, %Y")
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[11], line 1
----> 1 pd.to_datetime(df["date_added"], format="%B %d, %Y")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\tools\datetimes.py:1036, in to_datetime(arg, errors, dayfirst, yearfirst, utc, format, exact, unit, origin, cache)
   1034             result = arg.tz_localize("utc")
   1035 elif isinstance(arg, ABCSeries):
-> 1036     cache_array = _maybe_cache(arg, format, cache, convert_listlike)
   1037     if not cache_array.empty:
   1038         result = arg.map(cache_array)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\tools\datetimes.py:254, in _maybe_cache(arg, format, cache, convert_listlike)
    252 unique_dates = unique(arg)
    253 if len(unique_dates) < len(arg):
--> 254     cache_dates = convert_listlike(unique_dates, format)
    255     # GH#45319
    256     try:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\tools\datetimes.py:435, in _convert_listlike_datetimes(arg, format, name, utc, unit, errors, dayfirst, yearfirst, exact)
    433 # `format` could be inferred, or user didn't ask for mixed-format parsing.
    434 if format is not None and format != "mixed":
--> 435     return _array_strptime_with_fallback(arg, name, utc, format, exact, errors)
    437 result, tz_parsed = objects_to_datetime64(
    438     arg,
    439     dayfirst=dayfirst,
   (...)
    443     allow_object=True,
    444 )
    446 if tz_parsed is not None:
    447     # We can take a shortcut since the datetime64 numpy array
    448     # is in UTC

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\tools\datetimes.py:470, in _array_strptime_with_fallback(arg, name, utc, fmt, exact, errors)
    459 def _array_strptime_with_fallback(
    460     arg,
    461     name,
   (...)
    465     errors: str,
    466 ) -> Index:
    467     """
    468     Call array_strptime, with fallback behavior depending on 'errors'.
    469     """
--> 470     result, tz_out = array_strptime(arg, fmt, exact=exact, errors=errors, utc=utc)
    471     if tz_out is not None:
    472         unit = np.datetime_data(result.dtype)[0]

File pandas/_libs/tslibs/strptime.pyx:564, in pandas._libs.tslibs.strptime.array_strptime()

File pandas/_libs/tslibs/strptime.pyx:512, in pandas._libs.tslibs.strptime.array_strptime()

File pandas/_libs/tslibs/strptime.pyx:618, in pandas._libs.tslibs.strptime._parse_with_format()

ValueError: time data " August 4, 2017" doesn't match format "%B %d, %Y". You might want to try:
    - passing `format` if your strings have a consistent format;
    - passing `format='ISO8601'` if your strings are all ISO8601 but not necessarily in exactly the same format;
    - passing `format='mixed'`, and the format will be inferred for each element individually. You might want to use `dayfirst` alongside this.
clean = df["date_added"].str.strip()
df["added"] = pd.to_datetime(clean, format="%B %d, %Y")
print(df["added"].isna().sum())
print(df["added"].min(), df["added"].max())
10
2008-01-01 00:00:00 2021-01-16 00:00:00
no_date = df[df["added"].isna()]
print(no_date["type"].value_counts())
no_date[["title", "type"]].head(3)
type
TV Show    10
Name: count, dtype: int64
title type
258 A Young Doctor's Notebook and Other Stories TV Show
549 Anthony Bourdain: Parts Unknown TV Show
2263 Frasier TV Show
df["year_added"] = df["added"].dt.year.astype("Int64")
df["year_added"].value_counts().sort_index().tail(7)
year_added
2015      88
2016     443
2017    1225
2018    1685
2019    2153
2020    2009
2021     117
Name: count, dtype: Int64

Quick check: 2021 has only 117 titles. Did Netflix stop adding?

latest = df["added"].max()
print(latest.date(), (df["added"] >= "2021-01-01").sum())
2021-01-16 117

Turn durations into numbers#

Problem: duration mixes '90 min' with '2 Seasons'.

Common mistake: converting mixed text straight to numbers

df["duration"].astype(int)
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[16], line 1
----> 1 df["duration"].astype(int)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py:6529, in NDFrame.astype(self, dtype, copy, errors)
   6525     results = [ser.astype(dtype, errors=errors) for _, ser in self.items()]
   6527 else:
   6528     # else, only a single dtype is given
-> 6529     new_data = self._mgr.astype(dtype=dtype, errors=errors)
   6530     res = self._constructor_from_mgr(new_data, axes=new_data.axes)
   6531     return res.__finalize__(self, method="astype")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\managers.py:627, in BaseBlockManager.astype(self, dtype, errors)
    626 def astype(self, dtype, errors: str = "raise") -> Self:
--> 627     return self.apply("astype", dtype=dtype, errors=errors)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\managers.py:445, in BaseBlockManager.apply(self, f, align_keys, **kwargs)
    443         applied = b.apply(f, **kwargs)
    444     else:
--> 445         applied = getattr(b, f)(**kwargs)
    446     result_blocks = extend_blocks(applied, result_blocks)
    448 out = type(self).from_blocks(result_blocks, [ax.view() for ax in self.axes])

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\blocks.py:607, in Block.astype(self, dtype, errors, squeeze)
    604         raise ValueError("Can not squeeze with more than one column.")
    605     values = values[0, :]  # type: ignore[call-overload]
--> 607 new_values = astype_array_safe(values, dtype, errors=errors)
    609 new_values = maybe_coerce_values(new_values)
    611 refs = None

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\dtypes\astype.py:240, in astype_array_safe(values, dtype, copy, errors)
    237     dtype = dtype.numpy_dtype
    239 try:
--> 240     new_values = astype_array(values, dtype, copy=copy)
    241 except (ValueError, TypeError):
    242     # e.g. _astype_nansafe can fail on object-dtype of strings
    243     #  trying to convert to float
    244     if errors == "ignore":

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\dtypes\astype.py:182, in astype_array(values, dtype, copy)
    178     return values
    180 if not isinstance(values, np.ndarray):
    181     # i.e. ExtensionArray
--> 182     values = values.astype(dtype, copy=copy)
    184 else:
    185     values = _astype_nansafe(values, dtype, copy=copy)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\string_arrow.py:338, in ArrowStringArray.astype(self, dtype, copy)
    335 elif isinstance(dtype, np.dtype) and np.issubdtype(dtype, np.floating):
    336     return self.to_numpy(dtype=dtype, na_value=np.nan)
--> 338 return super().astype(dtype, copy=copy)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\base.py:881, in ExtensionArray.astype(self, dtype, copy)
    878     return TimedeltaArray._from_sequence(self, dtype=dtype, copy=copy)
    880 if not copy:
--> 881     return np.asarray(self, dtype=dtype)
    882 else:
    883     return np.array(self, dtype=dtype, copy=copy)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\arrow\array.py:920, in ArrowExtensionArray.__array__(self, dtype, copy)
    916 elif copy is None:
    917     # `to_numpy(copy=False)` has the meaning of NumPy `copy=None`.
    918     copy = False
--> 920 return self.to_numpy(dtype=dtype, copy=copy)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\arrow\array.py:1803, in ArrowExtensionArray.to_numpy(self, dtype, copy, na_value)
   1801 result = data._pa_array.to_numpy()
   1802 if dtype is not None:
-> 1803     result = result.astype(dtype, copy=False)
   1804 if copy:
   1805     result = result.copy()

ValueError: invalid literal for int() with base 10: '4 Seasons'
parts = df["duration"].str.extract(r"(\d+) (\w+)")
df["length"] = parts[0].astype(int)
df["unit"] = parts[1].str.replace("Seasons", "Season")
pd.crosstab(df["type"], df["unit"])
unit Season min
type
Movie 0 5377
TV Show 2410 0

Your turn#

Describe film lengths in minutes. What's typical?

movies = df[df["type"] == "Movie"]
movies["length"].describe().round(1)
count    5377.0
mean       99.3
std        28.5
min         3.0
25%        86.0
50%        98.0
75%       114.0
max       312.0
Name: length, dtype: float64
cols = ["title", "length", "release_year"]
print(movies.nlargest(3, "length")[cols])
print(movies.nsmallest(3, "length")[cols])
                           title  length  release_year
957   Black Mirror: Bandersnatch     312          2018
6850      The School of Mischief     253          1973
4490              No Longer kids     237          1979
                 title  length  release_year
5605            Silent       3          2014
5694       Sol Levante       5          2020
1501  Cops and Robbers       8          2020
import matplotlib.pyplot as plt
ax = movies["length"].plot.hist(bins=40, figsize=(7, 3.5))
ax.set_title("How long are Netflix films?")
ax.set_xlabel("Length (minutes)")
ax.set_ylabel("Number of films")
plt.show()
No description has been provided for this image
shows = df[df["type"] == "TV Show"]
shows["length"].value_counts().head(4)
length
1    1608
2     382
3     184
4      87
Name: count, dtype: int64

Ask questions with groupby#

Problem: turn curiosity into a calculation, and spot oddities.

df["added"].dt.month_name().value_counts().head(3)
added
December    833
October     785
January     757
Name: count, dtype: int64
df["added"].dt.day_name().value_counts().head(3)
added
Friday      2287
Thursday    1147
Tuesday     1070
Name: count, dtype: int64
df["lag"] = df["year_added"] - df["release_year"]
df.groupby("type")["lag"].median()
type
Movie      1.0
TV Show    0.0
Name: lag, dtype: Float64
film_lag = df.loc[df["type"] == "Movie", "lag"]
print(film_lag.describe().round(1))
print(round((film_lag >= 10).mean(), 3))
count    5377.0
mean        5.6
std         9.7
min        -1.0
25%         0.0
50%         1.0
75%         7.0
max        75.0
Name: lag, dtype: Float64
0.184
df.nsmallest(3, "release_year")[["title", "type", "release_year"]]
title type release_year
4867 Pioneers: First Women Filmmakers* TV Show 1925
4960 Prelude to War Movie 1942
6117 The Battle of Midway Movie 1942

Quick check: can a title arrive on Netflix before its release year?

odd = df[df["lag"] < 0]
print(len(odd))
odd[["title", "type", "release_year", "year_added"]].head()
12
title type release_year year_added
583 Arrested Development TV Show 2019 2018
1025 BoJack Horseman TV Show 2020 2019
2311 Fuller House TV Show 2020 2019
2602 Hans Teeuwen: Real Rancour Movie 2018 2017
2741 Hilda TV Show 2021 2020

Common mistake: averaging a text column

df.groupby("type")["duration"].mean()
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[28], line 1
----> 1 df.groupby("type")["duration"].mean()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\groupby\groupby.py:2285, in GroupBy.mean(self, numeric_only, skipna, engine, engine_kwargs)
   2277     return self._numba_agg_general(
   2278         grouped_mean,
   2279         executor.float_dtype_mapping,
   (...)
   2282         skipna=skipna,
   2283     )
   2284 else:
-> 2285     result = self._cython_agg_general(
   2286         "mean",
   2287         alt=lambda x: Series(x, copy=False).mean(
   2288             numeric_only=numeric_only, skipna=skipna
   2289         ),
   2290         numeric_only=numeric_only,
   2291         skipna=skipna,
   2292     )
   2293     return result.__finalize__(self.obj, method="groupby")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\groupby\groupby.py:1808, in GroupBy._cython_agg_general(self, how, alt, numeric_only, min_count, **kwargs)
   1805     result = self._agg_py_fallback(how, values, ndim=data.ndim, alt=alt)
   1806     return result
-> 1808 new_mgr = data.grouped_reduce(array_func)
   1809 res = self._wrap_agged_manager(new_mgr)
   1810 if how in ["idxmin", "idxmax"]:
   1811     # mypy expects how to be Literal["idxmin", "idxmax"].

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\managers.py:2295, in SingleBlockManager.grouped_reduce(self, func)
   2293 def grouped_reduce(self, func):
   2294     arr = self.array
-> 2295     res = func(arr)
   2296     index = default_index(len(res))
   2298     mgr = type(self).from_array(res, index)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\groupby\groupby.py:1783, in GroupBy._cython_agg_general.<locals>.array_func(values)
   1781 def array_func(values: ArrayLike) -> ArrayLike:
   1782     try:
-> 1783         result = self._grouper._cython_operation(
   1784             "aggregate",
   1785             values,
   1786             how,
   1787             axis=data.ndim - 1,
   1788             min_count=min_count,
   1789             **kwargs,
   1790         )
   1791     except NotImplementedError:
   1792         # generally if we have numeric_only=False
   1793         # and non-applicable functions
   1794         # try to python agg
   1795         # TODO: shouldn't min_count matter?
   1796         # TODO: avoid special casing SparseArray here
   1797         if how in ["any", "all"] and isinstance(values, SparseArray):

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\groupby\ops.py:963, in BaseGrouper._cython_operation(self, kind, values, how, axis, min_count, **kwargs)
    959 assert kind in ["transform", "aggregate"]
    961 cy_op = WrappedCythonOp(kind=kind, how=how, has_dropped_na=self.has_dropped_na)
--> 963 return cy_op.cython_operation(
    964     values=values,
    965     axis=axis,
    966     min_count=min_count,
    967     comp_ids=self.ids,
    968     ngroups=self.ngroups,
    969     **kwargs,
    970 )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\groupby\ops.py:553, in WrappedCythonOp.cython_operation(self, values, axis, min_count, comp_ids, ngroups, **kwargs)
    549 self._validate_axis(axis, values)
    551 if not isinstance(values, np.ndarray):
    552     # i.e. ExtensionArray
--> 553     return values._groupby_op(
    554         how=self.how,
    555         has_dropped_na=self.has_dropped_na,
    556         min_count=min_count,
    557         ngroups=ngroups,
    558         ids=comp_ids,
    559         **kwargs,
    560     )
    562 return self._cython_op_ndim_compat(
    563     values,
    564     min_count=min_count,
   (...)
    568     **kwargs,
    569 )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arrays\arrow\array.py:2748, in ArrowExtensionArray._groupby_op(self, how, has_dropped_na, min_count, ngroups, ids, **kwargs)
   2736 if isinstance(self.dtype, StringDtype):
   2737     if how in [
   2738         "prod",
   2739         "mean",
   (...)
   2746         "skew",
   2747     ]:
-> 2748         raise TypeError(
   2749             f"dtype '{self.dtype}' does not support operation '{how}'"
   2750         )
   2751     return super()._groupby_op(
   2752         how=how,
   2753         has_dropped_na=has_dropped_na,
   (...)
   2757         **kwargs,
   2758     )
   2760 # maybe convert to a compatible dtype optimized for groupby

TypeError: dtype 'str' does not support operation 'mean'
df.groupby(["type", "unit"])["length"].mean().round(1)
type     unit  
Movie    min       99.3
TV Show  Season     1.8
Name: length, dtype: float64

Your turn#

Which three calendar months had the most additions? Hint: to_period.

months = df["added"].dt.to_period("M")
months.value_counts().head(3)
added
2019-11    272
2020-01    228
2019-12    222
Freq: M, Name: count, dtype: int64

Split cells that hold several values#

Problem: 'United States, India' is one cell but two countries.

print(df["country"].str.contains(",").sum())
print(df["country"].nunique())
1153
681

Quick check: what does explode do to the raw text column?

print(len(df), len(df["country"].explode()))
7787 7787
countries = df["country"].str.split(", ").explode().str.strip()
print(len(countries))
countries.value_counts().head(8)
9570
country
United States     3296
India              990
United Kingdom     722
Canada             412
France             349
Japan              287
Spain              215
South Korea        212
Name: count, dtype: int64
n_countries = (df["country"].str.count(",") + 1).astype("Int64")
print(n_countries.value_counts().head(4))
print(n_countries.max(), df.loc[n_countries.idxmax(), "title"])
country
1    6127
2     763
3     240
4      95
Name: count, dtype: Int64
12 Barbecue
genres = df["listed_in"].str.split(", ").explode()
print(genres.nunique())
genres.value_counts().head(5)
42
listed_in
International Movies      2437
Dramas                    2106
Comedies                  1471
International TV Shows    1199
Documentaries              786
Name: count, dtype: int64

Your turn#

Find the five most common genres among TV shows only.

tv_genres = shows["listed_in"].str.split(", ").explode()
tv_genres.value_counts().head(5)
listed_in
International TV Shows    1199
TV Dramas                  704
TV Comedies                525
Crime TV Shows             427
Kids' TV                   414
Name: count, dtype: int64

Reshape and chart a trend#

Problem: see how the mix of films and shows changed over time.

full = df[df["year_added"].between(2015, 2020)]
mix = pd.crosstab(full["year_added"], full["type"])
mix
type Movie TV Show
year_added
2015 58 30
2016 258 185
2017 864 361
2018 1255 430
2019 1497 656
2020 1312 697
share = pd.crosstab(full["year_added"], full["type"],
                    normalize="index").round(3)
share
type Movie TV Show
year_added
2015 0.659 0.341
2016 0.582 0.418
2017 0.705 0.295
2018 0.745 0.255
2019 0.695 0.305
2020 0.653 0.347
ax = share["TV Show"].plot(marker="o", figsize=(7, 4))
ax.set_title("Share of Netflix additions that were TV shows")
ax.set_xlabel("Year added")
ax.set_ylabel("Share of titles added")
plt.show()
No description has been provided for this image

Your turn#

Which rating was most common among titles added in 2020?

added_2020 = df[df["year_added"] == 2020]
added_2020["rating"].value_counts().head(3)
rating
TV-MA    712
TV-14    464
R        199
Name: count, dtype: int64

Mini-project: is Netflix mostly American?#

Share of titles added each year with no United States production.

known = full[full["country"].notna()]
us = known["country"].str.contains("United States")
known = known.assign(intl=~us)
print(len(full), len(known), round(known["intl"].mean(), 3))
7603 7112 0.555
by_year = known.groupby("year_added")["intl"].mean().round(3)
by_year
year_added
2015    0.277
2016    0.519
2017    0.594
2018    0.606
2019    0.535
2020    0.530
Name: intl, dtype: float64
by_type = known.groupby(["year_added", "type"])["intl"].mean()
by_type.unstack().round(3)
type Movie TV Show
year_added
2015 0.241 0.345
2016 0.442 0.629
2017 0.564 0.672
2018 0.616 0.571
2019 0.496 0.634
2020 0.515 0.560
ax = by_type.unstack().plot(marker="o", figsize=(7, 4))
ax.axhline(0.5, color="grey", linestyle="--")
ax.set_title("Share of Netflix additions with no US production")
ax.set_xlabel("Year added")
ax.set_ylabel("Non-US share")
plt.show()
No description has been provided for this image
k20 = known[known["year_added"] == 2020]
c20 = k20["country"].str.split(", ").explode().str.strip()
c20[c20 != "United States"].value_counts().head(5)
country
India             203
United Kingdom    167
Canada            115
France             99
Japan              83
Name: count, dtype: int64
peak = by_year.idxmax()
print(f"Non-US share: {by_year.min():.0%} to {by_year.max():.0%},"
      f" peaking in {peak}")
Non-US share: 28% to 61%, peaking in 2018

Recap#

  • First look: dtypes, nunique, missing shares, duplicates, sample
  • Strip text before parsing dates with an exact format
  • Use str.extract to pull numbers and units out of text
  • Split and explode cells that hold several values
  • Compare shares, not raw counts, when group sizes differ

Challenges#

  1. Which director has the most titles? Watch out for shared credits.
  2. Has the median film length changed by release decade?
  3. Repeat the mini-project for India: what share of additions involve it?

What's next#

Lesson 57: Hands-on - Exploratory Data Analysis (EDA) Project A full EDA, from raw file to written conclusions.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.