How to convert pandas DataFrame index from datetime to a simple time index

Question

I'm trying to evaluate some quadcopter flight data and got some log-files with epoch timestamps.

I then converted them to datetime values (with pd.to_datetime([...], unit='ms')) and truncated some digits.

My problem is, that I actually don't need the dates, which also makes plotting the data a lot more complicated (unwanted xtick distances, error inducing matplotlib.dates locators, etc).

Now I'm left with the following index:

2019-09-13 10:09:16.200,...
2019-09-13 10:09:16.300,...
2019-09-13 10:09:16.400,...
...
2019-09-13 10:12:18.300,...

My imports:

%matplotlib inline
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import pandas as pd
import glob
import os.path
from datetime import datetime
from mpl_toolkits.mplot3d import Axes3D

My data input/initialization:

data  = pd.read_csv(s,',',index_col=0) # Commands
data2 = pd.read_csv(s2,',',index_col=0) # Observations

d1 = data[data['field.handle']==d].drop(['field.handle','Commanded alpha','Commanded beta','Commanded gamma'], axis=1)
d2 = data2[data2['field.handle']==d].drop(['field.handle','Observed alpha','Observed beta','Observed gamma'], axis=1)
merged_data = pd.concat([d1,d2], axis=1, sort=False)
merged_data.index = truncate_index(merged_data)
filled_merge = merged_data.groupby(merged_data.index).mean().fillna(method='ffill')
finished_merge = filled_merge.copy().dropna()
deviations = finished_merge.copy()

My plot code (sometimes working, sometimes not - depends on data, locators and formatter)

myFmt = mdates.DateFormatter('%M')
ax = deviations.plot(figsize=(14,9), use_index=True, y=['Positional x deviation','Positional y deviation','Positional z deviation'], subplots=True, sharex=True, layout=(3,1))
for axis in ax:
       for axi in axis:
              axi.set(xlabel = "Time in minutes (minor ticks in seconds)", ylabel="Deviation in meters")
              axi.xaxis.set_major_formatter(myFmt)
              axi.xaxis.set_minor_locator(mdates.SecondLocator())
              axi.xaxis.set_major_locator(mdates.MinuteLocator())
plt.suptitle(plot_title, fontsize=14)
plt.subplots_adjust(top=0.92)

It would be more beneficial for my work, I think, if the index could be in milliseconds (or fractions of a second - e.g., a float value) - starting at the first datetime value, like for example: 2019-09-13 10:09:16.200(first entry) will become 0 or 0.0, where the second entry would change from 2019-09-13 10:09:16.300 to 0.1. I sadly can not drop the index altogether and just numerate with the row count, as there are some gaps in the datetimes for, for example, 300 milliseconds, that I want to preserve.

I tried different things to plot my data consistently, but in the end nothing worked and I hope a new approach with a new index will solve my problem(s)...

I also looked at possible candidates in the pandas and matplotlib API (frome timedeltas to date2num, etc.) to enable my envisioned index-transformation, but to no evail. Probably because I'm not really sure what the correct terminology would be for this 'transformation'.

Any help is really appreciated!

Possible duplicate of [Converting pandas date column into seconds elapsed](https://stackoverflow.com/questions/44445336/converting-pandas-date-column-into-seconds-elapsed) — wwii, Sep 14 '19 at 15:11

score 0 · Accepted Answer · answered Sep 14 '19 at 15:06

If your index looks like this:

>>> d = ['2019-09-13 10:09:16.200',
'2019-09-13 10:09:16.300',
'2019-09-13 10:09:16.400',
'2019-09-13 10:12:18.300']
>>> s = pd.Series([pd.Timestamp(thing) for thing in d])
>>> s
0   2019-09-13 10:09:16.200
1   2019-09-13 10:09:16.300
2   2019-09-13 10:09:16.400
3   2019-09-13 10:12:18.300
dtype: datetime64[ns]
>>>

You can create a timedelta series and get total seconds relative to the first item. And use it.

>>> a = s - s[0]
>>> a
0          00:00:00
1   00:00:00.100000
2   00:00:00.200000
3   00:03:02.100000
dtype: timedelta64[ns]
>>> a.dt.total_seconds()
0      0.0
1      0.1
2      0.2
3    182.1
dtype: float64
>>>

Thank you very much! :) Works as intended! Another way I've discovered, that seems to work: `deviations.index = deviations.index.astype(np.int64) // 10**8 deviations.index = (deviations.index - deviations.index[0]) deviations.index = deviations.index.astype(np.float64) / 10 ` — Vorkosik, Sep 14 '19 at 15:16

How to convert pandas DataFrame index from datetime to a simple time index

1 Answers1