Read .csv file with arbitrary spaces between entries

Question

I have a .csv file for to read with pd.read_csv(). Unfortunately, each line is entered in a single cell instead of multiple cells for each column as shown below:

I am trying something like this to read that file:

sheffield = pd.read_csv('data/sheffield_weather_station.csv', skiprows=8, delimiter='|', engine='python')

It gives me this output without separating each value/data. I checked the spaces between columns in the Microsoft Excel, they are arbitrary. Is there a specific option of pd.read_csv() to solve this problem?

Answer 1

You may try

sheffield = pd.read_csv('data/sheffield_weather_station.csv', skiprows=8, sep='\s+', engine='python')

It is essentially the same as delim_whitespace=True as it is an alias for the sep parameter, see the pandas documentation on the matter.