In this tutorial we will unearth the solutions to three commonly asked questions that users come across while dealing with huge sets of data.

Problem Formulation

Given: Consider the following csv file (Note: You need to use it as a Pandas DataFrame).

import pandas as pd df = pd.read_csv('countries.csv')

print(df)

 Country Capital Population Area

0 Germany Berlin 84,267,549 348,560

1 France Paris 65,534,239 547,557

2 Spain Madrid 46,787,468 498,800

3 Italy Rome 60,301,346 294,140

4 India Delhi 1,404,495,187 2,973,190

5 USA Washington 334,506,463 9,147,420

6 China Beijing 1,449,357,022 9,388,211

7 Poland Warsaw 37,771,789 306,230

8 Russia Moscow 146,047,418 16,376,870

9 England London 68,529,747 241,930

Here’s the list of the questions that we will be focusing upon in this article:

• How to get the last N rows of a Pandas DataFrame?
• How to get last N rows from last N columns of a Pandas DataFrame?
• How to read last N rows of a large csv file in Pandas?

Recommended Read: How to Select Rows From a DataFrame Based on Column Values?

Without further delay, let us dive into the solutions to the first question and learn how to get the last N rows of a Pandas DataFrame.

Method 1: Using iloc

Approach: Use the iloc property as pandas.DataFrame.iloc[-n:].

The iloc property is used to get or set the values of specified indices. Select the last n rows using the square bracket notation syntax [-n:] with the iloc property. Here, -n represents the index of the last n rows of the given pandas DataFrame.

Code:

import pandas as pd df = pd.read_csv('countries.csv')

rows = df.iloc[-5:]

print(rows)

Output:

 Country Capital Population Area

5 USA Washington 334,506,463 9,147,420

6 China Beijing 1,449,357,022 9,388,211

7 Poland Warsaw 37,771,789 306,230

8 Russia Moscow 146,047,418 16,376,870

9 England London 68,529,747 241,930

Method 2: Using tail()

Approach: Use the pandas.DataFrame.tail(n) to select the last n rows of the given DataFrame.

The tail(n) method returns n number of methods from the bottom end of the DataFrame. Here, n represents an integer that denotes the number of rows you want to fetch from the bottom end of the DataFrame.

Code:

import pandas as pd df = pd.read_csv('countries.csv')

rows = df.tail(5)

print(rows)

Output:

 Country Capital Population Area

5 USA Washington 334,506,463 9,147,420

6 China Beijing 1,449,357,022 9,388,211

7 Poland Warsaw 37,771,789 306,230

8 Russia Moscow 146,047,418 16,376,870

9 England London 68,529,747 241,930

Well, that brings us to the next question in line – “How to get the last N rows from last N columns of a Pandas DataFrame?”

Method 1: Integer Based Indexing

Approach: Call pandas.DataFrame.iloc[-n:, -m:] to display last n rows from the last m columns of the given DataFrame.

Code: In the following code snippet we will fetch the last 5 rows from the last 2 columns, i.e., Population and Area.

import pandas as pd df = pd.read_csv('countries.csv')

rows = df.iloc[-5:, -2:]

print(rows)

Output:

 Population Area

5 334,506,463 9,147,420

6 1,449,357,022 9,388,211

7 37,771,789 306,230

8 146,047,418 16,376,870

9 68,529,747 241,930

Method 2: Name Based Indexing

In case, you happen to know the names of the specific columns and you want to get the last N records from the DataFrame from those columns then you can follow a two step process.

• Call the Pandas.DataFrame.loc(:, 'start_column_name':'end_column_name') selector. It allows you to use slicing on column names instead of integer identifiers which can be more comfortable.
• .loc is for label based indexing. Hence, the negative indices are not found and reindexed to NaN. Thus, to deal with this you have to use the tail() method to extract the last N records from the selected columns.

Code: The following code snippet shows how you can use the column names and fetch the corresponding values from the last 5 rows of the given Dataframe.

import pandas as pd df = pd.read_csv('countries.csv')

rows = df.loc[:, 'Population':'Area']

print(rows.tail(5))

Output:

 Population Area

