Mathew K Analytics

Lesson 57 · Pandas Full Course

Exploratory Data Analysis (EDA) in Pandas: Full Hands-on Project | Pandas Course #57

In this lesson you'll be able to: Audit a new dataset for gaps, caps and odd values Describe one column at a time with numbers and a histogram Compare…

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 57: Hands-on - Exploratory Data Analysis (EDA) Project#

In this lesson you'll be able to:

  • Audit a new dataset for gaps, caps and odd values
  • Describe one column at a time with numbers and a histogram
  • Compare groups and measure relationships between columns
  • Engineer fairer features such as rooms per household
  • Turn the exploration into one clear, checked finding Data: California housing, 20,640 districts from the 1990 census

Warm-up: the first-look routine#

Lesson 56 explored real datasets: shape, head, types, gaps.

import pandas as pd
housing = pd.read_csv("data/pandas/housing.csv")
print(housing.shape)
housing.head(3)
(20640, 10)
longitude latitude housing_median_age total_rooms total_bedrooms population households median_income median_house_value ocean_proximity
0 -122.23 37.88 41.0 880.0 129.0 322.0 126.0 8.3252 452600.0 NEAR BAY
1 -122.22 37.86 21.0 7099.0 1106.0 2401.0 1138.0 8.3014 358500.0 NEAR BAY
2 -122.24 37.85 52.0 1467.0 190.0 496.0 177.0 7.2574 352100.0 NEAR BAY
housing.info()
<class 'pandas.DataFrame'>
RangeIndex: 20640 entries, 0 to 20639
Data columns (total 10 columns):
 #   Column              Non-Null Count  Dtype  
---  ------              --------------  -----  
 0   longitude           20640 non-null  float64
 1   latitude            20640 non-null  float64
 2   housing_median_age  20640 non-null  float64
 3   total_rooms         20640 non-null  float64
 4   total_bedrooms      20433 non-null  float64
 5   population          20640 non-null  float64
 6   households          20640 non-null  float64
 7   median_income       20640 non-null  float64
 8   median_house_value  20640 non-null  float64
 9   ocean_proximity     20640 non-null  str    
dtypes: float64(9), str(1)
memory usage: 1.7 MB

Our three questions

  1. What does a typical district cost? 2. Where is it dearest?
  2. Which numbers move with price, and why?

Step 1: Audit the data before trusting it#

Find gaps, limits and odd values before any analysis.

missing = housing.isna().sum()
print(missing[missing > 0])
share = housing["total_bedrooms"].isna().mean()
print(f"{share:.1%} of districts lack a bedroom count")
total_bedrooms    207
dtype: int64
1.0% of districts lack a bedroom count

Quick check: are the gaps bunched in one kind of location?

gaps = housing["total_bedrooms"].isna()
gaps.groupby(housing["ocean_proximity"]).mean().mul(100).round(1)
ocean_proximity
<1H OCEAN     1.1
INLAND        0.8
ISLAND        0.0
NEAR BAY      0.9
NEAR OCEAN    1.1
Name: total_bedrooms, dtype: float64
print(housing.duplicated().sum())
print(sorted(housing["ocean_proximity"].unique()))
0
['<1H OCEAN', 'INLAND', 'ISLAND', 'NEAR BAY', 'NEAR OCEAN']
cols = ["housing_median_age", "median_income", "median_house_value"]
housing[cols].describe().round(1)
housing_median_age median_income median_house_value
count 20640.0 20640.0 20640.0
mean 28.6 3.9 206855.8
std 12.6 1.9 115395.6
min 1.0 0.5 14999.0
25% 18.0 2.6 119600.0
50% 29.0 3.5 179700.0
75% 37.0 4.7 264725.0
max 52.0 15.0 500001.0

Quick check: why might the maximum price be exactly 500,001?

top = housing["median_house_value"].max()
at_top = (housing["median_house_value"] == top).sum()
print(top, at_top)
housing["housing_median_age"].value_counts().head(3)
500001.0 965
housing_median_age
52.0    1273
36.0     862
35.0     824
Name: count, dtype: int64

