1 Joining left, right and centre

Let’s take stock for a moment. There’s the original, unchanged table (with full country names) about the life expectancy:

In []:

life

Out[]:

	Country name	Life expectancy (years)
0	China	75
1	Russia	71
2	United States	79
3	India	66
4	United Kingdom	81

… and a table with the GDP in millions of pounds and also full country names.

In []:

gdp

Out[]:

	Country name	GDP (£m)
0	United Kingdom	1711727
1	United States	10716029
2	China	5905202
3	Brazil	1435148
4	South Africa	233937

Both tables have a common column with a common name (‘Country name’). I can join the two tables on that common column, using the merge() function. Merging basically puts all columns of the two tables together, without duplicating the common column, and joins any rows that have the same value in the common column.

There are four possible ways of joining, depending on which rows I want to include in the resulting table. If I want to include only those countries appearing in the GDP table, I call the merge() function like so:

In []:

merge(gdp, life, on='Country name', how='left')

Out[]:

	Country name	GDP (£m)	Life expectancy (years)
0	United Kingdom	1711727	81
1	United States	10716029	79
2	China	5905202	75
3	Brazil	1435148	NaN
4	South Africa	233937	NaN

The first two arguments are the tables to be merged, with the first table being called the ‘left’ table and the second being the ‘right’ table. The on argument is the name of the common column, i.e. both tables must have a column with that name. The how argument states I want a left join , i.e. the resulting rows are dictated by the left (GDP) table. You can easily see that India and Russia, which appear only in the right (expectancy) table, don’t show up in the result. You can also see that Brazil and South Africa, which appear only in the left table, have an undefined life expectancy. (Remember that ‘NaN’ stands for ‘not a number.)

A right join will instead take the rows from the right table, and add the columns of the left table. Therefore, countries not appearing in the left table will have undefined values for the left table’s columns:

In []:

merge(gdp, life, on='Country name', how='right')

Out[]:

	Country name	GDP (£m)	Life expectancy (years)
0	United Kingdom	1711727	81
1	United States	10716029	79
2	China	5905202	75
3	Russia	NaN	71
4	India	NaN	66

The third possibility is an outer join which takes all countries, i.e. whether they are in the left or right table. The result has all the rows of the left and right joins:

In []:

merge(gdp, life, on='Country name', how='outer')

Out[]:

	Country name	GDP (£m)	Life expectancy (years)
0	United Kingdom	1711727	81
1	United States	10716029	79
2	China	5905202	75
3	Brazil	1435148	NaN
4	South Africa	233937	NaN
5	Russia	NaN	71
6	India	NaN	66

The last possibility is an inner join which takes only those countries common to both tables, i.e. for which I know the GDP and the life expectancy. That’s the join I want, to avoid any undefined values:

In []:

gdpVsLife = merge(gdp, life, on='Country name', how='inner')

Out[]:

	Country name	GDP (£m)	Life expectancy (years)
0	United Kingdom	1711727	81
1	United States	10716029	79
2	China	5905202	75

Now it’s just a matter of applying the data transformation and combination techniques seen so far to the real data from the World Bank.

Exercise 5 Joining left, right and centre

Question

Put your learning into practice by completing Exercise 5 in the Exercise notebook 3.

Remember to run the existing code in the notebook before you start the exercise. When you’ve completed the exercise, save the notebook.

1.1 Constant variables

You may have noticed that the same column names appear over and over in the code.

If, someday, I decide one of the new columns should be called ‘GDP (million GBP)’ instead of ‘GDP (£m)’ to make clear which currency is meant (because various countries use the pound symbol), I need to change the string in every line of code it occurs.

Figure 1

An abstract image of different coloured vertical strips with a column of numbers through each. The strips are distorted by an arrow moving horizontally through them Laziness is the mother of invention. If I assign the string to a variable and then use the variable everywhere instead of the string, whenever I wish to change the string, I only have to edit one line of code, where it’s assigned to the variable. A second advantage of using names instead of values is that I can use the name completion facility of Jupyter notebooks by pressing ‘TAB’. Writing code becomes much faster…

In[]:

gdpInGbp = 'GDP (million GBP)'
gdpInUsd = 'GDP (US$)'
country = 'Country name'
gdp[gdpInGbp] = gdp[gdpInUsd].apply(usdToGbp)
headings = [country, gdpInGbp]
gdp = gdp[headings]

Such variables are meant to be assigned once. They are called constants , because their value never changes. However, if someone else takes my code and wishes to adapt and extend it, they may not realise those variables are supposed to remain constant. Even I may forget it and try to assign a new value further down in the code! To help prevent such slip-ups the Python convention is to write names of constants in uppercase letters, with words separated by underscores. Thus, any further assignment to a variable in uppercase will ring an alarm bell (in your head, the computer remains silent).

In[]:

GDP_GBP = 'GDP (million GBP)'
GDP_USD = 'GDP (US$)'
COUNTRY = 'Country name'
gdp[GDP_GBP] = gdp[GDP_USD].apply(usdToGbp)
headings = [COUNTRY, GDP_GBP]
gdp = gdp[headings]

Using constants is not just a matter of laziness. There are various advantages. First, constants stand out in the code.

Second, when making changes to the repeated values throughout the code, it’s easy to miss an occurrence. Using constants means the code is always consistent throughout.

Third, the name of the constant can help clarify what the value means. For example, instead of using the number 1995 throughout the code, define a constant that makes clear whether it’s a year, the cubic centimetres of a car engine or something else.

To sum up, using constants makes the code clearer, easier to change, and less prone to silly (but hard to find) mistakes due to inconsistent values.

Any value can be defined as a constant, whether it’s a string, a number or even a dataframe. For example, you could store the data you have loaded from the file into a constant, as a reminder to not change the original data. In the rest of the week, I’ll use constants mainly for the column names.

Exercise 6 Constants

Question

To practise using constants, rewrite your exercises in the Exercise notebook 3 using them.

1.2 Getting real

Having tried out the data transformations and combination on small tables, I feel confident about using the full data from the World Bank, which I pointed you to in Life expectancy project.

Open a new browser window and go to the World Bank’s data page. Type ‘GDP’ (without the quote marks) in the ‘Find an indicator’ box in the centre of the page and select ‘GDP current US$’. Click ‘Go’. This will take you to the data page you looked at earlier. Look at the top of your browser window. You will notice the URL is http://data.worldbank.org/indicator/NY.GDP.MKTP.CD. Every World Bank dataset is for an indicator (in this case GDP in current dollars) with a unique name (in this case NY.GDP.MKTP.CD).

Knowing the indicator name, it’s a doddle to get the data directly into a dataframe, by using the download() function of the wb (World Bank) module, instead of first downloading a CSV or Excel file and then loading it into a dataframe. (Note that CoCalc’s free plan doesn’t allow connecting to other sites, so if you are using CoCalc you’ll need to download the data as a CSV or Excel file from the World Bank and upload it to CoCalc.)

Here’s the code to get the 2013 GDP values for all countries. It may take a little while for the code to fetch the data.

In []:

from pandas.io.wb import download
YEAR = 2013
GDP_INDICATOR = 'NY.GDP.MKTP.CD'
data = download(indicator=GDP_INDICATOR, country='all',
              start=YEAR, end=YEAR)
data.head()

Out[]:

		NY.GDP.MKTP.CD
country	year
Arab World	2013	2.843483e+12
Caribbean small states	2013	6.680344e+10
Central Europe and the Baltics	2013	1.418166e+12
East Asia & Pacific (all income levels)	2013	2.080794e+13
East Asia & Pacific (developing only)	2013	1.168563e+13

This table definitely has an odd shape. The three columns don’t have their headings side by side, and the row numbering (0, 1, 2, etc) is missing. That’s because the first two ‘columns’ are in fact the dataframe index. You saw a similar table in Changing a dataframe’s index , when the index of the weather dataframe was set to be the ‘GMT’ column, with values of type datetime64. There’s a dataframe method to do the inverse, i.e. to transform the row names into column values and thereby reinstate the default dataframe index.

In []:

gdp = data.reset_index()
gdp.head()

Out[]:

	country	year	NY.GDP.MKTP.CD
0	Arab World	2013	2.843483e+12
1	Caribbean small states	2013	6.680344e+10
2	Central Europe and the Baltics	2013	1.418166e+12
3	East Asia & Pacific (all income levels)	2013	2.080794e+13
4	East Asia & Pacific (developing only)	2013	1.168563e+13

