The Definitive Guide to Reading Files in Python for Data Science

As a data scientist or programmer, being able to efficiently read data from various file formats into your Python environment is an essential skill. Not only do you need to extract data from standard plain text and CSV files, but you‘ll likely encounter Excel spreadsheets, SQL database tables, JSON from APIs, and many other types of files.

Luckily, Python provides a wealth of built-in functions and third-party libraries to make reading diverse file formats a breeze. In this guide, we‘ll walk through how to read data from all the common file types you‘ll see in data science projects. We‘ll cover the different libraries and functions, explain any tricky aspects, and show clear code examples. Let‘s get started!

Text Files

Plain text files are the simplest file format and very common for storing unstructured log data, JSON, HTML and more. Python has built-in support for reading text files using the open() function.

Here‘s the general syntax for reading a text file into Python:

with open(‘myfile.txt‘, ‘r‘) as f:
    text = f.read()

This code opens the text file in read mode (‘r‘), reads the full contents of the file into a string variable called text, and automatically closes the file afterwards.

Some other useful modes are write (‘w‘) for writing to a file, and append (‘a‘) for adding to the end of an existing file. You can also read the file line-by-line instead of all at once:

with open(‘myfile.txt‘, ‘r‘) as f:
    for line in f:
        print(line)

When reading a text file, be aware of the character encoding if your text contains any special symbols. You can specify the encoding using the encoding parameter like open(‘myfile.txt‘, encoding=‘utf-8‘).

Delimited Text Files (CSV, TSV)

Comma-separated value (CSV) and tab-separated value (TSV) files are plain text files that use delimiters like commas or tabs to separate data into a tabular structure. They are some of the most ubiquitous file formats for structured data.

Python has a built-in csv module for easily reading delimited text files, but I recommend using the read_csv function from the pandas library for more flexibility:

import pandas as pd
df = pd.read_csv(‘mydata.csv‘)

By default read_csv assumes the separator is a comma, but you can change it using the sep parameter to handle TSV and other delimited files:

df = pd.read_csv(‘mydata.tsv‘, sep=‘\t‘)

Pandas uses the first row as the column headers by default. Specify header=None if there are no headers. You can also set the data types of the columns using dtype or parse_dates for date/time columns.

Excel Files

Microsoft Excel‘s XLSX format is a common way to share tabular data. While you can export Excel data to CSV for easier parsing, Python can read XLSX files directly using the pandas library:

import pandas as pd 
df = pd.read_excel(‘mydata.xlsx‘)

By default, read_excel loads the first sheet, but you can specify the sheet name or number to read:

df = pd.read_excel(‘mydata.xlsx‘, sheet_name=‘Sheet2‘)

Excel workbooks can contain multiple sheets. List the sheet names using:

print(pd.ExcelFile(‘mydata.xlsx‘).sheet_names)

JSON Files

JavaScript Object Notation (JSON) is a lightweight format for storing nested data structures. It‘s commonly used for configuration files and for transmitting data in web APIs.

Python has a built-in json module for parsing JSON data:

import json
with open(‘myfile.json‘, ‘r‘) as f:
data = json.load(f)

This reads the JSON file into a Python dictionary called data. You can then access elements using square bracket dictionary syntax:

print(data[‘key1‘][‘nestedkey‘])

Alternatively, the pandas library allows you to read a JSON file directly into a DataFrame:

import pandas as pd
df = pd.read_json(‘myfile.json‘)  

Databases (SQL)

Relational databases like PostgreSQL, MySQL, and SQLite3 allow you to persistently store large amounts of structured data. You can use Python to directly read from databases into a DataFrame using the sqlalchemy library along with pandas:

from sqlalchemy import create_engine
import pandas as pd
engine = create_engine(‘sqlite:///mydatabase.db‘)
df = pd.read_sql(‘SELECT * FROM mytable‘, engine)

This code creates an SQLite3 database connection and then reads the results of the SQL query into a DataFrame. You‘d need to modify the connection string for other databases like MySQL. Check out the SQLAlchemy documentation for connecting to different databases.

Web Files (HTML)

Often you‘ll want to scrape structured data from web pages for analysis. Python‘s requests library allows you to download the HTML contents of a web page:

import requests
url = ‘http://example.com‘
html = requests.get(url).text 

To extract data from the HTML document, use the beautifulsoup library to parse the HTML tree and select elements:

from bs4 import BeautifulSoup
soup = BeautifulSoup(html, ‘html.parser‘)
titles = soup.findall(‘h2‘, class=‘title‘)

This finds all the H2 header tags with CSS class "title". You can then iterate through the results and extract the text.

Binary Files (images, word docs, etc)

Binary file formats like images, videos, Word docs, and executables encode their data as raw bytes. Python‘s open() function allows you to read binary data by specifying the ‘rb‘ mode (read binary):

with open(‘myimage.jpg‘, ‘rb‘) as f:
    data = f.read()

However, for specific filetypes, it‘s best to use a third-party library that understands how to decode that filetype:

from PIL import Image
img = Image.open(‘myimage.jpg‘) 

The pillow library is great for reading many types of image files (jpeg, png, etc). There are Python libraries for reading other binary files like python-docx for Word documents.

Compressed Archives (.zip, .tar, etc)

Zip and other archive formats allow you to compress multiple files into a single file to save space. Python‘s built-in zipfile module allows you to read and extract zip archives:

from zipfile import ZipFile
with ZipFile(‘files.zip‘, ‘r‘) as zipobj:
zipobj.extractall(‘output_dir‘)

This extracts all the contents of the zip archive into the output_dir folder. The tarfile module works similarly for reading TAR archives.

Reading Multiple Files

Often a dataset is split across multiple files, so you need an easy way to bulk read many files into a single DataFrame. You can use the glob module to get a list of all files matching a pattern:

from glob import glob
csv_files = glob(‘data/*.csv‘)

This returns a list of all .csv files in the data/ subdirectory. You can then read each file in a loop or pass the list to pandas‘ concat function:

import pandas as pd
df_list = [pd.read_csv(file) for file in csv_files]
df = pd.concat(df_list)

The concat function stacks all the DataFrames vertically into one combined DataFrame.

Conclusion

As a data scientist, you‘ll constantly be pulling in data from diverse sources and file types for your analyses. This guide walked through the key Python functions and libraries you need to easily read all the common file formats. While we covered a lot, there are even more advanced libraries for handling complex or domain-specific formats like NetCDF for meteorology.

The key takeaways are:

• Use built-in functions like open() for plain text, JSON, and binary files
• The pandas library can read tabular formats like CSV, TSV, Excel (read_csv, read_excel)
• Use element.find or element.select for HTML parsing with BeautifulSoup
• The zipfile and tarfile modules can read compressed archives
• Glob for reading multiple files matching a pattern
• SQLAlchemy for reading from SQL databases (MySQL, PostgreSQL, etc.)
• Third-party libraries for domain-specific formats like pillow for images

I encourage you to practice reading many different file types with Python. Valuable data comes in all kinds of formats! Having a Swiss army knife of file parsing tools will make you a highly effective data scientist.

What other handy file parsing libraries do you use? Let me know in the comments below! And for a deeper dive into data wrangling with Python, check out my pandas tutorial series.