Both columns are capped: real values above the limit were cut.

Common mistake: converting a column that still has gaps

housing["total_bedrooms"].astype(int)
---------------------------------------------------------------------------
IntCastingNaNError                        Traceback (most recent call last)
Cell In[9], line 1
----> 1 housing["total_bedrooms"].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:185, in astype_array(values, dtype, copy)
    182     values = values.astype(dtype, copy=copy)
    184 else:
--> 185     values = _astype_nansafe(values, dtype, copy=copy)
    187 # in pandas we don't store numpy str dtypes, so convert to object
    188 if isinstance(dtype, np.dtype) and issubclass(values.dtype.type, str):

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\dtypes\astype.py:102, in _astype_nansafe(arr, dtype, copy, skipna)
     97     return lib.ensure_string_array(
     98         arr, skipna=skipna, convert_na_value=False
     99     ).reshape(shape)
    101 elif np.issubdtype(arr.dtype, np.floating) and dtype.kind in "iu":
--> 102     return _astype_float_to_int_nansafe(arr, dtype, copy)
    104 elif arr.dtype == object:
    105     # if we have a datetime/timedelta array of objects
    106     # then coerce to datetime64[ns] and use DatetimeArray.astype
    108     if lib.is_np_dtype(dtype, "M"):

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\dtypes\astype.py:146, in _astype_float_to_int_nansafe(values, dtype, copy)
    142 """
    143 astype with a check preventing converting NaN to a meaningless integer value.
    144 """
    145 if not np.isfinite(values).all():
--> 146     raise IntCastingNaNError(
    147         "Cannot convert non-finite values (NA or inf) to integer."
    148         "Replace or remove non-finite values or cast to an integer type"
    149         "that supports these values (e.g. 'Int64')"
    150     )
    151 if dtype.kind == "u":
    152     # GH#45151
    153     if not (values >= 0).all():

IntCastingNaNError: Cannot convert non-finite values (NA or inf) to integer.Replace or remove non-finite values or cast to an integer typethat supports these values (e.g. 'Int64')
fill = housing["total_bedrooms"].median()
housing["total_bedrooms"] = housing["total_bedrooms"].fillna(fill)
housing["total_bedrooms"] = housing["total_bedrooms"].astype(int)
print(fill, housing["total_bedrooms"].isna().sum())
435.0 0

Step 2: Describe one column at a time#

What does a typical district look like?

price = housing["median_house_value"]
print(f"Median {price.median():,.0f}, mean {price.mean():,.0f}")
print(price.quantile([0.1, 0.9]).round(0))
print(round(price.skew(), 2))
Median 179,700, mean 206,856
0.1     82300.0
0.9    376600.0
Name: median_house_value, dtype: float64
0.98
import matplotlib.pyplot as plt
ax = price.plot(kind="hist", bins=50, figsize=(8, 4))
ax.set_title("Median house value per district")
ax.set_xlabel("Median house value (USD)")
ax.set_ylabel("Number of districts")
plt.show()
No description has been provided for this image
places = housing["ocean_proximity"].value_counts()
print(places)
print(places.div(len(housing)).round(3))
ocean_proximity
<1H OCEAN     9136
INLAND        6551
NEAR OCEAN    2658
NEAR BAY      2290
ISLAND           5
Name: count, dtype: int64
ocean_proximity
<1H OCEAN     0.443
INLAND        0.317
NEAR OCEAN    0.129
NEAR BAY      0.111
ISLAND        0.000
Name: count, dtype: float64

Your turn: find the 25th, 50th and 75th percentile of median_income. Note: income is in tens of thousands of dollars.

income = housing["median_income"]
income.quantile([0.25, 0.5, 0.75]).round(2)
0.25    2.56
0.50    3.53
0.75    4.74
Name: median_income, dtype: float64
print(income.min(), income.max())
print((income == income.max()).sum())
0.4999 15.0001
49
age = housing["housing_median_age"]
ax = age.plot(kind="hist", bins=52, figsize=(8, 4))
ax.set_title("Median housing age per district")
ax.set_xlabel("Median age (years)")
ax.set_ylabel("Number of districts")
plt.show()
No description has been provided for this image

