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)


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

[ ]:


YEAR = 2013
GDP_INDICATOR = 'NY.GDP.MKTP.CD'
start=YEAR, end=YEAR)


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()


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'
start=YEAR, end=YEAR)
life = data.reset_index()


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


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()


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'


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

[ ]:




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')



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)



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)



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

[ ]:




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.