How to select multiple DataFrame columns using regexp and datatypes

DataFrame maybe compared to a data set held in a spreadsheet or a database with rows and columns. DataFrame is a 2D Object.

Ok, confused with 1D and 2D terminology ?

The major difference between 1D (Series) and 2D (DataFrame) is the number of points of information you need to inorer to arrive at any single data point. If you take an example of a Series, and wanted to extract a value, you only need one point of reference i.e. row index.

In comparsion to a table (DataFrame), one point of reference is not sufficient to get to a data point, you need an intersection of row value and column value.

Below snippet shows how to create a Pandas DataFrame from a csv file.

.read_csv() method by default creates a DataFrame. You can download the movies dataset from kaggle.com by searching for movies.

"""Script : Create a Pandas DataFrame from a csv file."""
import pandas as pd
movies_dataset pd read_csv "https://raw.githubusercontent.com/sasankac/TestDataSet/master/movies_data.csv")
# 1 print the type of object
type(movies_dataset)
# 2 print the top 5 records in a tabular format
movies_dataset head(5)

2. Selecting a single DataFrame column. Passing column name as a string or list to the index operator will return the column values as either Series or DataFrame.

If we pass in a string with the column name, you will get a Series as output, however, passing the list with just one column name will return the DataFrame. We will see this with examples.

# select the data as series movies_dataset["title"]

0 Avatar
1 Pirates of the Caribbean: At World's End
2 Spectre
3 The Dark Knight Rises
4 John Carter
...
4798 El Mariachi
4799 Newlyweds
4800 Signed, Sealed, Delivered
4801 Shanghai Calling
4802 My Date with Drew
Name: title, Length: 4803, dtype: object

# select the data as DataFrame movies_dataset[["title"]]

3. Selecting multiple DataFrame column.

# Multiple DataFrame columns movies_dataset[["title""runtime","vote_average","vote_count"]]

To avoid code readability issues, I always recommend defining a variable for holding the names of the columns as a list and use the column name instead of specifying multiple column names within the code.

columns=["title","runtime","vote_average","vote_count"]movies_dataset[columns]

4.DataFrame columns by column names.

.filter() method

This method is quite handy to search and select the columns using a string. This works pretty much the same way as the like %% parameter in SQL. Remember, .filter() method selects columns by only inspecting the column names and not the actual data values.

.filter() method supports three parameters that can be used for selection operation.

.like
.regex
.items

like parameter takes a string and attempts to find the column names that contain this string somewhere in the column name.

# Select the column that have a column name like "title" movies_dataset.filter(like="title").head(5)

.regex – More flexible way of selecting the columns names using regualr expressions

# Select the columns that end with "t"
movies_dataset.filter(regex=t).head()

.items – Duplicate of passing column name as string or list to index operator, except KeyError will not be raised

DataFrame columns by data types.

The .select_dtypes method works on the column data types if you are interested to filter and use only certain data types.

Again, .select_dtypes method accepts multiple data types (by a list) or single data type (as a String) in its include or exclude parameters and returns a DataFrame with columns of just those given data types.

.include parameter includes the columns with the specified data type/s and .exclude will ignore the columns with the specified data type.

Let’s first look at the data types and count of columns with those data types

movies_dataset=pd.read_csv("https://raw.githubusercontent.com/sasankac/TestDataSet/master/movies_data.csv"
movies_dataset.dtypes.value_counts()

object 5
int64 4
float64 3
dtype: int64

a) Filtering integer data types from pandas DataFrames.

movies_dataset select_dtypes(include="int")head(3)

b). Select integer and float data types from pandas DataFrames.

You can specify multiple data types as a list show below.

movies_dataset select_dtypes(include=["int64","float"]).head(3)

c) Well, if you just want all numeric data types, just specify number

movies_dataset select_dtypes(include=["number"]).head(3)

d). Exclude certain data types from pandas DataFrames.

movies_dataset select_dtypes(exclude=["object"]).head(3)

NOTE:- There is not string data type to deal with, pandas convert them to Object, so if you face an exception “TypeError: data type “string” not understood” , replace string with Object instead.

Kiran P

Updated on: 10-Nov-2020

563 Views

Kickstart Your Career

Get certified by completing the course

Get Started