Pandas DataFrame.values conversion error or feature?
<p>I having some difficulty with some "behind the scenes" conversions that <code>pandas</code> (v. 0.18.0) seems to be performing for the <code>values</code> property of a <code>DataFrame</code>.
I have a data set that looks something like the following: </p>

<pre><code>data = [(1473897600000000, 9.9166, 1.8621, 15), 
        (1473897660000000, 19.9166, 3.8621, 20),
        (1473897720000000, 29.9166, 5.8621, 25), 
        (1473897780000000, 39.9166, 7.8621, 30)]
</code></pre>

<p>The first element of each tuple represents a POSIX UTC timestamp in microseconds.  The <code>(name, dtype)</code> of each element is given by the following record array:</p>

<pre><code>dtype = [('timestamp', np.dtype('int64')), 
         ('a', np.dtype('float32')),
         ('b', np.dtype('float32')), 
         ('c', np.dtype('uint8'))]
</code></pre>

<p>I'm converting this to a <code>DataFrame</code> as using the following code:</p>

<pre><code>data_array = np.array(data, dtype=dtype)
df = pd.DataFrame.from_records(data_array)
</code></pre>

<p>However, the following code gives some strange results (at least to me):</p>

<pre><code>ts_orig = np.array([x[0] for x in data], dtype=float)
ts_column = df['timestamp'].values.astype(float)
ts_values = df.values[:, 0]
ts_diff = ts_values - ts_column

print(np.column_stack((ts_orig, ts_column, ts_values, ts_diff)))

# OUTPUT
#      ts_orig         ts_column         ts_values       ts_diff
[[  1.47389760e+15   1.47389760e+15   1.47389762e+15   1.87351040e+07]
 [  1.47389766e+15   1.47389766e+15   1.47389762e+15  -4.12648960e+07]
 [  1.47389772e+15   1.47389772e+15   1.47389775e+15   3.29528320e+07]
 [  1.47389778e+15   1.47389778e+15   1.47389775e+15  -2.70471680e+07]]
</code></pre>

<p>It seems clear that there is no problem converting the timestamp values to  identical floating point values directly (columns 1 and 2).  Accurate values are still retained in the <code>DataFrame</code>, however when the entire <code>DataFrame</code> is converted to an array through the <code>DataFrame.values</code> property (3rd column) something happens to scramble the timestamp values and throw them wildly off.   </p>

<p>Why is this happening and how do I prevent it?</p>

<p>At the risk of sounding stupid, I'll answer my own question, should have dug a little deeper in the first place.  The root of the problem is the conversion to the lowest common denominator type, quoting from the <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.values.html#pandas.DataFrame.values" rel="nofollow">DataFrame.values</a> docmentation: </p>

<blockquote>
  <p>The dtype will be a lower-common-denominator dtype (implicit upcasting); that is to say if the dtypes (even of numeric types) are mixed, the one that accommodates all will be chosen. Use this with care if you are not dealing with the blocks</p>
</blockquote>

<p>In this case the most accommodating type chosen is <code>float32</code> so all values are converted to that.  This can store the value of the <code>int64</code> timestamp but due to numeric precision issues with the <code>float32</code> type the timestamp values snap to the nearest <code>float32</code> value.  If you change one of the <code>dtype</code> values in the original data to <code>float64</code> the problem silently disappears.  </p>

<p>Here is a followup question though.  Given a mixed data set with 64 bit integer types and floating point types, wouldn't the best course of action be to choose the widest type (64 bit) so no precision is lost?</p>

python pandas