[FIXED] how to read space-separated values (csv) file made using shell-like syntax into DataFrame

Issue

i'm looking for a fast way to read a csv file into dataframe. the csv file is a space separated, however, columns names are placed between double quotation if it contains more than one word & contains multiple spaces. pd.read_csv with sep=" " does not work, because colomn names spaces.

i currently solved this by applying shlex.split on every line of the file, changing it to comma separated. however, it is taking too long: ~6 seconds for a file with 15K lines. below is an example of my file ('template.csv') and code snipet on how it is solved through shlex.split.

appreciate the help in advance!

a b c "d e  " "f g  " "h k  "
1 2 3 4 5 6
2 2 3 4 5 6
3 2 3 4 5 6
4 2 3 4 5 6
5 2 3 4 5 6
6 2 3 4 5 6

and below is code and desired dataframe output:

import pandas as pd
import shlex
data = []
df = pd.DataFrame()
for line in open(r'template.csv'):
    line = shlex.split(line)
    data.append(line)
df = pd.DataFrame(data[1:], columns=[colName for colName in data[0]])


   a  b  c d e f g h k
0  1  2  3   4   5   6
1  2  2  3   4   5   6
2  3  2  3   4   5   6
3  4  2  3   4   5   6
4  5  2  3   4   5   6
5  6  2  3   4   5   6 

Solution

You can set the separator:

# or suggested by @AndrejKesely, sep=r'\s+'
df = pd.read_csv('template.csv', sep=' ')
print(df)

# Output
   a  b  c  d e  f g  h k
0  1  2  3    4    5    6
1  2  2  3    4    5    6
2  3  2  3    4    5    6
3  4  2  3    4    5    6
4  5  2  3    4    5    6
5  6  2  3    4    5    6

Alternative with shlex but use it only on the first row:

with open('template.csv') as fp:
    headers = shlex.split(fp.readline())
    df = pd.read_csv(fp, header=None, names=headers, sep=' ')

Answered By - Corralien