5 334,506,463 9,147,420

6 1,449,357,022 9,388,211

7 37,771,789 306,230

8 146,047,418 16,376,870

9 68,529,747 241,930

Last but not least, let us solve the third and final problem of today’s tutorial – “How to read last N rows of a large csv file in Pandas?”

Unfortunately, read_csv() does not facilitate us with any parameter that allows you to directly read the last N lines from a file. This can be a troublesome issue to handle when you are dealing with large datasets.

Thus, a workaround to this problem is to first find out the total number of lines/records in the file. Then use the skiprows parameter to directly jump to the row/line from which you want to select the records.

Code: In the following code snippet we will fetch the first 5 rows from the csv file into our DataFrame.

import pandas as pd def num_of_lines(fname): with open(fname) as f: for i, _ in enumerate(f): pass return i + 1 num_lines = num_of_lines("countries.csv")

n = 5

df = pd.read_csv("countries.csv", skiprows=range(1, num_lines - n))

print(df)

Output:

 Country Capital Population Area

0 USA Washington 334,506,463 9,147,420

1 China Beijing 1,449,357,022 9,388,211

2 Poland Warsaw 37,771,789 306,230

3 Russia Moscow 146,047,418 16,376,870

4 England London 68,529,747 241,930

Conclusion

Phew! We have successfully solved all the problems that were presented to us in this tutorial.  I hope this tutorial helped you to sharpen your coding skills. Please stay tuned and subscribe for more interesting coding problems.

Recommended Reads:

• Pandas DataFrame head() and tail() Method
• Delete Column from Pandas DataFrame
• Change Column Type in Pandas

Learn Pandas the Fun Way by Solving Code Puzzles

If you want to boost your Pandas skills, consider checking out my puzzle-based learning book Coffee Break Pandas (Amazon Link).

It contains 74 hand-crafted Pandas puzzles including explanations. By solving each puzzle, you’ll get a score representing your skill level in Pandas. Can you become a Pandas Grandmaster?

Coffee Break Pandas offers a fun-based approach to data science mastery—and a truly gamified learning experience.

Problem Formulation

How to convert .blf from a CAN bus to .csv in Python?

What is BLF? The Binary Logging Format (BLF) is a proprietary
CAN log format from the automative company Vector Informatik GmbH.

What is CAN? The Controller Area Network (CAN bus) is a message-based protocol standard for microcontrollers in vehicles to communicate without a host computer.

Method 1: Using BLF Reader and CSV Writer

To convert the BLF file 'my_file.blf' to the CSV file 'my_file.csv', you can first iterate over the bus messages using can.BLFReader('my_file.csv') and add the data to a list of lists. Then, you can use the csv.writer() approach to write the list of lists to a CSV file.

Here’s an example that improves upon this SO thread:

import can

import csv log = [] for msg in list(can.BLFReader("my_file.blf")): msg = str(msg) row = [msg[18:26], msg[38:40], msg[40:42], msg[46], msg[62], msg[67:90]] log.append(row) with open("my_file.csv", "w", newline='') as f: writer = csv.writer(f, delimiter=',', quotechar='\"', quoting=csv.QUOTE_ALL) writer.writerows(log)

A more sophisticated version of this code is provided in this Github repository. Here’s a screenshot of the code — notice the more advanced processing of a single message compared to our solution:

Method 2: Using the candas Library

The candas library provides utility functions to work with .blf files and the CAN bus. Among other things, it helps you with the conversion from BLF to CSV as outlined here.

This is the provided example:

import candas as cd db = cd.load_dbc("dbc_folder") # This is the BLF file 'my_file.blf':

log = cd.from_file("my_file") # This prints a signal from the messages in the BLF:

print(log["AVGcellTemperature"])

Method 3: Using Custom Solution from python-can Library

You can use your tailor-made solutions by combining the Readers and Writers provided in the python-can library.

It provides multiple utility functions such as:

• Listener
• BufferedReader
• RedirectReader
• Logger
• Printer
• CSVWriter
• SqliteWriter
• ASC
• Log
• BLF

Chances are you’ll find what you’re looking for when going over those functions!

