CODEWITHSUNDEEP
pandascsvwhitespaceseparator
Matias Guerrero Irarrázaval
Matias Guerrero Irarrázaval

Reputation: 21

Pandas read .csv separated by whitespace but columns with names that contain spaces

I have a .csv file that have to read. It is separated by a whitespace but the column names also have spaces. Something like this:

column1 another column final column  
value ONE valueTWO valueTHREE

I have been trying to read it withthis but it confuses with the spaces of the column names (not separators). I tried using read_fwf and read_csv but did not worked:

df_mccf=pd.read_fwf(r'C:\Users\MatíasGuerreroIrarrá\OneDrive - BIWISER\Orizon\MCCF\inputs\valores-MCCF (3).csv',\
                   colspecs=[(0, 4), (5, 10), (11, 21), (22, 32), (33, 54), (55, 1000)])

and:

df_mccf=pd.read_fwf(r'C:\Users\MatíasGuerreroIrarrá\OneDrive - BIWISER\Orizon\MCCF\inputs\valores-MCCF (3).csv',\
                   sep=' ')

get this

and with this line:

df_mccf=pd.read_csv(r'C:\Users\MatíasGuerreroIrarrá\OneDrive - BIWISER\Orizon\MCCF\inputs\valores-MCCF (3).csv',\
                   encoding='UTF-16', delim_whitespace=True)

got this

Any help would be really amazing.

Upvotes: 1

Views: 1779

Answers (1)

Edgar Ramírez Mondragón
Edgar Ramírez Mondragón

Reputation: 3001

I'd suggest you ignore the header altogether and instead pass the names argument. That way you can use the whitespace separator for the rest of the file:

import io
import pandas as pd

data = """column one column two column three
a 1 x
b 2 y
"""

with io.StringIO(data) as f:
    df = pd.read_csv(
        f,
        delim_whitespace=True,
        names=['one', 'two', 'three'],  # custom header names
        skiprows=1,  # Skip the initial row (header)
    )

Result:

  one  two three
0   a    1     x
1   b    2     y

Upvotes: 3

Related Questions