Getting a ... in my CSV when using to_csv()

[BrendanMartin]

I have read in an hdf of 4 million+ rows and now I want to convert it to a sample CSV:

df_small = df[:int(1e6)]
df_small.to_csv("X.csv", sep='\t')
len(df_small)
# out: 1,000,000

The dataframe consists of a datetime index and a text column.

When I read the CSV back in, I get more rows than when I saved it:

df2 = pd.read_csv("X.csv",  
                  sep='\t',
                  engine='python', 
                  parse_dates=['datetime'],            
                  index_col='datetime'
                  infer_datetime_format=True)
len(df2)
# out: 1,000,002

And looking at my index, the datetime wasn't actually parsed, it's just dtype Object.

I used my own parser and it had an error when it hit a "..." in my datetime index, which wasn't there before.

I opened up the CSV in Excel and found a "..." in my datetime column, and I also noticed that my datetime index and first column were merged together. Not sure if that's relevant or just the way Excel reads it.

When I use read_csv the data comes in fine except for that couple of extra rows with "..." in the index. The row at that index is also just blank.

[WillAyd]

Unfortunately can't reproduce anything with the information provided - do you have a minimal example to reconstruct the issue?

[BrendanMartin]

I actually found out what it was. The text is all tweets, and I guess some tweets have \r in them for some reason. This seems to have caused a newline when writing to CSV, so I removed all \r and now I'm getting the right number of rows.

If there's \r in text, should it have been escaped?

[WillAyd]

Do you run into the same issue using the default C parser instead of python on read_csv? I imagine with the latter you are probably just mangling the universal newlines across platforms.

[BrendanMartin]

If I don't remove the \r and I try to use the C engine instead, I get this error:

---------------------------------------------------------------------------
ParserError                               Traceback (most recent call last)
<ipython-input-53-40a8d49090a2> in <module>()
      8 #                   date_parser=dateparser,
      9                   index_col='datetime',
---> 10                   infer_datetime_format=True,
     11                   )
     12 

~\AppData\Roaming\Python\Python36\site-packages\pandas\io\parsers.py in parser_f(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, escapechar, comment, encoding, dialect, tupleize_cols, error_bad_lines, warn_bad_lines, skipfooter, skip_footer, doublequote, delim_whitespace, as_recarray, compact_ints, use_unsigned, low_memory, buffer_lines, memory_map, float_precision)
    707                     skip_blank_lines=skip_blank_lines)
    708 
--> 709         return _read(filepath_or_buffer, kwds)
    710 
    711     parser_f.__name__ = name

~\AppData\Roaming\Python\Python36\site-packages\pandas\io\parsers.py in _read(filepath_or_buffer, kwds)
    453 
    454     try:
--> 455         data = parser.read(nrows)
    456     finally:
    457         parser.close()

~\AppData\Roaming\Python\Python36\site-packages\pandas\io\parsers.py in read(self, nrows)
   1067                 raise ValueError('skipfooter not supported for iteration')
   1068 
-> 1069         ret = self._engine.read(nrows)
   1070 
   1071         if self.options.get('as_recarray'):

~\AppData\Roaming\Python\Python36\site-packages\pandas\io\parsers.py in read(self, nrows)
   1837     def read(self, nrows=None):
   1838         try:
-> 1839             data = self._reader.read(nrows)
   1840         except StopIteration:
   1841             if self._first_chunk:

pandas\_libs\parsers.pyx in pandas._libs.parsers.TextReader.read()

pandas\_libs\parsers.pyx in pandas._libs.parsers.TextReader._read_low_memory()

pandas\_libs\parsers.pyx in pandas._libs.parsers.TextReader._read_rows()

pandas\_libs\parsers.pyx in pandas._libs.parsers.TextReader._tokenize_rows()

pandas\_libs\parsers.pyx in pandas._libs.parsers.raise_parser_error()

ParserError: Error tokenizing data. C error: Buffer overflow caught - possible malformed input file.

[WillAyd]

Hmm OK. FWIW it looks like Python's built-in CSV module is currently hard-coded to recognize \r or \n as EOL:

https://docs.python.org/3.6/library/csv.html#csv.Dialect.lineterminator

I suppose irrespective of the Python parser that the C parser should not be getting tripped up by it so this is probably a bug. Investigation / PRs welcome

[gfyoung]

@BrendanMartin : Can you provide an example tweet (and a minimal example) that would cause the CSV parser to break?

[smsubrahmannian]

I actually found out what it was. The text is all tweets, and I guess some tweets have \r in them for some reason. This seems to have caused a newline when writing to CSV, so I removed all \r and now I'm getting the right number of rows.

If there's \r in the text, should it have been escaped?

I had the same issue. By removing \r and \n in the text. Things were resolved.