How to use int timestamps as index in pandas dataframe

Question

Beginner here and a little confused.

I have a set of data that makes heavy use of UNIX int timestamps (ms). A lot of this is outside the scope of my software, we're talking APIs, DBs, etc. etc.

These APIs, Databases, etc. Are all setup to ingest and return Dataframes, and these contain the dates as ints: 1642463640000

Because of this, I don't want to convert these to readable dates.

In addition to this these timestamps are set as an index, so for example:

df = pd.DataFrame({"timestamp": 1642463640000, 'otherdata':'yolo'})
df.set_index('timestamp', inplace=True)

My problem is that I can't, for the life of me, figure out how to slice this data to get, say, everything between two dates.

Because everything is expecting timestamps, I don't want to convert from timestamp to date and back constantly. Am I missing something obvious? Is there a timestamp dtype maybe? Any help would be appreciated.

You could convert the date(s) to use for slicing to int timestamp instead. That should be only 2 conversions. — mozway
– mozway, Commented Jan 19, 2022 at 5:51

Pankaj Saini · Accepted Answer · 2022-01-20 02:06:16Z

2

Agree with the suggestion by @mozway. I have used similar techniques before when working with unix epochs.

Consider this code:

import pandas as pd
from datetime import datetime

def timestamp(dt):
    epoch = datetime.utcfromtimestamp(0)
    return (dt - epoch).total_seconds() * 1000.0

dt_high = datetime(2022, 1, 18)
dt_low = datetime(2022, 1, 15)

epoch_high = timestamp(dt_high)
epoch_low = timestamp(dt_low)

filtered = df[(df.index > epoch_low) & (df.index < epoch_high)]

edited Jan 20, 2022 at 2:06

answered Jan 19, 2022 at 7:05

Pankaj Saini

1,1941 gold badge7 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pomme.Verte Over a year ago

This doesn't work for me. Am I missing something? I get a KeyError: 'timestamp' when running the last line

Pomme.Verte Over a year ago

I can get this working > df[(df["timestamp"] > epoch_low) & (df["timestamp"] < epoch_high)] IF I reset the index and turn timestamp into a column. Is there no better way?

Pomme.Verte Over a year ago

You pushed me in the right direction. Got this working : filtered = df[(df.index > 1642463630000) & (df.index < 1642463650000)] Thank you!! Feel free to add that to your response and I'll approve it :)

Pankaj Saini Over a year ago

Yes, the last line was untested because I did not have the right data frame. Fixed it now and retested using dates

Collectives™ on Stack Overflow

How to use int timestamps as index in pandas dataframe

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related