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[]:__

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

__In []:__

gdp

__Out[]:__

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[]:__

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[]:__

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[]:__

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[]:__

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[]:__

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[]:__

I repeat the whole process for the life expectancy:

• search for ‘life expectancy’ on the World Bank sitesearch for ‘life expectancy’ on the World Bank site
• choose the ‘total’ dataset, which includes both female and male inhabitantschoose the ‘total’ dataset, which includes both female and male inhabitants
• note down its indicator (SP.DYN.LE00.IN)note down its indicator (SP.DYN.LE00.IN)
• use it to get the datause it to get the data
• reset the dataframe index.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[]:__

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[]:__

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[]:__

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[]:__

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[]:__

Finally, I drop the irrelevant year column.

__In []:__

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

__Out[]:__

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[]:__

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[]:__

__In []:__

gdp.head()

__Out[]:__

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 []:__

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[]:__

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[]:__

Lastly, the original columns are discarded.

__In []:__

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

__Out[]:__

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.