Interactive Session

A cartoon panda, mid-transformation. MidJourney 5

Everything we have done so far this week takes a table and gives you back a smaller piece of it. Filtering gives you fewer rows. Sorting gives you the same rows in a different order. Cleaning gives you fewer rows and tidier ones.

This afternoon we go the other way and make the table bigger. You will add columns that were not in the file: a unit somebody should have recorded, a comparison somebody should have computed, a label somebody should have written down.

Building new columns is a lot of what data science actually is, because the number your question needs is almost never a number that anybody put in the file.

By the end of this session you will be able to:

write the derived-column pattern, df['new'] = expression
build a new column from a column and a number, and from two columns
use np.log10() when a column spans several orders of magnitude
tidy a text column with .str.strip(), .str.lower(), and .str.replace()

Getting Started

Create the file. In the Explorer, hover over the EDS217 heading and click New File…, then type the name in full, extension included: Session_4C_Transforming_Data.ipynb
Check the kernel. The Kernel Selector in the notebook’s action bar should read Python 3.11.15 (Conda: eds217). If it reads anything else, click it, choose Change Kernel, and pick the eds217 entry.
Add a title cell. Click + Markdown in the action bar, and give it this content, with today’s date:

# Day 4: Session 4C - The Derived-Column Pattern

[Session Webpage](https://eds-217-essential-python.github.io/course-materials/interactive-sessions/4c_transforming_data.html)

Date: 09/03/2026

Save with Ctrl + S (Cmd + S on macOS), and keep saving as you go.
Read the microplastics data and keep only the samples reported in pieces per cubic metre, which is the unit you can actually do arithmetic with:

Code

import pandas as pd
import numpy as np

url = 'https://eds-217-essential-python.github.io/data/marine_microplastics.csv'
plastics = pd.read_csv(url)

m3 = plastics[plastics['Unit'] == 'pieces/m3'].copy()
m3.shape

(10178, 22)

10,178 samples, all in the same unit. Note the .copy(): you are about to change this table, which is exactly the case we learned to copy for on Day 3.

The pattern

Here it is, whole:

Code

m3['pieces_per_liter'] = m3['Measurement'] / 1000

Code

m3[['Measurement', 'Unit', 'pieces_per_liter']].head()

	Measurement	Unit	pieces_per_liter
0	0.020000	pieces/m3	0.000020
1	0.008000	pieces/m3	0.000008
2	0.019886	pieces/m3	0.000020
3	0.018000	pieces/m3	0.000018
4	0.000000	pieces/m3	0.000000

You have just written the derived-column pattern. The general form is:

df['new_column'] = expression

The left side is a column name that does not exist yet, in square brackets, exactly the way you would ask for a column that does. The right side is any expression that produces one value per row. Assignment creates the column.

First, the arithmetic happened on the whole column at once. You did not write a loop. You said “divide this column by a thousand” and pandas did it 10,178 times.

Code

m3['pieces_per_liter'].describe()

count    10178.000000
mean         0.219409
std          2.599555
min          0.000000
25%          0.000000
50%          0.000007
75%          0.000050
max        110.480000
Name: pieces_per_liter, dtype: float64

Second, unlike almost everything else we have learned this week, the derived-column pattern does change the table in place. There is nothing to assign back, because the assignment is the whole statement.

Code

m3.shape

(10178, 23)

23 columns now, up from 22.

Note

🐍 If the name on the left already exists, pandas replaces the old column with the new one and prints no warning at all. Overwriting is occasionally what you want, and it is more often how the original values get lost, so use a new name unless you are certain.

✏️ Test your knowledge

Latitude is in degrees. Add a column to m3 called latitude_radians holding the same values in radians, by multiplying by 3.141592653589793 / 180. Display Latitude and latitude_radians side by side for the first few rows, and check that 90 degrees would give about 1.57.

A column and a column

The right side can name more than one column. When it does, pandas lines the columns up row by row and computes each row’s answer from that row’s values.

Yesterday we ranked foods by the Economist’s banana index. Today you can build the index yourself.

Code

url = 'https://eds-217-essential-python.github.io/data/banana_index.csv'
foods = pd.read_csv(url, index_col='entity')
foods[['emissions_kg', 'land_use_kg', 'Bananas index (kg)']].head()

	emissions_kg	land_use_kg	Bananas index (kg)
entity
Ale	0.488690	0.811485	0.559558
Almond butter	0.387011	7.683045	0.443134
Almond milk	0.655888	1.370106	0.751002
Almonds	0.602368	8.230927	0.689721
Apple juice	0.458378	0.660629	0.524851

emissions_kg is kilograms of CO₂ per kilogram of food. The banana index is just that number divided by the same number for bananas:

Code

banana_emissions = foods.loc['Bananas', 'emissions_kg']
banana_emissions

np.float64(0.87334957)

Code

foods['my_banana_index'] = foods['emissions_kg'] / banana_emissions
foods[['emissions_kg', 'Bananas index (kg)', 'my_banana_index']].head()

	emissions_kg	Bananas index (kg)	my_banana_index
entity
Ale	0.488690	0.559558	0.559558
Almond butter	0.387011	0.443134	0.443134
Almond milk	0.655888	0.751002	0.751002
Almonds	0.602368	0.689721	0.689721
Apple juice	0.458378	0.524851	0.524851

Your column and theirs agree to seven decimal places. The banana index we ranked foods with yesterday is a single derived column, and now you can build your own.

So make one they did not:

Code

foods['land_per_emission'] = foods['land_use_kg'] / foods['emissions_kg']
foods['land_per_emission'].sort_values(ascending=False).head(5)

entity
Almond butter    19.852250
Almonds          13.664286
Beans            12.428466
Chickpeas        11.578514
Lentils          10.831714
Name: land_per_emission, dtype: float64

Square metres of land per kilogram of CO₂. Almond butter, almonds and beans sit at the top: foods that ask for a lot of ground and very little atmosphere. No column in the file answers that question, and building the column that does took one line.

Row alignment is the whole point

foods['land_use_kg'] / foods['emissions_kg'] divides each food’s land use by that same food’s emissions. Pandas matches the two columns up by row label before it computes anything.

Row alignment is why you can write column arithmetic as if it were ordinary algebra, and why a misaligned index is one of the few ways column arithmetic goes wrong. On Day 6 we will build that failure on purpose, so that you have seen it once.

✏️ Test your knowledge

Add a column to foods called emissions_per_calorie_ratio holding emissions_1000kcal divided by emissions_kg. Then use the top-N pattern from yesterday to display the five foods with the largest values. In a markdown cell, say what a large value of that ratio means about a food.

When a column spans orders of magnitude

Back to the plastics. Look at the range of measurements:

Code

m3['Measurement'].describe()

count     10178.000000
mean        219.409152
std        2599.554575
min           0.000000
25%           0.000000
50%           0.007200
75%           0.049937
max      110480.000000
Name: Measurement, dtype: float64

The median is 0.0072 pieces per cubic metre and the maximum is 110,480, so the maximum is about seven orders of magnitude above the median. A spread that wide is very hard to summarise with a mean or to read off a histogram, because the largest few samples dominate both.

The fix is to work with the logarithm. Vectorised mathematics in Python comes from numpy, which you imported at the top of the notebook as np:

Code

positive = m3[m3['Measurement'] > 0].copy()
positive.shape

(7091, 23)

Code

positive['log10_measurement'] = np.log10(positive['Measurement'])
positive[['Measurement', 'log10_measurement']].head()

	Measurement	log10_measurement
0	0.020000	-1.698970
1	0.008000	-2.096910
2	0.019886	-1.701453
3	0.018000	-1.744727
5	0.013000	-1.886057

Code

positive['log10_measurement'].describe()

count    7091.000000
mean       -1.254441
std         1.502175
min        -3.170053
25%        -2.188425
50%        -1.665546
75%        -0.879686
max         5.043284
Name: log10_measurement, dtype: float64

A range from -3.2 to 5.0. Taking the logarithm turned a spread of many orders of magnitude into a range of single digits, and the column is now something you can average and plot.

Notice the filter on the line before. 3,087 of these samples recorded exactly zero pieces, and the logarithm of zero is undefined. Filtering them out first, with yesterday’s filter pattern and a .copy(), is the whole fix.

This is your entire numpy budget

np.log10() is the one numpy function this course asks you to know. You will meet numpy again if you go further into modelling or image work, but for tabular environmental data, pandas has almost everything you need.

A function applied to a column returns a column, so it goes on the right-hand side of the derived-column pattern like any other expression.

✏️ Test your knowledge

Add a column to positive called log10_per_liter holding the base-10 logarithm of the pieces_per_liter column you built earlier. Then say, in one sentence, how it relates numerically to log10_measurement. (It differs by a constant. What constant, and why?)

Tidying text with `.str`

Text columns need their own kind of transformation. Every pandas column of text has a .str attribute, and through it you reach the string methods, applied to every row at once.

`.str.strip()` removes stray whitespace

Ask this table for every sample tagged with the research vessel Tara:

Code

plastics[plastics['Keywords'] == 'R/V Tara'].shape

(0, 22)

Zero rows. On Day 3 we learned that an empty result is an answer, and that it is usually a spelling problem. It is one here too, but it is a spelling problem you cannot see. Asking for the distinct values prints them with quotation marks around each one, which is the easiest way to see the difference. Keywords has hundreds of distinct values, so we take a slice of four of them here rather than printing the lot:

Code

plastics['Keywords'].unique()[10:14]

array(['Amazon Continental Shelf',
       'Antarctic Circumnavigation Expedition', 'R/V Tara ',
       'SV Mir; ORV Alguita; SV Sea Dragon; RV Stad Amsterdam'],
      dtype=object)

Code

plastics[plastics['Keywords'] == 'R/V Tara '].shape

(23, 22)

The stored value is 'R/V Tara ', with a trailing space that nobody can see in a table. 23 rows were invisible to a filter that looked correct.

.str.strip() removes whitespace from both ends of every value:

Code

plastics['Keywords'] = plastics['Keywords'].str.strip()
plastics[plastics['Keywords'] == 'R/V Tara'].shape

(23, 22)

Note

🐍 Strip text columns as a reflex, the way you check .isnull().sum() as a reflex. Trailing spaces are invisible, they survive every copy and export, and they break exact matches silently. Three values in this column had them.

`.str.lower()` makes matching predictable

Code

plastics['Density Class'].value_counts()

Density Class
Medium       8029
Very Low     4155
Low          1944
High         1671
Very High     446
Name: count, dtype: int64

Code

plastics['density_class'] = plastics['Density Class'].str.lower()
plastics['density_class'].value_counts()

density_class
medium       8029
very low     4155
low          1944
high         1671
very high     446
Name: count, dtype: int64

The two outputs are the same five categories with the same five counts, because Density Class happens to be consistent already. Run it anyway on a column you have not checked: lower-casing a label column before you compare or count means 'Medium', 'medium' and 'MEDIUM' stop being three different categories, and you usually find out that they were only after you look.

`.str.replace()` swaps one piece of text for another

Code

plastics['Oceans'].value_counts()

Oceans
Atlantic Ocean    14483
Pacific Ocean      1402
Arctic Ocean         69
Southern Ocean       20
Name: count, dtype: int64

Every value ends in the word “Ocean”, which is a wasted word on every axis label you will ever draw from this column. .str.replace() takes the text to find and the text to put in its place:

Code

plastics['ocean'] = plastics['Oceans'].str.replace(' Ocean', '')
plastics['ocean'].value_counts()

ocean
Atlantic    14483
Pacific      1402
Arctic         69
Southern       20
Name: count, dtype: int64

One method at a time

You will see code online that runs several of these together:

df['site'] = df['site'].str.strip().str.lower().str.replace('-', '_')

Chaining works, and we are not doing it this week. Write one method per line, assign it back, and look at the result before you write the next one. When a step in a chain silently does nothing, the chain gives you no way to tell which of the three methods it was.

The colab this afternoon asks you to write those three cleaning steps as three separate statements for exactly that reason.

✏️ Test your knowledge

The Sampling Method column of plastics contains values like 'Neuston net' and 'Grab sample'. Make a new column called sampling_method_clean that is the same text in lower case. Then use .value_counts() on it and say how many distinct methods this archive used.

Putting it together

Cleaning and transforming are the same pass. Here is Day 4 so far, as a workflow:

Code

url = 'https://eds-217-essential-python.github.io/data/marine_microplastics.csv'
plastics = pd.read_csv(url)

# Clean: drop the rows with no measurement (session 4A), then strip the stray
# whitespace out of Keywords (this session)
plastics = plastics.dropna(subset=['Measurement'])
plastics['Keywords'] = plastics['Keywords'].str.strip()

# Select one unit, so arithmetic means something (session 3B)
samples = plastics[plastics['Unit'] == 'pieces/m3'].copy()
samples = samples[samples['Measurement'] > 0].copy()

# Transform (this session)
samples['ocean'] = samples['Oceans'].str.replace(' Ocean', '')
samples['pieces_per_liter'] = samples['Measurement'] / 1000
samples['log10_measurement'] = np.log10(samples['Measurement'])

samples[['ocean', 'Measurement', 'pieces_per_liter', 'log10_measurement']].head()

	ocean	Measurement	pieces_per_liter	log10_measurement
0	Atlantic	0.020000	0.000020	-1.698970
1	Atlantic	0.008000	0.000008	-2.096910
2	Pacific	0.019886	0.000020	-1.701453
3	Atlantic	0.018000	0.000018	-1.744727
5	Pacific	0.013000	0.000013	-1.886057

Code

samples.shape

(7091, 25)

Read it as a paragraph. Nine lines took a 16,245-row archive with three incompatible units (pieces/m3, pieces/10 mins and pieces kg-1 d.w.) and a measurement column spanning eight orders of magnitude to 7,091 comparable samples, with three columns that were not in the file. Every line is a pattern we have learned this week.

Key points

The derived-column pattern is df['new'] = expression. Assignment creates the column, and unlike most pandas operations it changes the table in place.
Arithmetic on a column applies to every row at once. No loop.
Naming two columns on the right side computes row by row, matched up by row label.
Reusing an existing name overwrites it with no warning.
np.log10() rescales a column that spans orders of magnitude. Filter out zeros first, because the logarithm of zero is undefined.
Text columns are cleaned through .str: .strip() for invisible whitespace, .lower() for predictable matching, .replace(old, new) for everything else. One method per line.

Getting Started

The pattern

A column and a column

When a column spans orders of magnitude

Tidying text with .str

.str.strip() removes stray whitespace

.str.lower() makes matching predictable

.str.replace() swaps one piece of text for another

Putting it together

Key points

Resources

Tidying text with `.str`

`.str.strip()` removes stray whitespace

`.str.lower()` makes matching predictable

`.str.replace()` swaps one piece of text for another