How do I convert entire columns of data (date HH:mm:ss.ms, or just H:MM.SS.ms) to a decimal (e.g. hours) in python/PANDASn

Question

I work with a variety of instruments, and one is particularly troublesome in that the exported data is in XLS or XLSX format with multiple pages, and multiple columns. I only want some pages and some columns, I have achieved reading this into pandas already.

I want to convert time (see below) into a decimal, in hours. This would be from an initial time (in the time stamp data) at the top of the column so timedelta is probably a more correct value, in hours. I am only concerned about this column. How to convert an entire column of data from one format, to another?

date/time (absolute time) timestamped format YYYY-MM-DD TT:MM:SS

I have found quite a few answers but they don't seem to apply to this particular case, mostly focusing on individual cells or manually entered small data sets. My thousands of data files each have as many as 500,000 lines so something more automated is preferred. There is no upper limit to the number of hours.

What might be part of the same question (someone asked me) is this is already in a Pandas dataframe, should it be converted before or after being read in?

This might seem an amateur-ish question, and it is. I've avoided code writing for years, now I have to learn to data-wrangle for my job and it's frustrating so go easy on me.

Going about it the usual way by trying to adapt most of the solutions I found to a column, I get errors

**This is the code which works

import pandas as pd
import matplotlib.pyplot as plt

from pathlib import Path

from datetime import datetime  # not used

import time  #  not used

import numpy as np  # Not used


loc1 = r"path\file.xls"
pd.read_excel(loc1)   

filename=Path(loc1).stem  
str_1=filename

df = pd.concat(pd.read_excel(loc1, sheet_name=[3,4,5,6,7,8,9]), ignore_index=False)

***I NEED A CODE TO CONVERT DATESTAMPS TO HOURS (decimal) most likely a form of timedelta***

df.plot(x='Relative Time(h:min:s.ms)',y='Voltage(V)', color='blue')
plt.xlabel("relative time")   #  This is a specific value
plt.ylabel("voltage (V)")  
plt.title(str_1)        # filename is used in each sample as a graph              title
plt.show()  

Image of relevent information (already described above)

Answer 1

You should provide a minimal reproducible example, to help understand what exactly are the issues you are facing.

Setup

Reading between the lines, here is a setup that hopefully exemplifies the kind of data you have:

vals = pd.Series([
    '2019-10-21 17:22:06',  # absolute date
    '2019-10-21 23:22:06.236',  # absolute date, with milliseconds
    '2019-10-21 12:00:00.236145',  # absolute date, with microseconds
    '5:10:10',  # timedelta
    '40:10:10.123',  # timedelta, with milliseconds
    '345:10:10.123456',  # timedelta, with microseconds
])

Solution

Now, we can use two great tools that Pandas offers to quickly convert string series into Timestamps (pd.to_datetime) and Timedelta (pd.to_timedelta), for absolute date-times and durations, respectively.

In both cases, we use errors='coerce' to convert what is convertible, and leave the rest to NaN.

origin = pd.Timestamp('2019-01-01 00:00:00')  # origin for absolute dates

a = pd.to_datetime(vals, format='%Y-%m-%d %H:%M:%S.%f', errors='coerce') - origin
b = pd.to_timedelta(vals, errors='coerce')
tdelta = a.where(~a.isna(), b)
hours = tdelta.dt.total_seconds() / 3600

With the above:

>>> hours
0    7049.368333
1    7055.368399
2    7044.000066
3       5.169444
4      40.169479
5     345.169479
dtype: float64

Explanation

Let's examine some of the pieces above. a handles absolute date-times. Before subtraction of origin to obtain a Timedelta, it is still a Series of Timestamps:

>>> pd.to_datetime(vals, format='%Y-%m-%d %H:%M:%S.%f', errors='coerce')
0   2019-10-21 17:22:06.000000
1   2019-10-21 23:22:06.236000
2   2019-10-21 12:00:00.236145
3                          NaT
4                          NaT
5                          NaT
dtype: datetime64[ns]

b handles values that are already expressed as durations:

>>> b
0                       NaT
1                       NaT
2                       NaT
3           0 days 05:10:10
4    1 days 16:10:10.123000
5   14 days 09:10:10.123456
dtype: timedelta64[ns]

tdelta is the merge of the non-NaN values of a and b:

>>> tdelta
0          293 days 17:22:06
1   293 days 23:22:06.236000
2   293 days 12:00:00.236145
3            0 days 05:10:10
4     1 days 16:10:10.123000
5    14 days 09:10:10.123456
dtype: timedelta64[ns]

Of course, you can change your origin to be any particular date of reference.

Addendum

After clarifying comments, it seems that the main issue is how to adapt the solution above (or any similar existing example) to their specific problem.

Using the names seen in the images of the edited question, I would suggest:

# (...)
# df = pd.concat(pd.read_excel(loc1, sheet_name=[3,4,5,6,7,8,9]), ignore_index=False)

# note: if df['Absolute Time'] is still of dtypes str, then do this:
# (adapt format as needed; hard to be sure from the image)
df['Absolute Time'] = pd.to_datetime(
    df['Absolute Time'],
    format='%m.%d.%Y %H:%M:%S.%f',
    errors='coerce')

# origin of time; this may have to be taken over multiple sheets
# if all experiments share an absolute origin
origin = df['Absolute Time'].min()

df['Time in hours'] = (df['Absolute Time'] - origin).dt.total_seconds() / 3600