CODEWITHSUNDEEP
pythonpython-3.xpandas
Cameron Stewart
Cameron Stewart

Reputation: 79

Pandas - Find the order of a time series occurrence in a series of time stamps

I have a series of dates in a df and want to be able to find the order that each occurred, by creating a new column ie...occurred the first in the series of values, 2nd....3rd..4000th...last etc.

0      2016-01-27 16:37:17 
1      2015-11-08 13:54:15 
2      2015-06-15 22:17:25 
3      2015-06-18 10:47:01 
4      2015-01-06 18:42:23 
5      2015-06-19 15:05:21 
6      2015-12-06 10:41:59 
....
5769   2011-03-24 11:42:24 
5770   2010-01-14 09:51:24 
5771   2010-01-13 14:30:28 
5772   2010-04-29 10:44:22 
5773   2010-01-14 10:31:26 
5774   2010-11-22 16:10:22 
5775   2010-08-07 11:45:14

Name: CreationTime, Length: 5776, dtype: datetime64[ns]

How do I approach this in pandas?

Upvotes: 0

Views: 117

Answers (2)

Alex Zisman
Alex Zisman

Reputation: 411

Sort by creation time and then copy the index. 

df = df.sort_values(by=['creation_time'])
df['rank'] = df.reset_index().index

Upvotes: 0

Anton vBR
Anton vBR

Reputation: 18916

Here is one way:

import pandas as pd
import numpy as np

# Create sample data
data = dict(dates=pd.date_range(start="2017-12-01", end="2017-12-15"))

# Make it a dataframe, resample and reset index
df = pd.DataFrame(data).sample(frac=1).reset_index(drop=True)

# Get id by using map with sorted column
df['idx'] = df.dates.map(dict(zip(sorted(df.dates),range(len(df)))))

print(df)

Sample return:

        dates  idx
0  2017-12-08    7
1  2017-12-10    9
2  2017-12-09    8
3  2017-12-01    0
4  2017-12-04    3
5  2017-12-07    6
6  2017-12-03    2
7  2017-12-05    4
8  2017-12-11   10
9  2017-12-14   13
10 2017-12-15   14
11 2017-12-12   11
12 2017-12-02    1
13 2017-12-06    5
14 2017-12-13   12

Upvotes: 1

Related Questions