Date difference in hours (Excel data import)?
<p>I need to calculate hour difference between two dates (format: year-month-dayTHH:MM:SS  I could also potentially transform data format to (format: year-month-day HH:MM:SS) from huge excel file. What is the most efficient way to do it in Python? I have tried to use Datatime/Time object (TypeError: expected string or buffer), Timestamp (ValueError) and DataFrame (does not give hour result).</p>

<p>Excel File:</p>

<pre class="lang-none prettyprint-override"><code>Order_Date             Received_Customer   Column3
2000-10-06T13:00:58    2000-11-06T13:00:58    1
2000-10-21T15:40:15    2000-12-27T10:09:29    2
2000-10-23T10:09:29    2000-10-26T10:09:29    3
.....                  ....
</code></pre>

<p><strong>Datatime/Time object code</strong> (TypeError: expected string or buffer):</p>

<pre><code>import pandas as pd
import time as t

data=pd.read_excel('/path/file.xlsx')

s1 = (data,['Order_Date'])
s2 = (data,['Received_Customer'])

s1Time = t.strptime(s1, "%Y:%m:%d:%H:%M:%S")
s2Time = t.strptime(s2, "%Y:%m:%d:%H:%M:%S")

deltaInHours = (t.mktime(s2Time) - t.mktime(s1Time))

print deltaInHours, "hours"
</code></pre>

<p><strong>Timestamp (ValueError) code:</strong></p>

<pre><code>import pandas as pd
import datetime as dt

data=pd.read_excel('/path/file.xlsx')

df = pd.DataFrame(data,columns=['Order_Date','Received_Customer'])
df.to = [pd.Timestamp('Order_Date')]
df.fr = [pd.Timestamp('Received_Customer')]
(df.fr-df.to).astype('timedelta64[h]')
</code></pre>

<p><strong>DataFrame (does not return the desired result)</strong></p>

<pre><code>import pandas as pd

data=pd.read_excel('/path/file.xlsx')

df = pd.DataFrame(data,columns=['Order_Date','Received_Customer'])

df['Order_Date'] = pd.to_datetime(df['Order_Date'])
df['Received_Customer'] = pd.to_datetime(df['Received_Customer'])

answer = df.dropna()['Order_Date'] - df.dropna()['Received_Customer']

answer.astype('timedelta64[h]')

print(answer)
</code></pre>

<p>Output:</p>

<pre class="lang-none prettyprint-override"><code>0   24 days 16:38:07
1    0 days 00:00:00
2   20 days 12:39:52
dtype: timedelta64[ns]
</code></pre>

<p>Should be something like this:</p>

<pre><code>0   592 hour
1   0   hour
2   492 hour
</code></pre>

<p>Is there another way to convert <code>timedelta64[ns]</code> into <strong>hours</strong> than <code>answer.astype('timedelta64[h]')</code>?</p>