Step 3: Compare groups#

Where is housing dearest? Group by location and compare.

by_place = housing.groupby("ocean_proximity")["median_house_value"]
summary = by_place.agg(["count", "median", "mean"]).round(0)
summary.sort_values("median", ascending=False)
count median mean
ocean_proximity
ISLAND 5 414700.0 380440.0
NEAR BAY 2290 233800.0 259212.0
NEAR OCEAN 2658 229450.0 249434.0
<1H OCEAN 9136 214850.0 240084.0
INLAND 6551 108500.0 124805.0

Quick check: should we trust the ISLAND median?

island = housing[housing["ocean_proximity"] == "ISLAND"]
island[["median_income", "median_house_value", "population"]]
median_income median_house_value population
8314 2.1579 450000.0 744.0
8315 2.8333 414700.0 1100.0
8316 3.3906 300000.0 733.0
8317 2.7361 450000.0 341.0
8318 2.6042 287500.0 422.0
extra = ["median_income", "housing_median_age"]
housing.groupby("ocean_proximity")[extra].median()
median_income housing_median_age
ocean_proximity
<1H OCEAN 3.87500 30.0
INLAND 2.98770 23.0
ISLAND 2.73610 52.0
NEAR BAY 3.81865 39.0
NEAR OCEAN 3.64705 29.0

Your turn: which location has the highest share of capped prices?

housing["capped"] = housing["median_house_value"] == top
housing.groupby("ocean_proximity")["capped"].mean().round(3)
ocean_proximity
<1H OCEAN     0.058
INLAND        0.004
ISLAND        0.000
NEAR BAY      0.085
NEAR OCEAN    0.080
Name: capped, dtype: float64
import seaborn as sns
order = ["INLAND", "<1H OCEAN", "NEAR OCEAN", "NEAR BAY"]
main = housing[housing["ocean_proximity"].isin(order)]
fig, ax = plt.subplots(figsize=(8, 4))
sns.boxplot(data=main, x="ocean_proximity",
            y="median_house_value", order=order, ax=ax)
ax.set_title("House value by location")
ax.set_xlabel("Location")
ax.set_ylabel("Median house value (USD)")
plt.show()
No description has been provided for this image

Step 4: Find relationships between numbers#

Which columns move together with price?

Common mistake: correlating a table that contains text

housing.corr()
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[22], line 1
----> 1 housing.corr()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:13135, in DataFrame.corr(self, method, min_periods, numeric_only)
  13133 cols = data.columns
  13134 idx = cols.copy()
> 13135 mat = data.to_numpy(dtype=float, na_value=np.nan, copy=False)
  13137 if method == "pearson":
  13138     correl = libalgos.nancorr(mat, minp=min_periods)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:2081, in DataFrame.to_numpy(self, dtype, copy, na_value)
   2079 if dtype is not None:
   2080     dtype = np.dtype(dtype)
-> 2081 result = self._mgr.as_array(dtype=dtype, copy=copy, na_value=na_value)
   2082 if result.dtype is not dtype:
   2083     result = np.asarray(result, dtype=dtype)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\managers.py:1885, in BlockManager.as_array(self, dtype, copy, na_value)
   1883         arr.flags.writeable = False
   1884 else:
-> 1885     arr = self._interleave(dtype=dtype, na_value=na_value)
   1886     # The underlying data was copied within _interleave, so no need
   1887     # to further copy if copy=True or setting na_value
   1889 if na_value is lib.no_default:

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\internals\managers.py:1938, in BlockManager._interleave(self, dtype, na_value)
   1932 rl = blk.mgr_locs
   1933 if blk.is_extension:
   1934     # Avoid implicit conversion of extension blocks to object
   1935 
   1936     # error: Item "ndarray" of "Union[ndarray, ExtensionArray]" has no
   1937     # attribute "to_numpy"
