CODEWITHSUNDEEP
pythonpandastimestamp
Marc Schwambach
Marc Schwambach

Reputation: 438

Converting a Datatime column in pandas dataframe to seconds

I believe that my problem is really straightforward and there must be a really easy way to solve this issue, however as I am quite new with Python, I could not sort it out. I saw some similar questions but none of them really relate to the problem that I am facing.

I have one dataframe, in which the time is set by the column "Timestamp", displayed by the format YYYY-MM-DD HH:MM:SS. My goal is to plot this column in overall seconds, hence translating all this Datatime to seconds starting from the index 0 until the last index. Basically, the difference between my first element and last element should be displayed in seconds, and I want the count in seconds to start from the first element(0s) of the column "Timestamp'. At last, I will plot it as my x and speed and my y. My situation is naturally more complex than the scenario that I am posting here, as I am loading an external file into a dataframe and they might display the timestamp with different intervals.

            Timestamp      Category  ...      Status          Quantity
0     2011-02-14 20:00:00         1  ...          1               1
1     2011-02-14 20:00:01         1  ...          1               1
2     2011-02-14 20:00:02         1  ...          1               1.1
3     2011-02-14 20:00:03         1  ...          1               1.2
4     2011-02-14 20:00:04         1  ...          1               1.2
5     2011-02-14 20:00:05         1  ...          1               1.2
6     2011-02-14 20:00:06         1  ...          1               1.2
7     2011-02-14 20:00:07         1  ...          1               1.2
8     2011-02-14 20:00:08         1  ...          1               1.2

35999 2011-02-14 22:59:59         0  ...          1               2.3

Hope that I managed to be succinct and precise. I would really appreciate your help on this one!

Upvotes: 1

Views: 153

Answers (1)

jezrael
jezrael

Reputation: 862641

Use Timedeltas by subtract first value by Series.sub and convert to seconds by Series.dt.total_seconds:

df['td'] = df['Timestamp'].sub(df['Timestamp'].iat[0]).dt.total_seconds()

If there are all seconds then get range from 0 to length of DataFrame.

So same output is:

df['td'] = range(len(df))

Or:

df['td'] = df.index

If need seconds:

df['td'] = pd.to_timedelta(df['Timestamp'].dt.strftime(%H:%M:%S)).dt.total_seconds()

Upvotes: 1

Related Questions