Data Manipulation
Data manipulation is the process of changing the structure of data to make it easier to analyze. In this post, I cover Pandas, which is an open-source data analysis and manipulation package built on top of Python. Pandas is computationally very efficient and easy to use. In AI and DS applications, Pandas is mainly used for importing, preparing, and analyzing raw data before building a model. Most Python AI and Data Science packages accept Pandas data structures as input. Therefore, it is important to master Pandas.
Dataframes
The primary, most commonly used Pandas data structure is the dataframe. A dataframe is a two-dimensional, tabular data structure that organizes data into columns and rows, similar to a spreadsheet or SQL table. A column is an array of data values that are accessible by its column name, which is also called a label. Rows correspond to data records, and they are accessed by an index. We can set a column as the index of a dataframe, or Pandas will assign auto-increasing integers as indexes by default. The figure below illustrates the structure of a dataframe.
DataFrame Function
The DataFrame() function is used to create a dataframe from a variety of sources. This example demonstrates creating a dataframe from a Python list.
import numpy as np
import pandas as pd
data = ['FTP', 'HTTP', 'SSH', 'HTTPS']
df = pd.DataFrame(data)
print(df)
In the output above, notice that the DataFrame() function added the row indexes and the column names to the dataframe df automatically. It is always beneficial to define meaningful column names. This time, we will assign a column name (Protocol) while creating the dataframe.
Now let us create a dataframe with two columns, called Protocol and Port Number, from a Python nested-list.
data = [['FTP', 21], ['HTTP', 80], ['SSH', 22], ['HTTPS', 443]]
df = pd.DataFrame(data, columns=['Protocol', 'Port Number'])
print(df)
Realize that we did not specify the data types of the columns in the examples above. Pandas can assign data types automatically. The dtypes attribute of a dataframe holds the data types of the columns as shown below.
There are many ways of creating pandas dataframes, but most of the time, we will read data from a file (e.g, csv, json) or download it from the internet.
Indexes
Indexing allows us to access particular rows and columns in a dataframe. By default, a column name is the index (also called key) for the column. Hence, if we want to select a particular column, we need to type dataframe[column_label] as shown below.
Rows also have indexes. In fact, we use the term ‘index’ to refer to row indexes. If we do not define an index while creating a dataframe, Pandas assigns a RangeIndex as 0, 1, 2, … by default. We can use the index attribute to figure out the index type of a dataframe as shown below.
The statement above returns the following output, which means that the dataframe uses the default range indexing.
We can access a cell by its column name and row index. For example, we can access the element with the index of 0 in the Protocol column as follows:
In the next example, we will set the Port Number column as the index of the dataframe using the set_index() method. In the following code, we also use the inplace parameter to make the changes permanent. When we set the inplace parameter to True, we modify the dataframe in place (do not create a new Dataframe object). Otherwise, our update will not be applied to the dataframe unless we assign the dataframe to a new one.
data = [['FTP', 21], ['HTTP', 80], ['SSH', 22], ['HTTPS', 443]]
df = pd.DataFrame(data, columns=['Protocol', 'Port Number'])
df.set_index('Port Number', inplace=True)
Notice that the original dataframe had a RangeIndex, and now it was replaced by the “Port Number” column. Also, the “Port Number” column does not appear as a regular column anymore. Hence, the following statement will return an error.
The following statement will also return a KeyError because index 0 does not exist anymore.
Instead, we should use Port Number as the index to access the rows as shown in the following examples
Reading CSV Files
The comma-separated values format (csv) stores text-based data in a tabular form by separating individual data values by comma or some other delimiters. Usually, the first line of a csv file includes column names. The read_csv() function reads a csv formatted file and returns a Pandas object. We only need to specify the path of the file to be loaded. The following example demonstrates reading players.csv file and creating a dataframe from its content.
The example assumes that the players.csv file and the Python code are in the same directory. Therefore, the directory path is not specified. You can download the players.csv file using the following link:
players.csv
df = pd.read_csv('players.csv')
# Print the first five rows of the dataframe
print(df.head())
# Print the last five rows
print(df.tail())
# Check the column names
print(df.columns)
# Check the datatypes
print(df.dtypes)
In the above code, the head() and tail() methods print the top and bottom five rows of the dataframe, respectively. We usually use them for testing whether the dataframe has been loaded correctly.
Note that the read_csv() function has imported the column labels from the first line of the csv file and assigned datatypes to the columns automatically. This is the default behavior of read_csv(), which has many other parameters and options to perform complex operations while loading a dataset into the memory. In the following, we will learn the basic techniques, which are frequently used in practice. Refer to the Pandas Reference Manual for details.
usecols
When the target dataset contains a large number of columns, we may want to load only a specific set of columns into a dataframe. In such cases, the usecols parameter specifies the columns to be loaded into the dataframe. In the following example, we load only the Name, Number, Position, and College columns.
df = pd.read_csv('players.csv', usecols=['Name', 'Number', 'Position', 'College'])
print(df.head())
print(df.dtypes)
In some cases, it is easier to skip columns than to specify them. In this case, we can use the usecols parameter with a lambda function. In the following example, we load all the columns but not ‘Height’ and ‘Weight’.
skip rows and nrows
The skiprows parameter allows us to skip specific rows while loading the csv file into the dataframe. In the following example, rows 1, 3, and 5 are not loaded.
We can also use the range() function to specify a range of rows to be excluded. In the following example, the first 100 rows of the file are excluded while loading it into the dataframe.
The nrows parameter allows loading a limited number of rows instead of all rows of a file. This argument is useful if the file is too large. For example, we load only the first 4 rows of the file as follows: