Clean data is data that does not require any transformation or updates prior to commencing analysis. If your data requires cleansing work prior to use, then you have **messy data**.

If you primarily work with Excel, working with messy data means you regularly find yourself carrying out tasks such as:

1. Removing non printable characters such as line breaks.
2. Removing leading and trailing spaces or extra spaces.
3. Using the text to columns wizard to split data into separate columns.
4. Populating blank cells or removing blank rows or columns.
5. Identifying duplicate values or duplicate data.
6. Using conditional formatting to highlight errors.
7. Correcting the capitalisation of text.
8. Using paste special to clear formats.

This list is far from exhaustive and messy data is commonplace. Every messy dataset encountered is messy in its own unique way and requires its own unique processes to clean. However, once all such issues are solved and we have clean data, we can progress to the interesting part of the job, the analysis.

In this course we'll work through the complete data-cleaning pipeline, from importing data, to cleaning and manipulating it, to exporting it for analysis.

We'll do this using both Excel and Python, and we'll see how Python can be used to speed up our data cleaning operations.

<Image
	src={
		"/images/course-info/strategies-and-techniques-for-data-cleaning-in-excel-and-python/img1.png"
	}
	alt={"Basic data exploration in Python - digiLab Academy"}
	width={800}
	height={199}
	caption={"Figure 1. Basic data exploration in Python"}
/>

We'll start by focusing on the **Tidy Data Principles**. These principles are a set of rules that help us to structure our data in a way that makes it easy to clean, manipulate and analyse.

We'll then move on to look at some of the most common data cleaning techniques in Excel, and we'll see how we can use Python to speed up our data cleaning operations.

We'll then move on to look at how we can use Python to clean and manipulate data using Pandas, one of the most popular data science libraries.

When you complete this course, you'll have the ability to take real-world data and clean it for further analysis in your own data science projects.

The foundation of every successful machine learning model is clean, high-quality data. But what does clean data look like, and how can you ensure the data you're working with meets that standard? Whether you're dealing with tabular data in Excel or tackling larger datasets in Python, understanding effective strategies for data cleaning is crucial. In this course, we’ll equip you with the essential techniques to turn messy, incomplete, and inconsistent data into a valuable resource.

Richard Warburton

:::success
**📂 Resources**
Download the resources for this lesson <Link href="/resources/strategies-and-techniques-for-data-cleaning-in-excel-and-python/what-does-clean-data-look-like-in-microsoft-excel/Resources.zip" download="Resources.zip">here.</Link>
:::

## What Is Clean Data?

Clean data is data that does not require any transformation or updates prior to commencing work on analysis. If your data requires cleansing work prior to use, then you have messy data.

If you primarily work with Excel, working with messy data means you regularly find yourself carrying out tasks such as:

1. Removing non printable characters such as line breaks.
2. Removing leading and trailing spaces or extra spaces.
3. Using the text to columns wizard to split data into separate columns.
4. Populating blank cells or removing blank rows or columns.
5. Identifying duplicate values or duplicate data.
6. Using conditional formatting to highlight errors.
7. Correcting the capitalisation of text.
8. Using paste special to clear formats.

This list is far from exhaustive and messy data is commonplace. Every messy dataset encountered is messy in its own unique way and requires its own unique processes to clean. However, once all such issues are solved and we have clean data, we can progress to the interesting part of the job, the analysis.

## Why Does Clean Data in Excel Matter?

Clean data is essential for accurately completing the analysis required.

The process of cleaning data consumes a significant amount of effort. It is estimated that 80% of a data analyst’s time is spent cleaning messy data. As our stakeholders are often only interested in the output analysis, this effort can be viewed as an overhead. It is, however, an essential task. If the data is not in a suitable format to complete the analysis, then we can’t complete it.

It is therefore in our interests to understand what the best practices are for cleaning data and to have a range of tools and techniques in our armory to efficiently clean data when needed.

This naturally poses a question, what does good clean data look like?

## Introducing Tidy Data

Understanding the Tidy Data Principals makes the cleansing of data in Excel a much simpler task.

In 2014, a data scientist called Hadley Wickham was pondering the process of cleaning data. He noted that as projects evolved, cleaning activities often needed to be repeated or re-applied to ensure that the required analysis could be completed. Despite the significant effort spent cleaning data, there was little guidance regarding how to best structure data to minimize and simplify such tasks.

This led him to develop the Tidy Data Principals, a simple framework for defining what a good clean data set looks like.

The Tidy Data Principles are highly relevant to analysis work completed in Excel. By applying the principles, you maximize the amount of analysis that can be completed while minimizing the additional work required.

### Some Useful Definitions

Before we explore the Tidy Data Principals, we need to cover some definitions:

#### Observations

An **Observation** is an individual instance of all the data points that we have collected and are interested in.

For example, we may have collected data on individual people. All the data collected for a specific person would be called an Observation.

#### Variable

A **Variable** is a feature we measure for each observation and can vary from one observation to the next.

For example, if we are collecting data on individual people, we may capture their height, age, and place of birth as three separate variables.

Variables are sometimes referred to as **Features** or **Attributes** as well.

#### Value

A **Value** is the specific measurement recorded for an observation and variable.

For example, if we are collecting data on people, an individual person’s Height being 185cm would be an example of a Value.

### Tidy Data Principals

Tidy Data sets out three principles that help us define the optimal structure for capturing clean data.

The Tidy Data principles are:

1. Each value is a cell.
2. Each variable is a column.
3. Each observation is a row.

These principles transcend all data work and are particularly relevant to Excel as we can easily conceptualize and visualize them on spreadsheet grids.

## Tidy Data in Practice

Let’s look at how we apply these principles in Excel with an example of transforming a messy data set into a clean and tidy one.

### Example Of a Messy Data Set in Microsoft Excel

Consider the following task, we have been asked to analyze exam results across a school. We ask everyone to submit their class marks and we ask one teacher to collate all the results into a single file.

We receive the following back (example limited to first three students' results, the full file can be found in the tutorial resources panel)

<Image
	src={"/images/posts/clean-data-in-excel/img1.png"}
	alt={
		"Data collated by a teacher showing exam marks for three students by subject. The data is messy as the subject variables are in columns and each cell contains three values, the mark, marks available and percentage score."
	}
	width={1000}
	height={226}
	caption={"Data collated by a teacher showing exam marks in a messy format."}
/>

While this captures the exam results, we can quickly see that our options for analyzing this data are limited. For example, you cannot quickly calculate the average mark for each subject.

The full example data set can be found in the lesson resources panel.

To resolve this, we can apply the Tidy Data Principles and re-format how the data is captured.

### Each Value Is a Cell.

This may sound like a simple statement, but it is quite common to find multiple values within a single cell as is the case here. If we look at an example of a single cell, we can see that it contains three values.

<Image
	src={"/images/posts/clean-data-in-excel/img2.png"}
	alt={
		"Example of a single cell from the messy exam data, showing how the exam mark, total marks available and percentage score have been typed into a single cell."
	}
	width={1000}
	height={226}
	caption={"Example of a single cell from the messy exam data."}
/>

The Exam Mark (82), the Total Marks available (100) and the Percentage Performance (82%). Each of these values should be captured in separate cells.

### Each Variable Is a Column.

In exploring the values above, we split the data in one cell into three variables. However, as there are three subjects split across multiple columns of the spreadsheet, it is not totally straight forward to split these values into individual columns representing each variable.

If we were to split each subject response into individual columns, we would have instances of the three variables for each exam subject.

<Image
	src={"/images/posts/clean-data-in-excel/img4.png"}
	alt={
		"Example demonstrating how the subject variable being contained within columns means that we cannot contain other variables (such as exam mark) in a single column as they are distributed over a column for each subject."
	}
	width={1000}
	height={153}
	caption={"Example of variables stored in columns."}
/>

Meaning each variable is in one or more columns. To resolve this, we must reframe how we think about the subjects and think of them as a fourth variable.

By creating a subject column to store this variable we can align the corresponding values in their own columns.

<Image
	src={"/images/posts/clean-data-in-excel/img5.png"}
	alt={
		"Example demonstrating that by treating the subject as a column, we can ensure that each variable is one and only one column."
	}
	width={1000}
	height={141}
	caption={"Subject variable as a single column."}
/>

### Each Observation Is a Row.

We can ensure that each row of the data is an observation by recording the correct individual against each row as a variable.

<Image
	src={"/images/posts/clean-data-in-excel/img6.png"}
	alt={
		"Example showing how the addition of the person as a variable to the data means that each row of data is now referring to an observation."
	}
	width={1000}
	height={144}
	caption={"Rows are observation."}
/>

Individual observations are not always uniquely defined by a single variable. We can see in this example that it is the combination of the individual and the subject variables that define the unique observations.

### Example Of Tidy Data in Microsoft Excel

The messy data we were provided with did not enable us to analyze the exam results. By applying the Tidy Data Principles and reformatting how the data is captured, the resultant table is in a format that will allow us to quickly complete any analysis requested from the data.

<Image
	src={"/images/posts/clean-data-in-excel/img7.png"}
	alt={
		"Example of the exam results in a tidy format.  Each row is an observation, each column is a variable, and each value is a cell."
	}
	width={1000}
	height={312}
	caption={"Exam results in a tidy format."}
/>

## How Do I Use Tidy Data in Excel?

By capturing data in a tidy format, we maximize the analysis potential in Excel.

Tidy Data is optimal for use in pivot tables where clean tabular data organized in columns yields the best results. When Tidy Data is used, the aggregation and summarization of data via a pivot table becomes a quick and simple task. Given summarized data, the visualization and production of graphs becomes significantly easier.

Subsets of data can be quickly produced via filtering, meaning relevant parts can be extracted or irrelevant ones discarded. Data can be sorted easily and the top or bottom observations by any given variable can be identified. Finally, data can be transformed and calculations taking one or more variables as inputs can be applied with ease.

## How Do You Approach Data Cleaning in Excel.

This article has introduced Tidy Data and provides us with a framework to define what a good clean data format looks like. We have explored this from an Excel perspective and shown that by using the Tidy Data Principles, you put yourself in the best possible position for completing any requested analysis.

The good news is that these principles extend far past the capabilities of Excel and are highly relevant and beneficial in all data work.

The same is true of the tools and techniques needed to clean data effectively. We can learn about these in the familiar Excel environment and in doing so, gain knowledge that has a much wider application. This knowledge is particularly relevant if you are aspiring to a career in machine learning and using other data analytics tools.


What Does Clean Data Look Like in Microsoft Excel?

:::success
**📂 Resources**
Download the resources for this lesson <Link href="/resources/strategies-and-techniques-for-data-cleaning-in-excel-and-python/supercharge-your-excel-data-cleaning-with-python/Resources.zip" download="Resources.zip">here.</Link>
:::

## What Is Clean Data?

In our last lesson,[What Does Clean Data Look Like in Microsoft Excel?](/course/strategies-and-techniques-for-data-cleaning-in-excel-and-python/what-does-clean-data-look-like-in-microsoft-excel), we introduced the concept of clean data. Clean data is the utopian state for all data analysis work as it requires no transformation prior to commencing analysis. We also introduced the Tidy Data Principals, which provides us with a framework for what this clean data should look like.

In this article, we will explore the process of cleaning data in Microsoft Excel; assess the options we have available to clean data; and demonstrate how we can use Python to carry out basic cleaning activities before analysing data in Excel.

## What Is Data Cleaning?

Data cleaning is the process of taking messy data and transforming it into a clean data set that is ready for analysis.

When cleaning data, we need to explore each data set and determine the steps required to clean. We must call upon our knowledge and experience to determine what steps are needed and then implement the correct techniques to execute them.

When first exploring a new messy data set, it is likely that we will need to determine a new set of steps to clean the data. However, if we are dealing with a refreshed dataset we have explored before, it is possible previously defined steps will be repeatable.

## What Are My Options for Cleaning Data in Excel?

There are three primary options for cleaning data in Excel. All have their pros and their cons.

1. Manual Manipulation
2. Power Query
3. Python

Other options do exist. Visual Basic for Applications (VBA) was once the powerhouse of Excel and has been superseded by the Power suite of apps. Other coding languages such as R, are significantly more complicated for beginners to learn. These have not been considered here.

### Manual Excel Manipulation

This is where most (if not all) people start out, however, you will quickly hit limitations and issues.

Initially, we manually process raw data into a clean format. We use formulas such as the TRIM function to remove blank spaces, or similar functions to convert the text in a cell into a new format; we may use the text to columns wizard to split data in the same column into multiple cells; and we may use lookups (such as VLOOKUP) to lookup values from one data set into another.

As our manual data cleaning skills progress, we may get used to using shortcut keys to quickly navigate all the cells in the spreadsheet. This speeds the whole process up, but it remains manual. If we are provided with an update of the file, one of the few situations where the same cleansing steps can probably be reapplied, then we must manually repeat all the steps.

Such a manual process is also prone to human error; with a chance for something to go wrong at every step. Unless we pick up on this, we may introduce errors into our data.

Manual data processing in excel is not recommended on a regular or repeatable basis. As a first step, it teaches us a lot about the process of cleaning data, however the manual overhead and the propensity for error means we need to find better ways of cleaning data.

### Power Query

Power Query is a powerful tool and useful if you only ever clean data for analysis in Excel or for use in other Microsoft products such as PowerBI.

It provides an engine for extracting data from various sources, transforming it into the format needed and then loading it into an analysis tool. This process is often referred to as an ETL process.

Users can program a set of repeatable steps to cleanse data and then reapply them at the click of a button. A graphical user interface and easy to use wizards enable transformation steps to be implemented without a knowledge of the code it is producing in the background.

It is powerful! All manual Excel transformations can be completed in Power Query, and it introduces tools that Excel functions cannot complete. Such as file collation and merging datasets.

However, there are limitations. The M language it uses is counter intuitive and difficult to understand. Unless your process is particularly well annotated it is also exceptionally hard to follow others’ work (or understand your own after some time away from it). It can be slow for large data sets and tracking back through the code to find errors is a time-consuming task.

If you only ever want to transform data for use in the Microsoft suite of tools, then Power Query is a viable method for Cleaning Data. However, if you aspire to advanced analytics, data science, and machine learning, then it is not recommended.

### Python

Using Python in conjunction with Excel to clean data opens significant opportunities for analysts. Complex (and repeatable) transformation processes can be implemented with a few lines of human readable code.

Python is a standalone programming language and is not proprietary to Microsoft. It is regularly listed as the [top programming language for data science](https://www.datacamp.com/blog/top-programming-languages-for-data-scientists-in-2022) and is one of the [best programming languages to learn as a beginner](https://blog.hubspot.com/marketing/best-programming-language-learn). It also has a range of preprogrammed libraries, which allow complex processes to be implemented with very little code.

Any data cleansing process that can be implemented manually or via Power Query can be replicated in Python.

There is a learning curve for Python and unlike Power Query, there is no friendly user interface to implement the code for us. However, opening a wider set of data tools for us to use, far outweighs the learning curve effort.

We strongly recommend learning to clean data contained within Microsoft Excel spreadsheets with Python. Something we will explore in more detail in the rest of this article.

## How Do I Get Started with Python?

If you are new to Python, then there are two resources we recommend to get started.

Firstly, [Pre-flight checks - Getting Started & Python Basics](/course/getting-started-and-python-basics) is a thorough instructor led course that will provide you with the foundations you need.

Secondly, [Think Python (2nd Edition)](https://greenteapress.com/wp/think-python-2e/ "Green Tea Press") by Allen B. Downey. This provides numerous worked examples and practice problems for you to solve.

For cleaning data, there are a few prerequisites you will need to understand:

1. Basic Python syntax and operations
2. Pandas and data frames (including indexing/slicing)
3. Knowledge of data types (String, float, integer, dictionaries etc.)
4. Executing Python script (in an IDE and/or notebook)

These two resources cover these topics in detail and provide you with the knowledge you need to get started.

## How Do I Work with Excel and Python?

The process we will be using can be explained in three high level steps:

1. Import data into Python from Excel.
2. Carry out cleaning tasks in Python.
3. Export data from Python back to Excel.

Within this, there are two best practice considerations.

1. Within step 1, always take a copy of your original data and never work directly on the original.
2. Within step 3, never overwrite the original data.

These are both focused on ensuring that we maintain our original data and can start the process again if we need.

The complexity in this process comes within step 2 where we need to determine what cleansing actions need to be applied and then implementing them in code.

Let’s explore this with an example.

## Simple Example: Finding and Replacing Values

Consider a scenario where we want to understand the total volume and value of orders by customer. A very common ask if your business sells items directly to customers.

We go to our order system, and we extract data into Excel. We can see that the data is tidy in its format (see last article), however there is an issue with the Price Per Unit Value.

<Image
	src={"/images/posts/supercharge-your-excel-data-cleaning-with-python/img1.png"}
	alt={
		"Messy data in Excel, exported from a company’s sales system.  The Price Per Unit column contains non numerical values, preventing the application of mathematical operations to analyze."
	}
	width={1000}
	height={196}
	caption={"Messy data in Excel"}
/>

It has several unnecessary characters which prevent Excel processing this as a number. Before we can complete the analysis, we need to clean and correct the data format for these values.

The files and code discussed in this example can be found in the lesson resources panel.

### Step 1: Import Data to Python from Excel

Our first task is to import the data into Python for cleaning. To do this, we use the [read_excel](https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html "Read_excel Documentation") function from the [Pandas](https://pandas.pydata.org/docs/index.html "Pandas Documentation") library.

```python
# Import packages required
import Pandas as pd

# Import messy data from Excel and assign to the mesy_data variabe as a data frame
messy_data = pd.read_excel('messy_order_data.xlsx', sheet_name='Sheet1')
```

This code imports the data from Sheet1 and stores it in a variable called messy_data as a data frame.

### Step 2: Cary Our Cleaning Tasks in Python

#### The Process to Clean Data

By visually inspecting the data (either in the Excel spreadsheet or in Python) we can see that there are three cleansing tasks we need to complete.

1. Remove the ‘,’ from the Price Per Unit Values
2. Remove the ‘£---’ from the Price Per Unit Values
3. Ensure the Values are numerical (not text)

The last step may not be an obvious one; however, by exploring the data type of the Price Per Unit Variable (a Pandas data series) after steps 1 and 2 (see supporting Jupyter Notebook), we can see it is an object. This will be treated as text when we export the data to Excel.

#### Code to Cleanse Data

The three cleaning steps above can be implemented with the following three lines of code. While it would be considered more [‘Pythonic’](https://towardsdatascience.com/how-to-write-pythonic-code-208ec1513c49) to implement this with a single line of code, we have stepped through this one step at a time to help readers understand the process.

```python
# Cleaning step 1: Remove ',' from the values and replace with ''
messy_data['Price Per Unit (£s)'] = messy_data['Price Per Unit (£s)'].str.replace(',','')

# Cleaning Step 2: Remove '£---' from the values and replace with ''
messy_data['Price Per Unit (£s)'] = messy_data['Price Per Unit (£s)'].str.replace('£---','')

# Cleaning Step 3: Coerce Price Per Unit to be a float
messy_data['Price Per Unit (£s)'] = messy_data['Price Per Unit (£s)'].astype(float)
```

Steps 1 and 2 use the [str.replace](https://pandas.pydata.org/docs/reference/api/pandas.Series.str.replace.html "str.replace Documentation") function. This finds a predefined set of characters within the string value of a Pandas data series and replaces them with another. In this case, we use the replace function to find the string we wish to remove, (either ‘,’ or ‘£---‘) and replace with a null string (‘’).

Cleansing Step 3 uses the [astype](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.astype.html "Astype Documentation") function to coerce the values to a float number type. The process of changing a variable type to a new one is called coercion. This can result in errors if Python does not know how to manage a value with the new data type. This example has been constructed to not have any coercion errors. However, you should check to confirm that this is the case when changing variable types.

For example, lets attempt to coerce the values in the Price Per Unit (£s) variable before carrying out cleaning steps 1 and 2. The first value in the data frame is:

<Image
	src={"/images/posts/supercharge-your-excel-data-cleaning-with-python/img2.png"}
	alt={
		"Example of a text string that looks like a numeric value, but contains nonnumeric characters, preventing coercion to a float."
	}
	width={1000}
	height={107}
	caption={"A text string that looks like a numeric value"}
/>

This string contains numeric vales (‘1054.63’) mixed with non-numeric characters (‘£---' and ‘,’). The existence of the latter in the string will prevent the coercion to a float value. Python does not know how to treat these as numbers and returns the following error as a result.

<Image
	src={"/images/posts/supercharge-your-excel-data-cleaning-with-python/img3.png"}
	alt={
		"Example of coercion error returned by Python when a string value can not be coerced to a float."
	}
	width={1000}
	height={58}
	caption={"Example of coercion error"}
/>

However, if these are removed and we apply the coercion to the string value ‘1054.63’, then Python can convert this to float.

### Step 3: Export Data from Python Back to Excel

Our final task is to export the data back to Excel. We do this with the [to_excel](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html "t0_excel Documentation") function.

```python
# Export data back to excel
messy_data.to_excel('clean_order_data.xlsx',index=False)
```

To ensure we do not overwrite the raw messy data, we will export this with a new filename, passed in a string to the function as the first variable. By setting the index variable to False, Pandas won't export the indices to the Excel file with the data.

We now have data that has been cleansed and can be used for analysis.

<Image
	src={"/images/posts/supercharge-your-excel-data-cleaning-with-python/img4.png"}
	alt={
		"Clean data in Excel, following the application of the cleaning steps required.  The Price per Unit variable is now numerical and can be analyzed."
	}
	width={1000}
	height={195}
	caption={"Clean data in Excel"}
/>

Analysis in Excel can quickly provide the requested information.

<Image
	src={"/images/posts/supercharge-your-excel-data-cleaning-with-python/img5.png"}
	alt={
		"Excel pivot table, quickly produced from clean data, showing volume and total price of units ordered by customer"
	}
	width={1000}
	height={175}
	caption={"Excel pivot table analysis"}
/>

## Next Steps: Techniques to Learn

While this is a (relatively) simple example, it shows us that we can carry out complicated cleaning techniques in Python with a few lines of code.

In the next article, we will start to explore these new tools and demonstrate how to carry out more complicated tasks such as:

1. Splitting one column of text values into separate columns based on a text delimiter.
2. Removing duplicate data from the spreadsheet to ensure unique values.
3. Combining variables and values from one or more columns with functions.
4. Looking up values from one data source to another.
5. Formatting and manipulating text format or values.



Supercharge Your Microsoft Excel Data Cleaning with Python

:::success
**📂 Resources**
Download the resources for this lesson <Link href="/resources/strategies-and-techniques-for-data-cleaning-in-excel-and-python/how-to-clean-data-in-excel-using-python-the-basics/Resources.zip" download="Resources.zip">here.</Link>
:::

## Why Use Python to Clean Data in Microsoft Excel?

In the last lesson, [Supercharge your Excel data cleaning with Python!](/course/strategies-and-techniques-for-data-cleaning-in-excel-and-python/supercharge-your-excel-data-cleaning-with-python), we explained how using Python to clean Excel data not only provides us with all the cleaning functionality of Excel and Power Query, but it also opens up a whole new set of useful data tools and features. While there is a learning curve for Python, the skills you learn and the knowledge you gain will future proof your career.

## Advanced Data Cleaning in Excel

We all start out manually cleaning data in Excel. In doing this, we become familiar with the features and formulas that enable us to complete tasks. These can be easily replicated in a few lines of code in Python.

Such features and functions include:

1. Splitting columns into multiple variables via text to columns.
2. Applying formulas to multiple variables in the data to create new ones.
3. Looking up values from other data sources via VLOOKUP.
4. Removing duplicate data values.
5. Formatting text strings to show the correct capitalization.

In this article, we will be demonstrating the Python equivalents of these and applying them in an example.

## Example: Cleaning Messy Order Data

Let’s expand on the example from the previous article. The orders database contains more information than previously analysed.
The database also contains:

1. The name of the client contact placing the order.
2. The address of the client

In a separate Company Details table, we also have:

3. The customer names (and not just a customer number)
4. The sales representative for each customer.

### The Requested Analysis

The increased availability of data means that we can produce more useful data analytics for the business, and they have expanded their ask as a result.

We have been asked to:

1. Provide a breakdown of total order numbers (volume and value) by:

   - Customer Name (Not number)
   - Sales Rep Name
   - Country

2. Isolate the name of the person raising the order and each individual element of the address as separate variables so that shipping labels can be printed.

### Messy Data Extracted from Order System

We experience the same issue as before. When we extract the data from the order system and copy it into Excel it is messy!

#### Example Of Messy Order Data in Excel:

<Image
	src={
		"/images/posts/how-to-clean-data-in-excel-using-python-the-basics/img1.png"
	}
	alt={"Example of the messy order data copied from the system into Excel."}
	width={1000}
	height={153}
	caption={"Messy order data"}
/>
#### Example Of Messy Customer Details in Excel:

<Image
	src={
		"/images/posts/how-to-clean-data-in-excel-using-python-the-basics/img2.png"
	}
	alt={"Example of the customer details data copied into Excel."}
	width={650}
	height={163}
	caption={"Customer details"}
/>

_(NOTE: This is fabricated data, created using the Faker module, any similarity to existing people or entities is coincidental)_

Datafiles and supporting code and notebooks can be in the [tutorial resources panel](https://github.com/pod-8/messy_data_order_addresses)

#### Why Is This Messy?

This data is messy as we cannot complete the requested tasks without carrying out cleaning activities. Using the Tidy Data Principles as a guide, we can inspect the data and identify the issues we need to clean.

##### Tidy Data Principal 1: Each Value is a Cell

We have the same issues as before, the Price Per Unit variable is not a clean numeric value, limiting the production of the requested analysis.

##### Tidy Data Principal 2: Each Variable is a Column

For the analysis requested, we have multiple variables within a column. As we have been asked to split the address into individual elements and provide analysis by country, we will need to split this column into individual address element variables.

We also need to generate new variables for the data. The name of the person raising the order is something we can parse from the Order Email and the Total Order Value is something we can calculate from the product of the Units Ordered and the Price Per Unit. As variables, these will need their own columns.

##### Tidy Data Principal 3: Each Observation is a Row

Tidy data requires rows to be single observational units. In the Order Data, a row corresponds to an order, and in the Customer Details, a customer. We will need to merge the data into a single table where an order is the observational unit, and the properties of the customer are a variable for this observation.

### Data Cleaning Approach

Having identified the issues to resolve, we can devise a plan for cleaning the data. This is as follows:

1. Implement data cleaning from the last article to ensure Price Per Unit is a numeric value.
2. Apply a formula to calculate the Total Order Value per order.
3. Isolate the individual elements of the address and store them as separate variables.
4. Extract the name of the ordering individual from the order email and copy it into a new variable.
5. Correct the capitalization of the text values extracted.
6. Check for duplicates and remove any that are found.
7. Look up the company name and sales representative from the Company Details table.

Step 6 is not something that may be obvious from our initial investigations. As we work through this exercise, we will explain why it is beneficial to check for duplicates prior to looking up values in Python.

## Step 1: Import Data & Cleaning Price Per Unit Value

See the [previous lesson](/course/strategies-and-techniques-for-data-cleaning-in-excel-and-python/supercharge-your-excel-data-cleaning-with-python)for an overview of importing data and how to find and replace data values.

## Step 2: Calculating the Total Order Value

This transformation requires us to produce a new variable by applying a formula to two existing ones.

Pandas provides us with a simple way to apply formulas to data series that have the same shape (in Excel speak, columns of data that have the same number of rows populated). We can apply the mathematical operations we want directly to the columns. As the number of elements in each series is the same, Pandas knows to apply the function to corresponding values of the same index.

<Image
	src={
		"/images/posts/how-to-clean-data-in-excel-using-python-the-basics/img3.png"
	}
	alt={
		"Image showing the impact of multiplying two arrays (Series) of the same shape in Pandas and how the result is the product of the two corresponding elements."
	}
	width={1000}
	height={363}
	caption={"Broadcasting in Pandas"}
/>

Pandas does not always behave like this. If the arrays (another way to refer to Pandas data series) that are being multiplied are of differing shapes, then Pandas will adjust how the calculation is performed.

The process of determining how Pandas treats arrays of differing shapes and sizes when mathematical operations are applied is called broadcasting. How Pandas broadcasts in differing circumstances and why it does this are above the scope of this article. Further details can be [found here](https://numpy.org/doc/stable/user/basics.broadcasting.html).

This means we can calculate the total order value per line by implementing the following code:

```python
# Calculate Total Order Value
messy_data['Total Order Value (£s)'] = messy_data['Units Ordered'] *  messy_data['Price Per Unit (£s)']
```

Reviewing the output of this we can see this has carried out the expected calculations.

<Image
	src={
		"/images/posts/how-to-clean-data-in-excel-using-python-the-basics/img4.png"
	}
	alt={
		"Python output showing the result of multiplying the Units Ordered and Price Per Units (£s) variable to yield the Total Order Value (£s)"
	}
	width={650}
	height={241}
	caption={"Python output"}
/>

## Step 3: Splitting Client Address Column into Multiple Variables

The text to columns wizard is a very useful feature in Excel. It allows us to separate a single column of text values into multiple ones, based on a delimiter (often a comma) signifying a separation. The Pandas function [str.split](https://pandas.pydata.org/docs/reference/api/pandas.Series.str.split.html "str.split Documentation") carries out the same transformation in Python. We can use this here to separate the address into separate variables.

When you inspect the imported data in Python, you will notice that it differs from the data in Excel.

### Imported Client Addresses in Python

<Image
	src={
		"/images/posts/how-to-clean-data-in-excel-using-python-the-basics/img5a.png"
	}
	alt={
		"Example of raw data imported to Python.  We can see how the client Address is a single text string where each element is separated by a ‘\n’ nonprintable character."
	}
	width={650}
	height={214}
	caption={"Raw data imported to Python"}
/>

### Client Addresses in Excel

<Image
	src={
		"/images/posts/how-to-clean-data-in-excel-using-python-the-basics/img6.png"
	}
	alt={
		"Example of raw data in excel, demonstrating how the ‘\n’ character is either not displayed (if the cell has not been selected) or causes the data to split on to individual lines."
	}
	width={650}
	height={259}
	caption={"Raw data in Excel"}
/>

The data in Excel either appears on one line (see cell F2) or, if the cell has been selected at any point, on individual lines (see cell F3). In Python, it appears as a single string with ‘\n’ between each element.

This is an example of a non-printable character and in Excel (and other text interpreters) a ‘\n’ signifies a new line. This is why the data splits into multiple lines when we select the individual cells. As it is nonprintable, we would not see it in the cells either before or after selection.

In some cases, we may want to remove such characters, however, in this situation, we can use it as a delimiter to split the cell into multiple columns.

Noting that the data is in the same format for all values, (Street / Box Number / Country / Mail Code), the following code will split the data and return individual variables for each element.

```python
# Define a list of new variable names for the seperated data from the Client Address
address_details = ['Street','Box','County','Country','Mail Code']

# Split Client Data in to seperate columns and assign to the columns named in the list address_details
messy_data[address_details] = messy_data['Client Address'].str.split('\n',expand=True)

# Remove the original Client Address variable from the data
messy_data = messy_data.drop(['Client Address'],axis=1)
```

This creates four new variables in the messy_data dataframe (with the names set out in the address_details list) and extracts them from the ‘Client Address’ variable by applying the [str.split](https://pandas.pydata.org/docs/reference/api/pandas.Series.str.split.html "str.split Documentation") function outlined above. The setting of the variable expand=True ensures that the extracted variables are returned in their own columns.

As we have duplicated the data from the Client Address in the new individual variables, we can now drop the original data from the messy_data dataframe. The last line of code does this for us and the variable axis = 1, tells the drop function we are looking to drop a column.

## Step 4: Extract the Name of The Ordering Individual

Using ‘@’ as a delimiter, we can adapt the technique applied in step 3 to extract an order name from the email address.

We will need to isolate the first column only (i.e. that containing the name.surname), and then replace the ‘.’ With a space so that we have a correctly formatted name.

In code, this looks like this:

```python
# Extract the first name and last name from the Order Email and add to messy_data
messy_data['Order Name'] = messy_data['Order Email'].str.split('@',expand=True)[0].str.replace('.',' ')
```

The [0] in the middle of the code, limits the first application of the split function to return just the zeroth indexed column (That is the first column of name.surname, remember python indexes from zero).

## Step 5: Correcting the Capitalization of Text

Having completed the previous steps, we can see that there are two variables that are fully capitalized.

<Image
	src={
		"/images/posts/how-to-clean-data-in-excel-using-python-the-basics/img7.png"
	}
	alt={
		"Python output showing how the Order Name and Country variables contain fully capitalised values."
	}
	width={400}
	height={274}
	caption={"Fully capitalised values"}
/>

As they contain proper nouns, we want to capitalize the first letter of them only. This is something that in Excel can be completed with the use PROPER functions.

Again we see that Python and Pandas have an equivalent function, the [str.title](https://pandas.pydata.org/docs/reference/api/pandas.Series.str.title.html "str.title Documentation") function.

This can be implemented using the following code:

```python
# Update the Country variable to be a title case (i.e. capitalise the first letter of each word)
messy_data['Country'] = messy_data['Country'].str.title()

# Update the Order Name variable to be a title case
messy_data['Order Name'] = messy_data['Order Name'].str.title()
```

## Step 6: Identifying and Removing Duplicate Rows of Data

The final task we wish to complete (step 7) involves looking up the values contained within the companies’ data file.

If we import this into Python as company_data and view it, we can very quickly see that there are duplicate values in this data set.

<Image
	src={
		"/images/posts/how-to-clean-data-in-excel-using-python-the-basics/img8.png"
	}
	alt={
		"Python output showing the shape of the customer_data dataframe as (5,3) and showing duplicate entries in rows 3 and 4."
	}
	width={650}
	height={298}
	caption={"Customer_data shape and duplicate row"}
/>

A wizard in Excel allows duplicates to be identified and removed very efficiently. It is also worth noting that duplicates in an Excel VLOOKUP call are not necessarily an issue as it will return the first value it finds. However, when implementing the equivalent functionality in Python and Pandas, it can introduce a problem, it can create duplicates (something we will explain in Step 7).

The Pandas function [drop_duplicates](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.drop_duplicates.html "drop_duplicates Documentation") provides a quick way to remove these from our dataframe. The function is quite versatile and can be applied in many ways.

```python
# Remove duplicate rows from the company_data dataframe
company_data = company_data.drop_duplicates()
```

By default, [drop_duplicates](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.drop_duplicates.html "drop_duplicates Documentation") will keep the first instance of a duplicate it finds and drop the rest (Hence row 3 being kept in the data below)

<Image
	src={
		"/images/posts/how-to-clean-data-in-excel-using-python-the-basics/img9.png"
	}
	alt={
		"Python output showing the shape of the customer_data dataframe as (4,3) following the application of the drop_duplicates function and that the retained row of duplicate data is the first found (row 3)"
	}
	width={650}
	height={259}
	caption={"Customer_data shape post removal of duplicate row"}
/>

## Step 7: Looking Up Values

The process of looking values up in Excel is carried out with a VLOOKUP or an INDEX and MATCH formula.

Pandas provides similar functionality for Python via the [merge](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html "Merge Documentation") function. The behaviour of this function is based on a database join between two tables (the ‘Left table and the ‘Right table’) and in specific circumstances, will behave in the same way as a VLOOKUP.

Understanding how a database joins data allows us to change the behavior of the merge function. An overview of this can be [found here](https://dataschool.com/how-to-teach-people-sql/sql-join-types-explained-visually/). By default, the merge function applies an inner join.

In our example, we have our left table (the messy_data) and we want to look up the values from the right table (the company_data) via a match in values of the Customer ID (the ON value for the join)

If we have a many-to-one relationship between the left and right tables (that is many instances of the look-up ‘on’ variable values in the left table, but only one instance in the right table values) then the default merge will behave in the same way as a VLOOKUP.

If there are duplicates in the right table, it will permute and return all possible permutations of lookups. This will introduce duplicates into the resulting dataframe, something that depending on the ask may or may not be desirable. We need to be careful with the application of the merge function and check the shape of the dataframes both before and after.

Having identified and removed the duplicates from the company_data dataframe in step 6 (the right table in the join), duplicates are not to be expected. We will however verify this in the code.

The code to implement the desired look-up and checks are as follows:

```python
# Print the shape of the messy_data dataframe to the screen prior to merge
print('Shape of messy_data prior to merge:', messy_data.shape)

# Merge data sets and lookup values in the messy_data and customer_data via an Inner Join on the 'Customer' variable
clean_data = messy_data.merge(company_data, on = 'Customer')

# Print the shape of the meesy_data dataframe to the screen prior to merge
print('Shape of clean_data post merge:', clean_data.shape)
```

Reviewing the shape of the dataframes before and after the merge indicates that the only change is the addition of two new columns.

<Image
	src={
		"/images/posts/how-to-clean-data-in-excel-using-python-the-basics/img10.png"
	}
	alt={
		"Python output showing the shape of the messy_data as (50,12) prior to the look up of the two columns with the merge function and (50,14) afterwards."
	}
	width={650}
	height={78}
	caption={"Shape of messy_data pre and post merge"}
/>

As this is our last cleaning step, the code assigns the result to a new variable, clean_data, and exports it to Excel.

We are now able to complete the requested analysis. An example of this is provided in the supporting code folder for this article.


How to Clean Data in Excel Using Python: The Basics

Extracting Values From Free Text: Introduction to REGEX

Manipulating and working with Pandas dataframes

:::success
**📂 Resources**
Download the resources for this lesson <Link href="/resources/strategies-and-techniques-for-data-cleaning-in-excel-and-python/managing-missing-values-part-1/Resources.zip" download="Resources.zip">here.</Link>
:::

## The Problem With Missing Data

It is estimated that when you work with data, 80% of your time is spent preparing the data for analysis. We have been exploring how to make this process simpler when analysing data in Excel by using various cleaning techniques implemented in Python.

One area we have not explored yet is missing data. Missing data could be a course in its own right! It is a vast subject with many different techniques to use in differing situations. Over the next two lessons, we will take our first steps in exploring this vast subject.

Missing data can occur in many ways. We may not capture a variable that is critical for the analysis. We may not have observations from an adequate proportion of the population we are investigating. Or we may be missing specific values for variables and observations, something we saw in the last lesson, where values were lost during the import process.

Missing data and the causes of missing data can be particularly problematic, and we need to have tools, techniques, and strategies for dealing with the issue when found.

For this introductory session, we will consider the situation where we have missing values only. We will assume that we have all the variables and observations required to complete the analysis.

## Identifying Missing Data

When we work in Excel only, there are many ways to spot missing data. The first of these is quite simple, we may visibly spot gaps in the data. This however is limited as it will not flag values which are subtly inputted or formatted incorrectly. It is impractical when we have a large dataset. We may also see that formulas or analyses fail in the event of missing data, but again this is unreliable as a lot of formulas work around this issue.

In short, we need a more practical way to spot missing values across large data sets.
Again, Python provides us with a practical solution here.

Pandas uses special values (either NaN or NA) to indicate where a value is missing. When we import data from Excel, if a cell is null, it will be assigned to one of these values. If an error occurs when we try to coerce data into the correct data type, then we can set Python to return such a value.

This means, that if we import data into Python and ensure that each series has the correct data type, then we can spot missing data by analysing the NaN or NA values.
The `.info()` method provides us with an easy way to do this in Python.

### An Example: Fruit and Veg Prices over Time

For the rest of this lesson, we will use the [Fruit and Vegetable Wholesale Prices](https://www.data.gov.uk/dataset/c774f8b9-a606-4480-8cb8-ff6839d4b362/fruit-and-vegetable-wholesale-prices) data file supplied by DEFRA under the open government licence. This has been modified to ensure we have missing values of interest. The data for analysis along with the code used here can be found in the supporting material for the lesson.

This data file provides us with weekly wholesale prices and typical weights for five fruit and veg items. We have a data point for each week in 2023 from the 13th January to the 22nd September (37 weeks of data for each fruit)

We will assume a simple analysis request. We have been asked to trend prices over time for the fruits and provide a mean weight for each based on the 2023 data.

### Inspecting The Data & Finding Missing Values

Using the code below, we can import the data and inspect the resulting dataframe using the `.info()` method.

```python
# Import required packages
import Pandas as pd

# Import fruit and veg price data and assign to prices
prices = pd.read_Excel('2023 Fruit and Veg Prices.xlsx')

# Display prices information
prices.info()
```

The output of this code is displayed in figure 1.

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/managing-missing-values-part-1/img1.png"
	}
	alt={
		"Notebook output of prices information extracted with the .info() method.  For each column in the dataframe, this provides a non-null entry count and the data type."
	}
	width={500}
	height={363}
	caption={
		"Figure 1. Notebook output of prices information extracted with the .info() method.  For each column in the dataframe, this provides a non-null entry count and the data type."
	}
/>

The difference between the 185 entries and the Non-Null count shows us where we have missing values. shows us that we are missing 7 sample weight values and 38 price points.

## What To Do About Missing Data?

The analysis in figure 1 shows us that we have missing data. But what do we do about it?

The first step is always to understand what is missing and if possible, understand why. This information will then help us make an informed decision about how to progress.

Using the techniques presented in the last lesson we can quickly filter the data to show the observations where values are missing.

First, let’s consider the missing sample weight values. The `.isna()` function is a useful tool in Pandas. It analyses a value and returns a Boolean value of True if it is `NaN` or `Na`. We can apply this across a series and use the resulting Boolean mask in a filter. The following code does this.

```python
# Identify the average weight values with no values
null_weight_filter = pd.isna(prices['sample weight'])

# Display records in notebook
prices[null_weight_filter]
```

This yields the results in figure 2:

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/managing-missing-values-part-1/img2.png"
	}
	alt={
		"Notebook output of dataframe showing the seven observations with missing sample weights."
	}
	width={1000}
	height={299}
	caption={
		"Figure 2. Notebook output of dataframe showing the seven observations with missing sample weights."
	}
/>

From this dataframe, we can see that we are missing a single weight for the apples, savoy cabbage and curly kale, and two weights beetroot and red cabbage. Out of 185 observations (37 for each observation variant), this is a low volume to be missing. There is no clear pattern to the missing data.

The following code will allow us to investigate the missing price observations.

```python
# Identify the average weight values with no values
null_price_filter = pd.isna(prices['price'])

# Display records in notebook
prices[null_price_filter]
```

Running this code shows us that we are missing 38 values. One for apples and all 37 for savoy cabbage. We have not included the image of this analysis due to the size of the output. It is available in the notebook contained within the supporting lesson materials.

At this stage, it is useful to ask why. Why is this data missing? This is a useful question as the answer again may help us understand how to proceed. For example, we may identify an issue with data capture and can prevent future issues at source, or we may be able to identify the specific values that are missing.

For this example, we will assume that the data was not recorded and is not available to us. We now need to decide how to progress.

### What Options Do We Have for Managing Missing Data?

We have identified the missing data and we have also identified some insight to help us decide how to progress. We now have four options:

1. Do nothing
2. Update the values
3. Remove the datapoint
4. Stop the analysis

As the processor of the data, it is down to us to decide which approach is the most appropriate to apply. This is heavily dependent on the data and the situation. Whatever course of action you choose, remember, it could introduce bias or inaccuracies into your work and you may need to justify your assumptions and action at a later stage.

#### Do Nothing

This is the simplest response to missing data. It requires no action or no code to implement, we continue with the data as it is.

This would be an advisable approach if we are confident that the missing value will have limited impact on the analysis requested.

#### Update The Values

If doing nothing is not an option, then updating the data is the next step to consider. This is the most involved of the options and careful consideration needs to be taken.

During our preliminary investigations, we may have gotten lucky and have identified the actual values that are missing, if this is the case, then we can update these in the data set.

We may also be able to make an inference about the missing values and estimate (or guesstimate) and inferred value. This could involve updating to a chosen fixed value; a calculated statistic such as a population mean; or another sensible method of choosing a value (such as repeating the last available data point). There are also many other techniques that can be implemented here.

If we choose to take this path, then we need to ensure that we do not unnecessarily bias the subsequent analysis and can justify our assumptions.

#### Remove The Data Point.

If ignoring the data point is not an option and we are not able to update the values, then we are left with the removal option.

This can be dangerous, as again it may skew the results and understanding why data is missing is vitally important. We will also need to decide if we need to make the consumers of the analysis aware of the removal of the data as it may impact their interpretation.

It is also important to understand the ask as the removal of certain data may prevent the analysis from being completed.

#### Stop the Analysis

If we are not able to implement one of the three steps above safely, and we cannot complete the analysis without the missing data, then we are left with no option other than to stop the analysis.

If we find ourselves in this situation, we must revisit how the data is captured and determine if capturing the missing data is possible.

## Deciding The Best Way Forwards

Let’s consider this in the example above. We have three differing missing data points to deal with and therefore have three decisions to make:

1. How to manage the missing weights
2. How to manage the missing apple price
3. How to manage the price missing against all savoy cabbage observations

### How to Manage Missing Weights

When considering this, we need to remember that we have been asked to calculate the average weights for each fruit or vegetable.

The [Law of Large Numbers](https://www.sciencedirect.com/topics/mathematics/laws-of-large-number) states that as the sample size increases, the sample mean approaches the population mean. In short, one or two data points from 38 is unlikely to have a dramatic impact on the mean value.

As a result, we can safely do nothing and be confident that the missing data will not be of tangible impact to the analysis. As the number of missing values increases, we can have less confidence in such a statement.

### How To Manage the Missing Apple Price

We are missing a single price point for the apples. We have been tasked with producing a trend analysis for the prices.

Ignoring the missing value would result in an obvious point of discontinuity in the trend line, possibly inviting unnecessary queries. Replacing the value with the prior week’s price point will enable us to complete the analysis and prevent such a point of discontinuity.

We would need to state such an action in the end analysis to ensure people are aware it has been implemented.

### How To Manage the Missing Savoy Cabbage Price

As we are missing all price points for the savoy cabbage data, we have no data to infer the missing values from. In this situation, we will remove the savoy cabbage data and complete the analysis with the remaining data. We would have to follow up on such an issue and indicate in the final analysis that the data has been omitted.

## Implementing The Planned Updates

These updates are straightforward and are easy to implement in Python

### Updating the Missing Apple Price Point

We can see from the inspection of the data (see the missing price point dataframe in the supporting notebook) that observation with missing data is in index 2 and the prior value in index 1. This means that the following code will quickly update the missing data.

```python
# Update price in index 2 to that of index 1
prices.loc[2,'price'] = prices.loc[1,'price']
```

By inspecting this value post update we can check that it has successfully implemented the update.

### Removing Remaining Missing Price Points

Pandas has a built-in function called `dropna()`. As you would expect, this drops observations containing `NaN` from a data frame. By default, this will drop observations where a `NaN` is found in any variable. We can adjust the behaviour by providing different settings to the input of the function. By limiting the function to the price variable, we can quickly carry out the update. The following code implements this.

```python
# Drop the remaining NAN values from the price variable
prices = prices.dropna(subset='price')
```

By re-inspecting the information for the process dataframe, we can see that this has been successfully implemented.

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/managing-missing-values-part-1/img3.png"
	}
	alt={
		"Notebook output of prices information following the removal of the savoy cabbage observations."
	}
	width={500}
	height={369}
	caption={
		"Figure 3. Notebook output of prices information following the removal of the savoy cabbage observations."
	}
/>

Figure 3 shows us that we now have 148 entries and that all price values are non-null.

## Conclusion, Next Steps & Exercises

Missing data will be a regular issue for anyone who works with data. It is not always practical to spot missing data in Excel and Python not only provides simpler ways to inspect large data sets, but it is also easier to update and manage missing data in Python. Once identified, there are only a handful of high-level options for managing missing data and it is the analyst’s responsibility to decide the best way forward. Whenever making updates to data, you will need to ensure that you caveat any analysis to make users aware and are able to justify and explain the decisions you have made.

An exercise set to reinforce the learning above can be found in the supporting lesson materials. We will continue exploring methods for managing missing data in the next lesson.


Managing Missing Values Part 1

:::success
**📂 Resources**
Download the resources for this lesson <Link href="/resources/strategies-and-techniques-for-data-cleaning-in-excel-and-python/managing-missing-values-part-2/Resources.zip" download="Resources.zip">here.</Link>
:::

## Recap: What Have We Covered So Far?

The previous lesson introduced some basic techniques for identifying and managing missing values. In this lesson we will build on this and look at some more complicated techniques for updating missing data.

But before we get on to that, we are now able to explore an important feature of Pandas.

## Copies and Views of dataframes

One of the techniques we have used for managing missing values is updating. This means we implement code to overwrite the missing values with new ones in the data frame. This exposes us to an important feature within Pandas, that of views and copies. This is best explained with an example.

The following code builds a simple dataframe and then takes two separate subsets of it using techniques discussed in the previous lesson.

```python
# Define example data to use
example_data = {'Label':['A','B','C','D','E','F'],'Value':[1,2,3,4,5,6]}

#Generate example dataframe
example_df = pd.dataframe(example_data)

# Define two subsets of the data, using different sub setting techniques
example_subset_1 = example_df.iloc[0:1,:]
example_subset_2 = example_df.loc[example_df['Label'] == 'D',:]
```

This yields the dataframes displayed in figure 1

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/managing-missing-values-part-2/img1.png"
	}
	alt={
		"Notebook output of the main dataframe and the two subsets taken using differing techniques."
	}
	width={300}
	height={694}
	caption={
		"Figure 1. Notebook output of the main dataframe and the two subsets taken using differing techniques."
	}
/>

Using the following code, we can make updates to the value in the first row and second column in both.

```python
# Update values in first row and second column of both subsets
example_subset_1.iloc[0,1] = 1000
example_subset_2.iloc[0,1] = 1000
```

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/managing-missing-values-part-2/img2.png"
	}
	alt={
		"Notebook output following the updates made to the two subsets.  We can see the updates actioned in the two subsets and one (that made to subset 1) has been reflected in the main dataframe."
	}
	width={300}
	height={713}
	caption={
		"Figure 2. Notebook output following the updates made to the two subsets.  We can see the updates actioned in the two subsets and one (that made to subset 1) has been reflected in the main dataframe."
	}
/>

Re-inspecting the output (figure 2), we can see that the updates have been actioned in both subsets, but one has also carried through to the original dataframe. Why is this?

Well, one update (that applied to `example_subset_2`) is actioned upon a copy of the data frame and the other on a view. Conceptually, when a filter of the original data produces a view, we are effectively viewing the original data through a different window. This means that any updates made to the data, will be reflected in the original.

Conversely, if a filtering operation produces a copy, then we are working on a replication of the original data and any updates will only be made in the copy. Depending on your task, both behaviours can be desirable and undesirable. Our task here involves updating values and we will need to ensure that we are working on a view of the data as a result.

Generally, whenever an array or list of labels or a boolean vector are involved in the indexing operation, the result will be a copy. With single indexing and slicing views will be returned. See the Lesson Notebook for further examples

Generally, views will be returned by:

1. Taking Slices
2. Single Label Indexing (for `[]`, `.loc[... , ...]` and .`.iloc[... , ...]`)
3. Indexing with a Boolean Array

And copies will be returned by:

1. Indexing with a Boolean Expression
2. Indexing with a list (for `[]`, `.loc[... , ...]` and .`.iloc[... , ...]`)
3. Forcing a copy with the .copy() method

Examples of these can be found in the supporting lesson materials

## Updating To a Population Average

Let’s revisit the data from the last lesson. If you recall, we decided not to update the missing weight values. Another course of action would have been to update the values to the sample population.

In this example, this is not a totally straightforward calculation. This is because each weight value is reflective of an item and variety of fruit or vegetables. As a result, we can’t just calculate the mean over the whole data set and use this. We will need to calculate a mean for each individual variety and then use this to patch in the respective missing values.

Let’s start by importing the data from Excel and carrying out the same updates as the last lesson. We won’t cover the code for this here, however it is available in the Notebook in the supporting lesson material.

First, we need to calculate the mean averages for each item. The following line of code does this in Python.

```python
# Calculate the mean sample weight by item
mean_weights = prices.groupby(['item'])['sample weight'].mean()
```

This yields the data displayed in figure 3.

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/managing-missing-values-part-2/img3.png"
	}
	alt={"Pandas series containing mean sample weights by item."}
	width={500}
	height={210}
	caption={"Figure 3. Pandas series containing mean sample weights by item."}
/>

Having calculated these values, we now need to map them on to the missing values based on the item for each respective observation.

We do this in three steps:

First, we create a dictionary, mapping the item to the value.
We apply a lambda function to this view that looks up the update weight for each observation based on the item. This results in an array containing the updated values for each observation missing data (importantly, in the same order).

We overwrite the sample weight variable in the view filtering for the NaN values with the array of data created in step 2.

**NOTE**: The array created in step 2 will be of the same length and have observations in the same order as those the view of missing values. This allows us to assign the array directly to the prices variable in the view in step 3.

The following code does this and further explanations of how this code works can be found in the lesson video.

```python
#STEP1: create a dictionary providing a key value mapping from item to average weight
mean_weight_dict = dict(zip(mean_weights.index,mean_weights.values))

#STEP2: Apply lambda function to look up new values via the step 1 dictionary
update_values = prices[null_weight_filter].apply(lambda x: mean_weight_dict[x['item']], axis=1)

#STEP 3: Assign the update values to the to NaN filtered view
prices.loc[null_weight_filter,'sample weight'] = update_values.values
```

As we are updating a view of the data, this update carries back to the original data. We can confirm this by reinspecting the data for indices with missing values, see figure 4.

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/managing-missing-values-part-2/img4.png"
	}
	alt={
		"Notebook output of the indices within the prices dataframe that originally had missing sample weight values, confirming update success."
	}
	width={1000}
	height={260}
	caption={
		"Figure 4. Notebook output of the indices within the prices dataframe that originally had missing sample weight values, confirming update success."
	}
/>

## Pandas GroupBy Method

The code above introduces the pandas `.groupby()` method. This, along with its aggregate functions, is a powerful tool that allows vast data sets to be easily summarised (in a similar way to a pivot table in Excel).

We will not explore this in detail here as our focus for this course is cleaning data. However, there are two things to note about this method at this stage:

1. If you would like to learn more about this method, then the [Pandas Group By Documentation](https://Pandas.pydata.org/docs/reference/groupby.html) provides details for the implantation.
2. The `'groupby()` method has several other aggregation functions, such as `SUM()`, `COUNT()`, `MEDIAN()`, `MIN()`, `MAX()` and `MODE()`. This allows us to easily update missing values to other statistics should we choose to do so

## Case Study: England Cricket Cap 696

Sports generates a vast wealth of data. In any sport, coaches, commentators, and spectators churn over reams of data, looking for insight that can help them see things in new ways or can give them a competitive edge. The on-plate average in baseball, the assists of a player in football (or Soccer for the global audience) or the batting and bowling averages of cricket players are just a few of numerous examples.

Cricket is a quintessentially English game and can be confusing to people at first. For centuries, it has produced data. Vast amounts of data. Games played, runs scored, overs and balls bowled, batting averages, bowling averages…the list goes on. It is buried in this vast archive, in the performance of England Test Match Players (or capped players) that we find an interesting lesson in missing data.

Wikipedia provides us with our best source of data to see this and provides a table of all main statistics for every England Player. From #1 Tom Armitage in 1877, to #711 Josh Tongue in 2023 (Correct at time of writing).

Some simple code (which will be explained in a future lesson) extracts this data into Pandas and by searching for missing values, we see quite a few. The nuance of cricket means that not everyone will have every statistic, all players bat (some better than others), some also bowl as well.

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/managing-missing-values-part-2/img5.png"
	}
	alt={
		"Subset of data from the list of capped England players following import into Python.  We see legitimate missing values in this selection."
	}
	width={1000}
	height={199}
	caption={
		"Figure 5. Subset of data from the list of capped England players following import into Python.  We see legitimate missing values in this selection."
	}
/>

There are a couple of points to note regarding figure 5:

1. The columns presented are a subset of the total available in the data set. This is to aid presentation in the lesson text.
2. We may expect some of the values to be integers. (such as _'Runs Batting'_, or _'Balls Bowling'_). NaN values are a special case of the float data type and as a result, it is not possible to store an integer data type with a missing value.

There is however, one player, Alan Jones #696 who has no data whatsoever. How can this be?

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/managing-missing-values-part-2/img6.png"
	}
	alt={
		"Subset of the Capped England Players data showing Alan Jones (#696) has all his playing statistics missing."
	}
	width={1000}
	height={166}
	caption={
		"Subset of the Capped England Players data showing Alan Jones (#696) has all his playing statistics missing."
	}
/>

With our data hat on, we have noticed that there are missing values, and we need to decide how to progress. The first step in deciding how to progress is to investigate why data is missing.

Alan Jones Played a single match for England against a combined ‘Rest of the world side’ in 1970. The original match had been scheduled against South Africa, however, as a stand against apartheid, the match and tour were cancelled. (The international Cricket Council - ICC introduced a moratorium on South African Tours in 1970). The match went ahead, Alan Jones played, but the ICC revoked the test match status. As a result, Alan Jones, who never played for England again, was never recognised as an England Player. He never received his England Cap.

In June 2020, 50 years after the match, the England and Wales Cricket Board (ECB), moved to right what they saw as a great injustice. They recognised him as a capped player and awarded him cap #696, the next available number in the long list of players. While the record books do not show Alan as a capped player, the history of English players does.

As a result of this, he has a legitimate place in the list (#696), but no statistics. Or to put this another way, the data has an observation for him, but all values are missing.

How should we manage this missing data. Well, that depends on what we have been asked to do with it. Without such an ask, all options remain on the table. However, what this little digression into the world of sport tells us, is that missing values can hide important information and stories.

When dealing with data in the wild, always investigate missing values, you may miss something important.

## Conclusion, Next Steps & Exercises

Missing data is one of the numerous issues we will face when cleaning data. How to manage missing data is a vast subject, and we have only touched the surface here. Over the last two lessons, we have seen differing examples of how we can manage missing data and implement the corrective actions in Python code. Several of these techniques are not easily implementable in Excel and provide us with another argument to reinforce why cleaning data in Python is advantageous.

An exercise set for this lesson is included in the lesson support materials.


Managing Missing Values Part 2

:::success
**📂 Resources**
Download the resources for this lesson <Link href="/resources/strategies-and-techniques-for-data-cleaning-in-excel-and-python/obtaining-data-part-1-working-with-files/Resources.zip" download="Resources.zip">here.</Link>
:::

## Introducing Separated Variable Files

Since the mid-80s, Microsoft Excel has been the workhorse of offices. It is rare to find a business that does not utilise this powerful tool somewhere for analysis.

However, the .xlsx (or.xls) file format is proprietary to Microsoft. While a lot of other software providers find ways of working with this data format, this is not always the case. There is a need to have a standard data format which can transfer data between programmes and systems which don’t operate with the same native file types.

This means that as part of the data cleaning process, we need to be familiar with other file types and be happy transforming them into a format that can be used for analysis.

### Comma Separated Variable Files (CSV Files)

One such format is the comma separated variable, or CSV file. This is a plain text file, where rows of data (aka observations) are entered on rows of the text file and the data for the columns are separated on that row by a delimiter, a comma in this case. An example of CSV data can be seen in figure 1.

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/obtaining-data-part-1-working-with-files/img1.png"
	}
	alt={
		"Example of a raw CSV file in notepad.  Rows of data are entered on rows in the text file and columns are separated (delimited) with a comma on each row of data.  In this example, we have a header of variable names in row 1."
	}
	width={400}
	height={243}
	caption={
		"Figure 1. Example of a raw CSV file in notepad.  Rows of data are entered on rows in the text file and columns are separated (delimited) with a comma on each row of data.  In this example, we have a header of variable names in row 1."
	}
/>

Such data files may or may not contain a row of headers and are commonly found when exporting data from SQL databases, or other similar structured stores of data.
Excel is very good at reading and loading single CSV files. In fact, you may have already worked with them and not realised. However, there are some limitations which can be fiddly to work around in Excel.

Such limitations include spaces and gaps in files; reading free text which may contain a comma within the value; and a CSV file containing more data points than can fit in Excel. We must also recognise that a CSV file, is a text file. As a result, we will need to load the data into a programme like Excel to perform any requested analysis.

### Other Separated Variables (TSV, Other Delimiters)

The delimiter in such a file is not limited to a comma and it can in theory be any value. Tab delimiters (i.e. cell data separated by a tab spacing), defined widths for cells or a user defined character such as ‘%>%’ are all plausible.

For simplicity, we will only consider CSV files here, but be aware that other data formats exist and can be processed using very similar techniques to those we will outline.

## Working With CSV Files in Python and Pandas

When working with a single, well formatted CSV file, Excel is more than capable of loading the file directly for analysis. However, data is rarely provided in such a state.

Python and Pandas provide us with a set of highly customisable tools for processing and cleansing CSV data. As we progress our data journey, we are more likely to find raw data in CSV formats and building a set of tools to process such data is a valuable addition to the data analyst’s armoury.

Once any cleansing steps have been completed, we can export the data back to Excel for analysis. We could also use many of the modelling and visualisation tools available in Python should we so wish.

### Importing Using Read_CSV

Pandas offers us an out of the box function to read CSV files. This is highly customisable and allows us to quickly import CSV data into Pandas and Python. In its simplest form, the function takes a file path and name and will import the data to a dataframe.

```python
# Import required packages
import Pandas as pd

# Read the photo plankton CSV data and assign to a dataframe
plankton_data = pd.read_csv('PhytoplanktonResults060423.csv')
```

The `pd.read_csv` function has several useful settings to be aware of.

The `skiprows=` and `skipfooter=` settings will allow us to ignore a defined number of rows at the start and end of a file respectively. These can be defined within the function variables when importing. These are useful if your CSV file has metadata such as a key in either of these places. (Something we can see in the supporting notebook for the photo plankton data and in figure 2)

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/obtaining-data-part-1-working-with-files/img2.png"
	}
	alt={
		"dataframe output of photo plankton data displayed in a notebook.  This shows that the key meta data has been imported in addition to the core data."
	}
	width={600}
	height={427}
	caption={
		"Figure 2. dataframe output of photo plankton data displayed in a notebook.  This shows that the key meta data has been imported in addition to the core data."
	}
/>

The revised code below will skip the key at the end of the data.

```python
# Read the photo plankton CSV data, skipping the last 8 rows
# NOTE: Engine = ‘Python’ required for skipfooter to function correctly
plankton_data_trimmed = pd.read_csv('PhytoplanktonResults060423.csv',
                                    skipfooter=8, engine='Python')

```

Inspecting the tail of the data (figure 3), we can see that the key has been ommited.

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/obtaining-data-part-1-working-with-files/img3.png"
	}
	alt={
		"dataframe tail output of the photo plankton data displayed in a notebook where skipfooter was set to 8 on import."
	}
	width={600}
	height={436}
	caption={
		"Figure 3. dataframe tail output of the photo plankton data displayed in a notebook where skipfooter was set to 8 on import."
	}
/>

The `header=` setting allows us to control the variable names in the Pandas dataframe. By default, `read_csv` infers the header and assumes they are the first row in the data. If our data does not contain a header, we can either provide a list of names to use or set `header=None`. The former will apply the given names and the latter will see default numbering used.

The final setting to draw your attention to is `quotechar=`. This is particularly useful when importing variables which contain free text. It is plausible, that a block of free text could legitimately contain a comma as a punctuation mark. Providing an input to this setting allows us to wrap all such data within the defined quotes (often a quotation mark) and ensure that any delimiters that occur between them are ignored. We will explore how to use these settings in the supporting notebook and the exercise set for the lesson.

We will explore how to use these settings in supporting notebook and the exercise set for the lesson.

There are many other settings and configurations for this function. Overviews of these can be found in the [pd.read_csv documentation](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html)

### Other Import Methods

There are other import methods and libraries which can be of use for importing text files (including CSV files).

One you will often see involves the _'With Open'_ method. This will allow a user to open a file and then process it line by line. It usually requires a few more lines of code but results in a faster import process which can implement specific cleaning tasks. The general format for the code is as follows:

```python
# Open specified file for reading ('r')
with open('FILE_NAME.csv' ,'r') as infile:
    for line in infile:
        """LINE BY LINE PROCESSING CODE HERE"""
```

We mention this method here as it is one you will see used on occasion. We will however remain focused on `pd.read_CSV()` for this lesson.

## Working With Multiple Files in Folders

We are now in a position where we can explore working with CSV files with an example. It is quite common to have a set of data distributed over multiple CSV files. To complete the analysis requested, we will often need to amalgamate the data into a single file.

Let’s consider a simple example. A manufacturing company runs three machines producing identical widgets. Every day a data file is produced that stores the number of widgets manufactured and the average operating temperature of the machine for every 15 minute period of the working day. They hypothesise that the efficiency of the machine is linked to the operating temperature and that there are temperature thresholds which if exceeded, cause the machine to shut down for the rest of the day. They would like to amalgamate the data and see if there is evidence to support this hypothesis.

The data from these machines is contained within the ‘machine_data’ folder in the supporting lesson materials.

### Introducing `pd.concat()`, A Useful Panda Function.

Core to this example, is the ability to append multiple files together to create a single data set for analysis. The Pandas function `pd.concat()` provides us with this capability.

We can do this either by concatenating (or appending) data on to existing columns (i.e. concatenate along axis 1) or by adding new columns of data to existing rows (concatenate along axis 0). This behaviour is demonstrated in figure X.

The behaviour of the function can be controlled by passing differing variables into the function call.

By default, the function will append data to rows and will not reset the indices of the dataframes. This may result in duplicate indices.

```python
# Define two example dataframes
df1 = pd.dataframe({'Label':['A','B','C'],'Volume':[10,20,30]})
df2 = pd.dataframe({'Label':['A','B','C'],'Volume':[1,2,3]})

# Default concat call on a list of data frames
pd.concat([df1,df2])
```

This yields the dataframe displayed in figure 4.

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/obtaining-data-part-1-working-with-files/img4.png"
	}
	alt={
		"The result of concatenating two simple dataframes using the default pd.concat settings.  Note the index values have been inherited from the respective dataframes."
	}
	width={200}
	height={276}
	caption={
		"Figure 4. The result of concatenating two simple dataframes using the default pd.concat settings.  Note the index values have been inherited from the respective dataframes."
	}
/>

Indices can easily be reset by setting `ignore_index=True`. This may however lose information. Another option would be to pass a set of keys to identify which dataframe the data is from. This results in a multi-index.

By setting the `axis=1`, we can append new columns of data on to each row. Pandas will align data by index and care needs to be taken to ensure that data aligns correctly. This process may introduce missing values.

A similar process occurs where there are mismatched columns. A full exploration of these settings and techniques can be found in the supporting notebook. For further details of the varying settings, see the [pd.concat() documentation](https://pandas.pydata.org/docs/reference/api/pandas.concat.html).

### The OS Package

To solve the problem posed in the example, we will also need to list all files contained within a set directory on a computer. The OS package provides this capability in Python. It enables Python to interact with the operating system and find out specific information and details about files and settings. For this lesson we will use a specific function within it.

The `os.listdir()` function will print all files within a directory. This will enable us to access a folder of data files and loop through them each in succession.

```python
# Import required packages
import os

# List all files in the directory
file_list = os.listdir('machine_data\\')
```

A sample of the output from this can be seen in figure 5.

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/obtaining-data-part-1-working-with-files/img5.png"
	}
	alt={
		"First three filenames found in the machine_data folder contained within a list."
	}
	width={900}
	height={45}
	caption={
		"Figure 5. First three filenames found in the machine_data folder contained within a list."
	}
/>

By default, Python will assume that the location of your script or notebook is the working directory and will take this as the root path when defining folders. You can overwrite this if you like with a full file path.

Full details of the OS package can be found in the [OS documentation](https://docs.Python.org/3/library/os.html).

### Missing Data - Variables in File Names

Inspecting the individual files in the machine_data folder, we can see that there is an issue. Each file has the time, temperature and units produced (figure 1), but We are missing the machine and the date. This is an essential data point for the requested analysis.

Looking in more detail at the data we have, we see that the missing data points can be extracted from the file name (figure 5). But how do we do this?

Each character in a Python string can be indexed by its place in the string. For example, in the string _‘abc’_, _‘a’_ is at the zeroth index, _‘b’_ at the first and _‘c’_ at the second.

This means that the same indexing and slicing methods we learnt in the last lesson can be used for strings and the following code will extract these data points for us.

```python
# Set example file name
file = '20230201_MACHINE_A.csv'

# Extract the machiene from the file name
machine = file[-5:-4]

# Extract the date from the file name (dd/mm/yyyy format)
date = file[6:8] + '\\' + file[4:6] + '\\' + file[:4]
```

These can then be added as variables in the imported dataframes.

### Looping Through and Importing Files

We now have all the tools we need to solve this problem. The following code will loop through each of the files; import them into Python; extract missing key data from the file name; build a dataframe containing all the key data; and then append it to a list for storage.

```python
# Initiate a list to hold each imported datafile
imported_files = []

# For each file in directory
for file in file_list:
    # Extract the machiene from the file name
    machine = file[-5:-4]

    # Extract the date from the file name (dd/mm/yyyy format)
    date = file[6:8] + '\\' + file[4:6] + '\\' + file[:4]
    data_file = pd.read_csv('machine_data\\' + file)

    # add date and machine to respective data file (Recall broadcasting!!!)
    data_file['date'] = date
    data_file['machine'] = machine

    # Append imported data with machine and data labels to list
    imported_files.append(data_file)
```

Once we have a list of all imported data, we can use the concat function and the following code to build the overall data set needed for the analysis and Export back to Excel.

```python
# Concatenate list of single dataframes in to a single one along axis = 0 (i.e. append rows)
machine_operating_data = pd.concat(imported_files, axis = 0, ignore_index = True)

# Export the data to Excel
machine_operating_data.to_Excel('machine_operating_data.xlsx', index=False)
```

### Analysis Output

The focus of this lesson is the process to amalgamate the data. An example of the analysis output has been included in the supporting lesson material and from this we can see that there is evidence to suggest that the machines will shut down above 80 degrees and below 70 degrees Celsius. There is also some evidence to suggest that the optimal operating temperature is between 70 and 75 degrees Celsius, with a higher widget production count when the machines operate in this temperature range.

## Conclusion, Next Steps & Exercises

This lesson has looked at varying ways we can use Python to amalgamate data distributed across multiple files and produce a single data set that can be used for analysis. We have also introduced CSV files, a file type that is commonly used to store data and information.

The tools and techniques outlined in this lesson can be adapted to several differing use cases and variants of this process can be explored in the supporting exercise set.

We have focused in on CSV files in this lesson and in situations where there are physical files stored to read. However, there are other sources of data that we may wish to access. In the next lesson, we will explore how to read lists from websites and read data from online APIs.


Obtaining Data Part 1: Working with Files

:::success
**📂 Resources**
Download the resources for this lesson <Link href="/resources/strategies-and-techniques-for-data-cleaning-in-excel-and-python/obtaining-data-part-2-working-with-web-data/Resources.zip" download="Resources.zip">here.</Link>
:::

## Extracting Files from The Web

In the lessons so far, we have seen numerous examples of files being loaded into Python and Pandas. These files have been either Excel or CSV files and have been stored locally on the PC we are working from.

Both `pd.read_excel()` and `pd.read_csv` are quite versatile. We can pass a file name, and it will look for the file in the working directory, we can pass a file location and it will load the file form the specified path, or we can provide it with a URL.

### Direct File Reads

Passing a URL to the function call may be a little counterintuitive, but when files are stored and made available for download on the web, we can pass this to the respective read function and the file will download.

The following code will download the full fruit and veg price data used in Lesson 6 directly from the web.

```python
# Define URL and file name to import
file_url = 'https://assets.publishing.service.gov.uk/media/65201892244f8e000d8e7194/'
file_name = 'fruitvegprices-10oct23.csv'

# Import fruit and veg prices from url
fruit_veg_prices = pd.read_csv(file_url + file_name)
```

This process also works for files stored on intranets and SharePoint sites as well as other repositories such as GitHub.

### Read_HTML Function

Occasionally we may also want to extract data from tables presented on websites. The code required to create a table on a website means that behind the scenes, the data is quite well structured, however it is not in an easy format to directly input into a table. Fortunately, Pandas has an inbuilt function `pd.read_html()` that will read tables from a website and return any it finds in a dataframe format. If more than one table is found, then the function will return a list of tables.

We have already seen an example which utilised this process. In [lesson 7](/course/strategies-and-techniques-for-data-cleaning-in-excel-and-python/managing-missing-values-part-2) we investigated the case of the England Cricket player Alan Jones. The data for this was downloaded from Wikipedia and processed to produce a single data table for analysis. We will now explore the code to download the data.

Firstly, let’s use `pd.read_HTML()` to extract the data it finds on the web page.

```python
# Import required packages
import pandas as pd

# Define URL to parse for tables
wiki_url = 'https://en.wikipedia.org/wiki/List_of_England_Test_cricketers'

# Extract tables found in to list
wiki_data = pd.read_html(wiki_url)
```

We can use the following code to inspect the imported data in more detail.

```python
# Determine how many tables found
print(len(wiki_data), 'Tables found on page:')

# Output shape of each table found
for i in range(len(wiki_data)):
    print('Table at index' , i , 'is of dtype:',
          type(wiki_data[i]), 'and has shape', wiki_data[i].shape )
```

This results in the output in figure 1.

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/obtaining-data-part-2-working-with-web-data/img1.png"
	}
	alt={
		"Analysis of each of the 16 tables found on the Wikipedia page, showing the index, data type and shape of each respectively."
	}
	width={1000}
	height={477}
	caption={
		"Figure 1. Analysis of each of the 16 tables found on the Wikipedia page, showing the index, data type and shape of each respectively."
	}
/>

We can see that this has imported a list of dataframes. We see that the tables from indices 1 to 11 all have the same number of columns and inspecting these (see supporting notebook), we can see that they correspond to the data we are interested in.

Concatenating these and using several techniques from our earlier lessons we can clean the data and coerce the values to the correct types. The code to implement this runs to several lines and has been omitted from the lesson text. It can be found in the supporting notebook and the accompanying lesson video.

The resulting dataframe now contains clean data relating to all of England’s capped cricket players.

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/obtaining-data-part-2-working-with-web-data/img2.png"
	}
	alt={
		"Head of the England cricket caps data frame displayed in the notebook, post import, concatenation, and cleaning."
	}
	width={1000}
	height={141}
	caption={
		"Figure 2. Head of the England cricket caps data frame displayed in the notebook, post import, concatenation, and cleaning."
	}
/>

## Using APIs to Extract Data

### What Is An API

An Application Programming Interface (API) is a mechanism that allows two software components to speak to each other. They are used extensively in the cloud computing world and from a data perspective, provide a user (or a client) with a way to extract specific data from an online system (or server).

The client defines a request, passes this to the server, which then returns the requested data.

APIs have many uses and enable clients and servers to communicate effectively. For this lesson, we will only consider the case where a client is extracting data from a server.

### Basic Terminology

There are some key definitions that we will need to understand to implement a basic API call.

#### REST API

If we are using an API to access data, it is likely we are using a REST API. This stands for **RE**presentational **S**tate **T**ransfer and will define several methods that a client can use to interact with a server. These methods include GET, which provides a client with the ability to extract data; DELETE, which provides a client with the ability to delete data; and PUT, which enables new data to be added to the server. There are also many other methods that can be defined.

The client defines a request, usually in the form of a text string and sends this to the server. The server then sends back a response. One of the main features of a REST API is statelessness. This means that the response data is not stored by the server after the request has been made. The server provides a response to the client and the client can then utilise this as they wish.

We will focus on GET commands in this lesson as we are interested in extracting data for analysis.

#### API Documentation

Every API will be slightly different and the details about how a client should use and implement requests for each will be contained within the documentation. A well-documented API should give examples of syntax as well as examples of how to use the API.

#### Authentication Tokens & API Keys

Very few APIs are open access. To control use and ensure that both the system and the user are authenticated, access tokens and API keys are issued. These are integrated in the request and ensure that the server only returns data to an authorised client. Obtaining tokens and keys will usually require registration and quite often a payment to be made.

Details of how to obtain and integrate these into a request will be contained within the API documentation.

#### End Point

An endpoint is the location where a request for a specific server is sent for actioning. This is usually in the form of a 'Universal Resource Locator' (or URL)

#### Parameters

The parameters for an API call allow the user to specify specific criteria for the request. Conceptually this is like filtering data and obtaining subsets of interest.

#### Java Script Object Notation (JSON)

Java Script Object Notation (JSON) is an open data file format that uses human readable text to transfer data consisting of attribute and value pairs. These are very similar to dictionaries in Python. When a response is received from an API call, it is quite common for the data to be received in a JSON format.

An example of a JSON can be seen in figure 3. Fortunately, Python and Pandas have a number of built in tools for processing such data effectively. (We will explore how the `pd.DataFrame()` function works with JSON data below. The `pd.read_json()` function and the `json` package offer further functionality as well).

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/obtaining-data-part-2-working-with-web-data/img3.png"
	}
	alt={
		"Example of a JSON in plain text format. Note the similarities with a Python dictionary."
	}
	width={400}
	height={282}
	caption={
		"Figure 3. Example of a JSON in plain text format. Note the similarities with a Python dictionary."
	}
/>

#### Response Codes

In addition to any requested data, an API will provide other information in the response. One of the key data points in this is the response code. This code indicates how the API call has been actioned and if there are any factors to be aware of. (You may already be familiar with Error 404, page not found).

The codes are three-digit numbers and fall in to five categories.

| Code | Code Category | Code Description                                                                      |
| ---- | ------------- | ------------------------------------------------------------------------------------- |
| 1XX  | Informational | Communicates information relating to the transfer of data.                            |
| 2XX  | Successful    | Indicates that the client’s request was accepted successfully.                        |
| 3XX  | Redirection   | Indicates that the client must take some additional action to complete their request. |
| 4XX  | Client Error  | Indicates an error which has occurred in the client’s request.                        |
| 5XX  | Server Error  | Indicates an error which has occurred in the server.                                  |

### A Simple API Call Example

To investigate how we can use APIs to obtain data, we will use a public API provided by the Environment Agency under an Open Government Licence. As this is public we will not need to worry about implementing authorisation tokens and keys. The documentation for this API can be [found here](https://www.data.gov.uk/dataset/24542ae7-dd44-444f-b259-871c4cc43b5e/environmental-monitoring-data).

We will make a call to obtain the ‘temperature’ data for rivers. The following code does this:

```python
# Import required packages
import requests

# Define the endpoint and parameters for the API call
end_point_URL_root = 'http://environment.data.gov.uk/flood-monitoring/id/measures/'
parameters = '?parameter=temperature'

# Get response from server
response = requests.get(end_point_URL_root + parameters)

#Output response code
print('The response code for this API call is:',response.status_code)
```

We can see by inspecting the response code that this has been successful as a 200 code has been returned. (See notebook).

By inspecting the response object, we can also see that it has three keys within it, one of which (the ‘items’) contains the data we are after. We can convert this to a data frame with the following code:

```python
# Build dataframe from JSON object
response_dataframe = pd.DataFrame(response_json['items'])
```

This yields the data frame in figure 4

<Image
	src={
		"/images/courses/strategies-and-techniques-for-data-cleaning-in-excel-and-python/obtaining-data-part-2-working-with-web-data/img4.png"
	}
	alt={
		"Example of dataframe head returned from the API call. Note the full column range is not displayed in this image."
	}
	width={1000}
	height={328}
	caption={
		"Figure 4. Example of dataframe head returned from the API call. Note the full column range is not displayed in this image."
	}
/>

We can also expand the call to use more parameters by passing a dictionary to the params variable in the response function. This removes the need to add the query to the request URL. The following code will obtain the temperature and flow data for site 3680.

```python
# Define dictionary of parameters to query
params_query ={'parameter':['temperature','flow'],'stationReference':[3680]}

# Get response from server using the params variable
response = requests.get(end_point_URL_root, params=params_query)
```

We can see from the response code and by inspecting the data returned that this has been successful.

The supporting notebook and video contain the output to confirm this. They also explore a more detailed processing of the data extracted from this API and outline the additional cleaning steps required to format the data in a tidy way.

## Conclusion, Next Steps & Exercises

APIs provide a powerful method for obtaining data for use in analysis and data work. If your work integrates with a cloud service or third-party data provider, then it is highly likely that you will need to integrate with an API.

The example above shows us that while we can link into APIs easily, there is still often cleansing work that we need to complete prior to using the data for its intended purpose. While Excel comes with the capability to extract data from simple APIs, we have seen over the last few lessons that cleansing work is best completed in Python. When dealing with APIs Python provides us with the tools and methods to conduct both processes in a few lines of code. If we want the data in Excel for analysis, we should use Python to obtain it, clean it, then push it to Excel.

The notebook supporting this lesson contains a deeper dive into the results outlined above. It also contains the code to demonstrate failures and issues in the API calls. The supporting exercise set for this lesson will explore building API calls in to other publicly available data.


Strategies and Techniques for Data Cleaning in Excel and Python

Course Description

Lessons

1. What Does Clean Data Look Like in Microsoft Excel?

2. Supercharge Your Microsoft Excel Data Cleaning with Python

3. How to Clean Data in Excel Using Python: The Basics

4. Extracting Values From Free Text: Introduction to REGEX

5. Manipulating and working with Pandas dataframes

6. Managing Missing Values Part 1

7. Managing Missing Values Part 2

8. Obtaining Data Part 1: Working with Files

9. Obtaining Data Part 2: Working with Web Data

10. Case Study: Bringing it All Together