ex1

Data Cleaning Assignment

Data scientists spend a large amount of their time cleaning datasets and getting them down to a form with which they can work. In fact, a lot of data scientists argue that the initial steps of obtaining and cleaning data constitute 80% of the job.

Therefore, if you are just stepping into this field or planning to step into this field, it is important to be able to deal with messy data, whether that means missing values, inconsistent formatting, malformed records, or nonsensical outliers.

In this tutorial, we’ll leverage Python’s Pandas and NumPy libraries to clean data.

We’ll cover the following:

· Dropping unnecessary columns in a DataFrame

· Changing the index of a DataFrame

· Using .str() methods to clean columns

· Using the DataFrame.applymap() function to clean the entire dataset, element-wise

· Renaming columns to a more recognizable set of labels

· Skipping unnecessary rows in a CSV file

Here are the datasets that we will be using:

· BL-Flickr-Images-Book.csv – A CSV file containing information about books from the British Library

· university_towns.txt – A text file containing names of college towns in every US state

· olympics.csv – A CSV file summarizing the participation of all countries in the Summer and Winter Olympics

You can download the datasets from Real Python’s GitHub repository in order to follow the examples here.

Note: I recommend using Jupyter Notebooks to follow along.

This tutorial assumes a basic understanding of the Pandas and NumPy libraries, including Panda’s workhorse Series and DataFrame objects, common methods that can be applied to these objects, and familiarity with NumPy’s NaN values.

Let’s import the required modules and get started!

>>>

>>> import pandas as pd

>>> import numpy as np

Dropping Columns in a DataFrame

Often, you’ll find that not all the categories of data in a dataset are useful to you. For example, you might have a dataset containing student information (name, grade, standard, parents’ names, and address) but want to focus on analyzing student grades.

In this case, the address or parents’ names categories are not important to you. Retaining these unneeded categories will take up unnecessary space and potentially also bog down runtime.

Pandas provides a handy way of removing unwanted columns or rows from a DataFrame with the drop() function. Let’s look at a simple example where we drop a number of columns from a DataFrame.

First, let’s create a DataFrame out of the CSV file ‘BL-Flickr-Images-Book.csv’. In the examples below, we pass a relative path to pd.read_csv, meaning that all of the datasets are in a folder named Datasets in our current working directory:

>>>

>>> df = pd.read_csv('Datasets/BL-Flickr-Images-Book.csv')

>>> df.head()

(Insert full-screen snapshot of results)

When we look at the first five entries using the head() method, we can see that a handful of columns provide ancillary information that would be helpful to the library but isn’t very descriptive of the books themselves: Edition Statement, Corporate Author, Corporate Contributors, Former owner, Engraver, Issuance type and Shelfmarks.

We can drop these columns in the following way:

>>>

>>> to_drop = ['Edition Statement',

... 'Corporate Author',

... 'Corporate Contributors',

... 'Former owner',

... 'Engraver',

... 'Contributors',

... 'Issuance type',

... 'Shelfmarks']

>>> df.drop(to_drop, inplace=True, axis=1)

Above, we defined a list that contains the names of all the columns we want to drop. Next, we call the drop() function on our object, passing in the inplace parameter as True and the axis parameter as 1. This tells Pandas that we want the changes to be made directly in our object and that it should look for the values to be dropped in the columns of the object.

When we inspect the DataFrame again, we’ll see that the unwanted columns have been removed:

>>>

>>> df.head()

(Insert full-screen snapshot of results)

Alternatively, we could also remove the columns by passing them to the columns parameter directly instead of separately specifying the labels to be removed and the axis where Pandas should look for the labels:

>>>

>>> df.drop(columns=to_drop, inplace=True)

This syntax is more intuitive and readable. What we’re trying to do here is directly apparent.

If you know in advance which columns you’d like to retain, another option is to pass them to the usecols argument of pd.read_csv.

Changing the Index of a DataFrame

A Pandas Index extends the functionality of NumPy arrays to allow for more versatile slicing and labeling. In many cases, it is helpful to use a uniquely valued identifying field of the data as its index.

For example, in the dataset used in the previous section, it can be expected that when a librarian searches for a record, they may input the unique identifier (values in the Identifier column) for a book:

>>>

>>> df['Identifier'].is_unique

True

Let’s replace the existing index with this column using set_index:

>>>

>>> df = df.set_index('Identifier')

>>> df.head()

(Insert full-screen snapshot of results)

Technical Detail: Unlike primary keys in  SQL , a Pandas Index doesn’t make any guarantee of being unique, although many indexing and merging operations will notice a speedup in runtime if it is.

We can access each record in a straightforward way with loc[]. Although loc[] may not have all that intuitive of a name, it allows us to do label-based indexing, which is the labeling of a row or record without regard to its position:

>>>

>>> df.loc[206]

(Insert full-screen snapshot of results)

In other words, 206 is the first label of the index. To access it by position, we could use df.iloc[0], which does position-based indexing.

Technical Detail: .loc[] is technically a  class instance  and has some special  syntax  that doesn’t conform exactly to most plain-vanilla Python instance methods.

Previously, our index was a RangeIndex: integers starting from 0, analogous to Python’s built-in range. By passing a column name to set_index, we have changed the index to the values in Identifier.

You may have noticed that we reassigned the  variable  to the object returned by the method with df = df.set_index(...). This is because, by default, the method returns a modified copy of our object and does not make the changes directly to the object. We can avoid this by setting the inplace parameter:

df.set_index('Identifier', inplace=True)