Pandas Best Practices

What is Pandas?

Pandas is one of the most-used standard Python library when dealing with matrix-like transformable datasets which range from a simple tabular to a more complex structure dataset. It is an open-source library providing faster, flexible, and expressive mean for data manipulation and one of the essential to learn library in case you are getting in the data science field.
It mainly provides two well-suited data structures to multiple data type include Series and DataFrame. This article will get into how to use both of them and the case on which one may be helpful over the other in the following.

Installation

Pandas can be installed via pip from PyPI

1

pip install pandas

or using the Anaconda or Miniconda environment,

1

conda install pandas

Pandas components

As mentioned earlier and to handle different types of datasets, pandas provides 2 data structures mainly wrapped over Numpy. Series is nothing but one dimension representation similar to the Numpy array but structured in the way it is explicitly associate an index with the values. Whereas DataFrame is a multi-dimensional array that can be thought of as a collection of series and its rows are also indexed by default unless it is explicitly done. They are pretty similar in terms of operations as one can apply almost the same manipulation to both of them.

Dataset

The dataset being used in the following is either being created manually or loaded from Seaborn repository or the dataset Traffic and Pedestrian Stops by the Police in Rhode Island to make advanced operations. The choice lies in the fact that this dataset has multiple data types from which multiple scenarios can be derived.

Best practices

1. Importing necessary libraries

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

import pandas as pd 
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt 

# whether to explicitly show the whole interactive output cells in 
# jupyter notebook rather than showing only the last line.
# use `last_expr` to revert back the default behavior
from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"
# whether to display all DataFrame rows and cols 
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)  

2. Get to know the dataset

This is the first essential tasks that you would perform before getting started your analysis. Understanding the dataset may extend from knowing what’s inside, its content, its shape, the variable names, the variable datatypes, the ratio of null values, or going deeper to know the number of unique values of categorical variables, or even gather the descriptive statistics or the variable distribution. The following code snippet shows a few essential functions along with the must to know arguments to answer these questions.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

police = pd.read_csv('datasets/police_project.csv', 
                 sep=',', 
                 parse_dates=['stop_date'],
                 index_col=None,
                 usecols=['stop_date', 'driver_gender','driver_age_raw', 'violation'])

police.head(3)
police.info(memory_usage='deep')
# Whether to display all columns with their respective types 
#police.columns.to_series().groupby(police.dtypes).groups
# Only filter `number`, `datetime`, and `object` types
police.dropna().describe(include=[np.number, np.datetime64, np.object])
police.select_dtypes(include=['number', 'object']).describe()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

  RangeIndex: 91741 entries, 0 to 91740
  Data columns (total 4 columns):
    #   Column         Non-Null Count  Dtype         
  ---  ------         --------------  -----         
    0   stop_date      91741 non-null  datetime64[ns]
    1   driver_gender  86406 non-null  object        
    2   driver_age     86120 non-null  float64       
    3   violation      86408 non-null  object        
  dtypes: datetime64[ns](1), float64(1), object(2)
  memory usage: 12.0 MB

3. Generate data sample and rename columns

Generating synthetic sample may help us in some situation like conducting an experiment, for instance, testing algorithms, making visualization to better understand changes over your experiment, and so on. There are several ways of generating samples for any purpose but the following made use of Pandas and Numpy to make it simple.

1
2
3
4
5
6
7
8

# Create a dataframe and rename columns 
# Eg. 1
foo = pd.DataFrame({'Col One': [100, 200], 'Col Two': [300, 400]})
foo.rename({'Col One': 'col_one', 'Col Two': 'col_two'}, axis=1, inplace=True)
# Eg. 2
bar = pd.DataFrame(np.random.rand(4, 5), columns=list('abcde'))
bar.add_prefix('X_')
# 

4. Reverse row and column order

These are basic operations which consist of reversing the original order of the DataFrame row-wise or column-wise. Here, reset_index() is used to drop the old index and create a new one starting from 0. We can notice the difference between the first and the second output table.

1
2
3
4
5
6

# Reverse rows
police.loc[::-1, :].head(3)
# Reverse rows and drop the old index 
police.loc[::-1, :].reset_index(drop=True).head(3)
# Reverse columns
police.loc[:, ::-1].head(3)

5. Missing data

