How can I stop Pandas (Python) from reading reading the delimiter in rows I want it to skip?

user_4592206 0
Viewed 447

I am using Pandas to read a log file table separated by ; that comes with 16 rows of log file information. These rows are identified by a # before them.

# Logger type: CL2000
# HW rev: 7.2x
# FW rev: 5.79
# Logger ID: id0001
# Session No.: 94
# Split No.: 1
# Time: 20200222T230231
# Value separator: ";"
# Time format: 4
# Time separator: ""
# Time separator ms: ""
# Date separator: ""
# Time and date separator: "T"
# Bit-rate: 500000
# Silent mode: false
# Cyclic mode: false
Timestamp;Type;ID;Data
22T230231142;0;ad;1100000000000000
22T230231143;0;ac;0000f5ff04000000
22T230231143;0;ab;0000000000000000
22T230231143;0;aa;0000090000008000
22T230231143;0;a8;21005ac15cffd7ff
...

The issue is when I use Pandas to read this file, I am telling it to skip those 16 lines of header with the header field, but the row # Value separator: ";" is screwing up the csv_read command because it's seeing the delimiter there. How can this be avoided?

Calling

sample = pd.read_csv(filename, header=11, delimiter=';')
print(sample)

gives me 

          Timestamp  Type  ID              Data
0      22T230231142     0  ad  1100000000000000
1      22T230231143     0  ac  0000f5ff04000000
2      22T230231143     0  ab  0000000000000000
...

which is the correct output I am looking for, whereas what "should" be the proper function call

sample = pd.read_csv(filename, header=16, delimiter=';')
print(sample)

outputs

       22T230231143  0  a8  21005ac15cffd7ff
0      22T230231144  0  a7  0e00000006000000
1      22T230231144  0  a6  aeffa9ff90ff0000
2      22T230231144  0  a5  59054a003d0083d5
...

where the top row is the name of the columns taken from the middle of the data. When I removed the ; character from the header and called csv_read(filename, header=16, delimiter=';') the expected output was obtained, so it must be that semicolon. I can't find how to solve this issue in the documentation for read_csv or read_table, so if anyone knows that would be very helpful.

pandas delimiter python
2 Answers

IIUC, u could set the comments argument to '#'; pandas will automatically pick the first line without the # as ur header:

data = '''...wrapped ur data here...'''

#add the comments argument
#it will pick the first row after the hash
pd.read_csv(StringIO(data),comment='#',delimiter=';')

    Timestamp     Type  ID  Data
0   22T230231142    0   ad  1100000000000000
1   22T230231143    0   ac  0000f5ff04000000
2   22T230231143    0   ab  0000000000000000
3   22T230231143    0   aa  0000090000008000
4   22T230231143    0   a8  21005ac15cffd7ff
user_71757130

I think for this you'll need to use a little regex to read your file in and parse out the line to skip to as pandas will read ';' in the value sep row

import re


with open(r"your_file.csv",'r') as fin:
    for number,row in enumerate(fin):
        if re.match(r'# Value separator: ";"',row):
            row_start = number
        if not re.match('^#',row):
            skip_val = (number - row_start) + 2 # to account for 0 index & header
            break


df = pd.read_csv(your_file,sep=';',skiprows=skip_val)

print(df)

      Timestamp  Type  ID               Data
0  22T230231142     0  ad   1100000000000000
1  22T230231143     0  ac   0000f5ff04000000
2  22T230231143     0  ab   0000000000000000
3  22T230231143     0  aa   0000090000008000
4  22T230231143     0  a8   21005ac15cffd7ff
user_93751020
Related