Converting from Excel to HDF5 using Pandas
<p>I want to extract the content of an Excel document into a pandas dataframe and then write that dataframe into an HDF5 file. To do so, I've done this:</p>

<pre><code>xls_df = pd.read_excel(fn_xls)
xls_df.to_hdf(fn_h5, 'table', format='table', mode='w')
</code></pre>

<p>This results in the following error:      </p>

<blockquote>
  <p>TypeError: Cannot serialize the column [Col1] because
  its data contents are [unicode] object dtype</p>
</blockquote>

<p>I tried using convert.objects() on the dataframe from the Excel file, but this doesn't work (and convert.objects() is deprecated). Are there any suggestions on going about this?</p>

<p>Here is a little information on the Excel file:</p>

<pre><code>&lt;class 'pandas.core.frame.DataFrame'&gt;
RangeIndex: 101 entries, 0 to 100
Data columns (total 5 columns):
Col1                   101 non-null object
Col2                   101 non-null object
Col3                   94 non-null float64
Col4                   98 non-null object
Col5                   93 non-null float64
dtypes: float64(2), object(3)
</code></pre>

<p>The first and second columns are strings, the fourth column has 1 string but is mostly integers and the third and fifth columns are integers.</p>

<p>The mixed string and integer data types in column "Col4" are cause an error when converting to HDF5 in "table" format.</p>

<p>To save in hdf5 "tables" format you need to convert the numbers in Col4 to floats (and strings to NaN):</p>

<p><code>df["Col4"] = pd.to_numeric(df["Col4"], errors="coerce")</code></p>

<p>Or convert everything in the column to strings: </p>

<p><code>df["Col4"] = df["Col4"].astype(str)</code></p>

<p>Or use the "fixed" hdf5 format which allows columns to have mixed datatypes. This saves the mixed datatype column in in the python pickle format, and currently gives a PerformanceWarning.</p>

<p><code>df.to_hdf(outpath, 'yourkey', format='fixed', mode='w')</code></p>

python excel pandas dataframe hdf5