<jupyter_start><jupyter_text>_Module 2 lesson 2_: Programmatic data wrangling with Python and Pandas    Learning outcomes:                Integrate and apply methods from the core data analysis libraries of Numpy, Pandas and Matplotlib to investigate and manipulate source data.    ---  2.1 Investigating and manipulating data with PandasIt would be nice if you could import messy data and have a program automatically restructure it for you into your chosen metadata schema. There is no current mechanism to do this, and programmatic data wrangling only reduces the complexity of the process in small ways. What it does do is reduce repetitive tasks, like copying-and-pasting, and massively limit the risk of introducing copy-related errors. Setting up your development environment, and installing third-party packagesPython is a programming language. At its core are the functions and definitions you learned in Lesson 1.2.1. However, Python is also a community of passionate developers - just like you - who each bring to the programming language their particular interest. It could be solving complex maths, or drawing striking charts and visualisations, creating computer games, or even working with structured data. When they do this, they create new functions which they can package up as libraries to be used by others. The Python community has produced tens of thousands of these packages, and we're going to install some of them now.If you haven't already, start your Anaconda Navigator: ![Jupyter menu (base)](images/jupyter-home-base.jpg)Select Environments from the menu on the left:![Jupyter menu](images/jupyter-menu.jpg)Anaconda allows you to create isolated development environments. The important thing to understand about these is that it keeps your programming software contained to its own "play-area", making it easy to extend and delete. Create a new environment by clicking on the "Create" button at the bottom of the screen: ![Jupyter environments](images/jupyter-environments.JPG "Jupyter environments")Name your environment anything you like, but you can call it `datascience` to make it easy to find. Now you need to install the packages we need during this course. There are two ways of doing this, one for packages that Conda already knows about, and one from the operating system terminal. We're going to do the second one now which will install everything we need.Left-click on the "arrow" next to "datascience" and choose `Open Terminal`. Make sure you click on the right environment; not `base` or `root`, but the name of the environment you just created: ![Jupyter terminal](images/jupyter-terminal.JPG "Jupyter terminal")A new window will open with a command prompt. Type in the following and enter.    pip install whyqd    ![Jupyter terminal window](images/jupyter-terminal-window.JPG "Jupyter terminal window")  [Whyqd](https://github.com/whythawk/whyqd/) has dependencies we will use during future lessons, one of which is [Pandas](https://pandas.pydata.org/pandas-docs/stable/), and these will be installed automatically as well. We won't be using `whyqd` until the next lesson, but this is a good time to make sure we have it. Now do the same for `matplotlib`, a charting library:    pip install matplotlib    Once it has finished installing (and you may need to answer "yes" when it prompts), return to Home and click to install Jupyter Notebook.![Jupyter module launcher](images/jupyter-datascience-home.JPG "Jupyter Module Launcher")Once installed, launch it: ![Jupyter launch](images/jupyter-launch.JPG "Launch Jupyter Notebook")You will need to launch Jupyter Notebook every time you want to do a tutorial or edit your notebooks. Fortunately, Anaconda creates a Windows shortcut so you can easily find it:![Jupyter Windows launcher](images/jupyter-windows-launcher.JPG "Jupyter Windows launcher")If you get stuck, [read the docs](https://docs.anaconda.com/) or search for solutions on [Stack Overflow](https://stackoverflow.com/).There are a host of online tutorials and resources that will deepen your knowledge of the topics discussed in this lesson, and you can probably find these yourself. The documentation for the libraries we use are also a critical resource and familiarising yourself with how to read and use these will repay itself massively:     References to the libraries installed for this course:                Anaconda        Jupyter Notebook        Pandas        Numpy        Matplotlib        Whyqd    ---  Worked data wrangling exampleWorking with data programmatically means working symbolically. Most of the time you won't be looking at columns and rows of data the way you will in a spreadsheet. The advantage is that you won't imagine patterns that aren't in the data, and that you can work with very large datasets. The disadvantage is that it's not always clear when something goes wrong.Your first step remains the need for data to be machine-readable. That means you need to be good at the skills presented in the first lesson. The software we're going to use cannot open files that are not prepared appropriately.Before you can explore data, you must ensure that there is a _header row_ and that there are _no hidden columns or fields_. Please review Lesson 1.1 if you're not absolutely sure of what this means.Let's start working with the module you'll be using and learning about for the rest of this course: `pandas`.    Syntax                After you've imported a module, dir(module) lets you see a list of all the functions implemented in that library.        You can also read the help from the module docstrings with help(module)<jupyter_code>import pandas as pd
import numpy as np

help(pd)<jupyter_output>Help on package pandas:

NAME
    pandas

DESCRIPTION
    pandas - a powerful data analysis and manipulation library for Python
    =====================================================================
    
    **pandas** is a Python package providing fast, flexible, and expressive data
    structures designed to make working with "relational" or "labeled" data both
    easy and intuitive. It aims to be the fundamental high-level building block for
    doing practical, **real world** data analysis in Python. Additionally, it has
    the broader goal of becoming **the most powerful and flexible open source data
    analysis / manipulation tool available in any language**. It is already well on
    its way toward this goal.
    
    Main Features
    -------------
    Here are just a few of the things that pandas does well:
    
      - Easy handling of missing data in floating point as well as non-floating
        point data.
      - Size mutability: columns can be inserted and deleted [...]<jupyter_text>We `import` a module and can rename it using the term `as`:    import pandas as pd    import numpy as np    There's nothing special about `pd` or `np`, it's just a convention, but we will be using these modules a ___lot___ so shortening pandas to pd and numpy to np makes typing slightly easier. We are now going to import the same Excel file we used in Lesson 1.1 and do exactly the same tutorial as before, only this time using `pandas` and `numpy`.We start by importing the Excel file. In pandas, a spreadsheet is called a `dataframe`, or `df` for short.<jupyter_code>source = "http://api.worldbank.org/v2/en/indicator/SP.URB.TOTL?downloadformat=excel"
# read_excel is a pandas function which will open an Excel file for you
# the `source` can be anywhere, either in a local file, or a remote online resource
df = pd.read_excel(source)
# head will print the first x rows of the dataframe so we can see what it looks like
df.head(5)<jupyter_output><empty_output><jupyter_text>Well, that's a problem, but then - if you remember the original tutorial - the header row isn't in the first row, it's actually in the fourth. Let's reimport our file and make sure we ignore the first two rows:<jupyter_code>df = pd.read_excel(source, header=3)
df.head(5)<jupyter_output><empty_output><jupyter_text>Notice the `NaN` in the `2019` column? `NaN` is short for Not-a-Number, and is the way that pandas (and `numpy` the software that pandas runs on) keeps track of undefined numbers. In this case it means that there are no numbers available for some of the countries for that year. Pandas will try and guess the format of the columns for you, but sometimes it gets it wrong. Let's check and see what it thinks:<jupyter_code>df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 264 entries, 0 to 263
Data columns (total 64 columns):
 #   Column          Non-Null Count  Dtype  
---  ------          --------------  -----  
 0   Country Name    264 non-null    object 
 1   Country Code    264 non-null    object 
 2   Indicator Name  264 non-null    object 
 3   Indicator Code  264 non-null    object 
 4   1960            258 non-null    float64
 5   1961            258 non-null    float64
 6   1962            258 non-null    float64
 7   1963            258 non-null    float64
 8   1964            258 non-null    float64
 9   1965            258 non-null    float64
 10  1966            258 non-null    float64
 11  1967            258 non-null    float64
 12  1968            258 non-null    float64
 13  1969            258 non-null    float64
 14  1970            258 non-null    float64
 15  1971            258 non-null    float64
 16  1972            258 non-null    float64
 17  1973            258 non-null    flo[...]<jupyter_text>`dtypes` tells us the data-types for each of the columns. `object` is any sort of `string` or data-type that doesn't fit into the core numerical, categorical or date types. `float64` means that these values are considered to be 64-bit floating-point numbers.    From here on, I will only reference the documentation for each new function I introduce as a comment in the code, with a link to an online resource where you can find extra information. This is a useful coding habit to get into as you may sometimes forget exactly how you discovered this solution or reference.    As an example,  https://stackoverflow.com/a/51794989 is a reference to Stackoverflow - a popular question and answer site amongst developers. If you visit this link, you'll get a host of information on the line of code, or technical solution, that follows. Some references, e.g.  Reference for np.where https://docs.scipy.org/doc/numpy/reference/generated/numpy.where.html, will leed you to the package documentation.    Get into the habit of reading reference material. Software development requires patience and you will always be looking things up. Doing your own research makes you a good coder.The complete list of columns can be reached as a list:<jupyter_code>df.columns<jupyter_output><empty_output><jupyter_text>The file is currently in the `wide` format and we would prefer it to be `long`, with all the years running down the dataframe. To do that, we need to `pivot` our dataframe and create a new `year` column. Pandas has a function called `melt` to do this, but first we need to get the columns we want to use:<jupyter_code>id_columns = ['Country Name', 'Country Code', 'Indicator Name', 'Indicator Code']
year_columns = set(df.columns).difference(set(id_columns))

# https://pandas.pydata.org/docs/reference/api/pandas.melt.html
df = pd.melt(df, id_vars=id_columns, value_vars=year_columns,
             var_name='Year', value_name='Value')
df.head(5)
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 15840 entries, 0 to 15839
Data columns (total 6 columns):
 #   Column          Non-Null Count  Dtype  
---  ------          --------------  -----  
 0   Country Name    15840 non-null  object 
 1   Country Code    15840 non-null  object 
 2   Indicator Name  15840 non-null  object 
 3   Indicator Code  15840 non-null  object 
 4   Year            15840 non-null  object 
 5   Value           15291 non-null  float64
dtypes: float64(1), object(5)
memory usage: 742.6+ KB<jupyter_text>`Year` is of type `object`. If it is important to convert that into a date-type, then we would need to do that. However, the file is fine for our purposes, so we can save this as a CSV:<jupyter_code>destination = "data/lesson-programmatic/world-urban-population-data.csv"
# `index=False` means that we're not going to keep that index column - it will be recreated when we open the file in future
df.to_csv(destination, index=False)<jupyter_output><empty_output><jupyter_text>Pandas can do a lot more than just move data around. You can filter and sort and draw charts too:<jupyter_code># import the charting library we need and set it to draw inline with the notebook
%matplotlib inline
# First, sort by Year to make sure we get everything in order
# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.sort_values.html
# inplace = True means it will sort and save to the df variable
df
# Here we filter the df["Country Name"] column only by values == "World"
# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.plot.line.html
df[df["Country Name"] == "World"].plot(x="Year", y="Value")<jupyter_output><empty_output><jupyter_text>2.2 Tutorial - cleaning complex dataThe spreadsheet you worked on in the first tutorial had much more complex problems than just being wide. It has merged cells that need to be cleaned up. Obviously, you could do this manually before importing into pandas, but let's have a go at fixing this programmatically.Firstly, remember that this is what it looks like:![Tutorial spreadsheet](images/tutorial-1-2.jpg)<jupyter_code>import numpy as np
import pandas as pd

source = "data/lesson-spreadsheet/HDR 2007-2008 Table 03.xlsx"

df = pd.read_excel(source)
df.head(12)<jupyter_output><empty_output><jupyter_text>As with the previous example, you can see that the "real" header row doesn't begin until around row 7, and even then it appears spread out over multiple rows. Plus there are a range of different problems. See if you can spot all of them:- Seemingly empty columns and rows (filled with nothing but null value `NaNs`)- Special printing characters (like `\n`)- References that probably don't belong in this spreadsheetThere will be more, but lets have a go at fixing the immediate problems.<jupyter_code># Rebase the header row
df = pd.read_excel(source, header=7)
# https://stackoverflow.com/a/51794989
# First remove all columns (axis=1) with no values
df = df.dropna(how="all", axis=1)
# Remove all rows (axis=0) with no values
df = df.dropna(how="all", axis=0).reset_index(drop=True)
df.head(6)<jupyter_output><empty_output><jupyter_text>We could simply fix the columns, but lets get a list of the information that's there already and use that to guide the new names we need to include.<jupyter_code>columns = []
for c in df.columns:
    # For each column, get the values in the first 5 rows
    # then keep all the strings (i.e. exclude if null) and append as a column
    columns.append([str(v) for v in df[c][:5] if pd.notnull(v)])
columns<jupyter_output><empty_output><jupyter_text>There's a few columns that appear to have no names. But those columns weren't deleted when we removed empty columns and rows, so there is information in them. When in doubt, open the source file and have a look. When you do, you'll notice that the first "empty" column actually contains the country names, and the remaining empty columns contain the individual references. This allows us to come up with a list of new column names:<jupyter_code>columns = [
    "HDI rank",
    "Country",
    "Human poverty index (HPI-1) - Rank",
    "Reference 1",
    "Human poverty index (HPI-1) - Value (%)",
    "Probability at birth of not surviving to age 40 (% of cohort) 2000-05",
    "Reference 2",
    "Adult illiteracy rate (% aged 15 and older) 1995-2005",
    "Reference 3",
    "Population not using an improved water source (%) 2004",
    "Reference 4",
    "Children under weight for age (% under age 5) 1996-2005",
    "Reference 5",
    "Population below income poverty line (%) - $1 a day 1990-2005",
    "Reference 6",
    "Population below income poverty line (%) - $2 a day 1990-2005",   
    "Reference 7",
    "Population below income poverty line (%) - National poverty line 1990-2004",   
    "Reference 8",
    "HPI-1 rank minus income poverty rank"
]
# Reset the dataframe columns
df.columns = columns
# Remove the rows that contained the information we no longer need
# and reset the index
# np.arange(5) creates an array = [0, 1, 2, 3, 4, 5]
df = df.drop(np.arange(5), axis=0).reset_index(drop=True)
df.head()<jupyter_output><empty_output><jupyter_text>This file doesn't have a separate metadata file to accompany it. All the references and footnotes are literally at the bottom of the file. Have a look:<jupyter_code>df.tail(30)<jupyter_output><empty_output><jupyter_text>We need to delete this from the dataframe ___but___ we cannot lose this information. You should also create a metadata file containing this information to keep with your restructured data file, this way you have all the guidance a user may need, along with machine-readable data.<jupyter_code># Drop the rows from the bottom of the data we want to keep, to the end (defined as len(df))
df = df.drop(np.arange(129,len(df)), axis=0).reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>We have one other issue as well. There are three Human Development Index categories, arbitrarily defined as:- `LOW HUMAN DEVELOPMENT` for `HDI ranks > 155`- `MEDIUM HUMAN DEVELOPMENT` for `HDI ranks > 70`- `HIGH HUMAN DEVELOPMENT` for everything elseWe need to remove these rows (since they get in the way of the core data) and reassign them to columns.<jupyter_code># Categories
hdi_categories = ["HIGH HUMAN DEVELOPMENT", "MEDIUM HUMAN DEVELOPMENT", "LOW HUMAN DEVELOPMENT"]
# Get rid of rows with the HDI categories
# https://stackoverflow.com/a/27360130 & https://stackoverflow.com/a/12098586
df = df.drop(df[df["HDI rank"].isin(hdi_categories)].index, axis=0).reset_index(drop=True)
# Set "HDI rank" to integers
df["HDI rank"] = pd.to_numeric(df["HDI rank"])
# Reference for np.where https://docs.scipy.org/doc/numpy/reference/generated/numpy.where.html
# Think of this as being similar to an if-then statement : where(test is true, true-thing, false-thing)
# this can also be nested: where(test is true, true-thing, where(new-test is true, true-thing, false-thing))
df.loc[:, "HDI category"] = np.where(df["HDI rank"] > 155,
                                     "LOW HUMAN DEVELOPMENT",
                                     np.where(df["HDI rank"] > 70,
                                              "MEDIUM HUMAN DEVELOPMENT",
                                              "HIGH HUMAN DEVELOPMENT"
                                             )
                                    )
df.sort_values(by="HDI rank", inplace=True)
df.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0764388489208633, 'fraction_numerical': 0.02137113838341092, 'mean_word_length': 3.7179935113551283, 'pattern_counts': {'":': 0, '<': 60, '<?xml version=': 0, '>': 64, 'https://': 13, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '538145', 'license': 'MIT', 'n_tokens_mistral': 5495, 'n_tokens_neox': 5053, 'n_words': 2478, 'path': 'Module 2 - Lesson 2 - Programmatic data wrangling with Python and Pandas.ipynb', 'repo_name': 'whythawk/data-wrangling-and-validation', 'size': '18904'}