Related Video

Still not satisfied? I found the following relevant video when searching for a solution to this problem. I think you’ll find some nice tricks in the video!

Problem Formulation

Given a CSV file (e.g., stored in the file with name 'my_file.csv').

INPUT: file 'my_file.csv'

9,8,7
6,5,4
3,2,1

Challenge: How to convert it to a NumPy Array?

OUTPUT: 2D NumPy Array

[[9. 8. 7.] [6. 5. 4.] [3. 2. 1.]]

Method 1: np.loadtxt()

np.loadtxt()

You can convert a CSV file to a NumPy array simply by calling np.loadtxt() with two arguments: the filename and the delimiter string. For example, the expression np.loadtxt('my_file.csv', delimiter=',') returns a NumPy array from the 'my_file.csv' with delimiter symbols ','.

Here’s an example:

import numpy as np array = np.loadtxt('my_file.csv', delimiter=',')

print(array)

Output:

[[9. 8. 7.] [6. 5. 4.] [3. 2. 1.]]

Method 2: np.loadtxt() with Header

np.loadtxt() + header

You can convert a CSV file with first-line header to a NumPy array by calling np.loadtxt() with three arguments: the filename, skiprows=1 to skip the first line (header), and the delimiter string. For example, the expression np.loadtxt('my_file.csv', skiprows=1, delimiter=',') returns a NumPy array from the 'my_file.csv' with delimiter symbols ',' while skipping the first line.

Here’s an example:

import numpy as np array = np.loadtxt('my_file.csv', skiprows=1, delimiter=',')

print(array)

Output:

[[9. 8. 7.] [6. 5. 4.] [3. 2. 1.]]

Method 3: CSV Reader

CSV Reader

To convert a CSV file 'my_file.csv' into a list of lists in Python, use the csv.reader(file_obj) method to create a CSV file reader. Then convert the resulting object to a list using the list() constructor. As a final step, you can convert the nested list to a NumPy array by using the np.array(list) constructor.

Here’s an example:

import numpy as np

import csv csv_filename = 'my_file.csv' with open(csv_filename) as f: reader = csv.reader(f) lst = list(reader) print(lst)

The output is the list of lists:

[['9', '8', '7'], ['6', '5', '4'], ['3', '2', '1']]

Now, if you need to convert it to a NumPy array, you can simply use the np.array() function on the newly-created list like so:

array = np.array(lst)

print(array)

Output:

[['9' '8' '7'] ['6' '5' '4'] ['3' '2' '1']]

Related Tutorial: How to Convert CSV to List of Lists in Python

Method 4: np.genfromtxt()

np.genfromtxt()

You can convert a CSV file to a NumPy array simply by calling np.genfromtxt() with two arguments: the filename and the delimiter string. For example, the expression np.genfromtxt('my_file.csv', delimiter=',') returns a NumPy array from the 'my_file.csv' with delimiter symbol ','.

Here’s an example:

import numpy as np array = np.loadtxt('my_file.csv', delimiter=',')

print(array)

Output:

[[9. 8. 7.] [6. 5. 4.] [3. 2. 1.]]

Method 5: Pandas read_csv() and df.to_numpy()

read_csv() and df.to_numpy()

A quick and efficient way to read a CSV to a NumPy array is to combine Pandas’ pd.read_csv() function to read a given CSV file to a DataFrame with the df.to_numpy() function to convert the Pandas DataFrame to a NumPy array.

Here’s an example:

import pandas as pd df = pd.read_csv('my_file.csv', header=None)

array = df.to_numpy() print(array)

Output:

[[9 8 7] [6 5 4] [3 2 1]]

Related Tutorial: 17 Ways to Read a CSV File to a Pandas DataFrame

Summary

We have seen five ways to convert a CSV file to a 2D NumPy array:

• Method 1: np.loadtxt()
• Method 2: np.loadtxt() with Header
• Method 3: CSV Reader
• Method 4: np.genfromtxt()
• Method 5: Pandas read_csv() and df.to_numpy()

Our preferred way is np.loadtxt() for its simplicity and Pandas for its extensibility.