Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 64 ยท Getting Started

Reading Text and Fixed-Width Files

Read tab-separated, whitespace-aligned and fixed-width text files with pandas read_table, read_fwf and read_csv options, and convert them to clean CSV.

Not all data arrives as tidy CSV. Older systems, scientific instruments, government exports and log files often produce plain text where columns are separated by tabs, by runs of spaces, or by nothing at all: every field simply sits at a fixed position on the line. pandas can read all of these. The trick is to look at the raw text first, decide how the columns are separated, and then pick the matching reader.

As in the CSV lesson, the "files" in this lesson are strings wrapped in io.StringIO. On your computer you would pass a file path instead.

First, look at the raw text

Before you choose a function, print a few raw lines. Your eyes tell you more than any guess:

Python
with open("measurements.txt") as f:
    for line in f.readlines()[:5]:
        print(repr(line))

repr() makes tabs (\t) and trailing spaces visible. Then ask three questions:

  1. Is there a separator character (comma, semicolon, tab, pipe)?
  2. Are columns separated by a variable number of spaces?
  3. Are columns defined by character positions (fixed width)?
flowchart TD
    A["Raw text file"] --> B{"One separator<br/>character?"}
    B -->|yes| C["read_csv(sep=...)<br/>or read_table"]
    B -->|"no, many spaces"| D{"Do fields<br/>contain spaces?"}
    D -->|no| E["read_csv(sep=r'\\s+')"]
    D -->|yes| F["read_fwf(colspecs=...)"]

Choose the reader from what the text looks like, not from the file extension.

Tab-separated files with read_table

pd.read_table() is read_csv with the separator set to a tab by default. Tab-separated files often end in .tsv or .txt:

Python Tab-separated text

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Every option you learned for read_csv (usecols, dtype, parse_dates, na_values, skiprows and so on) works with read_table, because they share one implementation. The only difference is the default separator.

Columns separated by runs of spaces

Some tools line up their columns with spaces, using as many as needed. The width of the gap varies, so no single character works as a separator. A regular expression does: \s+ means "one or more whitespace characters":

Python Whitespace-aligned columns

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Note the raw string r"\s+". Without the r prefix Python would try to interpret \s as an escape sequence. You may see the old shortcut delim_whitespace=True in older code; it was deprecated, so write sep=r"\s+" instead.

This approach breaks when a field itself contains spaces, such as the city "New York". The reader would split it into two columns. For that case, you need fixed-width parsing.

Fixed-width files with read_fwf

In a fixed-width file, each column occupies the same character positions on every line. There may be no separators at all. Think of a printed report or an old mainframe export:

Text
Oslo        NO  709
New York    US 8258
Sรฃo Paulo   BR 11451

pd.read_fwf() reads such files. By default it tries to guess the column boundaries by looking for gaps of blanks that line up in every row. That works for tidy files, but it is better to state the boundaries explicitly.

Python read_fwf with guessed and explicit columns

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

You can describe the layout in three ways:

  • colspecs: a list of (start, stop) pairs. Positions count from zero and stop is exclusive, just like Python slices.
  • widths: the width of each column in turn. pandas computes the positions for you.
  • colspecs="infer" (the default): pandas guesses from the first rows (infer_nrows, 100 by default). Guessing fails if the first rows have blanks in a column that is filled later.

Explicit positions are safer, and they give you a way to document the file format in your code. The format description (the "record layout") is usually provided by whoever created the file.

Fixed-width files with messy details

Real fixed-width files have extras. A header line may or may not exist, there may be title lines above the data, and padding characters other than spaces may appear. The options you already know handle most of it:

Python Skipping junk, padding and missing values

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Leading and trailing spaces in each field are removed automatically. If your file pads with another character, such as a dot or a zero, pass it as delimiter=, for example delimiter=".".

From a text file to clean CSV

Fixed-width and odd text formats are awkward for other tools. A common job is to read them once and save a clean CSV. Type the cleaning steps in code so that the conversion is repeatable:

Python Convert fixed-width text to CSV

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

On your computer you would write df.to_csv("cities.csv", index=False). See Writing and Formatting Data for every option.

Big text files and encodings

Large text files follow the same rules as large CSV files: use usecols to read only what you need, dtype to keep memory small, and chunksize to process the file piece by piece (both read_table and read_fwf accept it). Old exports often use encodings other than UTF-8. If you see strange characters or a UnicodeDecodeError, try encoding="latin-1" or encoding="cp1252".

Which reader should I use?

File looks like Use
Commas, semicolons, pipes read_csv(sep=...)
Tabs read_table() or read_csv(sep="\t")
Aligned by varying spaces, no spaces inside values read_csv(sep=r"\s+")
Positions on each line, spaces inside values read_fwf(colspecs=...)
Free text, one record per line Path.read_text().splitlines() into a Series, then str methods

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the default separator of pd.read_table()?

    read_table is read_csv with sep set to a tab by default. All other options are the same.
  2. 2. Which call reads columns that are separated by a varying number of spaces?

    The regular expression \s+ matches one or more whitespace characters, so any gap width works as a separator.
  3. 3. In colspecs=[(0, 12), (12, 14)], what does (12, 14) select?

    colspecs use Python slice semantics: the start is included, the stop is excluded, and counting starts at zero.
  4. 4. Why is a whitespace separator a bad choice for a column that holds names like 'New York'?

    A whitespace separator cannot tell the space inside a value from the gap between columns. Fixed-width parsing uses positions instead.

Summary

  • Always look at the raw lines first (repr() shows tabs) and decide how the columns are marked.
  • read_table is read_csv with a tab as the default separator; every option carries over.
  • Use sep=r"\s+" for columns separated by runs of spaces, as long as no value contains spaces.
  • read_fwf reads fixed-width data with colspecs (start and stop pairs), widths, or guessed boundaries; explicit positions are safer.
  • skiprows, comment, na_values, names and encoding clean up headers, junk lines, missing markers and old encodings.
  • Read once, then save a clean CSV with to_csv.

Next: Writing and Formatting Data, the other direction.