I repeat the whole process for the life expectancy:

• search for ‘life expectancy’ on the World Bank site

• choose the ‘total’ dataset, which includes both female and male inhabitants

• note down its indicator (SP.DYN.LE00.IN)

• use it to get the data

• reset the dataframe index.

In []:

LIFE_INDICATOR = 'SP.DYN.LE00.IN'
data = download(indicator=LIFE_INDICATOR, country='all',
              start=YEAR, end=YEAR)
life = data.reset_index()
life.head()

Out[]:

	country	year	SP.DYN.LE00.IN
0	Arab World	2013	70.086392
1	Caribbean small states	2013	71.966306
2	Central Europe and the Baltics	2013	76.127583
3	East Asia & Pacific (all income levels)	2013	74.893439
4	East Asia & Pacific (developing only)	2013	73.981255

By defining the year as a constant, it’s very quick to change the code to load both datasets for any other year. If you wish to get GDP data for an earlier year than for life expectancy, then you need to define a second constant.

Exercise 7 Getting real

Question

The approach described above requires an internet connection to download the data directly from the World Bank. That may require some time, or sometimes not even work if the connection fails. Moreover, the World Bank sometimes changes its data format, which could break the code in the rest of this week.

Therefore, the Exercise notebook 3 loads instead the GDP and life expectancy data from files WB GDP 2013.csv and WB LE 2013.csv and Exercise 7 uses the file WB POP 2013.csv , which you should add to your disk folder or CoCalc project. All files are in the normal tabular format and need no resetting of the indices.

1.3 Cleaning up

You may have noticed that the initial rows are not about countries, but groups of countries. Such aggregated values need to be removed, because we’re only interested in individual countries.

The expression frame[m:n], with n an integer bigger than m , represents the ‘sub-table’ from row m to row n-1. In other words, it is a slice of frame with exactly n minus m rows. The expression is equivalent to the more convoluted expression frame.head(n).tail(n-m).

In []:

gdp[0:3]

Out[]:

	country	year	NY.GDP.MKTP.CD
0	Arab World	2013	2.843483e+12
1	Caribbean small states	2013	6.680344e+10
2	Central Europe and the Baltics	2013	1.418166e+12

To slice all rows from m onwards, you don’t have to count how many rows there are beforehand, just omit n.

In []:

gdp[240:]

Out[]:

	country	year	NY.GDP.MKTP.CD
240	Uzbekistan	2013	5.679566e+10
241	Vanuatu	2013	8.017876e+08
242	Venezuela, RB	2013	3.713366e+11
243	Vietnam	2013	1.712220e+11
244	Virgin Islands (U.S.)	2013	NaN
245	West Bank and Gaza	2013	1.247600e+10
246	Yemen, Rep.	2013	3.595450e+10
247	Zambia	2013	2.682081e+10
248	Zimbabwe	2013	1.349023e+10

By trying out head(m) for different values of m , I find that the list of individual countries starts in row number 34, with Afghanistan. Hence, I slice from row 34 onwards, and that’s my new dataframe.

In []:

gdp = gdp[34:]
gdp.head()

Out[]:

	country	year	NY.GDP.MKTP.CD
34	Afghanistan	2013	2.031088e+10
35	Albania	2013	1.291667e+10
36	Algeria	2013	2.101834e+11
37	American Samoa	2013	NaN
38	Andorra	2013	3.249101e+09

Unsurprisingly, there is missing data, so I remove those rows, as shown in Missing values in Week 4.

In []:

gdp = gdp.dropna()
gdp.head()

Out[]:

	country	year	NY.GDP.MKTP.CD
34	Afghanistan	2013	2.031088e+10
35	Albania	2013	1.291667e+10
36	Algeria	2013	2.101834e+11
38	Andorra	2013	3.249101e+09
39	Angola	2013	1.241632e+11

Finally, I drop the irrelevant year column.

In []:

COUNTRY = 'country'
headings = [COUNTRY, GDP_INDICATOR]
gdp = gdp[headings]
gdp.head()

Out[]:

	country	NY.GDP.MKTP.CD