Missing data is a common problem that we encounter in our data analysis task. In the machine learning task, one can define missing values as missing information that would bring additional information if they are observed¹. For that, investigating the missing information plays an important role in the data analysis problem. The following method displays the column-wise percentage of missing data followed by a pythonic way of getting rid of columns having only missing data followed by a tricky way to filter columns out based on the ratio of missing values.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# Reload the datset with full features
police = pd.read_csv('datasets/police_project.csv', 
                 sep=',', 
                 parse_dates=['stop_date'],
                 index_col=None)

def percent_missing(df):
    '''Missing data information summary'''
    percent_missing = (df.isnull().sum() / len(df) * 100).round(2)
    missing = pd.DataFrame({'column_name': df.columns, 'percent_missing': percent_missing})
    missing.sort_values('percent_missing', ascending=False, inplace=True)
    missing.reset_index(drop=True, inplace=True)
    return missing

def remove_missing(data, keep_ratio=.9):
    '''Missing data removal'''
    df = data.copy()
    # The pythonic way of getting rid of columns having only missing values
    df.dropna(axis="columns", how='all', inplace=True)
    # Keep columns having at least 90%(default value of keep_ratio)
    df.dropna(axis=1, thresh=len(df)*keep_ratio, inplace=True)
    # Alternative using index 
    #df = df[df.columns[df.isna().sum()/df.shape[0] < 0.9 ]]
    return df
    
# Missing data summary 
missing = percent_missing(police)
missing
# Keep columns having at least 90% values
new_police = remove_missing(police, .9)
new_missing = percent_missing(new_police)
new_missing

6. Split cells data

Splitting data is useful while dealing with object or category like data types. Let us assume that we are interested in splitting the stop_date into day, month, and year. There are at least two ways of doing so. The first and easiest approach is to treat the attribute stop_date as string and apply the transformation onto the string since there exists a couple of string manipulation helper on Python. But in our case, stop_date has already parsed to datetime64 while loading the dataset. By using the string helper functions, we are obliged to downcast again the field into str then applying split to the data afterward. This is computationally expensive when having millions of rows. The second approach comes into play to address this problem. In this, we treat the attribute as datetime64 and apply directly the transformation onto it.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

# Approach 1.1: string parsing
police[['year', 'month', 'day']] = police['stop_date'].astype(str).str.split('-', expand=True)
police.head(3)
police.drop(columns=['day', 'month', 'year'], inplace=True)

# Approach 1.2: string parsing
police['year'] = police["stop_date"].apply(lambda x: str(x).split('-')[0])
police['month'] = police["stop_date"].apply(lambda x: str(x).split('-')[1])
police['day'] = police["stop_date"].apply(lambda x: str(x).split('-')[2])
police.head(3)
police.drop(columns=['day', 'month', 'year'], inplace=True)

# Approach 2: datetime parsing
police['day'] = police['stop_date'].dt.day
police['month'] = police['stop_date'].dt.month
police['year'] = police['stop_date'].dt.year
police.head(3)
police.drop(columns=['day', 'month', 'year'], inplace=True)

The same operation can easily be applied to object or category data types

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

df = pd.DataFrame({'fullname':['John Doe McArthur', 'Jane Smith McDonald'],
                   'address':['Los Angeles, CA', 'Washington, DC']})

# Split fullname using space or tab as criteria
df['fullname'].str.split('\s+', expand=True)
df
# Split fullname using the same criteria and rename the newly generated columns
df[["first", "middle", "last"]] = df['fullname'].str.split('\s+', expand=True)
# Split location into city and state using comma as criteria 
df[["City", "State"]] = df['address'].str.split(',', expand=True)
df

7. Filter a dataframe

What makes a Pandas dataframe different from the other multi-dimensional data structures like Numpy is the way how it organizes the dataset to ease the manipulation task. Pandas makes use of index at every dimension of data in a Series or DataFrame. The index serves as label for each and every row and column to allow users accessing any specific set of cells. This makes the filtering tasks pretty straightforward. Indexes are, by default, of type integer unless it has explicitly preset.
There are multiple ways to access the indexes but two of them are common amongst the data science community including indexing operator which refers to the brackets, [] directly attached after a Series or DataFrame, and indexers that are in-built attributes of a Series and DataFrame like .iloc and .loc.
Alike to the previous approach, I will give some set of examples in the following from which you can easly derive for your own use case.