pandas read_csv ignore separator in last column

Question

I have a file with the following structure (first row is the header, filename is test.dat):

ID_OBS          LAT     LON     ALT TP TO LT_min LT_max STATIONNAME
ALT_NOA_000   82.45  -62.52   210.0 FM  0    0.0   24.0 Alert, Nunavut, Canada

How do I instruct pandas to read the entire station name (in this example, Alert, Nunavut, Canada) as a single element? I use delim_whitespace=True in my code, but that does not work, since the station name contains whitespace characters.

Running:

import pandas as pd
test = pd.read_csv('./test.dat', delim_whitespace=True, header=1)
print(test.to_string())

Produces:

                   ID_OBS    LAT LON  ALT   TP    TO  LT_min    LT_max STATIONNAME
ALT_NOA_000 82.45  -62.52  210.0  FM    0  0.0  24.0  Alert,  Nunavut,      Canada

Quickly reading through the tutorials did not help. What am I missing here?

Stephen Rauch · Accepted Answer · 2017-05-22 20:10:14Z

3

I often approach these by writing my own little parser. In general there are ways to bend pandas to your will, but I find this way is often easier:

Code:

import re

def parse_my_file(filename):
    with open(filename) as f:
        for line in f:
            yield re.split(r'\s+', line.strip(), 8)

# build the generator        
my_parser = parse_my_file('test.dat')

# first element returned is the columns
columns = next(my_parser)

# build the data frame
df = pd.DataFrame(my_parser, columns=columns)
print(df)

Results:

        ID_OBS    LAT     LON    ALT  TP TO LT_min LT_max  \
0  ALT_NOA_000  82.45  -62.52  210.0  FM  0    0.0   24.0   

              STATIONNAME  
0  Alert, Nunavut, Canada

answered May 22, 2017 at 20:10

Stephen Rauch♦

50.1k32 gold badges119 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ami Tavory · Accepted Answer · 2017-05-22 20:12:28Z

Your pasted sample file is a bit ambiguous: it's not possible to tell by eye if something that looks like a few spaces is a tab or not, for example.

In general, though, note that plain old Python is more expressive than Pandas, or CSV modules (Pandas's strength is elseswhere). E.g., there are even Python modules for recursive-descent parsers, which Pandas obviously lacks. You can use regular Python to manipulate the file into an easier form for Pandas to parse. For example:

import re
>>> ['@'.join(re.split(r'[ \t]+', l.strip(), maxsplit=8)) for l in open('stuff.tsv') if l.strip()]
['ID_OBS@LAT@LON@ALT@TP@TO@LT_min@LT_max@STATIONNAME',
 '[email protected]@[email protected]@FM@[email protected]@24.0@Alert, Nunavut, Canada']

changes the delimiter to '@', which, if you write back to a file, for example, you can parse using delimiter='@'.

Collectives™ on Stack Overflow

pandas read_csv ignore separator in last column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related