-> 1938     arr = blk.values.to_numpy(  # type: ignore[union-attr]
   1939         dtype=dtype,
   1940         na_value=na_value,
   1941     )
   1942 else:
   1943     arr = blk.get_values(dtype)

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: could not convert string to float: 'NEAR BAY'
corr = housing.corr(numeric_only=True)
corr["median_house_value"].sort_values(ascending=False).round(2)
median_house_value    1.00
median_income         0.69
capped                0.56
total_rooms           0.13
housing_median_age    0.11
households            0.07
total_bedrooms        0.05
population           -0.02
longitude            -0.05
latitude             -0.14
Name: median_house_value, dtype: float64

Quick check: does the price cap weaken the income link?

uncapped = housing[~housing["capped"]]
r = uncapped["median_income"].corr(uncapped["median_house_value"])
print(len(uncapped), round(r, 2))
19675 0.64
ax = housing.plot(kind="scatter", x="median_income",
                   y="median_house_value", alpha=0.1,
                   figsize=(8, 4))
ax.set_title("Income against house value")
ax.set_xlabel("Median income (tens of thousands USD)")
ax.set_ylabel("Median house value (USD)")
plt.show()
No description has been provided for this image
bins = [0, 2, 3, 4, 5, 6, 16]
labels = ["<2", "2-3", "3-4", "4-5", "5-6", "6+"]
housing["income_band"] = pd.cut(income, bins=bins, labels=labels)
band = housing.groupby("income_band", observed=True)
band["median_house_value"].median()
income_band
<2      93300.0
2-3    130800.0
3-4    171250.0
4-5    209450.0
5-6    253300.0
6+     371800.0
Name: median_house_value, dtype: float64
housing["income_band"].value_counts().sort_index()
income_band
<2     2457
2-3    4946
3-4    5150
4-5    3598
5-6    2127
6+     2362
Name: count, dtype: int64

Step 5: Engineer fairer features#

Raw totals grow with district size. Ratios compare like with like.

housing["rooms_per_home"] = (housing["total_rooms"]
                             / housing["households"])
housing["people_per_home"] = (housing["population"]
                              / housing["households"])
new = ["rooms_per_home", "people_per_home"]
housing[new].describe().round(1)
rooms_per_home people_per_home
count 20640.0 20640.0
mean 5.4 3.1
std 2.5 10.4
min 0.8 0.7
25% 4.4 2.4
50% 5.2 2.8
75% 6.1 3.3
max 141.9 1243.3
show = ["rooms_per_home", "households", "population"]
housing.nlargest(3, "rooms_per_home")[show].round(1)
rooms_per_home households population
1914 141.9 11.0 30.0
1979 132.5 15.0 36.0
12447 62.4 45.0 83.0
crowded = housing["people_per_home"] > 10
print(crowded.sum(), f"{crowded.mean():.2%}")
37 0.18%

Quick check: which links more strongly to price: total_rooms or rooms_per_home?

check = ["total_rooms", "rooms_per_home", "median_house_value"]
print(housing[check].corr().round(3))
                    total_rooms  rooms_per_home  median_house_value
total_rooms               1.000           0.134               0.134
rooms_per_home            0.134           1.000               0.152
median_house_value        0.134           0.152               1.000

Common mistake: a typo in a new column name

housing["rooms_per_house"].median()
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3641, in Index.get_loc(self, key)
   3640 try:
-> 3641     return self._engine.get_loc(casted_key)
   3642 except KeyError as err:

File pandas/_libs/index.pyx:168, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/index.pyx:197, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/hashtable_class_helper.pxi:7668, in pandas._libs.hashtable.PyObjectHashTable.get_item()

File pandas/_libs/hashtable_class_helper.pxi:7676, in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'rooms_per_house'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
Cell In[32], line 1
----> 1 housing["rooms_per_house"].median()

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py:4378, in DataFrame.__getitem__(self, key)
   4376 if self.columns.nlevels > 1:
   4377     return self._getitem_multilevel(key)
-> 4378 indexer = self.columns.get_loc(key)
   4379 if is_integer(indexer):
   4380     indexer = [indexer]

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:3648, in Index.get_loc(self, key)
   3643     if isinstance(casted_key, slice) or (
   3644         isinstance(casted_key, abc.Iterable)
   3645         and any(isinstance(x, slice) for x in casted_key)
   3646     ):
   3647         raise InvalidIndexError(key) from err
