Pandas read_csv() conditionally skipping header row

Question

I'm trying to read a csv file but my csv files differ. Some have different format and some have other. I'm trying to add controls so that I will not need to edit my code or my input file.

My problem is, some of these csv files have a line of String above the column headers. An example:

Created on 12-11-2018,CryptoDataDownload.com
Date,Symbol,Open,High,Low,Close,Volume From,Volume To
2018-12-11 11-AM,ADABTC,8.6e-06,8.61e-06,8.55e-06,8.57e-06,301141.7,2.59
2018-12-11 10-AM,ADABTC,8.69e-06,8.72e-06,8.6e-06,8.6e-06,236949.63,2.05

If I import this, the delimeter will use the first line and separate the file into two columns as Created on 12-11-2018 and CryptoDataDownload.com.

This is how df.head() looks like:

                        Created on 12-11-2018 CryptoDataDownload.com
Date             Symbol Open     High     Low      Close              Volume From                          Volume To
2018-12-11 11-AM ADABTC 8.6e-06  8.61e-06 8.55e-06 8.57e-06              301141.7                               2.59
2018-12-11 10-AM ADABTC 8.69e-06 8.72e-06 8.6e-06  8.6e-06              236949.63                               2.05
2018-12-11 09-AM ADABTC 8.7e-06  8.7e-06  8.62e-06 8.69e-06             509311.39                               4.41
2018-12-11 08-AM ADABTC 8.69e-06 8.7e-06  8.63e-06 8.7e-06              111367.34                             0.9656

I want to check if this file has this line and skip it if so.

How can I do this?

@coldspeed Not all my files have this line so I need to first check if this line exists. Otherwise, I will delete my column headers — iso_9001_, Dec 12 '18 at 08:53
Do all of your dataframes have the same header, or could it be different? Is there a pattern associated with these headers (for example, "created on...")? — cs95, Dec 12 '18 at 08:56
Headers are different too but I edit them to be the same after import — iso_9001_, Dec 12 '18 at 08:59
I recommend moving the break to one indent level up, since it seems you only need to check the first line. — cs95, Dec 12 '18 at 13:15

cs95 · Accepted Answer · 2018-12-12T14:17:24.890

8

If the headers in your CSV files follow a similar pattern, you can do something simple like sniffing out the first line before determining whether to skip the first row or not.

filename = '/path/to/file.csv'
skiprows = int('Created in' in next(open(filename)))
df = pd.read_csv(filename, skiprows=skiprows)

Good pratice would be to use a context manager, so you could also do this:

filename = '/path/to/file.csv'
skiprows = 0
with open(filename, 'r+') as f:
    for line in f:
        if line.startswith('Created '):
            skiprows = 1
        break
df = pd.read_csv(filename, skiprows=skiprows)

edited Dec 12 '18 at 14:17

answered Dec 12 '18 at 08:59

cs95

379,657
97
704
746

Giving me `TypeError: 'numpy.ndarray' object is not callable` error. Is this next(f) 0 indexed? – iso_9001_ Dec 12 '18 at 10:51
@iso_9001_ that was a weird error to run into from my code. Were you able to figure out the bug? Feel free to edit my answer. Thanks! – cs95 Dec 12 '18 at 13:14
You might have deleted my working code while editing. I think your code doesn't work because `startswith` function returns `true` or `false`, not index. – iso_9001_ Dec 12 '18 at 13:57

Ernest S Kirubakaran · Answer 2 · 2019-01-02T06:03:42.717

0

You can skip rows which start with specific character while using 'comment' argument in pandas read_csv command. In your case you can skip the lines which starts with "C" using the following code:

filename = '/path/to/file.csv'
pd.read_csv(filename, comment = "C")

edited Jan 02 '19 at 06:03

answered Dec 12 '18 at 09:03

Ernest S Kirubakaran

1,524
12
16

Doesn't work correctly. I have 8 columns after that line but this creates a df of 5 columns because of a column name starting with 'C' (Close). – iso_9001_ Dec 12 '18 at 11:04
2

AFAIK `comment` parameter must be a single character so a whole word will not work. – iso_9001_ Dec 31 '18 at 22:18

João Flávio Santos · Answer 3 · 2022-07-27T03:32:20.233

It's work for me:

import os
import requests

CSV_URL = '...'

with open(os.path.split(CSV_URL)[1], 'wb') as f, requests.get(CSV_URL, stream=True) as r:
    lines = 0
    for line in r.iter_lines():
        if lines == 0:
            lines += 1
        else:
            f.write(line+'\n'.encode())

For your case:

import os
import requests

CSV_URL = '...'

with open(os.path.split(CSV_URL)[1], 'wb') as f, requests.get(CSV_URL, stream=True) as r:
    for line in r.iter_lines():
        if line[:11] != 'Created on ':
            f.write(line+'\n'.encode())

Adapted from: stackoverflow

Pandas read_csv() conditionally skipping header row

3 Answers3

Linked

Related