Pandas pivoting on timestap table returns unexpected result
<p>I have a DataFrame  with two columns: <code>ts</code> (timestamp) and <code>n</code> (number)</p>

<p>timestamps begin at <code>2016-07-15</code>:</p>

<pre><code>In [1]: d.head()
Out[1]:
                       ts   n
0 2016-07-15 00:04:09.444  12
1 2016-07-15 00:05:01.633  12
2 2016-07-15 00:05:03.173  31
3 2016-07-15 00:05:03.970  12
4 2016-07-15 00:05:04.258  23
</code></pre>

<p>now, I pivot:</p>

<pre><code>pd.pivot_table(d, columns='n', values='ts', aggfunc=lambda x: (np.min(x) - pd.Timestamp('2016-07-15')).days)
</code></pre>

<p>I expect to see column with integers represent days but instead I see:</p>

<pre><code>n
12   1970-01-01
23   1970-01-01
31   1970-01-01
Name: ts, dtype: datetime64[ns]
</code></pre>

<p>What am O missing here? and is there a better way to achieve the same (trying to get the offset in days for the first appearance of <code>n</code> in the table)</p>

<p>IIUC you need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow"><code>groupby</code></a> and add custom function with <code>apply</code>:</p>

<pre><code>print (d.groupby('n')['ts'].apply(lambda x: (x.min() - pd.Timestamp('2016-07-15')).days))
n
12    0
23    0
31    0
Name: ts, dtype: int64
</code></pre>

<p>In your code you get <code>0</code> too, but values are converted to <code>datetime</code> (<code>1970-01-01</code>), because <code>dtype</code> of <code>ts</code> was <code>datetime</code> before.</p>

<p>I think then need cast <code>datetime</code> to <code>int</code>, but first convert to <code>numpy array</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.values.html" rel="nofollow"><code>values</code></a>:</p>

<pre><code>s = pd.pivot_table(d, columns='n', 
                      values='ts', 
                      aggfunc=lambda x: (np.min(x) - pd.Timestamp('2016-07-15')).days)
s = s.values.astype(int)
print (s)
n
12    0
23    0
31    0
Name: ts, dtype: int64
</code></pre>

python datetime pandas time-series pivot-table