nrows limit fails reading well formed csv files from Australian electricity market data

[ChristopherShort]

Reading Australian electricity market data files, read_csv reads past the nrows limit for certain nrows values and consequently fails.

These market data files are 4 csv files combined into a single csv file and so the file has multiple headers and variable field size across the rows.

The first set of data is from rows 1-1442.

Intent was to extract first set of data with nrows = 1442.

Testing several arbitrary CSV files from this data source shows well formed CSV - 120 fields between rows 1 to 1442 (with a 10 field at row 0)

lines = [len(line.strip().split(',')) for i,line in enumerate(csvFile) if i < 1442]
s = pd.Series(lines)
print (s.value_counts())

returns
120 1441
10 1
dtype: int64

Other python examples of reading the market data using csv module work fine

In the reproducible example below, code works for nrows< 824, but fails on any value above it.

Testing on arbitrary files suggests the 824 limit is variable - sometimes a few more rows, sometimes a few less rows.

import requests, io, zipfile
import pandas as pd

url = 'http://www.nemweb.com.au/Reports/CURRENT/Public_Prices/PUBLIC_PRICES_201406290000_20140630040528.zip'

    # get the zip-archive
request = requests.get(url)

    # make the archive available as a byte-stream
zipdata = io.BytesIO()
zipdata.write(request.content)
thezipfile = zipfile.ZipFile(zipdata, mode='r')

    # there is only one csv file per arhive - read it into a Pandas DataFrame
fname = thezipfile.namelist()[0] 

with thezipfile.open(fname) as csvFile:

        #works for nrows < = 823
    df1 = pd.read_csv(csvFile, header=1, index_col=4, parse_dates=True, nrows=823)
    print(df1.head())

        #fails for n> 823
    df1 = pd.read_csv(csvFile, header=1, index_col=4, parse_dates=True, nrows=824)
    print(df1.head())

[ChristopherShort]

I meant to say - the sort of error returned is:

CParserError: Error tokenizing data. C error: Expected 120 fields in line 1443, saw 130

Which is expected - the field size changes past row 1442 - but for these files, the nrows limit reads past the 1442 (or 823+) value.

I also tested nrows on arbitrarily created csv files via numpy arrays but couldn't reproduce the error from the real data I was working with.

(And apologies for poorly formed markdown above - first time posting :-)

[jreback]

why don't you create a test. Pull the header and 2 rows from each section (then limit the number of fields). Then try this using nrows to skip. If this is a bug, would need to create a reproducible example.

[ChristopherShort]

thanks - but I'm unclear on your request - that is, I thought I did what you asked already.

I created a reproducible example with the code at the bottom of my post - admittedly with iPython rather than a straight python file.

I'm trying to extract the first section (rows 1-1442 of a 3366 row file) - this is where my problem occurs.

Was my code example unclear?

For reproducibility purposes, the bulk of the code deals with downloading a zip file, but the test is in the five lines from 'with thezipfile.open(fname) as csvFile:' onwards

I'm expecting it to be a subtle bug (or I'm doing something very wrong) - nrows parameter clearly works on the various examples I threw at it that were much larger in row length.

But at the same time, these electricity market files are well formed CSV files (they are part of the market data process in a live electricity market where auctions are run every 5 minutes for the past 15 years) - and pandas is failing to parse the files I used in developing the code.

[jreback]

no, what I mean is we need an example that you can simply copy and paste (and not use an external URL).

[ChristopherShort]

thanks - you've given me a thought that I can test it by just breaking the relevant part of the CSV file out.

But if it turns out to be related to the file structure itself - not sure how to provide a test without a link to a sample file. Would creating a github repo with some sample csv files and a few lines of code be suitable as the test?

[jreback]

see what you come up with. this is either a bug, which can be reproduced via a generated type of test file (e.g. create a specific structure), a problem with the csv, or incorrect use of the options.

We need a self-contained test in order to narrow down the problem.

lmk what you find.

[ChristopherShort]

Thanks.

If the file is only the relevant section (or rows to skip at the front) - no error.

Implies not a problem with use of options too I think.

If the file structure includes the very next line (no. 1443) - the 130 field header for the next section - it fails with any nrows>823.

I also experimented with deleting arbitrary number of rows (but small number) at the end of the section before the next header row - to see if the issue related to that particular line ending. Again fail.

I'm not sure I can create a test file - other than the sample files I've been experimenting with.

I'll go and figure out how to make a github repo and perhaps we can take it from there.

For info - the full error at the fail point is:

/Users/ChristopherShort/anaconda/lib/python3.4/site-packages/pandas/io/parsers.py in read(self, nrows)
1128
1129 try:
-> 1130 data = self._reader.read(nrows)
1131 except StopIteration:
1132 if nrows is None:

/Users/ChristopherShort/anaconda/lib/python3.4/site-packages/pandas/parser.so in pandas.parser.TextReader.read (pandas/parser.c:7146)()

/Users/ChristopherShort/anaconda/lib/python3.4/site-packages/pandas/parser.so in pandas.parser.TextReader._read_low_memory (pandas/parser.c:7547)()

/Users/ChristopherShort/anaconda/lib/python3.4/site-packages/pandas/parser.so in pandas.parser.TextReader._read_rows (pandas/parser.c:7979)()

/Users/ChristopherShort/anaconda/lib/python3.4/site-packages/pandas/parser.so in pandas.parser.TextReader._tokenize_rows (pandas/parser.c:7853)()

/Users/ChristopherShort/anaconda/lib/python3.4/site-packages/pandas/parser.so in pandas.parser.raise_parser_error (pandas/parser.c:19604)()

CParserError: Error tokenizing data. C error: Expected 120 fields in line 1443, saw 130

[ChristopherShort]

how about this for a test.

Create two CSV files ( 1442 rows by 120 cols and 5 rows by 130 cols)
Concatenate them
Read the joined CSV file back into a dataframe with nrows option <= 1442

It fails in the same way - though the nrows parameter was much larger before failure occurred relative to the examples above (where the files contained more strings)

In the example below - it fails for nrow>1360. Works fine for lower values.

pd.DataFrame(np.random.uniform(size=1442*120).reshape(1442,120)).to_csv('test120.csv',index=False)

pd.DataFrame(np.random.uniform(size=5*130).reshape(5, 130)).to_csv('test130.csv',index=False)


filenames = ['test120.csv', 'test130.csv']
with open('testNrows.csv', 'w') as outfile:
    for fname in filenames:
        with open(fname) as infile:
            for line in infile:
                outfile.write(line)

df = pd.read_csv('testNrows.csv', nrows=1361)

[jreback]

ok great, must be a bug somewhere

[ChristopherShort]

I have some time now to try and look at this bug, but not much experience.

Do you have any recommendations on things I should know first?

[jreback]

well it's going to be in parser.pyx

IMHO not so easy to debug cython

I would start by putting print statements to figure out what it is doing on this file

[ChristopherShort]

OK - thanks