-> 3648     raise KeyError(key) from err
   3649 except TypeError:
   3650     # If we have a listlike key, _check_indexing_error will raise
   3651     #  InvalidIndexError. Otherwise we fall through and re-raise
   3652     #  the TypeError.
   3653     self._check_indexing_error(key)

KeyError: 'rooms_per_house'
print([c for c in housing.columns if "per" in c])
print(round(housing["rooms_per_home"].median(), 2))
['rooms_per_home', 'people_per_home']
5.23

Your turn: add bedrooms_per_room and find its correlation with price.

housing["bedrooms_per_room"] = (housing["total_bedrooms"]
                                / housing["total_rooms"])
bpr = housing["bedrooms_per_room"]
print(round(bpr.corr(housing["median_house_value"]), 3))
-0.233

Mini-project: is the coast premium just about income?#

Compare inland and coastal prices for districts with the same income.

housing["inland"] = housing["ocean_proximity"] == "INLAND"
housing.groupby("inland")["median_income"].median().round(2)
inland
False    3.82
True     2.99
Name: median_income, dtype: float64
table = housing.pivot_table(index="income_band", columns="inland",
                            values="median_house_value",
                            aggfunc="median", observed=True)
table.columns = ["coastal", "inland"]
table["inland_vs_coast"] = (table["inland"] / table["coastal"])
table.round(2)
coastal inland inland_vs_coast
income_band
<2 124500.0 67500.0 0.54
2-3 162500.0 91100.0 0.56
3-4 197300.0 118500.0 0.60
4-5 227200.0 145850.0 0.64
5-6 267650.0 185100.0 0.69
6+ 388700.0 267400.0 0.69
ax = table[["coastal", "inland"]].plot(kind="bar", rot=0,
                                       figsize=(8, 4))
ax.set_title("Same income, different price")
ax.set_xlabel("Median income band (tens of thousands USD)")
ax.set_ylabel("Median house value (USD)")
plt.show()
No description has been provided for this image
ax = housing.plot(kind="scatter", x="longitude", y="latitude",
                   c="median_house_value", cmap="viridis",
                   s=4, alpha=0.4, figsize=(7, 6))
ax.set_title("California districts by house value")
ax.set_xlabel("Longitude")
ax.set_ylabel("Latitude")
plt.show()
No description has been provided for this image
fair = housing[~housing["capped"]]
check = fair.pivot_table(index="income_band", columns="inland",
                          values="median_house_value",
                          aggfunc="median", observed=True)
(check[True] / check[False]).round(2)
income_band
<2     0.55
2-3    0.56
3-4    0.61
4-5    0.65
5-6    0.70
6+     0.75
dtype: float64
ratio = table["inland_vs_coast"]
low, high = ratio.min(), ratio.max()
print(f"Same income: inland homes cost {low:.0%} to {high:.0%}")
print("of coastal homes, so income is not the whole story.")
Same income: inland homes cost 54% to 69%
of coastal homes, so income is not the whole story.
import os
os.makedirs("output/pandas", exist_ok=True)
path = "output/pandas/l57_coast_premium.csv"
table.to_csv(path)
pd.read_csv(path, index_col="income_band").round(2).head(3)
coastal inland inland_vs_coast
income_band
<2 124500.0 67500.0 0.54
2-3 162500.0 91100.0 0.56
3-4 197300.0 118500.0 0.60

Recap#

  • Start with questions, then audit: gaps, caps and odd values
  • Describe one column at a time: median, percentiles, histogram
  • Compare groups with groupby, and check each group's count
  • corr(numeric_only=True) and scatter plots show relationships
  • Ratios beat raw totals; hold one factor fixed to test a story

Challenges#

  1. Hold housing age fixed instead of income. Does the inland gap remain?
  2. Which location type has the oldest housing, by median age?
  3. Plot a histogram of people_per_home below 6. What's typical?

What's next#

Lesson 58: Cleaning and Analysing Survey Data Real survey answers: free-text job titles, currencies and messy categories.

Found this useful?

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