How to Convert Space-Delimited File to CSV in Python?

5/5 - (2 votes)

The easiest way to convert a space-delimited file to a comma-separated values (CSV) file is to use the following three lines of code:

  1. import pandas as pd
  2. df = pd.read_csv('my_file.txt', sep='\s+', header=None)
  3. df.to_csv('my_file.csv', header=None)

We’ll explain this and other approaches in more detail next—scroll down to Method 3 for this exact method.

Problem Formulation

Given a space-delimited file with one or more empty spaces between two values in a given column

Input: 'my_file.txt'

Alice DataScience $100000
Bob Programmer $90000
Carl Manager $122000
Dave Freelancer $144000

How to convert the space-delimited values to a comma-separated values (CSV) file?

Output: 'my_file.csv'

0,Alice,DataScience,$100000
1,Bob,Programmer,$90000
2,Carl,Manager,$122000
3,Dave,Freelancer,$144000

We’ll also look at slight variations of this problem. Let’s go!

Method 1: String Replace Single Whitespace

The most straightforward way to convert a space-delimited to a comma-separated (CSV) file in Python is to replace each empty space ' ' with a comma ',' character using the string.replace() method. This works if two values are separated by exactly one empty space.

Here’s an example input file 'my_file.txt':

Alice DataScience $100000
Bob Programmer $90000
Carl Manager $122000
Dave Freelancer $144000

Here’s an example of some code to convert the space-delimited file to the CSV file:

with open('my_file.txt') as infile:

    # Read space-delimited file and replace all empty spaces by commas
    data = infile.read().replace(' ', ',')

    # Write the CSV data in the output file
    print(data, file=open('my_file.csv', 'w'))
    

Output file 'my_file.csv':

Alice,DataScience,$100000
Bob,Programmer,$90000
Carl,Manager,$122000
Dave,Freelancer,$144000

If you have any doubts, feel free to dive into our related tutorials:

Method 2: Regex Replace Arbitrary Whitespace

To replace one or more empty spaces between two column values with a comma ',' and obtain a CSV, use the regular expressions operation re.sub('[ ]+', ',', data) on the space-separated data. If you also need to replace tabular characters, use re.sub('[ \t]+', ',', data) instead.

If you have any doubts, feel free to dive into our related tutorials:

Here’s an example input file 'my_file.txt', notice the additional whitespaces that may separate two column values:

Alice DataScience $100000
Bob   Programmer  $90000
Carl  Manager     $122000
Dave  Freelancer  $144000

Here’s an example of some code to convert the space-delimited file to the CSV file:

import re

with open('my_file.txt') as infile:

    # Read space-delimited file and replace all empty spaces by commas
    data = re.sub('[ ]+', ',', infile.read())

    # Write the CSV data in the output file
    print(data, file=open('my_file.csv', 'w'))

Output file 'my_file.csv':

Alice,DataScience,$100000
Bob,Programmer,$90000
Carl,Manager,$122000
Dave,Freelancer,$144000

Method 3: Pandas read_csv() and to_csv()

To convert a space-delimited file to a CSV, first read the file into a Pandas DataFrame using pd.read_csv(filename, sep='\s+', header=None) and then write the DataFrame to a file using df.to_csv(outfilename, header=None).

Here’s an example input file 'my_file.txt':

Alice DataScience $100000
Bob   Programmer  $90000
Carl  Manager     $122000
Dave  Freelancer  $144000

Here’s an example of some code to convert the space-delimited file to the CSV file:

import pandas as pd

# Read space-delimited file
df = pd.read_csv('my_file.txt', sep='\s+', header=None)

# Write DataFrame to file
df.to_csv('my_file.csv', header=None)

Output file 'my_file.csv':

0,Alice,DataScience,$100000
1,Bob,Programmer,$90000
2,Carl,Manager,$122000
3,Dave,Freelancer,$144000

If you have any doubts, feel free to dive into our related tutorials:

Summary

We examined three great ways to convert a space-delimited to a comma-separated CSV file:

Thanks for taking the time to read this article, my friend! πŸπŸ’›


Regex Humor

Wait, forgot to escape a space. Wheeeeee[taptaptap]eeeeee. (source)