Importing Data from Tab Delimited Files with Python
Dec 10, 2018 • 7 Minute Read
Introduction
A tab-delimited file is a well-known and widely used text format for data exchange. By using a structure similar to that of a spreadsheet, it also allows users to present information in a way that is easy to understand and share across applications - including relational database management systems.
The IANA standard for tab-separated values requires the first line of the file to contain the field names. Additionally, other lines (which represent separate records) must have the same number of columns.
Other formats, such as comma-separated values, often pose the challenge of having to escape commas, which are frequent within text (as opposed to tabs).
Opening Files with Python
Before we dive into processing tab-separated values, we will review how to read and write files with Python. The following example uses the open() built-in function to open a file named players.txt located in the current directory:
with open('players.txt') as players_data:
players_data.read()
The open() function accepts an optional parameter that indicates how the file will be used. If not present, read-only mode is assumed. Other alternatives include, but are not limited to, 'w' (open for writing in truncate mode) and 'a' (open for writing in append mode).
After pressing Enter twice to execute the above suite, we will see tabs (\t) between fields, and new line breaks (\n) as record separators in Fig. 1:
Although we will be primarily concerned with extracting data from files, we can also write to them. Again, note the use of \n at the beginning to indicate a new record and \t to separate fields:
with open('players.txt', 'a') as players_data:
players_data.write('\n{}\t{}\t{}\t{}\t{}\t{}\t{}'.format('Trey', 'Burke', '23', '1.85', '2013', '79.4', '23.2'))
Although the format() function helps with readability, there are more efficient methods to handle both reading and writing - all available within the same module in the standard library. This is particularly important if we are dealing with large files.
Introducing the CSV Module
Although it was named after comma-separated values, the CSV module can manage parsed files regardless of the field delimiter - be it tabs, vertical bars, or just about anything else. Additionally, this module provides two classes to read from and write data to Python dictionaries (DictReader and DictWriter, respectively). In this guide we will focus on the former exclusively.
To illustrate, we will use a list of NBA games from November 2018 where the visitor won the match, which contains the list of NBA games from November 2018 where the visitor won the match.
First off, we will import the CSV module:
import csv
Next, we will open the file in read-only mode, instantiate a CSV reader object, and use it to read one row at a time:
with open('nba_games_november2018_visitor_wins.txt', newline = '') as games:
game_reader = csv.reader(games, delimiter='\t')
for game in game_reader:
print(game)
Although it is not strictly necessary in our case, we will pass newline = '' as an argument to the open() function as per the module documentation. If our file contains newlines inside quoted fields, this ensures that they will be processed correctly.
Fig. 2 shows that each row was read into a list after the above suite was executed:
Although this undoubtedly looks much better than our previous version where tabs and new lines were mixed with the actual content, there is still room for improvement.
The DictReader Class
A CSV DictReader object behaves essentially as a regular reader but maps each row to an ordered dictionary as of Python 3.6. In previous versions, each row is mapped to an ordinary dictionary. In any event, this allows us to manipulate a Python dictionary using the methods and tools we have covered in the last two guides (Manipulating Lists and Dictionaries in Python, Importing Data from Microsoft Excel Files with Python).
To begin, we will create an empty list where we will store each game as a separate dictionary:
games_list = []
Finally, we will repeat the same code as above with only a minor change. Instead of printing each row, we will add it to games_list. If you are using Python 3.5 or older, you can omit dict() and use games_list.append(game) instead. In Python 3.6 and newer, this function is used to turn the ordered dictionary into a regular one for better readability and easier manipulation.
with open('nba_games_november2018_visitor_wins.txt', newline = '') as games:
game_reader = csv.DictReader(games, delimiter='\t')
for game in game_reader:
games_list.append(dict(game))
We can go one step further and use list comprehension to return only those games where the visitor score was greater than 130. The following statement creates a new list called visitor_big_score_games and populates it with each game inside games_list where the condition is true:
visitor_big_score_games = [game for game in games_list if int(game['Visitor score']) > 130]
Now that we have a list of dictionaries, we can write it to a spreadsheet as explained in Importing Data from Microsoft Excel Files with Python or manipulate it otherwise. Another option consists of writing the list converted to string into a plain text file named visitor_big_score_games.json for distribution in JSON format:
with open('visitor_big_score_games.json', 'w') as games:
games.write(str(visitor_big_score_games))
The write() function requires a string as an argument. That is why we had to convert the entire list into a string before performing the write operation.
If you just want to view the list, not turn it into a spreadsheet or a JSON file, you can alternatively use pprint() to display it in a user-friendly format as shown in Fig. 3:
import pprint as pp
pp.pprint(visitor_big_score_games)
As you can see, the possibilities are endless and the only limit is our imagination!
Summary
In this guide we learned how to import and manipulate data from tab-delimited files with Python. This not only is a highly valuable skill for data scientists, but for web developers and other IT professionals as well.