34	Afghanistan	2.031088e+10
35	Albania	1.291667e+10
36	Algeria	2.101834e+11
38	Andorra	3.249101e+09
39	Angola	1.241632e+11

And now I repeat the whole cleaning process for the life expectancy table.

In []:

headings = [COUNTRY, LIFE_INDICATOR]
life = life[34:].dropna()[headings]
life.head()

Out[]:

	country	SP.DYN.LE00.IN
34	Afghanistan	60.931415
35	Albania	77.537244
36	Algeria	71.009659
39	Angola	51.866171
40	Antigua and Barbuda	75.829293

Note how a single line of code can chain a row slice, a method call and a column slice, because each takes a dataframe and returns a dataframe.

Exercise 8 Cleaning up

Question

Clean up the population data from Exercise 7, in Exercise 8 in the exercise notebook 3.

1.4 Joining and transforming

With the little tables, I first transformed the columns and then joined the tables.

Figure 2

An image of a bride and groom holding hands with the minister between them in the background As you may be starting to realise, there’s often more than one way to do it. Just for illustration, I’ll do the other way round for the big tables. Here are the tables, as a reminder.

In []:

life.head()

Out[]:

	country	SP.DYN.LE00.IN
34	Afghanistan	60.931415
35	Albania	77.537244
36	Algeria	71.009659
39	Angola	51.866171
40	Antigua and Barbuda	75.829293

In []:

gdp.head()

Out[]:

	country	NY.GDP.MKTP.CD
34	Afghanistan	2.031088e+10
35	Albania	1.291667e+10
36	Algeria	2.101834e+11
38	Andorra	3.249101e+09
39	Angola	1.241632e+11

First, an inner join on the common column to combine rows where the common column value appears in both tables.

In []:

gdpVsLife = merge(gdp, life, on='country', how='inner')

gdpVsLife.head()

Out []:

	country	NY.GDP.MKTP.CD	SP.DYN.LE00.IN
0	Afghanistan	2.031088e+10	60.931415
1	Albania	1.291667e+10	77.537244
2	Algeria	2.101834e+11	71.009659
3	Angola	1.241632e+11	51.866171
4	Antigua and Barbuda	1.200588e+09	75.829293

Second, the dollars are converted to millions of pounds.

In []:

GDP = 'GDP (£m)'
column = gdpVsLife[GDP_INDICATOR]

gdpVsLife[GDP] = column.apply(usdToGbp).apply(roundToMillions)

gdpVsLife.head()

Out[]:

	country	NY.GDP.MKTP.CD	SP.DYN.LE00.IN	GDP (£m)
0	Afghanistan	2.031088e+10	60.931415	12980
1	Albania	1.291667e+10	77.537244	8255
2	Algeria	2.101834e+11	71.009659	134322
3	Angola	1.241632e+11	51.866171	79349
4	Antigua and Barbuda	1.200588e+09	75.829293	767

Third, the life expectancy is rounded to the nearest integer, with a by now familiar function.

In []:

LIFE = 'Life expectancy (years)'

gdpVsLife[LIFE] = gdpVsLife[LIFE_INDICATOR].apply(round)

gdpVsLife.head()

Out[]:

	country	NY.GDP.MKTP.CD	SP.DYN.LE00.IN	GDP (£m)	Life expectancy (years)
0	Afghanistan	2.031088e+10	60.931415	12980	61
1	Albania	1.291667e+10	77.537244	8255	78
2	Algeria	2.101834e+11	71.009659	134322	71
3	Angola	1.241632e+11	51.866171	79349	52
4	Antigua and Barbuda	1.200588e+09	75.829293	767	76

Lastly, the original columns are discarded.

In []:

headings = [COUNTRY, GDP, LIFE]
gdpVsLife = gdpVsLife[headings]
gdpVsLife.head()

Out[]:

	country	GDP (£m)	Life expectancy (years)
0	Afghanistan	12980	61
1	Albania	8255	78
2	Algeria	134322	71
3	Angola	79349	52
4	Antigua and Barbuda	767	76

For the first five countries there doesn’t seem to be any relation between wealth and life expectancy, but that might be just for those countries.

Exercise 9 Joining and transforming

Question

Have a go at merging dataframes with an inner join in Exercise 9 in the Exercise notebook 3.