Customizing the separator in pandas read_csv

Question

I am reading many different data files into various pandas dataframes. The columns in these datafiles are separated by spaces. However, for each file, the number of spaces is different (for some of them, there is only one space, for others, there are two spaces and so on). Thus, every time I import the file, I have to manually go to that file and see the number of spaces that have been used and give those many number of spaces in sep:

import pandas as pd
df = pd.read_csv('myfile.dat', sep = '    ')

Is there any way I can tell pandas to assume "any number of spaces" as the separator? Also, is there any way I can tell pandas to use either tab (\t) or spaces as the separator?

Answer 1

Yes, you can use a simple regular expression like sep='\s+' to denote one or more spaces.

Answer 2

You can also use the parameter skipinitialspace=True which skips the leading spaces after any delimiter.

Answer 3

You can directly use delim_whitespace:

import pandas as pd
df = pd.read_csv('myfile.dat', delim_whitespace=True )

The argument delim_whitespace controls whether or not whitespace (e.g. ' ' or ' ') will be used as separator. See pandas.read_csv for details.

Answer 4

One thing I found is if you use a unsupported separator. Pandas/Dask will have to use the Python engine instead of the C engine. This is a good deal slower.