<jupyter_start><jupyter_text>Transforming and Combining DataIn the previous module you worked on a dataset that combined two different `World HealthOrganization datasets: population and the number of deaths due to tuberculosis`.They could be combined because they share a `common attribute: the countries`. Thisweek you will learn the techniques behind the creation of such a combined dataset.<jupyter_code>import warnings
warnings.simplefilter('ignore', FutureWarning)

import pandas as pd
table = [
  ['UK', 2678454886796.7],    # 1st row
  ['USA', 16768100000000.0],  # 2nd row
  ['China', 9240270452047.0], # and so on...
  ['Brazil', 2245673032353.8],
  ['South Africa', 366057913367.1]
]
headings = ['Country', 'GDP (US$)']
gdp = pd.DataFrame(columns=headings, data=table)
headings = ['Country name', 'Life expectancy (years)']
table = [
  ['China', 75],
  ['Russia', 71],  
  ['United States', 79],
  ['India', 66],
  ['United Kingdom', 81]
]
life = pd.DataFrame(columns=headings, data=table)
def roundToMillions (value):
    return round(value / 1000000)
def usdToGBP (usd):
    return usd / 1.564768 # average rate during 2013 
def expandCountry (name):
    if name == 'UK':
        return 'United Kingdom'
    elif name == 'USA':
        return 'United States'
    else:
        return name
def expandCountry (name):
    if name == 'UK':
        name = 'United Kingdom'
    if name == 'USA':
        name = 'United States'
    return name
gdp['Country name'] = gdp['Country'].apply(expandCountry)
gdp['GDP (£m)'] = gdp['GDP (US$)'].apply(usdToGBP).apply(roundToMillions)
gdp['GDP (US$)'].apply(roundToMillions).apply(usdToGBP).apply(round)
headings = ['Country name', 'GDP (£m)']
gdp = gdp[headings]<jupyter_output><empty_output><jupyter_text>Joining left, right and centreAt this point, both tables have a common column, 'Country name', with fully expanded country names.<jupyter_code>Let’s take stock for a moment. There’s the original, unchanged table (with full country
names) about the life expectancy:
life<jupyter_output><empty_output><jupyter_text>… and a table with the GDP in millions of pounds and also full country names.<jupyter_code>gdp<jupyter_output><empty_output><jupyter_text>Both tables have a common column with a common name (‘Country name’). We can **join** thetwo 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 joinsany rows that have the same value in the common column.There are four possible ways of joining, depending on which rows we want to include in theresulting table. If we want to include only those countries appearing in the GDP table, we callthe **merge()** function. A **left join** takes the rows of the left table and adds the columns of the right table.<jupyter_code>pd.merge(gdp, life, on='Country name', how='left')<jupyter_output><empty_output><jupyter_text>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 thecommon column, i.e. both tables must have a column with that name. The **how** argumentstates we want a **left join** , i.e. the resulting rows are dictated by the left (GDP) table. Youcan 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 appearonly 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 lefttable. Therefore, countries not appearing in the left table will have undefined values for theleft table’s columns. A **right join** takes the rows from the right table, and adds the columns of the left table.<jupyter_code>pd.merge(gdp, life, on='Country name', how='right')<jupyter_output><empty_output><jupyter_text>The third possibility is an **outer join** which takes all countries, i.e. whether they are in theleft or right table. The result has all the rows of the left and right joins. An **outer join** takes the union of the rows, i.e. it has all the rows of the left and right joins.<jupyter_code>pd.merge(gdp, life, on='Country name', how='outer')<jupyter_output><empty_output><jupyter_text>The last possibility is an **inner join** which takes only those countries common to bothtables, i.e. for which I know the GDP and the life expectancy. That’s the join we want, toavoid any undefined values: An **inner join** takes the intersection of the rows (i.e. the common rows) of the left and right joins.<jupyter_code>gdpVsLife = pd.merge(gdp, life, on='Country name', how='inner')
gdpVsLife<jupyter_output><empty_output><jupyter_text>TaskJoin your population dataframe previous task with `gdpVsLife`, in four different ways, and note the differences.<jupyter_code>gdpVsLife = pd.merge(gdp, life, on='Country name', how='inner')
gdpVsLife<jupyter_output><empty_output><jupyter_text>Constant variablesYou may have noticed that the same column names appear over and over in the code.If, someday, we 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 countriesuse the pound symbol), we need to change the string in every line of code it occurs.Laziness is the mother of invention. If we assign the string to a variable and then use thevariable everywhere instead of the string, whenever we wish to change the string, we onlyhave to edit one line of code, where it’s assigned to the variable. A second advantage ofusing names instead of values is that we can use the name completion facility of Jupyternotebooks by pressing **‘TAB’**. Writing code becomes much faster… 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 theirvalue never changes. However, if someone else takes our code and wishes to adapt andextend it, they may not realise those variables are supposed to remain constant. Even wemay forget it and try to assign a new value further down in the code! To help prevent suchslip-ups the Python convention is to write names of constants in uppercase letters, withwords separated by underscores. Thus, any further assignment to a variable in uppercasewill ring an alarm bell `(in your head, the computer remains silent)`. Constants are used to represent fixed values (e.g. strings and numbers) that occur frequently in a program. Constant names are conventionally written in uppercase, with underscores to separate multiple words.<jupyter_code>GDP_USD = 'GDP (US$)'
GDP_GBP = 'GDP (£m)'
GDP_USD
COUNTRY = 'Country name'
gdp[GDP_GBP] = gdp[GDP_USD].apply(usdToGbp)
headings = [COUNTRY, GDP_GBP]
gdp = gdp[headings]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09111584932480454, 'fraction_numerical': 0.014214641080312722, 'mean_word_length': 5.161120840630473, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 37, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '334582', 'license': 'MIT', 'n_tokens_mistral': 2127, 'n_tokens_neox': 1998, 'n_words': 919, 'path': 'Anita Mburu-WT-21-022/22.ipynb', 'repo_name': 'ruthwaiharo/Week-5-Assessment', 'size': '7035'}