The final solution was to use the read_csv converters parameter and check each value before adding it to the DataFrame. In the end, there were only 2 broken values in over 80 GB of raw data.
The parameter is as follows:
converters={'XXXXX': self.parse_xxxxx}
And a small static helper method like this:
@staticmethod
def parse_xxxxx(input):
if not isinstance(input, float):
try:
return float(input)
except ValueError:
print "Broken Value: ", input
return float(0.0)
else:
return input
When trying to read approx. 40GB + csv data to HDF file I ran into a confusing problem. After reading about 1 GB, the whole process fails with the following error
File "/usr/lib/python2.7/dist-packages/pandas/io/pytables.py", line 658, in append
self._write_to_group(key, value, table=True, append=True, **kwargs)
File "/usr/lib/python2.7/dist-packages/pandas/io/pytables.py", line 923, in write_to_group
s.write(obj = value, append=append, complib=complib, **kwargs)
File "/usr/lib/python2.7/dist-packages/pandas/io/pytables.py", line 2985, in write **kwargs)
File "/usr/lib/python2.7/dist-packages/pandas/io/pytables.py", line 2675, in create_axes
raise ValueError("cannot match existing table structure for [%s] on appending data" % items)
ValueError: cannot match existing table structure for [Date] on appending data
The read_csv method used is as follows:
pd.io.parsers.read_csv(filename, sep=";|\t", compression='bz2', index_col=False, header=None, names=['XX', 'XXXX', 'Date', 'XXXXX'], parse_dates=[2], date_parser=self.parse_date, low_memory=False, iterator=True, chunksize=self.input_chunksize, dtype={'Date': np.int64})
Why doesn't the Date column of the new fragment match the existing column when I implicitly set dtypte to int64?
Thanks for your help!
:
@staticmethod
def parse_date(input_date):
import datetime as dt
import re
if not re.match('\d{12}', input_date):
input_date = '200101010101'
timestamp = dt.datetime.strptime(input_date, '%Y%m%d%H%M')
return timestamp
Jeff . , bz2:
iterator_data = pd.io.parsers.read_csv(filename, sep=";|\t", compression='bz2', index_col=False, header=None,
names=['XX', 'XXXX', 'Date', 'XXXXX'], parse_dates=[2],
date_parser=self.parse_date, iterator=True,
chunksize=self.input_chunksize, dtype={'Date': np.int64})
for chunk in iterator_data:
self.data_store.append('huge', chunk, data_columns=True)
self.data_store.flush()
csv : {STRING}; {STRING}; {STRING}\t {INT}
ptdump -av, , :
ptdump -av datastore.h5
/ (RootGroup) ''
/._v_attrs (AttributeSet), 4 attributes:
[CLASS := 'GROUP',
PYTABLES_FORMAT_VERSION := '2.0',
TITLE := '',
VERSION := '1.0']
/huge (Group) ''
/huge._v_attrs (AttributeSet), 14 attributes:
[CLASS := 'GROUP',
TITLE := '',
VERSION := '1.0',
data_columns := ['XX', 'XXXX', 'Date', 'XXXXX'],
encoding := None,
index_cols := [(0, 'index')],
info := {'index': {}},
levels := 1,
nan_rep := 'nan',
non_index_axes := [(1, ['XX', 'XXXX', 'Date', 'XXXXX'])],
pandas_type := 'frame_table',
pandas_version := '0.10.1',
table_type := 'appendable_frame',
values_cols := ['XX', 'XXXX', 'Date', 'XXXXX']]
/huge/table (Table(167135401,), shuffle, blosc(9)) ''
description := {
"index": Int64Col(shape=(), dflt=0, pos=0),
"XX": StringCol(itemsize=16, shape=(), dflt='', pos=1),
"XXXX": StringCol(itemsize=16, shape=(), dflt='', pos=2),
"Date": Int64Col(shape=(), dflt=0, pos=3),
"XXXXX": Int64Col(shape=(), dflt=0, pos=4)}
byteorder := 'little'
chunkshape := (2340,)
autoIndex := True
colindexes := {
"Date": Index(6, medium, shuffle, zlib(1)).is_CSI=False,
"index": Index(6, medium, shuffle, zlib(1)).is_CSI=False,
"XXXX": Index(6, medium, shuffle, zlib(1)).is_CSI=False,
"XXXXX": Index(6, medium, shuffle, zlib(1)).is_CSI=False,
"XX": Index(6, medium, shuffle, zlib(1)).is_CSI=False}
/huge/table._v_attrs (AttributeSet), 23 attributes:
[XXXXX_dtype := 'int64',
XXXXX_kind := ['XXXXX'],
XX_dtype := 'string128',
XX_kind := ['XX'],
CLASS := 'TABLE',
Date_dtype := 'datetime64',
Date_kind := ['Date'],
FIELD_0_FILL := 0,
FIELD_0_NAME := 'index',
FIELD_1_FILL := '',
FIELD_1_NAME := 'XX',
FIELD_2_FILL := '',
FIELD_2_NAME := 'XXXX',
FIELD_3_FILL := 0,
FIELD_3_NAME := 'Date',
FIELD_4_FILL := 0,
FIELD_4_NAME := 'XXXXX',
NROWS := 167135401,
TITLE := '',
XXXX_dtype := 'string128',
XXXX_kind := ['XXXX'],
VERSION := '2.6',
index_kind := 'integer']
:
ValueError: invalid combinate of [values_axes] on appending data [name->XXXX,cname->XXXX,dtype->int64,shape->(1, 10)] vs current table [name->XXXX,cname->XXXX,dtype->string128,shape->None]
, read_csv, XXXX, :
dtype={'XXXX': 's64', 'Date': dt.datetime})
read_csv dtype ?
chunksize 10 2 chunk.info() :
Int64Index: 10 entries, 0 to 9
Data columns (total 4 columns):
XX 10 non-null values
XXXX 10 non-null values
Date 10 non-null values
XXXXX 10 non-null values
dtypes: datetime64[ns](1), int64(1), object(2)<class 'pandas.core.frame.DataFrame'>
Int64Index: 10 entries, 0 to 9
Data columns (total 4 columns):
XX 10 non-null values
XXXX 10 non-null values
Date 10 non-null values
XXXXX 10 non-null values
dtypes: datetime64[ns](1), int64(2), object(1)
pandas 0.12.0.