New columns, groups and joins

What you will be able to do

After this lesson you can

• make new columns from old ones,
• summarise the groups of a table with groupby, and
• join two tables with pd.merge.

New columns

In chapter 1, you wrote a formula once and used it on a whole array. A column of a table works in the same way, and a new column is made by assigning to a new name.

A good example is Kepler's third law of planetary motion. Johannes Kepler found, after years of work with the data of Tycho Brahe, that for the planets of the Sun, the square of the period $T$ is proportional to the cube of the distance $a$. If $T$ is in years and $a$ is in astronomical units (AU, the distance of the Earth from the Sun), then $T^2 / a^3 = 1$ for every planet.

Predict first. For the Earth, $T = 1$ and $a = 1$, so the ratio is 1. What do you expect for the other planets?

The ratios are 0.971, 1.030, 1.000, 1.006 and 1.000, and the mean is 1.002. Kepler's law is true to within a few percent, even for these rounded numbers. (The ratio for Mercury is the farthest from 1, and the reason is that the table gives Mercury's numbers with only two digits.)

Groups

A very common question is: how do the groups compare? The mean mass of the rocky planets and the giant planets, the average of a measurement for each trial, the number of patients in each ward. In pandas, this is groupby: split the rows into groups, apply a summary to each group, and combine the results.

It is worth seeing how the average hides the shape of the data. The mean of the giants, 111, is far from every one of them but Jupiter (317.8) and Saturn (95.2): Uranus has 14.5 and Neptune 17.1. A mean says little about a group with such different members.

Joining tables

Data is often in several tables. One table has the periods, and another has the masses, and the two share the column name. To use them together, we merge them.

The choice is again yours: do you want only the complete rows, or all the rows? The outer join shows you what is missing, which can be a good thing to know.

After an outer merge, the rows that were in only one table have missing values (NaN) in the other table's columns.

Common mistakes

• Applying a formula with a Python loop over the rows. Use the whole columns.
• Mixing up groupby("kind")["mass"] and groupby("mass")["kind"]. The first is the group, and the second is the column to summarise.
• Reading a mean of very unequal values as typical. Look at the values as well.
• Merging on a column with different spellings. "Earth " with a space is not "Earth".
• Forgetting that a default merge drops the rows that have no partner.

Practice

Start with two quick questions. Each New numbers gives you a fresh one, so you can practise as long as you like.

Then the labs and the problems:

1. Warm-up. Open the lab New columns. You add Kepler's ratio and a group mean.
2. Build. Open the lab Group and join and the problem Density of the planets.
3. Stretch. Open the problem Merge two tables.

Remember these

• A new column is a formula on whole columns: df["c"] = df["a"] / df["b"].
• groupby(column)[other].mean() splits, summarises and combines. agg gives several summaries.
• pd.merge(a, b, on=column) joins two tables. how="outer" keeps all the rows.
• Kepler's third law: $T^2 / a^3 = 1$ for planets of the Sun, with $T$ in years and $a$ in AU.

Check yourself

Try each one on your own first. If you are stuck, open a hint. Then check with Show the answer.

1. How do you add a column density that is mass divided by volume?

2. What does df.groupby("kind")["mass"].max() give?

3. Two tables have 5 and 4 rows, and share 3 names. How many rows does the default merge give? And the outer merge?

4. What is the value of $T^2 / a^3$ for a planet at 4 AU, with a period of 8 years?