02 · Pandas for data handling¶
CSE273 Unit II. Series and DataFrames, reading and writing CSV, inspecting a dataset, selecting with loc / iloc, boolean filtering, and visualising with Matplotlib: line, bar, histogram, scatter and pie charts, customising plots and subplots.
try:
import groundwork
except ImportError: # running on Kaggle
%pip install -q git+https://github.com/LuShadowX/Groundwork
import groundwork
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from groundwork import data, plotting
plotting.setup()
COLORS = [plotting.CLASS_COLORS[c] for c in data.CLASSES]
moisture = pd.Series([32.6, 56.6, 12.2, 50.3], index=["field_a", "field_b", "field_c", "field_d"], name="Soil_Moisture")
print(moisture)
print("\nfield_c:", moisture["field_c"], "| mean:", moisture.mean(), "| dtype:", moisture.dtype)
print(moisture[moisture < 40])
print((moisture / 100).round(3).to_dict())
field_a 32.6
field_b 56.6
field_c 12.2
field_d 50.3
Name: Soil_Moisture, dtype: float64
field_c: 12.2 | mean: 37.925 | dtype: float64
field_a 32.6
field_c 12.2
Name: Soil_Moisture, dtype: float64
{'field_a': 0.326, 'field_b': 0.566, 'field_c': 0.122, 'field_d': 0.503}
1.2 DataFrame¶
A DataFrame is a table: columns are Series that share one index, and each column can have its own type.
fields = pd.DataFrame({
"Crop_Type": ["Wheat", "Rice", "Maize", "Cotton"],
"Soil_Moisture": moisture.to_numpy(),
"Temperature_C": [15.0, 22.9, 34.1, 29.4],
"Mulching_Used": [False, True, False, True],
}, index=moisture.index)
fields["Dry_and_hot"] = (fields["Soil_Moisture"] < 30) & (fields["Temperature_C"] > 30)
print(fields)
print("\n", fields.dtypes, sep="")
Crop_Type Soil_Moisture Temperature_C Mulching_Used Dry_and_hot field_a Wheat 32.6 15.0 False False field_b Rice 56.6 22.9 True False field_c Maize 12.2 34.1 False True field_d Cotton 50.3 29.4 True False Crop_Type str Soil_Moisture float64 Temperature_C float64 Mulching_Used bool Dry_and_hot bool dtype: object
import time
t = time.perf_counter()
train = pd.read_csv(data.data_dir() / "train.csv")
print(f"read {len(train):,} rows × {train.shape[1]} columns in {time.perf_counter() - t:.2f} s")
few = pd.read_csv(data.data_dir() / "train.csv", usecols=["id", "Crop_Type", "Irrigation_Need"], nrows=5, index_col="id")
few
read 630,000 rows × 21 columns in 0.32 s
| Crop_Type | Irrigation_Need | |
|---|---|---|
| id | ||
| 0 | Sugarcane | Low |
| 1 | Wheat | Low |
| 2 | Rice | Low |
| 3 | Wheat | Medium |
| 4 | Wheat | Low |
2.2 Writing CSV¶
A small summary is written out and read back, to show that the round trip keeps the data (and that index=False matters).
from pathlib import Path
summary = train.groupby("Crop_Type")[["Soil_Moisture", "Temperature_C", "Rainfall_mm"]].mean().round(2)
out = Path("crop_summary.csv")
summary.to_csv(out)
back = pd.read_csv(out, index_col="Crop_Type")
print(out.read_text()[:200])
print("identical after the round trip:", back.equals(summary))
out.unlink()
Crop_Type,Soil_Moisture,Temperature_C,Rainfall_mm Cotton,37.2,27.05,1464.87 Maize,37.17,27.02,1465.16 Potato,37.08,27.11,1458.01 Rice,37.29,27.02,1470.71 Sugarcane,37.59,26.76,1449.85 Wheat,37.47,27.0 identical after the round trip: True
3. Inspecting datasets¶
print(train.shape)
train.info(memory_usage="deep")
(630000, 21)
<class 'pandas.DataFrame'> RangeIndex: 630000 entries, 0 to 629999 Data columns (total 21 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 id 630000 non-null int64 1 Soil_Type 630000 non-null str 2 Soil_pH 630000 non-null float64 3 Soil_Moisture 630000 non-null float64 4 Organic_Carbon 630000 non-null float64 5 Electrical_Conductivity 630000 non-null float64 6 Temperature_C 630000 non-null float64 7 Humidity 630000 non-null float64 8 Rainfall_mm 630000 non-null float64 9 Sunlight_Hours 630000 non-null float64 10 Wind_Speed_kmh 630000 non-null float64 11 Crop_Type 630000 non-null str 12 Crop_Growth_Stage 630000 non-null str 13 Season 630000 non-null str 14 Irrigation_Type 630000 non-null str 15 Water_Source 630000 non-null str 16 Field_Area_hectare 630000 non-null float64 17 Mulching_Used 630000 non-null str 18 Previous_Irrigation_mm 630000 non-null float64 19 Region 630000 non-null str 20 Irrigation_Need 630000 non-null str dtypes: float64(11), int64(1), str(9) memory usage: 352.4 MB
train.describe().T.round(2)
| count | mean | std | min | 25% | 50% | 75% | max | |
|---|---|---|---|---|---|---|---|---|
| id | 630000.0 | 314999.50 | 181865.48 | 0.00 | 157499.75 | 314999.50 | 472499.25 | 629999.00 |
| Soil_pH | 630000.0 | 6.48 | 0.92 | 4.80 | 5.69 | 6.44 | 7.27 | 8.20 |
| Soil_Moisture | 630000.0 | 37.30 | 16.38 | 8.00 | 23.34 | 37.75 | 51.27 | 64.99 |
| Organic_Carbon | 630000.0 | 0.92 | 0.37 | 0.30 | 0.61 | 0.91 | 1.22 | 1.60 |
| Electrical_Conductivity | 630000.0 | 1.74 | 0.95 | 0.10 | 0.93 | 1.74 | 2.58 | 3.50 |
| Temperature_C | 630000.0 | 27.00 | 8.62 | 12.00 | 19.52 | 26.96 | 34.54 | 42.00 |
| Humidity | 630000.0 | 61.56 | 19.71 | 25.00 | 45.39 | 61.65 | 79.12 | 94.99 |
| Rainfall_mm | 630000.0 | 1462.21 | 612.99 | 0.38 | 954.57 | 1467.16 | 2054.28 | 2499.69 |
| Sunlight_Hours | 630000.0 | 7.51 | 2.00 | 4.00 | 5.76 | 7.58 | 9.25 | 11.00 |
| Wind_Speed_kmh | 630000.0 | 10.38 | 5.69 | 0.50 | 5.28 | 10.48 | 15.43 | 20.00 |
| Field_Area_hectare | 630000.0 | 7.52 | 4.22 | 0.30 | 3.88 | 7.38 | 11.14 | 15.00 |
| Previous_Irrigation_mm | 630000.0 | 62.32 | 34.25 | 0.02 | 33.14 | 61.15 | 92.69 | 119.99 |
print("missing values:", int(train.isna().sum().sum()))
print("duplicated rows:", int(train.duplicated().sum()))
print(train.nunique().sort_values().to_string())
missing values: 0
duplicated rows: 0
Mulching_Used 2 Irrigation_Need 3 Season 3 Soil_Type 4 Water_Source 4 Irrigation_Type 4 Crop_Growth_Stage 4 Region 5 Crop_Type 6 Organic_Carbon 131 Soil_pH 341 Electrical_Conductivity 341 Sunlight_Hours 701 Field_Area_hectare 1466 Wind_Speed_kmh 1935 Temperature_C 2934 Soil_Moisture 5223 Humidity 6475 Previous_Irrigation_mm 10110 Rainfall_mm 19308 id 630000
train["Irrigation_Need"].value_counts().to_frame().assign(share=lambda d: (d["count"] / d["count"].sum()).round(3))
| count | share | |
|---|---|---|
| Irrigation_Need | ||
| Low | 369917 | 0.587 |
| Medium | 239074 | 0.379 |
| High | 21009 | 0.033 |
Inspection already shows the two facts that shape everything later: there are no missing values, and the classes are imbalanced (High is only 3.3%).
4. Selecting and filtering data¶
4.1 loc vs iloc¶
loc selects by label (index values and column names); iloc selects by position. With the default RangeIndex they look the same, until the index changes.
print(train.loc[0:2, ["Crop_Type", "Soil_Moisture"]]) # labels 0, 1 AND 2 (inclusive)
print(train.iloc[0:2, [11, 3]]) # positions 0 and 1 (end excluded)
by_id = train.set_index("id").sort_values("Soil_Moisture")
print("\nafter sorting, loc and iloc pick different rows:")
print(by_id.loc[[0]][["Soil_Moisture"]])
print(by_id.iloc[[0]][["Soil_Moisture"]])
Crop_Type Soil_Moisture
0 Sugarcane 32.58
1 Wheat 56.61
2 Rice 27.71
Crop_Type Soil_Moisture
0 Sugarcane 32.58
1 Wheat 56.61
after sorting, loc and iloc pick different rows:
Soil_Moisture
id
0 32.58
Soil_Moisture
id
472529 8.0
4.2 Filtering (boolean indexing)¶
dry_hot = train[(train["Soil_Moisture"] < 20) & (train["Temperature_C"] > 35)]
print(f"{len(dry_hot):,} dry and hot fields")
print(dry_hot["Irrigation_Need"].value_counts(normalize=True).round(3))
print("\nrice or sugarcane in the flowering stage:")
q = train[train["Crop_Type"].isin(["Rice", "Sugarcane"]) & (train["Crop_Growth_Stage"] == "Flowering")]
print(q["Irrigation_Need"].value_counts(normalize=True).round(3))
print("\nthe same with query():", len(train.query("Soil_Moisture < 20 and Temperature_C > 35")))
28,892 dry and hot fields Irrigation_Need Medium 0.715 High 0.228 Low 0.056 Name: proportion, dtype: float64 rice or sugarcane in the flowering stage: Irrigation_Need Medium 0.631 Low 0.305 High 0.063 Name: proportion, dtype: float64 the same with query(): 28892
Filtering alone finds a strong rule: among dry, hot fields, High is many times more common than its 3.3% overall share.
Group-by is the other everyday tool:
(train.groupby(["Crop_Growth_Stage", "Mulching_Used"])["Irrigation_Need"]
.value_counts(normalize=True).unstack().round(3))
| Irrigation_Need | High | Low | Medium | |
|---|---|---|---|---|
| Crop_Growth_Stage | Mulching_Used | |||
| Flowering | No | 0.106 | 0.157 | 0.737 |
| Yes | 0.014 | 0.487 | 0.499 | |
| Harvest | No | 0.006 | 0.762 | 0.232 |
| Yes | 0.001 | 0.932 | 0.067 | |
| Sowing | No | 0.003 | 0.776 | 0.220 |
| Yes | 0.000 | 0.951 | 0.048 | |
| Vegetative | No | 0.108 | 0.149 | 0.743 |
| Yes | 0.019 | 0.505 | 0.476 |
train["moisture_bin"] = pd.cut(train["Soil_Moisture"], bins=np.arange(0, 70, 2.5))
share = train.groupby("moisture_bin", observed=True)["Irrigation_Need"].value_counts(normalize=True).unstack()
mid = [b.mid for b in share.index]
fig, ax = plt.subplots()
for cls in data.CLASSES:
ax.plot(mid, share[cls], marker="o", ms=4, label=cls, color=plotting.CLASS_COLORS[cls])
ax.set(xlabel="soil moisture (%)", ylabel="share of fields", title="Irrigation need by soil moisture")
ax.legend(title="need")
plotting.save("g02-line")
plt.show()
5.2 Bar plot¶
counts = train.groupby("Crop_Type")["Irrigation_Need"].value_counts().unstack()[data.CLASSES]
ax = counts.plot.bar(stacked=True, color=COLORS, rot=0)
ax.set(ylabel="fields", title="Fields per crop, by irrigation need")
plotting.save("g02-bar")
plt.show()
5.3 Histogram¶
fig, ax = plt.subplots()
for cls in data.CLASSES:
ax.hist(train.loc[train["Irrigation_Need"] == cls, "Temperature_C"], bins=40, alpha=0.6,
density=True, label=cls, color=plotting.CLASS_COLORS[cls])
ax.set(xlabel="temperature (°C)", ylabel="density", title="Temperature distribution per class")
ax.legend()
plotting.save("g02-hist")
plt.show()
5.4 Scatter plot¶
s = data.sample(6000, seed=2)
fig, ax = plt.subplots()
for cls in data.CLASSES:
part = s[s["Irrigation_Need"] == cls]
ax.scatter(part["Soil_Moisture"], part["Temperature_C"], s=8, alpha=0.5, label=cls, color=plotting.CLASS_COLORS[cls])
ax.set(xlabel="soil moisture (%)", ylabel="temperature (°C)", title="High need sits in the dry, hot corner")
ax.legend(markerscale=3)
plotting.save("g02-scatter")
plt.show()
5.5 Pie chart¶
Pie charts are only good for a few parts of one whole, which is exactly the class balance:
vc = train["Irrigation_Need"].value_counts()[data.CLASSES]
fig, ax = plt.subplots(figsize=(4.5, 4.5))
ax.pie(vc, labels=vc.index, colors=COLORS, autopct="%1.1f%%", startangle=90, wedgeprops={"edgecolor": "white"})
ax.set_title("Class balance")
plotting.save("g02-pie")
plt.show()
6. Customising plots¶
Titles, labels, limits, ticks, annotations, reference lines, styles and colours. The same scatter, made to make one point:
fig, ax = plt.subplots(figsize=(8, 4.8))
high = s[s["Irrigation_Need"] == "High"]
rest = s[s["Irrigation_Need"] != "High"]
ax.scatter(rest["Soil_Moisture"], rest["Temperature_C"], s=6, color="#cbd5e1", label="Low or Medium")
ax.scatter(high["Soil_Moisture"], high["Temperature_C"], s=14, color=plotting.CLASS_COLORS["High"], edgecolor="white", lw=0.4, label="High")
ax.axvline(25, ls="--", color=plotting.ACCENT, lw=1)
ax.axhline(30, ls="--", color=plotting.ACCENT, lw=1)
ax.fill_betweenx([30, 45], 0, 25, color=plotting.CLASS_COLORS["High"], alpha=0.07)
ax.annotate(f"{len(high[(high.Soil_Moisture < 25) & (high.Temperature_C > 30)]) / len(high):.0%} of High fields\nare in this box",
xy=(12, 38), xytext=(38, 41), fontsize=10, arrowprops={"arrowstyle": "->", "color": plotting.ACCENT})
ax.set(xlim=(0, 65), ylim=(10, 45), xlabel="soil moisture (%)", ylabel="temperature (°C)")
ax.set_title("Dry and hot fields need the most water", loc="left")
ax.legend(loc="lower right", frameon=True)
plotting.save("g02-custom")
plt.show()
7. Subplots¶
A grid of small plots that share axes is the fastest way to compare many features:
features = ["Soil_Moisture", "Temperature_C", "Rainfall_mm", "Wind_Speed_kmh", "Humidity", "Soil_pH"]
fig, axes = plt.subplots(2, 3, figsize=(12, 6), sharey=False)
for ax, col in zip(axes.flat, features):
for cls in data.CLASSES:
ax.hist(train.loc[train["Irrigation_Need"] == cls, col], bins=30, histtype="step", lw=1.6,
density=True, color=plotting.CLASS_COLORS[cls], label=cls)
ax.set_title(col.replace("_", " "), fontsize=10)
ax.set_yticks([])
axes[0, 0].legend(fontsize=8)
fig.suptitle("Which features separate the classes?", fontweight="bold")
fig.tight_layout()
plotting.save("g02-subplots")
plt.show()
Soil moisture, temperature, rainfall and wind speed shift clearly between classes; humidity and pH barely move. That's a hypothesis for the next notebook to test properly.
| Syllabus topic | Where |
|---|---|
| Series and DataFrame | 1.1, 1.2 |
| Reading and writing CSV | 2.1, 2.2 |
| Inspecting datasets | 3 |
| Selecting and filtering: loc vs iloc, boolean indexing | 4.1, 4.2 |
| Line, bar, histogram, scatter, pie | 5.1 – 5.5 |
| Customising plots | 6 |
| Subplots | 7 |